g Text Version
Beauty & Self
Books & Music
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
News & Politics
Religion & Spirituality
Travel & Culture
TV & Movies

Bored? Games!
Take a Quiz
Rate My Photo

Natural Living
Folklore and Mythology
Distance Learning

All times in EST

Full Schedule
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor


A Brief Introduction to the Excel Pivot Table

Guest Author - Maria Elizabeth Romana

Excel Pivot Tables provide a tool to summarize, organize, and analyze complex and repetitive data. By using pivot tables, you can make flexible reports that allow users to easily pull just the views they need without requiring a brand new report for every different layout or subset.

Once you lay out the data as you want it, you can drag 'n drop the columns and rows in your summary table to get just the look and feel you want, or alternatively, you can drag 'n drop in the table designer.

In order to create a pivot table, you need to have your data in a single spreadsheet, laid out with one row per data record and a row of unique column headers at the top, like salesman, region, month, product, units sold. There can be no blank rows or columns in the table, but you can have individual items missing.

To create the basic pivot table, simply highlight the entire data table, then select Insert | Pivot Table from the menus. Choose "new sheet" as the destination, and you will be taken to the new sheet with the pivot table designer displayed. At this point, you will see a list of those column headers available to be dragged 'n dropped into the pivot table, as either page filters, column or row headers, or individual data items to be counted, summed, averaged, etc.

Once you decide on a basic layout for your summarized data, any changes you make to the original table will automatically be reflected in the pivot table with a simple right-click and "refresh" on the table (or use the pivot table menu's refresh). This is very handy for what-if scenarios or constantly changing source data.

With your basic layout selected, you can format the items in your pivot table by drawing on the "Pivot Table" design menus. If these menus aren't showing in the Excel menus, be sure to go to the pivot table page and click somewhere in the pivot table. This will make the two menus under "Pivot Table Tools" appear: Options and Design. Design is primarily about making the table look "pretty"; it offers a plethora of color and striping options, as well as subtotal and grand totalling options. The Options menu, on the other hand, is where the real formatting power is.

Under the Options menu and over toward the right, you will see the Field List button. If you click this, you'll get your original layout manager back. Within the layout manager, you can click on the down arrow next to any table value element and then choose "Value Field Settings". This allows you to manipulate that particular element in terms of the calculation used (sum, count, average, etc.) and the numeric format used to display it.

If you want to create a new, calculated field from some of your existing data, choose "formulas" from the Options menu, and then "Calculated Field". This allows you to create a difference or ratio or other combination of two or more existing fields in your table, for example, the percentage change in sales from one month to the next. After you define that calculated field, you can place it and format in the table just like any of the source data fields.

This has been a very brief overview of the power of Excel pivot tables. The important thing to remember is that although it can be time-consuming to get just what you want, you can reuse the table design by simply replacing the source data, and refreshing the pivot table. This makes the table design effort pay off in the long run.
Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Twitter Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Facebook Add A+Brief+Introduction+to+the+Excel+Pivot+Table to MySpace Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Digg A+Brief+Introduction+to+the+Excel+Pivot+Table Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Yahoo My Web Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Google Bookmarks Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Stumbleupon Add A+Brief+Introduction+to+the+Excel+Pivot+Table to Reddit

RSS | Related Articles | Editor's Picks Articles | Top Ten Articles | Previous Features | Site Map

For FREE email updates, subscribe to the Desktop Publishing / Ebooks Newsletter

Past Issues

Printer Friendly
tell friend
Tell a Friend
Email Editor

Content copyright © 2018 by Maria Elizabeth Romana. All rights reserved.
This content was written by Maria Elizabeth Romana. If you wish to use this content in any manner, you need written permission. Contact Laura Nunn for details.


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map


Past Issues

Note: BellaOnline uses cookies to help provide a consistent user experience. Our advertisers may use cookies to help customize ads. Please contact us with any question about our cookie use.

Summertime Foods
Corn on the Cob
Burgers on the Grill
Apple Pie


| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2018 Minerva WebWorks LLC. All rights reserved.

BellaOnline Editor