Part V: Data Visualization

Charts — Turning Data into Visual Stories

Learning Objectives

• Understand the purpose, strengths, and limitations of each major chart type
• Create and customize Column, Bar, Pie, Doughnut, Line, Area, Scatter, and Combo charts
• Add and format chart elements: titles, axis labels, legends, data labels, gridlines, trendlines
• Move, resize, and place charts on chart sheets
• Use Recommended Charts and create dynamic charts with Excel tables
• Choose the right chart type for any given data scenario

Chart Type Selection Guide

Before diving into each chart type, here is the golden rule: your data determines the chart, not the other way around. The table below maps data scenarios to the best chart type:

Column Charts

Column charts are the workhorses of data visualization. They use vertical bars to compare values across categories. Excel offers three main variants:

1. Clustered Column Chart

Bars for each data series are placed side by side. Best for comparing values across categories and series.

2. Stacked Column Chart

Bars are stacked on top of each other. Shows the total value of each category while also displaying the contribution of each series. Use when the total matters as much as individual parts.

3. 100% Stacked Column Chart

Every bar extends to 100%. Shows the percentage contribution of each series to the total. Best for comparing proportions when absolute values differ significantly.

Bar Charts

Bar charts are simply horizontal column charts. They work best when category names are long (e.g., Indian state names, department titles) or when you have many categories (10+). The horizontal layout gives text labels room to breathe.

Pie & Doughnut Charts

Pie Chart — Parts of a Whole

A pie chart divides a circle into slices, where each slice represents a proportion of the total. Use it to show percentage distribution when you have 2–6 categories. Never use a pie chart with more than 6 categories — the slices become too thin to distinguish.

Doughnut Chart — Multi-Series Pie Alternative

A doughnut chart is a pie chart with a hole in the middle. Its major advantage: you can plot multiple data series as concentric rings, which a pie chart cannot do.

Line & Area Charts

Line Chart — Trends Over Time

Line charts connect data points with straight lines to show trends and changes over continuous time periods. They are the go-to chart for stock prices, temperature, monthly sales, and any time-series data.

Area Chart — Cumulative Trends

Area charts are line charts with the area below the line filled with colour. They emphasize the magnitude of change over time and are excellent for showing cumulative or stacked values.

Scatter Plot (XY) & Combo Charts

Scatter Plot — Relationship Between Two Variables

Scatter plots (XY charts) display data points on a two-dimensional grid to reveal correlations and patterns between two numerical variables. Unlike line charts, scatter plots do NOT connect points in order — each dot is independent.

Combo Chart — Best of Both Worlds

Combo charts combine two chart types (typically column + line) on the same chart. They are perfect when you need to show values on one axis and percentages/rates on a secondary axis.

Chart Elements — Anatomy of a Chart

Every well-designed chart includes specific elements that make it informative and professional. Here's a complete breakdown:

The Chart Elements Button (+)

When you click a chart, a green + icon appears at the top-right corner. This is the quickest way to toggle chart elements on/off. Simply check or uncheck elements like Title, Legend, Data Labels, etc.

Trendlines — Types and When to Use

Chart Formatting, Layout & Advanced Features

Changing Chart Colours and Styles

1. Chart Styles Gallery: Click chart → Chart Design tab → Styles group → browse 10+ predefined styles
2. Change Colors: Chart Design → Change Colors → choose from monochromatic or colourful palettes
3. Individual Series Formatting: Right-click a data series → Format Data Series → Fill → choose Solid fill, Gradient fill, or Pattern fill
4. Theme Matching: Page Layout → Themes → all charts automatically adopt the new theme colours

3D Charts — Use With Caution

Excel offers 3D versions of Column, Bar, Pie, and Line charts. While they look visually appealing, they introduce perspective distortion that makes accurate value reading difficult. Use 3D charts only for presentations where visual impact matters more than precision.

Moving and Resizing Charts

