Using Advanced Formatting and Analysis Tools

2

Working with Grouped Worksheets: Grouping Worksheets

Data is entered simultaneously on all worksheets in the group

The “Grouped” sheet tabs turn white

The word “Group” displays in the title bar

3

Working with Grouped Worksheets: Ungrouping Worksheets

Remember to “ungroup” when ready to enter variable data

Ungroup using pop-up menu

Use sheet tab not in group

4

Working with Grouped Worksheets: Fill Across Worksheets

Copy existing data to other worksheets in the group

5

Consolidating Worksheet Data Combine values from source worksheets

into a destination sheet

Source 1

Source 2

Source 3

Destination

6

Consolidating Worksheet Data: Consolidation Functions

Consolidation Functions SUM AVERAGE MIN MAX

The SUM function is most commonly used

7

Consolidating Worksheet Data: Types of Consolidation

3-D referencing References same cell in

multiple worksheets

By position References same range in

multiple worksheets Works well with identical

layouts

By category References cells by row and

column Works well with different

layouts

Choose function

Reference list

Enter the reference or click to use Point Mode

8

Working with Data Validation Restrict data entry in cells

Type Range

Create input messages and error alert messagesto customize the validation

9

Using Natural Language Formulas Use row and column headings instead of cell

references to create formulas Type formulas exactly as they are written in the worksheet

Natural language formulas can be unpredictable with functions

10

Working with Conditional Formatting

Applies specific formatting depending on set conditions

Option

Comparison operator

Format

Preview

Value

Add additional conditions

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

11

Using Analysis Tools: Data Tables Data table created from a formula with two

variables

The input cells areblank so Excel can input multiple values from the rows and columns they represent into the formula

12

Using Analysis Tools: Scenario Manager

Scenario Named combination of

values assigned to variables in a what-if model

Manage Scenarios Save and compare multiple

scenarios

Add Scenarios Add additional what-if

models

Using Advanced Formatting and Analysis Tools