Tip #107 - Excel Pivot Tables

100 (or more) Computer Tips in 100 (or more) Days

Tip #107 - Excel Pivot Tables

Pivot tables are one of the most powerful features of the Excel program. They make it child's play to summarize and analyze large spreadsheets of related information. You can select which columns of information should be included in the analysis. Generally, the pivot table is displayed on a separate worksheet in the document for easy reference.

The information in your worksheet should be continuous with no blank columns or rows. Blank columns and rows indicate to Excel that information on one side of the blank area is not related to information on the other side. I am going to be working with over 1,000 rows of sales information that includes the product, customer, salesperson, region, quarter, and amount. Creating related tables to analyze this type of information used to be time-consuming and tedious. With just a few clicks this will be accomplished very easily. To see a video of how to create pivot tables in Excel scroll to the end of the page.

The data on the left pivots into the report on the right using the Pivot Table feature

To Create a Pivot Table:

1. Open the file that has the data you would like to analyze. Click any cell that has data in it so that Excel will know which area of the worksheet the data is to come from.

2. From the Insert Tab select Pivot Table. The Pivot Table dialog box will appear. You should not have to make any adjustments because Excel should have automatically identified the cell range and you would like the pivot table to appear on a new sheet.

3. Click OK. The pivot table appears on a new sheet. The new sheet has been inserted to the left of the previously selected sheet.

4. Drag the fields from the pivot table list on the right side of your screen down to the report filter/column/row/values boxes below the list. Do not use the checkboxes. If you do use the check boxes Microsoft will just put the items where it thinks you would like them...not necessarily where you want them.

Drag fields down to the Report Filter, Column Labels, Row Labels and
Values fields to create the Pivot Table Report

If you don't like the order that the information in the pivot table is being grouped you might consider switching the order of the fields around. If you have more than one field in the column or row box, drag one field above or below the other to change the order.

One thing to note, there are no formulas in pivot tables. As a result, if you change the data that the pivot table is based on you must use the refresh button on the Pivot Table Tab.

That's it! You now have a Pivot Table. Move the fields around until you get the report you need.

Happy computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.

Want more tech news or interesting links? You'll get plenty of both if you keep up with Diane McKeever, the writer of 100 Computer Tips in 100 Days, by subscribing to her Facebook posts or visiting her web page, www.dianemckeever.com .