Office 2016– Excel Basics 04
Video/Class Project #16
Excel Basics 4: PivotTables & SUMIFS Function to Create Summary Reports (Intro Excel #4)
Goal in video # 4: Learn how to create a summary report for adding with one condition with:
1. SUMIFS Function
and
2. The PivotTable feature
Table of Contents
1) What Excel can do ........................................................................................................................................................... 2
ii. Data Analysis ................................................................................................................................................................... 2
2) Define Proper Data Set ................................................................................................................................................... 3
3) SUMIFS function to create Regional Sale Report ............................................................................................................ 4
4) Pivot Table to create Regional Sale Report ..................................................................................................................... 5
5) Drag & Drop Fields in PivotTable Field Task Pane......................................................................................................... 10
6) Summary of how to create PivotTable for Video 04 ..................................................................................................... 11
7) Compare SUMIFS and PivotTable ................................................................................................................................. 11
8) Picture of Data Analysis ................................................................................................................................................ 12
Page 1 of 12
1) What Excel can do
i. Make Calculations: like calculate % Grade or Net Income or AVERAGE.
1. Like we did in video #1 & #2:
ii. Data Analysis: Converting Raw Data into Useful Information, like taking table of data and
creating a Reginal Sales Report.
1. Like we will do in this video:
SUMIFS
Function
Lots of
PivotTable
Raw Data
Reginal Sales
Report is Useful
Information
2. You may hear the term “Data Analysis” or “Business Intelligence”. They are synonyms.
Page 2 of 12
2) Define Proper Data Set
Page 3 of 12
3) SUMIFS function to create Regional Sale Report
i. Type and Format words, like:
ii. Type out the criteria / conditions for the SUMIFS function, like:
iii. Because we are copying the formula we must lock the ranges of cells using the F4 key (Absolute Cell
Reference), but keep the cell for the criteria as a Relative Cell Reference, like:
iv. Then you copy the formula down the column to get final Reginal Sales Report, like:
v. Using SUMIFS requires that you type out each criteria and create a formula with Relative and Absolute
Cell References.
Page 4 of 12
4) Pivot Table to create Regional Sale Report
i. The Proper Data Set we are using has Column Headers (Field Names) for “Date”, “Region”, “SalesRep”,
and “Sales” and looks like:
The names in the first row are
called:
“Column Headers”
or
“Field Names”
ii. To Start a PivotTable, you click in one cell in the Proper Data Set. You can click in any one cell. For the
picture here, cell C15 is selected, like:
iii. Click on the PivotTable button in the Table group in the Insert Ribbon Tab, like:
Page 5 of 12
iv. The Create PivotTable dialog box will appear. Because we had a single cell selected in our PivotTable, the
Table/Range text box has the correct range. Click in the Existing Location / Location text box, then with your
Selection Cursor, click in cell F12. The dialog box should look like this::
v. After you click OK, you will see an Empty PivotTable appear in the sheet and you will see the PivotTable Field List.
Notice that the Field Names in the Proper Data Set appear in the PivotTable Field List, like this:
Page 6 of 12
vi. Using the Check Box for the “Region” Field in the PivotTable Field List, check the box for “Region”, and a unique list
of Region Names will appear in the PivotTable Report, like:
Unique List
or
Distinct List
vii. Using the Check Box for the “Sales” Field in the PivotTable Field List, check the box for “Sales”, and the total sales
for each Region will appear in the PivotTable Report, like:
viii. Notice that the label above the Region names says “Row Labels”. This is not a smart name for our report. We would
like to change it to show the Field Name / Column Header Name.
ix.
Page 7 of 12
x. With a cell selected in the PivotTable, click on PivotTable Tools Design Ribbon Tab, go to the Layout group, like:
xi. Click drop-down for Report Layout and then click on “Show in Tabular Form”.
xii. After we choose “Show in Tabular Form”, the Field Name / Column Header Name will show up in report. We can
see our Field Name / Column Header Name “Region”, like:
Page 8 of 12
xiii. Now We need to add Number Formatting to the Sales Field.
xiv. Click in one cell in the Sum of Sales column, like:
xv. Right-click the cell and click on “Number Format…”, like:
xvi. In the Number Formatting dialog box select the Number Formatting that you would like and then click OK. Be sure
to notice that because we selected the “Number Format…” option in the previous step, the dialog box has only one
tab, and that tab is “Number” (this is a different dialog box than the Format Cells dialog box that has six tabs, and it
will add Number Formatting to the actual “Sum of Sales” area of the PivotTable.
Page 9 of 12
xvii. The finished Report looks like this:
5) Drag & Drop Fields in PivotTable Field Task Pane
i. Rather than use the Check Boxes in the PivotTable Field Task Pane, you can have more control by using
your Move Cursor to drag & drop fields in the PivotTable Field Task Pane, like in this picture:
ii.
Page 10 of 12
6) Summary of how to create PivotTable for Video 04
i. Click in one cell in Proper Data Set
ii. Insert Ribbon Tab, Tables group, PivotTable button.
iii. From Field List, drag field name to Rows area or Columns area. These are the conditions/criteria for the
calculation in the Values area of the PivotTable.
iv. From Field List drag the field you would like to make a calculation on to values area.
v. With a cell selected in the PivotTable, click on PivotTable Tools Design Ribbon Tab, go to the Layout
group, click drop-down for Report Layout and then click on “Show in Tabular Form”.
vi. To add Number Formatting to the Values area of the PivotTable, click in one cell in the Values area of
the PivotTable, Right-click the cell and click on “Number Format…”, then in the Number Formatting
dialog box select the Number Formatting that you would like and then click OK.
7) Compare SUMIFS and PivotTable
i. Advantage of PivotTable:
1. Quick and easy to make.
2. Conditions or Criteria in Rows area are created automatically
ii. Disadvantage of PivotTable:
1. If source data changes, you must right-click PivotTable and point to Refresh.
iii. Advantage of SUMIFS:
1. If source data changes, formulas update instantly.
iv. Disadvantage of SUMIFS:
1. Have to type out conditions/criteria for Rows area
Page 11 of 12
8) Picture of Data Analysis
Page 12 of 12
