Generally, a pivot table would be the right choice in any of the following situations:
- There is a large amount of transactional data that’s becoming more difficult to analyze and summarize in a meaningful way.
- You need to find relationships and groupings within your data.
- You need to find a list of unique values for one field in your data.
- You need to find data trends using various time periods.
- You anticipate frequent requests for changes to your data analysis.
- You need to create subtotals that frequently include new additions.
- You need to organize your data into a format that’s easy to chart.
|
First we will convert a range to a table. Once we have a table, we will use it to create a pivot table. We’re still using data file, FIRST RANGE TO TABLE EXERCISE. Select tab XYZ COMPUTER SALES.
STEP 1: Click on any cell of the table. From the Ribbon, select the Insert tab, in the Tables section, select the Table icon (FIGURE 1) The Create Table dialog box will appear (FIGURE 2). In the data range window you should see $A$1:$L$109. If the range is incorrect, select this range with your mouse. Ignore the dollar symbols in the range. They simply designate an absolute address. The table produced is shown in FIGURE 3. We can see that this table contains all of the cells specified (A1:L109) in the previous step (FIGURE 2). |
 FIGURE 1
 FIGURE 2
|
|
STEP 2: Click on one of the cells of the table. You must do this in order to view the Table Design contextual tab on the Ribbon (FIGURE 4).
Up on the Ribbon, click on the contextual tab, Table Design On the far left end of the Ribbon, underneath the File tab, locate the Table Name window in the Properties section (FIGURE 5). Replace the default table name with XYZ_SALES (you cannot have spaces in a table name, so we use an underscore instead of a space). Press the Enter key |
 FIGURE 4
 FIGURE 5
|
|
STEP 3: On the Ribbon select the Insert tab, go to the Tables section and select Pivot Table icon (FIGURE 6).
|
 FIGURE 6
|
|
For the “Select a table or range” you need to either enter the range of cells used from the data table, or enter the table name (Excel will automatically populate window). If the data already entered is not what you want, enter either the range or the table name (FIGURE 7).
For the “Choose where you want the PivotTable report to be placed, in most cases you will want to keep default, “New Worksheet” Leave the bottom box “Add this data to the Data Model” unchecked. Select OK button. |
 FIGURE 7
|
|
STEP 4:
This is what appears when you first begin creating a pivot table (FIGURE 8). The graphic you see simply states that to build a pivot table, click in this area to begin the pivot table. Once you begin selecting the fields from the fields list and see them appear on the screen is when you are actually building the pivot table. The Pivot Table Fields List box, shown in FIGURE 9, shows the fields that our pivot table can use. They are the same fields contained in our data table used to create the pivot table. |
 FIGURE 8
|
 FIGURE 9
|
STEP 5: The Category field will go into the Rows quadrant.
Check the Category box.
Notice that the Category field is now in the Rows quadrant.
Let’s say your boss wants to see the data by market. We need to put the Market field in the Columns quadrant. Since Excel would automatically place the Market field in the Rows quadrant, we will have to manually move the Market field down into the Columns quadrant.
REMEMBER: Fields that contain text will automatically be placed in the Rows quadrant when you check that field’s box in the Pivot Table Field list.
STEP 6: Click and drag the Market field down into the Columns quadrant.
He also wants to be able to see a pivot table for each month, so the Month field will be used in our Filter quadrant.
STEP 7: Click and drag the Month field down into the Filters quadrant.
(you may have to scroll down to see it) We’re measuring our Sales Qty and Sales Total fields so that will be important. Sales Qty is data so let’s start with it. Since Sales Qty is data, it goes into the Values quadrant.
STEP 8: Check the Sales Qty box at the top.
Notice that the Sales Qty field automatically goes into the Values quadrant.
REMEMBER: Fields that contain numbers will automatically be placed in the Values quadrant when you check that field’s box in the Pivot Table Field list.
