Formulas, Functions, and Formatting IMF

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 MAX, MIN, and AVERAGE functions Verify a formula using Range Finder Apply a theme to a workbook Apply a date format to a cell or range

• Check the spelling on a worksheet Change margins and headers in Page Layout view Preview and print versions and sections of a worksheet

Microsoft Excel 2013

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 illustrate these topics and presents some new ones. The new topics covered in this chapter include using formulas and functions to create a worksheet. A fimctioii is a prewritten formula that is built into Excel. Other new topics include option buttons, verifying formulas, applying a theme to a worksheet, borders, formatting numbers and text, conditional formatting, changing the widths of columns and heights of rows, spell checking, 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. HyperMass Online Storage established its online website to provide online storage for its customers. The owners pay employees to maintain the servers and handle customer requests. 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, number of dependents, hours worked, hourly pay rate, tax information, net pay, and hire date. As the complexity of the task of creating the summary increases, the owners want to use Excel to create a salary report.

EX 66

"T^n

SI H *3

C*

HOME

4 -

-

n,;:,.i.

II«F.RT

PAGE LAYOUT

c°'M B

/111!

-_ii

J LJ -

/«

- I l l

-

FORMULAS

DMA

KlVltW

- A' A" ~ ^ m *•• /'. - ,£, -

— Z

1 •:_•>:

3v.vpTc,i

S 6 1

a 9 10

11

•a 13

1

S-JMeirjeatrnl,.

!

•".

a i

1,

A

VIKV

B

3

licrosoft Excel 2013

