Excel - Inserting Blank Rows After Each Subtotal in Excel - Episode 2433

Microsoft Excel Tutorial: How to insert row after Subtotal in Excel.

Welcome to episode 2433 of the MrExcel Podcast, where we will be discussing how to add a blank row after each subtotal in Excel. This is a common request from managers and can be a bit tricky to figure out. But don't worry, I have a cheat and the real way to do it, so let's get started.

First, let's take a look at the cheat method. We will simply double the row height of the subtotal row and make sure all the data is top aligned. This will give the appearance of a blank row when printed. However, this method is not foolproof and may cause issues if the file is opened by someone else. So, let's move on to the real way to do it.

In the number two view, select all the cells to the right of the subtotal row and type 1. Then, go to the number three view and shift all the ones down one row. This will effectively select all the ones in the subtotal row. Next, use the Go To function to select only the constant cells, which are the ones we just typed. Now, simply insert a row and the blank row will be added after each subtotal.

But beware, the next person who tries to remove these subtotals will not be happy with you. However, don't worry, it's not your fault. Your manager requested this and you were just following orders. Speaking of great tips and tricks, be sure to check out my book, MrExcel 2021, Unmasking Excel. And if you enjoy these videos, please like, subscribe, and ring the bell to be notified of future episodes. As always, feel free to leave any questions or comments down below. Thanks for watching and I'll see you next time for another netcast from MrExcel.

Table of Contents
(00:00) Problem Statement: how to insert row after subtotal Excel
(0:21) Method 1: Cheat by Double Height and Align Top
(0:53) Method 2: Ctrl+Enter and Shift Cells Down
(1:28) Go To Special Constants in Excel
(1:50) Insert Blank Row Above Each Item in Selection
(2:12) 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:
Add blank row after each subtotal in Excel
Deleting column in Excel
Excel cheat for adding blank row after subtotal
Excel row height calculation for adding blank row
Formatting row height in Excel
Inserting blank row in Excel
Learn Excel from MrExcel Podcast episode 2433
Printing Excel with added blank row after subtotal
Real way to add blank row after subtotal in Excel
Removing subtotals in Excel
Selecting constant cells in Excel
Shift cells down in Excel

Your manager asks if you can add a blank row after each subtotal.
Episode 2433 shows you how to do this in Excel.

The first way, is clearly cheating: Increase the height of each Total row and top align.

But the second way is the real way, using Ctrl+Enter, Select Visible Cells, Insert Cells Shift Down, and Insert Rows.

#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks

[wayneedmondson]

Hi Mr. Excel. I solved it this way: Add Subtotals to the data range, collapse group 2 so that all you see is the subtotals and grand total, select the range of visible subtotaled figures or category names, press ALT+; to make the selection apply to only the visible cells, press ALT, H, O, H, 30 (or whatever is double of the standard row height) to double the row height of the selected visible cells, press group 3 button to show the full data range and see that each subtotal and grand total has the double row height applied, print or report to manager, as needed, subtotals can then be removed in the normal way (ALT, A, B, ALT+r) and all the row heights will return to standard and there will be no blank rows and no helper columns to delete. I would not have thought of this without seeing your examples. Thanks for the challenge :)) Thumbs up!!

[julian]

That is some wizard level Excel knwoledge

[Geevs]

I loved this video, many clever tricks

[johnborg]

Great Trick Mr Excel. Thanks for sharing :)

[wimgielis]

Around 1:25 you were selecting a lot of rows manually. You can just Shift-Ctrl-End to select them all. If needed a few Sift-Arrows to have the correct columns selected but here it’s not needed.

[ricos]

I think option one is the best (I usually change the font in the first column of the subtotal row to 22 or something, having the same effect as row height).
It's just a visualisation that is required in most cases, so it makes sense to just adjust row height, that way you can maintain the Ctrl/Shift navigation to get quickly around the workbook, as well as the obvious benefits of the subtotal cleanup.
They're still ugly abominations, of course

[rjbush]

Why do managers have lame requests LOL. I really like increasing the row height method. Would a manager notice?

[jackjohnshei]

blank columns show 1 in excel distinct you make any video related this issue please sent

[ubaidillahmuhammad]

nice. please put link workbook download