Microsoft Excel 2016: Part 4

View Options
Introduction to Excel Functions
And Naming Cells

Exercise Part 1-3:

Open a new workbook and enter the data as shown below.

Follow the steps below.

1) Insert two rows above row number 1.

2) Enter the word “Grocery List” in cell A1. Change the font style to and size 24.

3) Enter the word “DATE” in cell A2. Change the font style to .
4) In Cell F4, enter the formula to find the total cost for row 4. (Hint: Qty*Price =C4*E4).
5) Once the answer in cell F4, copy the formula to all rows below. (Hint: double-click on fill-handle)
6) Select cells E4:E12and format as “Currency”.
7) Select cells F4:F12 and format as “Accounting”.
8) Select cell A13 and Type in “Average Price”.
9) Select cell E13 and find out the average price of the price column E by using the =AVERAGE under

the function drop down arrow.

10) Select cell A14 and type in “Grand Total Cost”.

11) Select cell F14and find out the Sum by using the function.
12) Change row heights of row 1 and 14 to “30” by using Format>Row Height in Home>Cells group.
13) Select cell A3:G3. Change the Fill color to dark brown. Change the Font color to white.
14) Select cell A3:G14 and use “Linked Cell” Style.
15) Finally, select columns C through E and change the column width to “8” by using Format>Column
width in Home>Cells group.
16) Open the Grocery List Practice.xlsx workbook. Your spreadsheet should look like GroceryFinal
worksheet.
Copyright © 2020 ASCPL All Rights Reserved Page 1 of 10 MS2016-ExcelPart4 MMS 9/1/2020
View Options - Freezing Panes:
Excel has useful tools to view content from the different parts of your workbook at the same time.
They are called freeze panes (where you can freeze your rows and columns) and split your worksheet.
Let’s use WeekOne worksheet in the same Excel Part4 Functions.xlsx workbook to practice.

Freezing Rows And/Or Columns:

You may want to see certain rows or columns all the time in your worksheet, especially header cells.
By freezing rows or columns in place, you'll be able to scroll through your content while continuing to
view the frozen cells. Assume you want to see the first three rows with header and first column with
category name on it appear at all times no matter how far we scroll to the right or down.
 Select the row below the row(s) and the right of the column you wish to freeze. In our
example, we want to freeze rows 1, 2 and 3, and column A so we'll select cell B4.

 Click the View tab on the Ribbon.

 Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. The
rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet
while continuing to view the frozen rows at the top and scroll to the right while being able to
view the column A at all times.
 To unfreeze rows or columns, click the Freeze
Panes command, then select Unfreeze Panes
from the drop-down menu.
 The second choice Freeze Top Row on the drop-
down list will only freeze the first row (row 1
only). Make the first row still visible on screen is
when you apply this command and your active
cell can be anywhere on your data sheet.
 The last choice Freeze First Column will only
freeze the first column regardless of where your cell selection is when you apply this command.
You must make sure that your 1st column is still visible on screen when you apply this
command.

Copyright © 2020 ASCPL All Rights Reserved Page 2 of 10 MS2016-ExcelPart4 MMS 9/1/2020
Other View Options
Open a New Window: Excel allows you to open multiple windows for a single workbook at the same
time to compare and view the different sections of the workbook. In our example, we'll use this
feature to compare two different worksheets WeekOne and WeekTwo from the same workbook.
Follow instructions below.
 Click the View tab on the Ribbon, then select the New
Window command. A new window for the the
workbook will appear. Notice a number is assigned to
the name of the workbook as

and
in the Title area of the workbook.
 Regardless of which workbook number you are on, click on the
Arrange All command. Arrange Windows dialog box will
appear.
 Select Vertical to compare two different worksheets side by
side in Vertical position. You can now compare different
worksheets from the same workbook across windows. In our
example, we'll select WeekOne worksheet in one window and
WeekTwo in another to compare by using worksheet scrollbar on bottom left.

 When you are done comparing, close either one. The number assignment at the end of the
Title will disappear. Note: you can use New Window command to simultaneously open as
many worksheets you want to compare at the same time.

Copyright © 2020 ASCPL All Rights Reserved Page 3 of 10 MS2016-ExcelPart4 MMS 9/1/2020
Introduction to Functions:
A Function is a predefined formula that performs calculations using specific
values in a particular order. Excel includes many common functions under the
Home tab that can be useful for quickly finding the sum, average, count,
maximum value, and minimum value for a range of cells. Let’s examine the
different parts of a sum function and how to create arguments to calculate
values and cell references.
= Sum (A10:A20)

