excel 2010 contents - achievementses.com...view the cell reference in the name box. 3. click cell h9...

Contents…

EXCEL 2010

1

2

ISBN 978-0-9967-1763-2

Copyright © 2014 M. Kalmanowitz

LNM Publishing, Lakewood, NJ 08701

ALL RIGHTS RESERVED. This publication is protected by copyright. No part of this

publication may be adapted, reproduced, stored in a retrieval system, or transmitted in

any form or by any means, electronic, mechanical, photocopying, recording, or

otherwise, without prior written permission from the author.

For product information or orders please contact LNM Publishing at 732.806.1725

Contents…

EXCEL 2010

3

CHAPTER 1 EXCEL BASICS

INTRODUCTION .......................................... 8

EXERCISE 1 ................................................. 9 C H A N G E A C T I V E C E L L . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C H A N G E B E T W E E N W O R K SH E E T S . . . . . . . . . . . . . . . . V I E W / H I D E D I F F E R E N T E L E M E N T S O N T HE

S C R E E N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Z O O M

EXERCISE 2 ............................................... 12 E N T E R L A B E L S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . M A K E C O R R E C T I ON S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . UN D O / R E D O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C L E A R / D E L E T E C E L L C ON TE N T S . . . . . . . . . . . . . . . . . .

EXERCISE 3 ............................................... 15 VA L U E S & N U M E R I C L A B E L S . . . . . . . . . . . . . . . . . . . . . . . F R A C T I O N S & M I X E D N U M B E R S . . . . . . . . . . . . . . . . . . . DA T E S

EXERCISE 4 ............................................... 18 C O L U M N W I D T H /R O W H E I G H T . . . . . . . . . . . . . . . . . . . . DA T A A L I G N M E N T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . M E R G E A N D C E N TE R . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 5 ............................................... 21 A U T O C O M P L E T E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P I C K F R O M L I S T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A U T O C O R R E C T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SP E L L I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 6 ............................................... 24 A U T O F I L L . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C R E A T E S E RI E S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O P Y C E L L S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O P Y F O R M A T T I N G . . . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 1 .. . . . . . . . . . . . . . . . . . . . . . . . . . 27 MAS TE R IT! 2 .. . . . . . . . . . . . . . . . . . . . . . . . . . 28 MAS TE R IT! 3 .. . . . . . . . . . . . . . . . . . . . . . . . . . 29

CHAPTER 2 FORMULAS &

FUNCTIONS

EXERCISE 7............................................... 32 PR I N T I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P R I N T P R E V I E W . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P A G E B R E A K P R E V I E W . . . . . . . . . . . . . . . . . . . . P R I N T S E L E C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . S C A L E T O F I T

EXERCISE 8............................................... 35 H E A D E R A N D F O O T E R . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P A G E L A Y O U T V I E W . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P A G E S E T U P . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P R I N T T I T L E S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 9............................................... 38 F O R M U L A S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P R I N T F O R M U L A S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 10 ............................................ 41 C O P Y I N G F O R M U L A S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . R E L A T I V E R E F E R E N CE . . . . . . . . . . . . . . . . . . . . A B S O L U T E R E F E R E N CE . . . . . . . . . . . . . . . . . . . . M I X E D R E F E R E NC E . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 11 ............................................ 44 R A N G E S : . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . S E L E C T I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N A M I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

Contents…

EXCEL 2010

EXERCISE 12 ............................................ 47 C O M M O N F U N C T I O N S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SU M . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A V E R A G E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O U N T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O U N T A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . M A X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . M I N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . R O U N D . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 13 ............................................ 50 A U T O S U M . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A U T O C A L C U L A T E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 4 .. . . . . . . . . . . . . . . . . . . . . . . . . . . 52 MAS TE R IT! 5 .. . . . . . . . . . . . . . . . . . . . . . . . . . . 53 MAS TE R IT! 6 .. . . . . . . . . . . . . . . . . . . . . . . . . . . 54 MAS TE R IT! 7 .. . . . . . . . . . . . . . . . . . . . . . . . . . . 55

CHAPTER 3 FORMATTING

TECHNIQUES

EXERCISE 14 ............................................ 58 F O N T F O R M A T S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F I L L TH E M E S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C E L L ST Y L E S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 15 ............................................ 61 N U M B E R F O R M A T S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A C C O U N T I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P E R C E N T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O M M A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D E C I M A L ( I N C R E A S E / D E C RE A S E . ) . . . C U S T O M N U M B E R F O R M A T S . . . . . . . . . . . .

EXERCISE 16 ............................................ 64 C O P Y A N D C L E A R F O R M A T S . . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 8 .. . . . . . . . . . . . . . . . . . . . . . . . . . . 67

CHAPTER 4 MANAGING

WORKSHEETS

EXERCISE 17 ............................................ 70 M A N I P U L A T I N G W O R K S H E E T . . . . . . . . . . . . . . . . . . . . . . I N S E R T . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D E L E T E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O P Y . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . M O V E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . R E N A ME . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C H A N G E C O L O R . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . H I D E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G R O U P . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 18 ............................................ 73 IN S E R T / D E L E T E C E L L S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I N S E R T / D E L E T E C O L U M N S/ R O W . . . . . . . . . . . . . . . . . M O V I N G D A T A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 19 ............................................ 76 H I D E D A T A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C U S T O M V I E W S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 20 ............................................ 79 C O P Y A N D P A S T E S P E C I A L . . . . . . . . . . . . . . . . . . . . . . . . . . . T R A N S P O S E D A T A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 21 ............................................ 82 C O M B I N E D A T A W I T H C O P Y A N D P A S T E

S P E C I A L . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 9 .. . . . . . . . . . . . . . . . . . . . . . . . . . 85 MAS TE R IT! 10 .. . . . . . . . . . . . . . . . . . . . . . . . 87

5

Contents…

EXCEL 2010

CHAPTER 5 IMPORTING ,

EXPORTING AND DISTRIBUTING

DATA

EXERCISE 22 ............................................ 90 C O N V E R T T E X T T O C O L U M N . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 23 ............................................ 94 IM P O R T T E X T F I L E S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 24 ............................................ 97 L I N K A N D E M B E D E X C E L D A T A . . . . . . . . . . . . . . . . . . . . . P A S T E A P I C T U R E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 25 ......................................... 100 F R E E Z E L A B E L S W H I L E S C RO L L I N G . . . . . . . . . . . . . . SP L I T A W O R K S H E E T I N T O P A N E S . . . . . . . . . . . . . . . . DU P L I C A T E W O R K B O O K W I N D O W . . . . . . . . . . . . . . . . . SA V I N G A W O R K S P A C E . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 26 ......................................... 103 DR A G A N D D R O P B E T W E E N

W O R K B O O K S / W O R K S H E E T S . . . . . . . . . . . . . . . . . . . . . . . . . L I N K W O R K B O O K S / W O R K S H E E T S . . . . . . . . . . . . . . . . .

