How to create a simple Pivot Table in Microsoft Excel

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.














Related Topics

Create a Pivot Table

Format Table for Mac

How to create a Vlookup function in Excel

Excel Course


About Customised Computer Courses Sydney - Sutherland Shire M 0414 417 059

On-Site customised computer training courses Sydney - Sutherland Shire. Excel, Word,Power Point, Access, Outlook, Visio, Publisher, Excel VBA e: m: 0414 417 059
This entry was posted in Excel and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s