Excel – Can a Pivot table transpose values

I don't know if this is as "elegant" as you were hoping for, but you are on the right track with the pivot table idea. You just have to cheat a bit. A "helper column", a pivot table, and the "vlookup" function can be your friends here.

First, to the original data, add a helper column for a "SupplierNumber". Company X = 1, Company Y=2, Company Z=3. I put the helper column as the first column, for easy use later in a vlookup. Note the data is now in cells A5:D11.

Next, pivot the data like this. I used the "classic Pivot table layout" option in the "Display" tab of the "Pivot Table Options" dialog (right-click on the pivot table to bring that dialog up).

Select and copy the entire pivot table, then "paste special/values" in another location to the side of the original pivot table. Then, in the body of the table copy, enter the formula shown. Note that the vlookup formula is referring back to the original pivot table (the "H7" reference -- see the previous diagram) and also to the range A5:D11 mentioned earlier.

Like I said, it's not elegant, but it works. There are a lot of smart people that browse this site who will likely come up with an "elegant" solution for you!

Whenever you have more than one item to the Values box, Excel will need to show them side by side in some way.

The default is to show both values as side-by-side columns, that's why you're seeing this "value" item under column labels. It's there because you can drag it under rows labels to have them show on top of each other.

Example with 2 values as Columns:

Year    Average      Sum of 
       sale price    profit
2011        $50        $300
2012        $60        $300
Total       $55        $600

Edit: Here's how I would approach your example:

Or: