Sometimes, you are provided a table that contains blank cells where it would be a lot easier to manipulate the data if the dataset had been filled with the same value. Why?
Excel cannot recognize empty cells when you sort, filter the data or create a pivot table. In this case, you need to fill in all the blanks. So, let’s take a look at how to manipulate this data so it’s easier to run a pivot table or filter over it.
We’ll take a look at an example, a familiar one for a lot of people, where you need to fill in a large number of blank cells with a value or a formula. In the example below, we need the blank cells to reflect the relevant “Employee” and “Country”, ie cell B4 should be “Bob” etc.
Interesting read – How To Unsort In Excel
Fill Blanks With Value Above Using ‘Go-To Special’ And Formula
There are only two steps involved in filling in all the blank cells.
The first step is to identify and select all the blank cells. In this example, this would be done easily enough by highlighting them individually. However, if you have a data set with thousands of rows and weren’t sure where the blank cells were, it would be a bit of a nightmare!
To automatically highlight blanks cells, we need:
- Highlight the table with blank cells
- Press F5
- Press Special… (or Home Tab, Find & Select, Go To Special)
- Select “Blanks” (or Alt+K)
- Press Ok
Excel will then highlight all the blank cells in the area highlighted. If we wanted to run a pivot table or filter this data set to analyze the various stock items, we would need to put the “Employee” and “Country” in each row. So to do this:
- Make the first blank cell equal to the one above it, ie “=C3”.
- Press “Ctrl” + “Enter” to fill.
Try this yourself next time you are analyzing a large data set and let me know how it goes!
If there are any specific tips you’d like me to put up next time, please get in contact!
You may also be interested in Why Is Trim Not Removing Spaces In Excel?
