DeutschPivottables – Controllers favorite tool
In today’s blog post I would like to introduce you in the amazing world of pivottables .
As an example we use this Excel table:
| A | B | C | D | E | F |
| 2 | Company | Product | Turnover | Hours | Turn. per hour |
| 3 | Meierhans AG | Interim CFO Cola | 15000 | 150 | 100 |
| 4 | Pfistersohn GmbH | Interim Controller | 25000 | 220 | 114 |
| 5 | Heirisatz AG | Interim Accountant | 1200 | 15 | 80 |
| 6 | Pivottable AG | Pivottabellen | 2500 | 30 | 83 |
| 7 | Baumann Finance | CFO Service | 30000 | 350 | 86 |
| 8 | Meierhans AG | IFRS Buch | 3200 | 32 | 100 |
| 9 | Pfistersohn GmbH | US Gaap Buch | 2800 | 26 | 108 |
| 10 | Heirisatz AG | Excel König | 5050 | 40 | 126 |
| 11 | Pivottable AG | Excel für Dummies | 7500 | 80 | 94 |
| 12 | Baumann Finance | CEO Anleitung | 12500 | 1300 | 10 |
To generate the pivottable you just need to choose the menu Paste/Pivottable:
Now the Pivot Table field list appears. All attributes can be assigned to each area. Under subreport filters you can apply filters , which retain validity for the entire table. In the column and row label is usually the company , the product , etc. depending on what you would like to get analyzed. And then under value you insert the key figure you would like to analyze.
In our example i would like to analyze the sum of turnover per hour per entity. This means i have to choose the company as row value and turnover and hours as values.
Above in the menu header under PivotTable Tools you can find various formatting options. Also very useful is the ability of a calculated field . This can be found under fields , elements and groups. You have the possibility to divide one figure by the other. In our example, it is possible to calculate sales per hour easially by divide turnover by hours. Pivot tables are most comfortable with direct link to an external SQL or Access database. Therefore you just have to choose external data source at the beginning and then select the desired database.
I would be happy to assist you if you need an Excel expert. Contact me not binding under contact@baumann-finance.ch . I have a lot of experiences in this area and I think you will be amazed what can be accomplished with the favorable Office Tools. A great external page with further information about Pivottables you can find here.