HyperMass Online Stni.irjc Salary Report H0u,-. Houily Em|i!oyco [isijcnijoiits \Vaiked Pay Rate firossP.-iy Fctk-rjiTjx HitfTjs CMl,Mkha«l 3 GU41 s in 50 i 1,1787" S .',7 iq :;,3=8 '. Gr«n, Su< o ».es 1,862.28 ,09.70 7., ,-,y ppkir, Sith i fiij.so IV 36 i,4»(3.f; ifio oy 4!! ;ii Lan-Jo., 4 -,^, 1,269 " ;085o 07fi ill .'tS l-lichoti, I'rter 3 „.«, 097 7S i"i -,5 3501 Pf arson, AOo 3 ;(/,.(if 40.10 1,00233 2n/h./,a j j 45 Roiiiiquu, luan 2 5550 2O. AC 1,110 33 .1)5-3;, .-,:, 7J 1,668.110 3(16 96 (j!i 72 aoBj WJkamj, f."Dii o HSuffl Y a u,Xir, 4_ ?o. 0n 5-1 ^c 1«7S 1,313.50 ^67-35 Totals yM.io 460.63 1 ii,5i.F-Gs * J,',!M', t

J-4 Highest 15 Low«t ib Avcrafjo

5

4 0

^

Ui 50 .-,.-,6; 6646

I.IK".., ;;, C,^'-

26.00"; 25561: 25lfiV;

zi, ii:; ;.'(./(u-/. 250;,?; ilj.Qfi" : 2(, 37"' ':

i^.ie^i

*H.8S ui.6a

•n,863.sU 189775

^oH a 3 3 lU 3.',S »', 680.39 75,7.0-'. rt33;i> 1,23432 932 115 1 0,617.89

••.•'5'io 7;i;/ii

1/13/09 J'1'12 't'iS.'-ii 10/31/08 7ii&:o9 S/J.I-,,'09 '1/17/01

11,378.08 :63o 59 • 957.S*

i?

1

g 19 Salary Report

:'+•

iirr

-

_

.

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 HyperMass Online Storage Salary Report worksheet includes the following needs: source of data, summary of calculations, and other facts about its development (Figure 2-2).

Worksheet Title

HyperMass Online Storage Salary Report

Needs

An easy-to-read worksheet that summarizes the company's salary report (Figure 2-3 on the next page). For each employee, the worksheet is to include the employee's name, dependents, hours worked, hourly pay rate, gross pay, federal tax, state tax, total tax percent, net pay, and hire date. The worksheet also should include totals and the highest value, lowest value, and average for the columns of numbers specified below. The data supplied includes the employee names, dependents, hours worked, hourly pay rate, and hire dales. 1. 2. 3. 4. 5. 6. 7. 8.

Gross Pay = Hours Worked x Hourly Pay Rate Federal Tax = 0.22 x (Gross Pay - Dependents State Tax = 0.04 x Gross Pay Tax % = (Federal Tax + State Tax) / Gross Pay Net Pay = Gross Pay - (Federal Tax + State Tax) Compute the totals for hours worked, gross pay, federal tax, state tax, and net pay Compute the total tax percent Use the MAX and MIN functions to determine the highest and lowest values for dependents, hours worked, hourly pay rate, gross pay, federal tax, state tax, total tax percent, and net pay 9. Use the AVERAGE function Eo determine the average for hours worked, dependents, hourly pay rate, gross pay, federal tax, state tax, and net pay Figure 2-2

EX 67

EX 68 Excel Chapter 2 Formulas, Functions, and Formatting

In addition, using a sketch of the worksheet can help you visualize its design. The sketch for the I lypcrMass Online Storage Salaiy Report worksheet includes a title, a suhtivle, column and row headings, and the location of data values (Figure 2-3). It also uses specific characters to define the desired formatting for the worksheet, as follows: 1. The row of Xs below the leftmost column heading 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 Os. The 9s indicate that the selected format should instruct Excel to display any digits, including Os. 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 last column identify the cell entry as a date. 5. The dollar signs that are adjacent to the Zs below the totals row signify a floating dollar sign, or one that appears next to the first significant digit. 6. The commas indicate that the selected format should instruct Excel to display a comma separator only if the number has sufficient digits (values in the thousandths) to the left of the decimal point. 7. The percent sign (%) in the Tax % column indicates a percent sign should appear after the number.

skeich of worksheet

HyperMass Online Storage Salary Report

\ 1 / \ 1 / \ 1 / \ J / \ 1 /

Highest Lowest Average

99 99

\]/

\1/

J,

J/

\Jx

w

w

\lx

Figure 2-3

Roadmap In this chapter, you will learn how to use functions and create formulas. The following roadmap identifies general activities you will perform as you progress through this chapter: 1. ENTER FORMULAS in the worksheet. 2. ENTER FUNCTIONS in the worksheet.

Formulas, Functions, and Formatting

Excel Chapter 2 EX 69

3. VERIFY FORMULAS in the worksheet. 4. FORMAT the WORKSHEET. 5. CHECK SPELLING. 6. PRINT the WORKSHEET.

At the beginning of step instructions throughout the chapter, you will see an abbreviated form of this roadmap. The abbreviated roadmap uses colors to indicate chapter progress: gray means the chapter is beyond that activity, blue means the task being shown is covered in that activity, and black means that activity is yet to be covered. For example, the following- abbreviated roadmap indicates the chapter would be showing a task in the 2 ENTER FUNCTIONS activity. : : DijvtJL,--; | z ENTER FUNCTIONS j 3 VERIFY FORMULAS 4 FORMAT WORKSHEET | 5 CHECK SPELLING j E PRINT WORKSHEET

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 supersede visual aesthetics. When designing your worksheet, what steps should be taken to ensure this? • Avoid the temptation to use flashy or confusing visual elements within the worksheet. HI

• Understand the requirements document.

9 LH

• Choose the proper functions and formulas. • Build the worksheet,

To Run Excel and Create a Blank Workbook 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 13f>6 X 768. For Information about how to change a computer's resolution, refer to the Office and Windows chapter at the beginning- of this book. The following steps, which assume Windows 8 is running, use the Start screen or the search box to run Excel based on a typical installation. You may need to ask your instructor how to run Excel on your computer. For a detailed example of the procedure summarized below, refer to the Office and Windows chapter. mm Scroll the Start screen for an Excel 2013 tile, if your Start screen contains an Excel 2013 tile, tap or click it to run Excel and then proceed to Step 5; if the Start screen does not contain the Excel 2013 tile, proceed to the next step to search for the Excel app. ^J Swipe in from the right edge of the screen or point to the upper-right corner of the screen to display the Charms bar and then tap or click the Search charm on the Charms bar to display the Search menu. ^J Type Excel as the search text in the Search box and watch the search results appear in the Apps list. ^J Tap or click Excel 2013 in the search results to run Excel. ^J Tap or click the Blank workbook thumbnail on the Excel start screen to create a blank workbook and display it in the Excel window. If the Excel window is not maximized, tap or click the Maximize button on its title bar to maximize the window.

2

O

o

For an introduction to Windows and instruction about how to perform basic Windows tasks, read the Office and Windows 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.

EX 70 Excel Chapter 2 Formulas, Functions, and Formatting

For an introduction to Office and instruction about how to perform basic tasks in Office programs, read the Office and Windows chapter at the beginning of this book, where you can learn how to run a program, use the ribbon, save a file, open a file, exit a program, use Help, and much more.

Entering the Titles and Numbers into the Worksheet 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 salary report data shown in Table 2-1 on page F,X 71.

To Enter the Worksheet Title and Subtitle 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. The following steps enter the worksheet title and subtitle into cells Al and A2. If necessary, select cell A1. Type HyperMass Online Storage in the selected cell and then press the DOWN ARROW key to enter the worksheet title. Type Salary Report worksheet subtitle.

in cell A2 and then press the DOWN ARROW key to enter the

To Enter the Column Titles 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 1 2 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. The column titles in row 3 begin in cell A3 and extend through cell J3. Some of the column titles in Figure 2-4 include multiple lines of text, such as Hours Worked in cell C3. To start a new line in a cell, press ALT+ENTER after each line, except for the last line, which is completed by tapping or 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. Q With cell A3 selected, type Employee and then press the RIGHT ARROW key to enter the column heading. ^J Type Dependents and then press the RIGHT ARROW key to enter the column heading. ^J In cell C3, 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. Type Hourly in cell D3 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. Type Gross Pay column heading.

in cell E3 and then press the RIGHT ARROW key to enter the

Type Federal Tax column heading.

in cell F3 and then press the RIGHT ARROW key to enter the

Formulas, Functions, and Formatting

Type State Tax column heading.

in cell G3 and then press the RIGHT ARROW key to enter the

Type Tax

% in cell H3 and then press the RIGHT ARROW key to enter the column heading.

Type Net heading.

Pay

in cell 13 and then press the RIGHT ARROW key to enter the column

Type Hire Date in cell J3 and then press the RIGHT ARROW key to enter the column heading.

To Enter the Salary Data The salary 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 die data for each employee: name, dependents, hours worked, hourly pay rate, and hire date. qj) Select cell A4. Type Carl, Michael and then press the RIGHT ARROW key to enter the employee name. ^J Type 3 in cell B4 and then press the RIGHT ARROW key to enter a number in the selected cell. ^J Type 60 . 45 in cell C4 and then press the RIGHT ARROW key to enter a number in the selected cell. ^J Type 19 . 50 in cell D4 and then press the RIGHT ARROW key to enter a number in the selected cell. ^y Type 4/5/10

in cell J4 and then tap or click cell A5 to select the cell.

Cj Enter the payroll data in Table 2-1 for the eight remaining employees in rows 5 through 12 (shown in Figure 2-4 on page 72). < :ln Step 5, why did the date that was entered change from 4/5/10 to 4/5/2010? Q-'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 HyperMass Online Storage Salary Report Data Employee Carl, Michael Green, Sue Fekir, Sith Lane, Jon Nichols, Peter Pearson, Ada Rodriquez, Juan Williams, Sean Yau, Xin

Dependents

Hours Worked

Hourly Pay Rate

Hire Date

Excel Chapter 2 EX 71

EX 72 Excel Chapter 2 Formulas, Functions, and Formatting

