What if Analysis,

Charting, and Working

with Large Worksheets

Chapter 3

What we will cover

Rotating Text

Using the fill handle to create a

series of month names

Copying and pasting

Inserting, and deleting cells

Formatting numbers using format

symbols

Entering and formatting the

system date

Use absolute and mixed cell

references

What we will cover

Use the IF function

Create a spark line chart

Change spark line chart type

Use format painter to copy a

format

Create a cluster chart on another

sheet

What we will cover

Use chart filters to show a subset of

data in a chart

Change the chart type and style

Reorder sheet tabs

Freeze and unfreeze rows and

columns

What we will cover

Do sensitivity analysis (what if

analysis)

Use goal seek to answer what if

questions

Use Smart Lookup Insight

Find and fix accessibility issues

What we will cover

Splitting the screen

Using absolute cell references

Using the IF function

Using the format painter to copy

formats

What we will Cover

What we will Cover

Creating a clustered column chart on a

separate chart sheet

Reusing worksheet tables

Answering what-if questions

Using goal seek

Autofilling with the Fill Handle

Dragging to the right can be used to

create a series of

– Names of the days of the week

– Numbers

– Names of the months of the year

– Names of the days of the week as

follows:• Monday, Tuesday, Wednesday, Thursday, and so

on

– Numbers as follows:• 2, 2, 2, 2, 2, etc., OR 6, 7, 8, 9 and so on

• Names of the months of the year as

follows:• September, October, November, and so on

Other Series

1:00:2:00, 3:00, 4:00, 5:00 …

1st:2nd, 3rd, 4th, 5th …

2005:2006, 2007, 2008, 2009…

Mon, Tue, Wed, Thu, Fri, Sat…

Step 1, Step 2, Step 3, Step 4…

Starting Autofill with Two Cells Selected

1-Jan 1-Feb

Will result in 1-Jan,1-Feb,1-Mar,1-Apr

2 4

Will result in 2, 4, 6, 8, 10 …

-10 -12

Will result in -10, -12, -14, -16

Autofill Options Button

Fill Series with formatting (default)

– Fill the destination area with a series using the format of the

source area

Fill Formatting Only

– Fill destination area using the format of source area. No content

is copied unless the fill is a series.

Fill Without Formatting

– Fill the destination area with contents, without the formatting of

the source area.

Fill Months

– Fill the destination area with a series of months using the format

of the source area. This shows as an option only if source area

contains a month.

Cut, Copy, Paste

Cutting removes the cell contents and places

them on the clipboard

Pasting involves moving the cell contents from

the clipboard to a cell

Copying does just what it sounds like it would

do. It copies the cell contents to the clipboard

and leaves the original in the source cell(s). You

can then paste them to another cell or cells.

Paste Options

Keep Source Formatting (default) - Copy

the contents and the format of the source

area.

Match Destination Formatting--Copy the

contents of the source area, but not the

format.

Values and Number Formatting – Copy

contents and format of the source area for

numbers or formulas, but use the format of

the destination area for text.

Paste Options

Keep Source Column Widths – Copy the contents

and the format of the source area. Change the

destination column widths so that they are the

same as the source column widths.

Formatting Only - Copy format of source area,

but not the contents.

Inserting Cells, Rows and Columns

Avoid adding or deleting individual cells because it will

affect the rows and columns around them. Use the clear

command instead to remove cell content, not the entire

cell.

Adding rows is generally not a problem because cell

references in the moved rows adjust

Adding columns is generally not a problem because cell

references in the moved columns adjust

Be aware of any cells elsewhere in the worksheet that

reference cells in a deleted column, row, or cell. They

will now show error messages (because they are referring

to something that is no longer there).

Review of Relative Cell Addressing Relationships

A B C D E F G

1 2 2 2

2 3 1 6

3 3 4 5

4 8

5

6

7

=SUM(B1:B3)

Excel only knows that you want to add the three cells directly above the cell

containing the sum function. The relative position of these cells is that they

are the three cells directly above.

Relative Cell Addresses

Cell addresses without dollar signs ($) will

adjust when copied or moved. You have

used these before.

Example:

C33

Column Letter

To prevent a column letter from changing,

type a dollar sign ($) before the letter.

Example:

$C33

Row Number

To prevent a row number from changing,

type a dollar sign ($) before the number.

Example:

C$33

Column and Row

To prevent a column letter and a row

number from changing, type a dollar sign

($) before the column letter and the row

number.

Example:

$C$33

Absolute Cell Addressing

Formula in B4 is =B3*$F$1

A B C D E F G

1 Tax rate .07

2

3 Price 10.00

4 Tax 00.70

5 Total 10.70

6

7

Copied Formula in B6 is =B5*$F$1

A B C D E F G

1 Tax rate .07

2

3 Price 05.00

4 price 05.00

5 subtotal 10.00

6 Tax 00.70

7 total 10.70

Excel Functions

Functions that merely use a list as the

arguments

Functions that have several individual

arguments separated by commas

Functions that use Lists as arguments

They merely require a list of cells after the

name of the function.

Tell Excel the first and last cell address of

the cells in the list

Example:

=sum(A1:A5)

Functions that use individual arguments

Arguments are pieces of information that

Excel needs to perform an operation for

you =IF(ARGUMENT1, ARGUMENT2, ARGUMENT3)

Arguments go inside parentheses

Arguments are separated by commas

Arguments follow the name of the function

IF FUNCTION Starts with an equal sign (=)

The name of the function is IF

Argument 1 is the condition to be met

Argument 2 is what to do if true (condition is met)

Argument 3 is what to do if false(condition not met)

Arguments 2 and 3

These can be:

– Cell references

– Text strings inside double quotes

– Formulas using cell addresses

– Formulas using numbers

– Formulas using cell addresses and numbers

Location of If Function

Place the if function in the cell where you

want the result to appear

IF function using text strings

=IF(A1>=3.00, ”Buy a Big Mac”, ”Eat at home”)

IF function using cell references

=IF(A1>=3.00,A2,A3)

A B C

1 3.50 If statement here

2 Buy a Big Mac

3 Eat at home

Logical Operators used in an IF function

Operator Meaning

of Operator

Example Means

= Equal to A3=0 A3 is equal to 0

< Less than A3<10 A3 is less than 10

> Greater than A3>10 A3 is more than 10

>= Greater than or equal to A3>=10 A3 is 10 or more

<= Less than or equal to A3<=10 A3 is 10 or less

<> Not equal to A3<>10 A3 is not equal to 10

More on Spell checking

Excel checks selected worksheets

All cell values

Cell comments

Embedded charts

Text boxes

Headers and footers

To limit checking, you can select a range of cells

first—Excel will only spell check those cells

Goal Seeking

Personnel January Bonus

Gustav Blau $7,000 $700

Bonus % 10%

If you wish to increase the amount of the bonus to a specific

amount, but the bonus is dependent on the bonus percent,

you will have to change the bonus percent.

To calculate the correct percent to use in order to make the

bonus itself come out to the exact amount you want, you

can use goal seek.

Example: You want Blau’s bonus to be $1,000. You can

use goal seek to tell Excel to figure out what the bonus

percent should be to result in a $1,000 bonus for Blau. It

will calculate that the percent should be 14.28.

Split Screensplit

Reordering Sheet Tabs

You can change the order of the

worksheets in your workbook by changing

the order of their sheet tabs

Drag the tabs to where you want them

Rotating Text in a Cell

Saves Space

Sparkline Chart

(chart in a cell)

Do-It-Yourself Number Style Formatting

Number styles that you can either format

yourself or format using the number group:

Percent 5.5%*

Accounting or Currency $1,000.00

Comma 1,000.00

* Excel treats this as the decimal equivalent,

.055

Freezing Rows and Columns

Causes row(s) to remain in sight at the tops of

columns when working farther down the

worksheet

Also used to cause column(s) to remain in sight

at the left when the user is working far to the

right of the worksheet

Before using Freezing

Freezing Rows 1 and 2 at the top and Freezing Column A at the Left

Chart before Filtering

Chart After Filtering