• Move on same sheet: Click chart border → drag to new position
• Resize: Drag corner handles (hold Shift to maintain aspect ratio)
• Move to chart sheet: Right-click chart → Move Chart → select "New sheet" → name it → OK
• Move to another worksheet: Right-click chart → Move Chart → select "Object in" → choose target sheet

Recommended Charts Feature

Excel's AI-powered Recommended Charts feature analyzes your selected data and suggests the most appropriate chart types. Access it via: Insert → Recommended Charts. Excel shows thumbnails of suggested charts on the left; click any to preview.

Dynamic Charts with Excel Tables

When your chart source data is formatted as an Excel Table (Ctrl + T), the chart automatically expands when you add new rows of data. This creates a dynamic chart without any extra setup.

Solved Examples — Charts

Example 1: Flipkart Category Sales — Clustered Column

Task: Create a Clustered Column chart comparing sales across quarters for each category.

Solution: Select A1:E4 → Insert → Clustered Column. Each category gets a group of 4 bars. Q4 bars dominate due to festive season sales (Diwali, Big Billion Days). Add chart title "Flipkart Category Sales FY 2024".

Example 2: Indian Budget Allocation — Pie Chart

Task: Show the proportion of budget allocated to each ministry.

Solution: Select A1:B7 → Insert → Pie Chart → 2D Pie. Add data labels with percentages. Explode the "Infrastructure" slice by clicking it and dragging outward to highlight it as the largest allocation.

Example 3: CBSE Pass Percentage — Line Chart

Task: Show the trend of CBSE pass percentages for Class X and XII from 2019 to 2024.

Solution: Select A1:C7 → Insert → Line with Markers. Two lines appear — Class X above Class XII. The 2021 spike (internal assessment year) is immediately visible. Add a note using a text box near the 2021 peak.

Example 4: Study Hours vs. Marks — Scatter Plot

Using the CBSE student data from Section 18e, create a scatter plot, add a linear trendline, and interpret the R² value. R² = 0.95 indicates that 95% of the variation in marks can be explained by study hours — a very strong positive correlation.

Example 5: Sensex Trend — Line Chart with Moving Average

Using the Sensex monthly data from Section 18d, create a line chart and add a 3-period Moving Average trendline. This smooths out monthly fluctuations and reveals the underlying trend. The smoothed line shows steady growth from Jan to Sep, followed by a correction in Q4.

Example 6: Reliance Revenue — Doughnut Chart

Using the Reliance data from Section 18c, create a doughnut chart comparing 2023 and 2024 revenue mix. Observe that Jio's share grew from 22% to 27%, reflecting the digital transformation strategy.

Example 7: Infosys Revenue + Growth — Combo Chart

Using the Infosys data from Section 18e, create a combo chart with columns for revenue and a line for growth rate on the secondary axis. The dramatic drop in growth from 20.7% to 4.7% in FY 2024 is immediately visible despite revenue still growing.

Example 8: GST Collection by State — Bar Chart

Task: Show GST collections in a horizontal bar chart sorted from highest to lowest.

Solution: Sort data descending by collection. Select A1:B9 → Insert → Bar Chart → Clustered Bar. Reverse category axis order so Maharashtra appears at top. Add data labels at bar ends.

Example 9: Quarterly Sales Contribution — Stacked Column

Using the Flipkart data from Example 1, create a Stacked Column chart. The total height of each group represents total quarterly sales across all categories. Q4's bar is tallest, clearly showing the festive season impact.

Example 10: Market Share — 100% Stacked Column

Solution: 100% Stacked Column shows how market share has shifted over 3 years — Flipkart declining, Amazon and Meesho growing.

Example 11: Temperature Variation — Area Chart

