How To Add Extra Data Ranges To An Excel Chart

Tue 23rd August 2011

Once you've become familiar with creating charts in Excel, you may need to be able to change or add to the data source. This is straightforward if the data is simply an additional part of the original data, but what if the data is apart from the original? This article describes how to add data to an existing chart even if the data is away from the original table.

Once you've become familiar with creating charts in Excel, you may need to be able to change or add to the data source. This is straightforward if the data is simply an additional part of the original data, but what if the data is apart from the original? This article describes working with data sources for charts and describes how to add data to an existing chart even if the data is away from the original table.

Once you build a chart in Excel, the application obligingly adds a blue colour zone around the original data when the chart is selected. This blue zone looks like the selection which appears around a selected image or shape and you can move or resize this blue zone in a similar way. So if the blue zone is highlighting the wrong data in the original table, or you just want to change it, then you can hover over the surrounding edge of the blue zone until the cursor becomes a four way arrow. In Excel the four way arrow always means a move, so you can drag the zone over different cells, unselect the chart, and the chart updates.

Alternatively you can resize the blue zone by hovering over one of the four corners until the cursor changes to a two way arrow. Then you can drag the mouse to enlarge or contract the zone and then update the chart as before. You can also copy a chart and paste another version elsewhere, and then in the other chart change its source data. The second chart will then have exactly the size and formatting as the first but will show different data.

However suppose the extra data you want to add to your chart is further away from the chart. Provided the data is in the same relative row position but away from the chart, you can add one or more data ranges by editing the data range with one or more comma separator between the ranges.

For example suppose you want to add an extra columns of data several columns to the right of the original data which your chart is based on. You do this by selecting the chart and then editing the data range. In the data range box you click to the right of the existing range, type a single column, then drag the new data. The extra range will appear in the data range box after the comma. You can do this for several extra ranges provided all are in the same relative row position, as the chart still uses the original data structure from the first table.

You can do the same thing if the extra data is, say, several columns under the original data. The method is the same, just select the chart then choose the option to edit the data range. In the data range box add a comma, followed by the new range.

A consequence of adding more than one data range to your chart in this way is that the blue zone will not appear when the chart is selected. This only shows when the data source is continuous. However if you try changing values in the original or additional data you'll see that the data still drives the chart.

You may think that adding data to a chart in this way is similar to adding new data series, but in fact we are extending all the data series rather than adding new ones.

Just to finish off, here's an interesting example. Suppose you want to use a line chart to show off your weekly sales figures throughout the year. So you create a table two rows by fifty two columns. The top row contains the week numbers and the bottom row the actual sales figures.

Unfortunately the table is very awkward to add data to as you end up with lots of scrolling to the right and left as you enter or edit data. So instead you create one table four rows by thirteen columns. You are basically breaking up the data into four easy to reach parts. You then create the 3D line chart based on four data ranges separated by commas. This way you can see all the fifty two week numbers and sales cells without the need for constantly scrolling, and your impressive 3D line chart interprets the data as a continuous source. You need to do the same for the horizontal axis data range and use comma separators to add all the axis values, so all the week labels now show correctly in the chart.

Interested in learning more about charts in Excel? You might consider attending one of the many practical training courses available. By doing this you can learn a great deal in a relatively short time.

Author is a freelance copywriter. For more information on excel courses, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1875-how-add-extra-data-ranges-excel-chart.html