Excel and Pivot Tables
When doing so, keep the
following points in mind:
·
At least three columns of data are needed to create a pivot
table.
·
It is important to enter data correctly. Errors, caused by
incorrect data entry, are the source of many problems related to data
management.
·
Leave no blank rows or columns when
entering the data. This includes NOT leaving a blank row
between the column headings and the first row of data.
Creating
the Pivot Table
1.
Drag select cells A2 to D12 to highlight them.
2.
Click on the Insert tab of the ribbon.
3.
Click on the down arrow at the bottom of Pivot Table button to
open the drop down list.
4.
Click on Pivot Table in the list to open
the Create Pivot Table dialog box.
5.
By pre-selecting the data range A2 to F12, the Table/Range line
in the dialog box should be filled in for us.
1.
Choose Existing Worksheet for the location of
the pivot table.
2.
Click on the Location line in the dialog box.
3.
Click on cell D16 in the worksheet to enter that cell reference
into the location line.
4.
Click OK.
A blank pivot table
should appear on the worksheet with the top left corner of the pivot table in
cell D16.
The Pivot Table
Field List panel should open on the right hand side of the Excel
window.
At the top of the Pivot
Table Field List panel are the field names ( column headings ) from
our data table. The data areas at the bottom of the panel are linked to the
pivot table.
Adding
Data to the Pivot Table
Note: For help with these
instructions see the image example above.
You have two choices when
it comes to adding data to the Pivot Table:
·
Drag the field names from the Pivot Table Field List panel
and drop them on the Pivot Table in the worksheet.
·
Drag the field names to the bottom of the Pivot Table
Field List panel and drop them in the data areas.
The data areas in
the Pivot Table Field List panel are linked to corresponding
areas of the pivot table. As you add the field names to the data areas, your
data is added to the pivot table.
Depending on which fields
are placed in which data area, different results can be obtained.
Tutorial
Steps:
1.
Drag the field names to these data areas:
·
Total Sales to the Report Filter area
·
Region to the Column Labels area
·
Sales Rep to the Row Labels area
·
Orders to the Values area
2.
Once completed, the Pivot Table should have the data laid out in
the same order as the example in step 1 of
this tutorial.
Filtering
the Pivot Table Data
The Pivot Table has built
in filtering tools that can be used to fine tune the results shown by the Pivot
Table.
Filtering data involves
using specific criteria to limit what data is displayed by the Pivot Table.
Tutorial
Steps:
1.
Click on the down arrow next to the Region heading
in the Pivot Table to open the filter's drop down list.
2.
Click on the check box next to the Select All option
to remove the check mark from all the boxes in this list.
3.
Click on the check boxes next to the East and North options
to add check marks to these boxes.
4.
Click OK.
5.
The Pivot Table should now show only the order totals for the
sales reps that work in the East and North regions.
Changing
the Pivot Table Data
To change the results
shown by the Pivot Table:
1.
Rearrange the pivot table by dragging the data fields from one
data area to another in the Pivot Table Field List panel.
1.
Apply filtering to get the desired results.
Tutorial
Steps:
1.
Drag the field names to these data areas:
·
Orders to the Report Filter area
·
Sales Rep to the Column Labels area
·
Region to the Row Labels area
·
Total Sales to the Values area
2.
Click on the down arrow next to the Region heading
in the Pivot Table to open the filter's drop down list.
3.
Click twice on the check box next to the Select All option
to first add and then remove the check marks from all the boxes in this list.
4.
Click on the check box next to the West option
to add a check mark to this box.
5.
Click OK.
6.
The Pivot Table should now show the total sales for only those
sales reps that work in the West region.
No comments:
Post a Comment