Excel and Spreadsheet Tips
How To Format Your Excel Spreadsheet: 10 Tips for Finance Professionals
Here are 10 ways to format your Excel spreadsheet when building financial reports, including guidelines and best practices for each one.
When tracking core business metrics like monthly recurring revenue and unit sales in isolation, it’s hard to see what’s really driving customer retention, churn, or repeat purchases over time.
Cohort analysis helps you dig deeper into these numbers to uncover how different groups of customers behave month after month.
For example, you might notice that customers acquired during a discount campaign churn faster than those who joined through referrals, or that users onboarded in Q1 of 2025 have higher recurring revenue after six months than later cohorts.
These insights help you make data-driven decisions about pricing, customer acquisition, and retention strategies, so your forecasts and revenue models reflect what’s actually happening in your business.
In this blog, we’ll walk you through exactly how to set up and run a cohort analysis in Excel, step by step, to generate useful insights for more accurate planning and forecasting.
Cohort analysis groups customers based on a shared starting point—like when they made their first purchase—and then tracks how their behavior changes over time.
Instead of viewing “total customers” or “monthly revenue” as one big bucket, it breaks the data into smaller, more meaningful segments so you can answer questions like, “Of the 500 customers who joined in January, how many returned in February?”
For FP&A teams, cohort analysis is an especially valuable business analytics tool that makes it easier to connect behavior trends to financial outcomes.
Cohort analysis can reveal:
There are different ways to group your data depending on what you want to learn.
To get a more complete view of performance, you can combine different cohort types.
For instance, pairing time-based and segment-based cohorts shows how customers acquired in the same period perform across different regions, pricing tiers, or product lines. This combined view helps you connect retention and profitability trends to specific markets or customer segments instead of relying on averages alone.
Let’s go over how to set up and run a cohort analysis in Excel, so you can turn raw data into clear, actionable insights.
Start by exporting your customer transactions from your ERP, CRM, accounting software, or data warehouse. Save the .CSV or .XLSX file on your device.
From Excel, go to File > Open, select your file, and click Open. Excel will automatically load your data into a new worksheet.
If your data is stored in another workbook or tool (like Google Sheets), you can simply copy the dataset (including headers) and paste it into Excel, starting from cell A1.
In this example, we're using a sample dataset with 15 customers of a fictional technology company between January and April 2024. It includes Customer ID, Sign-Up Date, Purchase Date, Revenue and additional fields such as Product Line, Region and Channel.
Next, you’ll want to confirm that every column has a clear purpose and consistent data type. In this case, we’re looking at:
Then, scroll through your data to spot anything unusual: blank dates, negative revenue, or the same customer having different sign-up dates across their transactions. Fixing these early ensures your final results aren't misleading.
There are different ways to group data into cohorts—by time, customer segment, acquisition source, or behavior. In this example, we'll use time-based cohorts to group customers by the month they made their first purchase.
If your dataset already includes a sign-up date column (like our sample does), you can skip directly to creating the Cohort Month column. If not, calculate it first.
Add a new column header called “First Purchase Date”. In the first cell below that header, enter this formula: “=MINIFS($C:$C,$A:$A,A2).”
Press Enter, then copy the formula down to all rows, and format this column as Date.
This formula searches through all Purchase Dates in column C, finds every transaction for the Customer ID in column A, and returns the earliest date. So if Customer C001 made purchases on Jan 10, Feb 15, and Apr 5, this shows "1/10/2024" in all three of their rows.
Now add another column called “Cohort Month”. In the first cell below that header, enter a formula that references your First Purchase Date (or Sign-Up Date) column: “=DATE(YEAR([First Purchase Date cell]),MONTH([First Purchase Date cell]),1).”
For example, if First Purchase Date is in column H, your formula would be “=DATE(YEAR(H2),MONTH(H2),1).”
Copy the formula down to all rows and format the column as a Date.
This converts each first purchase date to the first day of that month. Jan 10, Jan 15, and Jan 28 all become 1/1/2024. This way, all customers who joined in January belong to the same "January 2024" cohort, regardless of which specific day they purchased.
You already have the Cohort Month, which is when each customer joined. Now you need to add the date each individual transaction occurred and the number of months that passed between joining and purchasing.
Add a new column called “Transaction Month”. In the first cell below that header, enter the formula, “=DATE(YEAR(C2),MONTH(C2),1).” Replace C2 with your Purchase Date column. Copy the formula down and format the column as Date.
This converts each transaction's purchase date to the first day of that month, just like you did for Cohort Month, but for the actual transaction date instead of the first purchase date.
Next, add another column called “Period Number”. This tells you how many complete months have passed between a customer’s Cohort Month and Transaction Month, which is what you’ll use to measure retention over time.
In the first cell under the Period Number header, enter the formula, “=DATEDIF([Cohort Month cell],[Transaction Month cell],"M").”
For our example, we’ll use, “=DATEDIF(I2,J2,"M")” and copy the formula down to all rows.
You’ll see results like:
Go to the Insert tab on the Excel ribbon and select “PivotTable”.
In the dialog box, select your entire dataset and choose to place the PivotTable in a new worksheet. Then click OK to continue.
Inside the PivotTable, drag Cohort Month to the Rows area, the Period Number to the Columns area, and the Customer ID to the Values area. You’ll now see a table where each cell shows the number of active customers per period for each cohort.
By default, Excel counts all transactions, but for cohort analysis, you need to count each customer only once per period. For instance, if Customer C001 made two purchases in February, you want to count them once, not twice.
By default, Excel’s PivotTable will count all transaction rows, not unique customers.
In modern Windows versions like Microsoft 365 and Excel 2019/2021, you can change the aggregation to “Distinct Count” under Value Field Settings, but only if your data has been added to the Data Model before creating the PivotTable. If “Distinct Count” isn’t available in the dropdown, make sure you selected “Add this data to the Data Model” when you created the pivot.
If your version of Excel (such as Excel Online or older builds) doesn’t support Distinct Count, you’ll need to remove duplicate customer-period rows before pivoting, so each customer is only counted once per cohort period.
At this stage, your PivotTable displays raw customer counts, but retention analysis focuses on percentages.
First, create a new sheet titled "Retention Rates" in your workbook, then copy and paste the PivotTable's contents.
To calculate retention rates, divide each month’s count by the count in Month 0 for that same cohort. You can do this directly in Excel next to your PivotTable by referencing each value.
For example, if January’s Month 0 value is in cell B5 and Month 1 is in cell C5, your formula would be =C5/B5. Copy it across to calculate retention for each month and format the results as percentages.
Once complete, you’ll see a clear retention table that shows how many customers return month after month.
Once your retention rates are ready, visualize them to quickly identify trends.
Select your entire retention table, including the cohort months and percentage values. Go to the Home tab, click Conditional Formatting, and choose Color Scales.
Excel will automatically shade higher retention percentages with darker colors and lower ones with lighter tones, creating a simple heatmap that shows retention strength at a glance.
For example, the January 2024 cohorts show darker colors through Month 3, that suggests stronger retention, possibly due to better onboarding or more loyal customers. If newer cohorts fade faster, like in April 2024 in our example, it could mean discount campaigns or weaker post-purchase engagement are driving churn.
For FP&A teams, these patterns help you pinpoint when churn typically occurs, compare whether newer cohorts retain better than older ones, and build more accurate recurring revenue forecasts based on actual customer behavior.
The real power of cohort analysis lies in connecting what customers do with what the business decides next.
While Excel gives you this foundation, Vena makes it easier to track and analyze critical trends in real time through interactive dashboards.
Anything you can do in Excel, you can do in Vena. You’ll use the same formulas and methods of analysis you just learned, but with real-time data that updates automatically from your Vena database. You can also save new data points directly back to the database, making it easier to keep reports and forecasts up to date.
And using Vena Insights, you can create dashboards to monitor key metrics such as annual recurring revenue (ARR), churn rate, subscriber count, customer acquisition cost (CAC), lifetime value (LTV), and gross margin—all in one view—so you can share insights with leadership faster and make decisions with confidence.
A dashboard created with Vena Insights highlighting core SaaS metrics such as top opportunities by contract ARR and more.
That kind of visibility helped transform Sprout Social’s finance operations and business partnering. Before Vena, their finance team spent days consolidating data from multiple systems before they could analyze key SaaS metrics like CAC, LTV, and churn.
After rolling out Vena for SaaS, those same reports now update in real time through integrated dashboards, cutting reporting time from three days to under an hour and giving their leaders the agility to make faster, data-driven growth decisions.
Cohort analysis helps FP&A teams track how groups of customers behave over time—such as how long they stay subscribed, when they tend to churn, and how their average revenue changes. These insights make it easier to forecast recurring revenue, plan customer acquisition budgets and identify where retention efforts will have the most financial impact.
The most common cohort types include:
Most FP&A teams run a cohort analysis monthly or quarterly, depending on data volume and reporting cycles. For fast-moving SaaS or subscription businesses, monthly analysis helps detect customer churn risks early and validate the impact of retention or pricing strategies.
Take the course in Vena Academy to learn how to use Dynamic Arrays, LAMBDA formulas and more.
Start LearningJessica Tee Orika-Owunna, Senior Content Marketer for B2B SaaS and Finance Companies
Jessica Tee Orika-Owunna is a content strategist and writer with over seven years of experience creating and repurposing relatable, helpful content for global brands including Contentsquare, Softr, Hotjar and Vena. She specializes in turning everyday product, user, and subject matter expert insights into product-led content that answers real buyer questions and supports better business outcomes.
