Excel Line Chart Data Range: How to Set It

In Excel for Microsoft 365 on Windows, select the cells you want to chart—including the category labels and series headings—then choose Insert > Insert Line or Area Chart and pick a line chart. To change an existing chart’s range, select the chart and choose Chart Design > Select Data; edit the Chart data range box or adjust the highlighted source cells on the worksheet.

What an Excel line chart data range includes

A chart’s data range is the group of worksheet cells Excel uses to build its plotted categories and values. For a typical line chart, it contains one column or row of category labels, such as months, and one or more columns or rows of numeric values. Headings tell Excel what to call each series, so including them usually makes the chart easier to read.

For example, suppose A1:C6 contains a Month heading and five months in column A, then two product headings and their sales in columns B and C. Selecting A1:C6 gives Excel the month labels, two series names, and both sets of values. The chart data range box may show a reference like =Sheet1!$A$1:$C$6. That is a worksheet range reference used by the chart, not a formula to enter into a cell.

Usually, select one rectangular block with no unrelated data inside it. Leaving out a heading can cause Excel to use a generic name such as Series 1; including a totals row can add a misleading extra point. A well-organized source range is often the simplest way to get the chart you intend.

Arrange data before creating the chart

Lay out the source data so Excel can identify the categories and series. A common format puts categories down the first column and each series in its own column, with headings across the top. For example:

A1: Month; B1: Online; C1: Store
A2:A6: Jan through May
B2:C6: the corresponding monthly values

Select A1:C6 and insert the chart. Excel normally treats the first row as series names and the first column as category labels. If your data is arranged the other way around—with series in rows—Excel may guess the orientation incorrectly. In that case, select the chart and use Chart Design > Switch Row/Column to change how Excel interprets the same range.

Keep each series’ values aligned with the same categories. If one series has fewer observations, leave its missing values blank rather than shifting later values up a row. Otherwise, a value intended for April might be plotted against March. Store actual numbers as numbers and dates as recognized Excel dates; text that looks like a number or date may not behave as expected.

Create a line chart from a selected range

Put category labels and series headings next to the values, ideally in a single rectangular block.
Select the full range, including the headings. You can drag across the cells or click a cell in a continuous block and use Ctrl+A when Excel identifies the current data region; check the selection before proceeding, because adjacent unrelated cells can affect what gets selected.
Choose Insert > Insert Line or Area Chart, then select the line chart style you want. A standard 2-D Line chart is a practical starting point for values tracked across categories.
Review the result. Confirm that the horizontal labels and series names are correct, and that Excel has plotted the intended values.

If the data is on a worksheet with other content, selecting the range first is more reliable than inserting an empty chart and trying to repair its source later. You can still change the range at any time. Excel for Mac has a similar chart workflow; ribbon labels and dialog placement can differ slightly by release. Excel for the web also supports chart insertion and editing, though some detailed chart controls may be more limited than in the desktop app.

Change the range of an existing chart

Click the chart to show the chart tabs, then choose Chart Design > Select Data. In the Select Data Source dialog, the Chart data range box shows the chart’s current source. Replace or edit the reference and confirm, or use the range-selection button beside the box to return to the worksheet and select cells directly. The dialog also lists the chart’s series and category labels, which can be edited separately when only one part needs correction.

For a small adjustment, you can sometimes select the chart and drag the colored outline around its source data on the worksheet. This is convenient for extending the range by a row or column, but the Select Data dialog is clearer when you need to check exactly which cells are included. If you change the source range and the chart still looks wrong, inspect the series and category-label entries in that dialog rather than repeatedly selecting a larger block.

A chart can refer to data on another worksheet in the same workbook. If you type a reference manually, sheet names containing spaces need single quotation marks, as in ='Monthly Sales'!$A$1:$C$6. Selecting the cells with the range picker avoids many typing mistakes. When copying charts between workbooks, verify that the reference points to the intended workbook and sheet; a chart can otherwise remain linked to a different source than expected.

Choose the right range and chart orientation

The best range depends on what the line is meant to show. Use one category column and one numeric column for a single series, or add a numeric column for each series you want to compare. For example, monthly sales for three regions should have one Month column and three region columns. Do not include a separate explanatory note, an unrelated metric, or a grand total merely because it sits next to the data.

Line charts are suited to comparing values across an ordered sequence, often time. Excel treats the horizontal axis of a line chart as a category axis by default, so category positions are generally evenly spaced even if the labels are dates. If the actual elapsed time between dates matters—for example, measurements taken on irregular dates—a line chart may not communicate spacing accurately. An XY (Scatter) chart uses numeric horizontal values and may be a better fit for that purpose.

