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 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .