This Excel tutorial explains how to use the value above to fill blank rows (blank cells) in inactive Pivot Table in Tabular format.
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.
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
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.
Step 1: Select B2:B15
Step 2: Press F5 >Special > Blanks
Step 3: In cell B3, enter formula =B2 (the value above)
Step 4: Press “Ctrl” + “Enter” to finish.
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).
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”
Now all blank rows are filled, except for those intentional blank Cells.
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.
Below is a comparison of the original data and the converted data.
Click here to download the application
Outbound References
Excel Fill Blank Rows or blank Cells in inactive Pivot Table
沒有留言:
張貼留言