Latest posts by techwriter (see all)
- How to Number Your Documents Properly – A Document Numbering Strategy - April 24, 2017
- How to Avoid Repeating Words in a Headline - April 18, 2017
- Leveraging Multi-Function Printers With Document Imaging Software - April 10, 2017
© Ugur Akinci Creating a Pivot Table in MS Excel 2010 is very easy if your data is in proper TABULAR LAYOUT FORMAT. Here is a sample table representing the imaginary Atlantic and Pacific region sales of a fictitious fish company (see the BONUS at the end):
As you can see, it’s not easy to look at this data and answer a simple question like “which fish brand is more profitable in Atlantic region?”, or, “Which fish is more profitable in Quarter 2, regardless of region?” Such “trick questions” is easy to answer when you re-format your data as a Pivot Table.
(1) Select your table. (2) Go to Insert tab on the ribbon and click the PivotTable button on the Tables pod. Create Pivot Table dialog box will display:
(3) Use the default “New Worksheet” setting and click OK to display the Pivot Table wizard in a new worksheet:
(4) Select the fields (variables) you’d like to include in your Pivot Table by putting a check mark next to them. REMEMBER: Your original data remains intact no matter what you do with your Pivot Table. However, it’s always good practice to keep a backup copy of all your important data before manipulating them. Your screen now should look something like this:
(5) Now you can click and drag the BRAND field from the ROW labels to the COLUMN labels to create a different Pivot Table:
(6) Now click and drag the QUARTER field from the ROW label to the COLUMN label and drag the BRAND label back to the ROW labels to create yet another Pivot Table:
As you can see, different Pivot Tables display different relationships between selected data, which is the whole idea to this exercise. To make these relationships easily detectable, you can select the part of the table that interests you and then switch on different visualization options from HOME Tab > CONDITIONAL FORMATTING. Here is one such example:
Here is the ORIGINAL MS Excel 2010 WORKBOOK for this exercise. Download it and play around to understand how exactly a Pivot Table works.