EXERCISE 27 ......................................... 106 3D F O R M U L A S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 11 .. . . . . . . . . . . . . . . . . . . . . . 109 MAS TE R IT! 12 .. . . . . . . . . . . . . . . . . . . . . . 111 MAS TE R IT! 13 .. . . . . . . . . . . . . . . . . . . . . . 112

CHAPTER 6 ADVANCED

FUNCTIONS

EXERCISE 28 ......................................... 114 F U N C T I O N S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 29 ......................................... 117 IF F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 30 ......................................... 120 N E S T E D IF F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 31 ......................................... 123 C O U N T IF F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SU M IF F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C O U N T IF S F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SU M IF S F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 32 ......................................... 126 C O N D I T I O N A L F O R M A T T ING . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 14 .. . . . . . . . . . . . . . . . . . . . . . 129

6

Contents…

EXCEL 2010

CHAPTER 7 MORE ADVANCED

FUNCTIONS & DATA ANALYSIS

EXERCISE 33 ......................................... 132 PM T F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . FV F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 34 ......................................... 135 G O A L S E E K . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 35 ......................................... 138 VL O O K U P F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . HL O O K U P F U N C T I O N . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

EXERCISE 36 ......................................... 141 L O C K/ U N L O C K C E L L S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . P R O T E C T A R AN G E / W O R K SH E E T / W O R K B O O K

EXERCISE 37 ......................................... 144 F O R M U L A A U D I T I N G . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F O R M U L A E R R O R C HE C K I N G . . . . . . . . . . . . . . . . . . . . . . . .

MAS TE R IT! 15 .. . . . . . . . . . . . . . . . . . . . . . 147 MAS TE R IT! 16 .. . . . . . . . . . . . . . . . . . . . . . 148

Chapter 1

Excel Basics

EXERCISE 1

Change active cell

Change between worksheets

View/hide different elements

on the screen

Zoom

EXERCISE 2

Enter labels

Make corrections

Undo/redo

Clear/delete cell contents

EXERCISE 3

Values & Numeric Labels

Fractions & Mixed numbers

Dates

EXERCISE 4

Column width/Row height

Data alignment

Merge and center

EXERCISE 5

Autocomplete

Pick from List

Autocorrect

Spelling

EXERCISE 6

Autofill

Create series

Copy cells

Copy formatting

COMMANDS RIBBON / GROUP BUTTON SHORTCUT

Align text left, center, right Home / Alignment

Align top, center, bottom Home / Alignment

Clear cell contents Home / Editing

Go to Home / Editing

Ctrl + G

Merge and center Home / Alignment

Spell check Review / Proofing

View/ hide screen elements View / Show

8

EXCEL WINDOW

Introduction

Quick Access

Toolbar

Formula bar

Sheet tabs

Status bar

View

buttons

Zoom slider

Column headings

Tab

scrolling

buttons

Horizontal

scroll bar

Help

Name box

Ribbon tabs

Vertical

scroll bar

Row headings

Ribbon

Group

Active Cell

Exercise 1

CHANGE ACTIVE CELL ZOOM

CHANGE BETWEEN

WORKSHEETS VI EW/H ID E DI F FER ENT

EL E ME NT S ON TH E S CRE EN

9

TERMS

Active cell: The cell that contains the cell pointer.

Cell: The intersection of a column and row on a

worksheet. Each box is a cell for data to be entered

into.

Cell pointer: A dark outline around the active cell.

Cell reference/cell address: The location of the cell

on the worksheet identified by the column letter

and row number {i.e. B12}

Column heading: The letter for each column used to

identify the cell address.

Formula bar: The bar that provides information

about the active cell. As you enter information into

a cell it simultaneously appears in the formula bar.

Name box: The box that displays the cell reference

of the active cell.

Row heading: The number for each row used to

identify the cell address.

Sheet tabs: The tabs that tell us the name of the

worksheet and allows us to switch between

worksheets - on the bottom left of the window.

Workbook: A file created in Excel.

Worksheet: The sheets of paper in a workbook.

The work area for entering data; made up of

columns and rows.

APPLICATION

1. List 5 ways to go to a specific cell.

_______________________________________________________________________________________________

2. Which ribbon do you use to hide/view the different window elements?

_______________________________________________________________________________________________

NOTES

10

Chapter 1 Exercise 1

Excel 2010

PRACTICE IT!

1. Follow these steps to start Excel from the taskbar:

a. Click Start.

b. Select All Programs, Microsoft Office, and then Microsoft Office Excel 2010.

2. Move the active cell pointer using the keyboard:

a. Press the right arrow key four times until cell E1 is highlighted.

i. Type your name.

b. Press the down arrow key four times until cell E5 is highlighted.

i. Type your class.

View the cell reference in the name box.

3. Click cell H9 to make it the active cell.

View the cell reference in the name box.

4. Press F5 to activate the Go To command.

5. In the reference text box, type T98.

6. Click OK.

The active cell changes to T98.

7. Click in the name box to change the active cell to the following, pressing Enter after each new cell address:

a. B1492 (row 1492, column B).

b. XFD1048576 (bottom right of worksheet).

8. Press Ctrl+Home to move to cell A1.

a. Type in the cell: XL01.

9. Display sheet 2.

10. Click cell D4.

11. Point to the horizontal scroll bar and click the right scroll arrow.

The worksheet moves right by one column but the active cell does not change.

12. Redisplay Sheet1.

The active cell for Sheet1 remains the same (A1). It did not change even as you changed the active cell on Sheet2.

13. Hide the formula bar.

14. Change to Page Layout view.

15. Return to Normal view.

16. Restore the formula bar.

17. Zoom to 150%.

18. Save the file as XL01_xx.

NOTE: every time it says to save as _xx do not type xx – instead, replace xx with your initials.

19. Print & close.

11


Excel 2010

REVIEW IT!

1. Start Excel.

2. Move the pointer to cell H8 using the arrow keys. Type RV01.

3. Change to Sheet3.

4. Using Go To, move the active cell to the following:

a. Q201

b. C96

c. HHH1000

5. Change back to Sheet1.

6. Using the Name box, move the active cell to the following:

a. G6 and type your first name.

b. B40 and type your last name.

c. ZA671

7. Using Go To move the active cell back to C96.

8. Zoom to 75%.

9. Using the mouse, move the active cell to the following:

a. R10

b. K21

c. F32 type your class name.

10. Hide the gridlines.

11. Change to Full Screen view.

12. Return to Normal view by pressing Esc.

13. Redisplay the gridlines.

14. Save the file as RV01_xx. Remember: Replace xx with your initials.

15. Print & close.

Exercise 2

ENTER LABELS UNDO / REDO

MAKE CORRECTIONS CLEAR / D EL ET E C EL L

CONT EN TS

12

TERMS

Default: The standard preprogrammed settings. Labels: A text entry in a cell.

