Naming Range, Sorting & Filtering, Freeze Panes and What-If Analysis

Lesson Plan: Unit - 02 Microsoft Office

Subject: BELE2CFA: Computer Fundamentals And Applications
Topic of Study: Naming Range, Sorting & Filtering, Freeze Panes and What-If Analysis
Grade/Level: Bachelor of SCIENCE

Objective: To demonstrate and explain create / modify steps of Naming Range, Sorting & Filtering, Freeze Panes and What-If Analysis (Goal Seek)

Time Allotment: 60 Minutes

19.0 Naming Range

To select the rows or columns - we need to select every time.
To remove such a step - we can use naming range for that kind of operations.
To Select or manage the selected range as per user selection with its different name is called naming range.
How to use Naming range:

Step 01: Select the rows or columns as per need. (For Example: Roll no. column)

Step 02: Click on Formula > Define name > Define name.

Step 03: It will display dialog, write comment (optional) and Click OK.

Note: To select roll no. whenever you need - you just click on selected address box and select Roll no naming range.

Note: To delete naming range - use Formula > Name Manager, select the naming range name & click on delete button.

19.1 Sorting & Filtering

Sorting

A sorting means an arrangement.
An arrangement may arrange in lowest to highest, highest to lowest, alphabetic order or customer order.
For example - sorting in alphabetic order.

Step 01: Select the column name. (for Example: Name)

Step 02: Click on Home > Sort & Filter > A to Z or Z to A.

A to Z - arrange the name in ascending order with A....Z
Z to A - arrange the name in descending order with Z....A

Step 03: It will display sorting warning dialog box and you have select any one option from them.

Step 03.01 - if option one is selected then output like this. (it will affect all the rows & columns)

Step 03.02 - if option two is selected then output like this. (it will affect only the selected column)

Filtering:

Filtering means to filter important information from selected data.
Filtering can be useful to find...

Top 10 records.
Above average
Below average
<, <=, >, >=, = and between value

How to apply filter on data.

Step 01: Select the columns where you want to apply filter.

Step 02: Click on Click on Home > Sort & Filter > Filter.

Step 03: Applied filter column look like this.

Step 03.01: Click on Roll no. Drop Down Arrow. it will look like this.

Step 03.02: Select the appropriate option and see the output. (for Example: Equals option)
Step 03.03: It will display one dialog box, input the value in Second box. (for Example: 9) and see the output, also.

Step 04: to clear filter from roll no, click on roll no and select clear option.

19.2 Freeze Panes

Freeze Panes means an area which can't move while scrolling data in horizontally or vertically.
There are main three options.

Freeze top row.
Freeze first columns
Freeze panes - custom option

19.3 What-If Analysis

As per name suggest - it will used to analysis with if condition or situation.
What if Analysis is special features which covers three options:

Scenario Manager

It is manager which manage and compare different given scenario.

Goal Seek

It is used to calculate in reverse manner.

Data Table

It is used to checking sensitivity analysis purpose.

19.3.2 Goal Seek

Goal seek means how to achieve a goal based on reverse calculations.
For Example:

Calculate CGST = 10000*9% = 900 or in excel =B1*B2
Calculate SGST = 10000*9% = 900 or in excel =B1*B3
Calculate Total = 10000 + CGST + SGST = 10000+900+900 or in excel =B1+B1*B2 + B1*B3