Microsoft Excel’s Pivot tables are used to summarize and group data by sum, average or Count. When creating a Pivot Table your data must be laid out correctly otherwise you will get an error message pop up. The correct format for your Microsoft Excel Pivot Table data is that the top header row must contain the field names and the consecutive rows below must contain the records of data. Do not have blank rows or blank columns. Don’t group data or add total rows. This is a definite NO NO in Microsoft Excel Pivot Tables. No Merged data headings either.
Whenever you can use “Format as Table” for your data format this will have great benefits and save time. To start a Microsoft Excel Pivot Table ensure your cursor is inside the data set, so long as your data has no blank rows or blank columns you should have no trouble going to the Insert Tab and Selecting, Insert Pivot Table as it will automatically pick up the block of data. If you have decided to use Format as Table, then you may have named the table in this case the table name will display here.
Selecting Fields to display in your Microsoft Excel Pivot Table
From the field list that now displays on the right hand-side choose a broad category to group by like State or Department, because this field is a text field when you tick it – it will display in the row items. Next choose a numerical piece of data to group by like “Total Amount” or “Inc GST Amount”. Sometimes the Total Amount field may position itself in the row field instead of the Values field or calculate the count instead of Sum. In this case simply drag and drop the field to the Values Item. If it records as count instead of sum, Right Mouse Click on a figure in the Pivot Table and select number format.
If you are still struggling with your Microsoft Excel Pivot Table AZ Solutions Pty Ltd offers Face to Face customized training courses in Sydney – Australia.
If you are working on a Mac it also does have the Format as Table feature.
If you have a large amount of column headings try creating a vlookup to shorten the number of columns you see in your Microsoft Excel Pivot Table Field List.
Correct Data Layout
Incorrect Data Layout
Ensure your cursor is in the data set before you go to the Insert tab and Select Pivot Table.