When a chart plots the wrong direction, try Chart Design > Switch Row/Column. If switching does not help, check that the range includes the correct heading row or column and that the data is laid out consistently. You can also use Select Data to remove an unwanted series or add the missing one without rebuilding the entire chart.

Make the range expand with new data

If you expect to add records regularly, convert the source block to an Excel table before building the chart. Select a cell in the data and choose Insert > Table, then confirm that the table has headers if it does. Create the chart from the table’s data. When you add a row directly beneath the table, Excel typically expands the table, and a chart based on it can include the new row automatically. Check the chart after adding data, particularly if rows are inserted outside the table or if the workbook has unusual layout or chart references.

A table is often easier to maintain than repeatedly editing a fixed range such as A1:C6. It also helps keep headings and formatting consistent as the dataset grows. However, a chart based on a table may include every table column unless you choose the desired fields carefully when selecting data. If the table contains notes, calculated helper columns, or unrelated measures, select only the fields intended for the chart or adjust the chart’s source afterward.

For a chart that should show only a changing window—for example, the latest 12 months—an expanding table alone may not provide that exact behavior: it can include older rows too. You can use a separate prepared range containing the records you want to display, or update the chart’s source when the window changes. Choose a method that makes the included dates obvious to anyone maintaining the workbook.

Handle blanks, hidden rows, and common errors

Blank cells can change the appearance of a line. Select the chart, choose Chart Design > Select Data, then select Hidden and Empty Cells. The available choices include showing empty cells as gaps, as zero, or connecting data points with a line. A gap signals missing data; zero says the value is actually zero; connecting points can imply a continuous trend despite missing observations. Choose the option that reflects what the data means, rather than just the smoothest-looking result.

If data is in hidden rows or columns, check the Hidden and Empty Cells settings in the same dialog. The option to show data in hidden rows and columns can make that data appear in the chart. This is useful when hiding rows is only a worksheet display choice, but not when hidden records should be excluded from the visual.

Wrong labels: Verify the category-label range in Select Data and confirm that the intended label cells are in the first row or column of the selected block.
Missing series: Check whether its values fall outside the current range, or whether Excel interpreted rows and columns in the wrong direction.
Unexpected zeroes or gaps: Inspect the source cells for blanks, formulas returning an empty string, or text values, then review the empty-cell display setting.
Extra point at the end: Check whether a total, note, or blank-looking row with content was accidentally included in the range.

Also check for filtered or hidden data when the chart appears to omit observations. The source range can be correct while chart settings determine whether hidden data is plotted. Conversely, a chart that includes an unexpected value may simply extend one row farther than the visible dataset.

Line chart versus other chart types

A line chart emphasizes a sequence of categories and makes it easy to compare trends across multiple series. It is often appropriate for months, days, or ordered stages. A column chart may make individual category values easier to compare, especially when there are only a few categories. An XY (Scatter) chart is preferable when the horizontal values are numeric measurements or irregularly spaced dates and their distances matter.

Excel also offers line chart variations, including lines with markers and stacked line charts. Markers help identify individual observations when there are not too many points. A stacked line chart emphasizes cumulative values and changes the meaning of the visual, so do not choose it if readers need to compare each series’ original values directly. Choosing a different chart type does not necessarily change the source range; you can change the chart type from the chart design controls while keeping its underlying data.

Frequently Asked Questions

How do I select the data range for a line chart in Excel?
Select the category labels, series headings, and numeric values, then choose Insert > Insert Line or Area Chart. For an existing chart, use Chart Design > Select Data to view or change its source range.

How do I add another series to an Excel line chart?
Put the new series heading and values in the worksheet, then open Chart Design > Select Data. Expand the chart’s data range to include the new cells or add the series from the dialog. Check that its values line up with the chart’s category labels.

Why is my Excel line chart showing the wrong data?
Excel may have selected an unintended range, treated rows as columns, or included an extra row or column. Open Select Data, inspect the series and category labels, and use Switch Row/Column if the layout was interpreted in the wrong direction.

How can I make a line chart update when I add rows?
Convert the source data to an Excel table with Insert > Table, then create the chart from that table. New rows added directly to the table are generally included. Confirm the chart updates in your workbook, especially if the added data is outside the table.

Should I include column headings in the chart range?
Usually, yes. Headings help Excel name each series, while the category labels identify the horizontal-axis points. Including them makes the chart easier to interpret; leave out unrelated headings, totals, and notes.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.