Equation Function Argument

Equation: is required at beginning of every formula.

Function: is a predefined formula; in this example, to sum or add what is stated in the argument.
Argument: contains the information you want to calculate and it must be enclosed within a parenthesis.
It can refer to both individual cells and cell ranges, in this example, referring to cell range A10 through
A20. You can include one argument or multiple arguments, depending on the syntax required for the
function.
Basic Mathematical Functions
AVERAGE Finds the average within a range of cells. =AVERAGE(A10:A20)
Finds the number of cells within a range of cells that
COUNT =COUNT(A10:A20)
contain values (numbers) in them.
The COUNTA function counts cells containing any type
of information, including error values and empty text
(""). For example, if the range contains a formula that
COUNTA =COUNTA(A3:A15)
returns an empty string, the COUNTA function counts
that value. The COUNTA function does not count
empty cells.
COUNTBLANK Counts empty cells in a specified range of cells. =COUNTBLANK(B3:B15)
MAXIMUM Finds the largest number within a range of cells. =MAX(D3:D15)
MINIMUM Finds the smallest number within a range of cells. =MIN(D3:D15)
SUM Finds the total or sum within a range of cells. =SUM(A10:A20)
Basic Date Functions
Displays the computer’s current date and time. The
NOW() formula will update the date and time next time the =NOW()
workbook is opened.
Displays the computer’s current date. The formula will
TODAY() =TODAY()
update the date next time the workbook is opened.

Copyright © 2020 ASCPL All Rights Reserved Page 4 of 10 MS2016-ExcelPart4 MMS 9/1/2020
 Creating Basic Functions:
Single Argument Exercise: Open WeekOne worksheet Excel Part4 Functions.xlsx to practice creating
arguments. Make sure to freeze the pane by selecting cell B4 so we can see header rows.
AVERAGE Function:
 In Cell E33 we want to calculate the average price per unit. Select Cell E33.
 Click on the drop-down arrow next to the Sum function in the Editing Group under the Home tab.
Select Average.
 The selected function will appear in the cell. If logically placed, the Average command will
automatically select a cell range for the argument. In our example, cells E4:E32 were selected
automatically. In our case, there is no data in E31 and E32. You can manually fix the argument
directly in the formula bar area by using the backspace button on your keyboard and change the cell
address from E32 to E30. (OR you can use your mouse; click and drag to include the desired cell range
into the argument as well.)

 Press Enter on your keyboard. The function will be calculated, and the result will appear in the cell. In
our example, the average of E4:E30 is $2.97.

COUNT Function:
 Select Cell C32.
 Since the column includes numbers, we will select the Count function from
the list. We are using the Count Function here to count how many items
are on the list. We are NOT ADDING the items.
 The formula appears =COUNT(C4:C31). Fix the formula to include up to
C30 only.
 Hit Enter. The count should give you 27 meaning there are 27 items
where there is a number in the Quantity column. Note the Count
Function will count any number including zero. But it will not count either blank nor text.

You can find the similar answer on Column B, the Item column with text by using
the CountA Function.

 Select Cell B31.

 Since the column includes texts, we are using CountA function to count
how many items are on the list. The answer should be the same as 27
when you hit Enter.

Copyright © 2020 ASCPL All Rights Reserved Page 5 of 10 MS2016-ExcelPart4 MMS 9/1/2020
 COUNT BLANK Function:
 Select Cell B31 again.
 Change the CountA function in that cell from CountA to Countblank in the formula bar area.
Hit Enter. You will have the answer zero as there is no blank cell value yet in column B.
 Delete the content of the Cell B30 by using the Delete button. Now it should say 1 in Cell B31
as there is one blank cell in the range B4:B30.

SUM Function: Find out the sum of Total Cost column, Column F.
 Click on Cell F34.
 Select the Sum function from the list.
 Fix the cell range in the formula bar using the backspace to change F33 to F30 since there is no
data F31:F33.
 Hit Enter. The sum of Total Cost in our example is: $180.67.

MAXIMUM Function: Find out the most expensive item in column E (Price Column) in cell E35 by using
the =MAX(E4:E30). The result should be: $6.99 for Coffee.

