Docs

Type to search guides, settings and shortcuts.
    Download

    Charts and sparklines

    Insert and recommend charts from 17 types, format titles, axes, legends, series, trendlines and error bars, move and resize charts, and add sparklines in cells.

    A chart draws the values of cells as a picture that updates when the values change. Sparklines are tiny charts inside cells, one per row. For charts built on a PivotTable, see PivotCharts.

    Insert a chart

    1. Select the data, including its headings. If you select a single cell, all the data on the sheet is used.
    2. Open Insert › Charts › Chart and choose a type.

    The chart is placed to the right of the data and selected, and the Chart tab appears. The status bar says what was added, for example Added a column chart of 3 series.

    How the data is read

    • If any cell in the first row is text, the first row holds the series names.
    • If the first data cell in the first column is text, the first column holds the category labels.
    • Every other column is a series.

    Some types show one series only: Pie, Doughnut, Histogram, Pareto, Waterfall, Funnel and Treemap. For these, only the first series is used, and the status bar says Added a … chart of the first series. A Bubble chart uses the second numeric column as the size of each bubble. A Waterfall chart treats its last value as the total.

    MessageWhat to do
    Select the cells to chart first.Select some data.
    A chart needs more than one cell. Select the data first.Select a block of cells.
    That selection is all headings — nothing to plot.Include the rows of values.
    That selection has labels but no numbers to plot.Include a column of numbers.

    Chart types

    The Chart menu lists these types, in groups:

    GroupTypes
    Column and barColumn, Bar
    Line and areaLine, Area
    Pie and doughnutPie, Doughnut, Sunburst
    X YScatter, Bubble
    StatisticHistogram, Pareto, Box & Whisker
    Waterfall and funnelWaterfall, Funnel, Stock
    Hierarchy and radarTreemap, Radar
    1. Select the data.
    2. Click the sparkle button in Insert › Charts (Recommended chart — picks the type that suits the shape of the selection).
    3. In Recommended charts, look through the suggestions, best fit first. Each shows a preview drawn from your data, the type, and a sentence explaining why it suits this data — for example, that the labels read as dates, so a line shows change over time.
    4. Click a suggestion to insert it, or click Cancel.

    The suggestions are based on the shape of the data: one column of many readings suggests a histogram, a few positive parts suggest a pie, labels that read as months or years suggest a line, two columns of numbers without labels suggest a scatter, and many categories suggest horizontal bars. A column chart is always offered.

    Select, move and resize a chart

    • Select: click the chart. Round handles appear on its corners and edges, and the Chart tab appears.
    • Move: drag the chart. Hold Alt as you release to line its top-left corner up with the cell grid.
    • Resize: drag a handle.
    • Nudge: press the arrow keys to move it a little; hold Shift to move it further.

    A move or resize is one undo step. Charts move with the cells under them when you insert or delete rows and columns.

    Delete a chart

    1. Click the chart.
    2. Click the bin in Insert › Charts (Delete the selected chart).

    The Chart tab

    ControlWhat it does
    Type menu (named after the current type)Changes the chart type. Changing to a pie or doughnut keeps only the first series.
    LegendPlaces the legend on the Right, Bottom, Top or Left, or None
    Data labelsShows or hides the value on each point
    Title…Opens Chart title. Type the title and click Apply.
    GroupingClustered, Stacked or 100% stacked — how several series sit together

    Format a chart

    1. Click the chart.
    2. Click the settings button in Insert › Charts (Format the selected chart — axis titles, series types, secondary axis, trendlines).
    3. Change the options in Format chart.
    4. Click Apply.

    Chart options

    OptionWhat it does
    TypeThe chart type, listed with its group
    Chart titleThe title above the chart
    Category axis titleThe title along the category axis
    Value axis titleThe title along the value axis
    Secondary axis titleThe title of the second value axis. Shown when a series uses it, and for Pareto charts.
    BinsFor a histogram: the number of bars, from 1 to 40, or Automatic
    GridlinesShows or hides the value gridlines
    Data labelsShows or hides the value on each point
    LegendNone, Right, Bottom, Top or Left

    Series options

    Under SERIES, each series is listed by name with its own options.

    OptionWhat it does
    Chart type (starts as Same as chart)Draws this series as Column, Bar, Line, Area, Scatter or Bubble, which makes a combo chart — columns for a count and a line for a rate, say
    2nd axisPlots the series against a second value axis, for values on a different scale
    Trendline (starts as No trendline)Adds a Linear, Exponential, Logarithmic, Polynomial, Power or Moving average trendline. Polynomial trendlines are second order and moving averages cover two points.
    EquationShows the trendline’s equation
    R²Shows how well the trendline fits
    Error bars (starts as No error bars)Adds Fixed value, Percentage, Standard deviation or Standard error bars
    Value or %For Fixed value and Percentage: the size of the bars
    DirectionBoth, Plus or Minus
    CapDraws a short line across the end of each bar

    Standard deviation and standard error bars describe the whole series, so every bar in the series is the same length.

    Charts follow their data

    A chart reads its cells each time it is drawn, so editing a value changes the chart straight away. Series keep their references: inserting rows inside the data extends them, and a chart’s position moves with the cells under it.

    Charts in workbooks made in other apps are drawn from their cells too. A chart you don’t change is saved exactly as it was read.

    PivotCharts

    A PivotChart plots a PivotTable and follows it: adding a field or changing the pivot redraws the chart from the new result.

    1. Click a cell inside the PivotTable.
    2. Open Insert › Charts › PivotChart and choose Column, Bar, Line or Pie.

    The chart is placed to the right of the PivotTable, with one series per value column and the grand total left out. The status bar says Pivot chart added. It follows the pivot rather than a fixed range.

    MessageWhat to do
    Put the cursor in a pivot table first.Create a PivotTable, or click inside one.
    This pivot has nothing to plot yet — give it a value field.Add a field to Values in the field list.
    This pivot already has a chart.Each PivotTable can have one PivotChart.

    See PivotTables.

    Sparklines

    A sparkline is a small chart drawn inside a cell, showing the trend of the values across a row.

    Add sparklines

    1. Select the rows of numbers, with at least two columns. A first column of labels is skipped.
    2. Click one of the sparkline buttons in Insert › Sparklines: line, column or win/loss (for example Line sparkline — one tiny chart per row of the selection, in the column beside it).

    One sparkline is added for each row that has numbers, in the column immediately to the right of the selection. The status bar says, for example, Added 6 line sparklines in column H. A sparkline takes the place of anything shown in its cell.

    KindWhat it shows
    LineA line through the values, with the highest and lowest points marked
    ColumnA column per value, with negative values and the axis shown
    Win/LossA block up for each positive value and down for each negative one
    MessageWhat to do
    Select at least two columns of numbers to draw a sparkline from.Select a wider block.
    That selection has labels but no numbers.Include the columns of numbers.
    No row in that selection has numbers to plot.Select rows that contain numbers.

    Change sparklines

    1. Click a cell that holds a sparkline.
    2. Click the settings button in Insert › Sparklines (Sparkline options). It is unavailable unless the current cell holds a sparkline.
    3. In the Sparklines dialog, which says how many sparklines are in the group, change the options.
    4. Click Apply.
    OptionWhat it does
    Line, Column, Win/LossThe kind of sparkline
    Highest point, Lowest point, First point, Last pointMarks those points
    Negative pointsMarks values below zero
    Every markerFor line sparklines: a marker on every point
    AxisDraws the zero line
    Same scale for every rowScales every sparkline in the group to the same range. Off, each row is scaled to its own values, which makes every row look the same shape whatever the numbers.

    The options apply to every sparkline in the group — all the sparklines added together.

    Remove sparklines

    1. Click a cell with a sparkline.
    2. Open Sparkline options and click Clear.

    The whole group is removed and the cells show their contents again. Sparklines are saved in the format Excel uses, so they appear in Excel.

    Something unclear or out of date on this page? Tell us.