Excel 2013 table formatting

Question

I currently have a table that looks like this:

Company | 2013 Total | 2013 Gross $'s | 2013 Gross % | 2012 Total | etc..
        |            |                |              |            |

Obviously this is not a sustainable format for data.
I would like to reformat it (possibly using a pivot table?) to look more like this:

              | 2013 | 2012 | 2011 |
Company Name  |      |      |      |
   Total      |      |      |      |
   Gross $'s  |      |      |      |
   Gross %    |      |      |      |

Company 2     |      |      |      |
   Total      |      |      |      |
   Gross $'s  |      |      |      |
   Gross %    |      |      |      |

Fairly similar to this example at microsoft.

I tried modifying some of the examples in the above link, by making multiple tables with ID keys to create relationships in a pivot table but I was unable to get the desired outcome.

When trying to create a pivot table using multiple tables I tried with the following:

Table 1 (names)    Table 2: (years)
   |                 |
| Company nm |    | Year  |
| comp 1     |    | 2011  |
| comp 2     |    | 2012  |
                  | 2013  |

Table 3 (values)
   |
| Company | Year | Total | Gross |
| comp 1  | 2011 | xxx   | xxx   |
| comp 1  | 2012 | xxx   | xxx   |
| comp 1  | 2013 | xxx   | xxx   |
| comp 2  | 2011 | xxx   | xxx   |

The Name from the Company column was a row, the years were set as columns, and the total/gross from values were set as a value in the pivot table.

The relationships:

1. values (company) -> name (company)
2. values (year) -> years (year)

If i only use the Totals and not the Gross, it looks similar to what i want: http://prntscr.com/32dsm9 , but if i add the gross field: http://prntscr.com/32dsu8 It adds a bunch of columns. I'm not sure how to make that be a row.

Answer 1

First of all, sorry that I don't have Excel 2013 so I demonstrate with Excel 2007 but they should be similar.

Click on the PivotTable you have created, at Column Labels at the PivotTable Field List of the right panel, you will see a "(Sigma) Values" button if you are to summarize over one field. Simply drag it to Row Labels area (as the two figures below).

It will look like this afterwards:

For further formatting choices you can explore PivotTable Tools -> Design -> Layout.

---

Extra - PivotTable with conditional formatting

To highlight a cell if the totals have gone down since the last year:

Click on one of the cells to apply the highlight condition (e.g. B6 as above figure), select Home > Conditional Formatting > New Rule. Select Apply Rule To "All cells showing "Sum of Total" values for "Company" and "Year"; Select rule type Use a formula to determine which cells to format; at Format values where this formula is true input =B6>C6 (B6 is the cell you selected and C6 is a relative reference); change the format (e.g. fill with yellow colour) as this figure:

Final outcome looks like this: