Excel Column Chart With Two Data Sources: How to Build One

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365, you can put two data sources in one column chart by adding each source as a separate data series through Chart Design > Select Data > Add. If the sources use different category lists, first align them in a shared table; Excel positions series by category labels and order, so unmatched rows can otherwise produce misleading comparisons.

What “two data sources” means in a chart

A chart’s data source is the range or ranges Excel uses to draw it. A column chart can contain multiple series, and each series can refer to a different range. For instance, one series might contain sales from a regional worksheet and another might contain sales from a separate report. They do not have to sit side by side in one original table.

For a useful comparison, each series normally needs values for the same set of categories. Categories might be months, products, departments, or locations. A clustered column chart displays the series beside one another for each category; a stacked column chart adds their values together visually, which is better for showing composition than comparing individual values.

“Two data sources” can also mean two workbooks, two database extracts, or two tables that use different labels. Those cases are not identical. Adding two ranges to a chart is straightforward; reconciling different category names, time periods, or units takes extra preparation.

Choose a layout before creating the chart

Decide what one column group should represent and what each series should measure. For example, if the goal is to compare two stores’ monthly revenue, use months as categories and Store A and Store B as series. A small, consistent layout makes the chart easier to maintain:

  • Use one category column or row, such as Q1, Q2, and Q3.
  • Give each source a clear series name, such as North and South.
  • Keep units consistent. Do not compare dollars in one series with units sold in another as though their column heights were directly comparable.
  • Decide how to represent missing categories before charting. A missing value is not necessarily the same as zero.

If the sources have different category names for the same thing, standardize them first. For example, “Jan” and “January” should be made consistent if they refer to the same month. Excel does not infer that two differently named labels mean the same category.

Add two ranges to a column chart

When the sources are in the same workbook, they can be separate ranges or ranges on separate worksheets. These steps use Excel for Microsoft 365 on Windows:

  1. Select the first source, including its series heading and category labels. If the source has categories in column A and values in column B, select both columns of the relevant rows.
  2. Choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column.
  3. Click the chart, then choose Chart Design > Select Data.
  4. In the Select Data Source dialog, under Legend Entries (Series), choose Add.
  5. In Edit Series, enter or select the second source’s heading for Series name and its numeric cells for Series values. Do not include the heading cell in the values range.
  6. Under Horizontal (Category) Axis Labels, choose Edit if needed, then select the category labels you want the chart to use. Choose OK to close the dialogs.

For example, suppose a worksheet named North has quarters in A2:A4 and sales in B2:B4, while a worksheet named South has quarters in D2:D4 and sales in E2:E4. The chart can use North!B2:B4 as one series and South!E2:E4 as the other, with one common quarter-label range. Include the worksheet name when selecting a range on another sheet; Excel will insert the reference for you. The series names can come from the heading cells, such as North!B1 and South!E1.

Excel charts expect the series values to correspond to the category positions. Make sure both series have the intended number and order of values. If the dialog’s range references are difficult to edit, use the range-picker button beside a field, navigate to the source sheet, select the cells, and return to the dialog.

Align sources with different categories

If one source has Q1, Q2, and Q3 while another has only Q1 and Q3, putting the short list directly into the chart can shift its values into the wrong positions. For reliable comparison, create a helper table with the complete category list and one column per source. The blank or missing category then occupies the correct row.

For example, put Q1, Q2, and Q3 in A2:A4. Put Source A’s categories in D2:D4 and values in E2:E4: 120 for Q1, 150 for Q2, and 135 for Q3. Put Source B’s categories in G2:G3 and values in H2:H3: 90 for Q1 and 110 for Q3. With those cell locations, enter this in B2 and fill down through B4 to retrieve Source A’s value for each category: =XLOOKUP(A2,$D$2:$D$4,$E$2:$E$4,NA()). The first result is 120.

With the same category layout and Source B ranges G2:G3 and H2:H3, enter this in C2 and fill down through C4: =XLOOKUP(A2,$G$2:$G$3,$H$2:$H$3,NA()). The results are 90, #N/A, and 110. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Here, #N/A deliberately marks a category with no matching source value; it avoids falsely presenting the missing Q2 value as zero. Excel charts generally do not plot #N/A values as ordinary columns. Check the finished chart to confirm the gap is displayed as intended.

