How Do You Make an ROI Chart in Excel?

How Do You Make an ROI Chart in Excel?

Search for an ROI chart in Excel and you will find plenty of tutorials promising a specific button. There is no such button. Excel ships seventeen named chart types and not one of them is an ROI chart, and there is no ROI worksheet function either.

That is not a gap to work around. "ROI chart" describes what you want a reader to understand, not a feature Microsoft built. The job is concrete: pick the return formula that matches your cash flows, then pick the chart that makes the comparison legible.

This guide does both using only what Microsoft documents — and flags two places where Microsoft's own pages disagree.

What Is the Short Answer?

Build an ROI chart as a combo chart with a secondary axis: your cash amounts as clustered columns on the left axis, your return percentage as a line on the right axis.

Microsoft's own reasoning for combo charts is exactly this situation. The documentation recommends them "when the numbers in a chart vary widely from data series to data series" or "when you have mixed types of data (price and volume)". Money and percentages are the textbook case.

If you want to show a running total — how an investment is built up or eaten away by a sequence of gains and costs — use a waterfall chart instead. It is a native Excel type designed for financial statements.

Not sure which of your marketing channels is actually returning anything? Get a free SEO audit and see where your traffic and conversions are really coming from.

Why Does Excel Have No ROI Chart Type?

Because ROI is an arithmetic result, not a shape. Microsoft's "Available chart types in Office" page lists every type Excel offers: column, line, pie and doughnut, bar, area, XY (scatter) and bubble, stock, surface, radar, treemap, sunburst, histogram, box and whisker, waterfall, funnel, combo and map.

Seventeen types. No ROI chart, and no "return on investment" chart under any other name.

What that means in practice

An ROI chart is always a composition: a documented chart type, fed by a documented formula, arranged so the comparison you care about is what the eye lands on.

That chart-type list also carries an explicit caveat: it describes "each chart type for Excel in Windows". Do not assume the same inventory on Mac or the web.

Which Formula Should Produce the Numbers?

Charting the wrong number well is worse than charting the right number badly. Pick the formula from the shape of your cash flows.

IRR and XIRR

IRR returns the internal rate of return for a series of cash flows, and its constraint is the one people forget: "the cash flows must occur at regular intervals, such as monthly or annually."

If your money moves on real-world dates rather than tidy intervals, you want XIRR, which Microsoft describes as returning the rate "for a schedule of cash flows that is not necessarily periodic."

The syntax is IRR(values, [guess]) and XIRR(values, dates, [guess]). Both need mixed signs: IRR requires "at least one positive value and one negative value", and XIRR returns #NUM! without one of each.

One quiet asymmetry: Microsoft documents that IRR gives up after 20 attempts, while XIRR tries 100 times before returning #NUM!. A stubborn model that fails on one may simply need the other's extra iterations.

NPV and XNPV

NPV(rate,value1,[value2],...) discounts a series of future payments back to today's money. Microsoft's framing is useful for a chart caption: NPV "returns the net value of the cash flows — represented in today's dollars", while IRR "goes one step further than NPV to determine a specific rate of return for a project."

XNPV(rate, values, dates) is the irregular-dates counterpart. It is strict about sign convention: "If the first value is a cost or payment, it must be a negative value."

Watch the date rules, which the two functions word differently. XIRR says "Dates may occur in any order." XNPV says all dates "must be later than" the first one. Both are true — the first date anchors the schedule and nothing may precede it — but a loose paraphrase will mislead you.

RRI

RRI(nper, pv, fv) returns "an equivalent interest rate for the growth of an investment" — the compound annual rate implied by a start value, an end value and a number of periods. It is the simplest honest answer when you have two endpoints and no intermediate cash flows.

Microsoft publishes the RRI equation only as an image, with no text version, so there is nothing quotable to cite. It is also the one function here with no Excel for the web entry in its applicability list.

Your SEO spend deserves the same scrutiny as every other line item. Request a free audit and get a clear picture of what your organic channel is actually worth.

How Do You Build the Chart, Step by Step?

Lay the data out first: one column of labels, one of cash amounts, one of return percentages.

Enter the percentages as decimals: store 0.1 and format it as a percentage to display 10%. Typing 10 and applying percent formatting gives 1000%, the most common way an ROI chart goes wrong before anyone inserts it.

Then:

1. Select the data, including headers.
2. Choose Insert and then Recommended Charts.
3. If nothing in the list fits, open the All Charts tab to see every available type.
4. Pick a clustered column chart and confirm with OK. Both series land on one axis, which looks wrong — percentages next to currency are invisible.
5. With the chart selected, go to Chart Design and then Change Chart Type.
6. Choose Combo, then the clustered column and line on secondary axis option.
7. Tick the Secondary Axis checkbox for your percentage series.
8. Set that series' type to Line from the drop-down.
9. Confirm with OK.

Your currency series now scales on the left and your return percentage on the right, so the two are finally comparable.

If the chart grouped your data the wrong way round — categories where you wanted series — use Chart Design and then Switch Row/Column. To reorder series, use Chart Design and then Select Data, working through the "Legend entries (Series)" list.