To Enter the Row Titles The following steps add row titles for the rows that will contain the totals, highest, lowest, and average amounts. Select cell A13. Type Totals and then press the DOWN ARROW key to enter a row header. Type Highest header.

in cell A14 and then press the DOWN ARROW key to enter a row

Type Lowest in cell A1 5 and then press the ENTER key to enter a row header. TYPe

Average in cell A16 and then press the DOWN ARROW key to enter a row header (Figure 2-4).

Figure 2-4

To Change the Worksheet Tab Name and Color and Save the Workbook The following steps change the worksheet tub name to Salary Report, change the sheet tab color, and save the workbook in the Kxcel folder (for your assignments) using the file name, 1 JyperMass Online Storage Salary Report. fQ| Double-tap or double-click the Sheet! tab and then enter Salary Report as the sheet tab name and then press the ENTER key. ^J Press and hold or right-click the sheet tab to display the shortcut menu. ^J Tap or point to Tab Color on the shortcut menu to display the Tab Color gallery. Tap or click Green, Accent 6, Darker 25% (column 10, row 5) in the Theme Colors area to apply a new color to the sheet tab.

Formulas, Functions, and Formatting

Excel Chapter 2 EX 73

Tap or click the Save button on the Quick Access Toolbar, which depending on settings, will display either the Save As gallery in the Backstage view or the Save As dialog box. To save on a hard disk or other storage media on your computer, proceed to Step 5a. To save on SkyDrive, proceed to Step 5b. If your screen opens the Backstage view and you want to save on storage media on your computer, tap or click Computer, if necessary, to display options in the right pane related to saving on your computer. If your screen already displays the Save As dialog box, proceed to Step 6. If your screen opens the Backstage view and you want to save on SkyDrive, tap or click SkyDrive to display SkyDrive saving options or a Sign In button. If your screen displays a Sign In button, tap or click it and then sign in to SkyDrive. Tap or click the Browse button in the right pane to display the Save As dialog box associated with the selected Save As Place (i.e.. Computer or SkyDrive). Type HyperMass Online Storage Salary Report in the File name 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. Navigate to the desired save location {in this case, the Excel folder in the CIS 101 folder [or your class folder] on your computer or SkyDrive). Tap or click the Save button (Save As dialog box) to save the document in the selected folder on the selected location 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 60.45 by 19.5 and then manually enter the product for Gross Pay, 1,178.78, in cell E4. Evciy time the values in cells C4 or D4 changed, you would have to recalculate the product and enter die new value in cell E4.13y contrast, if you enter a formula in cell E4 to multiply the values in cells C4 and D4, Excel recalculates the product whenever new values are entered into those cells and displays the result in cell E4. 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 Al 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. 1 ENTER FORMULAS | 1 ENTER FUNCTIONS | 3 VERIFY FORMULAS

To Enter a Formula Using the Keyboard

4 FORMAT WORKSHEET I S CHECK SPELLING I G PRINT WORKSHEET

The formulas needed in the worksheet are noted in the requirements document as follows: 1. 2. 3. 4. 5.

Gross Pay (column E) = Hours Worked x Hourly Pay Rate Federal Tax (column F) = 0.22 x (Gross Pay - Dependents x 24.32) State Tax (column G) = 0.04 x Gross Pay Tax % (column H) = (Federal Tax + State Tax) / Gross Pay Net Pay (column T) = Gross Pay - (Federal Tax + State lax)

EX 74 Excel Chapter 2 Formulas, Functions, and Formatting

Tbc gross pay for each employee, which appears in column E, is equal to hours worked in column C times hourly pay rate in column D. Thus, the gross pay for Michael Carl in cell E4 is obtained by multiplying 60.45 (cell C4) by 19.50 (cell D4) or =C4 x D4. The following steps enter the initial gross pay formula in cell E4 using the keyboard. Why? In order for Excel To perform the calculations, yon must create formulas. With cell E4 selected, type =c4*d4 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).

Storage Sulsry Ri-pnri Fit

as formula is typed, it appear;, in formula bar and in active cell E4

Mite Dilte .1/5/2010 7/15/2011 1/13/2009 3/1/2012 -1/15/2011 10/31/200S 7/15/2009 5/14/2009 C./17/2002

What occurs on the worksheet as I enter colored ce the formula? borders indicate nduded !The equal sign (-) in formula in preceding c4*d4 active ce alerts Excel that you are entering a formula or function and not text. Because the Figure 2-5 most common error when entering a formula is to reference the wrong cell in a formula mistakenly. Excel colors the borders of the cells referenced in the formula. The coloring helps in the reviewing process to ensure the celi references are correct. The asterisk {*) following c4 is the arithmetic operator that directs Excel to perform the multiplication operation.

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 E4 may be different, but these values will be adjusted later in this chapter.

HOME

INSERT

