MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Running Total in Footer


October 26, 2017 - by Bill Jelen

Running Total in Footer

Can Excel print a running total in the footer for each page? It is not built-in, but a short macro will solve the problem.


Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode 2058
  • running total at the end of each page
  • hey welcome back to MrExcel NetCast
  • I'm Bill Jelen today's question send by
  • Wiley Wiley wants to show a running
  • total of revenue and percentage of
  • category in the last row of each printed
  • page and so while he 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 till inside this
  • category and then percentage of the
  • category and so you can see we're at
  • nine point seven percent there when I go
  • to page two twenty one point one page
  • three 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 one hundred percent all
  • right and when wiley asked me about this
  • is like oh no i mean 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 GNH to
  • hide the running total and the
  • percentage of category in every single
  • row alright and then we'll 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 a
  • six is equal to 85 then take the sum of
  • this revenue so that's in f6 and the
  • previous running total out 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
  • and I will just take the the sum of the
  • value to the left of us which I could
  • have just put f6 there but here we are
  • it you know too late and then percentage
  • the category this one's going to be
  • horribly inefficient we take the revenue
  • on this row / the
  • some of all of the revenue where the
  • category is equal to a 6 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 dollar signs one two three four
  • dollar signs there no dollar signs in a
  • six and four dollar signs and they're
  • all right and we'll show this number as
  • a number maybe a thousand 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 all right but now
  • the goal here is to make sure that we
  • only see those totals when we get to the
  • page break currents right there that's a
  • automatic page break and then later on
  • when we switch from the end of a to be a
  • manual page break so this manual page
  • break here is different than an
  • automatic page break all right now
  • you'll notice up here that this file is
  • saved as an xlsx file because that's how
  • Excel once the save files xlsx is the
  • broken file type that does not allow
  • macros right where's file type in the
  • world so do not skip this step or this
  • all of your work from here on out will
  • be lost save as and we're a safe not as
  • an Excel workbook but as a macro enabled
  • workbook or as a binary workbook or as
  • an xls I'm gonna 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
  • never run macros before we're going to
  • right click and say customize the ribbon
  • over here on 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 macros
  • security by default it's going to be up
  • here disable all macros and don't tell
  • me that you've disabled a whole macros
  • you want to switch down to the second
  • one that way when we open the file say
  • hey there's macros here did you create
  • these are you okay with this and you can
  • say enable the macros all right click ok
  • now we're going to switch over to the
  • visual basic editor if you've never used
  • a visual basic before you 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 open half of my personal
  • macro workbook and here's the workbook
  • that I'm working on make sure that this
  • workbook is selected to insert module
  • insert module get a nice big blank white
  • canvas here alright and then you're
  • going to type in this code right now
  • we're using an object here called H page
  • break a horizontal page break and
  • because i don't use these a lot i had to
  • declare it up here as a variable as an
  • object HPV that way I would be able to
  • see the choices that are available to be
  • in each one all right 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 a 1048 576 this is an L here
  • and not a one this is an L everyone
  • screws that up l is an XL it sounds like
  • Excel get it Excel up so go to a 10 for
  • a 576 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 right i don't know how many
  • columns you have maybe your new columns
  • are over an 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 colon h and then
  • I'm concatenated 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 learn this
  • next one from the mr excel message board
  • if you don't put the active window in
  • page break preview mode before you run
  • this code this cope will not work it
  • works for some of the page breaks but
  • not all of the page breaks so you have
  • to temporarily despite display the page
  • breaks and then a loop here for each
  • this is my object variable at HPB and
  • active sheet each page breaks figure out
  • the last row right 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 one from that
  • alright and then here I admit this is
  • incredibly cheap go out to column 7
  • which is column G changes the number
  • format to be currency just of that row
  • and then go out to column eight 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 was 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 all right so we've created a
  • button I'm just going to say reset page
  • breaks totals our center that's on the
  • Home tab Center vertically centered
  • horizontally increase the height and
  • then right click assign macro and say
  • that we're adding it to find all page
  • breaks to click OK all right 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 still there but it's hidden them
  • except for on the rose that is the last
  • page break all right now I just want you
  • to notice here that we're in a 46 and a
  • 93 page breaks are funny things if you
  • change the margins around a little bit
  • if you change the header 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 and what
  • rose 124 to appear at the top of each
  • page which means that we're going to
  • have less rose and so then again now my
  • toes 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 in
  • percentage of category at the bottom of
  • each printed page there's nothing in the
  • Excel user interface they can let it
  • form of the know that you're at the
  • bottom of the printed page you can see
  • the page breaks put the formulas can
  • soon so one possible solution and I'm
  • welcome and welcoming others in the
  • YouTube comments I use a macro so add
  • the running total percentage of category
  • for each row hide all those rows here's
  • the two formulas that we use save 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
  • 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

Download File

Download the sample file here: Podcast2058.xlsm

Title Photo: MabelAmber / Pixabay