Tracking Expenses

[Karen615]

I created a spreadsheet to track all the groceries that I buy at work for our employee cafe. (see attached) I need to be able to pull up the following information when needed: How much is spent each month and each year.

You can see from the attached spreadsheet; I used the SUBTOTAL function. I'm thinking that this might not be the best way to set this up because I'll keep adding groceries to the list and by using SUBTOTAL, I will have to keep applying the SUBTOTAL each week. Someone who is not proficient with Excel will be using this and I don't want to make this very complicated for her. What is the best way to set this up? HELP!

Your help is greatly appreciated.

TIA,


Karen

[Alansidman]

Karen
With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Multiplication" = Table.AddColumn(Source, "Multiplication", each [Quantity] * [Price], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Multiplication",{{"Date", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Month Name", "Year"}, {{"Totals", each List.Sum([Multiplication]), type number}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
The Complete Guide to Installing Power Query - Excel Campus

- Follow this link for an introduction to Power Query functionality.
Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

- Follow this link for a video which demonstrates how to use Power Query code provided.
Power Query - How To Paste Code (video) - Excel Solutions

[Karen615]

Hi Alan,
Thank you so much for this recommendation. I'm not sure if my employer allows employees to use this, but I will look into it. I also want to learn about Power Query outside of my job, so all of this info with be very helpful.
Thank you again, Karen

[p45cal]

A pivot table can do this, see table at cell H5 of the Grocery List-Sub Totals sheet. (The data in that list does not need to be in any special order.)
2024-09-27_124404.jpg


Select any date in the Date field, click on Collapse Field and you have your spend summary for each day/month/year:
2024-09-27_124716.jpg


Do the same with a month field and you have your month/year summary:
2024-09-27_125201.jpg


If the source data on sheet Original Grocery List changes the pivot table will need updating; either right-click somewhere in the pivot table and choose Refresh
or
click on the Refresh icon in the tool bar (it's just next to the 1 icon in the 2nd picture above) while a cell within the pivot is selected.


ps. I've just noticed that I got the 1 and 2 icons in the wrong places on the 2nd picture, they should be swapped (you wont see the PivotTable Analyze toolbar unless you're in a Pivot first).

[Karen615]

Thank you so much for your help and recommendations. It's very kind of you.

I added the date entry (9/2/24), but not sure what I did wrong. It's not in ascending date order on Original Grocery List tab and I inserted rows above 9/9/24.

On the Original Grocery List tab, is it wise that I added borders all the way to row 20,000? Only problem, if the user prints this, it will print 477 pages. Is there a better way to do this?

Should I delete the sub totals tab? I don't need that now that I have the pivot table, right?

I also copied the pivot table to a new tab.

Thank you again for all your help,
Karen

[p45cal]

Originally Posted by Karen615

I added the date entry (9/2/24), but not sure what I did wrong. It's not in ascending date order on Original Grocery List tab and I inserted rows above 9/9/24.

Not sure. Could you attach the file where this has happened?
Why add at the top? Add to the bottom then sort. No need to insert.

Originally Posted by Karen615

On the Original Grocery List tab, is it wise that I added borders all the way to row 20,000? Only problem, if the user prints this, it will print 477 pages. Is there a better way to do this?

You could set the print region manually. Select what you want to print, go to the Page Layout tab of the ribbon and click the Print Area icon and choose Set Print Area. Then print.
Alternatively, select the area you want to print, go straight to the Print Dialogue and choose Print Selection:
2024-09-29_224022.jpg

Originally Posted by Karen615

Should I delete the sub totals tab? I don't need that now that I have the pivot table, right?

Correct.

Originally Posted by Karen615

I also copied the pivot table to a new tab.

Perfect!

Regarding Pecoflyer's suggestion of making the Original Grocery List a proper Excel table; I too considered that (it's very useful) but decided against advising that because you said that the user was not proficient at Excel. If the user tried adding a new entry not directly beneath the existing table (leaving a blank row inbetween, say to separate different day's purchases) then that row wouldn't get added to the table (the table wouldn't resize to include that new data). This would mean the pivot table source data wouldn't expand to include the new data. I didn't think you'd want to start training the user about adjusting a table's range.

What I did was to set the pivot table's source range to extend as far as you had added data validation to the cells, which was some 10,000 rows. I filtered out the blank rows in the Items field in the pivot (you could do that on the Date field instead). It'll take some time to fill 10,000 rows of purchases, let alone 20,000!

[ArviLaanemets]

Originally Posted by p45cal

You could set the print region manually.

Or you can define a dynamic named range, and set it as print region.

[p45cal]

Originally Posted by Karen615

I added the date entry (9/2/24), but not sure what I did wrong.

I added a date below the bottom of your table and it switched the month/day values around. I don't know why! However, it stopped doing that when I formatted the whole table's Date column to the format I wanted.
How to do that (this is for you to do, not the user):
Hover the mouse over the Date label header and when it turns into a black down arrow left-click.

2024-10-03_202151.jpg

This will select the whole table's Date column, then go into Format cells… and choose your number format.

2024-10-03_202724.jpg

Originally Posted by Karen615

On the Original Grocery List tab, is it wise that I added borders all the way to row 20,000? Only problem, if the user prints this, it will print 477 pages. Is there a better way to do this?

Now that you've opted to use proper Excel tables you can do this by adding a border around the table. See below.

Originally Posted by Karen615

A few questions:
On the Grocery-Supplies Log tab:
• I sorted a column and now my banded rows are not consistent. How do I set up the table so I ensure that the banded rows stay consistent after sorting any column?

Select a cell in the table, then goto the Table Design tab of the ribbon and untick the banded rows checkbox:
2024-10-03_203035.jpg

However, you can kill two birds with one stone by removing the banding and putting a border around the whole table in one operation by setting up your own table design, roughly as follows:
Select a cell in the table, in the Table Design tab of the ribbon, in the Table Styles section, then choose the one to the left of the current selection.


2024-10-03_203733.jpg

This alone gets rid of the banding but now we're going to make a new table style with a border around that you'll be able to use on other tables:
Click on the drop down:
2024-10-03_204524.jpg
and choose New Table Style…
can't add more pictures, see next message.

[Pecoflyer]

On the Original Grocery List tab, is it wise that I added borders all the way to row 20,000

Nope. better make your existing range an Excel Table (using PQ this will already have been done) and use it as source for your Pivot Table.

Each time you add data, just refresh your PT, and it will adapt

I added the date entry (9/2/24), but not sure what I did wrong. It's not in ascending date order on Original Grocery List tab and I inserted rows above 9/9/24.

This should have been taken care of with the above

Should I delete the sub totals tab? I don't need that now that I have the pivot table, right?

Yes

[Pecoflyer]

If the user tried adding a new entry not directly beneath the existing table (leaving a blank row inbetween, say to separate different day's purchases) then that row wouldn't get added to the table (the table wouldn't resize to include that new data).

One could use Data validation to warn about this

[Karen615]

Thank you all for your help.

The most current spreadsheet is attached: 2024.10.02 Supplies List

A few questions:
On the Grocery-Supplies Log tab:
• So, I did convert the range to a table
• I sorted a column and now my banded rows are not consistent. How do I set up the table so I ensure that the banded rows stay consistent after sorting any column?
• My drop-down in column D is not extending when I make a new entry. It's picking up the “StoresRestaurants” named range. Not sure how to fix this.

On the Grocery Report tab
My dates will not sort in properly.

Quote:
I added the date entry (9/2/24), but not sure what I did wrong. It's not in ascending date order on Original Grocery List tab and I inserted rows above 9/9/24.
Somehow this has been corrected. Now sure how.

Quote:
Originally Posted by p45cal
You could set the print region manually.
Or you can define a dynamic named range, and set it as print region.
Thank you, I have to make this very simple to use since the user that will be doing the data entry, it not very skilled with Excel.

Why add at the top? Add to the bottom then sort. No need to insert.
Thank you! I agree that this is the best way.

You could set the print region manually. Select what you want to print, go to the Page Layout tab of the ribbon and click the Print Area icon and choose Set Print Area. Then print.
Alternatively, select the area you want to print, go straight to the Print Dialogue and choose Print Selection:
Thank you, this may be too complicated for the user.

Any help would be greatly appreciated.

Thank you,
Karen

[p45cal]

…continued
So select New Table Style…
2024-10-03_204825.jpg
Then in the next dialogue box:
Select Whole table, and click Format:

2024-10-03_205524.jpg

then in the next dialogue chose the Border tab, select a line style for your border, click on the Outline icon and click OK, then OK again.

2024-10-03_205926.jpg

you now have a new custom table style you can click to select:
2024-10-03_210421.jpg

The border will always adjust to the table's size.

Originally Posted by Karen615

• My drop-down in column D is not extending when I make a new entry. It's picking up the “StoresRestaurants” named range. Not sure how to fix this.

If, when adding a new entry to the Grocery-Supplies Log table (it's called Table4 in your file) below the bottom of that table, the first thing you add should be in a column which does NOT have data validation. As soon as you do that, a new row gets added to the table and all the data validations are added to all the other columns in that new row . This means you don't need Data Validation in any cells below the extents of the table; in fact this is a waste of resources and I'd recommend you delete everything below that table; Formatting/Data Validation, the lot.

As far as setting a print range for printing out this data-entry table Table4, (ArviLaanamet's suggestion) the fact that it's now a table means that there already exists a dynamic range you can use to set the print area; it's called Table4[#All] and you can add it as the print area:

2024-10-03_211834.jpg

(The [#All] bit is just to include the headers)


I wish I could say the same about pivot table print areas; these are not so easy to set automatic print areas for.

[Karen615]

Thank you so much for all your help. I really appreciate your time and knowledge.
You are too kind as everyone is on this thread who has helped me.

I made all the changes you suggested. If banded rows will always cause an issue when sorting, then of course I would rather do without. It is easier to read across with banded rows. Is it possible to add them and be able to sort? If it works, how can I have the lightest gray fill.

Is there anything else you would recommend for my spreadsheet that would enhance it?

Thank you again EVERYONE!

Best,
Karen

[p45cal]

Originally Posted by Karen615

It is easier to read across with banded rows. Is it possible to add them and be able to sort? If it works, how can I have the lightest gray fill.

If the manual colouring you added was only for readability (I thought it was to highlight certain rows for your own reasons) then remove all your manual banding, add banding (right-click the style and choose Modify…) to your own table style (it's the Second Row Stripe) and choose your own colour for the fill and tick the Banded Rows checkbox on the ribbon. I did this in the attached. Sorting won't affect the banding.

I also, for demonstration, removed the freeze panes on the Grocery-Supplies Log sheet to show, when the selection is somewhere in the table, that the headers remain visible showing in the column headers instead of A, B, C etc. You may want to reinstate the freeze panes to keep your slicer visible.

I've also added a dynamic named range called GrocerySuppliesPrintArea (it's 4 columns wider than the table itself) and set the sheet's print area to that in its Page Layout. It doesn't seem to 'stick' sometimes though.

I thought, now that you have the pivot table, you'd be using that for your print-outs rather than your original log.

If you were to allow the file to be macro enabled (extension .xlsm or .xlsb) we could maintain some/all of these print areas more easily and automatically without user interventoin.

[Karen615]

Thank you so much for all your help.

I have a few more questions:

In both report tabs, is it possible to format the 3-character state to automatically spelling it out?

Also, is it possible to format each total row to automatically fill with a light gray color?

Not sure what happened, but I refreshed my grocery report and it's not populating with all my data.

Your help is greatly appreciated.

Thank you,
Karen.