EXCEL tutorial advanced Functions, Formulas, and Formatting


Télécharger EXCEL tutorial advanced Functions, Formulas, and Formatting

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★1 étoiles sur 5 basé sur 2 votes.
Votez ce document:

Télécharger aussi :


 

You will have mastered the material in this chapter when you can:

•   Enter formulas using the keyboard

•   Enter formulas using Point mode

•   Apply the AVERAGE, MAX, and MIN functions

•   Verify a formula using Range Finder

•   Apply a theme to a workbook

•   Add conditional formatting to cells

•   Change column width and row height

•   Check the spelling in a worksheet

•   Set margins, headers, and footers in Page Layout view

•   Preview and print versions of

a worksheet

• Apply a date format to a cell or range

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

Recall that the fi rst 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


 

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

General Project Decisions

While creating an Excel worksheet, you need to make several decisions that will determine the appearance and characteristics of the fi nished 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 fi t more or less data on each page. Headers and footers add an additional level of customization to the printed page.

When necessary, more specifi c 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.

In addition, using a sketch of the worksheet can help you visualize its design. The


Plan

Ahead


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 specifi c characters to defi ne the desired formatting for the worksheet, as follows:

1.    The row of Xs below the leftmost column defi nes 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 defi ne 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 fi rst row below the column headings and in the total row signify a fi xed dollar sign. The dollar signs that are adjacent to the Zs below the total row signify a fl oating dollar sign, or one that appears next to the fi rst signifi cant digit.

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 fl ashy 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.

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 Offi ce 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 fi les, 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 fi gures 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 Offi ce 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 Offi ce 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 fi les’ 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

The fi rst 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).

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.

To Enter the Column Titles

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 fi rst 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.

For an introduction to Offi ce 2010 and instruction about how to perform

basic tasks in Offi ce 2010 programs, read the Offi ce 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 fi le, open a fi le, quit a program, use Help, and much more.

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.

BTWs

For a complete list of the BTWs found in the margins of this book, visit the Excel 2010 BTW Web page ().

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 ().

Two-Digit Years When you enter a two-

digit 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.

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 fi t. 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

To Enter the Biweekly Payroll Data

 

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.

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).

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

Hire Date

Dependents

Hours Worked

Hourly Pay Rate

Charvat, Emily

3/3/09

1

65.25

 20.50

Chen, Bin

6/14/10

2

80.00

 25.85

Felski, Noah

10/11/08

0

64.50

 12.60

Kersey, Jane

3/4/11

1

68.50

 21.45

Merna, Thomas

1/15/10

3

78.25

 22.60

Pollitt, Sherry

11/15/08

2

49.25

 18.25

Prasad, Rao

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).


with ALT+ENTER.

To Change Document Properties

 

As discussed in Chapter 1, the fi rst 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.


Entering Numbers in a Range

An effi cient way to enter data into a range of cells is to select a range and then enter the fi rst 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 fi rst column, Excel moves the active cell selection to the top of the next column.

Plan

Ahead

To Change the Sheet Name and Save the Workbook

 

The following steps change the sheet name to Biweekly Payroll Report, change the sheet tab color, and save the workbook on a USB fl ash drive in the Excel folder (for your assignments) using the fi le name, The Mobile Masses Biweekly Payroll Report.

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 fl ash 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 fi le name. Do not press the ENTER key after typing the fi le 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 fl ash 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 fi le 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 fi ll handle and copy and paste operations to copy formulas. The fi ll 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 fi ll 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)


(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 simplifi es the formula used in a cell, resulting in improved readability.

Plan

Ahead

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

Figure 2–5

The asterisk (*) following d4 is the arithmetic operator that directs Excel to perform the multiplication operation.


2

 

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


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: fi rst negation (–), then all percentages (%), then all exponentiations (^), then all multiplications (*) and divisions (/), and fi nally, 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

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 fi nally 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

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.


 

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.

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).

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.

•  Type *24.32) to complete the formula (Figure 2–8).

Figure 2–8


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

 

cell reference to the           Figure 2–10 formula.

•  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).

5

 

