PivotTables is one of the most powerful, yet intimidating features in Excel. PivotTables can aggregate and analyze large data sets independent of the original data layout in the worksheet.
For some Excel users PivotTables is a completely foreign concept. Indeed, PivotTables is a complex feature, which is often a reason so many Excel users shy away from them. New versions of Excel can look like Greek to old-school users and PivotTables are intimidating even in legacy versions. Add Excel's 2007/2010 unusual features and layout to the mix, and you’ll probably give up on PivotTables altogether.
We strongly advise Excel users to attend a formal training course taught by certified instructor in order to master PivotTables or at least commit to a comprehensive self-study program. The meaning of an old Greek saying "Η ημιμάθεια είναι χειρότερη από την αμάθεια" is similar to the quote "if you think education is expensive, try ignorance".
Unfortunately, several Excel users are either not trained adequately or opt to learn Excel and PivotTables through on-the-job training. We have encountered several notable PivotTable mistakes by poorly trained Excel 'heros'.
Needless to say, that there is a significant productivity loss before assistance is sought from an Excel specialist. We have witnessed projects being stalled for days (while Excel 'gurus' struggle to decipher PivotTable functionality using online blogs) and undetected PivotTable errors, which have crept into stakeholder reports.
For some Excel users PivotTables is a completely foreign concept. Indeed, PivotTables is a complex feature, which is often a reason so many Excel users shy away from them. New versions of Excel can look like Greek to old-school users and PivotTables are intimidating even in legacy versions. Add Excel's 2007/2010 unusual features and layout to the mix, and you’ll probably give up on PivotTables altogether.
We strongly advise Excel users to attend a formal training course taught by certified instructor in order to master PivotTables or at least commit to a comprehensive self-study program. The meaning of an old Greek saying "Η ημιμάθεια είναι χειρότερη από την αμάθεια" is similar to the quote "if you think education is expensive, try ignorance".
Unfortunately, several Excel users are either not trained adequately or opt to learn Excel and PivotTables through on-the-job training. We have encountered several notable PivotTable mistakes by poorly trained Excel 'heros'.
Needless to say, that there is a significant productivity loss before assistance is sought from an Excel specialist. We have witnessed projects being stalled for days (while Excel 'gurus' struggle to decipher PivotTable functionality using online blogs) and undetected PivotTable errors, which have crept into stakeholder reports.
1. 'Pivoted' data used as data source for a PivotTable
This issue is covered thoroughly in our Excel Unpivot add-in article. For example: Date headers appear as discrete pivot fields during pivot table set-up, making configuration and filtering by date much more difficult, especially when data for several years is available.
At some point, during one of the several attempts to filter the PivotTable by month, the analyst left one pivot field unchecked and the totals were understated.
At some point, during one of the several attempts to filter the PivotTable by month, the analyst left one pivot field unchecked and the totals were understated.
2. Filtered range used as data source for a PivotTable
Filtering or hiding rows within a data source range has no effect on PivotTables results. PivotTables have their own filters, so Excel doesn't allow data to be filtered twice.
However, a PivotTable data source data range can be defined incorrectly, because an entire hidden area below the last visible row can be excluded, when the data source range is filtered and the range is selected by the user with CTRL+SHIFT+ARROW key combinations.
A single range with 35 columns (A:AQ) was used as the data source for several PivotTables. The analyst filtered the range during data import inspection and failed to clear or disable the filter after the data inspection was over.
The analyst didn't realize that rows 467-620 were excluded from PivotTable calculations, because a filtered range was used as a data source. Obviously, pivot totals were understated.
However, a PivotTable data source data range can be defined incorrectly, because an entire hidden area below the last visible row can be excluded, when the data source range is filtered and the range is selected by the user with CTRL+SHIFT+ARROW key combinations.
A single range with 35 columns (A:AQ) was used as the data source for several PivotTables. The analyst filtered the range during data import inspection and failed to clear or disable the filter after the data inspection was over.
The analyst didn't realize that rows 467-620 were excluded from PivotTable calculations, because a filtered range was used as a data source. Obviously, pivot totals were understated.
3. Retained items deleted from the data source selected in pivot field filter
A data range was being imported into a reporting workbook every month. Initially, full length month names were used. In June, the month names were replaced by 3-letter abbreviations. Since the pivot option to 'retain items deleted from the data source' was set to Automatic (default), the full length month names were not removed from the pivot filter list.
The analyst's intention was to filter the pivot by April, May and June. Unfortunately, only May was a valid selection, because May is a 3-letter month name !
An Excel 2010 pivot slicer could have alerted the analyst, as the items which have been deleted from the data source could have appeared greyed out at the bottom of the filter list (depending on slicer settings), while the 'number of items to retain per field' was set to Automatic.
Ideally, None should have been selected as the 'number of items to retain per field' in this project.
An Excel 2010 pivot slicer could have alerted the analyst, as the items which have been deleted from the data source could have appeared greyed out at the bottom of the filter list (depending on slicer settings), while the 'number of items to retain per field' was set to Automatic.
Ideally, None should have been selected as the 'number of items to retain per field' in this project.
4. Hidden blank column included in pivot data source
The analyst selected what seemed as a contiguous range while creating a new PivotTable. Unfortunately, one hidden blank column was included within the data source range. To make matters worse, Excel's warning message does not alert the user explicitly about entire blank columns within a pivot data source range. The analyst focused in validating field names in visible columns only.
The above example demonstrates how hiding content in Excel may create more problems than it tries to solve. Content hiding is not considered a best spreadsheet practice and should be avoided in our opinion.
The above example demonstrates how hiding content in Excel may create more problems than it tries to solve. Content hiding is not considered a best spreadsheet practice and should be avoided in our opinion.
We have created workbooks with hundreds of PivotTables using Excel Tables and external
databases as data sources. Workbooks are audited using our custom VBA pivot add-ins.
Please contact us to discuss your new project or an audit of your existing Excel application.
databases as data sources. Workbooks are audited using our custom VBA pivot add-ins.
Please contact us to discuss your new project or an audit of your existing Excel application.
