Excel - Add A Blank Row After Each Subtotal Row - Episode 2507

Microsoft Excel Tutorial - Add A Blank Row After Each Subtotal Row.

Welcome to another Excel tutorial! In this video, we will be tackling a question from Holly about adding a blank row after each subtotal row in Excel. This may seem like a simple task, but it actually involves a series of steps that can be quite confusing. So make sure to pause and take a screenshot of the steps as we go along.

If you have any tips or tricks for using the Go To Special function in Excel, please share them in the comments below. I will be speaking at a conference in October and would love to give a shout out to anyone who can teach me something new about this feature.

Now, let's get started. Holly has a dataset with subtotals and wants to insert a blank row after each subtotal row to perform a calculation. First, we will add a helper column and fill it with the number 1. Then, using the Go To Special function, we will select only the visible cells and insert a 1 in each subtotal row. Next, we will use the Go To Special function again, this time selecting only the constants that are numbers. This will allow us to insert a blank row after each subtotal row.

But we're not done yet! We still need to add a calculation in the blank row. To do this, we will use the Go To Special function once more, this time selecting only the blank cells. Then, we will build our calculation as if we were pointing to the row directly above the blank row. Finally, we can format the cells as needed.

If you have any questions or suggestions for future Excel tutorials, please leave them in the comments below. And don't forget to subscribe to my channel for more helpful tips and tricks. Thank you for watching and I'll see you in the next video!

Table of Contents
(0:00) Add Blank Row after Each Subtotal Row in Excel
(0:15) Go To Special Tricks?
(0:47) Subtotals
(1:10) Helper column on Total rows
(1:50) Move 1's down a row
(2:28) Select just 1's & Insert Rows
(3:00) Select blank cells
(3:32) Entering the formula
(4:04) 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:
Adding blank rows in Excel
Adding calculations in blank rows
Adding total rows in Excel
Calculation in blank rows
Excel Subtotals
Formatting selected cells in Excel
Go To Special in Excel
Go To Special tricks in Excel
Inserting cells in Excel
Inserting sheet rows in Excel
Removing columns in Excel
Using Alt+Semicolon to select visible cells

Holly has a data set with Subtotals. She needs to insert a blank row after each subtotal row and add a calculation to those blank rows.

[excelisfun]

Cool Tip, Mr Excel!!!! Ctrl + Enter Rules!!!

[TSSC]

Here’s a Go To tip:
F5 displays the Go To dialogue box (surely no surprise), but observe that the Reference part of the dialogue box is populated with the last address (cell, area or areas) activated via a Go To command. I find this very handy during formula auditing. F5+Enter enables quickly toggling between two references.
Example:
A formula references a cell on another worksheet, I use trace precedents, double click on the dotted line that appears to display the Go To dialogue box and select the reference I want to go to. When I want to return to the cell with the audited formula, I simply tap F5+Enter.

[wimgielis]

Bill, two birds with one blow... A trick with GoTo Special and selecting in your helper column very quickly. Select a cell near the top in your helper column. Press F5 > GoTo Special > Last cell. Press Shift and then OK... You gain a lot of time and effort and you have a new trick with that Last Cell.

[GeertDelmulle]

The one GoTo Special that comes to mind is the well-known trick to mimic the Power Query Fill Down function.
I’ve programmed all the steps under a single button on my Stream Deck.