insights by infactory in-factory GmbH MS Excel 2013 – Pivot and calculated fields

The 3 factors performance, time and cost are the basis for successful IT project management. We at in-factory GmbH deliver the expertise of targeted planning for successful project implementation to our customers.

MS Excel can be a helpful tool in project management by facilitating or automating many work steps. The Pivot function is particularly useful for reporting, as it can be used to display and summarize even large data sets easily and clearly without much preparation. It often helps to use calculated fields directly in the pivot in order to save the creation of auxiliary columns in the original table.

How to create a PivotTable?

As an example, a report is created about the previous project situation of employees who are assigned to different teams. The output table looks like this:

Excel-Pivot-Ausgangstabelle

Via the tab “Insert” –> PivotTable an empty pivot can be inserted at the desired position:

Excel-Einfuegen-PivotTable

When selecting the data to be analyzed, it is important to also select the headings of the source table (not just the values). After that we specify where the pivot should be placed – in this case on a new worksheet:

Excel-Pivot-erstellen

Then simply add the individual fields to the pivot by dragging and dropping them at the appropriate place (Filter – Columns – Rows – Values):

Excel-Pivot-DragDrop

How to create a calculated field?

As an enhancement, the total amount per order is now to be displayed in addition to the specification of the order in days. For this purpose, a calculated field is inserted in the pivot. In the “Pivot table tools” tab (only displayed if the cell selection is within the pivot table), select the calculated field in the “Analyze” tab via the “Fields, elements and groups” item. Then a name is assigned (in the example “Sum”) and the corresponding formula is entered (in the example “=’Commissioning in days’* Daily rate”). The field selection can be made via the selection below via “Add field”, but it is also possible to work with numbers (addition, subtraction, etc.), for example, if required. Via “Add” the calculated field is saved and you can now add it to the pivot table via drag&drop.

gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw== in-factory GmbH MS Excel 2013 – Pivot and calculated fields
Excel-Pivot-Summe-Berechnetes-Feld

Author: Roxana Trieb

ANY QUESTIONS? WE HAVE THE ANSWERS!

Write us. We are looking forward to your message!

MAIL TO