Figure 2–11 cell J4 (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 fi ve formulas for Emily Charvat in cells F4, G4, H4, I4, and J4 now are complete. You could enter the same fi ve 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 fi ll 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 fi ll 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 fi ll handle.



1

•  Select the source range, F4:J4 in this case, and then point to the fi ll handle. Drag the fi ll handle down through cell J12 and continue to hold the mouse button to select the destination range (Figure 2–12).

2

•  Release the mouse 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?

Recall that when you copy a formula, Excel adjusts the 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 cell references are called relative cell references.

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


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 fi nished, make sure to press the F8 key to turn off the selection process or you will continue to select ranges.

1

2

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, the Auto Fill Options button shown in Figure 2 – 13 appears after a fi ll operation, such as dragging the fi ll handle. When an error occurs in a formula in a cell, Excel displays the Trace Error button next to the cell and identifi es 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.

Table 2–4 Options Buttons in Excel

Button

Name

Menu Function

 

Auto Fill Options

Gives options for how to fi ll cells following a fi ll operation, such as dragging the fi ll 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

Specifi es 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.

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.

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).


 

Figure 2–14

To Determine the Total Tax %

 

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.

1   Select the cell to be copied, J12 in this case, and then point to the fi ll handle.

2   Drag the fi ll handle down through cell J13 to copy the formula (Figure 2–15).

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.

 

Figure 2–15


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].

Using the AVERAGE, MAX, and MIN Functions

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 fi ve 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 fi rst 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 specifi ed 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

typed =av, Excel Figure 2–16 displays all the functions that begin with the letters av.

2

•  Double-click AVERAGE in the Formula AutoComplete list to select the function.

•  Select the range to be averaged, 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.

3

•  Click the Enter box to compute the average of the numbers in the selected range and display the result in the selected cell (Figure 2–18).

Can I use the arrow keys to complete the entry instead?

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.

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     Figure 2–18 the Enter box or press the ENTER key.

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

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

       C15 in the Function                                                                                             Figure 2–20

Arguments dialog box. It also lists the fi rst few numbers in the selected range, next to the Number1 box.

 

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).

Figure 2–22


2

 

Figure 2–23

3

 

Figure 2–24

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 fi ll handle to copy the functions.

1

 

      (Figure 2–26).                                                                                                      Figure 2–26

 

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.

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 fi le name, The Mobile Masses Biweekly Payroll Report. The following step saves the workbook a second time, using the same fi le name.

 

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 fi le called Mobile Masses Biweekly Payroll 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 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

 

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.


 

Figure 2–29

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.

 

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 fi ll 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 fi ll 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 predefi ned set of colors, fonts, chart styles, cell styles, and fi ll effects that can be applied to an entire workbook. Every new workbook that you create is assigned a default theme named Offi ce. 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.

Plan

Ahead

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

6.    Average, highest, and lowest rows

a.   Font style of row titles in range A14:A16 — bold

b.   Numbers — Currency style with fl oating 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 fi t

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.

standardize on a Figure 2–31 specifi c 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 Offi ce programs, such as Word and PowerPoint, include the same themes included with Excel, meaning that all of your Microsoft Offi ce documents can share a common theme.


Why did the cells in the worksheet change?

The cells in the worksheet originally were formatted with the default font for the default Offi ce theme. The default font for the Trek theme is different from that of the default font for the Offi ce theme and, therefore, changed on the worksheet when you changed the theme. If you had modifi ed 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.

Display the Home tab.

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.

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.

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.

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).

 

background is ideal.                                                                                                       Figure 2–33

To Change the Background Color and Apply a Box Border to the Worksheet Title and Subtitle

 

The fi nal 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.

3

•  Click the Borders button arrow (Home tab | Font group) to display the Borders list (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).

 

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


Background Colors The most popular background color is blue.

Research shows that the color blue is used most often because this color connotes serenity, refl ection, and profi ciency.

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.


 

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.

   