Where Microsoft's own instructions are out of date

Here is the first contradiction, and it sits on the most important page. Microsoft's secondary-axis article tells you to "Select a chart to open Chart Tools" and then "On the Design tab, select Change Chart Type."

Chart Tools no longer exists in current Excel. It was replaced by a single Chart Design tab — which is what Microsoft's own "Update the data in an existing chart" page uses: "Select Chart Design > Switch Row/Column." Both pages list Excel for Microsoft 365 as applicable.

Follow the Chart Design wording above. If you go hunting for a Chart Tools group, you are not missing anything — that page describes a ribbon from several versions ago.

The same page names the combo subtype "Cluster Column - Line on Secondary Axis", while the chart-types page writes "clustered column – line on secondary axis". Same control, two spellings, two Microsoft pages.

Charting your return is easier when the inputs are trustworthy. Get a free SEO audit and start from numbers you can defend.

What About a Waterfall Chart?

For cumulative return, a waterfall is usually the better picture and it needs no secondary axis at all.

Microsoft describes it as showing "a running total as values are added or subtracted" and notes they "are often used to visualize financial statements, and are sometimes called bridge charts." Columns are colour-coded so positive and negative values read at a glance.

To create one, select your data and choose Insert, then the waterfall button, then Waterfall. It is also in the All Charts tab of Recommended Charts.

What makes a waterfall look professional is anchoring its totals. By default every column floats. Select a data point, right-click, choose Format Data Point, and tick the Set as total box to drop that column to the axis; clearing it makes the column float again. The same control appears as Set as Total on the right-click menu — Microsoft capitalises the two differently on one page.

There are no sub-types to choose between: Microsoft states plainly that "There are no chart sub-types for waterfall charts."

When Is an ROI Chart the Right Move?

Reach for the combo-plus-secondary-axis version when comparing several things — channels, campaigns, vendors, projects — and the money and the rate tell different stories. A channel can absorb the largest budget and still post the best percentage return, and only a two-axis chart shows both at once.

Reach for the waterfall when you are explaining one outcome: here is what we started with, here is what each decision added or cost, here is where we ended.

Use a plain line chart when tracking a single return percentage over time with no second series worth plotting. A secondary axis you do not need is clutter.

Sometimes the right answer is no chart. A single ROI figure in a sentence beats a lone column, which carries no comparison and therefore no information.

Want to know what your organic traffic is worth before you chart it? Start with a free SEO audit and get real numbers to model from.

What Are the Limitations Worth Knowing?

IRR is not always a single number. Microsoft says so directly: "sometimes there can be more than one acceptable IRR, or sometimes none at all", and "If there is more than one acceptable answer, the IRR function only returns the first one it finds." Excel hands you one value with no warning that others exist, so any chart presenting a lone IRR as definitive is overstating its case. Cash flows that change sign more than once are where this bites.

There is no documented way to format a secondary percentage axis. Microsoft's axis-display article covers number formatting but opens by saying "This article does NOT cover changing the scale of chart axes" — capitals theirs — and never mentions secondary axes. The secondary-axis article never mentions number formatting. Setting minimum and maximum values on a percentage axis is something you work out in the Format Axis pane without first-party instructions.

The old data-point limits are gone. Excel's specifications publish 255 data series per chart and 255 worksheets referred to by a chart. For data points, per-series and across all series, the answer is now "Limited by available memory". The widely-quoted 32,000-point ceiling is a legacy figure Microsoft has removed.

Platform coverage is thin. Excel for the web is absent from the applicability lists on the secondary-axis page, the waterfall page, the RRI page and the NPV/IRR explainer. The waterfall page lists Excel for iPad and iPhone while giving only Windows and macOS instructions. If your readers open workbooks in a browser, test there before shipping.

ROI Charts vs the Alternatives

Against a dashboard tool. Power BI or Looker Studio beat Excel on automatic refresh and on sharing one authoritative view. Excel wins when the model matters more than the distribution — when you need to audit the formula behind every number.

Against a table. A well-formatted table beats a chart for three or four rows. The chart earns its place once a reader must compare more items than they can hold in mind at once.

Against scenario modelling. A chart shows one set of assumptions. If the real question is how sensitive your return is to a changed assumption, you want Goal Seek, Scenario Manager or a data table feeding the chart.

Final Thoughts

There is no ROI chart in Excel, and knowing that is the useful part. You have instead a dependable kit: IRR and XIRR for rates, NPV and XNPV for present values, RRI for compound growth between two endpoints, a combo chart with a secondary axis for comparisons, and a waterfall for running totals.

Two cautions come from Microsoft's own documentation rather than anyone's opinion. IRR can have several valid answers and reports only the first. And Microsoft's secondary-axis instructions still describe a ribbon current Excel does not have — use Chart Design, not Chart Tools.

If your ROI model depends on assumptions that could reasonably be different, build that in before you build the chart. Our guide to what-if analysis in Excel covers Goal Seek, Scenario Manager and data tables, which is where an honest ROI chart gets its range from.