Excel Charts
Chart types & when to use · Insert & format · Axes & labels · Trendlines · Sparklines · Combo charts
Sheet 3 of 3
Excel 365
Beginner–Inter.
Printable
Chart Type Quick Chooser
Basic
📊 Clustered Column
Compare values across categories side by side. Best for ≤7 categories.
Basic
📈 Line
Show trends over time — sales by month, traffic by day.
Basic
🍕 Pie / Doughnut
Part-to-whole for a single series. Keep slices under 6.
Basic
📉 Bar (horizontal)
Long category names or ranking lists — easier to read than column.
Basic
📊 Stacked Column/Bar
Part-to-whole AND compare totals across categories at the same time.
Advanced
🔵 Scatter / Bubble
Correlation between two numeric variables. Bubble adds a 3rd dimension.
Advanced
🌡 Combo
Two metrics on different scales — e.g. Revenue (bars) + Margin % (line).
Advanced
🌊 Area
Cumulative totals over time. Stacked area = multiple series composition.
Modern
🟦 Treemap
Hierarchical part-to-whole. Great for large category breakdowns.
Modern
📉 Waterfall
Running total with positive/negative increments — cash flow, P&L bridges.
Modern
🔸 Funnel
Sequential stages with declining values — sales pipeline, conversion.
Modern
✦ Sunburst / Histogram
Sunburst = multi-level pie. Histogram = frequency distribution of data.
Rule of thumb: Use column/bar for comparisons, line for trends, pie for composition (max 6 slices), scatter for relationships. When in doubt, start with a column chart.
Insert & Format a Chart
Create a Chart
- Select your data — include headers. Non-adjacent ranges: hold Ctrl while selecting.
- Insert tab → Charts group → pick chart type, or press AltF1 for instant default chart.
- Recommended Charts button shows Excel's smart suggestions based on your data.
- Chart appears on the sheet — drag to reposition, drag corner handles to resize.
- Move to own sheet: right-click chart border → Move Chart → New sheet.
The 3 Chart Tabs (appear when chart is selected)
| Tab | What you can do |
|---|---|
| Chart Design | Change chart type · Switch row/col · Add chart element · Quick Layout · Chart Styles · Move chart |
| Format | Shape fill/outline · Text formatting · Size & position · Selection pane |
| PivotChart Analyze | (Pivot charts only) Field buttons · Active field · Insert Slicer |
Axes, Titles & Labels
Format Axis — double-click to open pane
Minimum / Maximum bounds Format Axis → Axis Options → set Minimum / Maximum (uncheck Auto to lock) Number format on axis Format Axis → Number → Category: Currency / % → Custom: #,##0 "K" Reverse axis order Format Axis → Axis Options → Values in reverse order ✓ Logarithmic scale Format Axis → Logarithmic scale ✓
Add & Format Chart Elements
Chart title Click "+ " button (top-right) → Chart Title → Above Chart Click title text to edit inline Data labels + → Data Labels → pick position Double-click label → Format → Label Contains: ✓ Value ✓ Category ✓ % Legend position + → Legend → Bottom / Right Gridlines + → Gridlines → toggle on/off
Trendlines, Error Bars & Secondary Axis
Add a Trendline
- Click a data series in the chart.
- Click the + button → Trendline → choose type.
- Double-click the trendline → Format Trendline pane for options.
- Tick Display Equation on chart and/or Display R-squared to show fit quality.
| Type | Use when |
|---|---|
| Linear | Steady rate of increase/decrease |
| Exponential | Growth/decay that accelerates |
| Polynomial | Curved fluctuating data |
| Moving Avg | Smooth out noisy time series |
| Power | Data that increases at a set rate |
Secondary Axis — Combo Chart
- Click a data series → Format Data Series → tick Secondary Axis. A second Y-axis appears on the right.
- Right-click chart → Change Chart Type → Combo → choose a different chart type per series (e.g. Column + Line).
- Format each axis independently — right-click → Format Axis.
Classic combo: Revenue as clustered columns on primary axis + Profit Margin % as a line on secondary axis — one chart tells the full story.
Error Bars & Reference Lines
Add Error Bars Select series → + → Error Bars → Standard Error / Percentage / Standard Deviation → More Options for custom values Add a target / average line Add a new data series with constant value = target → Change that series to Line → Format → Dash type: Dashed → Label it "Target" in legend Dynamic average line Helper column: =AVERAGE($B$2:$B$13) Plot as second line series
Sparklines
Insert Sparklines
- Select the cell where you want the sparkline to appear.
- Insert → Sparklines → Line / Column / Win/Loss.
- In Data Range, select the row of numbers (e.g. B2:M2).
- Click OK — a tiny chart appears inside the cell.
- To create for many rows at once, select all destination cells first, then set Data Range to all rows (e.g. B2:M10).
Format Sparklines
Sparkline tab appears when selected Highlight special points Sparkline tab → Show: ✓ High Point ✓ Low Point ✓ First Point ✓ Last Point ✓ Negative Points → each gets a different colour Change sparkline type Sparkline tab → Type: Line / Column / Win Loss Axis — same scale across rows Axis → Vertical Axis Minimum: "Same for All Sparklines" (critical for fair comparison!)
Set the same axis scale for all sparklines in a group — otherwise each mini chart auto-scales to its own data, making rows look identical even when values differ greatly.
Formatting & Design Tips
Quick Formatting Actions
Change series colour Click series once → Format Data Series → Fill → Solid fill Remove chartjunk (best practice) ✗ Delete default gridlines ✗ Remove 3D effects ✗ Remove background fill ✓ Keep data labels instead ✓ Use direct labels over legend Gap width (column/bar spacing) Double-click bar → Format Data Series → Gap Width: 40–80%
Link Chart Title to a Cell
Dynamic title that updates automatically 1. Click the chart title once 2. Click in the formula bar 3. Type = then click the cell that contains your title text 4. Press Enter Now the title reads from that cell — change the cell → title updates Useful for dashboards with year/region selectors
Dynamic Charts & Dashboard Tips
Auto-expanding Chart with Table
- Format your data as a Table: Ctrl+T.
- Insert a chart from the Table.
- Add new rows to the Table — the chart updates automatically on next refresh.
- No need to manually extend the chart's data range ever again.
Dashboard Best Practices
Layout ✓ Dedicate one sheet to dashboard ✓ Place slicers at top or left ✓ Align charts to cell grid Hold Alt while dragging → snaps to cell borders Navigation ✓ Freeze top rows: View → Freeze Panes ✓ Hide gridlines: View → Show → uncheck Gridlines ✓ Hide row/col headers too
Keyboard Shortcuts
Insert & Navigate
Alt F1 Instant chart (same sheet)
F11 Chart on new sheet
Ctrl C Copy chart
Ctrl V Paste chart
Select & Format
Esc Deselect element
↑ ↓ ← → Cycle chart elements
Ctrl 1 Format selected element
Delete Remove selected element
Common Chart Mistakes to Avoid
Wrong Chart Type
✗ Pie with 10+ slices → unreadable → Use bar chart instead ✗ Line chart for categories (not time-based) → Use column chart ✗ 3D charts — distort perception → Always use 2D ✗ Dual axis misused to imply false correlation → Label axes clearly
Axis Tricks (Misleading)
✗ Y-axis not starting at zero (exaggerates small differences) → For comparisons: start at 0 → For trends: broken axis is ok if clearly labelled ✗ Inconsistent axis intervals → Format Axis → Major Unit: set a fixed value ✗ Missing axis title — reader doesn't know the unit → Always label axes with unit
Clutter & Colour
✗ Too many series in one chart → Split into small multiples ✗ Rainbow colours with no meaning → One colour for same category Highlight key bar in accent ✗ Gridlines competing with data → Light grey gridlines or none ✗ Legend far from data → Use direct data labels or move legend inside chart
Excel Charts Mastery Checklist
| Chart Types | Key point |
|---|---|
| Choose the right chart type | column=compare, line=trend |
| Use combo for 2 metrics | Column + Line + secondary axis |
| Show hierarchy | Treemap / Sunburst |
| Show running totals | Waterfall chart |
| Format & Analyse | Key point |
|---|---|
| Lock axis min/max | Format Axis → uncheck Auto |
| Add trend/forecast line | + → Trendline → Linear |
| Link title to a cell | = CellRef in formula bar |
| In-cell mini charts | Insert → Sparklines |
| Dashboard | Key point |
|---|---|
| Auto-expanding chart data | Ctrl+T Table as source |
| Snap chart to cell grid | Hold Alt while dragging |
| Instant chart shortcut | Alt + F1 |
| Hide gridlines for clean look | View → uncheck Gridlines |
Microsoft Excel series complete! ✓ ·
You've covered Formulas, Pivot Tables, and Charts. Explore more on Dataplexa — Power BI, DAX Formulas, Tableau, and the full Analytics & BI cheat sheet collection.