RevOps
How To Create a Sales Pipeline on Sheets (Free Template)
Master your sales with a FREE sales pipeline template Google Sheets! Easily track leads, deals, and progress.
Table of Contents
For any sales team, a well-defined pipeline is the lifeblood of success. It provides a clear picture of potential deals, their progress through the sales cycle, and ultimately, the health of your revenue stream. However managing a complex pipeline can be a challenge, especially for smaller businesses or those just starting out.
This is where Google Sheets comes in. As a free and readily available tool, Google Sheets offers a fantastic platform to build a customizable sales pipeline tracker. In this blog, we'll guide you through the process of creating your own tracker, step-by-step, and explore how Superjoin, a powerful Google Sheets add-on, can further enhance your pipeline's functionality.
Why Use Google Sheets for Your Sales Pipeline?
Here's why Google Sheets is a great option for building your sales pipeline tracker:
Free and Accessible: Everyone has access to Google Sheets, making it a cost-effective solution for any business size.
Collaborative: Multiple team members can access and update the tracker simultaneously, fostering transparency and real-time communication.
Customizable: You can tailor the tracker to fit your specific sales process and data needs.
Integrations: Google Sheets integrates seamlessly with other Google Workspace apps, like Gmail and Calendar, for a streamlined workflow.
Things to Consider before Building Your Sales Pipeline Tracker
Let's get started! Here's a breakdown of the steps involved:
Step 1. Define Your Sales Stages:
The first step is to identify the distinct stages your prospects go through in your sales cycle. Common examples include:
Lead Generation - Identifying potential customers.
Qualification - Assessing a lead's fit for your product or service.
Proposal/Quote - Presenting your offer to the qualified lead.
Negotiation - Discussing terms and pricing.
Closed Won/Lost - The final outcome of the sales process.
Step 2. Design Your Spreadsheet:
Open a new Google Sheet and create a clear layout for your tracker.Let's cover some important aspects to consider:
Columns:
Prospect Name: Company or individual name.
Contact Information: Name, email, phone number.
Sales Stage: Dropdown menu or a designated column for each stage.
Deal Value: Estimated value of the sale.
Close Date: Target date for closing the deal.
Sales Rep: Assigned salesperson (optional).
Additional Columns: You can include custom columns depending on your needs, such as "Notes," "Next Steps," or "Competitors."
Rows:
Each row represents a potential deal.
Step 3. Populate Your Tracker:
Start by adding leads and their details to the relevant columns. Utilize conditional formatting to visually differentiate based on the sales stage (e.g., color-code each stage).
Step 4. Formulas and Functions:
Leverage Google Sheets' built-in formulas to automate calculations and gain valuable insights. Here are some examples:
SUMIFS: Calculate the total potential revenue in each sales stage.
COUNTIFS: Track the number of deals at different stages.
VLOOKUP: Automatically populate data from other sheets within your workbook.
Step 5. Charts and Dashboards:
Visualize your sales pipeline with charts and graphs. Google Sheets offers a variety of options, including bar charts, pie charts, and line graphs. Create insightful dashboards to monitor key metrics and identify trends, like conversion rates or average deal size by stage.
Supercharge Your Sales Pipeline Template Google Sheets with Superjoin
While Google Sheets provides a solid foundation, Superjoin takes your sales pipeline tracker to the next level. Superjoin is a powerful add-on that unlocks a plethora of features to streamline your sales process and boost efficiency.
Automated Workflows: Simplify repetitive tasks with automated workflows. Set up triggers that automatically update stages, send email notifications, or assign tasks based on specific criteria.
A Step-by-Step Guide to Create a Sales Pipeline in Sheets using Hubspot Data
HubSpot empowers businesses to cultivate strong customer relationships through centralized contact and deal management. But sometimes, you might need to analyze your data in a different format, like the familiar and versatile world of Google Sheets.
HubSpot's default reporting system lacks built-in scheduling for exports. This means manually exporting reports at regular intervals and relying on email reminders for new data. This approach can be inefficient and lead to outdated information.
Superjoin offers a solution to automate HubSpot report exports to Google Sheets. Here's how it works:
A. Install Superjoin:
Go to your Google Sheets account and access the Extensions menu.
Select "Add-ons" > “Get add ons” to open the Google Workspace Marketplace.
Search for "Superjoin" and install the app.
B. Connect Superjoin to HubSpot:
Launch Superjoin from the Extensions > Add-ons option>Launch Superjoin.
Choose HubSpot from the list of available Connections.
Click on Connect: Click on Connect and follow the on-screen instructions to connect your HubSpot account to Google Sheets seamlessly.
Choose Data Objects: Select the specific data objects you wish to import from HubSpot, such as Deals data.
Customize Field Selection: Customize your data import by choosing which fields to include and preview your data visually before importing.
Initiate Import: Click on Import, and watch as your HubSpot data populates your Google spreadsheet instantly.
Set up a Schedule: Superjoin lets you refresh your imported data instantly with one click or set it to auto-refresh on a pre-set schedule.
The data will be seamlessly imported into your Google Sheet.
Sales Pipeline Stages
Let's define the typical stages in a sales pipeline:
Lead: Initial contact with a potential customer.
Qualified Lead: Lead has been qualified based on certain criteria.
Proposal Sent: A proposal or quote has been sent to the potential customer.
Negotiation: Ongoing discussions and negotiations with the potential customer.
Closed Won: Sale has been successfully completed.
Closed Lost: Lead did not result in a sale.
How To Build Your Sales Pipeline Tracker
In your Google spreadsheet, create a new sheet to track your sales pipeline. Use data from your HubSpot import, with "Forecast Category" in the first column and months listed across the top row.
1. Applying the First Formula:
=iferror(INDEX('⚡️Opportunity'!$A$3:$M$7,match($B3,'⚡️Opportunity'!$A$3:$A$7,0),match(C$2,'⚡️Opportunity'!$A$3:$M$3,0)),)
Formula Explanation:
This formula uses INDEX and MATCH functions to look up the value from the Opportunity sheet.
INDEX(‘Opportunity’!$A$3:$M$7, finds the correct value within the specified range.
MATCH($B3,’Opportunity’!$A$3:$A$7,0) matches the forecast category.
MATCH(C$2,’Opportunity’!$A$3:$M$3,0) matches the month.
IFERROR is used to handle any errors that might arise from mismatches.
Select cell B3.
Drag the formula across the row to fill in the values for each month.
Then, drag down to fill in for each forecast category.
2. Applying the Second Formula for Closed Won:
=IF(MAX(‘Opportunity’!$B$3:$M$3)=C$7, TEXT(C$11, "$#,##0.0,K"), "")
Formula Explanation:
This formula checks if the maximum value in the month row from the Opportunity sheet matches the current month in the target sheet.
If it matches, it formats the value in C$11 as currency.
3. Enter the following formula in the appropriate cell where you want to calculate the "Closed Won" label:
"Highlight the cell that holds the formula you entered.
Drag the formula across the row to apply it to each month.
Creating Charts
4. Now, create your sales pipeline tracker chart. Select the entire range, then navigate to the top menu and click on 'Insert' followed by 'Chart'.
5. Change the chart to ‘Combo Chart’
6. Click the ‘Customize’ tab and select ‘Series’.
Now click on the drop-down and select ‘Target’
7. Let's review each sales prediction category (Pipeline, Best Case, etc.). Adjust the transparency of each category on the chart to show the difference in their total dollar amounts. Make sure 'Closed Won' is the most opaque (least transparent) category, highlighting its importance.
8. Switch to the Opportunity Sheet: Navigate to the sheet named "Opportunity" in your workbook.
9. Select Data Range: Highlight all the cells containing your data by clicking and dragging your cursor from cell A3 to cell M7.
10. Insert a Combo Chart: Click on the "Insert" tab in the ribbon. Locate the "Charts" group and click the dropdown arrow under "Chart."
11. Choose the "Combo Chart" option from the available chart types.
11. Format and Customize: Excel will automatically create a combo chart. You can now customize the chart by double-clicking on different chart elements (e.g., bars, lines, axes) to access formatting options. These options allow you to change colors, and fonts, add labels, and adjust the overall layout of the chart to best represent your data.
With Superjoin and Google Sheets, you can now easily see how your sales deals progress over the year, all thanks to your Hubspot data being readily available in just a few minutes.
Skip building your tracker and use our free, ready-made Sales Pipeline Template in Google Sheets! This lets you start monitoring your sales process immediately.
Enhancing the Usefulness and Scope:
Focus on the visual aspect: Superjoin offers a user-friendly interface with customizable dashboards that make pipeline data easy to understand.
Highlight customization: Emphasize that Superjoin allows you to tailor pipeline stages to your specific sales process.
Integrations: If Superjoin integrates with your CRM or other sales tools, mention that for added convenience.
Increased Productivity: By ditching manual data entry and scattered spreadsheets, Superjoin automates tasks and streamlines your workflow. This frees up valuable time for your sales reps to focus on what they do best – closing deals.
Conclusion
Keeping a pulse on your sales pipeline is essential for any business. By implementing a sales pipeline tracker, you can gain valuable insights into your sales process, identify bottlenecks, and ultimately close more deals. Whether you choose to use our sales pipeline template for Google Sheets or build your own from scratch using the steps outlined above, Google Sheets provides a powerful and user-friendly platform to manage your sales pipeline.
So, take control of your sales process today and start tracking your pipeline in Google Sheets!
Struggling to keep your Google Sheets data up-to-date? Superjoin, a popular spreadsheet automation tool used by over 350,000 professionals, can help! It saves you time by automatically syncing data from various sources like CRM, databases, and ad platforms into your Sheets.
Bid farewell to tedious exports and repetitive tasks. With Superjoin, you can add 1 additional day to your week. Try Superjoin out for free or schedule a demo.
For any sales team, a well-defined pipeline is the lifeblood of success. It provides a clear picture of potential deals, their progress through the sales cycle, and ultimately, the health of your revenue stream. However managing a complex pipeline can be a challenge, especially for smaller businesses or those just starting out.
This is where Google Sheets comes in. As a free and readily available tool, Google Sheets offers a fantastic platform to build a customizable sales pipeline tracker. In this blog, we'll guide you through the process of creating your own tracker, step-by-step, and explore how Superjoin, a powerful Google Sheets add-on, can further enhance your pipeline's functionality.
Why Use Google Sheets for Your Sales Pipeline?
Here's why Google Sheets is a great option for building your sales pipeline tracker:
Free and Accessible: Everyone has access to Google Sheets, making it a cost-effective solution for any business size.
Collaborative: Multiple team members can access and update the tracker simultaneously, fostering transparency and real-time communication.
Customizable: You can tailor the tracker to fit your specific sales process and data needs.
Integrations: Google Sheets integrates seamlessly with other Google Workspace apps, like Gmail and Calendar, for a streamlined workflow.
Things to Consider before Building Your Sales Pipeline Tracker
Let's get started! Here's a breakdown of the steps involved:
Step 1. Define Your Sales Stages:
The first step is to identify the distinct stages your prospects go through in your sales cycle. Common examples include:
Lead Generation - Identifying potential customers.
Qualification - Assessing a lead's fit for your product or service.
Proposal/Quote - Presenting your offer to the qualified lead.
Negotiation - Discussing terms and pricing.
Closed Won/Lost - The final outcome of the sales process.
Step 2. Design Your Spreadsheet:
Open a new Google Sheet and create a clear layout for your tracker.Let's cover some important aspects to consider:
Columns:
Prospect Name: Company or individual name.
Contact Information: Name, email, phone number.
Sales Stage: Dropdown menu or a designated column for each stage.
Deal Value: Estimated value of the sale.
Close Date: Target date for closing the deal.
Sales Rep: Assigned salesperson (optional).
Additional Columns: You can include custom columns depending on your needs, such as "Notes," "Next Steps," or "Competitors."
Rows:
Each row represents a potential deal.
Step 3. Populate Your Tracker:
Start by adding leads and their details to the relevant columns. Utilize conditional formatting to visually differentiate based on the sales stage (e.g., color-code each stage).
Step 4. Formulas and Functions:
Leverage Google Sheets' built-in formulas to automate calculations and gain valuable insights. Here are some examples:
SUMIFS: Calculate the total potential revenue in each sales stage.
COUNTIFS: Track the number of deals at different stages.
VLOOKUP: Automatically populate data from other sheets within your workbook.
Step 5. Charts and Dashboards:
Visualize your sales pipeline with charts and graphs. Google Sheets offers a variety of options, including bar charts, pie charts, and line graphs. Create insightful dashboards to monitor key metrics and identify trends, like conversion rates or average deal size by stage.
Supercharge Your Sales Pipeline Template Google Sheets with Superjoin
While Google Sheets provides a solid foundation, Superjoin takes your sales pipeline tracker to the next level. Superjoin is a powerful add-on that unlocks a plethora of features to streamline your sales process and boost efficiency.
Automated Workflows: Simplify repetitive tasks with automated workflows. Set up triggers that automatically update stages, send email notifications, or assign tasks based on specific criteria.
A Step-by-Step Guide to Create a Sales Pipeline in Sheets using Hubspot Data
HubSpot empowers businesses to cultivate strong customer relationships through centralized contact and deal management. But sometimes, you might need to analyze your data in a different format, like the familiar and versatile world of Google Sheets.
HubSpot's default reporting system lacks built-in scheduling for exports. This means manually exporting reports at regular intervals and relying on email reminders for new data. This approach can be inefficient and lead to outdated information.
Superjoin offers a solution to automate HubSpot report exports to Google Sheets. Here's how it works:
A. Install Superjoin:
Go to your Google Sheets account and access the Extensions menu.
Select "Add-ons" > “Get add ons” to open the Google Workspace Marketplace.
Search for "Superjoin" and install the app.
B. Connect Superjoin to HubSpot:
Launch Superjoin from the Extensions > Add-ons option>Launch Superjoin.
Choose HubSpot from the list of available Connections.
Click on Connect: Click on Connect and follow the on-screen instructions to connect your HubSpot account to Google Sheets seamlessly.
Choose Data Objects: Select the specific data objects you wish to import from HubSpot, such as Deals data.
Customize Field Selection: Customize your data import by choosing which fields to include and preview your data visually before importing.
Initiate Import: Click on Import, and watch as your HubSpot data populates your Google spreadsheet instantly.
Set up a Schedule: Superjoin lets you refresh your imported data instantly with one click or set it to auto-refresh on a pre-set schedule.
The data will be seamlessly imported into your Google Sheet.
Sales Pipeline Stages
Let's define the typical stages in a sales pipeline:
Lead: Initial contact with a potential customer.
Qualified Lead: Lead has been qualified based on certain criteria.
Proposal Sent: A proposal or quote has been sent to the potential customer.
Negotiation: Ongoing discussions and negotiations with the potential customer.
Closed Won: Sale has been successfully completed.
Closed Lost: Lead did not result in a sale.
How To Build Your Sales Pipeline Tracker
In your Google spreadsheet, create a new sheet to track your sales pipeline. Use data from your HubSpot import, with "Forecast Category" in the first column and months listed across the top row.
1. Applying the First Formula:
=iferror(INDEX('⚡️Opportunity'!$A$3:$M$7,match($B3,'⚡️Opportunity'!$A$3:$A$7,0),match(C$2,'⚡️Opportunity'!$A$3:$M$3,0)),)
Formula Explanation:
This formula uses INDEX and MATCH functions to look up the value from the Opportunity sheet.
INDEX(‘Opportunity’!$A$3:$M$7, finds the correct value within the specified range.
MATCH($B3,’Opportunity’!$A$3:$A$7,0) matches the forecast category.
MATCH(C$2,’Opportunity’!$A$3:$M$3,0) matches the month.
IFERROR is used to handle any errors that might arise from mismatches.
Select cell B3.
Drag the formula across the row to fill in the values for each month.
Then, drag down to fill in for each forecast category.
2. Applying the Second Formula for Closed Won:
=IF(MAX(‘Opportunity’!$B$3:$M$3)=C$7, TEXT(C$11, "$#,##0.0,K"), "")
Formula Explanation:
This formula checks if the maximum value in the month row from the Opportunity sheet matches the current month in the target sheet.
If it matches, it formats the value in C$11 as currency.
3. Enter the following formula in the appropriate cell where you want to calculate the "Closed Won" label:
"Highlight the cell that holds the formula you entered.
Drag the formula across the row to apply it to each month.
Creating Charts
4. Now, create your sales pipeline tracker chart. Select the entire range, then navigate to the top menu and click on 'Insert' followed by 'Chart'.
5. Change the chart to ‘Combo Chart’
6. Click the ‘Customize’ tab and select ‘Series’.
Now click on the drop-down and select ‘Target’
7. Let's review each sales prediction category (Pipeline, Best Case, etc.). Adjust the transparency of each category on the chart to show the difference in their total dollar amounts. Make sure 'Closed Won' is the most opaque (least transparent) category, highlighting its importance.
8. Switch to the Opportunity Sheet: Navigate to the sheet named "Opportunity" in your workbook.
9. Select Data Range: Highlight all the cells containing your data by clicking and dragging your cursor from cell A3 to cell M7.
10. Insert a Combo Chart: Click on the "Insert" tab in the ribbon. Locate the "Charts" group and click the dropdown arrow under "Chart."
11. Choose the "Combo Chart" option from the available chart types.
11. Format and Customize: Excel will automatically create a combo chart. You can now customize the chart by double-clicking on different chart elements (e.g., bars, lines, axes) to access formatting options. These options allow you to change colors, and fonts, add labels, and adjust the overall layout of the chart to best represent your data.
With Superjoin and Google Sheets, you can now easily see how your sales deals progress over the year, all thanks to your Hubspot data being readily available in just a few minutes.
Skip building your tracker and use our free, ready-made Sales Pipeline Template in Google Sheets! This lets you start monitoring your sales process immediately.
Enhancing the Usefulness and Scope:
Focus on the visual aspect: Superjoin offers a user-friendly interface with customizable dashboards that make pipeline data easy to understand.
Highlight customization: Emphasize that Superjoin allows you to tailor pipeline stages to your specific sales process.
Integrations: If Superjoin integrates with your CRM or other sales tools, mention that for added convenience.
Increased Productivity: By ditching manual data entry and scattered spreadsheets, Superjoin automates tasks and streamlines your workflow. This frees up valuable time for your sales reps to focus on what they do best – closing deals.
Conclusion
Keeping a pulse on your sales pipeline is essential for any business. By implementing a sales pipeline tracker, you can gain valuable insights into your sales process, identify bottlenecks, and ultimately close more deals. Whether you choose to use our sales pipeline template for Google Sheets or build your own from scratch using the steps outlined above, Google Sheets provides a powerful and user-friendly platform to manage your sales pipeline.
So, take control of your sales process today and start tracking your pipeline in Google Sheets!
Struggling to keep your Google Sheets data up-to-date? Superjoin, a popular spreadsheet automation tool used by over 350,000 professionals, can help! It saves you time by automatically syncing data from various sources like CRM, databases, and ad platforms into your Sheets.
Bid farewell to tedious exports and repetitive tasks. With Superjoin, you can add 1 additional day to your week. Try Superjoin out for free or schedule a demo.
FAQs
Is the free sales pipeline template Google Sheets customizable?
Is the free sales pipeline template Google Sheets customizable?
What if I need more features than what the template offers?
What if I need more features than what the template offers?
What data should I track in my sales pipeline?
What data should I track in my sales pipeline?
Automatic Data Pulls
Visual Data Preview
Set Alerts
other related blogs
Try it now