I have a spreadsheet that contains music data. The table contains columns for Genre, Artist, Album, Year, Song, and Time, but only the first value is filled in, much like an outline. I want to run this data through a pivot table to analyze the music in in different ways, but because the data isn’t well structured… most cells are blank, so the counts in the pivot table summary are off. To fix this this, I need to add correct values for each blank cell. On the surface, this looks like a really tedious job. Because even using the shortcut for Fill down, Control D, it’ll take a long time fill in all the missing data. But, there’s actually a really easy way to add the missing data using a simple shortcut recipe. I start by selecting the entire set of data with Control A, then use Go To Special to select only the blank cells. Next, I enter a formula that simply gets a value from the cell above…note that the address is relative. The trick here is to use a shortcut to enter this formula in all selected cells at the same time. For this, use Control Enter. Now all blank cells have a value. This works because it creates a chain of formulas that all get a value from the cell above. So, with all values in place, I want to make sure the data is stable, and won’t change if sorted by different columns. To do this, I’ll select all data again, copy, and use the shortcut for Paste Special with Values to overwrite the formulas. Now if we go to the pivot table and refresh, I get a useful summary, and I can work with the data any way I want. You can use this same recipe whenever you need to fill in data that’s been organized like an outline, and is missing values.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
