Excel – Pivottables


Pivottables – 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:

Pivot

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.

Feldlisten Pivot

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.

Pivot Auswertung

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.

Pivot externe Datenquelle

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.

Print Friendly, PDF & Email

Leave a comment

Your email address will not be published. Required fields are marked *