Excel Charts: Chart Types, Formatting, and Fixing Common Problems
Choosing the wrong chart type confuses your audience. Choosing the right one and formatting it well makes your data speak for itself. Here is what to pick and how to make it look professional.
Quick Create
Select your data (including headers) → Insert → Recommended Charts → pick from Excel's suggestions → click OK. Then format: remove the border, lighten the gridlines, increase the font size, and write a descriptive title. That alone puts your chart above 80% of what gets sent in a deck.
Which Chart Type to Use
Use when
Comparing values across categories at one point in time
Example
Sales by product in Q3
Avoid when
More than 6–7 categories — the chart becomes crowded
Use when
Showing part-to-whole composition across categories
Example
Revenue breakdown by region and segment
Avoid when
100% stacked if the total matters — it hides absolute values
Use when
Showing a trend over time with continuous data
Example
Monthly revenue over 24 months
Avoid when
Unordered categories — a line implies sequence
Use when
Showing cumulative totals or volume over time
Example
Cumulative signups over the year
Avoid when
Multiple series that overlap — they obscure each other; use a line chart instead
Use when
Showing a single series as percentage of a whole — 3–5 slices maximum
Example
Market share by competitor (5 companies)
Avoid when
More than 5 slices, or when comparing multiple pies — use a bar chart instead
Use when
Showing the relationship between two numeric variables
Example
Ad spend vs. conversion rate across campaigns
Avoid when
Time series — use a line chart for time on the X axis
Use when
Showing two series on the same chart that have different scales or types
Example
Revenue (bars) and profit margin % (line on secondary axis)
Avoid when
More than two Y-axes — beyond two scales the chart becomes unreadable
Use when
Showing how positive and negative values contribute to a net total
Example
Starting balance → additions → deductions → ending balance
Avoid when
Simple comparisons — a bar chart is cleaner when you do not need the running total
6 Formatting Changes That Make Any Chart Look Better
Remove chart borders and background fills
Format Chart Area → Fill → No fill; Border → No line. Floating charts on a white sheet look cleaner without a box.
Remove gridlines or make them very faint
Click a gridline → Format Gridlines → Solid line → Colour → light grey (#E5E7EB or similar). Heavy gridlines fight the data.
Increase font size on all text
Default chart fonts are too small to read in a presentation. Set axis labels to at least 11pt and data labels to 10pt.
Use colour to highlight, not to differentiate
Make all bars the same neutral grey, then colour only the one bar that represents your key finding. The eye goes straight to what matters.
Add a descriptive title that states the insight
Not "Sales by Quarter" but "Q4 Sales Up 23% YoY". A chart title that states the conclusion is more useful than one that just names the axes.
Add direct labels instead of a legend where possible
For line charts with 2–3 series, add a data label to the last point of each line with the series name. Eliminates the eye-movement between legend and chart.
Fixing Common Chart Problems
Chart not updating when data is added
Format source data as a Table (Ctrl+T). Charts referencing a Table auto-expand. If using a fixed range, right-click the chart → Select Data → expand the range manually.
Y-axis starts at a misleading value
Right-click the Y-axis → Format Axis → Bounds → set Minimum to 0 (or to a value that is honestly representative). A non-zero minimum exaggerates differences.
Wrong chart type chosen automatically
Right-click the chart area → Change Chart Type → pick the correct type from the gallery. Or use Insert → Recommended Charts on the original data selection.
Data series plotted in the wrong direction (rows vs columns)
Chart Design tab → Switch Row/Column. Excel sometimes guesses wrong about whether your series run across rows or down columns.
Legend is cluttered or missing
Click the chart → Chart Elements (+) → Legend → choose position. Delete the legend entirely if only one series — the chart title should describe it.
Dates on X-axis shown as numbers
The date column is stored as text, not real dates. Convert the column: select it → Data → Text to Columns → Finish. Then the axis formats correctly.
Chart looks different when printed or exported
Set the chart to a fixed size: right-click the chart → Format Chart Area → Size & Properties → lock aspect ratio and set exact height/width. For consistent exports, use File → Save As → PDF.
Dynamic Charts That Update Automatically
The most reliable way to build a chart that automatically includes new data: format your source data as an Excel Table (Ctrl+T) before inserting the chart. When you add rows to the table, the chart updates on the next recalculation. No manual range adjustment ever needed.
For dashboards where the chart should reflect filter selections, use a pivot chart — it is linked to a pivot table and updates as you filter or change slicers.
Secondary Axis — When and How
A secondary Y-axis is useful only when you have two series with different scales that you want to display on the same chart — for example, revenue (in thousands) and profit margin % (0–100%). To add one: right-click a data series → Format Data Series → Secondary Axis. Then right-click the secondary axis → Format Axis → set the scale to match your second series' range.
If the two series have the same unit, use a single axis. Two axes with the same unit create an opportunity to mislead — the scales can be tuned to make differences look larger or smaller than they are.
Frequently Asked Questions
How do I create a chart in Excel?
Select your data including headers → Insert → Recommended Charts (or choose a chart type directly) → OK. The chart appears in the sheet. Use Chart Design tab to customise.
Why is my Excel chart not updating when I add data?
Format your source data as a Table (Ctrl+T) first. Charts referencing a Table auto-expand when new rows are added. Otherwise right-click the chart → Select Data and widen the range.
What is the difference between a bar chart and a column chart in Excel?
Column = vertical bars. Bar = horizontal bars. Use column for time series, bar when category labels are long or you are ranking items.
Related Guides
Pivot Tables Guide
Pivot charts link directly to pivot tables — the most flexible way to build dynamic charts.
Read →
Excel Tables Guide
Format data as a Table before charting so charts auto-update when data grows.
Read →
Conditional Formatting
Use conditional formatting for in-cell visualisation — no chart needed for simple comparisons.
Read →
Excel Formulas Hub
Calculated fields and helper columns power the most flexible charts.
Read →