Return to the Excel 2007 web page

Lesson 4: Using Advanced Formatting and Analysis Tools

2

Working with Grouped Worksheets Work simultaneously on all worksheets in

the group Enter data in cells Format selected cells Copy and paste

The grouped sheet tabs turn white

3

Ungrouping Worksheets Remember to ungroup when ready to

enter variable data

Ungroup using context menu

Or click a sheet tab not in the group

4

Consolidating Worksheet Data Combine values from source worksheets

into a destination sheet

Destination worksheet

Reference list

Use Point Mode to select reference ranges in each worksheet to be consolidated

The summary worksheet does not automatically update when source data is changed.

5

Consolidation Functions SUM AVERAGE MIN MAX

The SUM function is used most often.

6

Creating Links to Source Data in a Consolidation

Displays an outline with source data rows above summary row Changes to source data do update in the

summary worksheet

7

Types of Consolidation By Position

References same range in multiple worksheet

Works well with identical layouts

By Category References cells by row or

column Works well with different

layouts

8

Working with Data Validation Restrict data entry in cells

Types of values Minimum and maximum values

Create input messages and error alert messages to customize the validation

9

Locating All Invalid Data Red circles indicate a potential problem

Ignore or correct problem

Red circles are temporary and do not print

10

Working with Conditional Formatting

Formatting applied only if cell contents meet set conditions Visual alert that a value is outside

parameters Choices on the Ribbon

Presets: Frequently used formats

Custom rules you create

Data bars, color scale, icon sets

11

Creating a Custom Conditional Formatting Rule

Conditional formatting is used as a visual alert to indicate that a value is outside the parameters.

12

Data Tables – One Variable Helpful in what-if analyses Values from data table substituted for a

cell reference in a formula Formula =FV(B3/12, B4,–B5) is the basis

for the data table calculations shown

Value of 0 is returned in the cell with the formula

Column of payment values that you input

Formula results for each payment value

Each payment value in the data table is substituted for cell B5 in the formula

13

Data Tables – Two Variables The same formula =FV(B3/12, B4,–B5) with

variables substituted for two cell references A result displays for every combination of

rate and payment listed in the table

The input cells are blank because multiple values are substituted from the row and column.

Row of interest rates you input

Column of payment values you input

Formula results for each combination of rate and payment

14

Creating Trendlines on Charts Linear trendline

Best-fit straight line that shows data trend

15

Creating Trendlines on Charts Polygonal trendline

Smooth out fluctuations by averaging adjacent data points

Increase the Order option value to make a smoother trendline.

Return to the Excel 2007 web page

Lesson 4: Using Advanced Formatting and Analysis Tools