I see this a lot in Numbers.
People will have separate tables, in fact entire separate sheets, for each month of
the year when keeping track of things like expenses.
This is handy when you need to produce a monthly report, maybe PDF or printout for each month.
But it's not so great when you need to relate these items across months like totals for
the entire year or, you know, tracking expenses over a few months by item type.
That kind of thing.
Also if you need to update these tables in someway you have to go and do it for each
month.
If you have three or four years worth it takes a long time to do that.
A better way to do it is to have a spreadsheet like this where everything is in one long
table.
You can keep adding to this.
This can go and be thousands and thousands of rows long after several years.
But the problem is what happens when you need to produce a monthly report.
How can you do that?
So you've got, here at the top, month and year.
I want to just do February of 2017 but I see everything from when I started keeping records
all the way to the present day.
How do I just get this to show February.
Well you could do filters.
So you could go in here, click on the Action button for column A, and then Filter Table,
and select every item that is in that specific month.
That takes a long time to select them all and then deselect them and it's not the proper
way to do it.
A better way is to create a new column.
So I'm going to create another column here.
So Add column before B and here I'm going to put a formula.
This formula is going to compare two things.
So I'm going to do And, to compare more than one thing, and the first thing I'm going to
compare is the year from this cell, whether it's equal to this number.
I'm going to set it so that it preserves own column.
So that it always goes from this specific cell even when I get further down in here.
Then the second thing I want to compare is whether the month here is equal to this.
But the month will actually return a number, 1 to 12.
In fact when I type February in here it interprets that as a date.
So this is actually interpreted as February 1st of the current year.
So I want to just basically extract the month from that as well.
The month from this cell and I'm going to also preserve row, preserve column and I'll
close out the And statement there and I will get False because that's in January, not February.
If I copy this and then paste it all the way down here you can see I only get True for
the month of February and False for everything else.
So now I can filter.
I can say Filter Table only on Trues.
You can see I only get February.
The great thing about this is if I change this to January then the Trues only are for
January and this changes there automatically.
In addition, I can get rid of this column here by hiding it.
Now I don't see it anymore.
So now it's like magic.
I just type March in here and it only shows me March 2017 dates that match over here.
Now to get the sum to work here at the bottom, this sum here is just a formula.
It's the sum of everything in this column whether it's filtered or not.
I want to use sum if.
So I will do sumif and I will say if column B, even though it's hidden I can still type
B, is True then include column E in the Cost.
Now I only get the visible ones because this formula is basically looking for true and
this filter that I had on column B is also looking for true.
They're two different things but they are both looking for true values in that B column
that is now hidden.
So when I change this to February it's now only showing me February and the sumif is
now only acting on those same ones.
So now it works.
I can change it for any month and any year that I want and it only shows me those items
that are there.
If I want to enter new data I have to turn filtering off.
So I go over to the right here to Sort and Filter to Filter.
I've got to select the table and then I just uncheck Filters.
That means the filter in column B is not deleted.
It's still there.
If I hit the Trashcan it will delete it and I have to recreate it every time.
But it just turns it off and turning it off means now that I can enter in new data, so
I can enter in something else for April, and now I can turn Filters back on again and change
this to April to see that.
So now I have a fully functioning spreadsheet that's one sheet with just this simple little
table here at the top to tell me the month and year.
Then this big table here which I can just keep growing and I can keep doing all sorts
of things.
I can add another Footer column here.
So I can extend this out.
Let me turn Filters off and let me extend this to show another Footer column there at
the bottom.
I want to move that up there.
This I'm going to do an actual sum of E. So now I've got that's the monthly total and
this is the complete total and I can do more work to actually make the total for the year.
That kind of thing.
But you get the basic idea for how to actually get a Table like this to fit your needs if
you need to keep track of something over the period of many months and many years but you
only need to show in a report what goes on during a given month.