Excel - Subtotal in Footer Part 1 - Episode 1668

Microsoft Excel Tutorial: Subtotal in Footer Part 1 - Episode 1668. Welcome to the MrExcel podcast. In this episode, we will be discussing how to add a subtotal in the footer of each page in Excel. This question was sent in by MRAM during the St. Patrick's Day weekend. MRAM wanted to know if it was possible to put a subtotal of the totals so far in the footer of each page. At first, it seemed like an impossible task, but after some experimentation, I have found two different ways to achieve this. In today's episode, we will be focusing on the formula-based solution. The first step is to set up your page layout and print titles to ensure that they will not change. Once that is done, we can start building the formula. We will be using the ROW function to get the row number of the current row. Then, we will use the MOD function to find the remainder when dividing the row number by 40. This will help us identify the last row on each page. We will then use the IF function to check if the remainder is equal to 5, indicating that we are on the last row of the page. If it is, we will use the SUM function to calculate the total from the first row to the current row. This will give us the subtotal in the footer of each page. But what if we only want the total for the current page? In that case, we will use the OFFSET function to create a variable range that grabs the last 40 rows. This will give us the total for the current page, even if rows are inserted or deleted. This formula-based solution is a great way to add subtotals in the footer of each page in Excel. In tomorrow's episode, we will explore a VBA solution for this problem. Thank you for tuning in to the MrExcel podcast, and a special thanks to MRAM for sending in this question. See you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-... Table of Contents: (00:00) Subtotal in Footer (00:15) Two Ways to Get Subtotal in Footer (00:38) Frustration with Inserting and Deleting Rows (00:48) Setting Up Page Layout (01:00) Using Print Preview (01:30) Building Formula Step by Step (03:42) Adding Heading for Total All Pages So Far (04:16) Total of Current Page Only (04:52) Test with Inserting Rows (05:25) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Delete new rows Formula way Insert new rows Learn Excel MOD function Page layout Print preview Print Title Subtotal in Footer VBA macro way Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads... MRAM asks if there is a way to Total All Pages so far...at the bottom of each Printed Page. MRAM is struggling with the situation where someone Inserts or Deletes Rows and then the Subtotal needs to move to a new location. In Episode #1668 today, Bill shows us a Formula-based solution [Tomorrow, Bill will show us a VBA Macro solution]. ...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20... "The Learn Excel from MrExcel Podcast Series" MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!