Do you use Excel to report and analyze large chunks of data? Don’t waste hours figuring out the proper formulas to use to report your data – use PivotTables instead, and extract the data you need in minutes.
PivotTables are an easy way to summarize data, and see how that data relates and corresponds to other gathered data. What’s more, they allow you to easily manage your spreadsheet data simply by dragging and dropping.
Introduced in Excel 2000, PivotTables aren’t new; they’re just not well known. While it was a handy feature even in 2000, the updates that Microsoft has made to PivotTables in Excel 2007 and 2010 make them a more robust business tool today than they were at their inception.
To use PivotTables, setup your data on one worksheet. (A worksheet is one tab within Excel. Example Sheet1, Sheet2….) Headers are very important! Make sure that each column has a header classifying the data. Do not leave a blank space between the header and the first piece of data.
Example:
PivotTable Data Setup
Disclaimer: These instructions are based off of Excel 2010. If you have a different version, you may have to access PivotTables differently.
Once your data is compiled, click on Insert, then click on PivotTable. This will bring up the Create PivotTable screen.
Select the table or range you want to use. To do this, click on the button with the red arrow, to the right of the Table/Range field. It will minimize this area to one long field. Click on Sheet1 (or whichever sheet contains your original data). Then, select the fields you would like to use in your PivotTable. Once selected, click the button with the red down arrow located to the right of your selection.
Next, choose where you want the PivotTable to be placed. Select Existing Worksheet, then select the fields where you would like the PivotTable to be located. You do this the same way you selected your table/range, by clicking the red arrow next to location, selecting Sheet2 (or whichever worksheet you would like to use), highlighting the fields where you would like the PivotTable to appear, and then clicking the red arrow again to return to the Create PivotTable screen.
Your Create PivotTable screen should look something like this. Once it’s setup, click on Okay to create the PivotTable.
PivotTable Setup
You should have a screen that says “PivotTable, to build a report, choose fields from the PivotTable Field List.” To the right, the headers for the selected fields will appear. Choose which fields you want on your report. Then, drag and drop the fields to change the order of what the PivotTable reports.
The PivotTable Field List will allow you to arrange your fields in the order you would like to see them. To change your layout, simply change the field order by dragging and dropping.
PivotTable Options
While PivotTables look easy, they do have a slight learning curve. But don’t let this
deter you. Once you learn how to access your data using the power of PivotTables, you’ll wonder how you ever got along without them.
Julie Strier is a freelance writer who is interested in making technology easier to understand. Email: [email protected]. Website: www.mybusinesswriter.com.
Trackbacks/Pingbacks