range (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


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 fi rst 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.

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 fi xed dollar sign to the far left in the cell, often with spaces between it and the fi rst digit. To assign a fl oating dollar sign that appears immediately to the left of the fi rst 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.

Plan

Ahead

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 dollar sign to the far left with spaces                Figure 2–41 between it and the fi rst digit in the cell.

     

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 fl oating dollar sign to the numbers in the range E14:I16.

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.

digit, and the fi xed dollar sign always appears on the left side of the cell. Cell E4, for example, has a fi xed dollar sign, while cell E14 has a fl oating dollar sign. The Currency style was assigned to cell E14 using the Format Cells dialog box and the result is a fl oating 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

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

2. Press CTRL+1, click Number tab (Format Cells dialog box), click Percentage

in Category list, select format, click OK button

3. Press CTRL+SHIFT+ percent sign (%)

Figure 2–47 times to display the numbers in the selected range with two decimal places (Figure 2–47).

 

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).

                                      Plan                      Establish rules for conditional formatting.

Ahead                    • 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.

Conditional Formatting

•     A condition, which is made up of two values and a relational operator, is true or false for each You can assign any

format to a cell, a range cell in the range. If the condition is true, then Excel applies the formatting. If the condition is of cells, a worksheet, false, then Excel suppresses the formatting. What makes conditional formatting so powerful or an entire workbook is that the cell’s appearance can change as you enter new values in the worksheet. conditionally. If the value

•     As with worksheet formatting, follow the less-is-more rule when considering conditional

of the cell changes and formatting. Use conditional formatting to make cells and ranges stand out and raise

no longer meets the attention. Too much conditional formatting can result in confusion for the reader of the

specifi ed condition, Excel suppresses the conditional                 worksheet. formatting.

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.

Figure 2–48

and then select   Figure 2–49 greater than to select the desired operator.

•  Select the rightmost box, and then type 70 in the box in the ‘Edit the Rule Description’ 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.

click the orange color in     Figure 2–50 column 5, row 5 to select the background color (Figure 2–50).


4

•  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–51).



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).

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.

Table 2–5 Summary of Conditional Formatting Relational Operators

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 defi ned 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 fi t 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.

To Change the Widths of Columns

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.


 

When changing the column width, you can set the width manually or you can instruct Excel to size the column to best fi t. Best fi t means that the width of the column will be increased or decreased so that the widest entry will fi t in the column. Sometimes, you may prefer more or less white space in a column than best fi t 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 fi t. 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 fi t, 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 fi t; column H to

10.22 characters; and columns D, E, and J to 7.56 characters.

1

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

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

column width. 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).

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. Drag until the

ScreenTip indicates Width: 7.56 (75 pixels). Do not release the mouse button (Figure 2–56).


Figure 2–56

5

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 fi t 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

 

Figure 2–58

row height.

 

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

button to change the row height.

Figure 2–60


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 fi le again and then you can quit Excel. To resume at a later time, start Excel, open the fi le called The Mobile Masses Biweekly Payroll Report and continue following the steps from this location forward.

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 fi nds 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.

Figure 2–61

What happens when the spell checker fi nds a misspelled word?

When the spell checker identifi es 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).

 

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


Additional Spell Checker Considerations

Consider these additional guidelines when using the spell checker:

•  To check the spelling of the text in a single cell, double-click the cell to make the formula bar active and then click the Spelling button (Review tab | Proofi ng group).

•  If you select a single cell so that the formula bar is not active and then start the spell checker, Excel checks the remainder of the worksheet, including notes and embedded charts.

•  If you select a cell other than cell A1 before you start the spell checker, Excel will display a dialog box when the spell checker reaches the end of the worksheet, asking if you want to continue checking at the beginning.

•  If you select a range of cells before starting the spell checker, Excel checks the spelling of the words only in the selected range.

•  To check the spelling of all the sheets in a workbook, right-click any sheet tab, click Select All Sheets on the sheet tab shortcut menu, and then start the spell checker.

•  To add words to the dictionary such as your last name, click the Add to D ictionary  button in the Spelling dialog box (Figure 2–61) when Excel i dentifi es the word as not in the dictionary.

