Goal: Print category running total and % of Category at bottom of each printed page
Problem: nothing in the Excel user interface can let a formula know you are at the bottom of a printed page
Yes, you can "see" the page breaks, but formulas can not see them
Possible solution: Use a macro
Strategy: Add the running total and % of category for each row. Hide on all rows.
Running Total for Category Formula: =IF(A6=A5,SUM(F6,G5),SUM(F6))
% of Category Formula: =G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844)
If your workbook is saved as XLSX, do a Save As to save as XLSM
If you've never used macros, change macro security
If you've never used macros, show Developer tab
Switch to VBA
Insert a module
Type the code
Assign that macro to a shape
As the page size changes, run the reset macro
Problem: nothing in the Excel user interface can let a formula know you are at the bottom of a printed page
Yes, you can "see" the page breaks, but formulas can not see them
Possible solution: Use a macro
Strategy: Add the running total and % of category for each row. Hide on all rows.
Running Total for Category Formula: =IF(A6=A5,SUM(F6,G5),SUM(F6))
% of Category Formula: =G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844)
If your workbook is saved as XLSX, do a Save As to save as XLSM
If you've never used macros, change macro security
If you've never used macros, show Developer tab
Switch to VBA
Insert a module
Type the code
Assign that macro to a shape
As the page size changes, run the reset macro
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2058: Running Total at the End of Each Page Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Wiley: Wiley wants to show a running total of revenue and percentage of category in the last row of each printed page.
So, Wiley has printed reports here with tons and tons of records, multiple pages for each category over there in Column A. And when we get down to the end of the print page, Wiley is looking for a total here that shows total revenue, running total inside this category and then percentage of the category.
And so, you can see we're at 9.7% there, when I go to page 2 - 21.1, page 3 - 33.3 and so on.
And at the page break where we get done with category A, grand total for the category and the grand total 100%.
Alright, and when Wiley asked me about this, I was like, “Oh no, we don't- there's no way in the footer to put a running total.” Alright, so this is admittedly a horrible cheap cheat and I encourage anyone who's watching this on YouTube, if you have a better way, please by all means, mention that in the comments, alright?
And so, my idea is just out there in Columns G and H, to hide the running total and the percentage of category in every single row.
Alright, and then we use a macro to detect if we're at the end of the page.
Alright, so the two formulas that we want here say, hey, if this category is equal to the previous category.
So if A6=A5 then take the SUM of this revenue, so that’s in F6 and the previous running total up there in G5.
Now, because I'm using the SUM function here, this does not error out if we would ever try and add running total.
Otherwise, we're just going to be at a brand new category so when we switch from A to B, we’ll just take the SUM of the value to the left of us, which I could have just put F6 there.
But here we are, you know, too late.
And then percentage of category, this one's going to be horribly inefficient.
We take the revenue on this row divided by the SUM of all of the revenue where the category is equal to A6.
So these are all the categories, this is the category in this row and then add up the corresponding cell from all rows.
Of course, $ signs – 1, 2, 3, 4 $ signs there.
No $ signs in A6 and 4 $ signs in there.
Alright, and we'll show this number as a Number, maybe 1000 separator, click OK, and then here as a percentage with one decimal place like that.
Alright, and we'll copy this formula down to all cells.
BAM, like that, alright.
But now the goal here is to make sure that we only see those totals when we get to the page break.
Alright, it’s right there.
That's an automatic page break and then later on when we switch from the end of A to B, a manual page break.
So, this manual page break here is different than an automatic page break.
Alright, now you'll notice up here that this file is saved as an XLSX file because that's how Excel wants to save files.
XLSX is the broken file type that does not allow macros, right?
Worst file type in the world.
So, do not skip this step or this.
All of your work from here and out will be lost.
Save As, and we're going to save not as an Excel workbook but as a Macro-Enabled Workbook or as a Binary Workbook or as an XLS.
I'm going to go with Macro-Enabled Workbook.
If you don't do that step, you are about to lose the rest of the work that you do.
Alright, and then, if you’ve never run macros before, we're going to right-click and say Customize the Ribbon.
Over here in the right-hand side, choose the box for Developer, that will get you a Developer tab.
Once you have the Developer tab, we can go to Macro Security, by default it's going to be up here Disable all macros, and don't tell me that you've disabled the whole macros.
You want to switch down to the second one, that way when we open the file, we’ll say, “Hey, there's macros here.
Did you create these?
Are you okay with this?” And you can say, Enable the macros.
Alright, click OK.
Now, we're going to switch over to the visual basic editor.
If you've never used a visual basic before, you’re going to start with this completely gray screen, go to View and the Project Explorer.
Here's a list of all the open workbooks.
So I have the Solver Add-in, my personal macro workbook and here's the workbook that I'm working on.
Make sure that this workbook is selected, do Insert, Module.
Insert, Module will get a nice big blank, white canvas here.
Alright, and then you're going to type in this code.
Alright now, we're using an object here called HPageBreak, a horizontal page break.
And because I don't use this a lot, I had to declare it up here as a variable, as an object HPB, that way I would be able to see the choices that are available to me in each one.
Alright, figure out where the last row with data is today so I'm using Column A, I'm going to the end of Column A - A1048576.
This is an L here and not a 1, this is an L. Everyone screws that up.
L as in Excel.
It sounds like Excel.
Get it?
Excel up.
So, go to A1048576, press the End key, and the Up Arrow key to get to the last row.
Figure out what row that is.
And then in Columns G and H, and if you're watching this, you need to take a look at your Excel data and figure out where your two new columns are, alright.
I don't know how many columns you have.
Maybe your new columns are over in I and J, or maybe they're in C and D. I don't know, figure out where those are and we're going to hide all of those rows, alright.
So in my case, it was starting from G6, that's the first place where we have a number, :H and then I'm concatenating the last row that we have today using a number format of three semicolons that will hide the data.
Alright, then this next one, I learned this next one from the MrExcel message board.
If you don't put the active window in Page Break Preview mode before you run this code, this code will not work.
It works for some of the page breaks but not all of the page breaks, so you have to temporarily display the page breaks.
And then a loop here: For Each, this is my object variable- HPB In ActiveSheet.HPageBreaks.
Figure out the last row, alright?
So for this object, for the page break, figure out the location, figure out the row.
And this is actually the first row of the next page so I have to subtract 1 from that, alright.
And then here, I admit this is incredibly cheap, go out to Column 7 which is Column G, change the NumberFormat to be currency, just of that row.
And then go out to Column 8 which is H and change it to a percentage and go next.
Finally, exit horizontal or a page break preview and go back into normal view.
Alright, so that's our code.
I will File, Close and Return to Microsoft Excel.
I want an easy way to run this, so I'm going to Insert, choose a nice shape here.
I will choose a rounded rectangle, draw my right- around a rectangle in, Page layout, go to Effects, choose the effects for Office 2007.
And then here on the Format tab we have a nice way to add some glow to that, alright.
So we've created a button, I'm just going to say Reset Page Breaks Totals, we’re going to center that, so on the Home tab, Center Vertically, Center Horizontally, increase the height and then right-click, Assign Macro and say that we're adding it to FindAllPageBreaksTwo, click OK.
Alright, and then you see we have all of our totals here and I reset page break totals, and now it's - The totals are still there, the formula is still there but it's hidden them except for on the rows that is the last page break.
Alright now, I just want you to notice here that we're in A46 and A93.
Page breaks are funny things.
If you change the margins around a little bit, if you change the header and footer, then the page break is going to move to a new spot.
So, we'll go to Wide margins, and the page break moved in those numbers in the wrong spot.
Also print titles on what- rows 124 to appear at the top of each page which means that we're going to have less rows, and so then again, now my totals are in completely the wrong place that's why I need that button back here to reset page break totals and you will see that now, this cell in Row 45 and this cell in Row 86, that's in a new place.
Alright, so today's question from Wiley.
We want to print category running total and % of Category at the bottom of each printed page.
There's nothing in the Excel user interface, they can let a formula know that you're at the bottom of the printed page.
Yeah, you can see the page breaks but the formulas can’t see them.
So, one possible solution, and I'm welcoming others in the YouTube comments, use a macro.
So add the running total and % of category for each row, hide all those rows.
Here's the two formulas that we used: Saved As to save the workbook as XLSM or your macros will not be allowed to run next time.
They'll actually- You'll lose your macros.
If you've never used macros: change the macro security, show the Developer tab, switch to VBA, insert a module, type the code and then assign that macro to a shape.
As the page size changes, reset the macro.
And you will have a cheap solution to what Wiley is trying to do.
Oh hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's question sent in by Wiley: Wiley wants to show a running total of revenue and percentage of category in the last row of each printed page.
So, Wiley has printed reports here with tons and tons of records, multiple pages for each category over there in Column A. And when we get down to the end of the print page, Wiley is looking for a total here that shows total revenue, running total inside this category and then percentage of the category.
And so, you can see we're at 9.7% there, when I go to page 2 - 21.1, page 3 - 33.3 and so on.
And at the page break where we get done with category A, grand total for the category and the grand total 100%.
Alright, and when Wiley asked me about this, I was like, “Oh no, we don't- there's no way in the footer to put a running total.” Alright, so this is admittedly a horrible cheap cheat and I encourage anyone who's watching this on YouTube, if you have a better way, please by all means, mention that in the comments, alright?
And so, my idea is just out there in Columns G and H, to hide the running total and the percentage of category in every single row.
Alright, and then we use a macro to detect if we're at the end of the page.
Alright, so the two formulas that we want here say, hey, if this category is equal to the previous category.
So if A6=A5 then take the SUM of this revenue, so that’s in F6 and the previous running total up there in G5.
Now, because I'm using the SUM function here, this does not error out if we would ever try and add running total.
Otherwise, we're just going to be at a brand new category so when we switch from A to B, we’ll just take the SUM of the value to the left of us, which I could have just put F6 there.
But here we are, you know, too late.
And then percentage of category, this one's going to be horribly inefficient.
We take the revenue on this row divided by the SUM of all of the revenue where the category is equal to A6.
So these are all the categories, this is the category in this row and then add up the corresponding cell from all rows.
Of course, $ signs – 1, 2, 3, 4 $ signs there.
No $ signs in A6 and 4 $ signs in there.
Alright, and we'll show this number as a Number, maybe 1000 separator, click OK, and then here as a percentage with one decimal place like that.
Alright, and we'll copy this formula down to all cells.
BAM, like that, alright.
But now the goal here is to make sure that we only see those totals when we get to the page break.
Alright, it’s right there.
That's an automatic page break and then later on when we switch from the end of A to B, a manual page break.
So, this manual page break here is different than an automatic page break.
Alright, now you'll notice up here that this file is saved as an XLSX file because that's how Excel wants to save files.
XLSX is the broken file type that does not allow macros, right?
Worst file type in the world.
So, do not skip this step or this.
All of your work from here and out will be lost.
Save As, and we're going to save not as an Excel workbook but as a Macro-Enabled Workbook or as a Binary Workbook or as an XLS.
I'm going to go with Macro-Enabled Workbook.
If you don't do that step, you are about to lose the rest of the work that you do.
Alright, and then, if you’ve never run macros before, we're going to right-click and say Customize the Ribbon.
Over here in the right-hand side, choose the box for Developer, that will get you a Developer tab.
Once you have the Developer tab, we can go to Macro Security, by default it's going to be up here Disable all macros, and don't tell me that you've disabled the whole macros.
You want to switch down to the second one, that way when we open the file, we’ll say, “Hey, there's macros here.
Did you create these?
Are you okay with this?” And you can say, Enable the macros.
Alright, click OK.
Now, we're going to switch over to the visual basic editor.
If you've never used a visual basic before, you’re going to start with this completely gray screen, go to View and the Project Explorer.
Here's a list of all the open workbooks.
So I have the Solver Add-in, my personal macro workbook and here's the workbook that I'm working on.
Make sure that this workbook is selected, do Insert, Module.
Insert, Module will get a nice big blank, white canvas here.
Alright, and then you're going to type in this code.
Alright now, we're using an object here called HPageBreak, a horizontal page break.
And because I don't use this a lot, I had to declare it up here as a variable, as an object HPB, that way I would be able to see the choices that are available to me in each one.
Alright, figure out where the last row with data is today so I'm using Column A, I'm going to the end of Column A - A1048576.
This is an L here and not a 1, this is an L. Everyone screws that up.
L as in Excel.
It sounds like Excel.
Get it?
Excel up.
So, go to A1048576, press the End key, and the Up Arrow key to get to the last row.
Figure out what row that is.
And then in Columns G and H, and if you're watching this, you need to take a look at your Excel data and figure out where your two new columns are, alright.
I don't know how many columns you have.
Maybe your new columns are over in I and J, or maybe they're in C and D. I don't know, figure out where those are and we're going to hide all of those rows, alright.
So in my case, it was starting from G6, that's the first place where we have a number, :H and then I'm concatenating the last row that we have today using a number format of three semicolons that will hide the data.
Alright, then this next one, I learned this next one from the MrExcel message board.
If you don't put the active window in Page Break Preview mode before you run this code, this code will not work.
It works for some of the page breaks but not all of the page breaks, so you have to temporarily display the page breaks.
And then a loop here: For Each, this is my object variable- HPB In ActiveSheet.HPageBreaks.
Figure out the last row, alright?
So for this object, for the page break, figure out the location, figure out the row.
And this is actually the first row of the next page so I have to subtract 1 from that, alright.
And then here, I admit this is incredibly cheap, go out to Column 7 which is Column G, change the NumberFormat to be currency, just of that row.
And then go out to Column 8 which is H and change it to a percentage and go next.
Finally, exit horizontal or a page break preview and go back into normal view.
Alright, so that's our code.
I will File, Close and Return to Microsoft Excel.
I want an easy way to run this, so I'm going to Insert, choose a nice shape here.
I will choose a rounded rectangle, draw my right- around a rectangle in, Page layout, go to Effects, choose the effects for Office 2007.
And then here on the Format tab we have a nice way to add some glow to that, alright.
So we've created a button, I'm just going to say Reset Page Breaks Totals, we’re going to center that, so on the Home tab, Center Vertically, Center Horizontally, increase the height and then right-click, Assign Macro and say that we're adding it to FindAllPageBreaksTwo, click OK.
Alright, and then you see we have all of our totals here and I reset page break totals, and now it's - The totals are still there, the formula is still there but it's hidden them except for on the rows that is the last page break.
Alright now, I just want you to notice here that we're in A46 and A93.
Page breaks are funny things.
If you change the margins around a little bit, if you change the header and footer, then the page break is going to move to a new spot.
So, we'll go to Wide margins, and the page break moved in those numbers in the wrong spot.
Also print titles on what- rows 124 to appear at the top of each page which means that we're going to have less rows, and so then again, now my totals are in completely the wrong place that's why I need that button back here to reset page break totals and you will see that now, this cell in Row 45 and this cell in Row 86, that's in a new place.
Alright, so today's question from Wiley.
We want to print category running total and % of Category at the bottom of each printed page.
There's nothing in the Excel user interface, they can let a formula know that you're at the bottom of the printed page.
Yeah, you can see the page breaks but the formulas can’t see them.
So, one possible solution, and I'm welcoming others in the YouTube comments, use a macro.
So add the running total and % of category for each row, hide all those rows.
Here's the two formulas that we used: Saved As to save the workbook as XLSM or your macros will not be allowed to run next time.
They'll actually- You'll lose your macros.
If you've never used macros: change the macro security, show the Developer tab, switch to VBA, insert a module, type the code and then assign that macro to a shape.
As the page size changes, reset the macro.
And you will have a cheap solution to what Wiley is trying to do.
Oh hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
