To add a formula’s results to an Excel graph, enter the formula in a worksheet column, fill it down for each category, then add that column as a chart series. If you mean showing a fitted equation on the graph, add a trendline and turn on its equation display. The steps below cover both methods in Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with category labels and numerical values arranged in columns.
- An existing chart, or data ready to turn into a chart.
- A clear idea of what the formula should calculate, such as a percentage adjustment, forecast, or average reference line.
For the main example, imagine months in A2:A5 and sales in B2:B5. The sales figures are 10, 20, 30, and 40. A rate of 10% is in E1. You will calculate adjusted sales in column C and plot that result beside the original sales. Your own data can use different cells; the important point is that each formula result lines up with the category it belongs to.
Step-by-Step
- Check the worksheet layout. Put one category per row, such as a month, product, or date, and put the corresponding numeric values in an adjacent column. Use a heading in the first row of each column. Make sure there are no blank rows inside the data and that the values you intend to graph are numeric rather than numbers stored as text. A tidy table makes it easier for Excel to identify series names and category labels.
- Choose what the formula should calculate. Decide whether you want a new calculated series, such as sales after a percentage increase, or a reference series, such as the average sales value repeated across each month. A calculated series changes by row; a reference line may repeat the same result. If a calculation depends on an input such as a rate, keep that input in a separate cell so you can change it without rewriting every formula.
- Add a heading for the new series. In C1, enter a descriptive label such as Adjusted Sales. Excel can use this heading as the series name in the legend. Avoid leaving the heading blank: a chart may assign a generic name or show an unclear legend entry. Keep the heading distinct from the category labels in column A.
- Enter the formula in the first data row. With months in A2:A5, sales in B2:B5, and a 10% rate in E1, enter
=B2*(1+$E$1)in C2. The dollar signs keep the rate cell fixed when the formula is copied, while the row reference to B2 adjusts for each month. With sales of 10 in B2 and 10% in E1, the result in C2 is 11. - Fill the formula down the column. Select C2 and drag its fill handle—the small square at the lower-right corner of the selected cell—down to C5. You can also select C2:C5 and press Ctrl+D on Windows or Command+D on Mac. The results should be 11, 22, 33, and 44 for the example sales figures. Check at least the first and last rows to confirm the formula references the matching sales row and continues to use E1 for the rate.
- Create a chart or select your existing one. To make a chart from the example, select A1:C5, then use Insert > Insert Line or Area Chart and choose a line chart. A column chart is also suitable if you want to compare the original and adjusted values category by category. If you already have a chart based on columns A and B, click the chart once so the Chart Design tab appears; you do not need to recreate it.
- Add the formula results to an existing chart. Select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, click Add. For Series name, select C1 or enter its cell reference; for Series values, select C2:C5. Do not include the heading cell in the series values. Click OK to return to Select Data Source, and confirm the horizontal category labels point to A2:A5. Click OK again. The chart should now show Adjusted Sales as a separate series.
- Check the chart and format the new series. Confirm that the new line or columns appear and that the legend uses the heading you supplied. If the added series is difficult to compare with the original, select it and use the chart formatting controls to choose a contrasting color or marker. Avoid changing the underlying formula just to make the series look different; chart formatting changes its appearance without changing the calculated values.
- Optional: create a horizontal average line. With original sales in B2:B5 and the average intended to appear once for each category, enter
=AVERAGE($B$2:$B$5)in D2, then fill it down through D5. The average of 10, 20, 30, and 40 is 25, so D2:D5 will each show 25. Add D1 as the series heading and D2:D5 as another series using Chart Design > Select Data > Add. A line chart will display a level reference line across the categories. - Optional: show a trendline equation on the graph. A trendline equation is different from a worksheet formula: Excel calculates it from the plotted data and displays it on the chart. Select the data series, click the Chart Elements plus button beside the chart, select Trendline, and choose More Options. In the Format Trendline pane, choose the appropriate type, such as Linear, and select Display Equation on chart. You can also select Display R-squared value if you want that statistic shown. Treat the displayed equation as a model of the selected series, not as a replacement for a row-by-row worksheet calculation.
On Mac, the chart commands are available from the chart tabs, although the controls may be arranged differently; use Chart Design > Add Chart Element > Trendline to add a trendline. Excel for the web can create and edit charts, but some advanced chart formatting options may be limited. If you cannot find the trendline equation setting in the browser, open the workbook in the desktop app.
Common Mistakes
- Typing a formula into the chart itself. A chart series normally plots values from worksheet cells; it is not a free-form calculator. Put the calculation in cells first, then add those cells as a series. A trendline equation is a chart annotation generated from plotted data, not a formula you enter as a new series.
- Selecting the heading as a plotted value. When adding a series, include the heading in Series name but select only numeric result cells for Series values. Including a text heading in the values range can make the series behave unexpectedly.
- Using the wrong category range. A series with four values needs four matching category labels. If the chart has a different number of labels, Excel may show blank categories, omit points, or assign the values to the wrong positions.
- Forgetting to lock an input cell. A reference such as E1 changes to E2, E3, and so on when filled down. Use an absolute reference, as in the example, when every row should use the same rate.
- Plotting results that are text or errors. A formula can return text, or it can produce an error if its input is invalid. Charts may ignore those results or display gaps. Check the worksheet cells before troubleshooting the chart.
- Confusing a trendline with a calculated data series. A trendline summarizes the shape of a plotted series. It does not automatically write forecast values into worksheet cells. If you need values to chart, calculate them in cells and add that range.
Tips
- Format the input rate as a percentage and the calculated results as numbers or currency, as appropriate. Number formatting changes how values appear, not the underlying calculation.
- Use a clear series heading such as Adjusted Sales or Four-Month Average. Descriptive names make the legend and Select Data dialog easier to understand.
- If your chart uses a regular Excel table, add the new formula column inside the table. The table can extend its calculated column as you add rows, and a chart based on the table may include the new data automatically. Check the chart after adding the column, because chart behavior depends on how its source was set up.
- Keep a copy of the original values when the formula represents a scenario or adjustment. Showing both series makes it easier for readers to compare the calculation with the source data.
- For a trendline, choose a type that fits the data rather than selecting one only because it looks smooth. A linear trendline assumes a straight-line relationship; other types may suit other patterns, but an equation on a chart should not be read as proof that the pattern will continue.
Troubleshooting
The new series does not appear: Open Chart Design > Select Data and check that the series values point to the intended result cells. Confirm the cells contain numbers and that the chart is not filtered to hide the series. If you created the chart before adding the formula column, add that range explicitly rather than assuming Excel included it automatically.
The line is flat or the scale looks wrong: Check whether the formula returns the same value in every row, whether the input rate is entered as 10% rather than 10, and whether the chart combines values with very different magnitudes. If the average line is intentionally constant, a flat line is expected. For a chart with widely different scales, consider whether a different chart type or a secondary axis is appropriate; do not use one just to disguise a mismatch.
The formula shows an error or the wrong result: Click a result cell and inspect its formula and inputs. Confirm that the rate cell contains a numeric percentage, and check that the row references point to the matching data. If you see a green error indicator or a number stored as text, convert the source to a numeric value before relying on the chart.
The chart shows the wrong labels or switches rows and columns: In Select Data Source, check the Horizontal (Category) Axis Labels range. Use Switch Row/Column only if the data is genuinely arranged in the opposite orientation; it does not repair an incorrect cell range by itself. Make sure each category corresponds to the value on the same row.
You cannot find the equation option: Select the plotted data series first, then open its Trendline options. The equation display is available for a trendline, not for every chart element or every chart type. If the option is unavailable in Excel for the web, try the desktop version and use a chart type that supports a trendline.
Frequently Asked Questions
Can I type a formula directly into an Excel graph?
Usually, no. Enter the calculation in worksheet cells and add those cells as a chart series. You can display a trendline equation on a chart, but that equation is generated from the selected data rather than entered as the series calculation.
How do I add a formula to a graph that already exists?
Calculate the values in a worksheet column, select the graph, then choose Chart Design > Select Data > Add. Set the series name to the column heading and the series values to the calculated cells. Check the category label range before closing the dialog.
Can the chart update when I change an input?
Yes. If the plotted formula cells refer to an input cell, changing that input recalculates the cells, and the chart updates from those values. Use absolute references for shared inputs when filling formulas down, and confirm the chart includes the full result range.
Is a trendline equation the same as a formula series?
No. A formula series consists of worksheet calculations plotted as data points. A trendline equation describes a fitted line or curve based on a chart series. Use a formula series when you need actual calculated values by category.
Why does my formula column not appear in the legend?
The chart may not include that column, or the series name may not point to its heading cell. Open Select Data, add or edit the series, and assign the heading cell as Series name. If the chart is based on a table, check whether its source range expanded to include the new column.
Can I show a formula result in the chart title?
You can link a chart title to a worksheet cell, then the title displays that cell’s contents and updates when the cell changes. This is separate from plotting a formula as a data series; use Select Data to add plotted values.
Related
- How to Add Checkboxes in an Excel File on SharePoint
- How to Add a Checkbox in an Excel Column
- How to Add a Checkbox in Excel Without the Developer Tab
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram Bin Number: How to Choose ItIn an Excel histogram, the bin number is the number of intervals used to group your numeric data.…
- Excel Line Chart in a Cell: Create and Format SparklinesTo put a line chart inside an Excel cell, insert a Line Sparkline: select the destination cell, choose…
- Excel Histogram Template: Build One and Set BinsAn Excel histogram template is a worksheet or chart setup that groups numeric data into ranges (bins) and…
- Histogram in Excel Using Formulas: Build Bins and ChartsTo make a histogram in Excel with formulas, list your data, set bin limits, and use FREQUENCY to…
- Basic Pivot Table: Create and Use One in ExcelA basic pivot table is an interactive summary of source data: in Excel for Microsoft 365 on Windows,…
- Excel Pivot Table Subtotals: How to Show, Change, and Fix ThemIn Excel for Microsoft 365 on Windows, select a cell in the PivotTable and choose PivotTable Design >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.