Thursday, January 20, 2011

How do you create pivot tables in Excel?

Today's post is all about pivot tables. I am going to answer the following questions: How do you use and create a pivot table in Microsoft Excel? What are the advantages of using a pivot table? What is a pivot table? How do you make a pivot chart? Let's begin with the definition of a pivot table.

What is a pivot table?

Pivot tables provide an interactive summary of data. You can quickly rearrange and sort the information in the pivot table. There's no need for any complicated formulas. A pivot table classifies numeric data in a list based on other fields in the list. Before creating your pivot table, you should organize your data in a nice and neat table. Do this by creating headers in the first row. Try not to have any blank cells, rows, or columns in your table.

What are the advantages of using a pivot table?

Pivot tables can easily be rearranged by moving, adding, or deleting unneeded fields. Pivot tables are also dynamic, which means that the results of the table are automatically recalculated and updated whenever something changes, like when fields are added or removed, or if categories are hidden or displayed. Pivot tables are also simple to update. It's also very easy to generate charts from your pivot table.

How do you create a pivot table in Excel?

First, under the "Data" menu, select "Pivot Table and Pivot Chart Report."  This will bring up the Pivot Table Wizard, which consists of the following three steps.

Step 1: If your data resides in a worksheet, simply click "Next."

Step 2: Excel will automatically select the data we are currently working with.  Click "Next" again.

Step 3: I usually open the pivot table in a new worksheet (again, this is the default).  Note that you can save a few seconds simply by clicking "Finish" during step 1 if you don't plan to do anything unusual (which, in my experience, is almost every time).

How do you create a pivot chart?

To create a chart from your pivot table right click on a cell in the Pivot Table and select "Pivot Chart."  A chart is automatically generated.  Note that you can view the data by using the drop down menu at the top of the chart, and you can modify the chart by either of the other variables by using the filter menus on the bottom and side.
If you don't like the type of chart Excel chooses, you can modify it by right clicking anywhere on the chart and selecting "Chart Type."
Note that if you modify the pivot table in any way, the chart is automatically updated.

Now you know all about pivot tables and pivot charts! More examples and pivot table templates are coming soon. Stay tuned!