APPLICATION

1. What are different ways to finalize a cell entry?

____________________________________________________________________________________________

2. How do you enable cell editing?

____________________________________________________________________________________________

3. How are labels, by default, aligned in a cell?

____________________________________________________________________________________________

4. What happens if a label is longer than the cell?

____________________________________________________________________________________________

5. What‟s the difference between deleting cell contents and canceling an entry?

____________________________________________________________________________________________

6. How do you insert a line break in a cell?

____________________________________________________________________________________________

7. What are row labels and column labels?

____________________________________________________________________________________________

NOTES

13


Excel 2010

PRACTICE IT!

1. Start Excel, if necessary.

2. Start a new blank workbook.

3. Put your name in A1 and exercise name in A2.

4. Save the file as XL02_xx. (Replace xx with your initials.)

5. Go to cell C2. Begin typing, Appliance Services but do not finalize the entry.

6. Cancel the entry.

The Cancel button is located to the left of the formula bar.

7. Instead, enter Appliance Services in cell A4.

8. In cell B6 begin typing Customers serviced, but before you press enter, use backspace to correct the entry so that it reads Customers received service.

9. In cell A9 type Dryer and press enter.

10. Click in cell A9 and replace its contents by typing Washer/dryer.

11. In column A, type the rest of the entries, exactly as follows:

a. Cell A10: Refrigerater

b. Cell A11: Freezer

c. Cell A12: Air conditionor

d. Cell A13: Heating system

e. Cell A14: Oven

12. Make cell A15 active. Click in the formula bar and type Totals.

13. You misspelled two of the entries. Enable cell editing to correct them:

a. Cell A10: Refrigerator

b. Cell A12: Air conditioner

14. Enable cell editing to correct the entry in cell A4 so that it reads Appliance Repair Services

15. Use the Clear Contents command to clear cell A14.

16. Enter the column labels for row 8 as shown in Illustration A.

a. Correct any errors using the method you choose.

b. Insert a line break in order for Toms River to go on 2 lines.

17. 5 people had their washer/dryer serviced in Lakewood. Enter 5 into cell B9.

Notice that some labels in column A extend into column B. As long as column B is empty you see the entire entry. But once you enter data into column B the labels are cut off.

a. Undo the last action.

18. Print your worksheet.

19. Close the file, saving all changes.

Illustration A

14


Excel 2010

REVIEW IT!




4. Save the file as RV02_xx. (Replace xx with your initials.)

5. Create a worksheet to track cookie sales for your grade; you will be setting it up in a table format, but will not include any specific order information yet:

a. Enter a title for the worksheet.

b. Enter column labels for each cookie type. (i.e.: chocolate chip, oatmeal, etc.) Enter at least 5 cookie types.

c. Enter row labels for each person in your grade who has placed an order. Enter at least 6 names.


7. Close the file, saving all changes.

Exercise 3

VALUES AND NUMERIC

LABELS

DATES

FRACTIONS AND MIXED

NUMBERS

15

TERMS

Value: A number entry in a cell.

Numeric label: A number treated as a label with no

value significance and will not be used in a

calculation.

Label prefix: An apostrophe {„} used to indicate

that a number is a label and not a value.

APPLICATION

1. When would you make a number into a numeric label?

_____________________________________________________________________________________________

2. How do you make a number into a numeric label?

____________________________________________________________________________________________

3. How do you enter a fraction into a cell? What about a mixed number?

____________________________________________________________________________________________

4. What are two examples of correct formats to use when entering dates?

____________________________________________________________________________________________

5. What‟s the shortcut for today‟s date?

____________________________________________________________________________________________

NOTES

16


Excel 2010

PRACTICE IT!


2. Open XL03_xx.


4. Put your name in F11 and exercise name in F12.

5. Click cell B12 and enter the phone number 288-0217.

6. Complete the section under the word Invoice as shown in Illustration A.

a. Type today’s date in cell F3.

Make sure the invoice and the member numbers are entered as a numeric label, and that you instruct Excel to ignore the error.

b. Enter the invoice number in cell F4.

c. Replace Customer ID with Member #.

d. In cell F5, type the member number 77894.

e. Enter a due date in cell F15 that is next month, same day as today. (Do not use the date that is in the illustration.)

Use the format, mm/dd/yy to enter the due date.

7. Enter the Qty, Size, & Unit Price values for the order from Hoji, as shown in Illustration A.

The total for each item and the total invoice amount is computed automatically. Do not touch the Line Total column!!!

Do not type $ symbols. You will learn about them in Chapter 3. Ignore all cell alignments that were customized for this invoice.

8. In cell C41 enter 1/2. (This should be entered as a fraction and not as a date!)

9. In cell D41 enter paid.

10. Print. Close the workbook, saving all changes.

Illu

stra

tion

A

17


Excel 2010

REVIEW IT!


2. Start a new blank workbook and save it as RV03_xx. (Replace xx with your initials.)


Create a worksheet to track the orders for new little league uniforms for the team you coach.

4. Type a title for your report in cell B3.

5. Enter the following labels as column labels starting in Column B (all on same row):

a. ID Number

b. Name

c. Address

d. City

e. ZIP Code

f. Uniform Size

g. Cap Size

h. Deposit

6. In the rows below the column labels enter data for at least 4 orders.

a. Make sure you enter the ID Number & ZIP Code as a label.

b. Enter uniform and cap sizes that use fractions.

c. Enter an amount for the deposit.

7. Print the worksheet.

8. Close the workbook, saving all changes.

Exercise 4 DATA ALIGNMENT

MERGE AND CENTER

COLUMN WIDTH / ROW HEIGHT

18

TERMS

Cell Orientation: The slant of text in a cell.

APPLICATION

1. Why do ####### display in a cell?

____________________________________________________________________________________________

2. How do you fix this error?

____________________________________________________________________________________________

3. How do you adjust column width to fit longest entry?

____________________________________________________________________________________________

4. What‟s the difference between adjusting the column width to fit longest entry versus as much as necessary?

____________________________________________________________________________________________

5. How do you set the slant of the text in the cell?

____________________________________________________________________________________________

NOTES

19


Excel 2010

PRACTICE IT!


2. Open XL04_xx.



5. Drag with the mouse to adjust the width of column A so that the row labels (appliances) can be seen fully.

You shouldn’t adjust column A to fit its data automatically, because if you did, the column would be adjusted to the width of the company name which is also in column A, and it would be too wide for its data.

6. Adjust columns B through F to fit their data by using the following steps.

a. Drag over the column headings for columns B through F.

b. Move the mouse pointer to the right edge of the heading for column F.

c. Double-click, and all the selected columns will be adjusted to fit their longest entry.

7. Column B is too wide because it was adjusted to fit the worksheet title. Click cell B8 and use the ribbon to auto fit column width.

8. Enter the number of clients serviced in each location centered, as shown in Illustration A.

