Steps
1. 1
Start Microsoft Excel.
Ads by Google
Try mini-graphs in Excel
Bissantz SparkMaker for Excel dashboards and reports - free trial
www.bissantz.de
2. 2
Download the data used for this exercise at PivotTable source file so you can follow along.
3. 3
Click on the Data menu and choose PivotTable and PivotChart Report.
PivotTables command - Click to enlarge
4. 4
Answer the questions presented as follows:
Questions, Questions - Click to enlarge
5. 5
Specify the location of the data you are going to summarize. If you have your data in an Excel list that is
currently open, Excel will automatically select the cell range.
6. 6
Click Next.
7. 7
Ensure the New Worksheet option is selected after you click Next from the previous step, and then
click Finish.
Final step in creating a PivotTable - Click to enlarge
8. 8
Assemble the PivotTable. The PivotTable field list can be a lifesaver for new and advanced users alike.
PivotTable Field List - Click to enlarge
9. 9
Looking at the data, let's say you want to see how my sales representatives did. Add SalesRep field to
the Row area. To do that, click on SalesRep, change the dropdown to Row Area and click Add.
SalesRep in the Row Area - Click to enlarge
10. 10
Next, add the Total field to the Data area.
SalesRep in the Row Area, and Total in the Data area - Click to enlarge
11. 11
That just tells you how much of everything they sold, but what if you want to see how much in each
category? Add the Category field to the Column area.
Category added to the Column area - Click to enlarge
12. 12
Click to enlarge
Supposing you don't want the Condiments category as part of the analysis, click on the dropdown
arrow beside Category and deselect Condiments.
13. 13
Click OK. Notice how the Grand Totals have been recalculated to not include results from the Condiments
category...
No Condiments for you! - Click to enlarge
14. 14
To make a PivotTable look good, there are a multitude of AutoFormats available. Click on the Format
menu and choose AutoFormat.
Autoformat PivotTables - Click to enlarge
15. 15
You're done, unless you want to do further analysis!
Ads by Google
Download Google Chrome
Searching is fast and easy with Google's web browser.
www.Google.com/Chrome
Free iXBRL Reporting
View, analyse, and publish your own financial reports from xbrl data
www.mashiq.com
edit Video
edit Tips
If you use the Import Data command from the Data menu, you have more options on how to import
data ranging from Office Database connections, Excel files, Access databases, Text files, ODBC
DSN's, webpages, OLAP, XML/XSL, and more. Then you can use your data as you would an Excel
list.
Ads by Google
BIME - SaaS Analytics
Data analysis & dashboarding 1st month free, no L/T contract
businessintelligence.me
If you click the Browse button in the dialog box from Step 7, Excel will allow you to browse to an
external Excel file but you will have to specify the cell range yourself. It's better to use the External
Data source instead.
All columns need to have a "Name" in the field.
There should not be any gaps between the "Name" row and the data.
If you are using an AutoFilter (Under "Data", "Filter"), disable when creating the pivot table. It is alright
to re-enable after you have created the pivot table.
Even though "pivot tables" are used widely, PivotTable is an officially licensed trademark of Microsoft.
edit Warnings
If you are using data in an existing spreadsheet make sure that the range that you select has a unique
column name at top of each column of data.
edit Things You'll Need
Microsoft Excel
Data to consolidate
Network connection for some kinds of external data.
edit Related wikiHows
How to Consolidate in Excel
How to Create an Excel Financial Calculator
How to Create a Currency Converter With Microsoft Excel
How to Recover a Corrupt Excel File
How to Add Two Cells Already Containing Sum of Other Formulas
How to Calculate the Day of the Week in Excel
How to Merge Documents in Microsoft Word
How to Create a Browse Tab in Excel
Articles for You to Write
Here is a list of suggested articles that have not yet been written. You can help by researching and writing one
of these articles. To get started writing one of these articles, click on the red link of a title below.
How to Create a Financial Report
How to Create a Drop Down Box in Access
How to Create a Drop Down Box in Excel
How to Calculate Fields in an MS Access Report
How to Use Excel Currency Conversion
Article Info
Featured Article
Last edited:
November 24, 2010 by Writelf
Categories:
Featured Articles | Spreadsheets
Recent edits by: Wendy Weaver, VC, Leona (see all)
Article Tools
Share this Article:
Discuss
Print
Email
Watch
Edit
Send fan mail to authors
+ Embed this: Republish this entire article on your blog or website.
Thanks to all authors for creating a page that has been read 302,564 times.
Was this article accurate?
Yes No
Write An Article Random Article
Related wikiHows
Consolidate in Excel
Create an Excel Financial Calculator
Create a Currency Converter With Microsoft Excel
Recover a Corrupt Excel File
Ads by Google
Microsoft Excel
Pivot Table
Excel 2007
Download Excel
Excel Pivot
Hide all ads - Why?
Featured Articles
Take Care of a
Drunk Person
Fly in Your
Dreams
Give a Gift to
Someone Who
Likes to Cook
Help Your
Spouse With
Depression
22Recent Changes
LatinFoodie just joined the wikiHow Community
10 minutes ago
Bokehmon sent fan mail to Krystle
9 minutes ago
Bokehmon sent fan mail to Ttrimm
9 minutes ago
Bokehmon sent fan mail to Carolyn Barratt
9 minutes ago
LatinFoodie posted a comment on the discussion page for Prepare Gallo Pinto
9 minutes ago
Want to join in?
Meet a Community Member
Meet Harri, who joined wikiHow in 2009. She enjoys categorizing articles on wikiHow to help keep them
organized, and to help people find them easier.
Join The Community
- collapse Things to Do
Write an Article
Edit this Article
Request a New Article
Answer a Request
Login for more!
+ expand Places to Visit
Follow Us On...
