excel budget spread sheet

[robert402310]

Greetings, I am new to exel. I have an spread sheet I developed and converted from Lotus to keep track of my granddaughter's expenses for a SSI annual report. So, I have column for the date, the expenditure, the type of expenditure (a number), the amount and a running total. the issue is, I cannot figure out the formula to take the expenditure amount from it cell and and based on the type of expenditure, place the amount in the proper column. For example, food is the number 4 , the amount is 15 dollars and I want the 15 dollars to be reflected or copied to column 10 in the spread sheet

[#2]

@robert402310 

Let me suggest a slightly different approach. Use Excel's Pivot Table capability to do the breaking out by category.

I've attached a very simple example, using random numbers for expenses (beween 13 and 500, if you're curious) and only four categories, also assigned randomly.

My basic goal was to demonstrate how the Pivot Table, which you'll see to the right of the database, can break out the expenses by month by category (and, frankly, I'd start using names or abbreviations rather than numbers for the category, perhaps entering the number but using VLOOKUP to translate into "Food", "Car", etc)

Anyway, take a look. The Pivot Table makes data entry easier and reporting clearer.

By all means, come back with follow-up questions if you want more explanation.

[#3]

Here,@robert402310 , is a revised version of the spreadsheet I sent earlier. I've done two main things here which I hope are helpful.

1. I've moved the Pivot Table to its own tab (tabs are down at the bottom), away from the database on which it's based. This is generally a good practice for a variety of reasons.
2. I've added a lookup table that translates the category codes into more user friendly budget category labels. It would be easy to do the same (or something similar) with month numbers, turning them into the three letter abbreviations for the names of the months.

(Just an aside: once you get into the Pivot Table menu, you'll see my numbers change each time you hit refresh; that's because the random number function gets activated each time. Clearly that wouldn't happen with real numbers, real data)

I just recently created a (much more ambitious) expense tracker for our own personal use and will say that once you get into the Pivot Table, you'll find it amazingly powerful as a tool for summarizing and analyzing basic raw data such as a history of expenses. Just as one example, you can lump expense categories under "Fixed" and "Discretionary" just by adding another column to that lookup table and then the raw database itself. You can add Income categories as well. And then the pivot table will group and subgroup....

A personal aside: I cut my spreadsheet eyeteeth on Lotus (back in the 1980s) as did you. Good to meet another old-timer. Did you also use Quattro in some of the intervening years? Anyway, Excel has built on those foundations and added features on a regular basis. I hope you'll grow in your use and mastery.

[#4]

how can I zero out everything at once

[#5]

@kenny40 , is that question related to what was discussed above in this conversation?