•  Click the AutoCorrect button (Spelling dialog box) to add the misspelled word and the correct version of the word to the AutoCorrect list. For example, suppose that you misspell the word, do, as the word, dox. When the spell checker displays the Spelling dialog box with the correct word, do, in the Change to box, click the AutoCorrect button. Then, anytime in the future that you type the word dox, Excel automatically will change it to the word, do.

Error Checking Always take the time to check the formulas of a worksheet before submitting it to your supervisor. You can check formulas by clicking the Error Checking button

(Formulas tab | Formula Auditing group). You also should test the formulas by employing data that tests the limits of formulas. Experienced spreadsheet specialists spend as much time testing a workbook as they do creating it, and they do so before placing the workbook into production.


Preparing to Print the Worksheet

Excel allows for a great deal of customization in how a worksheet appears when printed. For example, the margins on the page can be adjusted. A header or footer can be added to each printed page as well. Excel also has the capability to work on the worksheet in Page Layout view. Page Layout view allows you to create or modify a worksheet while viewing how it will look in printed format. The default view that you have worked in up until this point in the book is called Normal view.

Specify how the printed worksheet should appear.

Before printing a worksheet, you should consider how the worksheet will appear when printed. In order to fi t as much information on the printed page as possible, the margins of the worksheet should be set to a reasonably small width and height. While the current version of a worksheet may print on one page, you may add more data in the future that causes the worksheet to extend to multiple pages. It is, therefore, a good idea to add a page header to the worksheet that prints in the top margin of each page. A header is common content that prints on every page of a worksheet. Landscape orientation is a good choice for large worksheets because the printed worksheet’s width is greater than its length.

Plan

Ahead

To Change the Worksheet’s Margins, Header, and Orientation in Page Layout View

 

The following steps change to Page Layout view, narrow the margins of the worksheet, change the header of the worksheet, and set the orientation of the worksheet to landscape. Often, you may want to reduce margins so that the printed worksheet better fi ts the page. Margins are those portions of a printed page outside the main body of the printed document and always are blank when printed. Recall that in Chapter 1, the worksheet was printed in landscape orientation. The current worksheet also is too wide for a single page and requires landscape orientation to fi t on one page in a readable manner.

1

of each page includes         Figure 2–63 a Header area, and the bottom of each page includes a Footer area. Page Layout view also includes a ruler at the top of the page that assists you in placing objects on the page, such as charts and pictures.

3

 

      key. Type Chief                                   Figure 2–65

Financial Officer to complete the worksheet header (Figure 2–65).

What else can I place in a header?

You can add text, page number information, date and time information, the fi le path of the workbook, the fi le name of the workbook, the sheet name of the workbook, and pictures to a header.

Figure 2–66

available on the Home tab on the Ribbon. To continue working in Excel, therefore, you should select a cell in the worksheet so that all of the commands on the Ribbon are available for your use.

 

Figure 2–67

 

Other Ways

 

1. Click Page Setup Dialog

Box Launcher (Page Layout tab | Page Setup group), click Page tab

(Page Setup dialog box), click Portrait or Landscape, click OK button

 

   

To Print a Section of the Worksheet

 

You might not always want to print the entire worksheet. You can print portions of the worksheet by selecting the range of cells to print and then clicking the Selection option button in the Print what area in the Print dialog box. The following steps print the range A3:F16.

      (Figure 2–69).                                                                                                      Figure 2–69

Other Ways

     

1. Select range, click Print

Area button (Page Layout tab | Page Setup group), click Set Print

Area, click Quick Print button on Quick Access Toolbar, click Print Area, click Clear Print Area

2. Select range, click Print Area button (Page Layout tab | Page Setup group), click Set Print

Area, click File tab to open Backstage view, click Print tab, click

Print button

2

 

Click cell A18 to   Figure 2–70 deselect the range A3:F16.

What are my options for telling Excel what to print?

Excel includes three options to allow you to determine what should be printed (Figure 2–69). As shown in the previous steps, the Print Selection button instructs Excel to print the selected range. The Print Active Sheets button instructs Excel to print the active worksheet (the worksheet currently on the screen) or the selected worksheets. Finally, the Print Entire Workbook button instructs Excel to print all of the worksheets in the workbook.


