iTechtalk

atom

Rules for Preparing Underlying Pivot Data in Excel 2007:-


Pivot tables work best when they are created from transactional data. Every row in a dataset should represent a detailed transaction. You do not want any sort of a summary in your dataset. If you have months going across the columns, this dataset is not ideal for creating pivot charts. A report with months going across the columns is a cross-tab summary of the detailed records in your dataset. You should go back to the person who provided that dataset and see if you can get the original underlying data that person used to produce the summary.

Your data should have no blank columns or blank rows. An occasional blank cell is not fatal but really compromises the pivot table engine’s internal logic. If you have 99,999 cells with numbers and 1 blank cell, Excel assumes that the column contains text and chooses to count the records instead of summing them. If you can, you should fill any blank cells with zeros before you begin.

Every column should have a unique one-cell heading. These headings will appear in the Pivot Table Field List box, so they should be relatively short but meaningful.

It is not necessary, but you might want to convert your dataset to a table before creating a pivot table. The advantage is that if you later add new records to the table, you can easily refresh your pivot chart without re-specifying the range of data to be used. To create a table, you select a cell in the dataset and press Ctrl+T.