Solution: Unstacked Area chart (NOT stacked, since temperatures don't add up). Delhi shows the highest variation, Mumbai stays moderate, and Bangalore remains pleasant year-round.

Example 12–15: Quick Solved

12. Cricket Run Rate — Line chart showing run rate per over for an IPL match (overs 1–20 on x-axis, runs on y-axis).

13. Employee Age Distribution — Column chart with age groups (20-25, 25-30, 30-35, etc.) showing count of employees at TCS.

14. Zomato Rating Distribution — Pie chart (5 slices: 1★ to 5★) showing proportion of restaurant ratings.

15. Gold Price vs. Sensex — Scatter plot checking if there's a correlation between gold prices and stock market performance (spoiler: weak negative correlation).

Examples 16–20: Chart Customization

16. Take Example 1 and change column colours to Flipkart's brand blue and yellow.

17. Take Example 3 and add a data table below the chart showing the actual values.

18. Take Example 4 and add horizontal + vertical reference lines at average study hours and average marks.

19. Create a chart with the source data as an Excel Table. Add 3 new rows and verify the chart auto-updates.

20. Take Example 8 and move the chart to its own chart sheet named "GST Analysis".

Examples 21–25: Stacked & Combo

21. School marks — Stacked bar showing marks in 5 subjects for 4 students (each bar = 1 student, segments = subjects).

22. Telecom subscribers — 100% Stacked Column for Jio, Airtel, Vi, BSNL market share over 4 years.

23. Family monthly expenses — Stacked Area chart showing cumulative spending on Rent, Food, Transport, Entertainment.

24. Combo chart — Indian car sales (columns) + fuel price trend (line on secondary axis).

25. Doughnut chart — Two rings comparing school sports day medal distribution for 2023 and 2024.

Examples 26–30: Advanced Features

26. Add linear, exponential, and polynomial trendlines to the same chart and compare R² values.

27. Create a chart showing forecast using the "Forward forecast by __ periods" trendline option.

28. Create a Sunburst chart for hierarchical data: Country → State → City → Sales.

29. Create a Waterfall chart showing how starting revenue transforms into net profit through costs.

30. Create a Map chart showing Indian state-wise population using the Filled Map chart type.

Exercises, MCQs & Interview Questions — Charts

Practice Exercises

Exercise 1

Create a dataset of monthly sales (Jan–Dec) for 3 products sold by a local Indian grocery shop (Atta, Rice, Dal). Create a clustered column chart, a stacked column chart, and a 100% stacked column chart from the same data. Write one sentence explaining when each is most appropriate.

Exercise 2

Download or create India's annual GDP data from 2010 to 2024. Create a line chart with markers. Add a linear trendline and display the equation. Based on the trendline, predict the GDP for 2025.

Exercise 3

Create a dataset showing the percentage of students in your class who prefer different sports (Cricket, Football, Badminton, Kabaddi, Table Tennis). Create a pie chart with data labels showing both sport name and percentage. Explode the most popular sport slice.

Exercise 4

Collect the heights (cm) and weights (kg) of 15 students. Create a scatter plot and add a linear trendline. What is the R² value? Is the correlation strong or weak?

Exercise 5

Create a combo chart using the following data: Monthly electricity units consumed (bar) and electricity bill amount (line on secondary axis) for Jan–Jun. The relationship should be linear but not 1:1 due to slab rates.

Exercise 6

Create a bar chart showing the top 8 longest rivers in India with their lengths. Sort from longest to shortest. Add data labels, remove gridlines, and apply a professional colour scheme.

Exercise 7

Using your school's exam data, create a doughnut chart comparing the grade distribution (A, B, C, D, E) for the First Term and Second Term as two concentric rings.

Exercise 8

Create an area chart showing cumulative rainfall (mm) across 4 months (Jun–Sep) for Delhi, Mumbai, and Chennai. Use a stacked area variant.

Exercise 9

Format an existing chart: change the column colours to a custom gradient, add a chart title in 14pt bold, move the legend to the bottom, add horizontal gridlines only, and increase the font size of axis labels to 10pt.

Exercise 10

Create a dynamic chart using Excel Table (Ctrl+T). Start with 6 months of data. Add 3 more months and verify the chart updates automatically without any manual intervention.

Exercise 11

Move a chart from the current worksheet to a new chart sheet. Then copy that chart and paste it into a Word document and a PowerPoint slide.

Exercise 12

Use the Recommended Charts feature: select a dataset and note which charts Excel suggests. Create the top 2 recommended charts and write why Excel chose them.

MCQ Quiz

Interview Questions

Mini Project — Annual Sales Report Dashboard

Assignments

Assignment 1: Your School Analysis

Collect actual data from your school: marks in 5 subjects for 10 students. Create: (a) a clustered column chart comparing students, (b) a stacked column chart showing total marks composition, (c) a scatter plot of Math marks vs. Science marks. Write 100 words analyzing the correlation.

Assignment 2: India Economic Dashboard

Research and create charts for: GDP growth rate (line, 10 years), sector-wise GDP contribution (pie), top 5 exports (bar), FDI inflow vs. GDP growth (scatter). Present all on one sheet with proper annotations.

Sparklines — Tiny Charts, Big Insights

Learning Objectives

• Understand the three types of sparklines: Line, Column, and Win/Loss
• Create sparklines using the Insert → Sparklines menu
• Customize sparklines with markers, colours, and axis settings
• Format sparkline groups and individual sparklines
• Apply sparklines effectively in dashboards and KPI reports

Why Sparklines Matter

Traditional charts take up significant screen real estate. When you have a table with 20+ rows and need to show trends for EACH row, creating 20 separate charts is impractical. Sparklines solve this by embedding trend visualization inline with the data.

Sparkline Types & Creation

1. Line Sparkline

A tiny line chart inside a cell. Best for showing continuous trends — like stock prices, temperature, or monthly revenue over time.

2. Column Sparkline

A tiny bar chart inside a cell. Best for showing individual value comparisons where each data point matters independently — like daily attendance, weekly sales, or test scores.

3. Win/Loss Sparkline

Shows only two states: positive (win) or negative (loss). All positive bars are the same height; all negative bars are the same height below the axis. Best for binary outcomes, profit/loss status, or target met/missed.

Sparkline Customization & Formatting

When you click a sparkline, the Sparkline tab (also called Sparkline Design) appears in the ribbon with these options:

Markers

Markers add dots at specific data points on line sparklines:

Sparkline Colors and Styles

• Sparkline Color: Changes the line or column fill colour
• Sparkline Weight: Changes the line thickness (1pt, 1.5pt, 2pt, etc.)
• Marker Color: Sets individual colours for each marker type
• Style Gallery: Predefined colour combinations — hover to preview

Axis Settings

By default, each sparkline has its own independent axis. This means a line that goes from 10 to 20 looks the same as one going from 1,000 to 2,000. To fix this:

Sparkline Groups vs. Individual Formatting

When you create multiple sparklines at once, they form a group. Formatting changes apply to the entire group. To format one sparkline individually:

1. Click the sparkline you want to change
2. Sparkline tab → Ungroup
3. Now format that individual sparkline (colour, markers, etc.)
4. To regroup: select all sparklines → Sparkline tab → Group

Deleting Sparklines

You cannot delete sparklines by pressing Delete (that only clears cell content). Instead:

1. Click the sparkline
2. Sparkline tab → Clear → Clear Selected Sparklines (or Clear Selected Sparkline Groups)

Solved Examples — Sparklines

Example 1: Monthly Revenue Trend

Solution: Select trend column cells → Insert → Line Sparkline → Data Range = Q1:Q4 for each row. Add High Point (green) and Low Point (red) markers. IT Services shows consistent growth, ISRE has a dip-recovery pattern, and Consulting is volatile.

Example 2: Student Test Scores — Column Sparkline

Using the Class X data from Section 19a, create column sparklines. Aarav's sparkline shows tall Math bar and short Science bar — immediate visual identification of strengths and weaknesses without reading numbers.

Example 3: Sales Target Achievement — Win/Loss

Using the target data from Section 19a, Win/Loss sparklines immediately show: Sneha had the best record (3 wins, 1 loss), while Amit was inconsistent (2 wins, 2 losses).

Example 4: Common Axis Comparison

Problem: With independent axes, both sparklines look identical (slight upward trend). Solution: Set axis → "Same for All Sparklines" → Product A's sparkline shows tall columns while Product B shows tiny columns — accurately reflecting the 10× difference in scale.

Example 5: Dashboard KPIs with Sparklines

Solution: Each KPI row gets a line sparkline with markers. For "Avg Delivery Time" and "Complaints," a downward trend is positive. Use green sparkline colour for KPIs trending well and red for those trending poorly.

Example 6: Cricket Performance — Win/Loss

Create Win/Loss sparklines for IPL teams' last 10 matches. Mumbai Indians: W, L, W, W, L, L, W, W, W, L (+1 or -1 encoding). The sparkline instantly shows winning/losing streaks.

Example 7: Temperature Trends Across Cities

Average monthly temperature for 12 months for Delhi, Mumbai, Chennai, Kolkata — each city gets a line sparkline. Delhi's sparkline shows dramatic variation (14°C to 35°C); Mumbai's is relatively flat (25°C to 32°C).

Example 8: Ungrouping for Individual Formatting

In Example 5, ungroup the sparkline for "Customer Complaints" and change its colour to red (since complaints are negative). All other sparklines remain blue. This visual distinction helps dashboard users instantly understand polarity.

Example 9: Sparkline with Empty Cells

If a data range has empty cells (missing months): right-click sparkline → Sparkline Axis → show empty cells as Gaps, Zero, or Connect with Line. "Connect with Line" is usually best as it preserves the trend shape.

Example 10: Column Sparkline for Budget vs. Actual

Solution: Column sparklines with Negative Points marked in red. Positive variances (under budget) show as coloured bars above; negative variances (over budget) show as red bars below the axis. Marketing had 2 over-budget months; Sales mostly under budget; HR was tight throughout.

Examples 11–15: Practice Scenarios

11. Create line sparklines showing daily Sensex values for one week for 5 different stocks.

12. Create column sparklines showing attendance percentage for 6 months for 10 students.

13. Create Win/Loss sparklines for 8 cricket matches for 4 teams.

14. Create sparklines with a custom axis minimum of 0 (to prevent misleading trends when values are all high like 95, 96, 97, 98).

15. Create a single-row dashboard: Place metric name in column A, current value in B, target in C, and sparkline trend in D for 5 KPIs.

Exercises, MCQs & Interview Questions — Sparklines

Practice Exercises

Exercise 1

Create a table with 5 products and their monthly sales (Jan–Jun). Add line sparklines in the last column. Customize: add High Point (green) and Low Point (red) markers, set sparkline weight to 2pt.

Exercise 2

Create column sparklines for 8 students showing their marks in 5 subjects. Set the vertical axis to "Same for All Sparklines" so students can be compared fairly.

Exercise 3

Create Win/Loss sparklines for 6 sales representatives showing whether they met (1) or missed (-1) their monthly target for 6 months. Colour wins green and losses red.

Exercise 4

Take any sparkline group and ungroup it. Change one sparkline's colour to orange while keeping others blue. Then regroup them — what happens to the orange one?

Exercise 5

Create a KPI dashboard table with these rows: Revenue, Profit Margin, Customer Count, Employee Satisfaction, Defect Rate. Add appropriate sparklines and markers for each. Consider which KPIs are "higher is better" vs. "lower is better".

Exercise 6

Create sparklines from data that contains empty cells. Try all three empty cell options (Gaps, Zero, Connect with Line) and note the visual difference.

Exercise 7

Delete sparklines using the correct method (Sparkline tab → Clear). Try pressing Delete first and observe that it doesn't remove the sparkline — only clears the cell "content".

Exercise 8

Create a "traffic light" dashboard: use Win/Loss sparklines alongside conditional formatting icons to show monthly performance status for 10 KPIs.

MCQ Quiz

Interview Questions

Mini Project — Monthly KPI Summary Dashboard

Assignment

Assignment: Personal Academic Dashboard

Create a dashboard tracking your academic performance across 5 subjects for 6 test instances (Unit Test 1, UT2, UT3, Half Yearly, UT4, Final Exam). Each subject gets a line sparkline showing your progress. Add High/Low markers and set a common axis. Write 3 observations about your performance based on the sparkline patterns.

Conditional Formatting for Dashboards

Learning Objectives

• Apply Data Bars (solid and gradient) as in-cell bar charts
• Create heat maps using Color Scales (2-colour and 3-colour)
• Add Icon Sets (arrows, traffic lights, stars, flags) based on values
• Write custom conditional formatting rules using formulas
• Highlight entire rows based on a single column's condition
• Build professional dashboards with traffic light indicators and progress bars
• Understand dashboard design principles: colour theory, layout, and focus areas

Conditional Formatting — Quick Overview

Conditional formatting dynamically changes a cell's appearance (fill colour, font colour, borders, icons, or data bars) based on its value or a formula. It's the bridge between raw data and visual insight — no charts needed.

Access it via: Home → Conditional Formatting

Data Bars — In-Cell Bar Charts

Data bars add a coloured horizontal bar inside each cell, where the bar length is proportional to the cell value. They turn any column of numbers into an instant visual comparison — like a bar chart embedded in the cells.

Solid vs. Gradient Data Bars

Customizing Data Bars

Progress Bars Using Data Bars

To create professional progress bars:

1. Enter percentage values (0% to 100%)
2. Apply green gradient data bars
3. Edit Rule → check "Show Bar Only"
4. Set Minimum = Number: 0, Maximum = Number: 1 (or 100 if not using %)
5. Set fill colour to a green gradient
6. Result: clean progress bars that look like a professional dashboard element

Color Scales — Heat Maps

Color scales fill cells with a gradient of colours based on their value relative to other cells in the range. They create instant heat maps that make patterns visible at a glance.

Types of Color Scales

Customizing Color Scale Ranges

Icon Sets — Visual Status Indicators

Icon sets add small icons (arrows, traffic lights, stars, flags) inside cells based on value ranges. They're perfect for KPI dashboards where you need to show status at a glance.

Available Icon Set Categories

Showing Icons Only (No Numbers)

For a clean dashboard look, you may want to show ONLY the icon without the number:

1. Edit the icon set rule
2. Check "Show Icon Only"
3. The cell displays just the icon — no number
4. The actual value is still in the cell (visible in the formula bar) — only the display changes

Custom Icon Set Rules

You can mix icons from different sets and assign custom value ranges:

Custom Conditional Formatting Rules with Formulas

The most powerful type of conditional formatting uses custom formulas. This allows you to format cells based on ANY condition — including conditions in other columns, complex logic, and entire row highlighting.

How Formula-Based CF Works

Instead of a simple "greater than" condition, you write an Excel formula that returns TRUE or FALSE. If the formula returns TRUE for a cell, the formatting applies.

Rule 1: Highlighting Entire Rows Based on a Condition

Rule 2: Alternating Row Colours (Zebra Stripes)

Formula: =MOD(ROW(),2)=0

This highlights every even-numbered row. Apply it to the entire data range with a light grey fill for professional-looking alternating rows.

Rule 3: Highlight Cells Greater Than the Row Average

For the student marks dataset, highlight any individual subject mark that exceeds that student's average:

Formula (applied to B2:D5): =B2>AVERAGE($B2:$D2)

This highlights above-average performance for each student individually.

Rule 4: Duplicate Detection

Highlight duplicate values in a list:

Formula: =COUNTIF($A:$A,A2)>1

Useful for cleaning data — instantly spots duplicate entries in employee IDs, invoice numbers, etc.

Rule 5: Upcoming Deadlines (Next 7 Days)

Highlight dates that fall within the next 7 days:

Formula: =AND(A2>=TODAY(),A2<=TODAY()+7)

Apply orange fill — gives a visual warning for upcoming deadlines.

Rule 6: Dynamic Threshold with Cell Reference

Highlight values above a user-entered threshold:

Formula: =B2>$H$1 (where H1 contains the threshold value entered by the user)

Changing H1's value instantly updates the highlighting — making the dashboard interactive!

Dashboard Design Principles

Conditional formatting is one tool in the dashboard designer's toolkit. Here are the principles that separate amateur dashboards from professional ones:

1. Colour Theory for Dashboards

2. Layout Principles

• F-Pattern: People read dashboards in an F-shape — put the most important KPIs at the top-left
• Less is More: Limit to 5-7 key metrics per dashboard view. Information overload defeats the purpose
• Grouping: Group related metrics together (all financial KPIs in one section, all operational in another)
• White Space: Use empty rows/columns between sections to prevent visual clutter
• Consistent Units: Don't mix ₹ Lakhs and ₹ Crores on the same dashboard without clear labels

3. Traffic Light KPI Dashboard Pattern

The traffic light pattern uses three layers:

1. Color Scales on the Achievement % column (green-yellow-red gradient)
2. Icon Sets (traffic lights) in the Status column — with custom thresholds
3. Data Bars on the Actual column — shows relative performance

4. Combining All Three CF Types

A professional dashboard might use ALL conditional formatting types on the same table:

• Data bars on numeric columns for visual comparison
• Color scales on a heat map grid
• Icon sets for status indicators
• Custom formula rules for row highlighting

Solved Examples — Conditional Formatting

Example 1: Data Bars for Employee Salaries

Task: Add gradient data bars to the salary column.

Solution: Select C2:C7 → Conditional Formatting → Data Bars → Blue Gradient. Meera's bar is longest (₹1.1L), Priya's is shortest (₹55K). This instantly shows the salary distribution without reading numbers.

Example 2: Heat Map for City-wise Monthly Sales

Solution: Select B2:G6 → Conditional Formatting → Color Scales → Green-White. The darkest green cells (Mumbai, Jun) represent the highest sales. The heat map reveals that sales increase month-over-month across all cities, with Mumbai consistently outperforming.

Example 3: Traffic Light Icons for Student Results

Solution: Select percentage column → Icon Sets → 3 Traffic Lights. Edit rule: Green ≥ 75, Yellow ≥ 40, Red < 40 (matching Indian grading thresholds). Switch Type from "Percent" to "Number" for accurate thresholds.

Example 4: Highlight Entire Row for Overdue Tasks

Formula: =AND($D2="Pending",$C2<TODAY()) — highlights the entire row red if the task is still Pending AND the due date has passed. Applied to range A2:D5 with row highlight.

Example 5: GST Compliance Dashboard

Solution: Apply three CF rules:

1. Row highlight: =$B2="Overdue" → red fill
2. Row highlight: =$B2="Pending" → yellow fill
3. Data bars on Balance column (red gradient) — longer bars = more outstanding payment

Example 6: Star Ratings for Restaurant Reviews

Zomato restaurant data with ratings 1.0–5.0. Apply 5-star icon set with thresholds at 4.5, 3.5, 2.5, 1.5. Show Icon Only. Result: each restaurant displays its star rating visually.

Example 7: Conditional Formatting with Dynamic Threshold

Sales data where a target value is entered in cell H1. Formula rule: =B2>=$H$1 formats cells green. Changing H1 from 50,000 to 75,000 instantly updates which cells are highlighted.

Example 8: Duplicate ID Detection

Employee ID list with 50 entries. Formula: =COUNTIF($A$2:$A$51,A2)>1. Duplicates get a red fill. Found: 3 duplicate IDs that need correction in the HR system.

Example 9: Top 5 Performers Highlighting

Select sales column → Conditional Formatting → Top/Bottom Rules → Top 10 → change to Top 5 → green fill. The 5 highest sales values are instantly highlighted.

Example 10: Weekend Date Highlighting

Date column formula: =OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7). Saturdays and Sundays get a light blue fill. Useful for attendance tracking and work scheduling.

