Session 14 · Phase 2: Excel

Excel Charts, Conditional Formatting & What-If Analysis

Turn numbers into visuals, highlight what matters, and model "what if" scenarios — the skills that make analysis persuasive and decision-ready.

⏱ ~2 hrs 📚 Core content 🎯 High priority

Learning Objectives

Choose the right chart type for comparison, trend, part-to-whole, and relationship.
Use Conditional Formatting to highlight values, outliers, and trends.
Use Goal Seek to find the input that hits a target result.
Use Data Tables to see how one/two variables affect a formula.
Use Scenario Manager to compare multiple input sets (best/worst/base).

1. Charts — Choosing the Right One

The right chart makes the insight obvious; the wrong one hides it. Match the chart to the question.

Chart	Best for	Example
Column / Bar	comparing categories	sales by region
Line	trend over time	monthly revenue
Pie / Doughnut	part-to-whole (one series)	market share
Scatter	relationship between two numbers	price vs units sold
Area	cumulative trend / magnitude	cumulative sales
Combo	two different scales together	sales + profit %

💡

Pro Tip: "Trend over time → line chart" and "compare categories → bar/column chart" are the two answers interviewers most want to hear. Avoid pie charts for more than ~5 categories.

2. Conditional Formatting

Conditional Formatting applies visual rules to cells automatically — colors, data bars, icons — based on their values. It makes patterns and outliers jump out without manual formatting.

Common rule types

Highlight Cell Rules — greater than, less than, between, duplicates.
Top/Bottom Rules — top 10 items, above/below average.
Data Bars — a mini in-cell bar showing relative size.
Color Scales — gradient from low to high.
Icon Sets — arrows/flags for quick status.

🌐

Real World: "Highlight every region where sales dropped below target in red" is a 10-second conditional-formatting rule — and exactly the kind of request a stakeholder makes.

3. What-If Analysis — an Overview

What-If tools let you test how changing inputs affects a result, without rewriting formulas. Excel has three, each solving a different problem.

Tool	Question it answers
Goal Seek	"What input do I need to hit this target?"
Data Table	"How does the result change across a range of inputs?"
Scenario Manager	"How do several inputs compare together (best/worst/base)?"

4. Goal Seek

Goal Seek works backwards: you tell Excel the result you want, and it finds the one input that produces it.

Data → What-If Analysis → Goal Seek
  Set cell:     the formula cell (e.g. profit)
  To value:     your target (e.g. 5000)
  By changing:  the input cell (e.g. price)

📝

Example: "How many units must I sell to reach ₹50,000 profit?" Set the profit cell to 50000, and change the units cell — Excel solves it. Note: Goal Seek changes one input at a time.

5. Data Tables

A Data Table shows how a formula changes across a range of one or two input values — like a mini sensitivity grid.

Data → What-If Analysis → Data Table
  Row input cell:    (for a two-variable table)
  Column input cell: the variable you're testing

Use it to answer: "What would profit be at 100, 200, 300… units?" in one grid.

6. Scenario Manager

Scenario Manager compares multiple sets of inputs — a "best case", "base case", and "worst case" — and summarizes them side by side.

Data → What-If Analysis → Scenario Manager → Add Scenario

💡

The key difference to remember: Goal Seek = one target, one input. Data Table = a range of one/two inputs. Scenario Manager = several complete input sets compared together.

7. Sparklines

Sparklines are tiny charts inside a single cell — a quick visual of a row's trend without a full chart. Great for dense dashboards and tables.

📋 Stable content — Reviewed: August 2026

8. Interview Questions (with Model Answers)

Charts, conditional formatting, and what-if questions. Self-test before revealing.

IQ1. Which chart would you use to show a trend over time?

Model answer: "A line chart — it shows change over time most clearly. For comparing categories I'd use a bar/column chart, and for part-to-whole a pie chart."

IQ2. What is Conditional Formatting, and how do you use it?

Model answer: "It applies visual rules to cells automatically based on their values — like highlighting values above a threshold, top 10 items, or duplicates. I use it to surface outliers and patterns without manually coloring cells."

IQ3. How do you highlight outliers or top values using Conditional Formatting?

Model answer: "I use Top/Bottom Rules for the top N or above-average values, Highlight Cell Rules for a threshold like 'greater than 100', and Data Bars or Color Scales to visualize relative size."

IQ4. What is Goal Seek, and when would you use it?

Model answer: "Goal Seek works backwards — I set a formula cell to a target value and Excel finds the single input that produces it. For example, how many units to sell to reach a ₹50,000 profit."

IQ5. What's the difference between Goal Seek and Scenario Manager?

Model answer: "Goal Seek solves for one target using one input. Scenario Manager compares multiple complete sets of inputs — like best, base, and worst case — side by side."

IQ6. What is a Data Table in What-If analysis?

Model answer: "A Data Table shows how a formula changes across a range of one or two input values — a sensitivity grid. I'd use it to see profit at 100, 200, 300 units in a single table."

IQ7. What's the difference between a Data Table and Scenario Manager?

Model answer: "A Data Table varies one or two inputs across a continuous range. Scenario Manager compares a handful of named, complete scenarios — each with multiple inputs set together."

IQ8. How do you decide between a bar chart and a line chart?

Model answer: "Bar/column for comparing distinct categories; line for a continuous trend, usually over time. If the x-axis is time, line is usually the better choice."

IQ9. What are Sparklines, and when would you use them?

Model answer: "Sparklines are tiny charts inside a single cell that show a row's trend at a glance. I use them in dense tables and dashboards where a full chart would be too large."

Hands-On Project: Visualize and Model

Using a simple sales dataset (region, month, sales, target, units, price), complete the following.

Steps

Create a line chart showing sales by month.
Create a bar chart comparing total sales by region.
Apply conditional formatting to highlight any month where sales fell below target.
Use Goal Seek to find the price that produces a target profit of ₹50,000.
Create a Data Table showing profit across a range of units sold.

View Solution / Walkthrough

Select month + sales → Insert → Line Chart.
Select region + total sales → Insert → Bar Chart.
Select the sales column → Home → Conditional Formatting → Highlight Cell Rules → Less Than → point to the target cell.
Data → What-If Analysis → Goal Seek → Set profit cell = 50000, By changing price cell.
Set up a formula for profit → Data → What-If Analysis → Data Table → column input = units.

Together these mirror the daily "visualize + highlight + model" cycle of an analyst.

Key Takeaways

Line = trend over time; bar = compare categories; pie = part-to-whole.

Conditional Formatting surfaces outliers and patterns automatically.

Goal Seek finds the input for a target result (one input, one target).

Data Tables vary one/two inputs; Scenario Manager compares full input sets.

Sparklines pack a mini trend chart into a single cell.

Learning Objectives

1. Charts — Choosing the Right One

2. Conditional Formatting

Common rule types

3. What-If Analysis — an Overview

4. Goal Seek

5. Data Tables

6. Scenario Manager

7. Sparklines

8. Interview Questions (with Model Answers)

Hands-On Project: Visualize and Model

Steps

Key Takeaways

Objective Questions — Test Your Understanding