Free Excel Course Part 2 — Functions | Goal Seek, Data Validation, Scenario Manager, INDIRECT & More

By Subham Ghadge  |  Subu Data Labs  |  Excel Training & Free Workbooks  |  July 2026

Building upon Part 1—which focused on navigation shortcuts, formatting standards, and date math—this second module introduces intermediate Excel functions used extensively in corporate analysis, financial planning, and operational modeling.

Functions like Goal Seek, Scenario Manager, and INDIRECT allow you to automate what-if analysis, build dynamic summary dashboards, and enforce strict data entry validation rules. This guide walks through 7 essential Excel features with step-by-step instructions, formula syntax references, and practical exercise scenarios included in the downloadable practice workbook.

---

Curriculum Overview — All 7 Topics

---

Standard formulas calculate an output based on known inputs. Goal Seek performs reverse calculations: you specify a target output value, and Excel calculates the exact input variable needed to achieve that target.

Common Use Cases

• Determining required conversion rates to achieve sales lead targets.
• Calculating maximum allowable cost-per-click (CPC) rates to maintain campaign profitability.
• Finding required sales volumes necessary to reach breakeven points.
• Calculating required academic grades or scores needed to hit a target cumulative GPA.

Execution Workflow

Example 1 — Marketing Lead Target Analysis

A marketing team tracks lead generation efficiency across 4 channels, targeting an average of 2.85 leads per $100 spent. Calculate the conversion rate required for the Affiliate channel:

Channel       Cost/Click   Conversion Rate   Leads per $100 Spent
Facebook      $1.00        3.0%              3.00
Affiliate     $0.50        [Variable]        → Target: 2.85
YouTube       $1.50        4.0%              2.67
Pinterest     $0.70        2.0%              2.85

Goal Seek Result: The Affiliate channel requires a 1.425% conversion rate to achieve the target of 2.85 leads per $100 spent.

Example 2 — Academic GPA Target

Calculate the 5th-semester GPA needed to achieve a cumulative 3.75 GPA target:

---

Data Validation restricts cell inputs to predefined rules, preventing typos, invalid entries, and formatting inconsistencies in shared workbooks.

Applications

• Creating dropdown lists for selecting pre-approved categories.
• Restricting numeric inputs to specific integer bounds (e.g., ratings between 1 and 10).
• Enforcing valid date ranges for transaction logging.
• Preventing duplicate text entries in master registration lists.

Creating a Dropdown Picklist

1. List the allowed entries in a master reference table.
2. Select the target cells where the dropdown should appear.
3. Go to Data tab → click Data Validation.
4. Under Settings, set Allow to List.
5. In Source, select the master reference range.
6. Click OK to activate the cell dropdown arrow.

Master Account Categories Reference List:

Entertainment            Printing & Stationery
Insurance Premium        Travelling Allowance
Miscellaneous Expenses   Repair & Maintenance
Air Conditioner          License Renewal Fee
Business Development     Loan from Directors
Sales Revenue            Purchase
Advertisement            Furniture & Fixture

Validated Transaction Ledger View:

Configuring Numeric Input Bounds (Range 0 to 10)

1. Select input cells.
2. Go to Data Validation → set Allow to Whole Number.
3. Set Minimum = 0 and Maximum = 10.
4. Configure custom Error Alert: "Input Error: Please enter a whole number between 0 and 10."
5. Click OK. Excel blocks invalid entries automatically.

---

Scenario Manager allows you to create, save, and switch between different sets of input variables (e.g., Best Case, Base Case, Worst Case) and generate automated side-by-side summary reports.

Workflow Steps

Example — Equipment Financing Comparison Summary

Excel generates an automated summary worksheet displaying all defined scenarios and their output metrics side-by-side.

---

Name Manager allows you to assign descriptive labels to individual cells or data ranges. Referencing named ranges (e.g., =SUM(Quarterly_Revenue) instead of =SUM(D12:D20)) improves formula readability and auditing.

Creating Named Ranges

1. Go to Formulas tab → click Name Manager.
2. Click New.
3. Enter a descriptive name (names must not contain spaces; use underscores like Total_Revenue).
4. In Refers To, select the target cell or range.
5. Click OK.

Example — Quarterly Sales Consolidation

Master Data Table:

Formula Comparison:

Standard Reference Formula:  =SUM(D29, E29, F29)
Named Range Formula:        =SUM(January_Total, February_Total, March_Total)

---

Advanced Filter extracts data rows matching complex AND/OR criteria and copies filtered results to a separate destination without altering the raw data table.

Execution Workflow

1. Create a Criteria Block above or beside your dataset using exact column headers.
2. Enter condition thresholds below headers (e.g., >100 under "Total_Volume_Kg").
3. Select main data table → go to Data tab → click Advanced (Filter section).
4. Set List Range (data table) and Criteria Range (criteria block).
5. Select "Copy to another location" and specify target destination cells.
6. Click OK to extract matching rows.

Example — High-Volume Sales Extraction

Criteria Block Setup:

Total_Volume_Kg
>100

Extracted Output Table (Volume > 100 Kg):

---