'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate the Percentage of Repeat Customers in Excel
Question details
The user needs to calculate the percentage of repeat customers (those who made multiple purchases) compared to the total number of unique customers in an Excel dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing customer sales data to determine the customer retention rate or the exact percentage of customers who have purchased more than once.
- Observed behavior
- Requires a formula to identify and count customers with multiple entries in a dataset, and divide that count by the total number of unique customers to output a percentage result.
Ensure your sales dataset is organized into columns with clear customer identifiers (like names or IDs) and the products they purchased. Verify that your version of Excel supports modern dynamic array functions like UNIQUE and FILTER.
Using Basic Dynamic Array Functions (LET, UNIQUE, FILTER)
This is the most straightforward method to calculate the repeat customer percentage, assuming each customer-product row represents a distinct purchase.
This formula uses the LET function to define variables for your customer list and unique customers, making the calculation cleaner. It then filters out customers who appear two or more times and divides that count by the total unique customers.
Click on the blank cell where you want the final percentage to be displayed.
Type the formula =LET(cus,A2:A7,ucus,UNIQUE(A2:A7),COUNTA(FILTER(ucus,COUNTIF(cus,ucus)>=2))/COUNTA(ucus)), replacing A2:A7 with your actual customer data range.
Press Enter to calculate the ratio. Then, navigate to the Home tab and click the Percentage style button to display the decimal as a percentage.
Calculating Percentage Based on Distinct Products Purchased
Use this advanced LAMBDA formula if you only want to count customers as 'repeat' when they buy different, distinct products, ignoring multiple purchases of the exact same item.
Calculating Percentage Based on Total Items Purchased
Use this variation of the LAMBDA formula to count customers who bought more than one item in total, regardless of whether the items were distinct or identical.
Calculate Repeat Customer Percentages Easily in WPS Office
WPS Spreadsheet provides robust formula support and dynamic arrays, allowing you to seamlessly analyze customer retention data and calculate repeat purchase percentages just like you would in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your customer sales data.
- 2. Select an output cell: Click on an empty cell where you wish to display the calculated retention percentage.
- 3. Input the calculation formula: Type your preferred formula (such as the COUNTIF and UNIQUE combination) tailored to the range of your customer data.
- 4. Format the result: Press Enter, then click the '%' icon on the Home tab to convert the decimal output into a readable percentage.
Frequently Asked Questions
Can I calculate repeat customers without using the LET function?
Yes, if your software version does not support LET, you can use a helper column to count occurrences with =COUNTIF(A:A, A2). You can then use a Pivot Table or standard COUNTIF formulas to find the percentage of customers with counts greater than 1.
Why is my formula returning a decimal instead of a percentage?
Formulas naturally output raw ratios (for example, 0.5 instead of 50%). To fix this, simply select the result cell and apply the Percentage number format from the Home tab or by pressing Ctrl+Shift+%.
How do I quickly count the total number of unique customers?
You can count unique customers by combining the COUNTA and UNIQUE functions. Entering the formula =COUNTA(UNIQUE(A2:A100)) will return the exact number of distinct customer names in that range.
Does the UNIQUE function work in older versions of Excel?
No, UNIQUE is a dynamic array function available only in Microsoft 365 and Excel 2021 or newer. For older versions, you will need to rely on Pivot Tables or complex array formulas combining INDEX and MATCH.
