A chart turns rows of numbers into a visual that communicates instantly. This guide covers picking the right chart type, building it from scratch, and formatting it to look professional — no design experience needed.
Chart Type Overview: Which to Use and When
Choosing the wrong chart type is the most common mistake. Here's a quick decision guide:
| Chart Type | Best For | Avoid When |
| Column | Comparing categories side-by-side | Too many categories (10+) |
| Bar | Same as column but horizontal; better for long category names | Showing time series |
| Line | Trends over time with continuous data | Comparing unrelated categories |
| Pie | Part-to-whole relationships with 5 or fewer slices | You have more than 6 categories or need to compare two series |
| Scatter (XY) | Correlation between two numeric variables | Either axis is categorical |
| Area | Cumulative totals over time | Values overlap and overlap meaning is unclear |
| Combo | Two related metrics with different scales on the same chart | Series have the same unit/scale |
Create a Basic Chart in 3 Steps
Step 1: Select Your Data
Select the headers and data you want to chart. For a column chart of monthly sales:
- Select A1:B13 (Month column + Sales column, including headers)
- Non-adjacent columns: hold Ctrl while selecting
Step 2: Insert the Chart
Insert tab → Charts group → choose a chart type.Or use the recommended charts feature: Insert → Recommended Charts. Excel analyzes your data and suggests the most appropriate chart types. Click each thumbnail to preview it.
For the fastest result: select your data and press Alt+F1 to insert a default column chart on the current sheet, or F11 to create it on its own chart sheet.
Step 3: Position and Resize
Click and drag the chart to move it. Drag any corner handle to resize while maintaining proportions. Drag a side handle to resize in one direction only.
Column and Bar Charts
Column charts (vertical bars) are the workhorse of Excel charting.
Variations
- Clustered Column: Bars side-by-side for each category. Best for direct comparison between groups.
- Stacked Column: Bars stacked to show total and composition. Good for "how does the mix change over time".
- 100% Stacked Column: All bars the same height (100%) showing only proportions. Use when you care about share, not absolute values.
Tips
- Keep clustered column charts to 3-4 series maximum. More than that and the chart becomes unreadable.
- For time series with a large number of time points (50+ months), switch to a line chart instead.
Line Charts
Line charts connect data points with a continuous line, making them ideal for trends.
When to Use Lines
- Monthly/quarterly/yearly revenue, expenses, or KPIs
- Stock prices or any time-series measurement
- Comparing trends for multiple series over time
Smooth vs. Straight Lines
Right-click a line → Format Data Series → Fill & Line → check Smoothed line. Smoothed lines look cleaner but can misrepresent sharp changes. For accurate data, stick to straight lines.
Markers
Markers (dots, squares, triangles) at each data point help readers identify individual values. Right-click → Format Data Series → Marker Options → set type and size.
For presentations, markers often look cluttered. For analytical reports, they're useful.
Pie Charts
Pie charts show how parts make up a whole.
Golden rules for pie charts:- All slices must sum to 100% of a meaningful total
- Use 5 slices maximum — more and the chart becomes unreadable
- Sort slices from largest to smallest, starting at the 12 o'clock position
- Never use 3D pie charts (they distort the visual proportions)
Exploded Pie Chart
Click a slice once to select the whole pie, then click again to select just that slice. Drag it outward to "explode" it for emphasis.
Pie of Pie / Bar of Pie
For data with many small slices, Excel's Pie of Pie chart groups small slices into an "Other" slice on the main pie, then breaks that out into a second pie. Insert → Other Charts → Pie of Pie.
Scatter (XY) Charts
Scatter charts plot two numeric variables against each other to reveal correlations.
Example: Do marketing spend (X axis) and revenue (Y axis) correlate?- Select two columns of numeric data (no text headers required for X, though Excel handles it)
- Insert → Scatter → Scatter with Only Markers
Adding a Trendline
Right-click any data point → Add Trendline:
- Linear: Straight line — shows the general direction
- Exponential / Power: For data that grows at increasing rates
- Check Display R-squared value to see how well the line fits (R² = 1 is perfect fit)
- Check Display Equation on chart to show the formula
Chart Formatting: Titles, Labels, and Axes
Chart and Axis Titles
Click the chart once to select it. You'll see the Chart Design and Format tabs appear in the ribbon.
Chart Design → Add Chart Element → Chart Title → Above Chart or Centered Overlay.Double-click the title text to edit it inline. Don't just leave it as "Chart Title."
Axis Titles: Chart Design → Add Chart Element → Axis Titles → Primary Horizontal / Primary Vertical. Always label axes with units (e.g., "Revenue ($000s)" or "Month").Data Labels
Chart Design → Add Chart Element → Data Labels → placement options vary by chart type (outside end, inside end, center, etc.).For pie charts, choose More Data Label Options to show both category name and percentage on each slice.
Axis Formatting
Double-click any axis to open Format Axis:
- Bounds: Set minimum and maximum manually (default is auto-scaled). A Y-axis that starts at 0 is generally more honest.
- Units: Control the interval between tick marks
- Number format: Apply currency, percentage, abbreviated numbers (thousands/millions)
- Text direction: Rotate axis labels to prevent overlap on crowded category axes
Chart Styles and Design
Chart Design tab → Chart Styles gallery — click any style to apply it instantly. Styles control colors, backgrounds, and font treatment. Change Colors: Chart Design → Change Colors → choose a color palette. Palettes match your workbook's theme colors. Chart Layout presets: The Quick Layout gallery changes which elements appear (title, legend, data labels, gridlines) without changing the chart type.Moving to a Chart Sheet
Right-click the chart → Move Chart → New sheet. This places the chart on its own full-screen tab — useful for presentation-ready charts.
Formatting Individual Elements
Click any chart element (a specific bar, a single data label, the legend) to select it, then use the Format sidebar that appears on the right. Right-click → Format [element] also opens it.
Hold Ctrl and click multiple bars/lines to format several series at once.
Frequently Asked Questions
Why does my chart show the data in the wrong orientation (rows instead of columns)?
Excel guesses whether your data runs by rows or columns. If it guesses wrong, click the chart → Chart Design → Switch Row/Column. This transposes the series and category assignments.
How do I add a second Y-axis to a chart?
Right-click the data series you want on the secondary axis → Format Data Series → Series Options → select Secondary Axis. A second axis appears on the right side. Then right-click the chart → Change Chart Type → change that series to a Line to create a combo chart (e.g., bars for volume, line for price).
How do I prevent my chart from resizing when I insert or delete rows nearby?
Right-click the chart → Format Chart Area → Properties → select "Don't move or size with cells". This locks the chart's position and size independently of the worksheet grid.
Can I link chart title text to a cell?
Yes. Click the chart title to select it, then click in the formula bar and type = followed by the cell address (e.g., =Sheet1!$A$1). The title will display the contents of that cell and update automatically when the cell changes.
How do I copy a chart's formatting to another chart?
Select the source chart → Ctrl+C. Select the destination chart → Paste Special → Formats. This pastes only the formatting, not the data. Alternatively, right-click the formatted chart → Save as Template (.crtx file), then right-click any chart → Change Chart Type → Templates to apply it.