Examples 11–15: Data Bars Variations

11. Create bidirectional data bars for profit/loss data (positive = green right, negative = red left).

12. Create progress bars (Show Bar Only + set min=0, max=100%) for project completion tracking.

13. Apply solid blue data bars to exam scores and compare with gradient blue — note readability difference.

14. Data bars on a column with values 95, 96, 97, 98, 99 — notice all bars look nearly identical. Fix by setting minimum to 90.

15. Add data bar borders (Edit Rule → Border → Solid) for a crisper look.

Examples 16–20: Color Scales & Icons

16. Create a 2-colour scale (white to dark blue) for a population density table of Indian states.

17. Create a 3-colour scale where midpoint is the class average mark — highlights above-average (green) and below-average (red).

18. Apply 5-arrow icon set to year-over-year growth percentages. Customize: ↑↑ for >20%, ↑ for 10-20%, → for 0-10%, ↓ for -10%-0%, ↓↓ for < -10%.

19. Apply flag icons to project milestones: green flag = complete, yellow = in progress, red = not started.

20. Create a mixed icon set combining traffic light circles (for status) and arrows (for trend) in adjacent columns.

Examples 21–25: Formula-Based Rules

21. Highlight all marks below 40 in red AND above 90 in green using two custom formula rules.

22. Highlight rows where any subject mark is below 33 (compartment candidate): =COUNTIF($B2:$F2,"<33")>0

