Look Who it Was Random video

Excel - Efficiently Fill in Blanks in Excel Using Pivot Tables | MrExcel Netcast - Episode 1146

391 views · Published 17 November 2009 · 3:13 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Efficiently Fill in Blanks in Excel Using Pivot Tables | MrExcel Netcast.

Welcome to another episode of the MrExcel netcast, where we tackle all your Excel problems and find the most efficient solutions. In today's episode, we're going to be discussing a common issue faced by many Excel users - filling in blanks in a large dataset. This can be a tedious and time-consuming task, but fear not, because we have a solution that will save you a lot of time and effort.

The question for today's episode comes from Mark, who has a large dataset from a client's GL software. However, the account ID and account description are missing from the ending balance, making it difficult to analyze the data. Mark's initial attempt at filling in the blanks using the incrementing account number method was not successful. But don't worry Mark, we have a better solution for you.

Using the power of Pivot tables, we can easily fill in those pesky blanks in just a few simple steps. First, we select the blank cells using the Go To function, then we use the Special option to select only the blank cells. Next, we enter a formula that references the cell above and fill it down using Ctrl+Enter. And just like that, all the blanks are filled in and we're ready to analyze the data.

But what if the blanks are not really blank? This was the case for Mark's data, where the cells appeared blank but were not recognized as such by Excel. In this scenario, we use the Auto-filter function to select only the ending balance cells and then use a formula to fill in the blanks. While this method may take a bit longer, it is still a more efficient solution than manually deleting the blank rows.

So there you have it, two different methods to fill in blanks in Excel. I hope this helps you save time and frustration when dealing with large datasets. And remember, there's always more than one way to solve a problem in Excel. Thank you for tuning in to this episode of the MrExcel netcast, and don't forget to send in your Excel questions for a chance to have them featured on our show. See you next time!"

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/

Table of Contents:
(00:00) Analyzing Data with Pivot Tables
(00:15) Solving a Data Problem with Pivot Tables
(00:25) The Challenge of Blank Cells
(00:40) Alternative Solution with Auto-filters
(01:56) Unexpected Data Format
(02:29) Filling in Blank Cells
(02:39) Programmer's Mistake
(02:49) 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:
Converting text fields to general in Excel
Excel data analysis with pivot tables
Filling cells with formula in Excel
Filtering data in Excel
Fixing blank cells in Excel
How to solve blank cell issue in Excel
MrExcel netcast episodes
Solution for missing account ID and description in Excel
Tips for handling unusual data in Excel
Troubleshooting blank cell detection in Excel
Using Ctrl+G and Special to find blank cells in Excel
YouTube pivot table tutorial


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


Mark asks how to fill blanks in with the data from above. Now...if you've watched this podcast, you've seen the trick for this, but Mark's data has something preventing Go To Special Blanks from working! So - another way to go. Episode 1146 shows you how.

More from this channel