Forum Discussion
Subtotals in a table
Hello friends.
I have a workbook with one page for each account; like groceries, rent etc.
The data is already sorted on transaction date; like 1/14/2021
I want to present the data with a break with subtotal on amount when the month changes.
I have tried to make this work by following the help in Excel; but have not been able to make it work.
I would appreciate it if someone could "draw" me a picture of how to make this happen; sometimes I just can't see the "forest for the trees" .
Thanks
FastEddyG2
Hello Hans.
I understand that. I believe I had converted it; but I will double check.
Thanks,
FastEddyG2
6 Replies
Add a column that returns the month.
For example, let's say the transaction dates are in B2 and down. Insert a blank column in column C.
Enter Month in C1.
Enter the formula =B2-DAY(B2)+1 in C2 and fill down.
If you wish you can format C2 and down as mmm yyyy so that it displays only the month and year:
Select any cell in the data.
Click Subtotal in the Outline group of the Data group of the ribbon.
Select Month from the 'At each change in' drop-down.
Under 'Add subtotals to' tick the check box for the amount column:
Click OK.
- Hello Hans.
Thanks for the help. I don't know what I am doing wrong. I followed your very clear "picture" and got a bit further. However, after I "..select any cell.." the subtotal button under outline is grayed out.
Any idea why?
Thanks
FastEddyG2The subject of your question says "Subtotals in a table".
If your data are in a table (created by selecting Insert > Table), you cannot insert subtotals. Tables don't allow that. So you'd have to convert the table to a normal range. You should then be able to create subtotals.
