How to Use Copilot in Excel to Build a Waterfall Chart From Variance Data

You have a table of actual versus budget figures and need a waterfall chart that shows how each variance contributes to the final total. Building this chart manually requires sorting data, calculating running totals, and inserting invisible placeholder series. Copilot in Excel can generate the chart directly from your variance data without manual setup. This article explains how to structure your data, write the correct Copilot prompt, and adjust the chart for clear financial reporting.

How Copilot Handles Waterfall Charts and What Data It Needs

Copilot in Excel uses the same chart engine as the manual Insert Chart dialog. A waterfall chart shows how a starting value increases or decreases through a series of intermediate steps to reach a final total. For variance analysis, the starting value is often zero or a baseline budget figure. Each variance category appears as a floating column that either adds or subtracts from the running total.

Copilot cannot generate a waterfall chart from raw transaction data. You must first aggregate your data so that each row represents one variance category. For example, if you have monthly sales figures, sum them by category before asking Copilot to create the chart.

The table must contain at least two columns: one for category names and one for numeric variance values. A third column for a running total is optional but helps Copilot identify the waterfall structure. If your data includes a row named “Total” or “Net,” Copilot will treat that row as the final total bar and display it in a different color.

Recommended Table Layout for Variance Data

Arrange your data in this order:

1. Category column
   Each variance driver gets its own row. Examples: “Revenue Variance,” “Cost Variance,” “FX Impact.”
2. Variance column
   Positive values represent favorable variances. Negative values represent unfavorable variances.
3. Running total column (optional)
   A cumulative sum of the variance column. Copilot uses this to place the floating bars correctly.

If you include a “Total” row at the bottom, set its variance value to zero. The waterfall chart will then show the total as a full column that touches the axis, distinguishing it from the intermediate floating bars.

Steps to Create a Waterfall Chart in Excel Using Copilot

1. Select the data range
   Click anywhere inside your variance table. Copilot works on the active range, so ensure the table includes headers and all data rows. If your data is formatted as an Excel table, press Ctrl+T to convert it first.
2. Open the Copilot pane
   Click the Copilot icon on the Home tab of the ribbon. The pane opens on the right side of the Excel window.
3. Type the chart prompt
   In the Copilot text box, enter: “Create a waterfall chart from this data.” If you want to specify the variance column, type: “Insert a waterfall chart using the Variance column as the values.” Press Enter or click the Send arrow.
4. Review the generated chart
   Copilot inserts a new chart object on the active sheet. Examine the floating bars to confirm each variance category appears correctly. Positive variances should rise above the horizontal axis. Negative variances should fall below it.
5. Adjust the total bar color
   Click the chart to select it. On the Chart Design tab, click Change Colors and pick a palette that distinguishes the total bar from the intermediate bars. Alternatively, right-click the total bar, select Format Data Point, and choose a solid fill color such as dark blue.
6. Clean up the axis and gridlines
   Click the chart, then click the Chart Elements button the plus sign next to the chart. Uncheck Gridlines. Right-click the vertical axis and select Format Axis. Set the Bounds Minimum to zero if all variances are positive. If negative variances exist, let Excel auto-scale the axis.

If Copilot Does Not Create a Waterfall Chart

Copilot may generate a stacked column chart instead of a waterfall chart. This happens when the data structure does not clearly indicate a running total. To fix this, add a running total column to your table and re-prompt Copilot with the request: “Create a waterfall chart using the Running Total column as the cumulative values.”

Copilot Returns an Error Message

If Copilot responds with “I cannot create that chart” or a similar error, check the following:

1. Data must be contiguous
   Copilot cannot process data that has blank rows or columns within the selected range. Remove any empty rows.
2. Headers must be text
   Ensure the first row of each column contains a text label. Numeric headers confuse Copilot and may cause it to treat the header as data.
3. Copilot license requirement
   Waterfall chart creation with Copilot requires a Copilot for Microsoft 365 license. The free Copilot in Bing cannot generate Excel charts. Verify your license under File > Account.

Copilot Creates a Chart with Incorrect Bar Heights

When the variance column contains both positive and negative values, Copilot may misplace the floating bars. For example, a negative variance might appear as a bar extending upward instead of downward. To correct this, manually edit the chart data series:

1. Right-click the chart and select Select Data
   In the dialog box, click the waterfall series and choose Edit.
2. Verify the series values range
   Ensure the Series values box points to the correct variance column. If the range includes the header cell, remove it.
3. Set the total bar manually
   If the “Total” row is not treated as a total, right-click the bar, select Format Data Point, and check the box “Set as total.”

Copilot in Excel vs Manual Waterfall Chart Creation

Item	Copilot in Excel	Manual Chart Creation
Time to create chart	10-30 seconds	2-5 minutes
Data preparation	Requires aggregated table with running total	Requires helper columns for invisible series
License needed	Copilot for Microsoft 365	Any Excel version with charting
Customization options	Limited to post-creation formatting	Full control over every element
Error handling	May misinterpret data structure	User controls all calculations

Manual creation gives you complete control but requires more steps. Copilot is faster for standard variance waterfall charts. If your data has complex logic, multiple totals, or custom subtotals, build the chart manually using the Insert Waterfall Chart button on the Insert tab.

You can now use Copilot in Excel to produce a waterfall chart from variance data in under one minute. Start by structuring your table with a category column and a variance column. Next, write a direct prompt such as “Create a waterfall chart from this variance data.” After the chart appears, adjust the total bar color and remove gridlines for a professional look. For advanced variance reporting, try adding a secondary axis for percentage variance or combining the waterfall chart with a data table beneath it.