1

I have an Excel worksheet which is regularly populated with daily expenses:

enter image description here

Then to extract a report on the amount of money spent on each category, I simply use a pivot table like below:

enter image description here

I am not sure how to integrate the planned budget into this report. Suppose I've got the planned budget stored in a separate table:

enter image description here

How can I seamlessly integrate this plan into my report so that I can know how much have been spent in each category according to the plan? i.e. What's the best way to get a report like this (all in one table):

enter image description here

Is it possible to manipulate the pivot table fields to get such report?

1

use vlookup to complement the table with the link on category.

  • create a new table [sheet] called 'Status' that reference the planned budget (optional but easier)

  • create first column with something like cell A1 formula is =Planned!A1 until A7 [could be bigger])

  • create second column with :
    • B1 = Spent
    • B2 through B7 something like cell b2 formula is =vlookup(A2;PivotSpent!$A$2:$b$7;false). Normaly the pivot table should be sorted on category (first column) for a vlookup to work.
  • create third column with:
    • C1 = planned
    • c2 through C7 formula like =Planned!b7
  • column D and E are calculation base on column B
    • D: =b2/C2 (cell format in %) + conditionnal formating
    • E: =c2-b2 + and conditionnal formating
  • add sum in lower cell

Planed and PivotSpend are name i give to sheet that contain the table with data (but could be the same and also on same sheet as the one that will containt this new array). I assume taht array inside are located since cell A1 (adapt reference if somewhere else)

You can do it with pivot table to be fully dynamic but certainly need to create a temporary data storage sheet with both source table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.