How to get the expense totals by month in Excel, using a pivot table with slicers or a formula.
Transcript of the video:
Hey, welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 163.
Expense Totals By Month.
Alright, so today's question, sent in from YouTube: we have this data down here: date, expense and amount.
He wants to know, if there's a formula: when you type in the end date for a month, it triggers the total expense for that particular month.
I assume, that we want totals for all of these.
Well, formula… now, sure, yeah, Mike will be able to do a formula.
But this is just… It's screaming… “It’s Pivot, Pivot Table time!” That’s right, Pivot Table, it’s screaming PivotTable.
So first I'm going to take this data, I want to make it into a real table, Ctrl+T, my table has headers, yep.
That way, when I add more data later, it will continue to update.
INSERT, PivotTable, I'm going to go to a new worksheet, click OK.
And down the left hand side, I'm going to click Expense and then Amount.
And across the top, watch this: temporarily I'm going to put the Date across the top, I'm going to choose the very first date field, come here to either the ANALYZE tab in Excel 2013, or the OPTIONS tab in Excel 2010 or 2007.
GroupField and I'm going to group that up to months and years, like that, OK.
And then I'm actually going to take those two fields and remove them from the database, so that I just have one single amount.
Then: INSERT, Slicer, I'm going to choose both, the Date field and the Years field, click OK.
They're going to go right in the middle of the screen, but I'm going to rearrange them, like this.
Let's see, those Years are nice and small, but change it to three columns, bam.
And then Date, which is actually a month, we'll probably go three columns as well.
Change it up like this and change the colors, all right.
So now, if we want to see total expense for March of 2014, there's our answer.
April of 2014, January through March.
Very, very versatile way to see these totals, without creating a single formula, using a Pivot Table.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Oh, the Pivot Table, the Table feature and probably one of the more beautiful aspects of Excel: Slicers to pick your criteria for your calculation.
Oh man, and I'm stuck doing formulas, I can't use those Slicers.
All right, let's go over to this sheet here.
We'll build the formula in this cell.
Hey, but we have our list of expenses right in the sheet, so why don't we add a Data Validation drop-down list?
Because our formulas are going to have to look at criteria for Expense and the end of the month.
All right, so we're clicking in a Cell, Alt, D, L, Tab, L to open up Data Validation dialog box, Tab to get to Allow, L to get to List, Tab to get to Source and boom, I simply highlight, notice it says “in-cell drop-down”, click OK.
That is pretty cool.
Now, the end of the date is going to be a criteria, so I'll put 8/31/2014.
Now I'm actually going to put the… whatever End Date in this cell, but I'm going to build my formula a little bit more robust.
So any data, if I put 8/25/2014, the formula will still know to get all of the Operations Expense for the 8th month in 2014.
Hey, we'll use =SUMIFS.
SUMIFS is great, it can add with one or more criteria, sum_range (C12), Ctrl+Shift+Down arrow, Ctrl+Backspace, we don't need to lock it, because we're not copying it anywhere, comma.
We're going to have criteria_range1, criteria_range2 and 3, so we'll start with Expense (B12), Ctrl+Shift+Down arrow, Ctrl+Backspace.
The criteria, to go with that B range, is going to be: Operations for this particular selection, comma.
criteria_range2: we'll have to actually use the Date range twice, Ctrl+Shift+Down arrow, Ctrl+Backspace, comma.
Now, criteria2 and 3: we're going to take this Date, but I need to get the first of the month from that particular Date.
Not only that, but I have to look at this column and say: are any of the Dates greater than or equal to this, which will be the first of the month.
So, here in criteria, we’ll build our comparative operator, in double quotes: greater than or equal to (“>=”) and join (&) it to… Well, I need to get the first of the month from that, so I'll use end of the month (EOMONTH).
No, wait, check this out, I'll say: give me the end of the month of that, comma, and we will use the months.
If I put 0 here, it'll give me the end of this month; if I put 1, it gives me the end of next month, but if I put -1, it gives me the end of last month.
That's a serial number, so when we close this off, we add one (+1) to get to the first of whatever month is of the Date in that cell.
Comma.
And now we'll get our Date range, Ctrl+Shift+Down arrow, Ctrl+Backspace.
So that's our criteria_range3, comma, criteria3, we're going to say in double quotes: less than or equal to, and double quote, join (“<=”&). And you know what, just to be robust.
So I'm going to use end of the month of this, comma, 0 says: give me the end of the month.
Now that's ridiculous here, we're assuming that they'll always have 8/30 or whatever the end of the month is here, but just in case: someone puts 8/25 or 8/30, boom, that will get the end of the month.
Right now, this is giving me the same serial number as the serial number in the cell.
All right, let's see if this works: (SUMIFS(C12:C4676,B12:B4676,E12,A12:A4676,”>=”&EOMONTH(G12,-1)=1,A12:A4676,”<=”&EOMONTH(G12,0)).
Ctrl+Enter, Ctrl+Shift+4 to add currency, let's try it.
Let's change this to the 25th and boom, our formula is working.
If I change this to Miscellaneous, just like that, if I change this to 2/1/2015, instantly I get for Miscellaneous, February 2015 that Total Expenses.
All right, throw it back to MrExcel.
Bill: Hey Mike, that was ingenious, using EOMONTH to get back to the first of this month.
-1 takes us to the end of the last month, +1, what a brilliant way to go.
Great, great formula there.
So two different solutions, Pivot Table or formula, another great example of different ways to solve things in Excel.
I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 163.
Expense Totals By Month.
Alright, so today's question, sent in from YouTube: we have this data down here: date, expense and amount.
He wants to know, if there's a formula: when you type in the end date for a month, it triggers the total expense for that particular month.
I assume, that we want totals for all of these.
Well, formula… now, sure, yeah, Mike will be able to do a formula.
But this is just… It's screaming… “It’s Pivot, Pivot Table time!” That’s right, Pivot Table, it’s screaming PivotTable.
So first I'm going to take this data, I want to make it into a real table, Ctrl+T, my table has headers, yep.
That way, when I add more data later, it will continue to update.
INSERT, PivotTable, I'm going to go to a new worksheet, click OK.
And down the left hand side, I'm going to click Expense and then Amount.
And across the top, watch this: temporarily I'm going to put the Date across the top, I'm going to choose the very first date field, come here to either the ANALYZE tab in Excel 2013, or the OPTIONS tab in Excel 2010 or 2007.
GroupField and I'm going to group that up to months and years, like that, OK.
And then I'm actually going to take those two fields and remove them from the database, so that I just have one single amount.
Then: INSERT, Slicer, I'm going to choose both, the Date field and the Years field, click OK.
They're going to go right in the middle of the screen, but I'm going to rearrange them, like this.
Let's see, those Years are nice and small, but change it to three columns, bam.
And then Date, which is actually a month, we'll probably go three columns as well.
Change it up like this and change the colors, all right.
So now, if we want to see total expense for March of 2014, there's our answer.
April of 2014, January through March.
Very, very versatile way to see these totals, without creating a single formula, using a Pivot Table.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Oh, the Pivot Table, the Table feature and probably one of the more beautiful aspects of Excel: Slicers to pick your criteria for your calculation.
Oh man, and I'm stuck doing formulas, I can't use those Slicers.
All right, let's go over to this sheet here.
We'll build the formula in this cell.
Hey, but we have our list of expenses right in the sheet, so why don't we add a Data Validation drop-down list?
Because our formulas are going to have to look at criteria for Expense and the end of the month.
All right, so we're clicking in a Cell, Alt, D, L, Tab, L to open up Data Validation dialog box, Tab to get to Allow, L to get to List, Tab to get to Source and boom, I simply highlight, notice it says “in-cell drop-down”, click OK.
That is pretty cool.
Now, the end of the date is going to be a criteria, so I'll put 8/31/2014.
Now I'm actually going to put the… whatever End Date in this cell, but I'm going to build my formula a little bit more robust.
So any data, if I put 8/25/2014, the formula will still know to get all of the Operations Expense for the 8th month in 2014.
Hey, we'll use =SUMIFS.
SUMIFS is great, it can add with one or more criteria, sum_range (C12), Ctrl+Shift+Down arrow, Ctrl+Backspace, we don't need to lock it, because we're not copying it anywhere, comma.
We're going to have criteria_range1, criteria_range2 and 3, so we'll start with Expense (B12), Ctrl+Shift+Down arrow, Ctrl+Backspace.
The criteria, to go with that B range, is going to be: Operations for this particular selection, comma.
criteria_range2: we'll have to actually use the Date range twice, Ctrl+Shift+Down arrow, Ctrl+Backspace, comma.
Now, criteria2 and 3: we're going to take this Date, but I need to get the first of the month from that particular Date.
Not only that, but I have to look at this column and say: are any of the Dates greater than or equal to this, which will be the first of the month.
So, here in criteria, we’ll build our comparative operator, in double quotes: greater than or equal to (“>=”) and join (&) it to… Well, I need to get the first of the month from that, so I'll use end of the month (EOMONTH).
No, wait, check this out, I'll say: give me the end of the month of that, comma, and we will use the months.
If I put 0 here, it'll give me the end of this month; if I put 1, it gives me the end of next month, but if I put -1, it gives me the end of last month.
That's a serial number, so when we close this off, we add one (+1) to get to the first of whatever month is of the Date in that cell.
Comma.
And now we'll get our Date range, Ctrl+Shift+Down arrow, Ctrl+Backspace.
So that's our criteria_range3, comma, criteria3, we're going to say in double quotes: less than or equal to, and double quote, join (“<=”&). And you know what, just to be robust.
So I'm going to use end of the month of this, comma, 0 says: give me the end of the month.
Now that's ridiculous here, we're assuming that they'll always have 8/30 or whatever the end of the month is here, but just in case: someone puts 8/25 or 8/30, boom, that will get the end of the month.
Right now, this is giving me the same serial number as the serial number in the cell.
All right, let's see if this works: (SUMIFS(C12:C4676,B12:B4676,E12,A12:A4676,”>=”&EOMONTH(G12,-1)=1,A12:A4676,”<=”&EOMONTH(G12,0)).
Ctrl+Enter, Ctrl+Shift+4 to add currency, let's try it.
Let's change this to the 25th and boom, our formula is working.
If I change this to Miscellaneous, just like that, if I change this to 2/1/2015, instantly I get for Miscellaneous, February 2015 that Total Expenses.
All right, throw it back to MrExcel.
Bill: Hey Mike, that was ingenious, using EOMONTH to get back to the first of this month.
-1 takes us to the end of the last month, +1, what a brilliant way to go.
Great, great formula there.
So two different solutions, Pivot Table or formula, another great example of different ways to solve things in Excel.
I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun.
