Excel Fill Blank Rows or blank Cells in inactive Pivot Table

This Excel tutorial explains how to use the value above to fill blank rows (blank cells) in inactive Pivot Table in Tabular format.

You may also want to read:

Excel Delete Blank Rows

Excel VBA reformat merged cells to row

Fill Blank Rows or blank Cells

Excel Pivot Table Fill Blank Rows

In Pivot Table, when you group an item in column, the same grouping name does not repeat itself and leaving blank rows.

In the below picture, Division contains blank rows.

fill_blank_rows_01

In Excel 2010 onwards, you can insert blank rows using the Excel built-in Function, under PivotTable Tools > Design Blank Rows > Remove Blank Line after Each Item

fill_blank_rows_02

However, you may receive Pivot Table which has been pasted as value, so the Pivot Table is inactive (Dead). To do data analysis, you may want to fill blank rows.

Excel Fill Blank Rows

In the below example, column A is the control data while column B is to be converted to fill blank rows.

fill_blank_rows_03

Step 1: Select B2:B15

fill_blank_rows_04

Step 2: Press F5 >Special > Blanks

fill_blank_rows_05

Step 3: In cell B3, enter formula =B2 (the value above)

fill_blank_rows_06

Step 4: Press “Ctrl” + “Enter” to finish.

fill_blank_rows_07

You can select multiple columns in Step 1 to convert multiple columns and rows.

However, it is important to note that, if you have multiple column grouping, you should not use this method because this method only copies the value above but does not consider the grouping on the left column, items that are intentionally blank will also be converted. To solve this problem, read the below method to convert using VBA.

Excel Fill Blank Rows with VBA

It is not easy to determine intentional blank using Formula involve checking the item on the left if you have more than three columns, because this requires you to fill the blank for each column from the left to the right in order.

I created an application to fill blank rows for the whole table instead of just one column.

Below is data to be converted, in Pivot Table Tabular Format (2003 format).

fill_blank_rows_10

Run Macro > fill_blank_rows

Fill in the start and end Cell you need to fill blank rows

Check the “Convert multiple columns” check box, otherwise only the first column will be filled

Press the button “Fill blank rows”

fill_blank_rows_09

Now all blank rows are filled, except for those intentional blank Cells.

fill_blank_rows_11

There is another type of conversion, click on the “Type 2 Conversion” tab and repeat the same steps above.

This conversion is same as Type 1 Conversion, but adding one more step to fill in the intentional blank value with the grouping value on the left.

fill_blank_rows_13

Below is a comparison of the original data and the converted data.

fill_blank_rows_12

Click here to download the application

Macro fill blank row

Outbound References

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27847613.html

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *