How to calculate conversion rate in Excel
How to calculate conversion rate in Excel might be not crystal clear, because of the challenges using Excel and because the conversion rate is a challenging topic itself.
What’s challenging with conversion rates?
“Conversion rate” may mean everything and its contrary. So, the main challenge before starting to calculate it is to define the meaning of the metric in the case at issue.
Done this, how to calculate conversion rate comes straightforward and how to calculate conversion rate in Excel is just a matter of practising the tool, having taken out of the table any misunderstanding on what there’s to do.
Let’s make some examples.
You are in charge of the marketing of a SaaS business selling only online and you are interested in knowing the conversion rate of the business.
The question is: what is the conversion rate about? It may be the conversion rate of the Facebook PPC campaigns continuously running and representing the only scalable source of revenue.
Or, the marketing strategy may not use any PPC campaign, while being focused on word-of-mouth and content marketing, and the conversion rate refers to the whole number of visitors landing anywhere on the website.
Many more meanings of the conversion rate can be discussed.
The good news is that they do not affect how to calculate conversion rate in Excel since there’s a general concept all the possible meanings of the conversion rate rely on.
The general concept to calculate conversion rate
The general concept which applies whatever the meaning of conversion rate in the case at issue is that the conversion rate is the percentage of conversions to a certain goal with respect to the potential conversions achievable.
Retrieving the example of the PPC Facebook campaign, imagine the goal of the campaign is to have users download a certain report. The total number of users getting to the landing page from the PPC campaign represents the number of potential conversions, while the number of users downloading the report represents the number of conversions.
So, the conversion rate can be calculated as the number of users downloading the report by the total number of users getting to the landing page from the PPC campaign.
The conversion rate, in this case, is intended to be strictly related the PPC Facebook campaigns.
Broadening the reasoning, the general concept of calculating the conversion rate is made of four steps. How to calculate it in Excel follows straightforwardly.
- Define the goal of the metric (e.g. conversion rate to the report download of the PPC campaign, conversion rate to the trial sign-up of organic visitors). This is the baseline for the next steps and therefore for the calculation itself.
- Identify the number of conversions to the goal (e.g. number of users who downloaded the report, number of users who signed up for a trial).
- Identify the total number of potential conversions to the goal (e.g. users landed to the report download page from the campaign, organic users landing to any page of the website).
- Compute the conversion rate dividing the number of conversions by the number of potential conversions.
The numbers (conversions and potential conversions) should refer both to a specific timespan (e.g. January, the period of a campaign running). It follows that the conversion rate refers to the same period.
Practical conversion rate calculation in Excel
Following the steps of the general concept, we can now go through how to calculate conversion rate in Excel from the most practical perspective. And it will shock you how simple it is.
Just set a cell to be inputted the number of conversions to the goal (in the picture, it’s cell blue cell “C11”).
Then, set a second cell to be inputted the number of potential conversions (in the picture, it’s the magenta cell “C12”).
Finally, in a third cell, compute the formula “number of conversions / number of potential conversions” (in the example in the picture, =C11/C12). Set the cell format as a percentage and the conversion rate is calculated and formatted.
Download the Excel
How could the topic be addressed better than using an Excel file?! Said, done.
You can download here the Excel spreadsheet to be used daily for your calculations.