Once the helper table is complete, select its category and value columns and insert the chart. This approach is often safer than trying to make a chart reconcile two lists independently, because the aligned table makes the mapping visible and easy to check.

Use sources in separate workbooks

A chart can refer to data in another workbook, but the external link adds a maintenance dependency. The source workbook may need to remain available at the expected location for Excel to refresh linked values reliably. Moving, renaming, or deleting that file can lead to broken links or update prompts. If the data is important, keep the files in a stable shared location and test the chart after reopening the destination workbook.

For recurring reports, consider bringing the source data into one workbook before charting. You can copy the values into a controlled helper table, or use Power Query to import and combine files when the sources follow a repeatable structure. Power Query is useful when the data must be refreshed regularly; a one-time chart may not justify setting up a query. In either case, verify that the imported categories and units still match before relying on the chart.

Format the comparison so it is readable

After adding both series, give the chart a descriptive title and check the legend. A title such as “Quarterly sales by region” states both the measure and comparison. The legend should use meaningful names rather than generic labels such as Series 1 and Series 2. If Excel has interpreted rows and columns in the opposite direction, use Chart Design > Switch Row/Column and review the result.

Use clustered columns when the reader should compare the two values within each category. Use stacked columns when the total and each category’s contribution are the focus; remember that only the bottom series shares a common baseline, making comparisons of upper segments harder. If the series have very different magnitudes or different units, a single column scale may hide the smaller series. Consider separate charts or a combo chart rather than adding a second scale without explanation.

Keep the vertical axis honest and legible. Column charts are usually easiest to interpret when their value axis starts at zero, because column height represents magnitude. Add data labels only when the exact values are important and the chart remains uncluttered. For many categories, a wider chart or a bar chart may be easier to read than narrow columns.

Common problems and how to fix them

  • The second series does not appear: Reopen Chart Design > Select Data and inspect the series reference. Confirm it includes numeric cells, points to the intended worksheet, and excludes the heading row from the values.
  • Bars appear under the wrong labels: Check the category-axis label range and the order of both value ranges. Excel aligns chart points by position in the series, not by independently matching category names for every series.
  • Some columns are missing: Look for blank cells, text stored where numbers are expected, errors, or filtered/hidden data. Also confirm that the chart range includes all intended rows.
  • The chart changes when rows are added: Check whether the chart’s source range includes the new rows. Excel tables can help charts expand with appended data, but confirm the chart references after changing the table or its layout.
  • The values look wrong despite a valid chart: Confirm that the two sources use the same time period, currency, measurement units, and definition of the metric. Chart formatting cannot correct inconsistent source data.

When another approach is better

A single chart is not always the clearest answer. If the sources have unrelated categories, different units, or very different time coverage, separate charts can prevent an invalid visual comparison. If the key question is how one measure changes over time, a line chart may communicate the trend more clearly than columns. If you need to compare many categories, a bar chart can make long labels easier to read.

For a repeatable report with many files or frequent updates, use a consolidated table or a Power Query workflow and build the chart from the resulting clean dataset. That separates data preparation from presentation. For a quick, static comparison, manually adding a second series is usually simpler. Choose the least complicated method that keeps the categories and values trustworthy.

Frequently Asked Questions

Can an Excel chart use two different worksheets?
Yes. Add the second worksheet’s values as another series in Chart Design > Select Data > Add, then set the category labels to the shared category range. Check that each series’ values correspond to the same category order.

Can I chart data from two separate Excel files?
Yes, by adding a series that refers to the other workbook. Because the chart depends on an external link, keep the source file in a stable location and check that the link updates after the files are moved or reopened.

Do the two sources need identical category labels?
They need a consistent category mapping for a fair comparison. If one source lacks a category or uses different labels, make a shared category list and align both sources to it before charting.

Should I use a clustered or stacked column chart?
Use clustered columns to compare the series side by side for each category. Use stacked columns to show how series contribute to a total. If comparing the individual values is the main goal, clustered columns are usually clearer.

Why are my columns assigned to the wrong categories?
Excel places values according to their position in each series and the chart’s category-label range. A differently ordered or shorter source can shift the apparent match. Align the values in a helper table and verify the axis-label range.

Related

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.