Data Analysis

How to Make Professional Charts in Excel (Step-by-Step Guide)

Q: What is the best chart type for my Excel data?

Use bar or column charts for comparisons across categories. Use line charts for trends over time. Use pie charts only for parts of a whole with 5 or fewer categories. Use scatter plots for relationships between two variables. Use combo charts when showing two different scales (e.g. revenue as bars + margin as a line). Use waterfall charts for showing how values build up or break down.

Q: How do I save a chart style so I can reuse it across workbooks?

Right-click the chart area → Save as Template. Excel stores it as a .crtx file in your user template folder. Next time you insert a chart, go to Insert → Charts → See All Charts → Templates tab to apply it. For teams, share the .crtx file and have each person drop it into C:\Users\[name]\AppData\Roaming\Microsoft\Templates\Charts on Windows or ~/Library/Application Support/Microsoft/Office/Chart Templates on Mac.

Q: Why does my Excel chart show straight lines or gaps in my time series?

Usually one of three things: dates are text-typed (fix: Data → Text to Columns → Finish to re-parse as real dates), some cells in the value range are empty (fix: right-click chart → Select Data → Hidden and Empty Cells → choose 'Connect data points with line' or 'Zero'), or the X-axis is treating dates as categories instead of a date axis (fix: right-click axis → Format Axis → Axis Type → Date axis).

Q: Should I use a pivot chart or a standard chart?

Use a pivot chart when you need slicer-driven filtering or the underlying data refreshes from a pivot cache (most dashboards). Use a standard chart when the data is static, already summarised, or when you want full formatting control — pivot charts reset some formatting on refresh. Pivot charts also inherit the parent pivot's sort order, so they can reshuffle unexpectedly when new categories appear.

By Sagnik Bhattacharya20 Mar 20269 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Creating Professional Charts and Visualisations in Excel, with bold charts, trend lines, and presentation-ready visuals.

A chart should communicate an insight in seconds. Most Excel charts fail because they use the wrong chart type, have too much clutter, or lack clear formatting. Here's how to create charts that actually tell a story.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

This tutorial is beginner-first and practical. We work through chart-type selection first (the decision that makes or breaks every chart before you even click Insert), then formatting rules that separate amateur from professional charts, then advanced patterns — combo charts, sparklines, dynamic ranges — that you'll reach for in real reporting work. Every technique shown works in Excel 2019, Excel 2021, Microsoft 365, and Excel for the web.

For the broader Excel skill stack, see the Excel formulas and analysis hub, which links every Excel tutorial on this site in a recommended reading order.

Choosing the Right Chart Type

For interactive data that changes with filters, consider using pivot charts built from pivot tables — they update automatically as you slice the data.

Data Story	Best Chart	Avoid
Comparison across categories	Bar/Column chart	Pie chart (if >5 categories)
Trend over time	Line chart	Pie chart
Part-of-whole	Pie/Donut (≤5 items)	3D charts
Distribution	Histogram	Bar chart
Correlation	Scatter plot	Line chart
Comparison + composition	Stacked bar chart	Multiple pie charts

Creating a Chart

Select your data (including headers)
Go to Insert → choose your chart type
Or press Alt+F1 for a quick default chart

Faster workflow: Select the data → press F11 to insert a chart on a brand new sheet (presentation-sized visuals), or Alt+F1 to embed a default chart on the current sheet. Use Ctrl+1 inside the chart to open the Format pane for the selected element — this shortcut saves tens of right-clicks per chart.

Beginner pitfall: If Excel puts the wrong categories on the X-axis (for example, plots each Year as a separate data series), click Switch Row/Column on the Chart Design tab. Nine times out of ten the chart is right but Excel guessed the orientation wrong. The fix is one button — no need to restructure the source data.

Real-world scenario: A product analyst shows conversion rate across 12 marketing channels. Their first attempt used a pie chart — 12 thin slices, impossible to rank, colour-coded from a rainbow palette. The rebuild: a horizontal bar chart sorted descending by conversion rate, single accent colour for the top performer, grey for the rest. Same data, completely different decision quality. The chart type alone changed how the team read the numbers.

Professional Formatting Rules

1. Remove Clutter

Delete gridlines (or make them very light grey)
Remove chart borders
Remove the legend if there's only one data series
Delete the chart title if you're adding your own text box

2. Use Colour Intentionally

Use one accent colour for the main data, grey for everything else
Highlight the key insight with a contrasting colour — for in-cell colour coding, see the conditional formatting guide
Never use more than 5-6 colours in one chart
Avoid rainbow colour schemes — they don't convey meaning

3. Format Numbers

Abbreviate large numbers: "50K" not "50,000"
Remove unnecessary decimal places
Add currency symbols or percentage signs to axis labels

4. Add Context

Add a clear, descriptive title that states the insight: "Revenue grew 23% YoY" not just "Revenue"
Add data labels to the most important bars/points
Add reference lines for targets or averages

Chart Formatting Shortcuts

Double-click any chart element to format it
Click a chart → use the + icon to add/remove elements (title, legend, data labels, gridlines)
Right-click axis → Format Axis → change min/max values, number format
Use Chart Design tab → Change Colours for predefined palettes

Advanced Techniques

Combination Charts

Show two data series with different scales — e.g., revenue as bars on the primary axis, and profit margin as a line on the secondary axis. Right-click a series → Change Series Chart Type → check "Secondary Axis".

