Look Who it Was Random video

Excel - Fill in Blank Cells in Excel Outline | Microsoft Excel Tutorial - Episode 451

525 views · Published 4 June 2009 · 2:39 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Fill in Blank Cells in Excel Outline.

Welcome to another episode of the MrExcel netcast. In this video, we will be discussing a common issue faced by many Excel users - filling an outline with varying numbers of sub-items. Our question today comes from Heidi, who attended one of my Excel seminars at a controllers conference in Las Vegas. Heidi's outline has a different number of sub-items for each topic, making it difficult to fill in the middle items. 

To solve this problem, we will be using a trick that I often use when converting pivot tables to values. First, select all the cells, including the headings and blank cells that need to be filled in. Then, go to "Edit" and click on "Go to". In the "Go to" dialog box, click on "Special" and then "Blanks". This will select only the blank cells in the range. 

Next, enter the formula =0.01+ the cell immediately above the selected cell. Here's the real trick - instead of pressing enter, press Ctrl+Enter to fill in all the cells in the selection. This will quickly fill in all the numbers needed. However, these are live formulas and you may want to convert them to static values. To do this, simply copy the cells and use "Edit" and "Paste special" to paste them as values. 

I want to thank Heidi for bringing up this question and I hope this solution helps her and others facing a similar issue. If you work for a large company and are looking for some continuing education for your accountants, give me a call. I would love to come out and do a quick one or two-hour seminar to share some Excel tips with your team. You can visit our website, MrExcel.com, and click on the "bring a seminar to your city" link for more details. Or, simply give us a call and we can arrange for an Excel seminar at your site. Thanks for watching and don't forget to subscribe to our channel for more Excel tips and tricks."

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/


Heidi asks a question that is a new twist on an old problem  how to fill in the blank cells in a range with an outline numbering system. Episode 451 shows how to adapt the technique discussed in Learn Excel to solve this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction and question from Heidi
(00:19) Heidi's outline and problem with numbering
(00:34) Solution using a pivot table trick
(01:18) Entering formula and using Ctrl+Enter to fill in cells
(01:51) Converting formulas to values
(02:01) Thanks to Heidi and invitation for seminars
(02: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:
Accounting conference
Continuing education for accountants
Controllers conference
Converting formulas to static values
Entering formulas in Excel
Excel sub items
Excel tips for accountants
Filling in blank cells
Filling in outline view
MrExcel.com seminar details
Pivot table to values
Using Edit Go to

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

More from this channel