9. Enter the report number as a label in cell G6. (See Illustration A.)

10. Center the title (Customers Serviced) across columns B-F.

11. Change the orientation of the column labels (Row 8) to Angle Counterclockwise.

12. Right align the Appliances and Totals in Column A.

13. Change the row height for the company name (Appliance Repair Services) to 50.

14. Vertically align the company name in the middle.



Illustration A

20


Excel 2010

REVIEW IT!


2. Open RV04_xx.



5. Adjust the column widths to fit the longest entry.

6. Readjust column A to a column width of 11.

7. Merge and center the title.

8. Adjust the row height for the rows that contain the information for the members ordering. Choose your own height. Make it look professional.

9. Change the alignments (horizontal, vertical and orientation) for the different data on the page.



Exercise 5 AUTOCOMPLETE

PICK FROM LIST

AUTOCORRECT

SPELLING

21

TERMS

Autocomplete: A feature used to complete an

entry based on previous entries made in the

column containing the active cell.

Autocorrect: A feature that corrects many common

typographical errors.

Pick from list: A shortcut used to insert repeated

labels in a single column.

APPLICATION

1. Which feature automatically corrects common typographical errors?

____________________________________________________________________________________________

2. Which feature allows you to quickly finish entering a label that had already been typed in that same column?

____________________________________________________________________________________________

3. What should you do when Spellcheck flags a proper noun?

____________________________________________________________________________________________

NOTES

22


Excel 2010

Illustration A

PRACTICE IT!


2. Open XL05_xx.


4. Put your name in D1, and exercise name in D2.

5. Double click cell A10 to enable cell editing. Type (r) after Whole Grains Bread and press enter.

After you press enter, notice that AutoCorrect has changed Whole Grains Bread (r) to Whole Grains Bread®.

6. Type the data as shown in Illustration A.

a. As you type the customer’s name (Column C), use the AutoComplete feature to speed up the process.

b. Use the Pick from Drop-down List feature to enter the item names (Column D).

7. Click cell A10.

8. Use the Spelling & Grammar feature to check the spelling in your worksheet.

a. Change all misspelled words.

b. Ignore all instances of Gribaldi’s & Ristorante. (This is the correct spelling. It is Italian for restaurant.)



23


Excel 2010

REVIEW IT!


2. Open RV05_xx.



5. Complete the sheet as shown in Illustration A.

a. Use AutoComplete for the Employees.

b. Pick from Drop-down List to find the products when applicable.

6. Adjust columns so that the data can be fully viewed (but not unnecessarily wide.)

7. Merge and center the store title.

8. Check spelling in your worksheet.

a. Ignore all errors in names.

b. Change all other errors.

9. Print the workbook.


Illustration A

Exercise 6 AUTOFILL

CREATE SERIES

COPY CELLS

COPY FORMATTING

24

TERMS

Autofill: The feature used to fill a range of cells with

the same or consecutive data.

Fill handle: A small square in the lower right hand

corner of the active cell.

Series: A sequence of numbers, dates, times or text.

APPLICATION

1. Where is the fill handle? What does the mouse pointer look like when resting on the fill handle?

____________________________________________________________________________________________

2. What are the 3 uses of the autofill feature?

____________________________________________________________________________________________

3. How do you create an incremental series?

____________________________________________________________________________________________

4. If dates are not filling correctly as a series – what might be the problem?

____________________________________________________________________________________________

5. What happens when you fill a time?

____________________________________________________________________________________________

NOTES

25


Excel 2010

PRACTICE IT!


2. Open XL06_xx.



5. Use Illustration A as a guideline to make the following changes:

6. Create a series of months:

a. Type January in cell B1.

b. Drag the fill handle of cell B1 across to cell G1.

7. Create another incremental series:

a. Type Year 1 in cell A2.

b. Type Year 3 in cell A3.

c. Select both cells A2 & A3. Drag the fill handle of cell A3 down to cell A5.

8. Use the fill handle to copy cells but not the formatting.

a. Copy B2 to cells C2, D2, & E2 using the fill handle.

b. By Auto Fill Options choose the option to Fill Without Formatting.

9. Use the fill handle to apply formatting only.

a. Copy B2 down until B5 using the fill handle.

b. Choose the Fill Formatting Only option.

10. Create another series:

a. Type Qtr 1 in cell A10.

b. Use the fill handle to create a series from cell A10 to cell A13.



Illustration A

26


Excel 2010

REVIEW IT!





You will be creating a worksheet of when your cleaning lady came to you for the past 2 months. You will mark the times she arrived and departed, and how much you paid her.

5. Your cleaning lady comes to you once a week, every week on the same day.

a. Type Date in cell B3 as a column label.

b. Enter the date she came to you 2 month ago in cell B5. Enter the next time she came in cell B6.

She always comes 7 days after she’s been there the time before.

c. Use the dates in cells B5 & B6 to create a series of 8 dates.

6. She arrives between 9:00 and 11:00, and leaves 1 hour later.

a. Type Time Arrived in cell C3 as a column heading.

b. Type Time Departed in cell D3 as a column heading.

c. Type in the times in column C. She is not reliable and does not come the same time every day.

d. Fill in the times she leaves in column D without typing them.

7. You pay her $8 an hour.

a. Type Payment in cell E3 as a column heading.

b. Type in the amount you paid her in cell E5.

c. Copy her payment to the rest of the column.

8. Adjust column width as necessary.



27

Chapter 1 Master It 1

Excel 2010

MASTER IT!



3. Save the file as MS01_xx. (Replace xx with your initials.)


5. Enter the data as shown in Illustration A.

a. Enter today’s date in cell E4.

b. Enter your name in cell B12.

c. Enter your ID number (make it up!!) as a label in cell B13.

6. Adjust column A to fit its data.

7. Set the width of columns B to G to exactly 11 characters.

8. Center the day of the week column labels. Increase their row height to 30 points and align middle vertically.

9. Merge and center the company name across all the columns.



Illustration A

28


Excel 2010

MASTER IT!


2. Open MS02_xx.

3. Save the file as MS02_xx. (Replace xx with your initials.)

4. Put your name in A18, and exercise name in A19.

5. Enter the data as shown in Illustration A.

a. Do not enter information into column F, it’ll automatically be entered by the computer.

b. Use the Pick from Drop-down List and the AutoComplete features to help you insert the data.

c. Use numeric labels where necessary.

6. Spell check the worksheet.

a. Make all necessary changes.

b. Ignore all name errors.



Illustration A

29


Excel 2010

MASTER IT!


2. Open MS03_xx.

3. Save the file as MS03_xx. (Replace xx with your initials.) 4. Put your name in A15, and exercise name in A16.

You are the office manager for Deering industries. One of your responsibilities is creating a monthly calendar containing information on staff meetings, training, and due dates for time cards.

5. Insert the text September 2010 in cell A2.

6. Insert the days of the week by creating a series in cells A3 through G3 (Sunday through Saturday)

