How to Track Marketing ROI % by Month in Excel

Marketing ROI % by Month and Year

1. Extract the Month and Year from the Lead Timestamp

• Locate your Lead Date or Lead Timestamp column.
• Use the TEXT function to extract the Month.
• Use the TEXT function again to extract the Year.
• Confirm both columns populate correctly.
• If you already have Month and Year in your dataset, you can skip this step.

2. Create a Unique List of Month-Year Combinations

• Select the Month (or Month-Year helper) column.
• Use the UNIQUE function to remove duplicates.
• Copy the results and paste values using Ctrl + Shift + V.
• This will serve as the driver for your monthly summary table.

3. Build the Summary Table Structure

Create the following column headers in your summary area:

• Date
• Marketing Spend
• Gross Profit
• ROI
• Target

Format the Date column so it displays clearly for charting on the X-axis.

4. Calculate Monthly Marketing Spend

• Identify the Lead Cost column in your dataset.
• Use the SUMIFS function.
• Sum Lead Cost where:
  • Month equals the selected Month
  • Year equals the selected Year

Format the result as currency using Ctrl + Shift + 4.

5. Calculate Monthly Gross Profit

• Identify the Gross Profit column in your dataset.
• Use the SUMIFS function again.
• Sum Gross Profit where:
  • Month equals the selected Month
  • Year equals the selected Year

Format as currency and align consistently with Marketing Spend.

6. Calculate Marketing ROI %

• Divide Gross Profit by Marketing Spend.
• Format the result as a percentage using Ctrl + Shift + 5.
• Drag the calculation down for all months.

7. Add a Target ROI Line

• Enter a target ROI percentage (example used in the video: 600%).
• Reference that target cell down the column so it remains dynamic.
• This allows you to adjust one cell and update the entire chart instantly.

8. Create the Combo Chart

• Highlight Date, Gross Profit, ROI, and Target.
• Go to Insert → Recommended Charts.
• If needed, switch to All Charts → Combo.

Set the chart configuration:

• Gross Profit as columns on the primary axis
• ROI as a line on the secondary axis
• Target as a line on the secondary axis

This ensures ROI percentages are readable and not compressed against large dollar values.

9. Format the Target Line

• Open Format Data Series for the Target line.
• Reduce line thickness.
• Change it to a dotted style.
• Use a neutral color so it does not overpower the actual ROI line.

10. Add Data Labels to the ROI Line Only

• Add data labels to the chart.
• Remove labels from columns if the chart looks crowded.
• Keep labels only on the ROI line.

If needed:

• Add a light background fill to the ROI labels.
• Adjust font weight for clarity.

11. Final Chart Cleanup

• Rename the chart title to “Marketing ROI % by Month”.
• Adjust spacing and sizing for readability.
• Confirm your dataset remains structured so future rows update automatically.