The days of trawling through pages of numbers and manual cross-referencing, data entry and flip charts are long gone. For staff working in sales, having reports that deliver value is essential – and having efficient ways of processing and extracting that data are key.
Excel is used by most sales teams to varying degrees, and who can blame them? With its functional design and processing capabilities, Excel simplifies much of the manual labour that reporting used to require nothing more than pre-set formulas and generated results.
Getting the most out of Excel means mastering the formulas which are built into it. We’ve dug up four of the best Excel formulas for sales reports to help you get the most from your data.
Percentages
Definitely one of the easiest ones to use and a great figure to have included in reports. Percentages in Excel allow you to check what your close rate is based on closed won deals and lost deals, making comparisons straightforward.
Total sales – SUM or ARRAY
The SUM function is a lifesaver for getting your additions and totals spot on. It can be set to certain cells, columns or tables to always generate the correct total. Using the ARRAY formula allows you to work across large ranges of data, such as multiple cells, tables or columns for extracting data.
Closing sales – DATEDIF
Great for checking how long it takes for a prospect to convert to a closed lead, DATEDIF functions can calculate the days between the open and close, so you can measure how quickly you’re converting sales.
Funnel Conversion Rate
An extension of percentages, this formula measures the number of leads in a sales funnel vs the number of leads that have progressed through to sales conversion. The formula gives you a percentage so measuring conversions are easy to track.
Example: = (Number of Won Deals / Total Number of Leads)*100
Lifetime value
A useful tool for measuring how profitable customer relationships are over time. B2B businesses who tend to have long term relationships/partnerships will find this particularly insightful. The lifetime value function calculates the customer value first, by taking Average Purchase Value * Average Number of Purchases. This is then worked into the lifetime value via the formula Customer Value * Average Customer Lifespan.
Example: =Monthly Recurring Revenue*Contract Length
Bonus tool: HLOOKUP/VLOOKUP
No need to spend hours squinting at cells to check if you’re working on the correct figures or columns. Find the right data quickly with the HLOOKUP or VLOOKUP tools in Excel. You can even work across multiple sheets to extract data – useful for working on yearly sales reports or if you’re extracting specific figures for comparison.
If you want to find out more about how to use Excel for your sales reports, why not download our free Excel Sales Pipeline template, or speak to a member of the Clarity team about our range of sales and reporting software tools.