PAG[LAYOUT

FORM'JI/iS

DATA

REVIf.V

Figure 2-6

Formulas, Functions, and Formatting

Arithmetic Operations Excel provides powerful functions and capabilities that allow you to perform arithmetic operations easily and efficiently. Table 2-2 describes multiplication and other valid Excel arithmetic operators.

Table 2-2 Summary of Arithmetic Operators Arithmetic Operator

Meaning

Example of Usage

Result Negative 78 Multiplies 23 by 0.01 Raises 3 to the fourth power Multiplies the contents of cell C5 by 61.5 Divides the contents of cell H3 by the contents of cell H1 Adds 11 and 9 Subtracts the contents of cell F1 5 from 22

Order of Operations 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 (A), 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.

Table 2-3 Examples of Excel Formulas Formula

Result

=G15 =2 A 4 + 7 = 100 + D2 or=D2 +100 or =(100 + D2) =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. Assigns the difference between the values contained in cells US and B8 times 6 to the active cell. Completes the following operations, from left to right: exponentiation (22 A L7), then division (J7 / AS), then multiplication (G9 * M6), then addition (J7 / A5) + (G9 * M6), and finally subtraction (J7 / AS + G9 * M6) - (22 A |_7). If cells A5 = 6, G9 = 2, J7 = 6, L7 = 4, M6 = 5, and 22 = 2, then Excel assigns the active cell the value -5; that is, 6 / 6 + 2 * 5 - 2 A 4 = -5.

Excel Chapter 2 EX 75

EX 76 Excel Chapter 2 Formulas, Functions, and Formatting 1 ENTER FORMULAS j 2 ENTER fUNCTIONS | 3 VERIFY FORMULAS

To Enter Formulas Using Point Mode

4 FORMAT WORKSHEET | 5 CHECK SPELLING [ 6 PRINT WORKSHEET

The sketch of the worksheet in Figure 2-3 on page EX 68 calls for the federal tax, state tax, tax percent, and net pay for each employee to appear in columns F, G, H, and I, respectively. All four of these values are calculated using formulas in row 4: Federal Tax (cell F4) = 0.22 x (Gross Pay - Dependents x 24.32) or -0.22 * (E4 - B4 * 24.32) State Tax (cell G4) = 0.04 x Gross Pay or -0.04 * E4 Tax % (cell H4) - (Federal Tax + State Tax) / Gross Pay or =(F4 + G4)/E4 Net Pay (eell 14) = Gross Pay - (Federal Tax + State Tax) or =E4 - (F4 + G4) An alternative to entering the formulas in cells F4, G4, H4, and 14 using the keyboard is to enter the fonnulas using the pointer and Point mode. Point mode allows you to select cells for use in a formula by using your finger or the pointer. The following steps enter formulas using Point mode. Why? Using Point mode 'makes it easier to create formulas without worrying about typographical errors when entering cell references. ETJ H *> - • -

With cell F4 selected, type = 0 . 2 2 * ( to begin the formula and then tap or click cell E4to add a cell reference in the formula (Figure 2-7).

|

HOME

i - NSERT

Hyper Mas: Online Slarjcjs Sjlaiy Report- Excel

PACE LAYOUT

FORMULAS

-&cPaste

^

PX

CliubosrU E4

"

A

Li J

H .

-S

/*

r; !

• - • " > - A -

REVIEW

DATA

r" '••• ~~

B

C

Rf'V.'rnp-k^

~-l^

Fj M.-Hjt ft Ccrstw -

Alignment

? - ?i

'.

'., '

^3 ?S

Wiii^tacr

CI

E

F

G

H

".

f

J

K

t

M

N

; Hours

Houily

Employee Depencer Worked

4 Carl, Mich'

3'

5 Green, Su

0

Pay R.ite

GO, 45 B1.5

Gross Pay Federal TiStste Tax Tax %

19. 5! 22.85

^*"

Net Pay

Hire Date 4/S/2010

Ii^V75!=0,22*(E i 1 i

7/ 15/2011' 1/13/2003

[TnT'jon'1

marqUee

3 Nichols, P

selected

Pearson,;

IO Kodnque;

J1 0

12 fau, Xin

4

^ 12. S

cell E4

11 Williams. H

Styles

1

2 Salary Report

9

j

CmHituri.il F-ormnU- C I oriTwttmi; - Tjbte • Sty

=D.2i*(E4

6

22.15

55.? SO

70

20.1S

^_^_L

.

3/1/2012

cell reference

I

4/15/2011

E4 appendoc to (unnulLi

ID/31/20081 7/15/20091

20. B5.

5/14/2009

\i

18.75

!

Tot ali

14 Highest 15 Lowest

Figure 2-7

Type * 2 4 . 3 2 ) to complete the formula (Figure 2-8).

(,-iif.sl

1 Hyperfctass Online Storage

3

Type (minus sign) and then tap or click cell B 4 t o a d d a subtraction operator and a reference to another cell to the formula.

/

-:?•-

1 onl

X

/

vi rv;

PAGE LAYOUT

FORMUIAS

cell reference B4 added to formula Hourly P j y R a t e ^Gros^Pay^FederalTt^ateTax Tax' ig.5[ 117S.775l=0.22"(b4-l- -*24.32). 22.35

'

!

17.30 IB.45

12.6 22,45

20.15 20.85. IS,75;

Figure 2-8

i

Formulas, Functions, and Formatting

Excel Chapter 2 EX 77

Tap or click the Enter box in the formula bar and then select cell G4 to prepare to enter the next formula. Type = 0 . 0 4 * and then tap or click cell E4toadd a cell reference to the formula (Figure 2-9).

I/11/2009 3/1/2012 4/15/2011 10/31/2008 7/15/2009 5/14/2003 d/17/2002

; Why should I use o3 O Point mode to enter formulas? Using Point mode to enter formulas

often is faster and more accurate than using the Figure 2-9 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 touch gestures or the pointer when entering a formula in a cell. You can use the keyboard to begin the formula, for example, and then use touch gestures or the pointer to select a range of cells.

Tap or click the Enter box in the formula bar to enter the formula in cell G4. Select cell H4. Type = ( (equal sign followed by an open parenthesis) and then tap or click cell F4to add a reference to the formula. Type + (plus sign) and then tap or click cell G 4 t o a d d a cell reference to the formula. Type ) / (close parenthesis followed by a forward slash), and then tap or click cell E4 to add a cell reference to the formula. • Tap or click the Enter box in the formula bar to enter the formula in cell H4 (Figure 2-10).

result of formula to calculate tax percentage appears in rcll HI

Figure 2-10

EX 78 Excel Chapter 2 Formulas, Functions, and Formatting

• Tap or click cell 14 to select cell 14 to prepare to enter the next formula. Type = (equal sign) and then tap or click cell E4 to begin the formula and add a cell reference to the formula.

formula assigned to cell 14 CFosrPa'y Fedei.il T t State T, 1173.775 243,27'J3

.17.151

Type - ( (minus sign followed by an open parenthesis) and then tap or click cell F4 to add a subtraction operator, open parenthesis, and cell reference to the formula. Type + (plus sign) and then tap or click cell G4 to add an addition operator and cell reference to the formula. Type )

Figure 2-11

(close parenthesis) to complete the formula (Figure 2-11).

Tap or click the Enter box in the formula bar to enter the formula.

To Copy Formulas Using the Fill Handle The five formulas for Michael Carl in cells E4, F4, G4, H4, -and 14 now arc complete. The next step is to copy them to the range E5:I12. When performing copying1 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, it sometimes 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, it sometimes is called the destination range. Recall from Chapter 1 that the hll handle is a small black square in die lower-right corner of the active cell or active range. The following steps copy the formulas using the fiJl handle. fj) Select the source range, E4:I4 in this case, activate the fill handle, drag the fill handle down through cell 112, and then continue to hold your finger or the mouse button to select the destination range. ^J Lift your finger or release the mouse button to copy the formulas to the destination range (Figure 2-12). How docs Excel adjust the cell references in the formulas in the destination area? Recall that when you copy a formula, Excel adjusts Ihe cell references so that 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 ceil references are called relative cell references.

Formulas, Functions, and Formatting

WE TiSt I*?03 FTP 2. Insert Delete Fornul

» l-orma Clipboard

r>

i"

Fonl

; A

i

Excel Chapter 2 EX 79

f,

t.

C

r.

Aliijtmcnt

r.

=OPD4 D

E

F

G

H

1

HyperM;iss Online Storage

2 Salary Repor Hours

Houily

3 Employee Depent et Worked Pay R,«0 Gross Pay Federal T=StJteT,iK TJX % NetP.iv Hire 4' Carl, Midi 3 00.4^ 19.5 117H.775 243.2793 47.151 0.2J6333 S33.3-I47 4 I Giccn, Su

0

31.5

t

Feklr, Slth

t

69.5

T LjllO, JOI! « Nichols, P 9 Pe.irson, t

2

65.25

3

71.25

3

'1-1.05

} 55 5 10 Rodrlque] 11 Williams, f orrnulas copied t2 Van, xin t o range E5:I12 H Tot tit

14 Highest

1K6Z.275 409.7005

1Z.S5

17.3fi

1206.52

74 4'JJ

0 Hi

137 SAW

7/1

2GO.OS-1

43.2608 IU55Sli5 898.1752

19 '15 Ufit 1125

2"3501

50.7045 0.2515GS 949.3441

3

S57.75

.13J.-153G

35.1)! 0, 2-12121 6S0.3362

Ml

22. -15 1002.3'J25

J04.4752

12.6

)(1 IS

.10.09S? 0.243187 757.S217 IO/_

1118.325 235.3B07 luOE

Ju.ss in 'h

I/

-14.733 0.250431 838.2013

366.90

66.72

1312.5 2ti/.34S4

0.20 J.2=M. H

52.5 0.243054 992.u51C

7/1 !>y fl' W*l-*

'

'Auto Fill Options' button appears

15 Lowest

10 Averjjje 17

' i

i

[8 19

I

20

i

i

Figure 2-12

Option Buttons 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, die 'Auto Fill Options' button shown in Figure 2- 12 appears after a fill operation, such as dragging the rill 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, tap or click its arrow to produce the list of options for modifying the operation or to obtain additional information. Table 2-4 Option Buttons in Excel Menu Function 'Auto Fill Options'

operation, such as dragging the

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

5>uloSum - A |j,F|B. Z _ Sc .* C!PJ.-h;.

EX 80 Excel Chapter 2 Formulas, Functions, and Formatting

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. You can choose from the following options:

oc

• Paste an exact copy of what you copied, including the cell contents and formatting.

!'!

• Copy only formulas.

o u

• Copy only formatting. • Copy only values. • Copy a combination of these options. • Copy a picture of what you copied.

To Determine Totals Using the Sum Button The next step is to determine the totals in row 13 for the hours worked in column C, gross pay in column F,, federal tax in column. F, state tax in column G, and net pay in column T. To determine die total hours worked in column C, the values in the range C4 through CI2 must be summed. To do so, enter the function =sum(c4:cl2) in cell C13 or select cell C13, tap or click the Sum button (HOME tab I 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 E13, F13, Gl 3, and II 3 to total gross pay, federal tax, state tax, and net pay, respectively. The following steps determine totals in cell C13, the range E13:G13, and cell 113. Select the cell to contain the sum, cell C13 in this case. Tap or click the Sum button (HOME tab | Editing group) to sum the contents of the range C4:C12 in cell C13 and then tap or click the Enter box to display a total in the selected cell. Select the range to contain the sums, range E13:G13 in this case. Tap or click the Sum button (HOME tab | Editing group) to display totals in the selected range. Select the cell to contain the sum, cell 113 in this case. Tap or click the Sum button (HOME tab | Editing group) to sum the contents of the range 14:112 in cell 113 and then tap or click the Enter box to display a total in the selected cell (Figure 2-13). < Why did I have to tap or click the Enter box? When creating a sum for a single column, you tap or click the Enter box. If you are calculating the sum for multiple ranges, you tap or click the Sum button.

i tmpl 1 Carl, M i l l

CiO 45 fil.5 63.5 6S.25

19.5

1173.775 2-152793

-17.1S1 O.M&Ji!! 833.3-M7

4/5/2010

22. as 1 B G 2 J 7 5 J09.7005 I7.3G 1206.52 260 OS4 13.45 1269 1125 203.304

/J 431 0.2U 1373.031: 7/15/2011 -13 7GD3 0.255565 EM.1/S2 1/13/2003 50.7645 0.251563 313.JI HI 3/1/2012

22.45 1002.3925 20-1.4752

-10.0957 0.243937 757.S217 JO/31/200B

Figure 2-13

Formulas, Functions, and Formatting

Excel Chapter 2 EX 81

To Determine the Total Tax Percentage With the totals in row 13 determined, the next step is to copy the tax percentage formula in cell HI2 to cell HI3. The following step copies the tux percentage formula. Select the cell to be copied, H12 in this case, and then drag the fill handle down through cell H13 to copy the formula (Figure 2-14).

< Why was the SUM function not used for tax percentage in H13? & The tax percentage is based off of the totals, not the sum, of the tax percentage column; I therefore, there was no need to use the SUM function.

51 H «3^Jljjl

:-

HOME

4'

7'iif^r . fi''tf

Cliplionril

u

f

' '!l'

""''A-

Ton\,

;

A

B

FORMULAS

DATA

REVIEW

MEW

''" ~' A ' A' ~~ ==;s '' ?x ' B-w»pT«t u

r,

-; • A

PAGE LAYOUT

c''""'

*r ' , , *FormrtP.intn

H12

HyprrMa^ Online Slor.ipe Salary Report- Excel

-

INSERT

C

,G-MI

rf ^ S if:*- tSI Merge & Ccntrr Aliunmcnt

;;-i [JH

$ • % ' 1

%S -i°3

flumlm

ig>

;j

Cond ITonal Fcmwl ,!•, C Foim.lting- Tsbk- % r,

st-yFcs

, =(F12+1-1,733 o «o-m B3B.2613 7/15/2009 66.72

o.je

1234.32

5/11/2009

52.5 0.2.43094 992.05KJ _LliJ5-&5_ -ZiTO., U&_ jlliO G2G 0.251037 3617.333

fi/i.7/2002

highest vdlue in range B4:B12 1

IS Lowe rt 16 Average 17

visually could scan . the range B4:B12, determine that the Figure 2-17 highest number of dependents is 4, and manually enter the number 4 as a constant in cell B14. Excel would display the number the same as in Figure 2-17. Because it contains a constant, however, Excel will continue to display 4 in cell B14, even if the values in the range change. If you use the MAX function. Excel will recalculate the highest value in the range each time a new value is entered into the worksheet. Other Ways 1. Tap or click Sum arrow (HOME tab | Editing group), tap or click Max

2. Tap or click Sum arrow (FORMULAS tab | Function Library group), tap or click Max

3. Type =MAX in cell, fill in arguments

EX 84 Excel Chapter 2 Formulas, Functions, and Formatting

To Determine the Lowest Number in a Range of Numbers Using the Sum Menu

| 2 ENTRF1 FUNCTIONS | 3 VERIF* FORMULAS
The next step is to enter the MIN function in cell B15 to determine the lowest (minimum) number in the range B4:B 12. Although you can enter the MIN function using the method used to enter the MAX function, die following- steps illustrate an alternative method using the Sum button (HOME tab I Editing group). Why? The Sum menu provides another way that you can insert functions, providing n mcmi from which you am choose.

O • Select cell B15 and then tap or click the Sum arrow (HOME tab | Editing group) to display the Sum menu (Figure 2-18). < Why should I use the Sum menu? Using the Sum 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.

ZAutaSum ^A-

Min function

19.5

1173.775 2-1).2/13

2Z.85 lfl(i2-275 17.36 120652 13.45 1209 1125 12. (>

397.75

-17 15i

1(19,7005 21,0.034 263.504

.1/5/JDin 7/15/20 LI 1/13/200'J 3/1/2012

35.91 0.2J2121 030.3362

4/15/2011

151.4538

22AS 1002 3925 20-1.1752 20.15 111S.J25 IK.'iiat 20 Z-, 13 75

0.2.10335 3S3 1J47

7*1.'191 0.2G I"l7fl.03-1 43.260,1 0.255505 39S.1752. 50.76-15 0.2515Qj IM'JSMl

-10 01 =,7 0.243937 757 S217 10/51/20CIS JA.'/il 0.250431 33iUG13 7/15/2009

J6G3 3lid 95 1312.5 207.3434

66 72 0.26 12M.M 5/1J/2009 S2.5 0 243GEI4 ^92.0515 G/17/J002

11515.65 2437.136

460.r>2fi' 0.251637 ,1(il7.333

Figure 2-18

Tap or click Min to display the MIN function in the formula bar and in the active cell (Figure 2-19). Why does Excel select the incorrect range? The range automatically selected by Excel is not always 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.

| gj a

*} -

H^B

'.

-k *

HOME

~y*'Cul pf=

'

H perMmOnlrneSton.geS^ Report LXK!

PACE LAYOUT

..,1,1,1, • ••

-

D

• '

c plio.nci MAX

;

INSFIt'r

\A

H

A- .-:

c-w.pi,,.

.,,,,1

|r,i

[""> j'

r it F:' n, Hiriq-

n i

*l

pant

X

RCVIEW

roRMULAS

i/

.ft

B

:!

T.;!.'.--

?!.::

n;

__J MIN function displays in formula bar

=MIN(B14)-*

n

"in

E

(j

F

H

S

1

L

M

II

/

1 ,Hy|> iM.iss Online Sloiage 2 |Siilj y Report

' Hours

Hourly

J

Employee Dependei Worked

Pay Rale

4

Call, Midi

3

5

6roeri,Sn

0

S.E 5

6

Fakir, Sith

1

39.5

7 'Lane Jon

2

05.25

n

Nithols.P

3

71.21

0

44.1)5

00. -15

Pear on, i1

3

IU Rodi 'luu;

2

55.5

II 'VVilllams.

0

30

4

70 593 I

12 Yau, Xin 13 Tola 5 14 Htgh ist 15 Lo'.ve it 16 Aver 1SC

|

.i
0.03J

43,2003 0.255565

353.1752

1/13/2009

19.45

2GS 504

50.7645

949. 3-141

3/1/2012

397.75

131.-15J3

35.91

22.45 1002,3925

204. .1752

40.0957

0.243937

757,8217 .[0/31/2003

20.15 111,1325

235 3307

44.733

0.25043i

333,2513

7/15/2009

3C .9ii

00.72

0.2C

1234 32

5/L1/2009

13125 2673.134 i-m uiji^^™ 156

52.5

0.243694

992.6516

6/17/2002

J60.r,2(

D.251637

;iuJ7.38i3

1269.1125

12.6

20.35 13.75 |

IOCS

0.2515CS

Excel selects cell B14

f = n t p j ( r : ' !>-* because it includes '—TTT-v ~1 numbers and is mM.bnlfn.mh. a d J B c e n t t o B 1 5

^—

HheDate

17.30 1201.. 52

17

ia

(Jet Pay

—-^_

~~-

~~—

Figure 2-19

I \1 680.3362

4/1S

Formulas, Functions, and Formatting

Tap or click eel! B4 and then drag through cell B12 to display the function with the new range in the formula bar and in the selected cell (Figure 2-20).

OJ

W

*5 '

.'

HOME

Excel Chapter 2 EX 85

-I,"

INiERT

PAGE LAYOUT

FORMULAS

DATA

HWIEW

VIEW

Ijnl'l•" ,•-••' [>'.'|."-iij-ji .'1.-,rl.. il

1312,5 21.7.34BJ 11515.65 2-1J7.13C

•100 020

0 251037 a&17.S33

Figure 2-20 Tap or click the Enter box to determine the lowest value in the range B4:B12 and display the result in the selected cell (Figure 2-21).

Qi H *^-

-k ' =

HOMF

IMSFRT

Enter bow

1

M

Hours •lontler Wort;o

Contl tional hotm.it as C

r;

K

1269.1125

20,15

'iS ,"

"i

1 GiossPay

0

6 ;Fekir, Sitli

rxn [.|M

-j '

Numiicr

Hourly

5

IJ Tota s

5> - %

whan Enter box is tapped or clicked or ENTER key G is pressed L~~ — '

.1 tail, Midi Gree i. Su

" """ iGtnerih

right parenthesis automatically appended

' .

Bib

1

~

S-WmpTral

i^:

|

VIEW

to Name bo*

1

Excel Chapter 2 EX 87

S5.91

0.242121

0,243937

D. 250431 838,2013' 0 2G

0,251637

in cell GIG

-1/15/2011

757,821710/31/2008-

52.5 0.245C94 .160,026

6B0.38S2

7/15/2009

1234.32

5/14/2009

3D2.651G

6/17/2002

8617,833

i

i

""" "."i: :

. i_:_

19 20 ' 21

/

r

^—~-^^---—~~~~

~~——_

^—^~~

What is the purpose Figure 2-24 of the parentheses in the function? The AVERAGE function requires that the argument (in this case, the range B4:B12) be included within parentheses following the function name. Excel automatically appends the right parenthesis to complete the AVERAGE function when you tap or click the Enter box or press the ENTER key.

Other Ul'ays 1. Tap or click Insert Function box in formula bar, select Statistical category, tap or click AVERAGE

Tap or click Sum arrow (HOME tab | Editing group), tap or click Average

Tap or click Sum arrow (FORMULAS tab ] Function Library group), tap or click Average

To Copy a Range of Cells Across Columns to an Adjacent Range Using the Fill Handle The next step is to copy the AVERAGE, ;M AX, und MJN functions in the range B14:B16 to the adjacent range C14:116. The following steps use the fill handle to copy the functions, C)

Select the source range from which to copy the functions, in this case B14:B16.

^J Drag the fill handle in the lower-right corner of the selected range through cell 116 to copy the three functions to the selected range.

EX 88 Excel Chapter 2 Formulas, Functions, and Formatting

I

Select cell H16 and then press the DELETE key to delete the average of the tax % (Figure 2-25).

Why delete the formula in cell H16? The average of the tax percentage in coll H16 is deleted because an average of percentages of this type is mathematically invalid. How can I be sure that the function arguments are proper for the cells in range C14ill6? 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-25. You should see that the functions in each column return the appropriate values, based on the numbers in rows 4 through 12 of that coiumn.

a

, |

^

^^^H

;

HOI.1F

„

INSERT

,

lion

PAGE LAYOUT

*™

-jll

F>CCF/ • J

v Format ,.,„„., tlipbtnrri

H10

,

B / y -

- A" A*

DATA " '----

-"» - ^ -

Fo nt

r.

A

-

HyperMaii Online Storage- ialary Hcport- ESCL-! FORMULAS

2T = ^

REVIbW

VlbW

'?/'-

1 r'Wrap'Inrt

*^ •*=

@ r.Icrr;e ft Oniei -

r,

GsnErjl

Aligniritnl

5 - ','u '

ii

*:•''' *"»

[l.iniljcr

C"?LIL> \' T Styles

ii

/• ti

E

0

F

r,

H

J

L

K

M

N

HyperMass Online Storage

z Sjkiiy Repot

1

Hours

Houily

3

Employee Uepemlei Woil.td

4

Carl, Mich

1

60.45

Pay RMe GroisPay Federal T;SUtc Ta\* "= Net Pay Hire Hate 15.5 1173.775 243.J793 -17151 0,246383 838.3447 4/5/2010

5 Green, Su

Q

SI. 5

22.85

1362,275

409.7005

6

Fakir, Sith

1

695

l/.JG

U06.52

260.03-1

43.2603 0.255565

-

Lane, ioii

1

65.25

19.45 1269.1125

263.504

50.7645 0.25156S 9-19.3441

3/1/2012

1!

35,91. 0.242121 6B0.3S02

4/15/201L

Nichols, P

3

71.25

9 Pearson,;

3

1-l.uS

22.45 1002. 3925 20.1.4752

in Rodriquc; M Williams,

2

5S.5

20.15 1118.325 235.3307

0

KB

l2jYau,Xin

4

11 Totals

70

12. v

20 35 1375

S'JS.l

317.75

166S

1B1.453S

366.36

74.491

44.733 0.250431 333.2613 66.72

52.5 0.213694 992.6516 -160020 0.251037 8017.338

15 Lowest

0

2 6G.-15556 19.32339 1279.5107 270.7929 51.1306?|

12.6

397.75 131.4533

74,491

7/15/200'J (i/17/2002

207.3434 2437.136

IBJAveraic

44 (is

1/13/200'J

5/14/2009.

1312.5

l.lf.2.275 409.7005

7/15/2011

0.26 1234.32

11515.65

4

22.35

393.1752

.10.0957 0.243937 757.3217 10/31/2003

1-1 Highest

11.5

0,20 1373.0B4

0.20 1376. OB-1

35.91 0.2-12121 6,10. 3SG2

r

Q

\l

N

18

—-—

^—-^^

__^__——-^

~~^- mainematieauy invalid average of percentages in range H4:H12 deleted

FlCIUrt* 2 — 25

^

Other Ways Select source area, tap or click Copy button (HOME tab [Clipboard group), select destination area, tap or click Paste button (HOME tab | Clipboard group)

Press and hold or right-click source area, tap or click Copy on shortcut menu; press and hold or right-click destination area, tap or 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, HyperMiiss Online Storage Salary Report. The following step saves the workbook a second time, using the same file name. Tap or click the Save button on the Quick Access Toolbar to overwrite the previously saved file.

Formulas, Functions, and Formatting

Excel Chapter 2 EX 89

Break Point: If you wish to take a break, this is a good place to do so. You can exit Excel now. To resume at a later time, run Excel, open the file called HyperMass Online Storage Salary Report, and continue following the steps from this location forward.

Verifying Formulas Using Range Finder One of the more common mistakes made with Excel is to include an incorrect cell reference in a formula. An easy way to veri Py that a formula references the cells you want it to reference is to use Range Finder. Range Finder checks which cells are referenced in the formula assigned to the active cell. To use Range Finder to verify that n formula contains the intended cell references, double-tap or 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 verify that the cell references are correct. .. i | ' I

To Verify a Formula Using Range Finder

| 3 VEBIFV FORMULAS

4 FOHMAT WORKSHEET | 5 CHECK SPELLING ' 6 PRINT WOHKSHEET

Wljy? Range Finder allows you to uifikc immediate changes to the cells referenced hi a formula. The following steps use Range Finder to check the formula in cell 1-14. Double-tap or double-click cell H4 to activate Range Finder (Figure 2-26).

HOME

INSiFil

PAGELAVOU1

Press the ESC key to quit Range Finder and then tap or click anywhere in the worksheet, such as cell A18, to deselect the current cell.

KMivUll

'>

rout

- A -

EE: == :=

f=: ••-

i l Merge & Cooler "

Alignment

15

S, . o'

r.

,

i^'t

Number

on

j^rf &

\T~ty I-LJ

Conditional Format as Cell Formattmtj • Table- Styles -

Q

tff^

f.

Insert

D
5-aS

Nidwii, Pf l'^rS(.n,A. Rodriquez, Williams, S' Yju,Xin 13 Total? u, Hlijhcst 15

L0w,St

iG Avotacjc

3 71 =5 3 v.

"fofB

B

-ram

1

?

jFrnniiHKjRulc

B" 125 ™

F°'™1*

!!)•

T-lljIC

c S! V I

Uyltl

5e,e«7I^Ti lie

ttfflj y (tils that cnntni

C

?

K

1

1

fo It

MuitllJW

Hours

Cjrl, Mich;

S Gican, Sue 6 Fekir, Situ

e

F

i i

Nichols, Pe

F.int il>ls.

•Jt C...NI liHe riuigii

Regular

60. (,5 :

o

81.50

rr •» MM in

i

r,;i so

•^AM,,

3

T-1-.-^

:

iTijci,

Bold 6oki Italic

3

55 50

ILI II

u

f^l 5tilLctl,ru

A LI to in j tie

[wfL;•

1 Color arrow 1

A,rtom.ht

Kill

• i ,-' '

^

« 11 Totals

.

1 it... ,l

3

• ID f;odnquei,

J

-,;„

Hi

1 Employee Dependent! Worked P.ii >*

• Bonier '

Font

•

_

• «•

• ••

598.10

Li, 1 Highest

^

81.50

15

o

4465

^

66 /,6

Lowest

K) Average

. I l l l l l l l l

For Conil liora Forraattina you inil stt Font!

Sundwd Colon

17 18

'•• tf

*9 jo

••• • ••

Mine Colon

"M H

1

21

—^ o.; Caned —_^___ _ _^— _ _"^"^

OK button

•••.— -—""^

•M

Figure 2-46

O

Tap or click the Fill tab (Format Cells dialog box) to display the Fill sheet and then tap or click the orange color in column 6, row 5 to select the background color (Figure 2-47).

s

VIEW

t Cells dialog box 1

' ^ Copy -

A

M,»n,-.,

Excel Chapter 2 EX 101

HOME

INM-KT

PAGE LAVOUT

FORMULAS

DATA

RFVItW

VlfW

Figure 2-47

*i

~~~—-

__/

f

EX 102 Excel Chapter 2 Formulas, Functions, and Formatting

Tap or click the OK button (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-48).

ff u «>- -. HUME

i- = INSERT

PAGf LAYOUT

FORMULAS

DAIA

KFVIEV.1

VIEV.1

- Sly

It mil, fell; that (..jntiin

at onl/ Ullfttire =r cliiplrcatr ™rn« Uic-.ifoimulal.j Jctrtiiilneivhidi cell; tf, format Employee De|)i.

£dtttti(Rul*Dt)

f • -I. '

QH HC','£ 'i!*' 0 " '•'•'

-

" '

I

FORMULAS

- | l l -"! A' /T

" ' - • ""A'

f.

A

B

IV,TA •

\ytKI

-„".*>/-

"" " ^

L>W,upl«l

|At«™l,.i;i

t-»I \:]>.-f,vt-,t,r.,c, -

_

$- % •

-|

YS j;

|J^

[;_>

\':J*

C, -,.,-,

UI4

=

INSECT

August

Expenses

209081

I09.G31.00 1 63,811.00 300.WO.OO 22J.207.00 2JS.349.00 J4.11S.OO £2.?ro.OO 1J5.32-1.00 305.44J.OO 224741.00 15S.4.I4.00 . i f " '." .[t92.17-l.EO J305.(4y.OO

Expenses

I

M

N