Excel Access and Pivot Tables in Controlling You should avoid these mistakes

Pivot tables have become indispensable in controlling and in the evaluation of data tables. Often finished tables are made available to specialist departments or clerks who often ponder the usefulness of these tables. Often, essential rules are not taken into account when creating. I have summarized some avoidance strategies for you. The following list provides an overview of the most common mistakes when creating pivot tables:

1. Split functionally dependent columns into row / column headings

Example: Article number in the row label and article name in the column label. As a rule, you will only find ONE value in the result of the value range.

2. Evaluation of clear tables

By evaluating a unique table (e.g. master data) on a unique field, there is no consolidation!

3. Too many groupings in the column headings

The evaluation can be illegible or incomprehensible and stretched too far to the right if too many fields or columns are grouped.

4. Accept fields / columns in row AND column labeling

The result would be something like the division of functional dependencies.

5. Evaluate uncleaned data

Example: a date column that contains invalid date values ​​(e.g. text entries). These values ​​cannot be grouped or evaluated.

6. Problematic: Access “from outside”

Reference to values ​​of a pivot table with the function PIVOTDATA ASSOCIATE. As soon as the pivot table is “rebuilt” it “rips” the function. Take special care here!

7. Up-to-dateness of the data

“Forgot” to update the data and keep the evaluation “current”. The pivot table options can be used to set, among other things, that the pivot table is updated when it is opened.

8. Link to data with variable path

Example: When creating a pivot table, the export file V: Daten 201304Daten.xlsx is linked, but the next data export is then called V: Daten 201305Daten.xlsx. When trying to update the data, the source path is no longer found.

9. Keep the pivot extract up to date

Note: A pivot extract can be created at any time by double-clicking on a pivot data or value cell. This extract filters the data on a separate worksheet – so it represents a “status”. However, subsequent updates are not possible on this extract.

10. Incorrect relationship between columns in different tables

The result can indeed “look” right here, but sometimes represents completely wrong results (from V2013).

11. Evaluation of “wrong columns”

Example: If the cost center is managed numerically, this can lead to the total of the cost centers being included in the data section of the pivot table. Incidentally, this also happens automatically if you simply check the field list of the PivotTable fields. Then numerical values ​​are automatically transferred to the values ​​section. However, the sum of the cost centers is usually just as meaningless as the average postcode of all customers from North Rhine-Westphalia.

If these tips are taken into account, almost nothing can go wrong. Perhaps one more tip: pivot tables only have read access to the data. Even if a pivot table is rebuilt (corrected), this does NOT affect the source data.

Leave a Reply

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