Displaying and Printing the Formulas Version of the Worksheet

Thus far, you have been working with the values version of the worksheet, which shows the results of the formulas you have entered, rather than the actual formulas. Excel also can display and print the formulas version of the worksheet, which shows the actual formulas you have entered, rather than the resulting values.

The formulas version is useful for debugging a worksheet. Debugging is the process of fi nding and correcting errors in the worksheet. Viewing and printing the formulas version instead of the values version makes it easier to see any mistakes in the formulas.

When you change from the values version to the formulas version, Excel increases the width of the columns so that the formulas and text do not overfl ow into adjacent cells on the right. The formulas version of the worksheet, thus, usually is signifi cantly wider than the values version. To fi t the wide printout on one page, you can use landscape orientation, which has already been selected for the workbook, and the Fit to option in the Page sheet in the Page Setup dialog box.

Values versus Formulas When completing class assignments, do not enter numbers in cells that require formulas. Most instructors will check both the values version and formulas version of your worksheets. The formulas version verifi es that you entered formulas, rather than numbers, in formulabased cells.


To Display the Formulas in the Worksheet and Fit the Printout on One Page

 

The following steps change the view of the worksheet from the values version to the formulas version of the worksheet and then print the formulas version on one page.

Figure 2–71

3

•    If necessary, click Landscape in the Orientation area to select it.

•    If necessary, click Fit to in the Scaling area to select it.

4

•    Click the Print button (Page Setup dialog box) to print the formulas in the worksheet on one page in landscape orientation (Figure 2–72). If necessary, in the Backstage view, select the Print Active Sheets option in the Settings area of the Print gallery.

•    When Excel displays the Backstage view, click the Print button to print the worksheet.

5

