Charts, Tables & Dashboards
Raw numbers rarely persuade anyone — a well-chosen chart does. This lesson covers picking the right chart type for the message you're sending, styling charts so they read cleanly in seconds, and assembling several visuals into a single-screen dashboard that communicates status at a glance.
Why this skill matters professionally
Every role that touches reporting — from marketing coordinator to financial analyst to operations manager — is eventually asked to 'put this in a chart' or 'build a dashboard for the team.' The difference between a chart that gets understood in three seconds and one that gets squinted at for a minute is almost entirely about chart-type selection and decluttering, not fancy formatting. Analysts who can build clean, well-organized dashboards get handed the recurring reporting responsibilities that build visibility with leadership — and dashboards are frequently the exact deliverable evaluated in interview skills tests.
Learning objectives
- Choose the correct chart type for the message.
- Format charts cleanly and avoid junk.
- Compose a simple KPI dashboard.
Background concepts
Charts answer questions, they don't just display data
Before opening the Insert Chart menu, identify the single question the chart needs to answer: Is something trending up or down? Are categories being compared? Is one part of a whole bigger than another? The answer determines the chart type — never the other way around.
Chart type by intent
Match the chart family to the message:
- Trend over time → Line chart
- Compare discrete categories → Column or Bar chart
- Part-to-whole composition → 100% Stacked Bar/Column (avoid pie charts with more than 4-5 slices)
- Relationship between two numeric variables → Scatter chart
- Distribution of a single numeric variable → Histogram
- Progress toward a single goal → a simple bar with a target line, not a gauge or speedometer chart
The Excel Table as the dashboard's foundation
Convert any list feeding a chart or KPI into a proper Excel Table (Ctrl+T). Tables auto-expand, keep formulas and formatting consistent as rows grow, and are what Pivot Charts and Slicers connect to for interactive dashboards.
What makes a dashboard, versus just a bunch of charts
A true dashboard has hierarchy and a single filter mechanism: a top row of headline KPI numbers, a middle section with the one or two most important charts, supporting detail below, and ideally one Slicer that filters everything at once. Randomly scattered charts on a sheet are not a dashboard.
Step-by-step
1. Step 1 — Prepare clean, Table-based source data
Convert your source range to a Table (Ctrl+T). Confirm headers are clear and every column is a consistent data type — dates as dates, numbers as numbers.
2. Step 2 — Select data and insert the chart
Select the relevant columns (not the whole sheet), then Insert → Charts and pick the type matching your message. Use Insert → Recommended Charts if unsure — it's a reasonable starting suggestion, not a final answer.
3. Step 3 — Strip out chart junk
Remove default gridlines (Chart Elements → uncheck Gridlines) unless precise value reading matters. Delete legends when a chart has only one series, or when data labels make the legend redundant. Turn off 3D effects entirely — they distort perceived values and add nothing.
4. Step 4 — Add direct data labels instead of relying on axes
Chart Elements → Data Labels for the key series. Direct labels let a viewer read exact values without hunting along a gridline, especially effective on a simple 2-3 category bar chart.
5. Step 5 — Use one accent color intentionally
Pick a single accent color for the metric that matters, and gray out the rest. On a column chart comparing this year vs. last year, color the current year's bars in your brand accent and make prior-year bars light gray — the eye goes straight to what changed.
6. Step 6 — Build KPI cards
In a small cluster of merged/formatted cells, place a large bold number driven by a formula like =SUM(SalesData[Revenue]), a small gray label underneath ('Total Revenue'), and a Sparkline (Insert → Sparklines → Line) beneath that showing the trend in miniature.
+----------------------+ | $362,270 | | Total Revenue | | ▂▃▅▄▆▇█▆ | +----------------------+
7. Step 7 — Lay out the dashboard grid
Reserve the top row of the sheet for 3-4 KPI cards, the middle band for one or two primary charts (trend and a category comparison), and the bottom band for a supporting detail table or secondary chart. Turn off gridlines on the whole sheet (View → uncheck Gridlines) for a cleaner canvas.
8. Step 8 — Add one filter control for the whole dashboard
If your charts are Pivot Charts, insert one Slicer (e.g. Region or Date range via Timeline) and connect it to every pivot behind every chart via Report Connections, so one click updates the entire dashboard.
9. Step 9 — Lock down the print/share view
Use Page Layout → Print Area to define exactly the dashboard region, and consider freezing panes or hiding the raw data sheet (right-click tab → Hide) before sharing so viewers see only the polished dashboard.
10. Step 10 — Sanity-check colorblind and print accessibility
Avoid relying on red/green alone to distinguish series — pair color with direct labels or distinct shapes/patterns. Preview in grayscale (Page Layout → Print Titles → Sheet → check Black and White) to confirm the dashboard still reads if printed without color.
Real-world workplace examples
Monthly revenue trend line for a board deck
A finance team builds a single clean line chart with month on the X-axis and revenue on the Y-axis, one accent color, no gridlines, and data labels only on the final data point — instantly communicating the trend direction.
Marketing channel comparison
A marketing analyst uses a horizontal bar chart (not a pie) to compare leads generated per channel, sorted descending, because a bar chart lets viewers compare lengths far more accurately than pie slice angles.
Operations KPI wall
An operations manager builds four KPI cards (Orders Shipped, On-Time %, Returns, Avg. Fulfillment Time) with sparklines, refreshed weekly from a linked Table, replacing what used to be a five-tab spreadsheet nobody opened.
Sales dashboard with a region Slicer
A sales-ops analyst combines two Pivot Charts and one Slicer into a single tab; regional managers click their own region and instantly see their trend and top products without any spreadsheet editing.
Budget vs. actual variance chart
A budget analyst uses a clustered column chart (Budget vs. Actual side by side per department) with a thin marker line overlay for variance percentage, giving both magnitude and direction in one visual.
Practical scenarios
Replacing a pie chart that confused the room
A product manager originally presented market share across seven competitors as a pie chart; in the meeting, two similarly-sized slices caused confusion about which company was actually larger. She rebuilt it as a horizontal bar chart sorted largest to smallest with data labels showing exact percentages — the next meeting had zero clarifying questions about the ranking.
Building the weekly leadership dashboard from scratch
An operations coordinator inherits a request to build 'a dashboard' for weekly leadership review, previously delivered as six separate emailed screenshots. She converts the source logs into an Excel Table, builds three Pivot Charts (weekly throughput trend, backlog by team, error rate), adds four KPI cards with sparklines at the top, and connects a single week-range Timeline to filter everything. The dashboard becomes the standing agenda item, and leadership self-serves historical weeks without asking her for a rebuild each time.
Common mistakes beginners make
Defaulting to a pie chart for everything
Pie charts are hard to read accurately beyond 3-4 slices because humans compare angles poorly. Fix: use a sorted horizontal bar chart for more than a handful of categories, reserving pies for true 2-3 slice part-to-whole stories.
Using dual Y-axes without labeling them clearly
Combining two metrics with very different scales on one chart using a secondary axis often misleads viewers into thinking two unrelated lines are directly comparable. Fix: label each axis clearly with its unit and color-match the axis title to its series, or split into two charts if the audience is non-technical.
Cramming too many series onto one chart
A line chart with ten overlapping series becomes an unreadable 'spaghetti chart.' Fix: highlight the 2-3 series that matter in color and gray out the rest, or split into small multiples (several small identical charts, one per category).
Leaving default gridlines, 3D effects, and legends on every chart
Default chart junk adds visual noise without adding information. Fix: strip gridlines, avoid 3D entirely, and remove single-series legends, replacing them with a direct chart title or data labels.
Building a dashboard with no single filter mechanism
A sheet full of static charts each requiring separate manual edits to filter is not a real dashboard and creates a maintenance burden. Fix: base every chart on a Pivot/Table and connect one Slicer or Timeline to control them all together.
Best practices
- Choose chart type based on the specific question being answered, never by habit or by what looks impressive.
- Strip gridlines, 3D effects, and redundant legends from every chart before sharing.
- Sort categorical bar/column charts largest-to-smallest unless there's a natural order (like months).
- Use one accent color to draw the eye to what matters; gray out the rest.
- Base dashboard charts on Pivot Tables/Charts fed by an Excel Table so they can be filtered and refreshed centrally.
- Reserve the top of a dashboard for headline KPI numbers, with supporting charts below.
- Add exactly one shared filter control (Slicer/Timeline) rather than a separate filter per chart.
- Preview dashboards in grayscale/print view before sharing to confirm they hold up without color.
Professional tips
- Use Insert → Sparklines for tiny inline trend lines inside KPI cards or table cells — no full chart object needed.
- Format Data Labels → Number Format to match the axis (e.g. $#,##0 or 0%) so labels read cleanly without extra digits.
- Right-click a chart → Change Series Chart Type to combine a column and a line in one combo chart (e.g. Sales bars + Margin % line).
- Use Chart Design → Switch Row/Column when a chart looks backwards — no need to rebuild it from scratch.
- Save a fully-styled chart as a template (right-click → Save as Template) to make every future chart match instantly.
- Hide the raw data sheet before sharing a dashboard workbook so recipients focus on the polished view.
Practice exercises
Rebuild a pie chart as a bar chart
Take any dataset with 6+ categories, build a pie chart, then rebuild the same data as a sorted horizontal bar chart and compare how much faster the ranking is to read.
Declutter an existing chart
Take a default Excel chart with gridlines, a legend, and 3D effects on, and strip it down to a clean, single-color, data-labeled chart with no legend.
Build three KPI cards with sparklines
Using a sample sales dataset, build three KPI cards (Total Revenue, Average Order Value, Order Count), each with a formula-driven big number and a sparkline showing the trend by month.
Assemble a one-screen dashboard
Combine your KPI cards from the previous exercise with one trend chart and one category comparison chart on a single sheet, then hide gridlines and define a print area around just the dashboard.
Review questions
How should you decide which chart type to use?
Start from the specific question the chart needs to answer (trend, comparison, part-to-whole, relationship, or distribution) and choose the chart family that matches that question, rather than picking a chart type first.
Why are pie charts discouraged for more than 4-5 categories?
Humans compare angles and areas far less accurately than bar lengths, so pie charts with many similarly-sized slices make it hard to judge which category is actually larger.
What are the three components of a good KPI card?
A large bold formula-driven number, a small descriptive label underneath, and often a Sparkline showing the recent trend in miniature.
What turns a collection of charts into a real dashboard?
A clear layout hierarchy (KPIs on top, primary charts in the middle, detail below) and a single shared filter mechanism, like a connected Slicer or Timeline, controlling every chart at once.
Why should you strip gridlines and 3D effects from charts before presenting them?
They add visual noise and, in the case of 3D, actively distort the perceived size of data points, without adding any real information.
How do you make one chart element stand out among several similar series?
Apply a single accent color to the series or category that matters and set the rest to a neutral gray, directing the viewer's eye immediately.
Key takeaways
- Pick chart type based on the question being answered, not by default habit.
- Line charts show trend, column/bar charts show comparison, 100% stacked charts show composition, and scatter charts show relationships.
- Strip gridlines, 3D effects, and unnecessary legends from every chart before sharing.
- KPI cards combine a big formula-driven number, a label, and a sparkline for a compact status view.
- A real dashboard has a layout hierarchy and one shared filter control, not just several unrelated charts.
- Excel Tables are the stable foundation that keeps charts and dashboards accurate as data grows.
- Sort categorical comparisons largest-to-smallest for instant readability.
Frequently asked questions
When is a pie chart actually appropriate?
Only for a true part-to-whole story with 2-4 categories where the largest slice is the clear focal point — beyond that, a sorted bar chart communicates better.
What's the difference between a Sparkline and a full chart?
A Sparkline is a tiny inline trend line that fits inside a single cell, ideal for KPI cards or table rows, while a full chart is a separate object with axes, labels, and legends for deeper analysis.
Should I use a secondary axis for two metrics with very different scales?
It's acceptable but risky — label both axes clearly and color-code them to their series, or consider two separate charts if the audience isn't chart-literate.
How many charts should a single dashboard sheet have?
Generally 3-5 well-chosen visuals plus a handful of KPI cards; more than that usually means the dashboard is trying to answer too many questions at once.
Can I make a dashboard interactive without using Pivot Tables?
Yes, using regular Tables with a Slicer (available on Tables in modern Excel) combined with dynamic formulas like FILTER, though Pivot-based dashboards remain the most common and robust approach.
What's the fastest way to make an existing chart look more professional?
Remove gridlines and 3D effects, apply one accent color, add direct data labels, and delete a legend that's no longer necessary — these four changes alone transform most default charts.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced