Excel · Lesson 8 of 8

Conditional formatting, validation & charts

Highlighting what matters, preventing bad input, and presenting with charts.

Conditional formatting

Home → Conditional Formatting colours cells by rule: values above a target, duplicates, top 10, data bars, colour scales. Use “Use a formula” for custom rules, e.g. highlight the whole row when status is “Overdue”: =$D2="Overdue".

Data validation

Data → Data Validation restricts input: a drop-down List (Pending, Done), whole numbers between limits, dates after today. It keeps shared sheets clean.

Charts

Column/bar — compare categories.
Line — trends over time.
Pie — parts of a whole, only with a few slices.

Give every chart a clear title and labelled axes, and avoid 3-D effects that distort values.

Sample sheet & formulas

Type these in Excel or Google Sheets

     A          B          C           D
1    Task       Owner      Due date    Status
2    GST file   Anu        05-Nov-26   Pending
3    Payroll    Rahul      01-Nov-26   Overdue
4    Audit      Meera      20-Nov-26   Done

Data validation on D2:D50:
  Allow: List   Source: Pending,Done,Overdue

Conditional formatting on A2:D50 (Use a formula):
  =$D2="Overdue"        → red fill
  =$D2="Done"           → green text

Chart: select Owner + a count of tasks → Insert → Clustered Column

Results

Row 3 (Payroll) turns red; row 4 (Audit) shows green text.
Typing "Finished" in D5 is rejected with an error message.

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

Formula-based rules can colour whole rows.
Validation lists stop typos in shared sheets.
Pick the chart that matches the question.

Quick check

Which chart best shows a trend over 12 months?

Pie Line Doughnut Radar

Practice task

Build an attendance sheet with a P/A/L drop-down for each day and a rule that highlights any employee with more than 3 'A' in the month.

Interview corner

How do you highlight an entire row based on one cell?

Select the rows, add a conditional formatting rule with a formula such as =$D2="Overdue" — the $ locks the column while the row changes.

How would you build a simple dashboard in Excel?

Clean data in a Table, summarise with PivotTables, add PivotCharts, connect Slicers for filtering, and place KPIs on one sheet.