How to Create Pivot Tables

How to Create Pivot Tables in Excel

from wikiHow - The How to Manual That You Can Edit

PivotTables are an interactive table that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. One advantage of this feature in Excel is that it allows you to rearrange, hide, and display different category fields within the PivotTable to provide alternate views of the data.

Steps

1. Start Microsoft Excel.
2. Download the data used for this exercise at PivotTable source file so you can follow along.
3. Click on the Data menu and choose PivotTable and PivotChart Report.
4. Answer the questions presented as follows:
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. Click Next.
7. Ensure the New Worksheet option is selected after you click Next from the previous step, and then click Finish.
8. Assemble the PivotTable. The PivotTable field list can be a lifesaver for new and advanced users alike.
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.
10. Next, add the Total field to the Data area.
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.
12. Supposing you don’t want the Condiments category as part of the analysis, click on the dropdown arrow beside Category and deselect Condiments.
13. Click OK. Notice how the Grand Totals have been recalculated to not include results from the Condiments category…
14. To make a PivotTable look good, there are a multitude of AutoFormats available. Click on the Format menu and choose AutoFormat.
15. You’re done, unless you want to do further analysis!

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.
• 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.

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.

Things You’ll Need

• Microsoft Excel
• Data to consolidate
• Network connection for some kinds of external data.

Related wikiHows

• 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

Article provided by wikiHow, a collaborative writing project to build the world’s largest, highest quality how-to manual. Please edit this article and find author credits at the original wikiHow article on How to Create Pivot Tables in Excel. All content on wikiHow can be shared under a Creative Commons license.