Excel help - how to categorise my spends!

[Livingmagicallyagain]

Help please!

I already have an excel spreadsheet of all of my spends for the last 6 months. I want to somehow categorise each amount (electricity, food, clothes etc) so that at the end I can clock on, say, food and it adds up everything I have spent on food.

I don't want to have to create a new spreadsheet, drag and drop into columns - just work within what I have.

Is there a way to do this? TIA!

Could you introduce another column to filter as you wish?
E.g. “Expense type” with options such as “food/groceries”, “utility bills”, “commute/petrol”, “leisure/weekend spend” etc

Yes you need to have a column with all the titles of the different categories. And then you can just filter by that column

I'd recommend using an open banking app, such as Snoop. You can link all of your accounts to one app and it will do all of this for you, and more. Saves a lot of work.

Yes, extra column and then use a pivot table or SUMIF formula to total it up by category. Decide on your categories before you start and use them consistently.

Thank you all, I can do so much with Excel but this just seems beyond me. Not in UK so many of those apps don't work!

Ok, so extra columns next to the spends. How do I set the categories, and then how do I link the categories to the amounts in the cells next to them? And then how do I clock on, say, food and get the total?

One column has to have the category that you want to specify. If you then at the top of the one with £ totals put a formula in and use subtotal in it, and put a filter on the top row, you can select say 'food' and the total spend on food will appear at the top of the £ column.

Or you can do a pivot table that you can have on the next tab, which will recalculate your spends whenever you want to see what you have spent.

how do you mean “how do I set the categories”.

For the totals you could either create a pivot table (do you know how) or set up a second table that lists the categories (food/transport/cinema/etc) in one column and a Sumif in the next to get the totals.

Thank you, pivots are something I have never needed to do. I have uploaded the excel to Sheets and finding the data validation easier to work with there.

I want to just click and go iykwim, rather than move the amounts over to several new columns.

Put in a new column with a drop down list of categories, then do an IF statement to add up the relevant sections.

SUMIF, sorry.

Pivot tables are brilliant and easy, like doing a graph.

Insert pivot table, highlight the data set you want it to include, use categories as your rows, date/month/year (whatever you have in your data set, month will probably be the most useful) as your columns and then in the values section select 'sum' you can format

I'm guessing you have ? 3 ? columns at the moment? Date, amount and description.

You'd just add a fourth column called Category and pick the correct category for each row.

Pivots are really easy, probably simplest to watch a video tutorial first.

Add an extra column, do a data validation on that to creat a drop down box for each category
then do a pivot( splice it if you’re feeling fancy)

Than you all.

Yes, I have date, description and value. Now I also have categories, with data validation drop down menu but am stuck at this point, will look at a pivot table tutorial. Once I get it, I'll get it!

You don't need to do a pivot table - I've got no idea how to do them. Just highlight the whole table and click on Sort and filter and then filter.

You can then filter by whichever type of spend you want.

Use the pivot table wizard.

Select all the columns

Click pivot table wizard

Select new tab for where you want the pivot table

Then drag £ to the top of the pivot table and the category column name whatever you have called it for the left hand side side and then £ for the bottom right, and finish.

When your table is created it will automatically count the numbers rather than sum up the totals so right click where one of the numbers is shown and change it to 'sum' and it will sum up the total spend for each category.

It would help if you showed us a screenshot of what you have called your columns.

Thank you, this is super. So far Pivot doesn't like "field name" of my table and will not let me progress. Hmmm!

Columns are called "money out" "category" and that is it!

@Livingmagicallyagain Are all the columns named? Such as this? It's a pity I can't attach an excel sheet for you

1. Select Insert from top
2. Pivot table
3. Choose your table/ range using the up arrow box
4. Hit ok – this will take you to a new tab
5. Select your fields say Supplier and amount – these are on the right hand side
6. And you should have something like this

Ok, thank you - I will try this tomorrow when the kids are occupied watching a movie! Determined!

Ok, I have it and it is actually so easy! Big huge thank you! Now for the scary part of actually adding up what I spend each month and creating a budget!!

I'm going to suggest adding another field of 'date' so that you can track your spends across the months as you go.

@Livingmagicallyagain one important thing with pivot tables is to update them as well. So if you add more info to your spread sheet the pivot will not automatically update. You need to click on the table to update

You can change the settings so that pivots refresh automatically when you open the file

support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2

Oh great, thanks a mill for letting me know 😁