Using the Pivot Table in Microsoft Excel
At its most basic, a pivot table or report will display your data and present it in a form where you can more easily analyze your data. Once it is set up, you can switch the way you view the data even further by either filtering the data or dragging and dropping the field headings into different positions. The entire view can be pivoted, hence the name, pivot table. In addition, you can add formulas to calculate totals, averages and other calculations.
Setting up and using a pivot table is simple. First start with a list of data, either in the spreadsheet you are working on or an external source. Let’s start with a list within the current spreadsheet. Click anywhere within that list and then go up to the menu bar and choose “Data” and then “PivotTable and PivotChart Report.” In step one, choose “Microsoft Excel list or database” and also “PivotTable” and then click “next”.
In step two, the wizard will automatically select your existing list. If you want to expand that list beyond the current data, you may do so. You might want to do this if you anticipate adding rows of data in the future.
In step three, choose either to create the table in a new worksheet or within the existing worksheet. Choose whichever option works best for you. By choosing a new worksheet, you will have a clean sheet dedicated to this particular table. If the data lends itself to be grouped with other data, you might choose to include it within the other data’s layout.
While still remaining in the step three screen of the wizard, click on the “layout” button. Here is where you will layout your fields and data. Simply drag each field button to the area of your choice. You can either have a field located in a page, column, row or data section. How you want to view the data will determine where you place it. Go ahead and experiment with various layouts, you can always rearrange them to better suit your needs.
While still in the layout screen, double click a field button. A new menu opens giving you options on how you want to summarize the data. Perhaps you want to count the number of transactions in which case you would choose “count”. If you prefer a total, choose “sum”. In addition, you can choose how to show the data whether as a percentage, a running total, regular text or a variety of other ways by simply clicking on the “options” button. Click “OK” to exit the layout screen.
Another button in step three is the “options” button. This offers even more options including formatting options as well as data options. If the data source changes frequently, you can set the data to refresh at set intervals or each time the sheet is opened. This ensures you are working with the most current data.
Once you have the options desired, click “OK” and then “Finish”. You will now see your completed pivot table.
