Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Pivot table row labels in separate columns

A common query regarding Pivot Tables in the more recent versions of Excel is how to get pivot table row labels in separate columns.

So in the below example there are 2 rows of data and they both appear to be in column A.

pivot table row labels in separate columns 1

This is fine for viewing and useful for printing, but if you want to use the data from the pivot table in a sheet somewhere else, when you copy and paste it, it will come out looking like this which makes it hard to sort or filter on the data.

The issue here is simply that the more recent versions of Excel use this as the default report format. Our preference is rather that the pivot tables are shown in tabular form (all columns separated and next to each other).

You can do this by changing the report format. So when you click in the Pivot Table and click on the DESIGN tab one of the options is the Report Layout.

pivot table row labels in separate columns 2

Click on this and change it to Tabular form.

Your pivot table report will now look like the bottom picture and will be easier to use in other areas of the spreadsheet and in our opinion is also easier to read.

pivot table row labels in separate columns 3