Look Who it Was Random video

Excel - Master Subtotal Formulas in Excel: Copying Data to Subtotal Rows - Episode 658

859 views · Published 19 March 2009 · 2:42 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Adding formulas to the Subtotal Rows in Excel.

Welcome back to another episode of the MrExcel netcast! In this video, we will be discussing how to use subtotal row formulas in Excel. This is a question sent in by Debbie from Virginia, who attended one of my power excel seminars. She had a slightly different problem with subtotals and we will be addressing that today.

To add subtotals by account, we will go to the "Data" tab and select "Subtotals". Then, we will choose "Account" as the change in field and add the SUM function to "Profit", "COGS", "Revenue", and "Quantity". This will quickly add subtotals to our data. As I always demonstrate in my seminars, we also have the option to use the group and outline buttons to view one line per customer.

However, in this case, we have account numbers instead of customer names and we want to copy the data down to all of the subtotal rows from the row above. To do this, we will select all of the cells from the first subtotal on row 6 to the last subtotal on row 591. Then, we will use the shortcut Alt + ; to select only the visible cells. This will ensure that our formula is only entered in the subtotal rows.

Next, we will enter the formula =E5, which will give us the value from the cell above. To quickly enter this formula in all of the selected cells, we will use the shortcut Ctrl + Enter. It is important to remember to use Alt + ; before entering the formula to avoid filling in all of the cells and causing a disaster. This simple trick will save you time and effort when working with subtotals.

Thank you to Debbie for sending in this question and thank you for watching. Don't forget to send in your questions for a chance to be featured in a future netcast. See you next time on the MrExcel netcast!"

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-on-youtube/


Debbie from Virginia asks about adding new calculations to the subtotal lines in a data set with subtotals. Episode 658 shows how to do this.

Table of Contents
(00:00) Adding Customer Name to the Subtotal Row when Subtotaling by Account Number
(00:35) Add the Subtotals
(00:58) Collapse to one line per customer
(01:16) Select all blank rows in the customer name column using Go To Special
(01:43) Type a formula & Ctrl+Enter
(01:53) 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:
Avoiding filling all cells when copying formulas in Excel
Copying data down to subtotal rows in Excel
Excel seminar on power excel techniques
Excel tip for adding subtotals quickly
Excel tutorial on using the SUM function with subtotals
Formula to copy data from row above in Excel
How to add subtotals by account in Excel
How to use Ctrl+Enter to enter formulas in multiple cells in Excel
MrExcel netcast episode on working with subtotals and copying data
Shortcut for selecting visible cells only in Excel
Using the group and outline buttons in Excel
YouTube video on adding subtotals in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152274/

More from this channel