23. Alternate row colouring using =MOD(ROW(),2)=0

24. Highlight the current day's row in a date-based task list: =$A2=TODAY()

25. Create a "search highlight" — user types a name in G1, and all rows containing that name get highlighted: =ISNUMBER(SEARCH($G$1,$A2))

Exercises, MCQs & Interview Questions — Conditional Formatting

Practice Exercises

Exercise 1

Create a dataset of 10 students with marks in 5 subjects. Apply a 3-colour scale (green-yellow-red) to create a heat map. Which subjects are weakest across the class?

Exercise 2

Create a project tracker with 8 tasks, each with a completion percentage (0-100%). Apply gradient data bars. Then edit the rule to show "Bar Only" mode, creating clean progress bars.

Exercise 3

Apply traffic light icons to a KPI table with 6 metrics. Customize thresholds using "Number" type (not percentage). Set green ≥ 90%, yellow ≥ 70%, red < 70%.

Exercise 4

Create a formula-based rule to highlight entire rows where the "Department" column equals "Sales". Use the =$C2="Sales" formula pattern with correct dollar sign placement.

Exercise 5

Create a date-based task list with due dates. Apply CF to highlight: overdue (red), due today (orange), due within 3 days (yellow), using three formula rules.

Exercise 6

Apply conditional formatting to highlight duplicate values in a student roll number column. Verify by intentionally adding 2-3 duplicates.

