Paste From Excel Pivot Table With Excluding Blank Cells

Paste From A Pivot Table With Excluding Blank Cells Excel

Show Pivot Table Data in Tabular Layout

1) Click in the Pivot Table to bring up the Pivot Table Tools tab on the Ribbon;
2) Below the Pivot Table Tools tab click the Design tab;
3) On the left end of the ribbon, click the Report Layout option in the Layout group;
4) Click ‘Show in Tabular form’. Now each row item is in its own column.
Show Pivot Table in Tabular Form


Remove the Subtotal Rows from a Pivot Table

Another thing you may want to do with the data outside of the Pivot Table is to remove the subtotal rows. You can delete the subtotal rows after you copy the data outside of the Pivot Table or you can remove the subtotals in the Pivot Table.

1) Click in the Pivot Table to bring up the Pivot Table Tools tab on the Ribbon;
2) Below the Pivot Table Tools tab click the Design tab;
3) On the left end of the ribbon, click the Subtotals option in the Layout group;
4) Click ‘Do Not Show Subtotals’.

Fill blank row labels in an Excel 2010 Pivot Table

Unfortunately, in Tabular format, blanks are still shown instead of repeating the items down the columns.

If you are using Excel 2010, I’ve got good news. For Excel 2007 users, not so good.

In Excel 2010 there is a new option that lets you to display data in your Pivot Table without the blank cells…all items are repeated down the columns.

1) Click in the Pivot Table to bring up the Pivot Table Tools tab on the Ribbon;
2) Below the Pivot Table Tools tab click the Design tab;
3) On the left end of the ribbon, click the Report Layout option in the Layout group;
4) Click ‘Repeat All Item Labels’.

Now your data is ready to Copy and Paste Special, Values to another range outside of the Pivot Table in another worksheet or workbook.

Paste From Excel Pivot Table With Excluding Blank Cells

Copy Pivot Table data to worksheet
Paste From Excel Pivot Table With Excluding Blank Cells

 

Leave a Reply

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