How to Track Referral-Sourced Revenue % and
Measure Customer Trust

How to Calculate Referral-Sourced Revenue % in Excel

1. Create an End-of-Month Date Column

• Start with the event date or service date in your dataset.
• Use the EOMONTH function to convert each date into its month-end value.
• Format the result as a date so it displays correctly.
• This creates a clean monthly grouping field that you can use throughout the analysis.

2. Build a Unique List of Months

• Use the UNIQUE function on the month-end column you just created.
• This produces one row for each month in your dataset.
• Make sure the list is sorted in proper chronological order so your chart reads correctly from month to month.
• Check that the sequence flows cleanly across all years in your data.

3. Set Up the Output Columns

Create the structure for your KPI table with these columns:

• Month
• Total Revenue
• Non-Referral Revenue
• Referral Revenue
• Referral Revenue %
• Target

This gives you the layout needed for both the calculations and the final chart.

4. Calculate Total Revenue by Month

• Use the SUMIFS function to total revenue for each month.
• Reference the gross revenue column as the value field.
• Use the month-end helper column as the matching criteria.
• Format the results as currency so the monthly revenue values are easier to read.

5. Calculate Referral Revenue by Month

• Use SUMIFS again.
• Sum the same revenue column by month.
• Add a second condition using the acquisition channel field.
• Filter that second condition to only include referral-sourced jobs.
• Format the results as currency and fill the calculation down for all months.

6. Calculate Non-Referral Revenue

• Subtract referral revenue from total revenue.
• This gives you the non-referral portion for each month.
• Fill the calculation down so every month is split into referral and non-referral revenue.

7. Calculate Referral Revenue %

• Divide referral revenue by total revenue.
• Format the result as a percentage.
• Add decimals only if you want a more precise display.
• Fill the percentage calculation down for the full monthly range.

8. Create a Target Line

• Enter your target percentage in the first row of the Target column.
• Reference that first target value in the rows below it so the target line stays consistent across the full chart.
• This creates a dynamic target range that can easily be adjusted later for sensitivity analysis.
• You can change the target at any time to compare which months are above or below goal.

9. Insert the Chart Using a Combo Layout

• Highlight the Month column first.
• Then hold Ctrl and highlight the revenue and percentage fields needed for the chart.
• Go to Insert and open Recommended Charts.
• Switch to All Charts and choose Combo.
• Change the revenue series to a Stacked Column chart.
• Set the percentage series to a Line chart.

10. Place the Percentage Metrics on the Secondary Axis

• Keep the revenue series on the primary axis.
• Move the referral revenue percentage line to the secondary axis.
• Move the target line to the secondary axis as well.
• This keeps the percentage lines readable and separate from the large revenue values.

11. Clean Up the Chart Design

• Update the chart title and position it neatly.
• Change any colors that do not fit the message of the chart.
• Adjust the referral percentage line color so it stands out clearly.
• Format the target line with a thinner weight and, if preferred, a dashed style so it reads as a benchmark instead of a main metric.

12. Reformat the Month Labels

• Click the horizontal axis labels.
• Open the formatting pane with Ctrl + 1.
• Go to Custom date formatting.
• Shorten the month display so the axis takes up less space and the chart looks cleaner.
• This helps when you have many months on the chart.

13. Refine the Secondary Axis Formatting

• If the percentage axis shows unnecessary decimals, format that axis separately.
• Reduce the decimals to zero if you want a cleaner presentation.
• Keep the table values and the chart axis formatting independent if needed.

14. Add Data Labels Only Where Needed

• Avoid labeling every point if the chart becomes too crowded.
• Add labels only to the most important columns or standout months.
• You can label just the high points, low points, or a few key values.
• If labels are hard to read, add a fill background behind them or reposition them manually.

15. Finalize the KPI View

• Review that each month shows:
  • total revenue
  • referral revenue
  • non-referral revenue
  • referral revenue percentage
  • target comparison
• Confirm the stacked columns and lines are displaying correctly.
• Once complete, you have a monthly KPI chart that shows both revenue composition and referral performance against target.