Instructional Objectives for this activity:
• Extract data from worksheets.
• Validate input data.
• Verify data results.
This week's lab work is to construct a final project.
The final is broken up into two parts. You have the entire
week to complete it. You can submit each portion individually
in your Lab Work Dropbox throughout the week.
After reviewing the textbook chapters and links from
previous weeks in eCollege, complete the final project as
instructed. You will create two workbooks:
•
•
A budget using:
o
Cases and Places 3: Analyzing Profit Potential,
EX 135.
An Excel spreadsheet that uses functions and problem
solving skills:
o Cases and Places 1: Inventory Level Priority, EX
359.
Save your Excel workbook with a file name that includes your name
course code-section number title.
•
For example: JaneSmith CGS2573C-12 LabWork #12.xlsx
Your Excel workbooks should be saved with .xlsx extension.
To submit your Individual Work, go to the Dropbox and click
"Submit Assignment."
Submit this document to the Week 12: Lab Work basket in the
Dropbox.
Microsoft Excel 2010
2
Formulas, Functions,
and Formatting
Objectives
You will have mastered the material in this chapter when you can:
• Enter formulas using the keyboard
• Add conditional formatting to cells
• Enter formulas using Point mode
• Change column width and row height
• Apply the AVERAGE, MAX, and MIN
functions
• Check the spelling in a worksheet
• Verify a formula using Range Finder
• Apply a theme to a workbook
• Apply a date format to a cell or range
• Set margins, headers, and footers
in Page Layout view
• Preview and print versions of
a worksheet
Microsoft Excel 2010
2
Formulas, Functions,
and Formatting
Introduction
In Chapter 1, you learned how to enter data, sum values, format a worksheet to make
it easier to read, and draw a chart. This chapter continues to highlight these topics and
presents some new ones.
The new topics covered in this chapter include using formulas and functions to
create a worksheet. A function is a prewritten formula that is built into Excel. Other
new topics include option buttons, verifying formulas, applying a theme to a worksheet,
adding borders, formatting numbers and text, using conditional formatting, changing the
widths of columns and heights of rows, spell checking, using alternative types of worksheet
displays and printouts, and adding page headers and footers to a worksheet. One alternative
worksheet display and printout shows the formulas in the worksheet instead of the values.
When you display the formulas in the worksheet, you see exactly what text, data, formulas,
and functions you have entered into it.
Project — Worksheet with Formulas and Functions
The project in this chapter follows proper design guidelines and uses Excel to create
the worksheet shown in Figure 2 – 1. The Mobile Masses Store opened its doors when
consumer demand for mobile devices, such as mobile phones and PDAs, had just begun.
The store’s owners pay each employee on a biweekly basis. Before the owners pay the
employees, they summarize the hours worked, pay rate, and tax information for each
employee to ensure that the business properly compensates its employees. This summary
includes information such as the employee names, hire dates, number of dependents,
hours worked, hourly pay rate, net pay, and tax information. As the complexity of
the task of creating the summary increases, the owners want to use Excel to create
a biweekly payroll report.
EX 66
Microsoft Excel 2010
worksheet
with formulas
and functions
Figure 2 –1
Recall that the first step in creating an effective worksheet is to make sure you
understand what is required. The people who will use the worksheet usually provide
the requirements. The requirements document for The Mobile Masses Store Biweekly
Payroll Report worksheet includes the following needs: source of data, summary of
calculations, and other facts about its development (Figure 2 – 2 on the following page).
EX 67
EX 68 Excel Chapter 2 Formulas, Functions, and Formatting
REQUEST FOR NEW WORKSHEET
Date Submied:
April 16, 2012
Submied By:
Samuel Snyder
Worksheet Title:
The Mobile Masses Store Biweekly Payroll Report
Needs:
An easy-to-read worksheet that summarizes the company’s biweekly payroll
(Figure 2-3). For each employee, the worksheet is to include the employee’s
name, hire date, dependents, hours worked, hourly pay rate, gross pay,
federal tax, state tax, net pay, and total tax percent. The worksheet also
should include totals and the average, highest value, and lowest value for
column of numbers specified below.
The data supplied by Samuel includes the employee names, hire dates, hours
worked, and hourly pay rates. This data is shown in Table 2-1 on page EX 72.
The following calculaons must be made for each of the employees:
1. Gross Pay = Hours Worked × Hourly Pay Rate
2. Federal Tax = 0.22 × (Gross Pay – Dependents * 24.32)
3. State Tax = 0.04 × Gross Pay
4. Net Pay = Gross Pay – (Federal Tax + State Tax)
5. Tax % = (Federal Tax + State Tax) / Gross Pay
6. Compute the totals for hours worked, gross pay, federal tax, state tax, and
net pay.
7. Compute the total tax percent.
8. Use the AVERAGE funcon to determine the average for dependents, hours
worked, hourly pay rate, gross pay, federal tax, state tax, and net pay.
9. Use the MAX and MIN funcons to determine the highest and lowest
values for dependents, hours worked, hourly pay rate, gross pay, federal tax,
state tax, net pay, and total tax percent.
Source of Data:
Calculaons:
Approvals
Approval Status: X
Approved By:
Approved
Rejected
Julie Adams
Date:
April 23, 2012
Assigned To:
J. Quasney, Spreadsheet Specialist
Figure 2–2
Overview
As you read this chapter, you will learn how to create the worksheet shown in
Figure 2 – 1 by performing these general tasks:
• Enter formulas and apply functions in the worksheet
• Add conditional formatting to the worksheet
• Apply a theme to the worksheet
• Set margins, and add headers and footers to a worksheet
• Work with the worksheet in Page Layout view
• Change margins on the worksheet
• Print a section of the worksheet
Plan
Ahead
General Project Decisions
While creating an Excel worksheet, you need to make several decisions that will determine
the appearance and characteristics of the finished worksheet. As you create the worksheet
necessary to meet the requirements shown in Figure 2–2, you should follow these general
guidelines:
1. Plan the layout of the worksheet. Rows typically contain items analogous to items in a list.
A name could serve as an item in a list, and, therefore, each name could be placed in a row.
As a list grows, such as a list of employees, the number of rows in the worksheet will increase.
Information about each item in the list and associated calculations should appear in columns.
2. Determine the necessary formulas and functions needed. Calculations result from
known values. Formulas for such calculations should be known in advance of creating
a worksheet. Values such as the average, highest, and lowest values can be calculated
using Excel functions as opposed to relying on complex formulas.
3. Identify how to format various elements of the worksheet. The appearance of the
worksheet affects its ability to express information clearly. Numeric data should be
formatted in generally accepted formats, such as using commas as thousands separators
and parentheses for negative values.
4. Establish rules for conditional formatting. Conditional formatting allows you to format a
cell based on the contents of the cell. Decide under which circumstances you would like
a cell to stand out from related cells and determine in what way the cell will stand out.
5. Specify how the hard copy of a worksheet should appear. When it is possible that a
person will want to create a hard copy of a worksheet, care should be taken in the
development of the worksheet to ensure that the contents can be presented in a
readable manner. Excel prints worksheets in landscape or portrait orientation, and
margins can be adjusted to fit more or less data on each page. Headers and footers
add an additional level of customization to the printed page.
In addition, using a sketch of the worksheet can help you visualize its design. The
sketch for The Mobile Masses Store Biweekly Payroll Report worksheet includes a title,
a subtitle, column and row headings, and the location of data values (Figure 2 – 3 on the
following page). It also uses specific characters to define the desired formatting for the
worksheet, as follows:
1. The row of Xs below the leftmost column defines the cell entries as text, such as
employee names.
2. The rows of Zs and 9s with slashes, dollar signs, decimal points, commas, and
percent signs in the remaining columns define the cell entries as numbers. The
Zs indicate that the selected format should instruct Excel to suppress leading 0s.
The 9s indicate that the selected format should instruct Excel to display any
digits, including 0s.
3. The decimal point means that a decimal point should appear in the cell entry and
indicates the number of decimal places to use.
4. The slashes in the second column identify the cell entry as a date.
5. The dollar signs that are not adjacent to the Zs in the first row below the column
headings and in the total row signify a fixed dollar sign. The dollar signs that are
adjacent to the Zs below the total row signify a floating dollar sign, or one that
appears next to the first significant digit.
BTW
When necessary, more specific details concerning the above guidelines are presented at
appropriate points in the chapter. The chapter also will identify the actions performed and
decisions made regarding these guidelines during the creation of the worksheet shown in
Figure 2–1 on page EX 67.
Aesthetics versus
Function
The function, or purpose,
of a worksheet is to
provide a user with direct
ways to accomplish tasks.
In designing a worksheet,
functional considerations
should come before visual
aesthetics. Avoid the
temptation to use flashy or
confusing visual elements
within the worksheet.
One exception to this
guideline occurs when
you may need to draw the
user’s attention to an area
of a worksheet that will
help the user more easily
complete a task.
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 69
EX 70 Excel Chapter 2 Formulas, Functions, and Formatting
The Mobile Masses Store
Biweekly Payroll Report
Hire
Employee Date Dependents
xxxxxxxxx 99/99/99
99
Hours Hourly
Worked Pay Rate
99.99 $ ZZ9.99
Totals
999.99
Average
Highest
Lowest
99
99
99
Xs indicate text
data
Gross Pay
$ ZZ,ZZ9.99
Federal
Tax
$ ZZ9.99
State Tax
$ ZZ,ZZ9.99
Net Pay
$ ZZ,ZZ9.99
Tax %
Z9.99%
$ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99
Z9.99%
99.99 $ ZZ9.99 $ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99 $ ZZZ,ZZ9.99
9s indicate
numeric data
Zs indicate numeric
data with 0s suppressed
$ adjacent to Z indicates
floating dollar sign
Z9.99%
$ not adjacent to Z indicates
a fixed dollar sign
Figure 2–3
6. The commas indicate that the selected format should instruct Excel to display
a comma separator only if the number has enough digits to the left of the
decimal point.
7. The percent sign (%) in the far-right column indicates a percent sign should
appear after the number.
With a good comprehension of the requirements document, an understanding of
the necessary decisions, and a sketch of the worksheet, the next step is to use Excel to
create the worksheet.
For an introduction
to Windows 7 and
instruction about
how to perform basic
Windows 7 tasks, read
the Office 2010 and
Windows 7 chapter at
the beginning of this
book, where you can
learn how to resize
windows, change
screen resolution,
create folders, move
and rename files, use
Windows Help, and
much more.
To Start Excel
If you are using a computer to step through the project in this chapter and you want
your screens to match the figures in this book, you should change your screen’s resolution
to 1024 × 768. For information about how to change a computer’s resolution, refer to the
Office 2010 and Windows 7 chapter at the beginning of this book.
The following steps, which assume Windows 7 is running, start Excel based on
a typical installation. You may need to ask your instructor how to start Excel for your
computer. For a detailed example of the procedure summarized below, refer to the
Office 2010 and Windows 7 chapter.
1
Click the Start button on the Windows 7 taskbar to display the Start menu.
2
Type Microsoft Excel as the search text in the ‘Search programs and files’ text
box, and watch the search results appear on the Start menu.
3
Click Microsoft Excel 2010 in the search results on the Start menu to start Excel and display
a new blank workbook in the Excel window.
4
If the Excel window is not maximized, click the Maximize button next to the Close button
on its title bar to maximize the window.
Entering the Titles and Numbers into the Worksheet
For an introduction
to Office 2010 and
instruction about
how to perform
basic tasks in Office
2010 programs, read
the Office 2010 and
Windows 7 chapter
at the beginning of
this book, where you
can learn how to start
a program, use the
Ribbon, save a file,
open a file, quit a
program, use Help,
and much more.
The first step in creating the worksheet is to enter the titles and numbers into the
worksheet. The following sets of steps enter the worksheet title and subtitle and then the
biweekly payroll report data shown in Table 2 – 1.
To Enter the Worksheet Title and Subtitle
The following steps enter the worksheet title and subtitle into cells A1 and A2.
1
If necessary, select cell A1. Type The Mobile Masses Store in the selected cell
and then press the DOWN ARROW key to enter the worksheet title.
2
Type Biweekly Payroll Report in cell A2 and then press the DOWN ARROW key
to enter the worksheet subtitle (Figure 2 – 4 on page 73).
BTW
To Enter the Column Titles
The Ribbon and Screen
Resolution
Excel may change how
the groups and buttons
within the groups appear
on the Ribbon, depending
on the computer’s screen
resolution. Thus, your
Ribbon may look different
from the ones in this book
if you are using a screen
resolution other than
1024 × 768.
BTW
The employee names and the row titles Totals, Average, Highest, and Lowest in the
leftmost column begin in cell A4 and continue down to cell A16. The employee data is
entered into rows 4 through 12 of the worksheet. The remainder of this section explains
the steps required to enter the column titles, payroll data, and row titles, as shown in
Figure 2–4, and then save the workbook.
BTWs
For a complete list of the
BTWs found in the margins
of this book, visit the
Excel 2010 BTW Web page
(scsite.com/ex2010/btw).
The column titles in row 3 begin in cell A3 and extend through cell J3. Some of
the column titles in Figure 2–3 include multiple lines of text, such as Hours Worked in
cell D3. To start a new line in a cell, press alt+enter after each line, except for the last
line, which is completed by clicking the Enter box, pressing the enter key, or pressing one
of the arrow keys. When you see alt+enter in a step, press the enter key while holding
down the alt key and then release both keys. The following steps enter the column titles.
1
With cell A3 selected, type Employee and then press the RIGHT ARROW key to enter the
column heading.
2
Type Hire Date in cell B3 and then press the RIGHT ARROW key to enter the column
heading.
3
Type Dependents and then press the RIGHT ARROW key to enter the column heading.
4
In cell D3, type Hours and then press ALT+ENTER to enter the first line of the column
heading. Type Worked and then press the RIGHT ARROW key to enter the column heading.
5
Type Hourly and then press ALT+ENTER to begin a new line in the cell. Type Pay
Rate and then press the RIGHT ARROW key to enter the column heading.
6
Type Gross Pay in cell F3 and then press the RIGHT ARROW key to enter the column
heading.
7
Type Federal Tax in cell G3 and then press the RIGHT ARROW key to enter the column
heading.
8
Type State Tax in cell H3 and then press the RIGHT ARROW key to enter the column
heading.
9
Type Net Pay in cell I3 and then press the RIGHT ARROW key to enter the column
heading.
10 Type Tax % in cell J3 to enter the column heading.
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 71
EX 72 Excel Chapter 2 Formulas, Functions, and Formatting
To Enter the Biweekly Payroll Data
Q&As
For a complete list of the
Q&As found in many of
the step-by-step sequences
in this book, visit the
Excel 2010 Q&A Web page
(scsite.com/ex2010/qa).
Two-Digit Years
When you enter a twodigit year value (xx) that is
less than 30, Excel changes
that value to 20xx; when
you enter a value that is
30 or greater (zz), Excel
changes the value to 19zz.
Use four-digit years, if
necessary, to ensure that
Excel interprets year values
the way you intend.
1
Select cell A4, type Charvat, Emily , and then press the RIGHT ARROW key to enter
the employee name.
2
Type 3/3/09 in cell B4 and then press the RIGHT ARROW key to enter a date in the
selected cell.
3
Type 1 in cell C4 and then press the RIGHT ARROW key to enter a number in the selected cell.
4
Type 65.25 in cell D4 and then press the RIGHT ARROW key to enter a number in the
selected cell.
5
Type 20.50 in cell E4 and then click cell A5 to enter a number in the selected cell.
6
Enter the payroll data in Table 2–1 for the eight remaining employees in rows 5 through 12
(Figure 2–4).
Q&A
BTW
BTW
The biweekly payroll data in Table 2 – 1 includes a hire date for each employee.
Excel considers a date to be a number and, therefore, it displays the date right-aligned
in the cell. The following steps enter the data for each employee: name, hire date,
dependents, hours worked, and hourly pay rate.
In step 2, why did the date that was entered change from 3/3/09 to 3/3/2009?
When Excel recognizes that you entered a date in mm/dd/yy format, it automatically
formats the date as mm/dd/yyyy for you. Most professionals prefer to view dates in mm/
dd/yyyy format as opposed to mm/dd/yy format because the latter can cause confusion
regarding the intended year. For example, a date displayed as 3/3/50 could imply a date
of 3/3/1950 or 3/3/2050. The use of a four-digit year eliminates this confusion.
Table 2 – 1 The Mobile Masses Store Biweekly Payroll Report Data
Employee
Charvat, Emily
Chen, Bin
Felski, Noah
BTW
Kersey, Jane
Wrapping Text
If you have a long text
entry, such as a paragraph,
you can instruct Excel to
wrap the text in a cell. This
method is easier than your
pressing ALT+ENTER to end
each line of text within
the paragraph. To wrap
text, right-click in the cell,
click Format Cells on a
shortcut menu, click the
Alignment tab, and then
click Wrap text. Excel will
increase the height of the
cell automatically so that
the additional lines will
fit. If you want to control
where each line ends
in the cell, rather than
letting Excel wrap the text
based on the cell width,
you must end each line
with ALT+ENTER.
Merna, Thomas
Pollitt, Sherry
Prasad, Rao
Hire Date
Dependents
Hours Worked
Hourly Pay Rate
3/3/09
1
65.25
20.50
6/14/10
2
80.00
25.85
10/11/08
0
64.50
12.60
3/4/11
1
68.50
21.45
1/15/10
3
78.25
22.60
11/15/08
2
49.25
18.25
2/15/08
0
33.50
9.35
Washington, Yolanda
5/11/06
2
79.25
23.75
Zica, James
4/14/11
1
80.00
19.65
To Enter the Row Titles
The following steps add row titles for the rows that will contain the totals, average,
highest, and lowest amounts.
1
Select cell A13. Type Totals and then press the DOWN ARROW key to enter a row header.
2
Type Average in cell A14 and then press the DOWN ARROW key to enter a row header.
3
Type Highest in cell A15 and then press the DOWN ARROW key to enter a row header.
4
Type Lowest in cell A16 and then press the ENTER key to enter a row header. Select
cell F4 to prepare to enter a formula in the cell (Figure 2–4).
worksheet title
and subtitle
height of row 3
automatically
increased to fit
multiple lines
of text
BTW
column titles
on multiple
lines within cell
dates entered
with a two-digit
year are displayed
with a four-digit
year automatically
numbers
Figure 2– 4
To Change Document Properties
As discussed in Chapter 1, the first time you save a workbook, you should change
the document properties. The following steps change the document properties.
1
Click File on the Ribbon to open the Backstage view. If necessary, click the Info tab in the
Backstage view to display the Info gallery.
2
Click the Properties button in the right pane of the Info gallery to display the Properties
menu.
3
Click Show Document Panel on the Properties menu to close the Backstage view and
display the Document Information Panel in the Excel workbook window.
4
Click the Author text box, if necessary, and then type your name as the Author property.
If a name already is displayed in the Author text box, delete it before typing your name.
5
Click the Subject text box, if necessary delete any existing text, and then type your course
and section as the Subject property.
6
If an AutoComplete dialog box appears, click its Yes button.
7
Click the Keywords text box, if necessary delete any existing text, and then type
Biweekly Payroll Report as the Keywords property.
8
If an AutoComplete dialog box appears, click its Yes button.
9
Click the Close the Document Information Panel button so that the Document Information
Panel no longer is displayed.
Formatting Worksheets
With early worksheet
programs, users often
skipped rows to improve
the appearance of the
worksheet. With Excel it
is not necessary to skip
rows because you can
increase row heights to
add white space between
information.
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 73
EX 74 Excel Chapter 2 Formulas, Functions, and Formatting
To Change the Sheet Name and Save the Workbook
BTW
The following steps change the sheet name to Biweekly Payroll Report, change the
sheet tab color, and save the workbook on a USB flash drive in the Excel folder (for your
assignments) using the file name, The Mobile Masses Biweekly Payroll Report.
Entering Numbers
in a Range
An efficient way to enter
data into a range of cells is
to select a range and then
enter the first number in
the upper-left cell of the
range. Excel responds by
accepting the value and
moving the active cell
selection down one cell.
When you enter the last
value in the first column,
Excel moves the active cell
selection to the top of the
next column.
Plan
Ahead
1
Double-click the Sheet1 tab and then enter Biweekly Payroll Report as the
sheet name and then press the ENTER key.
2
Right-click the tab to display the shortcut menu and then click Tab Color on the shortcut
menu to display the Color gallery. Click Blue, Accent 1, Darker 25% (column 5, row 5) in
the Theme Colors area to apply a new color to the sheet tab.
3
With a USB flash drive connected to one of the computer’s USB ports, click the Save button
on the Quick Access Toolbar to display the Save As dialog box.
4
Type The Mobile Masses Biweekly Payroll Report in the File name text box
to change the file name. Do not press the ENTER key after typing the file name because you
do not want to close the dialog box at this time.
5
Navigate to the desired save location (in this case, the Excel folder in the CIS 101 folder
[or your class folder] on the USB flash drive).
6
Click the Save button (Save As dialog box) to save the document in the selected folder on
the selected drive with the entered file name.
Entering Formulas
One of the reasons Excel is such a valuable tool is that you can assign a formula to a cell,
and Excel will calculate the result. Consider, for example, what would happen if you had
to multiply 65.25 by 20.5 and then manually enter the product for Gross Pay, 1,337.625,
in cell F4. Every time the values in cells D4 or E4 changed, you would have to recalculate
the product and enter the new value in cell F4. By contrast, if you enter a formula in cell
F4 to multiply the values in cells D4 and E4, Excel recalculates the product whenever new
values are entered into those cells and displays the result in cell F4.
Determine the formulas and functions needed.
As you have learned, formulas and functions simplify the creation and maintenance of
worksheets because Excel performs calculations for you. When formulas and functions are
used together properly, the amount of data that a user manually must enter in a worksheet
greatly can be diminished:
• Utilize proper algebraic notation. Most Excel formulas are the result of algebraic
calculations. A solid understanding of algebraic operators and the order of operations
is important to writing sound formulas.
• Utilize the fill handle and copy and paste operations to copy formulas. The fill handle
and the Excel copy and paste functionality help to minimize errors caused by retyping
formulas. When possible, if a similar formula will be used repeatedly in a worksheet,
avoid retyping the formula and instead use the fill handle.
• Be careful about using invalid and circular cell references. An invalid reference occurs
when Excel does not understand a cell reference used in a formula, resulting in Excel
displaying a #REF! error message in the cell.
(Continued)
A formula in a cell that contains a reference back to itself is called a circular reference.
Excel often warns you when you create a circular reference. In almost all cases, circular
references are the result of an incorrect formula. A circular reference can be direct
or indirect. For example, placing the formula =A1 in cell A1 results in a direct circular
reference. An indirect circular reference occurs when a formula in a cell refers to another
cell or cells that include a formula that refers back to the original cell.
• Employ the Excel built-in functions whenever possible. Excel includes prewritten formulas
called functions to help you compute a range of values and statistics. A function takes a
value or values, performs an operation, and returns a result to the cell. The values that
you use with a function are called arguments. All functions begin with an equal sign
and include the arguments in parentheses after the function name. For example, in the
function =AVERAGE(C4:C12), the function name is AVERAGE, and the argument is the
range C4:C12. Become familiar with the extensive number of built-in functions. When
you have the choice, always use built-in functions instead of writing and typing a formula
version of your mathematical expression. Such a practice reduces the possibility of errors
and simplifies the formula used in a cell, resulting in improved readability.
Plan
Ahead
BTW
(Continued)
Automatic
Recalculation
Every time you enter
a value into a cell in
the worksheet, Excel
automatically recalculates
all formulas. You can
change to manual
recalculation by clicking
the Calculation Options
button (Formulas tab |
Calculation group) and
then clicking Manual.
In manual calculation
mode, pressing the F9
key instructs Excel to
recalculate all formulas.
To Enter a Formula Using the Keyboard
The formulas needed in the worksheet are noted in the requirements document as follows:
1. Gross Pay (column F) = Hours Worked × Hourly Pay Rate
2. Federal Tax (column G) = 0.22 × (Gross Pay – Dependents × 24.32)
3. State Tax (column H) = 0.04 × Gross Pay
4. Net Pay (column I) = Gross Pay – (Federal Tax + State Tax)
5. Tax% (column J) = (Federal Tax + State Tax) / Gross Pay
The gross pay for each employee, which appears in column F, is equal to hours worked in column D times
hourly pay rate in column E. Thus, the gross pay for Emily Charvat in cell F4 is obtained by multiplying 65.25
(cell D4) by 20.50 (cell E4) or =D4*E4. The following steps enter the initial gross pay formula in cell F4 using
the keyboard.
1
• With cell F4 selected, type =d4*e4
Q&A
in the cell to display the formula in
the formula bar and in the current
cell and to display colored borders
around the cells referenced in the
formula (Figure 2 – 5).
formula begins
with equal sign
What occurs on the worksheet as I
enter the formula?
The equal sign (=) preceding d4*e4
alerts Excel that you are entering
a formula or function and not
text. Because the most common
error when entering a formula is
colored cell borders
to reference the wrong cell in a
indicate cells included
in formula in active cell
formula mistakenly, Excel colors
the borders of the cells referenced
in the formula. The coloring helps
in the reviewing process to ensure
the cell references are correct.
Figure 2–5
The asterisk (*) following d4 is the
arithmetic operator that directs Excel to perform the multiplication operation.
as formula is typed, it
appears in formula bar
and in active cell F4
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 75
EX 76 Excel Chapter 2 Formulas, Functions, and Formatting
2
• Press the RIGHT ARROW
key to complete the
arithmetic operation
indicated by the
formula, to display
the result in the
worksheet, and to
select the cell to the
right (Figure 2– 6). The
number of decimal
places shown in cell F4
may be different, but
these values will be
adjusted later in this
chapter.
active cell
is G4 after
pressing RIGHT
ARROW key
value of formula
(65.25 × 20.5)
Figure 2–6
Arithmetic Operations
Table 2 – 2 describes multiplication and other valid Excel arithmetic operators.
Table 2 – 2 Summary of Arithmetic Operators
Arithmetic
Operator
Meaning
Example
of Usage
Meaning
Negation
– 78
Negative 78
Percentage
=23%
Multiplies 23 by 0.01
^
Exponentiation
=3 ^ 4
Raises 3 to the fourth power
*
Multiplication
=61.5 * C5
Multiplies the contents of cell C5 by 61.5
–
%
/
Division
=H3 / H11
Divides the contents of cell H3 by the contents of cell H11
+
Addition
=11 + 9
Adds 11 and 9
–
Subtraction
=22 – F15
Subtracts the contents of cell F15 from 22
When more than one arithmetic operator is involved in a formula, Excel follows
the same basic order of operations that you use in algebra. Moving from left to right in a
formula, the order of operations is as follows: first negation (– ), then all percentages (%),
then all exponentiations (^), then all multiplications (*) and divisions (/), and finally, all
additions (+) and subtractions (– ).
As in algebra, you can use parentheses to override the order of operations.
For example, if Excel follows the order of operations, 8 * 3 + 2 equals 26. If you use
parentheses, however, to change the formula to 8 * (3 + 2), the result is 40, because the
parentheses instruct Excel to add 3 and 2 before multiplying by 8. Table 2 – 3 illustrates
several examples of valid Excel formulas and explains the order of operations.
Troubling Formulas
If Excel does not accept a
formula, remove the equal
sign from the left side
and complete the entry as
text. Later, after you have
entered additional data
in the cells reliant on the
formula or determined the
error, reinsert the equal
sign to change the text
back to a formula and edit
the formula as needed.
Table 2 – 3 Examples of Excel Formulas
Formula
Meaning
=G15
Assigns the value in cell G15 to the active cell.
=2^4 + 7
Assigns the sum of 16 + 7 (or 23) to the active cell.
=100 + D2 or =D2 +100 or
=(100 + D2)
Assigns 100 plus the contents of cell D2 to the active cell.
=25% * 40
Assigns the product of 0.25 times 40 (or 10) to the active cell.
– (K15 * X45)
Assigns the negative value of the product of the values contained in cells K15 and
X45 to the active cell. You do not need to type an equal sign before an expression
that begins with minus signs, which indicates a negation.
=(U8 – B8) * 6
Assigns the product of the difference between the values contained in cells U8 and
B8 times 6 to the active cell.
=J7 / A5 + G9 * M6 – Z2
^ L7
Completes the following operations, from left to right: exponentiation (Z2 ^ L7),
then division (J7 / A5), then multiplication (G9 * M6), then addition (J7 / A5) +
(G9 * M6), and finally subtraction (J7 / A5 + G9 * M6) – (Z2 ^ L7). If cells
A5 = 6, G9 = 2, J7 = 6, L7 = 4, M6 = 5, and Z2 = 2, then Excel assigns the active
cell the value – 5; that is, 6 / 6 + 2 * 5 – 2 ^ 4 = – 5.
To Enter Formulas Using Point Mode
The sketch of the worksheet in Figure 2 – 3 on page EX 70 calls for the federal tax, state tax, net pay, and tax
% for each employee to appear in columns G, H, I, and J, respectively. All four of these values are calculated using
formulas in row 4:
Federal Tax (cell G4) = 0.22 × (Gross Pay – Dependents × 24.32) or =0.22*(F4– C4*24.32)
State Tax (cell H4) = 0.04 × Gross Pay or = 0.04* F4
Net Pay (cell I4) = Gross Pay – (Federal Tax + State Tax) or =F4-(G4+H4)
Tax % (cell J4) = (Federal Tax + State Tax) / Gross Pay or =(G4+H4)/F4
An alternative to entering the formulas in cells G4, H4, I4, and J4 using the keyboard is to enter the formulas
using the mouse and Point mode. Point mode allows you to select cells for use in a formula by using the mouse.
The steps on the following pages enter formulas using Point mode.
Excel Chapter 2
Order of Operations
BTW
Formulas, Functions, and Formatting Excel Chapter 2 EX 77
EX 78 Excel Chapter 2 Formulas, Functions, and Formatting
1
• With cell G4 selected
type =0.22*( to
begin the formula and
then click cell F4 to
add a cell reference
in the formula
(Figure 2 – 7).
Note: To help you
locate screen elements
that are referenced in
the step instructions,
such as buttons and
commands, this book
uses red boxes to
point to these screen
elements.
cell reference F4
appended to formula
marquee surrounds
selected cell F4
Figure 2–7
2
• Type – (minus sign)
and then click cell C4
to add a subtraction
operator and a
reference to another
cell to the formula.
formula appears
in formula bar
• Type *24.32) to
complete the formula
(Figure 2– 8).
cell reference C4
added to formula
border of cell and its cell
reference in formulas
are colored green
Figure 2–8
3
• Click the Enter box in
the formula bar and
then select cell H4 to
prepare to enter the
next formula.
formula appears
in formula bar
• Type =0.04* and
then click cell F4 to
add a cell reference
to the formula
(Figure 2 – 9).
value of formula .22 ×
(1337.625 – 1 * 24.32)
Enter box
marquee surrounds
selected cell F4
cell reference F4
appended to formula
Q&A
Figure 2–9
Why should I use Point mode to enter formulas?
Using Point mode to enter formulas often is faster and more accurate than using the
keyboard to type the entire formula when the cell you want to select does not require you
to scroll. In many instances, as in these steps, you may want to use both the keyboard and
mouse when entering a formula in a cell. You can use the keyboard to begin the formula,
for example, and then use the mouse to select a range of cells.
4
• Click the Enter box in
the formula bar and
then select cell I4 to
prepare to enter the
next formula.
formula assigned
to cell I4
• Type = (equal
sign) and then click
cell F4 to begin the
formula and add a
cell reference to the
formula.
value of formula
(.04 * 1337.625)
• Type –( (minus sign
followed by an open
parenthesis) and then
click cell G4 to add a
subtraction operator,
open parenthesis, and
cell reference to the
formula.
Net Pay formula
appears in cell I4
Figure 2–10
• Type + (plus sign) and then click cell H4 to add an addition operator and cell reference to
the formula.
• Type ) (close parenthesis) to complete the formula (Figure 2–10).
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 79
EX 80 Excel Chapter 2 Formulas, Functions, and Formatting
5
• Click the Enter box
in the formula bar to
enter the formula in
cell I4.
formula assigned
to cell J4
• Select cell J4. Type
=( (equal sign
followed by an open
parenthesis) and then
click cell G4 to add
a reference to the
formula.
• Type + (plus sign)
and then click cell H4
to add a cell reference
to the formula.
value of formula (288.9271 +
53.505 ) / 1337.625
• Type )/ (close
parenthesis followed
by a forward slash),
and then click cell F4
to add a cell reference
to the formula.
• Click the Enter box
Q&A
in the formula bar to
enter the formula in
cell J4 (Figure 2– 11).
Figure 2–11
Why do three decimal places show in cell J4?
The actual value assigned by Excel to cell J4 from the division operation in step 5 is
0.256000075. While not all the decimal places appear in Figure 2 –11, Excel maintains all of
them for computational purposes. Thus, if referencing cell J4 in a formula, the value used
for computational purposes is 0.256000075, not 0.256. The cell formatting is set to display six
digits after the decimal point, but the formatting also suppresses trailing zeroes. If the cell
formatting were set to display six digits and show trailing zeroes, then Excel would display
0.256000 in cell J4. If you change the cell formatting of column J to display nine digits after
the decimal point, then Excel displays the true value 0.256000075.
To Copy Formulas Using the Fill Handle
The five formulas for Emily Charvat in cells F4, G4, H4, I4, and J4 now are complete. You could enter the
same five formulas one at a time for the eight remaining employees. A much easier method of entering the formulas,
however, is to select the formulas in row 4 and then use the fill handle to copy them through row 12. When performing
copying operations in Excel, the source area is the cell, or range, from which data or formulas are being copied. When a
range is used as a source, sometimes it is called the source range. The destination area is the cell, or range, to which data
or formulas are being copied. When a range is used as a destination, sometimes it is called the destination range. Recall
from Chapter 1 that the fill handle is a small rectangle in the lower-right corner of the active cell or active range. The
following steps copy the formulas using the fill handle.
1
• Select the source
range, F4:J4 in this
case, and then point
to the fill handle.
• Drag the fill handle
down through cell J12
and continue to hold
the mouse button to
select the destination
range (Figure 2– 12).
initial location
of fill handle
range F4:J4
selected as
source area
fill handle dragged
through cell J12
Figure 2–12
2
• Release the mouse
Q&A
button to copy the
formulas to the
destination range
(Figure 2 – 13).
How does Excel adjust
the cell references in
the formulas in the
destination area?
gross pay, federal
tax, state tax, net
pay, and tax %
formulas in range
F4:J4 copied to
range F5:J12
Recall that when
you copy a formula,
Excel adjusts the cell
references so that
Figure 2–13
the new formulas
contain references corresponding to the new location and perform calculations using
the appropriate values. Thus, if you copy downward, Excel adjusts the row portion of cell
references. If you copy across, then Excel adjusts the column portion of cell references. These
cell references are called relative cell references.
Auto Fill Options button
appears after copying the
range F4:J4 to range F5:J12
Other Ways
1. Select source area, click
Copy button (Home tab |
Clipboard group), select
destination area, click
Paste button (Home tab |
Clipboard group)
2. Right-click source area,
click Copy on shortcut
menu, right-click
destination area, click
Paste icon on shortcut
menu
3. Select source area and
then point to border of
range; while holding
down CTRL, drag source
area to destination area
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 81
EX 82 Excel Chapter 2 Formulas, Functions, and Formatting
Option Buttons
BTW
BTW
Excel displays Option buttons in a workbook while you are working on it to indicate
that you can complete an operation using automatic features such as AutoCorrect,
Auto Fill, error checking, and others. For example, the Auto Fill Options button shown
in Figure 2 – 13 appears after a fill operation, such as dragging the fill handle. When an
error occurs in a formula in a cell, Excel displays the Trace Error button next to the
cell and identifies the cell with the error by placing a green triangle in the upper left
of the cell.
Table 2 – 4 summarizes the Option buttons available in Excel. When one of these
buttons appears on your worksheet, click the button arrow to produce the list of options
for modifying the operation or to obtain additional information.
The Paste Options
Button
The Paste Options button
provides powerful
functionality. When
performing copy and paste
operations, the button
allows you great freedom
in specifying what it is
you want to paste. For
example, you could choose
to paste an exact copy of
what you copied, including
the cell contents and
formatting. You also could
copy just formulas, just
formatting, just the cell
values, a combination of
these options, or a picture
of what you copied.
Selecting a Range
You can select a range
using the keyboard. Press
the F8 key and then use
the arrow keys to select
the desired range. After
you are finished, make sure
to press the F8 key to turn
off the selection process
or you will continue to
select ranges.
Table 2 – 4 Options Buttons in Excel
Button
Name
Menu Function
Auto Fill Options
Gives options for how to fill cells following a fill operation,
such as dragging the fill handle.
AutoCorrect Options
Undoes an automatic correction, stops future automatic
corrections of this type, or causes Excel to display the
AutoCorrect Options dialog box.
Insert Options
Lists formatting options following an insertion of cells, rows,
or columns.
Paste Options
Specifies how moved or pasted items should appear (for
example, with original formatting, without formatting, or
with different formatting).
Trace Error
Lists error-checking options following the assignment of an
invalid formula to a cell.
To Determine Totals Using the Sum Button
The next step is to determine the totals in row 13 for the hours worked in column D,
gross pay in column F, federal tax in column G, state tax in column H, and net pay in column
I. To determine the total hours worked in column D, the values in the range D4 through
D12 must be summed. To do so, enter the function =sum(d4:d12) in cell D13 or select cell
D13, click the Sum button (Home tab | Editing group), and then press the enter key. Recall
that a function is a prewritten formula that is built into Excel. Similar SUM functions can
be used in cells F13, G13, H13, and I13 to total gross pay, federal tax, state tax, and net pay,
respectively. The following steps determine totals in cell D13 and the range F13:I13.
1
Select cell to contain the sum, cell D13 in this case. Click the Sum button (Home tab |
Editing group) to sum the contents of the range D4:D12 in cell D13 and then click the
Enter box to display a total in the selected cell.
2
Select the range to contain the sums, range F13:I13 in this case. Click the Sum button
(Home tab | Editing group) to display totals in the selected range (Figure 2–14).
Sum button
Editing group
total gross pay
total state tax
total federal tax
total net pay
Figure 2 – 14
To Determine the Total Tax %
1
Select the cell to be copied, J12 in this case, and then point to the fill handle.
2
Drag the fill handle down through cell J13 to copy the formula (Figure 2–15).
Q&A
With the totals in row 13 determined, the next step is to copy the tax % formula in
cell J12 to cell J13 as performed in the following steps.
Why was the formula I13/F13 not copied to cell J13 earlier?
The formula, I13/F13, was not copied to cell J13 when cell J4 was copied to the range
J5:J12 because both cells involved in the computation (I13 and F13) were blank, or zero,
at the time. A blank cell in Excel has a numerical value of zero, which would have resulted
in an error message in cell J13. Once the totals were determined, both cells I13 and F13
(especially F13, because it is the divisor) had nonzero numerical values.
formula is
=(G12+H12)/
F12
formula is
=(G13+H13)/
F13
Figure 2–15
Auto Fill Options button
appears after copying cell J12
to cell J13
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 83
EX 84 Excel Chapter 2 Formulas, Functions, and Formatting
Using the AVERAGE, MAX, and MIN Functions
BTW
Statistical Functions
Excel usually considers a
blank cell to be equal to
0. The statistical functions,
however, ignore blank
cells. Excel thus calculates
the average of three cells
with values of 10, blank,
and 8 to be 9 [(10 + 8) / 2]
and not 6 [(10 + 0 + 8) / 3].
The next step in creating The Mobile Masses Biweekly Payroll Report worksheet is
to compute the average, highest value, and lowest value for the number of dependents
listed in the range C4:C12 using the AVERAGE, MAX, and MIN functions in the range
C14:C16. Once the values are determined for column C, the entries can be copied across
to the other columns.
With Excel, you can enter functions using one of five methods: (1) the keyboard
or mouse, (2) the Insert Function box in the formula bar, (3) the Sum menu, (4) the
Sum command (Formulas tab | Function Library group), and (5) the Name box area
in the formula bar (Figure 2–16). The method you choose will depend on your typing
skills and whether you can recall the function name and required arguments.
In the following pages, each of the first three methods will be used. The keyboard
and mouse method will be used to determine the average number of dependents (cell C14).
The Insert Function button in the formula bar method will be used to determine the
highest number of dependents (cell C15). The Sum menu method will be used to
determine the lowest number of dependents (cell C16).
To Determine the Average of a Range of Numbers Using the Keyboard and Mouse
The AVERAGE function sums the numbers in the specified range and then divides the sum by the number
of cells with numeric values in the range. The following steps use the AVERAGE function to determine the average
of the numbers in the range C4:C12.
1
• Select the cell to
contain the average,
cell C14 in this case.
• Type =av in the cell
Q&A
to display the Formula
AutoComplete list.
Press the DOWN ARROW
key to highlight the
required formula
(Figure 2– 16).
Name box changes to Function
box as soon as = (equal sign) is
entered to start function
What is happening as
I type?
As you type the equal
sign followed by the
characters in the name
of a function, Excel
displays the Formula
AutoComplete list.
This list contains
those functions that
alphabetically match
the letters you have
typed. Because you
typed =av, Excel
displays all the
functions that begin
with the letters av.
=av typed
in cell C14
AVERAGE
function
name
ScreenTip includes description
of currently selected function
in Formula AutoComplete list
Formula AutoComplete list
Figure 2 – 16
2
• Double-click AVERAGE in the
Formula AutoComplete list to select
the function.
Enter box
• Select the range to be averaged,
Q&A
C4:C12 in this case, to insert the
range as the argument to the
function (Figure 2 – 17).
As I drag, why does the function in
cell C14 change?
When you click cell C4, Excel
appends cell C4 to the left
parenthesis in the formula bar and
surrounds cell C4 with a marquee.
When you begin dragging, Excel
appends to the argument a colon
(:) and the cell reference of the cell
where the mouse pointer is located.
AVERAGE function with range
to average shows in active cell
and formula bar
marquee
surrounds
selected
range
C4:C12
ScreenTip indicates arguments
for AVERAGE function
Figure 2 – 17
3
• Click the Enter box to compute
Q&A
the average of the numbers in the
selected range and display the result
in the selected cell (Figure 2 – 18).
Function box
changes back
to Name box
Can I use the arrow keys to
complete the entry instead?
right parenthesis automatically
appended when Enter box is
clicked or ENTER key is pressed
No. When you use Point mode
you cannot use the arrow keys to
complete the entry. While in Point
mode, the arrow keys change the
selected cell reference in the range
you are selecting.
Q&A
when cell C14 is active cell, formula
bar displays AVERAGE function
Insert Function box
What is the purpose of the
parentheses in the function?
The AVERAGE function requires
that the argument (in this case, the
range C4:C12) be included within
parentheses following the function
name. Excel automatically appends
the right parenthesis to complete the
AVERAGE function when you click
the Enter box or press the ENTER key.
average number
of dependents
Figure 2 – 18
Other Ways
1. Click Insert Function box
in the formula bar, click
AVERAGE
2. Click Sum button arrow
(Home tab | Editing
group), click Average
3. Click Sum button arrow
(Formulas tab | Function
Library group), click
Average
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 85
EX 86 Excel Chapter 2 Formulas, Functions, and Formatting
To Determine the Highest Number in a Range of Numbers Using the Insert Function Box
The next step is to select cell C15 and determine the highest (maximum) number in the range C4:C12. Excel
includes a function called the MAX function that displays the highest value in a range. Although you could enter
the MAX function using the keyboard and Point mode as described in the previous steps, an alternative method
to entering the function is to use the Insert Function box in the formula bar. The following steps use the Insert
Function box in the formula bar to enter the MAX function.
1
• Select the cell to
contain the maximum
number, cell C15 in
this case.
Insert Function
dialog box
Insert Function box
• Click the Insert
Function box in the
formula bar to display
the Insert Function
equal sign
automatically
dialog box.
• Click MAX in the
inserted in
active cell and
formula bar
Search for a
function box
Or select a
category box
‘Select a function’
list (Insert Function
dialog box) to select
it (Figure 2– 19). If
the MAX function is
not displayed in the
‘Select a function’ list,
scroll the list until the
function is displayed.
MAX selected in
Select a function list
Help on this
function link
OK button
Figure 2 – 19
2
• Click the OK button
(Insert Function dialog
box) to display the
Function Arguments
dialog box.
• Type c4:c12 in
Q&A
the Number1 box
(Function Arguments
dialog box) to enter
the first argument
of the function
(Figure 2 – 20).
function appears
in formula bar
Function Arguments
dialog box
range typed in
Number 1 box
Collapse Dialog button
first few numbers
in selected range
Why did numbers
appear in the
Function Arguments
dialog box?
As shown in
Figure 2 –20, Excel
displays the value
Help on this
last part of function
function link
appears in active cell
the MAX function
will return to cell
C15 in the Function
Figure 2 – 20
Arguments dialog
box. It also lists the first few numbers in the selected range, next to the Number1 box.
result of function
OK button
3
• Click the OK button
MAX function
determines highest
value in range C4:C12
Q&A
(Function Arguments
dialog box) to display
the highest value
in the chosen range
in the selected cell
(Figure 2 – 21).
Why should I not just
enter the highest value
that I see in the range
C4:C12 in cell C15?
In this example, rather
highest value in
than entering the
range C4:C12
MAX function, you
visually could scan
Figure 2 – 21
the range C4:C12,
determine that the highest number of dependents is 3, and manually enter the number 3 as
a constant in cell C15. Excel would display the number the same as in Figure 2–21. Because it
contains a constant, however, Excel will continue to display 3 in cell C15, even if the values in
the range C4:C12 change. If you use the MAX function, Excel will recalculate the highest value
in the range C4:C12 each time a new value is entered into the worksheet.
Other Ways
1. Click Sum button arrow
(Home tab | Editing group),
click Max
2. Click Sum button arrow
(Formulas tab | Function
Library group), click Max
3. Type
=MAX
in cell
To Determine the Lowest Number in a Range of Numbers Using the Sum Menu
The next step is to enter the MIN function in cell C16 to determine the lowest (minimum) number in the range
C4:C12. Although you can enter the MIN function using either of the methods used to enter the AVERAGE and MAX
functions, the following steps perform an alternative using the Sum button (Home tab | Editing group).
1
• Select cell C16 to
Sum button arrow
prepare to enter the
next function.
Editing group
• Click the Sum button
Q&A
arrow (Home tab |
Editing group) to
display the Sum
button menu
(Figure 2–22).
Sum button menu
Min function
Why should I use the
Sum button menu?
Using the Sum button
menu allows you
to enter one of five
often-used functions
easily into a cell,
without having to
memorize its name
or the required
arguments.
C16 is active cell
Figure 2 – 22
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 87
EX 88 Excel Chapter 2 Formulas, Functions, and Formatting
2
• Click Min to display
Q&A
the MIN function
in the formula bar
and in the active cell
(Figure 2 – 23).
Why does Excel select
the range C14:C15?
MIN function appears
in formula bar
The range C14:C15
automatically selected
by Excel is not correct.
Excel attempts to
guess which cells you
want to include in the
function by looking
for ranges that are
adjacent to the
selected cell and that
contain numeric data.
Excel selects range
C14:C15 because it
includes numbers and
is adjacent to cell C16
MIN function
appears in
active cell C16
Figure 2 – 23
3
• Click cell C4 and then
drag through cell C12
to display the function
with the new range in
the formula bar and
in the selected cell
(Figure 2 – 24).
range C4:C12
selected using
Point mode
Enter box
selected range
appears in formula
bar and in active cell
Figure 2 – 24
4
• Click the Enter box
Q&A
to determine the
lowest value in the
range C4:C12 and
display the result in
the formula bar and
in the selected cell
(Figure 2 – 25).
MIN function determines
lowest value in range C4:C12
How can I learn about
other functions?
Excel has more
than 400 additional
functions that
perform just
about every type
of calculation
you can imagine.
These functions
result of MIN function
are categorized in
appears in cell
the Insert Function
dialog box shown in
Figure 2 –19 on page
Figure 2 – 25
EX 86. To view the
categories, click the ‘Or select a category’ box arrow. To obtain a description of a selected
function, select its name in the Insert Function dialog box. Excel displays the description of
the function below the Select a function list in the dialog box.
Other Ways
1. Click Insert Function box
in the formula bar (if
necessary, select Statistical
category), click MIN
2. Click Sum button arrow
(Formulas tab | Function
Library group), click Min
3. Type =MIN in cell
To Copy a Range of Cells Across Columns to an Adjacent Range Using the Fill Handle
The next step is to copy the AVERAGE, MAX, and MIN functions in the range C14:C16 to the adjacent
range D14:J16. The following steps use the fill handle to copy the functions.
1
• Select the source
range from which to
copy the functions, in
this case C14:C16.
copy range
C14:C16
selected
• Drag the fill handle
in the lower-right
corner of the selected
range through
cell J16 and continue
to hold down the
mouse button to
begin a fill operation
(Figure 2 – 26).
fill handle dragged to select
destination area, range D14:J16
Figure 2 – 26
mouse pointer
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 89
EX 90 Excel Chapter 2 Formulas, Functions, and Formatting
2
• Release the mouse
AVERAGE, MAX, and MIN
functions in range C14:C16
copied to range D14:J16
button to copy the
three functions to
the selected range
(Figure 2 – 27).
Auto Fill Options button
Q&A
Figure 2 – 27
How can I be sure that the function arguments are proper for the cells in range D14:J16?
Remember that Excel adjusts the cell references in the copied functions so that each
function refers to the range of numbers above it in the same column. Review the numbers in
rows 14 through 16 in Figure 2 – 27. You should see that the functions in each column return
the appropriate values, based on the numbers in rows 4 through 12 of that column.
Save button
3
• Select cell J14 and
Q&A
then press the DELETE
key to delete the
average of the tax %
(Figure 2– 28).
Why is the formula
in cell J14 deleted?
The average of the
tax % in cell J14 is
deleted because
an average of
percentages of this
type is mathematically
invalid.
average of
percents in
range J4:J12
mathematically
invalid
Figure 2 – 28
Other Ways
1. Select source area, click
Copy button (Home
tab | Clipboard group),
select destination area,
click Paste button
(Home tab | Clipboard
group)
2. Right-click source area,
click Copy on shortcut
menu, right-click destination area, click Paste icon
on shortcut menu
3. Select source area and
then point to border of
range; while holding down
CTRL, drag source area to
destination area
4. Select source area, press
CTRL+C, select destination
area, press CTRL+V
To Save a Workbook Using the Same File Name
Earlier in this project, an intermediate version of the workbook was saved using
the file name, The Mobile Masses Biweekly Payroll Report. The following step saves the
workbook a second time, using the same file name.
1
Click the Save button on the Quick Access Toolbar to overwrite the previously saved file.
Break Point: If you wish to take a break, this is a good place to do so. You can quit Excel now. To resume at a later time, start
Excel, open the file called Mobile Masses Biweekly Payroll Report, and continue following the steps from this location forward.
BTW
Verifying Formulas Using Range Finder
One of the more common mistakes made with Excel is to include a wrong cell reference
in a formula. An easy way to verify that a formula references the cells you want it to
reference is to use the Excel Range Finder. Use Range Finder to check which cells are
referenced in the formula assigned to the active cell. Range Finder allows you to make
immediate changes to the cells referenced in a formula.
To use Range Finder to verify that a formula contains the intended cell references,
double-click the cell with the formula you want to check. Excel responds by highlighting
the cells referenced in the formula so that you can check that the cell references are correct.
To Verify a Formula Using Range Finder
The following steps use Range Finder to check the formula in cell J4.
1
• Double-click cell J4 to
activate Range Finder
(Figure 2 – 29).
2
• Press the ESC key to
quit Range Finder and
then click anywhere
in the worksheet, such
as cell A18, to deselect
the current cell.
cells referenced in
formula in active cell
are highlighted with
corresponding colors
colors of cell references
correspond to colors of
highlighted cells
Figure 2 – 29
Entering Functions
You can drag the Function
Arguments dialog box
(Figure 2 – 20 on page
EX 86) out of the way in
order to select a range.
You also can click the
Collapse Dialog button to
the right of the Number 1
box to hide the Function
Arguments dialog box.
The dialog box then
collapses and the Collapse
Dialog button becomes an
Expand Dialog box button.
After selecting the range,
click the Expand Dialog to
expand the dialog box.
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 91
EX 92 Excel Chapter 2 Formulas, Functions, and Formatting
Formatting the Worksheet
Although the worksheet contains the appropriate data, formulas, and functions, the text
and numbers need to be formatted to improve their appearance and readability.
In Chapter 1, cell styles were used to format much of the worksheet. This section
describes how to change the unformatted worksheet in Figure 2 – 30a to the formatted
worksheet in Figure 2 – 30b using a theme and other commands on the Ribbon. The
colors and fonts that are used in the worksheet shown in Figure 2 –30b are those
that are associated with the Trek theme.
(a) Unformatted
Worksheet
orange
background
column titles
centered
Title cell style
applied to title
and subtitle
thick box border
surrounds worksheet
title and subtitle
row height
increased
Heading 3 cell
style applied
to row 3
numbers over 70 displayed
with orange background
and white font color
Comma
style format
Total cell style
applied to row 13
row height
increased
dates formatted
to mm/dd/yy
style
data centered
in cells
Currency
style format
(b) Formatted
Worksheet
column width changed
Figure 2 – 30
Percent
style format
Identify how to format various elements of the worksheet.
As you have learned, applying proper formatting to a worksheet improves its appeal and
readability. The following list includes additional worksheet formatting considerations.
• Consider using cell borders and fill colors for various portions of the worksheet. Cell
borders, or box borders, draw a border around a cell or range of cells to set the cell or
range off from other portions of the worksheet. For example, worksheet titles often
include cell borders. Similarly, the use of a fill color in a cell or range of cells sets off the
cell or range from other portions of the worksheet and provides visual impact to draw
the user’s eye toward the cell or range.
• Use good judgment when centering values in columns. If a cell entry is short, such as the
dependents in column C, centering the entries within their respective columns improves
the appearance of the worksheet.
• Consider the use of a different theme. A theme is a predefined set of colors, fonts, chart
styles, cell styles, and fill effects that can be applied to an entire workbook. Every new workbook that you create is assigned a default theme named Office. Excel, however, includes a
variety of other themes that provide a range of visual effects for your workbooks.
• Apply proper formatting for cells that include dates. Excel provides a number of date
formats so that date values can be formatted to meet your needs. How you decide to
format a date depends on a number of factors. For example, dates that include years
both before and after the year 2000 should be formatted with a four-digit year. Your
organization or department may insist on the use of certain standard date formats.
Industry standards also may indicate how you should format date values.
The following outlines the formatting suggested in the sketch of the worksheet in
Figure 2–3 on page EX 70.
1. Workbook theme — Trek
2. Worksheet title and subtitle
a. Alignment — center across columns A through J
b. Cell style — Title
c. Font size — title 18; subtitle 16
d. Background color (range A1:J2) — Orange Accent 1, Lighter 60%
e. Border — thick box border around range A1:J2
3. Column titles
a. Cell style — Heading 3
b. Alignment — center
4. Data
a. Dates in column B — mm/dd/yy format
b. Alignment — center data in range C4:C12
c. Numbers in column D — Comma style and two decimal places; if a cell in
range D4:D12 is greater than 70, then cell appears with background color of
orange and a font color of white
d. Numbers in top row (range E4:I4) — Accounting number format
e. Numbers below top row (range E5:I12) — Comma style and decimal places
5. Total line
a. Cell style — Total
b. Numbers — Accounting number format
Plan
Ahead
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 93
EX 94 Excel Chapter 2 Formulas, Functions, and Formatting
6. Average, highest, and lowest rows
a. Font style of row titles in range A14:A16 — bold
b. Numbers — Currency style with floating dollar sign in the range E14:I16
7. Percentages in column J
a. Numbers — Percentage style with two decimal places
8. Column widths
a. Columns A, B, and C — best fit
b. Column H — 10.22 characters
c. Column D, E, and J — 7.56 characters
9. Row heights
a. Row 3 — 48.00 points
b. Row 14 — 27.00 points
c. Remaining rows — default
To Change the Workbook Theme
The Trek theme includes fonts and colors that provide the worksheet a professional and subtly colored
appearance. The following steps change the workbook theme to the Trek theme.
1
• Display the Page
Themes button
Page Layout tab
Layout tab.
• Click the Themes
button (Page Layout
tab | Themes group)
to display the Themes
gallery.
Themes gallery
• Scroll to the bottom
of the gallery
(Figure 2 – 31).
I Experiment
• Point to several
Trek
Q&A
theme
themes in the
Themes gallery
to see a live preview
of the themes.
Why should I change
the theme of a
workbook?
A company or
department may
standardize on a
Figure 2 – 31
specific theme so that
all of their documents have a similar appearance. Similarly, an individual may want to have
a theme that sets his or her work apart from the work of others. Other Office programs,
such as Word and PowerPoint, include the same themes included with Excel, meaning that
all of your Microsoft Office documents can share a common theme.
2
• Click Trek in the
Home tab
Themes gallery to
change the workbook
theme (Figure 2– 32).
font of worksheet cells
reflects theme’s default font
Q&A
Figure 2 – 32
Why did the cells in the worksheet change?
The cells in the worksheet originally were formatted with the default font for the default
Office theme. The default font for the Trek theme is different from that of the default font
for the Office theme and, therefore, changed on the worksheet when you changed the
theme. If you had modified the font for any of the cells, those cells would not receive the
default font for the Trek theme.
To Format the Worksheet Titles
The following steps merge and center the worksheet titles, apply the Title cells style
to the worksheet titles, and decrease the font of the worksheet subtitle.
1
Display the Home tab.
2
Select the range to be merged, A1:J1 in this case, and then click the Merge & Center
button (Home tab | Alignment group) to merge and center the text in the selected range.
3
Select the range A2:J2 and then click the Merge & Center button (Home tab | Alignment
group) to merge and center the text in the selected range.
4
Select the range to contain the Title cell style, in this case A1:A2, click the Cell Styles
button (Home tab | Styles group) to display the cell styles gallery, and then click the Title
cell style in the Cell Styles gallery to apply the Title cell style to the selected range.
5
Select cell A2 and then click the Decrease Font Size button (Home tab | Font group) to
decrease the font size of the selected cell to the next lowest font size (Figure 2–33 on the
following page).
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 95
BTW
Q&A
EX 96 Excel Chapter 2 Formulas, Functions, and Formatting
What is the effect of clicking the Decrease Font Size button?
When you click the Decrease Font Size button, Excel assigns the next lowest font size in the Font
Size gallery to the selected range. The Increase Font Size button works in a similar manner but
causes Excel to assign the next highest font size in the Font Size gallery to the selected range.
Color Selection
Knowing how people
perceive colors helps you
emphasize parts of your
worksheet. Warmer colors
(red and orange) tend to
reach toward the reader.
Cooler colors (blue, green,
and violet) tend to pull
away from the reader.
Bright colors jump out of a
dark background and are
easiest to see. White or
yellow text on a dark blue,
green, purple, or black
background is ideal.
Increase Font Size button
Decrease Font Size button
Cell Styles button
Merge and Center button
font size of
worksheet
subtitle
decreased
Figure 2 – 33
To Change the Background Color and Apply a Box Border to the Worksheet
Title and Subtitle
The final formats assigned to the worksheet title and subtitle are the orange background color and thick box
border (Figure 2 – 30b on page EX 92). The following steps complete the formatting of the worksheet titles.
1
• Select the range
A1:A2 and then click
the Fill Color button
arrow (Home tab |
Font group) to display
the Fill Color gallery
(Figure 2 – 34).
Home tab
Fill Color
button arrow
desired background color
Font group
I Experiment
• Point to a number of
colors in the Fill Color
gallery to display a live
preview of the color in
the range A1:A2.
Fill Color gallery
range A1:A2
is active range
Figure 2 – 34
2
• Click Orange,
Accent 1, Lighter 60%
(column 5, row 3)
in the Fill Color
gallery to change the
background color
of the range of cells
(Figure 2 – 35).
background of range
A1:A2 is orange
Figure 2 – 35
3
• Click the Borders
Home tab
Borders button
arrow
button arrow (Home
tab | Font group) to
display the Borders list
(Figure 2 – 36).
Font group
desired border
Borders gallery
Figure 2 – 36
4
• Click Thick Box Border
in the Borders list to
display a thick box
border around the
selected range.
• Click anywhere in the
worksheet, such as
cell A18, to deselect
the current range
(Figure 2 – 37).
thick box border surrounds
worksheet title and subtitle
in cells A1:A2
Figure 2 – 37
Other Ways
1. Click Format Cells Dialog
Box Launcher (Home
tab | Font group), click
appropriate tab (Format
Cells dialog box), click
desired format, click
OK button
2. Right-click range, click
Format Cells on shortcut
menu, click appropriate
tab (Format Cells dialog
box), click desired format,
click OK button
3. Press CTRL+1, click
appropriate tab (Format
Cells dialog box), click
desired format, click OK
button
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 97
BTW
EX 98 Excel Chapter 2 Formulas, Functions, and Formatting
Background Colors
The most popular
background color is blue.
Research shows that
the color blue is used
most often because this
color connotes serenity,
reflection, and proficiency.
To Apply a Cell Style to the Column Headings and Format
the Total Rows
As shown in Figure 2 – 30b on page EX 92, the column titles (row 3) should have
the Heading 3 cell style and the totals row (row 13) should have the Total cell style. The
summary information headings in the range A14:A16 should be bold. The following steps
assign these styles and formats to row 3 and row 13 and the range A14:A16.
1
Select the range to be formatted, cells A3:J3 in this case.
2
Apply the Heading 3 cell style to the range A3:J3.
3
Click the Center button (Home tab | Alignment group) to center the column headings.
4
Apply the Total cell style to the range A13:J13.
5
Bold the range A14:A16 (Figure 2–38).
Home tab
Bold button
Format Cells:Number Dialog
Box Launcher button
Center button
Alignment group
column headings
formatted with
Heading 3 cell style
total row formatted
with Total cell style
range A14:A16
formatted bold
Figure 2 – 38
To Format Dates and Center Data in Cells
With the column titles and total rows formatted, the next step is to format the dates in column B and center
the dependents in column C. The following steps format the dates in the range B4:B12 and center the data in the
range C4:C12.
1
• Select the range to
Home tab
Format Cells
dialog box
contain the new date
format, cells B4:B12 in
this case.
• Click the Format Cells:
Number Dialog Box
Launcher (Home tab |
Number group) to
display the Format
Cells dialog box.
Number tab
Date selected
in Category list
03/14/01 style
selected in Type list
• If necessary, click the
Number tab (Format
Cells dialog box), click
Date in the Category
list, and then click
3/14/01 in the Type list
to choose the format
for the selected range
(Figure 2 – 39).
dates in range
B4:B12 selected
OK button
Figure 2 – 39
2
• Click the OK button
(Format Cells dialog
box) to format the
dates in the current
column using
the selected date
format style.
cell E4 selected
3
• Select the range C4:C12
and then click the
Center button (Home
tab | Alignment group)
to center the data in
the selected range.
• Select cell E4 to
Q&A
deselect the selected
range (Figure 2– 40).
Excel displays dates in
range B4:B12 using date
style format, mm/dd/yy
data centered in
range C4:C12
Figure 2 – 40
Can I format an entire column at once?
Yes. Rather than selecting the range B4:B12 in Step 1, you could have clicked the column B
heading immediately above cell B1, and then clicked the Center button (Home tab | Alignment
group). In this case, all cells in column B down to the last cell in the worksheet would have
been formatted to use center alignment. This same procedure could have been used to format
the dates in column C.
Other Ways
1. Right-click range, click
Format Cells on shortcut
menu, click appropriate
tab (Format Cells dialog
box), click desired format,
click OK button
2. Press CTRL+1, click
appropriate tab (Format
Cells dialog box), click
desired format, click OK
button
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 99
EX 100 Excel Chapter 2 Formulas, Functions, and Formatting
Formatting Numbers Using the Ribbon
As shown in Figure 2 – 30b on page EX 92, the worksheet is formatted to resemble
an accounting report. For example, in columns E through I, the numbers in the first row
(row 4), the totals row (row 13), and the rows below the totals (rows 14 through 16) have
dollar signs, while the remaining numbers (rows 5 through 12) in column E through
column I do not.
Plan
Ahead
Determine proper formatting for cells that include currency and other
numeric amounts.
• To append a dollar sign to a number, you should use the Accounting number format. Excel
displays numbers using the Accounting number format with a dollar sign to the left of
the number, inserts a comma every three positions to the left of the decimal point, and
displays numbers to the nearest cent (hundredths place). Clicking the Accounting Number
Format button (Home tab | Number group) assigns the desired Accounting number format.
• When you use the Accounting Number Format button to assign the Accounting number
format, Excel displays a fixed dollar sign to the far left in the cell, often with spaces
between it and the first digit. To assign a floating dollar sign that appears immediately to
the left of the first digit with no spaces, use the Currency style (Format Cells dialog box).
Whether you use the Accounting number format or the Currency style format depends on
a number of factors, including the preference of your organization, industry standards, and
the aesthetics of the worksheet.
• The Comma style format is used to instruct Excel to display numbers with commas and no
dollar signs. The Comma style format, which can be assigned to a range of cells by clicking the
Comma Style button (Home tab | Number group), inserts a comma every three positions to
the left of the decimal point and causes numbers to be displayed to the nearest hundredths.
To Apply an Accounting Number Format and Comma Style Format Using the Ribbon
The following steps assign formats using the Accounting Number Format button and the Comma Style
button (Home tab | Number group). The Accounting Number format is applied to the currency amounts in rows 4
and 13. The Comma style is applied to the range E4:I12 and to column D (Hours Worked).
1
• Select the range to contain the
Accounting
Number button
Accounting Number Format, cells
E4:I4 in this case.
• While holding down the CTRL key,
select the range F13:I13 to select the
nonadjacent range.
Number group
• Click the Accounting Number
Q&A
Format button (Home tab | Number
group) to apply the Accounting
number format with fixed dollar
signs to the selected nonadjacent
ranges (Figure 2– 41).
What is the effect of applying
the Accounting number format?
Excel displays
nonadjacent
range E4:I4 and
F13:I13 using the
Accounting number
format with fixed
dollar signs
The Accounting Number Format
button assigns a fixed dollar
sign to the numbers in the
ranges E4:I4 and F13:I13. In each cell
in these ranges, Excel displays the
dollar sign to the far left with spaces
between it and the first digit in the cell.
Figure 2 – 41
2
• Select the range to
Comma Style button
contain the Comma
style format, cells
E5:I12 in this case.
width of columns
automatically
increased due to
formatting
• Click the Comma Style
button (Home tab |
Number group) to
assign the Comma
style format to
the selected range
(Figure 2 – 42).
Number group
Excel displays range
E5:I12 using Comma
style format
range E5:I12
selected
Figure 2 – 42
3
• Select the range to
Comma Style button
Home tab
contain the Comma
style format, cells
D4:D16 in this case.
• Click the Comma Style
button (Home tab |
Number group) to
assign the Comma
style format to
the selected range
(Figure 2 – 43).
Excel displays
numbers to two
decimal places
Number group
Excel displays
range D4:D16
using Comma
Style format
Figure 2 – 43
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 101
EX 102 Excel Chapter 2 Formulas, Functions, and Formatting
To Apply a Currency Style Format with a Floating Dollar Sign Using the
Format Cells Dialog Box
The following steps use the Format Cells dialog box to apply the Currency style format with a floating dollar
sign to the numbers in the range E14:I16.
1
• Select the range
E14:I16 and then
click the Format Cells:
Number Dialog Box
Launcher (Home tab
| Number group) to
display the Format
Cells dialog box.
Home tab
Format Cells dialog box
Number tab
• If necessary, click
the Number tab
(Format Cells dialog
box) to display the
Number tab (Format
Cells dialog box)
(Figure 2 – 44).
Range E14:I16
selected
Figure 2 – 44
2
• Click Currency in the
Q&A
Category list to select
the necessary number
format category, and
then click the third
style ($1,234.10) in
the Negative numbers
list (Format Cells
dialog box) to select
the desired currency
format for negative
numbers (Figure 2–45).
sample of how number in
upper-left cell of selected
range will appear
Currency style
selected
number of decimal places
$ symbol selected
Negative
numbers list
How do I select the
proper format?
You can choose from
OK button
12 categories of
formats. Once you
select a category, you
can select the number
of decimal places,
whether or not a
dollar sign should be
Figure 2 – 45
displayed, and how
negative numbers should appear. Selecting the appropriate negative numbers format is
important, because doing so adds a space to the right of the number in order to align the
numbers in the worksheet on the decimal points. Some of the available negative number
formats do not align the numbers in the worksheet on the decimal points.
3
• Click the OK button (Format Cells
Q&A
dialog box) to assign the Currency
style format with a floating
dollar sign to the selected range
(Figure 2 – 46).
Excel displays range
E14:I16 using Currency
style format with
floating dollar signs
What is the difference between
using the Accounting Number
style and Currency style?
Figure 2 – 46
When using the Accounting Number
Style button, recall that a floating dollar sign always appears immediately to the left of the first
digit, and the fixed dollar sign always appears on the left side of the cell. Cell E4, for example,
has a fixed dollar sign, while cell E14 has a floating dollar sign. The Currency style was assigned
to cell E14 using the Format Cells dialog box and the result is a floating dollar sign.
Other Ways
1. Press CTRL+1, click
Number tab (Format Cells
dialog box), click Currency
in Category list, select
format, click OK button
2. Press CTRL+SHIFT+DOLLAR
SIGN ($)
To Apply a Percent Style Format and Use the Increase Decimal Button
The next step is to format the tax % in column J. Currently, Excel displays the numbers in column J as a
decimal fraction (for example, 0.256 in cell J4). The following steps format the range J4:J16 to the Percent style
format with two decimal places.
Percent Style
button
1
• Select the range to format, cell
Increase Decimal
button
J4:J16 in this case.
Decrease Decimal
button
• Click the Percent Style button (Home
Q&A
tab | Number group) to display the
numbers in the selected range as a
rounded whole percent.
Number group
What is the result of clicking the
Percent Style button?
The Percent Style button instructs
Excel to display a value as a percentage, determined by multiplying the
cell entry by 100, rounding the result
to the nearest percent, and adding a
percent sign. For example, when cell
J4 is formatted using the Percent Style
buttons, Excel displays the actual value
0.256 as 26%.
Excel displays range
J4:J16 using Percent
style format with
two decimal places
2
• Click the Increase Decimal button
(Home tab | Number group) two
times to display the numbers in the
selected range with two decimal
places (Figure 2– 47).
Figure 2 – 47
Other Ways
1. Right-click range, click
Format Cells on shortcut
menu, click Number tab
(Format Cells dialog
box), click Percentage
in Category list, select
format, click OK button
in Category list, select
format, click OK button
2. Press CTRL+1, click Number
tab (Format Cells dialog
box), click Percentage
3. Press CTRL+SHIFT+ percent
sign (%)
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 103
EX 104 Excel Chapter 2 Formulas, Functions, and Formatting
Conditional Formatting
The next step is to emphasize the values greater than 70 in column D by formatting
them to appear with an orange background and white font color (Figure 2 –48).
BTW
Plan
Ahead
Conditional Formatting
You can assign any
format to a cell, a range
of cells, a worksheet,
or an entire workbook
conditionally. If the value
of the cell changes and
no longer meets the
specified condition, Excel
suppresses the conditional
formatting.
Establish rules for conditional formatting.
• Excel lets you apply formatting that appears only when the value in a cell meets conditions
that you specify. This type of formatting is called conditional formatting. You can apply
conditional formatting to a cell, a range of cells, the entire worksheet, or the entire
workbook. Usually, you apply conditional formatting to a range of cells that contains
values you want to highlight, if conditions warrant.
• A condition, which is made up of two values and a relational operator, is true or false for each
cell in the range. If the condition is true, then Excel applies the formatting. If the condition is
false, then Excel suppresses the formatting. What makes conditional formatting so powerful
is that the cell’s appearance can change as you enter new values in the worksheet.
• As with worksheet formatting, follow the less-is-more rule when considering conditional
formatting. Use conditional formatting to make cells and ranges stand out and raise
attention. Too much conditional formatting can result in confusion for the reader of the
worksheet.
To Apply Conditional Formatting
The following steps assign conditional formatting to the range D4:D12, so that any cell value greater than 70
will cause Excel to display the number in the cell with an orange background and a white font color.
1
• Select the range
Conditional
Formatting button
D4:D12.
• Click the Conditional
Styles group
Formatting button
(Home tab | Styles
group) to display
the Conditional
Formatting list
(Figure 2 – 48).
Conditional
Formatting list
New Rule
command
range
D4:D12
selected
Figure 2 – 48
2
• Click New Rule in
the Conditional
Formatting list to
display the New
Formatting Rule
dialog box.
• Click ‘Format only
cells that contain’ in
the Select a Rule Type
area (New Formatting
Rule dialog box) to
change the ‘Edit the
Rule Description’ area.
New Formatting
Rule dialog box
Select a Rule
Type list
‘Format only cells
that contain’ rule
type selected
Edit the Rule
Description
area
value 2
relational operator
value 1
Format button
• In the ‘Edit the Rule
Description’ area, click
the box arrow in the
relational operator
box (second text box)
to display a list of
relational operators,
and then select
greater than to select the desired operator.
Figure 2 – 49
• Select the rightmost box, and then type 70 in the box in the ‘Edit the Rule Description’
Q&A
area to enter the second value of the rule description (Figure 2–49).
What do the changes in the ‘Edit the Rule Description’ indicate?
The ‘Edit the Rule Description’ area allows you to view and edit the rules for the conditional
format. In this case, reading the area indicates that Excel should conditionally format only
cells with cell values greater than 70.
3
• Click the Format
Format Cells dialog box
Fill tab
button (New
Formatting Rule
dialog box) to display
the Format Cells
dialog box.
• If necessary, click
the Font tab. Click
the Color box arrow
(Format Cells dialog
box) to display the
Color gallery and
then click White,
Background 1
(column 1, row 1) in
the Color gallery to
select the font color.
• Click the Fill tab
(Format Cells dialog
box) to display the
Fill sheet and then
click the orange color in
column 5, row 5 to select the background color (Figure 2–50).
desired font
color
Sample area
displays font
color
OK button
Figure 2 – 50
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 105
EX 106 Excel Chapter 2 Formulas, Functions, and Formatting
4
• Click the OK button
New Formatting
Rule dialog box
(Format Cells dialog
box) to close the
Format Cells dialog
box and display the
New Formatting
Rule dialog box with
the desired font
and background
colors displayed in
the Preview box
(Figure 2 – 51).
Preview box displays
format assigned
to cell in selected
range if value in cell
is greater than 70
OK button
Figure 2 – 51
5
• Click the OK button to
assign the conditional
format to the selected
range.
• Click anywhere in the
worksheet, such as
cell A18, to deselect
the current range
(Figure 2 – 52).
Excel displays numbers
greater than 70 in range
D4:D12 with an orange
background and white
font color
Figure 2 – 52
Conditional Formatting Operators
As shown in Figure 2–49 on page EX 105, the second text box in the New Formatting
Rule dialog box allows you to select a relational operator, such as less than, to use in the
condition. The eight different relational operators from which you can choose for conditional
formatting in the New Formatting Rule dialog box are summarized in Table 2–5.
Relational Operator
Description
between
Cell value is between two numbers.
not between
Cell value is not between two numbers.
equal to
Cell value is equal to a number.
not equal to
Cell value is not equal to a number.
greater than
Cell value is greater than a number.
less than
Cell value is less than a number.
greater than or equal to
Cell value is greater than or equal to a number.
less than or equal to
Cell value is less than or equal to a number.
Changing the Widths of Columns and Heights of Rows
When Excel starts and displays a blank worksheet on the screen, all of the columns
have a default width of 8.43 characters, or 64 pixels. These values may change depending
on the theme applied to the workbook. For example, in this chapter, the Trek theme was
applied to the workbook, resulting in columns having a default width of 8.11 characters. A
character is defined as a letter, number, symbol, or punctuation mark in 11-point Calibri
font, the default font used by Excel. An average of 8.43 characters in 11-point Calibri font
will fit in a cell.
Another measure of the height and width of cells is pixels, which is short for
picture element. A pixel is a dot on the screen that contains a color. The size of the dot
is based on your screen’s resolution. At the resolution of 1024 × 768 used in this book,
1024 pixels appear across the screen and 768 pixels appear down the screen for a total
of 786,432 pixels. It is these 786,432 pixels that form the font and other items you see
on the screen.
The default row height in a blank worksheet is 15 points (or 20 pixels). Recall
from Chapter 1 that a point is equal to 1/72 of an inch. Thus, 15 points is equal to
about 1/5 of an inch. You can change the width of the columns or height of the rows at
any time to make the worksheet easier to read or to ensure that Excel displays an entry
properly in a cell.
BTW
Table 2 – 5 Summary of Conditional Formatting Relational Operators
Hidden Rows and
Columns
For some people, trying to
unhide a range of columns
using the mouse can be
frustrating. An alternative
is to use the keyboard:
select the columns to
the right and left of the
hidden columns and then
press CTRL+SHIFT+) (RIGHT
PARENTHESIS). To use the
keyboard to hide a range
of columns, press CTRL+0
(ZERO). You also can use
the keyboard to unhide a
range of rows by selecting
the rows immediately
above and below the
hidden rows and then
pressing CTRL+SHIFT+( (LEFT
PARENTHESIS). To use the
keyboard to hide a range
of rows, press CTRL+9.
To Change the Widths of Columns
When changing the column width, you can set the width manually or you can instruct Excel to size the
column to best fit. Best fit means that the width of the column will be increased or decreased so that the widest
entry will fit in the column. Sometimes, you may prefer more or less white space in a column than best fit provides.
To change the white space, Excel allows you to change column widths manually.
When the format you assign to a cell causes the entry to exceed the width of a column, Excel automatically
changes the column width to best fit. If you do not assign a format to a cell or cells in a column, the column width
will remain 8.43 characters. To set a column width to best fit, double-click the right boundary of the column heading
above row 1.
The steps on the following pages change the column widths: column A, B, and C to best fit; column H to
10.22 characters; and columns D, E, and J to 7.56 characters.
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 107
EX 108 Excel Chapter 2 Formulas, Functions, and Formatting
1
• Drag through column
headings A, B, and C
above row 1 to select
the columns.
columns A, B,
and C selected
mouse pointer pointing
to right boundary of
column C
• Point to the boundary
Q&A
on the right side of
column heading C
to cause the mouse
pointer to become
a split double arrow
(Figure 2 – 53).
What if I want to
make a large change
to the column width?
If you want to increase
or decrease column
width significantly,
you can right-click a
column heading and
Figure 2 – 53
then use the Column
Width command on the shortcut menu to change the column’s width. To use this command,
however, you must select one or more entire columns.
2
• Double-click the right
boundary of column
heading C to change
the width of the
selected columns to
best fit.
mouse pointer pointing
to right boundary of
column H
• Point to the boundary
on the right side of
the column H heading
above row 1.
• When the mouse
Q&A
pointer changes
to a split double
arrow, drag until the
ScreenTip indicates
Width: 10.22 (99
pixels). Do not release
the mouse button
(Figure 2– 54).
ScreenTip shows
proposed column
width
column widths
A, B, and C set
to best fit
dotted line shows
proposed right
border of column H
What happens if
I change the column
width to zero (0)?
If you decrease the
Figure 2 – 54
column width to 0,
the column is hidden. Hiding cells is a technique you can use to hide data that might not
be relevant to a particular report or sensitive data that you do not want others to see. To
instruct Excel to display a hidden column, position the mouse pointer to the right of the
column heading boundary where the hidden column is located and then drag to the right.
3
• Release the mouse
button to change the
column width.
columns D, E,
and J selected
• Click the column D
heading above row 1
to select the column.
• While holding down
the CTRL key, click the
column E heading
and then the column J
heading above row 1
so that nonadjacent
columns are selected
(Figure 2 – 55).
new column H width
Figure 2 – 55
4
• If necessary, scroll
the worksheet to the
right so that the right
border of column J is
visible. Point to the
boundary on the right
side of the column J
heading above row 1.
columns D, E,
and J selected
ScreenTip
shows proposed
column widths
mouse pointer
• Drag until the
ScreenTip indicates
Width: 7.56 (75 pixels).
Do not release the
mouse button
(Figure 2 – 56).
Figure 2 – 56
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 109
EX 110 Excel Chapter 2 Formulas, Functions, and Formatting
5
• Release the mouse
button to change the
column widths.
widths of columns
D, E, and J set to
7.56 characters
width of columns
A, B, and C set to
best fit
• If necessary, scroll the
worksheet to the left
so that the left border
of column A is visible.
• Click anywhere
in the worksheet,
such as cell A18, to
deselect the columns
(Figure 2 – 57).
Width of column
H set to 10.22
Other Ways
1. Right-click column
heading or drag
through multiple
column headings and
right-click, click Column
Width on shortcut
menu, enter desired
column width, click OK
button
Figure 2 – 57
To Change the Heights of Rows
When you increase the font size of a cell entry, such as the title in cell A1, Excel automatically increases the
row height to best fit so that it can display the characters properly. Recall that Excel did this earlier when multiple
lines were entered in a cell in row 3, and when the cell style of the worksheet title and subtitle was changed.
You also can increase or decrease the height of a row manually to improve the appearance of the worksheet.
The following steps improve the appearance of the worksheet by increasing the height of row 3 to 48.00 points and
increasing the height of row 14 to 27.00 points.
1
• Point to the boundary
below row heading 3.
• Drag down until the
ScreenTip indicates
Height: 48.00
(64 pixels). Do not
release the mouse
button (Figure 2– 58).
ScreenTip shows
proposed height
of row 3
current bottom
border of row 3
mouse
pointer
dotted line shows
proposed bottom
border of row 3
Figure 2 – 58
2
• Release the mouse
button to change the
row height.
row 3 height
is 48.00 points
• Point to the boundary
below row heading 14.
• Drag down until the
ScreenTip indicates
Height: 27.00
(36 pixels). Do not
release the mouse
button (Figure 2– 59).
current bottom
border of row 14
ScreenTip shows
proposed height
of row 14
dotted line shows
proposed bottom
border of row 14
mouse pointer
Figure 2 – 59
3
• Release the mouse
button to change the
row height.
formatting
of worksheet
complete
• Click anywhere in the
Q&A
worksheet, such as
cell A18, to deselect
the current cell
(Figure 2 – 60).
Can I hide a row?
Yes. As with column
widths, when you
decrease the row
height to 0, the row
is hidden. To instruct
Excel to display a
hidden row, position
the mouse pointer
just below the row
heading boundary
where the row is
hidden and then
drag down. To set a
row height to best
fit, double-click the
bottom boundary of
the row heading.
row 14
height is
27.00 points
added white space
in row 14 improves
appearance of
worksheet
Figure 2 – 60
Other Ways
1. Right-click row heading
or drag through multiple
row headings and rightclick, click Row Height
on shortcut menu, enter
desired row height, click
OK button
Excel Chapter 2
Formulas, Functions, and Formatting Excel Chapter 2 EX 111
EX 112 Excel Chapter 2 Formulas, Functions, and Formatting
Break Point: If you wish to take a break, this is a good place to do so. Be sure to save the The Mobile Masses Biweekly
Payroll Report file again and then you can quit Excel. To resume at a later time, start Excel, open the file called The Mobile
Masses Biweekly Payroll Report and continue following the steps from this location forward.
BTW
Spell Checking
While Excel’s spell checker
is a valuable tool, it is
not infallible. You should
proofread your workbook
carefully by pointing to
each word and saying it
aloud as you point to it. Be
mindful of misused words
such as its and it’s, through
and though, and to and
too. Nothing undermines
a good impression more
than a professional
looking report with
misspelled words.
Checking Spelling
Excel includes a spell checker you can use to check a worksheet for spelling errors. The
spell checker looks for spelling errors by comparing words on the worksheet against
words contained in its standard dictionary. If you often use specialized terms that are not
in the standard dictionary, you may want to add them to a custom dictionary using the
Spelling dialog box.
When the spell checker finds a word that is not in either dictionary, it displays the
word in the Spelling dialog box. You then can correct it if it is misspelled.
To Check Spelling on the Worksheet
To illustrate how Excel responds to a misspelled word, the following steps misspell purposely the word,
Employee, in cell A3 as the word, Empolyee, as shown in Figure 2 – 61.
1
• Click cell A3 and then
Spelling button
Review tab
Spelling dialog box
type Empolyee
to misspell the word
Employee.
Ignore Once button
• Select cell A1 so that
the spell checker
begins checking at
the selected cell.
Proofing group
Ignore All and Change
All buttons impact words
throughout document
Add to Dictionary button
desired word in
Suggestions list
Change button
• Click Review on the
Ribbon to display the
Review tab.
• Click the Spelling
AutoCorrect button
misspelled
word
indicates language to
spell check against
button (Review tab |
Proofing group) to
run the spell checker
and display the
misspelled word in the
Spelling dialog box
(Figure 2 – 61).
Q&A
Figure 2 – 61
What happens when the spell checker finds a misspelled word?
When the spell checker identifies that a cell contains a word not in its standard or custom
dictionary, it selects that cell as the active cell and displays the Spelling dialog box. The
Spelling dialog box lists the word not found in the dictionary and a list of suggested
corrections (Figure 2 – 61).
Save button
2
• Click the Change button (Spelling
dialog box) to change the
misspelled word to the correct
word (Figure 2– 62).
• Click the Close button (Spelling
dialog box) to close the Spelling
dialog box.
• If the Microsoft Excel dialog box
Empolyee changed
to Employee
Microsoft Excel
dialog box
is displayed, click the OK button.
3
• Click anywhere in the worksheet, such
as cell A18, to deselect the current cell.
• Display the Home tab.
OK button
• Click the Save button on the Quick
Q&A
Access Toolbar to save the workbook.
Figure 2 – 62
What other actions can I take in the Spelling dialog box?
If one of the words in the Suggestions list is correct, click it and then click the Change
button. If none of the suggestions is correct, type the correct word in the Not in Dictionary
text box and then click the Change button. To change the word throughout the worksheet,
click the Change All button instead of the Change button. To skip correcting the word, click
the Ignore Once button. To have Excel ignore the word for the remainder of the worksheet,
click the Ignore All button.
Other Ways
1. Press F7
Consider these additional guidelines ...
Purchase answer to see full
attachment