Exercise 7

Create a combined dashboard: data bars on sales column, colour scale on profit margin column, and icon sets on target achievement column — all on the same table.

Exercise 8

Create a "dynamic threshold" dashboard: place a value in cell H1, and apply CF that highlights all sales values above that threshold in green. Change H1's value and observe the highlight update instantly.

Exercise 9

Use Manage Rules to view all conditional formatting rules on a sheet. Change the priority order of two conflicting rules and observe the effect. Test the "Stop If True" checkbox.

Exercise 10

Clear all conditional formatting from a range using: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells. Then clear rules from the entire sheet.

MCQ Quiz

Interview Questions

Mini Project — Classroom Performance Heat Map Dashboard

Assignments

Assignment 1: Company Sales Dashboard

Create a sales dashboard for a fictional company with 10 salespeople and their monthly targets vs. actuals for 6 months. Use data bars for actual sales, traffic light icons for achievement, colour scales for the performance grid, and formula-based CF to highlight rows where any month's achievement is below 70%.

Assignment 2: Personal Expense Tracker

Track your daily expenses for one month across 5 categories (Food, Transport, Shopping, Bills, Entertainment). Apply colour scales to identify high-spending days, data bars to compare category totals, and icon sets to flag days where spending exceeded your daily budget. Use a formula rule to highlight weekends differently.