How to create a Pivot table


Pivot tables are used to summarize by something mathematical – sum, average, Count…

Your data must be layout in a linear format – top row must be field header names and the records of data in rows following immediately underneath. There must be no blank columns or rows, all headings must be labeled.

Correct Data Layout

car-data

Incorrect Data Layout

incorrect pivot data.png

  1. Ensure your cursor is in the data setpivot-data-selection
  2. Go to the Menu for Insert
  3. Select Pivot Table
  4. It should have automatically detected the start and finish point of your data set.
  5. By Default the pivot table is created on a New Worksheet, Press Ok
  6. You should now see a Pivot Field list to your right.
  7. Tick the field of what you would like to group by: ie Car Make
  8. Because it is formatted for text it will be automatically inserted into the row field
  9. Tick the Monthly Repayment – because it is formatted for number it should go to the Value field. If it does not it will go to the row field. This will happen because the field is formatted for text not number.
  10. Drag it to the Value area if it does not display “SUM” then it will say Count
  11. If that happens right mouse click on a figure in the pivot table and select number format.

pivot-table-fieldspivot-table

Related Topics

Format as Table Feature in Excel

Format Table for Mac

How to create a Vlookup function in Excel

Excel Course

 

Advertisements

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: Info@azsolutions.com.au m: 0414 417 059 www.azsolutions.com.au
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s