7. Insert the dates by creating a series for each week in rows 4, 6, 8, 10 & 12. The first of the month is on Wednesday(D4).

When creating a series here, fill without formatting. 8. Excel training will be held Thursday, September 2, from 9-11 a.m.

a. Insert this information in cell E5 on two lines.

9. A staff meeting is held on the second and fourth Monday of each month from 9-10 a.m. Insert this information appropriately on 2 lines in the correct cell.


11. Close the workbook, saving all changes

30

Chapter 1 Notes…

Excel 2010

NOTES

Chapter 2

Formulas & Functions

EXERCISE 7

Printing

Print preview

Scale to fit

Page break preview

Print selection

EXERCISE 8

Header and Footer

Page layout view

Page setup

Print titles

EXERCISE 9

Formulas

Print formulas

EXERCISE 10

Copying formulas

Relative reference

Absolute reference

EXERCISE 11

Ranges:

Selecting

Naming

EXERCISE 12

Common functions

Sum, average, count, counta,

max, min, round

EXERCISE 13

Autosum

AutoCalculate


Autosum Home / Editing

Display formulas Formula / Formula Auditing Ctrl + `

Functions Formula / Function Library

Header and footer Insert / Text

Managing range names Formulas / Defined names

Page break preview View / Workbook views

Page orientation Page layout / Page setup

Print gridlines/heading Page Layout / Sheet options

Print preview File / Print

Remove page break Page layout / Page setup

Scale to fit Page layout / Scale to fit

Chapter 3

Formatting Techniques

EXERCISE 14

Font formats

Fill

Themes

Cell Styles

EXERCISE 15

Number formats

Accounting

Percent

Comma

Decimal increase/decrease

Custom Number Formats

EXERCISE 16

Copy formats

Clear formats


Accounting number format Home / Number

Bold / Italics Home / Font

Cell style Home / Styles

Clear formats Home / Editing

Comma style Home / Number

Currency format Home / Number

Custom number format Home / Number

Fill color Home / Font

Format painter Home / Clipboard

Increase decimal / Decrease decimal Home / Number

Percent style Home / Number

Theme Page layout / Theme

Chapter 4

Managing Worksheets

EXERCISE 17

Insert, delete, copy, move and

rename worksheets

Change color of a worksheet

tab

Hide sheets

Group sheets

EXERCISE 18

Insert/delete cells

Insert/delete columns/rows

Moving data

EXERCISE 19

Hide data

Custom views

EXERCISE 20

Copy and paste special

Transpose data

EXERCISE 21

Combine data with copy and

paste special


Copy Home / Clipboard

Custom view View / Workbook views

Cut Home / Clipboard

Delete cell / row / column Home / Cells

Hide / Unhide worksheet Home / Cells

Hide cell Home / Number

Hide row / column Home / Cells

Insert cell / row / column Home / Cells

Insert worksheet Home / Cells

Paste special Home / Clipboard

Transpose Home / Clipboard

Chapter 5

Importing, Exporting &

Distributing Data

EXERCISE 22

Convert text to column

EXERCISE 23

Import text files

EXERCISE 24

Link and embed excel data

Paste a picture

EXERCISE 25

Freeze labels while scrolling

Split a worksheet into panes

Duplicate workbook window

Saving a workspace

EXERCISE 26

Drag and drop between

workbooks/worksheets

Link workbooks/worksheets

EXERCISE 27

3D Formulas


Arrange all windows View / Window

Convert text to columns Data / Data tools

Duplicate workbook window View / Window

Freeze window panes View / Window

Import a text file View / Window

Link cell Home / Clipboard

Maximize the window

Restore down the window

Save a workspace View / Window

Split window View / Window

Chapter 6

Advanced Functions

EXERCISE 28

Functions

EXERCISE 29

IF Functions

EXERCISE 30

Nested IF Functions

EXERCISE 31

CountIF/CountIFS

SumIF/SumIFS

EXERCISE 32

Conditional Formatting


Conditional formatting Home / Styles

114

Exercise 28

FUNCTIONS

TERMS

There are no new terms this exercise.

APPLICATION

Functions are _____________________ provided by Excel to perform special calculations.

Parts of a function: Every function starts with an ______ sign, followed by the function name.

Following the name is an open parenthesis in which the ____________ are placed. Multiple

arguments within a function are separated by _________. A closed parenthesis ends the

function.

What is a nested function?

________________________________________________________________________

Common Functions:

SUM _____________________________________________________________________

AVERAGE ________________________________________________________________

COUNT __________________________________________________________________

COUNTA _________________________________________________________________

MAX _____________________________________________________________________

MIN ______________________________________________________________________

ROUND ___________________________________________________________________

What is the format of a round function?_________________________________

5 ways to insert a function:

1. Manually

2. Using Function AutoComplete ________________________________________

3. Formula Tab- ___________________ group

4. Insert Function Button – found in two places _____________ ________________

5. For common functions, use ________ tab, __________ group, _________ button.

AutoCalculate:

What? ____________________________________________________________

Where?___________________________________________________________

How?_____________________________________________________________

Absolute vs. Relative References:

What is an absolute reference? Why is it used? How is it indicated?

________________________________________________________________________

________________________________________________________________________

115


Excel 2010

PRACTICE IT!


2. Open XL28_xx.


4. Create a formula in cell D4 that calculates the annual rent.

5. Copy the formula to the range D5:D10.

6. Create a function in cells C11 and D11 to total the expenses.

7. Create a formula in cell D13 to calculate the estimated annual sales.

8. Create a formula in cell C14 that determines whether you will make a profit or loss.

Think: You had a lemonade stand and you made $50. However, your expenses (i.e. Supplies, advertising) cost you $10. How much money did you really make?

9. Copy the formula into cell D14.

s

10. Type .2 in cell I1. This will represent a 20% projected increase.

11. Create a formula in cell F4 that calculates the new monthly rent amount when there’s an increase by the amount in cell I1. (You will be copying this formula to other cells, so remember to use an absolute reference.)

Note: The number in F4 must be greater than the number in C4.

If it is not, you probably only computed the increase amount without adding it to the original number. Therefore, extend the formula, and add the increase to the original amount.

12. Create a formula in G4 that calculates the new annual amount based on cell F4. {F4 has your monthly amount including the increase. Therefore F4 should be the only cell reference included in your formula.}

13. Copy the formulas in F4 and G4 to the remaining monthly and annual expense cells.

14. Use a function in F11 and G11 to total the increased expenses both monthly and annually.

15. Create a formula in cell G13 to calculate the annual sales.

16. Create formulas in cells F14 and G14 to calculate the monthly and annual profit/loss with the projected increase.

17. Insert a ROUND function in cell F10 to eliminate any decimals.

18. Type your name in cell A16 and the exercise name in cell A17.

19. Adjust column widths as needed.


21. Scale to fit to 1 page.


23. Print the worksheet a second time showing complete formulas and fitting on 1 page.


116


Excel 2010

REVIEW IT!


2. Open RV28_xx.

3. Save the file as RV28_xx.

4. In row 28, enter a function that calculates the average sales for each month- January through June.

5. In row 29, enter a function to display the sales total of the store that sold the most for that month.

6. In row 30, enter a function to display the sales total of the store that sold the least that month.

7. Use the AutoCalculate feature to check the formula results.

8. Adjust column widths, if necessary.

9. Type your name and the exercise name on the bottom of the worksheet.

10. Resave.



13. Change page orientation to landscape and scale to fit to 1 page


15. Print the worksheet a second time showing formulas and fitting on 1 page.


117

Exercise 29

IF FUNCTIONS

TERMS

Criteria: A value, text, or expression that defines the

type of cells you‟re looking for.

APPLICATION

1. What is the format of an IF function?

____________________________________________________________________________________________

2. What separates arguments from each other?

____________________________________________________________________________________________

3. What are the conditional operators?

____________________________________________________________________________________________

4. In an IF function, what must always be included in the first arguments (“criteria”).

____________________________________________________________________________________________

5. How do you use text in an IF function?

____________________________________________________________________________________________

NOTES

118


Excel 2010

PRACTICE IT!


2. Open XL29_xx.



5. Enter a column heading in cell E4: PASS/FAIL.

6. Insert an IF function in column E that assigns a PASS to any student with an average that is > or = to 65, and a FAIL to any student with an average below 65.

7. Enter a column heading in cell F4: CREDITS.

8. Insert an IF function in column F that assigns 3 credits to any student with an average that is > or = to 65, and a 0 credits to any student with an average below 65.


10. Center both the column headings and the column information for columns E & F.



13. Print the worksheet. Print a second time showing formulas.


119


Excel 2010

REVIEW IT!


2. Open RV29_xx.



5. Find COMM. for the first employee.

Commission is calculated by multiplying the sales by the commission rate.

6. Copy the formula to the remaining employees.

7. Insert an IF Function to find the bonus amount for those agents whose sales are $500,000 or more. They receive a .5% bonus of the sales. (The bonus is calculated as a percent of the sales; multiply the sales by the bonus percentage rate.) All others should show 0.

8. Copy the formula to the remaining employees.

9. Find TOTAL COMPENSATION (=COMM. + BONUS)

10. Format all money columns as currency with no decimal places.

11. Total all money columns. Format the totals as currency with no decimal places.



14. Change page orientation to landscape and scale to fit to 1 page.

15. Print the worksheet. Print a second time showing formulas. Make sure all formulas are completely visible.


120

Exercise 30

NESTED IF FUNCTIONS

TERMS

Nested Function: A function used as an argument

within another function.

APPLICATION

1. When do you nest an IF function?

____________________________________________________________________________________________

2. By which argument do you start the new nested IF function?

____________________________________________________________________________________________

3. How many times do you close parenthesis at the end?

____________________________________________________________________________________________

4. When doing a nested IF, which conditional operator is better to use < or >? Why?

____________________________________________________________________________________________

5. When doing a nested IF, can you switch conditional operators in the middle?

____________________________________________________________________________________________

6. For the last option, do you have to nest another IF?

____________________________________________________________________________________________

NOTES

121


Excel 2010

PRACTICE IT!


2. Open XL30_xx.



5. Enter a column heading in cell G4: GRADE.

6. Enter a column heading in cell H4: COMMENT.

7. Enter a column heading in cell I4: AWARD.

8. Insert a nested IF Function in column G that assigns a letter grade to each student based on her average. Use the diagram below to set up the IF Function.

9. Insert a nested IF Function in column H that assigns a comment to each student based on her average. Use the diagram below to set up the IF Function based on the students’ average.

10. Center all column headings and columns G and H.

11. Insert a nested IF Function in column I that assigns an award amount to each student. Use the diagram below to set up the IF Function based on the students’ average.

12. Apply the accounting format, two decimal places to column I.






Average Grade Comment Award

≥90 A EXC 100

80 - 89 B VG 75

70 – 79 C G 50

65 - 69 D ACC 25

<65 F NI 0

122


Excel 2010

REVIEW IT!


2. Open RV30_xx.



5. Make I6 the active cell.

Vantage Videos pays the annual health premiums for their employees. They pay $2,100 per year per employee for family coverage and $1,380 per year per employee for single coverage.

6. Calculate the cost of the health benefit for those employees who opted in to the plan.

Use a nested IF function that will result in $2,100, $1,380 or 0 depending on the contents of column D.

7. Copy the formula in I6 to the rest of the column.

8. Make J6 the active cell.

Vantage Videos pays the annual dental premiums for their employees. They pay $1,500 per year per employee for family coverage and $1,000 per year per employee for single coverage.

9. Calculate the cost of the dental benefit for those employees who opted in to the plan.

Use a nested IF function that will result in $1,500, $1,000 or 0 depending on the contents of column E.

10. Copy the formula in J6 to the rest of the column.

11. Apply the accounting format, two decimal places to column I & J.






123

Exercise 31

COUNTIF / COUNTIFS SUMIF / SUMIFS

TERMS

There are no new terms this exercise.

APPLICATION

1. The _______________ function answers the question HOW MUCH?

The _______________ function answers the question HOW MANY?

2. What is the format of a SUMIF function?

____________________________________________________________________________________________

3. How must the condition argument be formatted in a SUMIF and COUNTIF function?

____________________________________________________________________________________________

4. When doing a SUMIF, can you specify more than one condition for more than one range? What function

would you use?

____________________________________________________________________________________________

5. What is the format of a COUNTIF function?

____________________________________________________________________________________________

6. Can you specify more than one condition for a COUNTIF? What function would you use?

____________________________________________________________________________________________

7. How is the condition argument different between a SUMIF/COUNTIF and an IF function?

____________________________________________________________________________________________

NOTES

124


Excel 2010

PRACTICE IT!


2. Open XL31_xx.



5. In cell F20, type: Total Pass and in cell F21, type: Total Fail.

6. In cell G20, insert a COUNTIF Function to count the amount of students who passed this term.

7. In cell G21, insert a COUNTIF Function to count the amount of students who failed this term.

8. In cell I20, type: Total Awards Distributed.

9. Beneath that, in cells I21:I24, type A, B, C and D.

10. In cell J21, insert a SUMIF Function that displays the total amount of money distributed to students who received an A this term.

11. Use a similar formula for cells J22:J24 to display the amount distributed to students who received a B, C and D this term.

12. Format cells J21:J24 with accounting format, two decimal places.




16. Print the worksheet. Print a second time showing formulas. Make sure to display the complete formula for all formulas that were created in this exercise.


125


Excel 2010

REVIEW IT!


2. Open RV31_xx.


4. Put your name in D1 and exercise name in D2.

5. Create a recap of the daily sales (See illustration on bottom):

a. Create formulas that show the total number (each) of dogs, cats and fish sold that day.

b. Create a formula that shows the dollar amount in sales (each) for pets, feed and accessories. Format with the accounting format.

c. Display the sales totals for each salesperson. Format with accounting format.

6. Resave.




10. Print the worksheet. Print a second time showing formulas. Be sure to display the complete formula for all formulas that were used in this exercise.


Sales Recap

Amount Sold

Dog

Cat

Fish

Total Sales

Pets

Feed

Accessory

Total Sales

Alice Harper

Bob Cook

126

Exercise 32

CONDITIONAL FORMATTING

TERMS

Conditional formatting: Formatting applied to cells

when they meet the specified conditions.

APPLICATION

1. If you want to format all cells in a range based on their contents, which type of rule should you use? (3)

____________________________________________________________________________________________

2. Can a cell have a few rules applied at once?

____________________________________________________________________________________________

3. Where can you control the order of which rule is processed first?

____________________________________________________________________________________________

NOTES

127


Excel 2010

PRACTICE IT!


2. Open XL32_xx.



5. Create a conditional format in the Zip Code column so that entries greater than 46649 appear in light red fill with dark red text.

6. Adjust column widths to fit their data and headings.

7. Center the column headings.

8. Use conditional formatting so that entries for Year of Membership Expiration that are between 2011 and 2013 appear in a bold font, with a fill.

9. Use conditional formatting to create an orange data bar for column F.

10. Resave.






128


Excel 2010

REVIEW IT!


2. Open RV32_xx.



5. The owner of this Insurance Company is considering changing the discount plan for those customers with no claims or only one claim. Use conditional formatting to format the numbers that meet this criteria to be Dark Red and bold.

6. Create a new formatting rule to add a yellow fill color to the cells in the No. of Autos column for those policies that have more than two cars.

7. After reviewing your work, the owner decided that cells should be formatted for all policies with 2 or more cars. Edit the formatting rule to reflect this change.

8. Before presenting the final worksheet to the company owner you will classify the number of drivers using an icon set. Choose the Red to Black Icon Set (sixth from top).

9. Resave.


11. Spellcheck the worksheet.




129


Excel 2010

MASTER IT!


2. Open MS14_xx. Save the file as MS14_xx.

3. Put your name in C1 and exercise name in C2.

4. On the January Available worksheet, insert a column between columns H and I.

5. Type a column label Rental Bonus.

6. Enter formulas in column I that calculate the possible bonus to be paid if the apartment is rented.

a. A bonus of $100 is paid for renting a 1 BR apartment, $150 for a 2 BR apartment, and $225 for a 3 BR.

You’ll need to use a nested IF function to create the formulas. Also, be sure to put “1 BR” “2 BR” and “3 BR” in quotes, since they are text labels.

7. Format column I with Accounting format, two decimal places.

8. Type Recap in cell B26. Copy the format from cell B11.

9. Adjust the height of the row so it looks more like row 11.

10. In cell B27, type Total apartments:

11. In cell B28, type Total apartments Rented:

12. In cell E27, enter a formula that counts the total number of apartments in the listing.

13. In cell E28 enter a formula to calculate the number of apartments rented this month.

14. Calculate the bonuses:

a. In cell C30, type # of Units Rented.

b. In cell D30, type Bonus.

c. In cell B31, type Mark Brandon.

d. In cell B32, type Pam Grier.

e. In cell B33, type Tyrone Hill.

f. In cell B34, type Kate Harper.

g. Copy the format from cell B11 to the range C30:D30.

h. Adjust the height of the row.

i. In the range C31:C34, enter formulas that calculate the number of apartments rented by each associate.

To specify the rental agent’s name as a condition, you can either type the name in quotes or refer to the cell containing the name.

j. In the range D31:D34, enter formulas that calculate the bonuses to be paid.

k. Format the range D31:D34 with Accounting format, two decimal places.




130

Chapter 6 Notes…

Excel 2010

NOTES

Chapter 7

More Advanced Functions

Data Analysis

EX E R C I S E 33

Functions

PMT

FV

EX E R C I S E 34

Goal seek

EX E R C I S E 35

Vlookup

Hlookup

EX E R C I S E 36

Lock/unlock cells

Protect a range / worksheet /

workbook

EX E R C I S E 37

Formula auditing

Formula error checking


Goal seek Data / Data tools

Lock/unlock cells Home / Cells

Protect a worksheet Review / Changes

Exercise 33

PMT FUNCTIONS FV FUNCTIONS

132

TERMS

PMT function: A function that returns the amount

of your monthly payment for a loan or an

investment based on the interest rate and term for

the money being discussed.

FV function: A function that returns what the future

value of an investment would be based on constant

payments and interest rate.

APPLICATION

1. What is the format of a PMT function for a loan?

____________________________________________________________________________________________

2. What does NPER stand for? How does it work in the function?

____________________________________________________________________________________________

3. How do you put the rate into the function to show the monthly rate?

____________________________________________________________________________________________

4. Which argument must always be negative?

____________________________________________________________________________________________

5. When dealing with an investment, how do you know which function to use?

____________________________________________________________________________________________

6. How is the PMT function different when used for an investment instead of for a loan?

____________________________________________________________________________________________

NOTES

133


Excel 2010

PRACTICE IT!


2. Open XL33_xx.


4. Click on the loan sheet tab. Put your name in A14 and exercise name in A15.

5. In cell E5 enter a PMT function to calculate the monthly payment of a loan using the information in cells B5, C5, and D5. (amount should be $5,445.81)

i. Note: you are given the term in years; remember to change it to months when using it in the function.

6. Enter a similar PMT function in cells E6 and E7 using the cells in rows 6 and 7, respectively.

7. In cell F5 enter a formula that multiplies the monthly payment by the amount of months you will be repaying the loan to find the Total Payments.

i. Why is the total payment amount so much more than the loan amount?

What accounts for the difference?

8. Calculate the total payment amount in cells F6 and F7.

9. In cell E10, insert the PMT function to calculate the monthly payment towards an investment whose future value is given in cell B10. Use the information in cells B10, C10 and D10. (amount should be $171.99)

i. Note: you are given the term in years; remember to change it to months when using it in the function.

10. Enter a similar PMT function in cells E11 and E12 using the cells in rows 11 and 12, respectively.

11. In cell F10, calculate the total amount you will be investing in this account by multiplying the monthly payment by the amount of months you will be investing.

i. Why is the amount less than the future value amount?

What accounts for the difference?

12. Calculate the total amount invested in cells F11 and F12.


14. Change orientation to landscape.




134


Excel 2010

REVIEW IT!


2. Open RV33_xx.



The owner of real photography is interested in purchasing a new developer and would like to determine monthly payments on three different models.

5. Click in cell E5 enter a function to calculate the monthly payment of a loan using the information in cells B5, C5 and D5.

6. Enter a similar function in cells E6 and E7 using the cells in rows 6 and 7 respectively.

7. In cell F5, enter a formula to figure out how much your total payments will be after you finish paying up the loan.

8. Calculate the total payment amount in cells F6 and F7.

9. In cell G5 calculate the total interest you will be paying for the developer.

10. Calculate the total interest amount in cells G6 and G7.

The owner of Real Photography decided to save up money to purchase a new developer. He wants to compute how much can be earned by investing the money in an investment account that returns 9% annual interest. He determines that he can afford to invest $1200 for 3 years.

11. Click in cell B15 and use a function to calculate a future value of the investment using cells B12, B13, and B14.

12. The owner decides to determine the future return after two years. Click in cell C15 and insert a function using the values in cells C12, C13, and C14.





Exercise 34

135

GOAL SEEK

TERMS

Goal seek: A feature that can give us our target

number by changing one of the variable

dependents.

APPLICATION

1. What is goal seek?

____________________________________________________________________________________________

2. Do you have to select a specific cell before using goal seek?

____________________________________________________________________________________________

3. The cell that you are setting – what must it contain?

____________________________________________________________________________________________

4. Can you have a formula referring to empty cells?

____________________________________________________________________________________________

5. Does goal seek only work with the PMT function?

____________________________________________________________________________________________

NOTES

136


Excel 2010

PRACTICE IT!


2. Open XL34_xx.


You are assisting at Poppies, a florist shop. You are considering adding local delivery service to your business. You receive a quote to purchase a combination of vans, sedans and compact cars to deliver your orders. The quote uses one interest rate. You would like to use Goal Seek to find the interest rate you can afford.

Before you start, examine the spreadsheet. Find the columns with the information for the van, sedan and compact car respectively. Find the row with the amount of the current monthly payment. Find the cell with the current interest rate being used in all calculations.

4. Use Goal Seek to find the interest rate that will produce a monthly payment for the van purchase of $1400. Note: The current interest rate which you would like to change is located in cell B2 and the current monthly van payment that you would like set to $1,400 is in cell B11. Type the new interest rate into cell D19. Reset the interest rate in cell B2 to its original value (6.75%).

5. Use Goal Seek to find the interest rate that will produce a monthly payment for the sedan purchase of $990. Type the interest rate into cell D20. Reset the interest rate in cell B2 to its original value (6.75%).

6. Use Goal Seek to find the interest rate that will produce a monthly payment for the compact purchase of $790. Type the interest rate into cell D21. Reset the interest rate in cell B2 to its original value (6.75%).


8. Type your name and the exercise name on bottom of the worksheet.

9. Resave.

10. Make sure the data fits onto one page.



137


Excel 2010

REVIEW IT!


2. Open RV34_xx.


You are trying to determine the price you must charge for tickets to a performance so that you can ensure you’ve covered your expenses and made some profit to use as seed money for your next performance.

4. Compute the total expenses for the play in cell B12. You must first determine the cost for playbills (cell B9). Use the information on the worksheet to determine the cost of playbills and then compute the total of all the expenses.

5. Now that you know how much money is needed ($12,085), use the tables on the bottom of the worksheet and Goal Seek to answer the following questions (each table should be used for one question): Note: To answer each question, keep the following in mind. Your goal is to make $12,085.00 this performance. This can be done by changing either the amount of tickets sold or the price per ticket. The cell that you are setting must contain a formula even if it is referring to empty cells and your answer now is still zero.

a. What is the lowest price you can charge for a ticket if the play were a sellout? (Hint: changing cell will be ticket price.)

b. How many tickets would you have to sell if the ticket price were $9.00? Round the answer to zero decimal places.

c. What is the lowest ticket price you could charge if you had an 80% sellout?


7. Type your name and the exercise name on the bottom of the worksheet.

8. Resave.



11. Print the worksheet a second time showing formulas and fitting on 1 page.


Exercise 35

138

VLOOKUP FUNCTION HLOOKUP FUNCTION

TERMS

There are no new terms for this exercise.

APPLICATION

1. What‟s the difference between a VLOOKUP and HLOOKUP?

____________________________________________________________________________________________

2. What is the format of a VLOOKUP?

____________________________________________________________________________________________

3. If you are copying a VLOOKUP/HLOOKUP – what should you remember?

____________________________________________________________________________________________

4. For the lookup value argument – what do you use (what should not be used!!!)?

____________________________________________________________________________________________

5. How does the col_index_num argument work?

____________________________________________________________________________________________

6. What kind of table must you use in order for the function to work?

____________________________________________________________________________________________

7. If your table is not sorted, what can you do to make the function still work?

____________________________________________________________________________________________

NOTES

139


Excel 2010

PRACTICE IT!


2. Open XL35_xx.


4. Group the student_list and giftcard_list worksheets.

5. Put your name and workbook name in the header.

6. UNGROUP the worksheets.

Your school is having a major campaign to raise money for a new building. Any girl who brings in more than $500 will get a gift card to the store of her choice. The amount of the gift card is decided according to her grade level.

7. Click on the giftcard_list worksheet. Here is a list of the girls who raised more than $500. Now you need to add their grade and address so that you can give them their correct gift cards.

8. In cell D3 enter a Vlookup Function to lookup the girl’s class number from the Student_list worksheet.

9. Copy to the rest of the girls in the list.

10. Before creating any further lookup functions, you decide to name your range on the Student_list worksheet. {The range, is the cells that you use as your table in the lookup functions.} Select the correct cells on the Student_list worksheet [do not include the column labels] and name it master_student_table

11. In cell E3 enter a Vlookup Function to lookup the girl’s address. Use the named range as your table_array function..

12. In cell F3 enter a Vlookup Function to lookup the girl’s city.

13. In cell G3 enter a Vlookup Function to lookup the girl’s zip.

14. Copy all the functions that you created to all the rest of the girls on the giftcard_list worksheet

15. Save.

16. Print the giftcard_list worksheet. Print a second time showing formulas – make sure to display complete formulas.


140


Excel 2010

REVIEW IT!


2. Open RV35_xx.


4. Name the postage rates range, C23:H25, RATES.

5. In cell D7, use the Hlookup Function to find the TAX RATE based on the zones. Use the named range for table_array.

6. Copy the function to the rest of the column.

7. Compute the SALES TAX in column E (price * tax rate). Format with the accounting format.

8. In cell F7, use the Hlookup Function to find the POSTAGE based on the zones. Use the named range for table_array.

9. Copy the function to the rest of the column. Format with the accounting format.

10. Compute TOTAL SALE in column G (add price, sales tax and postage). Format with the accounting format. Copy the formula to the rest of the column.


12. Type your name and exercise name on the bottom of the worksheet.

13. Resave.


15. Print the worksheet. Print a second time showing formulas.


excel 2010 contents - achievementses.com...view the cell reference in the name box. 3. click cell h9...

Documents