How to Make an ROI Chart in Excel

A simple ROI number tells you if an investment was profitable, but an ROI chart tells you the whole story. Visualizing your return on investment helps you compare different campaigns side-by-side, track performance over time, and clearly communicate your wins to your team or stakeholders. This guide will walk you through exactly how to build clear and insightful ROI charts in Microsoft Excel, from setting up your data to customizing your visuals for maximum impact.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is an ROI Chart?

Before building the chart, let's quickly cover the fundamentals. Return on Investment (ROI) is a performance metric used to evaluate the efficiency or profitability of an investment. The formula is refreshingly simple:

ROI (%) = (Net Profit / Cost of Investment) x 100

An ROI chart, therefore, is a visual representation of this metric. While a standalone percentage like "a 250% ROI" is good, a chart puts that number in context. It answers deeper questions, such as:

• Which of our marketing campaigns delivered the highest ROI last quarter?
• How has the ROI of our Google Ads campaign trended over the last six months?
• How do our social media, email, and content marketing ROIs stack up against each other?

By plotting this data, you move from a single data point to a strategic overview, making it much easier to spot trends, identify top performers, and decide where to allocate your budget next.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Gather and Organize Your Data

The quality of your chart depends entirely on the quality of your data. The first step is to collect all the necessary information and structure it properly in an Excel sheet. You can't just start plotting numbers, you need a clean, organized table first.

What Data You'll Need

For each investment or time period you want to analyze, you'll need the following:

• Investment Name or Time Period: This is what you're measuring. It could be "Facebook Q3 Campaign," "Email Marketing Newsletter," or specific months like "January," "February," "March."
• Cost of Investment: Be thorough here. This isn't just ad spend. Include all associated costs, such as software subscriptions, agency fees, creative development costs, or even a portion of team salaries if a significant amount of time was dedicated to the project.
• Return / Revenue Generated: This is the total income attributed to the investment. Make sure your attribution model is consistent. Is it last-click revenue? Multi-touch? Define it and stick to it.

Setting Up Your Excel Table

Open a new Excel sheet and create a simple table. Your goal is to have all the raw data in one place before you start calculating ROI and building charts.

Create columns for your data points. Let's imagine we're comparing the performance of four different marketing campaigns from the last quarter. Your table should look something like this:

1. Calculate Net Profit

In the "Net Profit" column (cell D2), subtract the cost from the return. The formula is: =C2-B2 Drag the fill handle (the small square in the bottom-right corner of the cell) down to apply this formula to the other campaigns.

2. Calculate ROI

In the "ROI (%)" column (cell E2), you'll use the classic ROI formula. The formula references your Net Profit and Cost of Investment columns: =D2/B2 Again, drag the fill handle down to calculate the ROI for all rows. To display these values as percentages, select the cells, right-click, choose "Format Cells," and select "Percentage" from the Category list. You can also specify the number of decimal places.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Create a Bar Chart to Compare Investments

A column or bar chart is the best choice for comparing the ROI of different, distinct projects. It provides a quick, easy-to-read visual comparison of which campaigns performed best.

How to Build It:

1. Select Your Data: Highlight the columns you want to visualize. In this case, you want to show the campaign name and its corresponding ROI. Hold down the Control key (or Command on Mac) and click to select the "Campaign" column (A1:A5) and the "ROI (%)" column (E1:E5). Holding Ctrl/Cmd allows you to select non-adjacent columns.
2. Insert the Chart: Go to the Insert tab on Excel's top ribbon. In the "Charts" section, click the icon for "Insert Column or Bar Chart." A 2D Clustered Column chart is a great starting point.
3. Your Chart is Generated: Excel will instantly create a basic column chart based on your selected data.

Customizing Your Chart for Readability

The default chart is functional, but a few tweaks can make it professional and much clearer.

• Add a Descriptive Title: Click on the default "Chart Title" and change it to something specific, like "Q3 Marketing Campaign ROI Comparison."
• Add Data Labels: It's helpful to see the exact percentage on each column. Click on your chart, then click the green "+" icon that appears on the right. Check the box for Data Labels. The ROI percentages will now appear on top of each bar.
• Remove Chart Clutter: If the data labels are present, is the Y-axis still necessary? Sometimes, removing it (and the gridlines) can create a cleaner look. Click on the Y-axis and press Delete. Do the same for the gridlines.
• Adjust Colors: Use brand colors or conditional colors to add more meaning. For example, you could make the highest-performing campaign's bar green to draw attention to it.

Step 3: Create a Line Chart to Track ROI Over Time

What if you want to track the performance of a single initiative over time? A line chart is the perfect tool for this. It helps you visualize trends, seasonality, and the impact of changes you've made to a campaign month-over-month.

First, set up your data with a time-based dimension. Let's track the ROI of your Google Ads spend over the last six months:

How to Build It:

1. Select Your Data: Highlight the "Month" column and the "ROI (%)" column.
2. Insert the Chart: Go to the Insert tab, and this time, click the "Insert Line or Area Chart" icon. Choose a 2D Line chart, preferably one with markers to indicate each data point.
3. Customize for Context:

The resulting line chart immediately shows a story that a table of numbers can't: while there was a dip in March, the campaign's ROI has been on a strong upward trend since April.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Tips for Better ROI Analysis and Visualization

Creating the chart is only half the battle. Presenting it in a way that’s accurate and drives action is just as important. Here are some common pitfalls to avoid:

• Include All Costs: A very common mistake is to only count direct ad spend as your "cost." This inflates your ROI. Be sure to account for software, personnel time, creative costs, and other overhead to get a true picture of profitability.
• Keep Your Data Apples-to-Apples: When comparing campaigns, ensure the time periods and attribution methods are consistent. Comparing the ROI of a one-week flash sale to a six-month brand awareness campaign is not a fair or useful analysis.
• Provide Context: An ROI chart is powerful, but it doesn't exist in a vacuum. Always be prepared to explain the "why" behind the numbers. Why did the SEO campaign's ROI dip in March? "That was when we were heavily investing in new content that hadn't ranked yet." The chart starts the conversation, your analysis completes it.

Final Thoughts

Building an ROI chart in Excel is a straightforward process that transforms a simple list of costs and returns into a powerful decision-making tool. Whether you're using a bar chart to compare different marketing channels or a line chart to track a single campaign's performance, visualizing your data is the key to unlocking actionable insights that can guide your strategy.

The real challenge isn't making the chart itself - it's the tedious, manual work of constantly pulling and cleaning data from a dozen different platforms before you can even get started. Exporting CSVs from Google Ads, Shopify, Salesforce, and Facebook Ads every week is time-consuming. We built Graphed to eliminate that friction by connecting all your data sources automatically. You can just ask, "Show me my Facebook Ads ROI by campaign this month," and instantly get a real-time dashboard, giving you back the hours you used to spend wrangling spreadsheets.