Excel Pivot Table returns wrong data

Question

I have two columns: Raw Material; Unit Cost

These columns are ok. However, when I insert the Unit of how each product is bought, the data messes up.

When I insert the "Unit" under Row Labels here is what I get:

Here is what I want it to look like:

However, when I switch Unit from area "Row Labels" to "Value" here is what I get:

Disregard actual data within the table. I just want it to look like the gray table (image #3)

Answer 1

Hi,

Right click on any cell in the Pivot Table > Pivot Table Options > Layout > Classic Pivot Table Layout > OK.  This will move the Unit to another column in the Row labels section.  You may now remove the subtotal rows.

Hope this helps.

Answer 2

Why are you using a pivot table? Just use a table, and filter the values, unless you are getting averages.

If you change to the classic pivot table layout, you can get Unit as the 2nd column , instead of the stacked row look in a single column, but pivot tables won't allow non-numeric fields to be the last column displayed.