Sparklines

Tiny inline charts that fit inside a single cell. Insert → Sparklines → choose Line, Column, or Win/Loss. Perfect for showing trends in tables without full-sized charts. They also work brilliantly as trend indicators when building interactive dashboards.

Dynamic Chart Range

Use an Excel Table as your chart data source. When new rows are added to the table, the chart automatically expands to include them. For more advanced auto-updating data sources, you can use Power Query to import and transform data before charting.

Save and Reuse a Chart Style

Spending 20 minutes formatting one chart and then repeating it by hand ten times is the classic beginner time-sink. Right-click a finished chart → Save as Template. Excel writes a .crtx file you can apply in two clicks next time: Insert → Charts → See All Charts → Templates tab. On shared-team workbooks, drop the .crtx into %AppData%\Microsoft\Templates\Charts and everyone can apply the company chart style.

Reference Lines for Targets and Averages

To show a target line on a bar chart, add the target as a second data series (one value repeated down every row), insert the chart as normal, then right-click the target series → Change Series Chart Type → Line. Set the line to dashed red and remove its markers. The same trick works for industry-average benchmarks, moving averages, or last-year comparison. This one move is what separates "a chart" from "a chart that tells a story".

Real-world scenario: A finance team builds a 15-chart monthly performance pack. Before saving the chart template, each rebuild took 4 hours of formatting. After: 25 minutes. The saved template is a hidden ROI multiplier on recurring report work.

Beginner pitfall: Pivot charts reset formatting every time the underlying pivot refreshes unless you tick "Preserve cell formatting on update" in PivotTable Options. Even then, chart colours sometimes revert on slicer clicks. If you are investing heavy formatting time, use a standard chart sourced from a helper table (linked to the pivot via GETPIVOTDATA) rather than a pivot chart directly.

Common Chart Mistakes

3D charts — They distort data perception. Always use 2D.
Pie charts with too many slices — Maximum 5. Use a bar chart for more categories.
Truncated axes — Starting a bar chart at a value other than 0 exaggerates differences.
Missing labels — Every chart needs a title, axis labels, and a data source.
Line chart for categorical data — Lines imply continuity. For distinct categories (regions, products), always use a bar chart, never a line.
Dual-axis everywhere — Dual axes suggest correlation that may not exist. Use them only when the two series genuinely share a time dimension and different units.

Troubleshooting

Five problems that trip up nearly everyone when charts are first rolled into reports, with exact fixes.

Chart shows straight lines across date gaps instead of breaks. Empty cells in the value column render as zero by default. Right-click chart → Select Data → Hidden and Empty Cells → choose "Gaps" to show a break or "Connect data points with line" to bridge cleanly. Pick one behaviour and apply it consistently across every chart in a report.
X-axis dates show as "1", "2", "3" instead of real dates. Dates are text-typed. Select the date column → Data → Text to Columns → Next → Next → Date format → Finish. Excel re-parses them as proper dates and the axis updates on refresh. If the axis still looks categorical, right-click → Format Axis → Axis Type → Date axis.
Chart lost its formatting after refreshing the pivot. PivotChart refresh resets formatting. Right-click the parent pivot → PivotTable Options → Layout & Format → tick "Preserve cell formatting on update" and untick "Autofit column widths on update". For stubborn cases, save a chart template and reapply after each refresh — or switch to a standard chart sourced from a helper table.
Chart is empty or shows "No data to display". The data range has been deleted or the named range the chart points at no longer resolves. Right-click chart → Select Data → check the Chart data range. For Table-sourced charts, confirm the Table name still exists on Formulas → Name Manager. Restore or re-point the range.
Axis numbers show 1,000,000 instead of 1M. Format Axis number formats are per-chart and don't inherit from the cells. Right-click axis → Format Axis → Number → custom format: #,,"M" for millions or #,"K" for thousands. Saving as a chart template preserves this format for reuse.

Frequently Asked Questions

How do I make professional charts in Excel?
Start with clean source data, choose the right chart type for your data story, then customise: remove chart junk (gridlines, default legends), use a consistent colour palette with one accent and grey for secondary data, add direct data labels, use titles that state the insight ("Revenue grew 23% YoY" not "Revenue"), and align fonts with your presentation style.

What is the best chart type for my Excel data?
Bar or column for category comparisons. Line for trends over time. Pie only for parts of a whole with ≤5 categories. Scatter for relationships between two variables. Combo charts for two scales (revenue bars + margin line). Waterfall for build-up/break-down flows.

How do I save a chart style so I can reuse it across workbooks?
Right-click the chart → Save as Template. Excel saves a .crtx file. Apply via Insert → Charts → See All Charts → Templates tab. Share with your team by dropping the file into %AppData%\Microsoft\Templates\Charts on Windows or ~/Library/Application Support/Microsoft/Office/Chart Templates on Mac.

Why does my Excel chart show straight lines or gaps in my time series?
Three common causes: dates are text-typed (Data → Text to Columns → Finish), empty value cells (right-click → Select Data → Hidden and Empty Cells → pick Gaps/Connect/Zero), or category-axis instead of date-axis (right-click axis → Format Axis → Axis Type → Date axis).

Should I use a pivot chart or a standard chart?
Pivot chart for slicer-driven filtering or pivot-cache data (most dashboards). Standard chart for static/pre-summarised data or when you need formatting to stick perfectly — pivot charts reset some formatting on refresh.