Chapter 9: Charts and Visualizations

Charts and Visualizations is one of the most enjoyable parts of Excel because a good chart can tell the story of your data in seconds — much better than looking at hundreds of rows of numbers.

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you in Airoli (hope you’re enjoying your morning chai while we work on this!). We’ll continue using our tea shop sales data from previous chapters as the example. All steps are based on Excel for Microsoft 365 / Excel 2021+ / 2024 / 2025–2026 (the interface is almost identical).

Let’s turn your numbers into professional-looking charts!

1. Creating Various Chart Types

Quick rule before starting: Always select your data including headers before inserting a chart — Excel uses headers for labels automatically.

Recommended chart types for common data:

Step-by-step to create a chart:

1. Go to your TeaSales table (or any data range with headers).
2. Select the data you want to chart.
   • Example 1: Sales by Item Select Item column + Total column (A1:A10 and E1:E10 — or just click inside table and select columns).
3. Go to Insert tab → Charts group.
4. Click the chart type you want (or Recommended Charts — Excel suggests good ones).
   • Column or Bar Chart → choose Clustered Column (most common)
   • Line Chart → Line with Markers
   • Pie Chart → 2-D Pie
   • Scatter → Scatter with Straight Lines or just Scatter

Real example – Sales by Item (Clustered Column Chart)

1. In TeaSales table, make sure you have:
   • Column B: Item (Chai, Coffee, Biscuit, …)
   • Column E: Total (with formula =[@Qty]*[@Price])
2. Click any cell in the table → Insert → Recommended Charts
3. Excel usually suggests a Clustered Column — click it → OK → Instant chart appears!

Quick alternatives:

• Bar chart (horizontal): Insert → Bar → Clustered Bar
• Pie chart (share of sales): Select Item + Total → Insert → Pie → 2-D Pie (Tip: Pie works best with 5 or fewer categories — otherwise use column/bar)

2. Customizing Charts (Titles, Legends, Axes)

Once the chart is created, click anywhere on it → three new tabs appear on the Ribbon:

• Chart Design
• Format

Most common customizations (do these in order):

1. Add / Edit Chart Title
   • Click Chart Design → Add Chart Element → Chart Title → Above Chart
   • Click the title box → type: “Monthly Sales by Item – Jan 2026”
   • Make it bold, size 16, dark blue.
2. Legend (shows what colors mean)
   • Usually appears on right by default.
   • Move it: Add Chart Element → Legend → Right / Bottom / Top
   • Or remove if unnecessary (e.g., pie chart with data labels).
3. Data Labels (show numbers on bars/points)
   • Add Chart Element → Data Labels → Outside End or Inside End
   • Format: Right-click a label → Format Data Labels → choose Value, position, font size.
4. Axes Titles
   • Add Chart Element → Axis Titles → Primary Horizontal → type “Items”
   • Primary Vertical → type “Sales Amount (₹)”
5. Change Colors / Style
   • Chart Design → Change Colors → pick a palette (avoid too many bright colors)
   • Or More → Chart Styles → choose a professional look (e.g., Style 3 or 8)
6. Adjust Axes (very useful for readability)
   • Right-click vertical axis → Format Axis
     • Bounds: Minimum 0, Maximum auto or 15000 (if highest bar is ~12000)
     • Units: Major 2000 (nice round steps)
   • Horizontal axis: Rotate labels if long names → Text Options → Text Box → Custom angle 45°

Pro tip: Press Alt + F11 no — better: double-click any chart element (bar, title, axis) to open Format pane on the right — very fast way to customize.

3. Sparklines (Mini Charts Inside Cells)

Sparklines are tiny charts inside a single cell — perfect for showing trends next to data without full chart space.

Types: Line, Column, Win/Loss

Example – Monthly sales trend per item

1. Assume you have 12 months data in columns G:R (Jan to Dec sales per item).
2. Select the cell next to each item (say column S, S2:S10).
3. Go to Insert tab → Sparklines group → Line (or Column).
4. Data Range: select the 12 months for that row (e.g., G2:R2).
5. Click OK.

→ Tiny line sparkline appears in S2 showing sales trend for that item over the year!

Customize:

• Click the sparkline cell → Sparkline tab appears.
• Marker Color → red for high/low points.
• Show Markers → check to see data points.

4. Combo Charts and Secondary Axes

Combo chart = two (or more) different chart types in one (e.g., columns + line).

Secondary Axis = one data series uses right axis (different scale).

Real example – Sales (₹) + Quantity Trend Goal: Show Total Sales as columns + Average Qty per day as a line (different scales).

1. Select data: Item, Total Sales, Avg Qty (three columns).
2. Insert → Recommended Charts → look for Combo tab (or manually).
3. If not recommended: Insert → Insert Combo Chart (bottom of Charts group) → Clustered Column – Line on Secondary Axis
4. In Chart Tools → Change Chart Type (if needed):
   • Series “Total Sales” → Clustered Column → Primary Axis
   • Series “Avg Qty” → Line → Secondary Axis
5. Click OK.

→ Now you see tall columns for sales money + a smooth line for average quantity (right axis shows different scale).

Customize secondary axis:

• Right-click the line → Format Data Series → Secondary Axis
• Right-click right axis → Format Axis → set nice min/max/units.

Quick Summary Table – Chapter 9

Homework / Practice (Do This Today!)

Use your TeaSales table:

1. Create Clustered Column Chart → Sales by Item (Total column).
2. Add title: “Top Selling Items – Jan 2026”
3. Add data labels on top of bars, legend at bottom, axis titles.
4. Change colors to tea-themed (browns, greens).
5. Create Pie Chart of the same data → add % data labels.
6. Create Line Chart if you have daily data (Date vs Total).
7. Add Sparklines in a new column → show Qty trend over last 7 days (if you have it).
8. Create Combo Chart: Columns = Total Sales, Line on secondary axis = Qty.