•    After viewing and printing the formulas version, press CTRL+ACCENT MARK (`) to instruct Excel to display the values version.

•    Click the left horizontal scroll arrow until column A appears.


Certifi cation

The Microsoft Offi ce Specialist (MOS) program provides an opportunity for you to obtain a valuable industry credential — proof that you have the Excel 2010 skills required by employers. For more information, visit the Excel 2010 Certifi cation Web page ( ex2010/cert).

To Change the Print Scaling Option Back to 100%

 

Depending on your printer, you may have to change the Print Scaling option back to 100% after using the Fit to option. Doing so will cause the worksheet to print at the default print scaling of 100%. The following steps reset the Print Scaling option so that future worksheets print at 100%, instead of being resized to print on one page. 1             If necessary, display the Page Layout tab and then click the Page Setup Dialog Box Launcher (Page Layout tab | Page Setup group) to display the Page Setup dialog box.

Click Adjust to in the Scaling area to select the Adjust to setting.

If necessary, type 100 in the Adjust to box to adjust the print scaling to a new percentage.

4   Click the OK button (Page Setup dialog box) to set the print scaling to normal.

5   Display the Home tab.

What is the purpose of the Adjust to box in the Page Setup dialog box?

The Adjust to box allows you to specify the percentage of reduction or enlargement in the printout of a worksheet. The default percentage is 100%. When you click the Fit to option, this percentage automatically changes to the percentage required to fi t the printout on one page.

To Save the Workbook and Quit Excel

 

With the workbook complete, the following steps save the workbook and quit Excel.

 

Chapter Summary

Quick Reference

For a table that lists how to complete the tasks covered in this book using the mouse, Ribbon, shortcut menu, and keyboard, see the Quick Reference Summary at the back of this book, or visit the Excel 2010 Quick

In this chapter you have learned how to enter formulas, calculate an average, fi nd the highest and lowest numbers in a range, verify formulas using Range Finder, added borders, align text, format numbers, change column widths and row heights, and add conditional formatting to a range of numbers. In addition, you learned to spell check a worksheet, print a section of a worksheet, and display and print the formulas version of the worksheet using the Fit to option. The items listed below include all the new Excel skills you have learned in

Reference Web page ().


this chapter.

1.     Enter a Formula Using the Keyboard (EX 75)

2.     Enter Formulas Using Point Mode (EX 77)

3.     Copy Formulas Using the Fill Handle (EX 80)

4.     Determine the Average of a Range of Numbers Using the Keyboard and Mouse (EX 84)

5.     Determine the Highest Number in a Range of

Numbers Using the Insert Function Box (EX 86)

6.     Determine the Lowest Number in a Range of Numbers Using the Sum Menu (EX 87)

7.     Copy a Range of Cells Across Columns to an

Adjacent Range Using the Fill Handle (EX 89)

8.     Verify a Formula Using Range Finder (EX 91)

9.     Change the Workbook Theme (EX 94)

10.  Change the Background Color and Apply a Box

Border to the Worksheet Title and Subtitle (EX 96)

If you have a SAM 2010 user profi le, your instructor may have assigned an autogradable version of this assignment. If so, log into the SAM 2010 Web site at to download the instruction and start fi les.

11.  Format Dates and Center Data in Cells (EX 98)

12.  Apply an Accounting Number Format and Comma Style Format Using the Ribbon (EX 100)

13.  Apply a Currency Style Format with a Floating

Dollar Sign Using the Format Cells Dialog

Box (EX 102)

14.  Apply a Percent Style Format and Use the Increase Decimal Button (EX 103)

15.  Apply Conditional Formatting (EX 104)

16.  Change the Widths of Columns (EX 107)

17.  Change the Heights of Rows (EX 110)

18.  Check Spelling on the Worksheet (EX 112)

19.  Change the Worksheet’s Margins, Header, and Orientation in Page Layout View (EX 114)

20.  Print a Section of the Worksheet (EX 118)

21.  Display the Formulas in the Worksheet and Fit the

Printout on One Page (EX 119)



 
 
 
 
 
 
 

Formulas, Functions, and Formatting   Excel Chapter 2   EX 129

2.    Use the ctrl key and mouse to select the nonadjacent chart ranges A4:A10 and G4:G10. That is, select the range A4:A10 and while holding down the ctrl key, select the range G4:G10.

3.    Click the Bar button (Insert tab | Charts group) and then select Clustered Bar in 3-D in the 3-D Bar area. When the chart is displayed on the worksheet, click the Move Chart button (Chart Tools Design tab | Location group). When the Move Chart dialog box appears, click New sheet and then type Bar Chart for the sheet name. Click the OK button (Move Chart dialog box). Change the sheet tab color to the Green standard color.

4.    When the chart is displayed on the new worksheet, click the Series 1 series label and then press the delete key to delete it. Click the chart area, which is a blank area near the edge of the chart, click the Shape Fill button (Chart Tools Format tab | Shape Styles group), and then select Orange, Accent 1, Lighter 60% in the gallery (column 5, row 3). Click one of the bars in the chart. Click the Shape Fill button (Chart Tools Format tab | Shape Styles group) and then select the Green standard color. Click the Chart Title button (Chart Tools Layout tab | Labels group) and then select Above Chart in the Chart Title gallery. If necessary, use the scroll bar on the right side of the worksheet to scroll to the top of the chart. Click the edge of the chart title to select it and then type Accounts Receivable as the chart title.

5.    Drag the Accounts Receivable tab at the bottom of the worksheet to the left of the Bar Chart tab to reorder the sheets in the workbook. Preview and print the chart.

6.    Click the Accounts Receivable sheet tab. Change the following beginning balances: customer Oliver Danielsson to $702.13 and customer Lin Tuan to $482.74. The company also decided to change the service charge from 3.25% to 2.75% for all customers. After copying the adjusted formula in cell F4 to the range F5:F10, click the Auto Fill Options button and then click Fill without Formatting to maintain the original formatting in the range F5:F10. The total new balance in cell G11 should equal $3,720.82.

7.    Select both sheets by holding down the shift key and then clicking the Bar Chart tab. Preview and print the selected sheets. Save the workbook. Submit the assignment as specifi ed by your instructor.

In the Lab

Lab 2: Sales Summary Worksheet

Problem:You have been asked to build a   Table 2–7 Electry Auto Start-Up Financing Needs Data worksheet for a start-up company, Electry

Auto, that analyzes the fi nancing needs for              Month     Revenue  Costs the company’s fi rst six months in business.

                                                                                            January                                105000                                220000

The company plans to begin operations

in January with an initial investment of       February  82000      260000 $500,000.00. The expected revenue and         March      200000    255000 costs for the company’s fi rst six months        April         250000    320000 are shown in Table 2–7. The desired   May          325000    420000 worksheet is shown in Figure 2–79 on the                 June         510000    540000 following page. The initial investment is shown at the starting balance for January

(cell B4). The amount of fi nancing required by the company is shown as the lowest ending balance (cell F12).

 

Formulas, Functions, and Formatting   Excel Chapter 2   EX 131

8.    One at a time, merge and center the worksheet title and subtitle across columns A through F. Select cells A1 and A2 and change the background color to light blue (column 7 in the Standard Colors area in the Fill Color gallery). Apply the Title cell style to cells A1 and A2. Change the worksheet title in cell A1 to 28-point white (column 1, row 1 on the Font Color gallery). Change the worksheet subtitle to the same color. Assign a thick box border to the range A1:A2.

9.    Center the titles in row 3, columns A through F. Apply the Heading 3 cell style to the range A3:F3. Use the Italic button (Home tab | Font group) to italicize the column titles in row 3 and the row titles in the range A10:A12.

10.   Assign a thick box border to the range A10:F12. Change the background and font color for cell F12 to the same colors applied to the worksheet title in Step 8.

11.   Change the row heights of row 3 to 36.00 points and row 10 to 30.00 points.

12.   Assign the Accounting number format to the range B4:F4. Assign the Comma style format to the range B5:F9. Assign a Currency format with a fl oating dollar sign to the range B10:F12.

13.   Rename the sheet tab as Start-Up Financing Needs. Apply the Light Blue color to the sheet tab. Change the document properties, as specifi ed by your instructor. Change the worksheet header with your name, course number, and other information as specifi ed by your instructor. Save the workbook using the fi le name Lab 2-1 Part 1 Electry Auto Start-Up Financing Needs. Print the entire worksheet in landscape orientation. Next, print only the range A3:B9.

14.   Display the formulas version by pressing ctrl+accent mark (`). Print the formulas version using the Fit to option button in the Scaling area on the Page tab in the Page Setup dialog box. After printing the worksheet, reset the Scaling option by selecting the Adjust to option button on the Page tab in the Page Setup dialog box and changing the percent value to 100%. Change the display from the formulas version to the values version by pressing ctrl+accent mark (`). Do not save the workbook.

15.   Submit the revised workbook as requested by your instructor.

Instructions Part 2: In this part of the exercise, you will change the revenue amounts until the lowest ending balance is greater than zero, indicating that the company does not require fi nancing in its fi rst six months of operation. Open the workbook created in Part 1 and save the workbook as Lab 2-1 Part 2 Electry Auto Start-Up Financing Needs. Manually increment each of the six values in the revenue column by $1,000.00 until the lowest ending balance in cell F12 is greater than zero. The value of cell F12 should equal $5,000.00 All six values in column C must be incremented the same number of times. Update the worksheet header and save the workbook. Print the worksheet. Submit the assignment as specifi ed by your instructor.

Instructions Part 3: In this part of the exercise, you will change the monthly costs until the lowest ending balance is greater than zero, indicating that the company does not require fi nancing in its fi rst six months of operation. Open the workbook created in Part 1 and then save the workbook as Lab 2–1 Part 3 Electry Auto Start-Up Financing Needs. Manually decrement each of the six values in the costs column by $1,000.00 until the lowest ending balance in cell F12 is greater than zero. Decrement all six values in column C the same number of times. Your worksheet is correct when the lowest ending balance in cell F12 is $5,000.00. Update the worksheet header and save the workbook. Print the worksheet. Submit the assignment as specifi ed by your instructor.


 
 
   

Property of Cengage Learning



136