MININUM Function: Find out the least expensive item in column E (Price Column) in cell E36 by using
the =MIN(E4:E30). The result should be: $.49 for Bananas.

Multiple Argument Example: A function can contain multiple

arguments. Each argument has to be separated by the
“comma”. For example, in Cell F37, we want to find out total
costs for all “Produce”, “Pasta” and “Supplies” selectively. The function =SUM(F4:F8,
F20:F21;F29:F30) will add the values of all the cells in the three arguments. The answer should be
$40.19.

Now Function: Displays the computer’s current date and time. The formula will update the date and
time next time the workbook is opened. Select Cell C2 and type in =Now()
Today Function: Displays the computer’s current date. The formula will update the date next time
the workbook is opened. Select Cell B2 and type in =Today()

Complex Function/Formula:
There are hundreds of functions in Excel and
you can search them by category, such as
Financial, Logical, Text, Date & Time, and
more from the Function Library on the
Formulas tab. The ones you use most
frequently will depend on the type of data
your workbook contains. To access the Function Library, select the Formulas tab on the Ribbon. The
Function Library will appear. Click on each category to explore some of the different types of functions
that will be helpful as you create new projects.

Copyright © 2020 ASCPL All Rights Reserved Page 6 of 10 MS2016-ExcelPart4 MMS 9/1/2020
For complex formulas, it’s not easy to remember which
function and which arguments to use for each task.
Fortunately, the Insert Function feature in Excel helps you
with this. In cell H4, we will use the same example above to
count the number of cells to get the answer “how many
Produce are ordered in column A”. Since we do not know
which functions to use, we can simply type in a natural
question like “count cells” in the “Search for a function:”
box and hit “Go” button. From suggested functions
appeared in the “Select a Function” box, an explanation on
“COUNTIF” function suits our need – We want to count how
many cells in Column A (range) includes the word “Produce”
(criteria). (If we want more explanation and see the
examples, we can click on Help on this Function box and a
new Help window will pop up with more detailed explanation and examples.) Follow the steps below.
 Select Cell H4.
 Click on Formulas>Insert Function.
 Select COUNTIF from the suggested functions.
 Click on OK button.
 Function Arguments dialog box will come up. In the Range box, you want to count all cells in
Column A. So, simply select the entire column A.
 In the Criteria box, you want to include all cells in column A with the word “Produce” in it. You can
type in the word “Produce” yourself or simply select one of the cells that include “Produce” in it, in
this example, point and click on cell A4. You can see the word “Produce” shows up on the right of
the Criteria box and the result shows “5” already.
 Click on OK button.
 Click on OK. The result should be 5.

 Use the same function to count “Dairy” in cell H9; “Meat” in cell H13; “Drinks” in cell H16; “Pasta”
in cell H20; “Soup” in cell H22; “Bakery” in cell H24; “snacks” in cell H25; and “Supplies” in H29.

Copyright © 2020 ASCPL All Rights Reserved Page 7 of 10 MS2016-ExcelPart4 MMS 9/1/2020
Display Formulas on your worksheet:
You can display the formulas in your worksheet by
using the Show Formulas command in the Formula
Auditing group under the Formulas tab. All column
widths will be noticeably widened to show values (if
they are fixed value) and all formulas.

To get back to normal display, click on the Show Formulas one more time.

Copyright © 2020 ASCPL All Rights Reserved Page 8 of 10 MS2016-ExcelPart4 MMS 9/1/2020
Appendix:
“Order of Operations” to more complex mathematical expressions:
A complex mathematical expression has more than one mathematical operator, such as 5+2*8. When
there is more than one operation in a formula, the order of operations tells Excel which operation to
calculate first. In order to use Excel to calculate complex formulas, you will need to understand the
order of operations as follows:

Excel calculates formulas based on the following order of operations:

1. Operations enclosed in parentheses

2. Exponential calculations (3^2, for example)
3. Multiplication and division, whichever comes first
4. Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally.

So in this following example to solve - 10+(6-3)/2^2*4-1, the order of operations will be carried out
according to the order stated above. See the steps below.
Step 1)

Step 2)

Step 3)

Copyright © 2020 ASCPL All Rights Reserved Page 9 of 10 MS2016-ExcelPart4 MMS 9/1/2020
Step 4)

Step 5)

Step 6)

Step 7)

Copyright © 2020 ASCPL All Rights Reserved Page 10 of 10 MS2016-ExcelPart4 MMS 9/1/2020