Build a CRM Tracker in Google Sheets

Build a multi-tab CRM with automated KPIs and pipeline tracking.

Introduction

30 Second Summary

Every business tracks customers somehow. Whether it's sticky notes, a messy spreadsheet, or an expensive tool you never fully set up, keeping tabs on who you've talked to and what they need is one of the hardest parts of growing a business.

In this project, you will build a complete CRM (Customer Relationship Management) tracker in Google Sheets that manages contacts, tracks deals through your sales pipeline, and gives you an automated dashboard with real-time KPIs.

What You'll Build

A multi-tab Google Sheets CRM with a Deals pipeline, Contacts database, Settings tab for dropdown values, and a Dashboard with automated formulas and a pipeline chart.

By the end of this project, you'll have:

A Deals pipeline and Contacts database with validated dropdowns that enforce clean, consistent data entry.
An automated Dashboard with KPI formulas (SUMIFS, COUNTIF, IFERROR) that update in real time as you add deals.
Conditional formatting rules that color-code pipeline stages and highlight overdue opportunities at a glance.
Secret Mission: A weighted forecast formula and stale-deal alert system that flags deals not updated in 14+ days.

Are there any prerequisites?

All you need is a free Google account. No paid software, no installations, no prior spreadsheet experience required.

Set Up Your Google Sheet and Settings Tab

Every CRM needs consistent data to work properly. If one deal says "Qualified" and another says "Qual", your reporting formulas will break.

The solution is a dedicated Settings tab that defines exactly which values are allowed across your spreadsheet. In this step, you'll create your Google Sheets file, set up four tabs, and populate the Settings tab with standardized values.

In this step, get ready to:

Create a new Google Sheet with four tabs for your CRM.
Populate the Settings tab with deal stages, lead sources, and owner names.
Format and freeze the Settings tab so it's easy to read and maintain.

Create a new Google Sheet with four tabs

Your CRM tracker lives in a single spreadsheet with four tabs, each serving a different purpose. Let's create the file and all four tabs now.

Open a new browser tab and go to sheets.google.com.
Click the Blank spreadsheet tile (the large + icon) to create a new sheet.
Click the title in the top left corner (it says "Untitled spreadsheet") and rename it to CRM Tracker.

Your new spreadsheet has one default tab called "Sheet1". You need four tabs total: Deals, Contacts, Dashboard, and Settings.

Double-click the Sheet1 tab at the bottom of the screen.
Rename it to Deals and press Enter.
Click the + icon to the left of the tab bar to add a new tab.
Double-click the new tab and rename it to Contacts.
Click + again and rename the new tab to Dashboard.
Click + one more time and rename the new tab to Settings.

You should now see four tabs along the bottom of your spreadsheet: Deals, Contacts, Dashboard, and Settings.

Don't see all four tabs?

If a tab is missing, click the + icon at the bottom left to add it. Double-click any tab name to rename it.

If you accidentally created extra tabs, right-click the unwanted tab and select Delete.

Help me fix my tabs in Google Sheets.

Add standardized values to the Settings tab

The Settings tab is the control center for your CRM. Every dropdown menu in your Deals and Contacts tabs will pull its options from here.

Click the Settings tab at the bottom of the screen to switch to it.
Click cell A1 and type Deal Stages.
Click cell B1 and type Lead Sources.
Click cell C1 and type Owners.

Now fill in the values below each header. These are the exact options that will appear in your dropdown menus later.

In cells A2 through A7, enter the following deal stages (one per cell): Lead, Qualified, Proposal, Negotiation, Won, Lost.
In cells B2 through B6, enter the following lead sources: Website, Referral, Cold Email, LinkedIn, Event.
In cells C2 through C4, enter the following owner names: Alex, Jordan, Sam.

Why a separate Settings tab?

Keeping your dropdown values in one place means you can update them once and every dropdown across your CRM updates automatically. It also prevents typos like "Qualfied" from sneaking into your data and breaking your formulas.

Format and freeze the Settings tab

A bit of formatting makes the Settings tab easier to scan and signals to anyone else using this spreadsheet that Row 1 contains headers, not data.

Select cells A1:C1 by clicking A1 and dragging to C1.
Click the Bold button in the toolbar (or press Cmd+B on macOS, Ctrl+B on Windows).
Click View in the top menu bar.
Hover over Freeze.
Select 1 row.

You should see a thick gray line appear below Row 1. This means Row 1 is frozen and will stay visible as you scroll down.

Finally, add a note explaining what this tab is for.

Right-click cell A1.
Select Insert note from the context menu.
Type: This tab controls the dropdown values used across the CRM. Edit values here to update all dropdowns in Deals and Contacts..
Click anywhere outside the note to close it.

What is a note in Google Sheets?

A note is a small text annotation attached to a cell. It appears when you hover over the cell. Notes are different from comments. Comments are for collaboration (they tag people and have threads), while notes are for static documentation.

Your Settings tab should now have bold headers in Row 1 with a frozen row, values filled in below each header, and a note on cell A1.

Row didn't freeze?

Make sure you're on the Settings tab when you apply the freeze. Each tab has its own freeze settings. Go to View > Freeze > 1 row again while on the Settings tab.

Help me freeze Row 1 in my Google Sheet.

Your spreadsheet's foundation is set. Next up, you'll build the Contacts tab with headers and data validation rules that pull directly from this Settings tab.

Build the Contacts Tab

Your Settings tab is locked and loaded with standardized dropdown values. Now it's time to build the first tab your team will actually interact with daily.

The Contacts tab is your master database of people and companies. In this step, you'll set up its structure, connect it to your Settings tab with data validation dropdowns, and fill it with sample data to test everything works.

In this step, get ready to:

Create column headers for all contact fields.
Add data validation dropdowns and date formatting.
Enter sample contacts and format the tab for readability.

Set up your contact headers

Headers define what information you track for each contact. Getting these right from the start means your CRM captures everything you need without clutter.

Switch to the Contacts tab at the bottom of your spreadsheet.
Click on cell A1 and type Contact ID.
In cell B1, type First Name.
Continue across Row 1 with the remaining headers: Last Name (C1), Email (D1), Phone (E1), Company (F1), Lead Source (G1), Date Added (H1), Notes (I1).

Why these specific columns?

Contact ID gives each person a unique identifier so you can reference them from the Deals tab. First Name and Last Name are split into separate columns so you can sort and filter by either one.

Lead Source will connect to your Settings tab via a dropdown, keeping your data consistent. Date Added lets you track when contacts entered your pipeline.

Add data validation and date formatting

Data validation forces anyone using this sheet to pick from your pre-approved list of lead sources. This prevents typos like "Linked In" vs "LinkedIn" that would break your dashboard formulas later.

Select cells G2:G100 in the Lead Source column. Click on cell G2, then hold Shift and click G100.
Click Data in the menu bar.
Select Data validation.
Click Add rule.
Under Criteria, choose Dropdown (from a range).
In the range field, type Settings!B2:B6 to point to your Lead Sources list.
Click Done.

What is data validation?

Data validation restricts what values can go into a cell. By pointing to your Settings tab, you create a single source of truth. If you ever need to add a new lead source, you update it once in Settings and it automatically appears in the dropdown everywhere.

Now format the Date Added column so dates display consistently.

Select cells H2:H100 in the Date Added column.
Click Format in the menu bar.
Hover over Number, then select Date.

Verify your dropdown works

Click on cell G2. You should see a small dropdown arrow appear. Click it and confirm you see all five lead sources from your Settings tab: Website, Referral, Cold Email, LinkedIn, Event.

Dropdown not showing your values?

Make sure you typed the range as Settings!B2:B6 with the exclamation mark between the tab name and the cell range. Double-check that your Settings tab has values in cells B2 through B6 with no blank rows in between.

Help me fix my data validation dropdown

Enter sample contacts and format the tab

Sample data lets you test that your dropdowns, date formatting, and future dashboard formulas all work correctly before relying on real contact information.

Click on cell A2 and start entering your first sample contact across the row.

Enter 5 contacts using realistic data. Here is an example to follow:

Row 2: C001, Sarah, Chen, sarah.chen@techcorp.com, 555-0101, TechCorp, Website, 2025-01-15, Met at product demo.
Row 3: C002, Marcus, Rivera, marcus.r@innovate.io, 555-0102, Innovate Inc, Referral, 2025-02-03, Referred by David Kim.
Row 4: C003, Priya, Patel, priya@startuplab.co, 555-0103, StartupLab, LinkedIn, 2025-02-20, Connected after webinar.
Row 5: C004, James, O'Brien, james.ob@greenfield.com, 555-0104, Greenfield Solutions, Cold Email, 2025-03-08, Responded to outbound campaign.
Row 6: C005, Aisha, Nakamura, aisha.n@bluewave.dev, 555-0105, BlueWave Digital, Event, 2025-03-22, Met at SaaS Summit.

Use the dropdown for Lead Source

When you reach column G for each contact, click the dropdown arrow and select the lead source instead of typing it manually. This confirms your data validation is working correctly.

Now format the headers so the tab is easy to scan.

Select all header cells in Row 1 by clicking on the row number 1 on the left side.
Click the Bold button in the toolbar (or press Cmd+B on macOS or Ctrl+B on Windows).
Click the Fill color button (paint bucket icon) in the toolbar.
Select a light blue color from the palette.
Click View in the menu bar.
Hover over Freeze, then select 1 row.

Why freeze the header row?

Freezing Row 1 keeps your column headers visible as you scroll down through dozens or hundreds of contacts. Without this, you'd lose track of which column is which once you scroll past the first screen.

Your Contacts tab should now show bold headers on a light blue background in Row 1, with 5 sample contacts filled in below. Scrolling down keeps the headers pinned at the top.

Headers not staying visible when you scroll?

Make sure you selected 1 row under View > Freeze. You should see a thick gray line appear below Row 1. If you accidentally froze more rows, go back to View > Freeze > 1 row to reset it.

Help me fix my frozen rows in Google Sheets

Your Contacts tab is ready to hold every lead you'll ever track. Next up, you'll build the Deals pipeline tab where the real action happens.

Build the Deals Pipeline Tab

Your Contacts tab is ready with validated data and clean formatting. Now it's time to build the main event of any CRM: the Deals pipeline.

The Deals tab tracks every sales opportunity from first conversation to closed deal. You'll set up columns for value, stage, ownership, and key dates so nothing slips through the cracks.

In this step, get ready to:

Set up 12 column headers for your Deals pipeline.
Add data validation dropdowns for Stage, Owner, and Lead Source columns.
Enter sample deals across different pipeline stages to test the system.

Set up your Deals tab headers

The Deals tab needs more columns than your Contacts tab because each deal has a lifecycle. You need to track who owns it, what stage it's in, how much it's worth, and when it's expected to close.

Switch to the Deals tab at the bottom of your spreadsheet.
Click on cell A1 and type Deal ID.
Continue across Row 1, entering one header per cell in this order:

B1: Deal Name.
C1: Company.
D1: Contact.
E1: Deal Value.
F1: Stage.
G1: Owner.
H1: Lead Source.
I1: Creation Date.
J1: Expected Close Date.
K1: Last Updated.
L1: Next Step.

Select all headers (cells A1:L1) and click the Bold button in the toolbar (or press Cmd+B on macOS or Ctrl+B on Windows).
With A1:L1 still selected, click the Fill color button (paint bucket icon) in the toolbar and choose a light blue background.
Freeze Row 1 by clicking View in the top menu bar.
Select Freeze, then 1 row.

Why so many columns?

Each column serves a specific purpose in helping you prioritize and follow up. The Last Updated column helps you spot deals going stale. The Next Step column keeps you accountable for follow-ups.

Professional CRMs like Salesforce or HubSpot track the same fields. Building them yourself helps you understand what matters in pipeline management.

Add data validation dropdowns and currency formatting

Just like you did on the Contacts tab, you'll connect three columns to your Settings tab so every entry uses a consistent dropdown value. You'll also format the Deal Value column as currency.

Select cells F2:F100 (the Stage column). Click cell F2, then hold Shift and click F100.
Click Data in the top menu bar.
Select Data validation.
Click Add rule.
Under Criteria, choose Dropdown (from a range).
In the range field, type Settings!A2:A7 to pull your Deal Stages values.
Click Done.

Now repeat for the Owner column.

Select cells G2:G100 (the Owner column).
Click Data > Data validation > Add rule.
Choose Dropdown (from a range) and enter Settings!C2:C4.
Click Done.

Finally, set up the Lead Source column.

Select cells H2:H100 (the Lead Source column).
Click Data > Data validation > Add rule.
Choose Dropdown (from a range) and enter Settings!B2:B6.
Click Done.

Now format the Deal Value column as currency so dollar amounts display cleanly.

Select cells E2:E100 (the Deal Value column).
Click Format in the top menu bar.
Select Number, then Currency.

What did the currency format do?

Any number you type in column E will now automatically display with a dollar sign and two decimal places. This makes it much easier to read deal values at a glance, and ensures the Dashboard formulas can sum them correctly later.

Test your dropdowns by clicking on cell F2. You should see a dropdown arrow appear with your six Deal Stages (Lead, Qualified, Proposal, Negotiation, Won, Lost).
Click on cell G2 and confirm you see your three Owner names (Alex, Jordan, Sam).

Don't see a dropdown arrow?

Make sure you selected starting from row 2, not row 1. Data validation on the header row would override your header text.

Double-check the range you typed matches your Settings tab exactly. For example, Settings!A2:A7 must include the exclamation mark and correct cell references.

Still stuck? Help me troubleshoot why my data validation dropdown isn't appearing in my Deals tab.

Enter sample deals to test your pipeline

Sample data lets you verify that your dropdowns, formatting, and future Dashboard formulas all work correctly. You'll enter 8-10 deals spread across different pipeline stages.

Click on cell A2 to start entering your first deal.
Enter deals row by row. For each deal, fill in: a Deal ID (like D001, D002, etc.), a Deal Name, Company, Contact name, Deal Value, Stage, Owner, Lead Source, Creation Date, Expected Close Date, Last Updated date, and Next Step.
Use the dropdown menus for Stage, Owner, and Lead Source columns. Type numeric dollar amounts in the Deal Value column (e.g. 5000 for $5,000).
Vary your deal values between $1,000 and $25,000.
Make sure you include at least 2 deals with the Stage set to Won.
Include at least 1 deal with the Stage set to Lost.
Distribute the remaining deals across Lead, Qualified, Proposal, and Negotiation stages.

Make your sample data realistic

Use real-sounding company names and deal names like "Acme Corp Website Redesign" or "TechStart Annual License". Realistic data makes it easier to spot issues when you build the Dashboard in the next step.

Why do I need Won and Lost deals?

Your Dashboard will calculate a win rate by dividing Won deals by the total of Won plus Lost. Without at least one of each, those formulas would return errors or zeros.

After entering all your deals, scroll through the rows and confirm that Deal Value amounts display with dollar signs and two decimal places.
Check that every Stage, Owner, and Lead Source entry came from a dropdown (no manually typed values).

Deal values not showing as currency?

If you entered values before applying the currency format, select the cells with values, then apply Format > Number > Currency again. Make sure you typed plain numbers (like 5000) without a dollar sign.

Need more help? Help me fix my currency formatting in Google Sheets.

✔️ Awesome, I've got everything!

Great. Your Deals tab should have 12 bold headers with a light blue background, frozen Row 1, three dropdown-validated columns, currency formatting on Deal Value, and 8-10 sample deals across all pipeline stages.

ⓧ I'd like to double check my setup

Here is what your Deals tab should look like:

Row 1 headers (bold, light blue, frozen): Deal ID (A), Deal Name (B), Company (C), Contact (D), Deal Value (E), Stage (F), Owner (G), Lead Source (H), Creation Date (I), Expected Close Date (J), Last Updated (K), Next Step (L).
F2:F100 has a dropdown validated from Settings!A2:A7 (Deal Stages).
G2:G100 has a dropdown validated from Settings!C2:C4 (Owners).
H2:H100 has a dropdown validated from Settings!B2:B6 (Lead Sources).
E2:E100 is formatted as currency.
8-10 sample deals with values between $1,000 and $25,000. At least 2 Won, 1 Lost, and the rest distributed across Lead, Qualified, Proposal, and Negotiation.

Your pipeline is loaded and ready. Next up, you'll build a Dashboard that automatically calculates KPIs from this data. Every deal you add from now on will feed into real-time reporting.

Build the Dashboard with Automated Formulas

Your Deals and Contacts tabs are fully loaded with sample data. The problem is that you have no quick way to see how your pipeline is performing without manually counting deals or adding up values.

In this step, you will build a Dashboard tab that uses formulas to automatically calculate your key sales metrics. Every time you add or update a deal, the dashboard recalculates instantly.

In this step, get ready to:

Create KPI formulas for total pipeline value and total revenue.
Add win rate and average deal size metrics.
Build a deals-by-stage summary table with a bar chart.

Create the KPI section with pipeline value and revenue formulas

KPIs (Key Performance Indicators) are the numbers a sales team checks every day. You will place these at the top of the Dashboard tab so they are the first thing you see.

Switch to the Dashboard tab at the bottom of your spreadsheet.
Click cell A1 and type Total Pipeline Value.
Click cell B1 and enter this formula:

=IFERROR(SUMIFS(Deals!E2:E100,Deals!F2:F100,"<>Won",Deals!F2:F100,"<>Lost"),0)

Click cell A2 and type Total Revenue (Won).
Click cell B2 and enter this formula:

=IFERROR(SUMIFS(Deals!E2:E100,Deals!F2:F100,"Won"),0)

Press Enter to confirm the formula.

What do these formulas do?

SUMIFS adds up values in one column only when corresponding cells in another column meet your criteria. The <>Won and <>Lost criteria mean "not equal to Won" and "not equal to Lost". This gives you only the deals still in play.

IFERROR wraps the formula so that if there is no data yet (which would cause a divide-by-zero or reference error), the cell shows 0 instead of an ugly error message.

You should see dollar values appear in B1 and B2 based on the sample deals you entered earlier.

Seeing a 0 when you expect a number?

Make sure your Deals tab has at least one deal with a value in column E and a stage in column F. If you typed "won" in lowercase instead of "Won" with a capital W, the formula will not match. Check that your Stage column values exactly match the Settings tab.

help me figure out why my SUMIFS formula returns 0

Add win rate and average deal size formulas

These next three metrics tell you how effective your sales process is. Win rate shows what percentage of closed deals you actually won. Average deal size tells you the typical revenue per successful deal.

Click cell A3 and type Deals Won.
Click cell B3 and enter this formula:

=COUNTIF(Deals!F2:F100,"Won")

Click cell A4 and type Win Rate.
Click cell B4 and enter this formula:

=IFERROR(COUNTIF(Deals!F2:F100,"Won")/(COUNTIF(Deals!F2:F100,"Won")+COUNTIF(Deals!F2:F100,"Lost")),0)

Click cell A5 and type Average Deal Size.
Click cell B5 and enter this formula:

=IFERROR(SUMIFS(Deals!E2:E100,Deals!F2:F100,"Won")/COUNTIF(Deals!F2:F100,"Won"),0)

Press Enter to confirm.

What do these formulas do?

COUNTIF counts how many cells in the Stage column match a specific value. The Win Rate formula divides Won deals by total closed deals (Won + Lost). The Average Deal Size divides total Won revenue by the number of Won deals.

Both Win Rate and Average Deal Size are wrapped in IFERROR because if you have zero Won or zero Lost deals, the division would produce a #DIV/0! error.

Cell B3 should show a count (like 2), B4 should show a decimal between 0 and 1, and B5 should show a dollar amount.

Format Win Rate as a percentage

Select cell B4, then go to Format > Number > Percent. This displays the decimal as a readable percentage like 67%.

Seeing #DIV/0! in your cells?

This means the IFERROR wrapper is missing or mistyped. Double-click the cell and check that your formula starts with =IFERROR( and ends with ,0). Also confirm your Deals tab has at least 2 Won deals and 1 Lost deal for the Win Rate to calculate.

help me fix my Win Rate formula

Build the deals-by-stage summary table and chart

A summary table breaks down your pipeline by stage so you can see where deals are clustering. You will also turn this table into a bar chart for a visual snapshot.

Click cell A9 and type Stage.
Click cell B9 and type Count.
Click cell C9 and type Total Value.
In cells A10 through A15, type each stage name: Lead, Qualified, Proposal, Negotiation, Won, Lost (one per row).

Now add the COUNTIF formulas for column B. Each formula counts how many deals are in that stage.

Click cell B10 and enter this formula:

=COUNTIF(Deals!F2:F100,"Lead")

Click cell B11 and enter:

=COUNTIF(Deals!F2:F100,"Qualified")

Repeat this pattern for B12 through B15, changing the criterion to "Proposal", "Negotiation", "Won", and "Lost".

Next, add SUMIFS formulas for column C to show the total deal value per stage.

Click cell C10 and enter this formula:

=SUMIFS(Deals!E2:E100,Deals!F2:F100,"Lead")

Click cell C11 and enter:

=SUMIFS(Deals!E2:E100,Deals!F2:F100,"Qualified")

Repeat for C12 through C15, changing the criterion to match each stage name.

What is this summary table doing?

Each row of the table asks one question: "How many deals are in this stage, and what is their combined value?" The COUNTIF column answers the first part. The SUMIFS column answers the second.

Together they show where your pipeline is heavy or thin. If most deals are stuck at "Lead" with no "Proposal" or "Negotiation" deals, that tells you qualification is the bottleneck.

Now turn this table into a bar chart so you can see the distribution visually.

Select cells A9:B15 (the Stage and Count columns including headers).
Click Insert in the top menu bar.
Click Chart.
In the Chart editor panel on the right, click the Chart type dropdown and select Bar chart if it is not already selected.
Close the Chart editor panel.

You should see a bar chart appear on your Dashboard showing the count of deals in each stage. You can drag the chart to reposition it below your summary table.

Chart showing unexpected data?

Make sure you selected A9:B15 (including the header row) before inserting the chart. If you accidentally included column C, the chart will show two data series. Double-click the chart, click Setup, and adjust the data range to A9:B15.

help me fix my pipeline chart

Your Dashboard now updates automatically as you add or change deals. Next up, you will add conditional formatting to the Deals tab so overdue deals and pipeline stages are color-coded for instant scanning.

Add Conditional Formatting for Visual Scanning

Your Dashboard is now pulling live numbers from the Deals tab. Any time you add a deal or update a stage, your KPIs and chart refresh automatically.

Numbers alone can be hard to scan when your pipeline grows. In this step, you'll add conditional formatting to color-code deal stages and flag overdue opportunities so problems jump out visually.

In this step, get ready to:

Add color-coded conditional formatting to pipeline stages on the Deals tab.
Create an overdue deal alert using a custom formula.
Format your Dashboard KPIs for professional readability.

Add conditional formatting for deal stages

Right now every stage in your Deals tab looks the same. Conditional formatting lets Google Sheets automatically change a cell's background color based on its value.

Switch to the Deals tab in your CRM Tracker spreadsheet.
Select cells F2:F100 (the Stage column below the header).
Click Format in the top menu bar.
Click Conditional formatting.

A panel opens on the right side of the screen. You'll add three rules here, one for each stage you want to highlight.

Under Format cells if, select Text is exactly.
Type Won in the value field.
Under Formatting style, click the fill color icon and choose a green background.
Click Done.

Now add the second rule for lost deals.

Click Add another rule.
Under Format cells if, select Text is exactly.
Type Lost in the value field.
Choose a red background.
Click Done.

Add the third rule for deals in negotiation.

Click Add another rule.
Under Format cells if, select Text is exactly.
Type Negotiation in the value field.
Choose a yellow background.
Click Done.

Why does rule order matter?

Google Sheets evaluates conditional formatting rules from top to bottom. The first matching rule determines the cell's appearance.

If you reorder these later, make sure "Won" stays above "Lost" which stays above "Negotiation" in the rules panel.

Check your Stage column. Cells containing "Won" should have a green background, "Lost" should be red, and "Negotiation" should be yellow.

Colors not showing up?

Make sure you typed the stage name exactly as it appears in your data. Conditional formatting is not case-sensitive, but extra spaces or typos will prevent a match.
Confirm you selected F2:F100 and not a different column. The rules only apply to the range you selected.
Check that your sample deals actually contain "Won", "Lost", or "Negotiation" in the Stage column.

Help me troubleshoot why my conditional formatting rules aren't applying colors to my Stage column.

Create an overdue deal alert

Deals that pass their expected close date without being won or lost need attention. A custom formula can automatically highlight these overdue deals in red so you never miss a follow-up.

Select cells J2:J100 (the Expected Close Date column).
Click Format in the top menu bar.
Click Conditional formatting.
Under Format cells if, select Custom formula is.
In the Value or formula field, enter: =AND(J2<TODAY(),J2<>"").
Under Formatting style, click the fill color icon and choose a light red background.
Click Done.

What does this formula do?

AND() checks that both conditions inside it are true at the same time.
J2<TODAY() checks if the date in the cell is before today, meaning the deal is past its expected close date.
J2<>"" ensures the cell is not empty, so blank rows don't get highlighted.

Any sample deals with Expected Close Dates in the past should now have a light red background. Deals with future dates or empty date cells remain unformatted.

No red highlights appearing?

Make sure at least one of your sample deals has an Expected Close Date that has already passed. If all your dates are in the future, the rule is working but nothing triggers it yet.
Check that your formula starts with = and has no extra spaces. The full formula should be =AND(J2<TODAY(),J2<>"").
Confirm your Expected Close Date column actually contains date values, not plain text. Select a cell and check that Format > Number shows a date format.

Help me figure out why my overdue deal conditional formatting isn't highlighting any cells.

Format the Dashboard for readability

Your Dashboard formulas are working, but the raw numbers can be hard to parse at a glance. Formatting the values with bold text, proper number formats, and borders makes the Dashboard look professional and easy to scan.

Switch to the Dashboard tab.
Select cells B1:B5 (your KPI values).
Press Cmd+B (macOS) or Ctrl+B (Windows) to bold the values.
With B1:B5 still selected, change the font size to 14 using the font size dropdown in the toolbar.

Now apply the correct number formats so dollar values show as currency and the win rate shows as a percentage.

Select cells B1, B2, and B5 (hold Cmd on macOS or Ctrl on Windows to select multiple cells).
Click Format, then Number, then Currency.
Select cell B4 (your Win Rate value).
Click Format, then Number, then Percent.

Finally, add borders to make your KPI section and summary table visually distinct.

Select the KPI section by highlighting cells A1:B5.
Click the Borders icon in the toolbar (it looks like a grid).
Select All borders.
Select the Deals by Stage summary table by highlighting cells A9:C15.
Click the Borders icon again and select All borders.

Your Dashboard should now look polished. KPI values appear in bold at 14pt, dollar amounts show currency symbols, and the win rate displays as a percentage. Both sections have visible borders separating the data.

Formatting not applying to specific cells?

Double-check that you selected the correct cells. B1, B2, and B5 are the dollar values (Total Pipeline Value, Total Revenue, Average Deal Size). B4 is the Win Rate.
If the currency or percent format isn't showing, make sure the cells contain numbers from your formulas, not text. A cell showing 0 is fine. A cell showing an error like #DIV/0! means a formula issue from the previous step.

Help me fix number formatting issues on my Google Sheets Dashboard.

Your CRM Tracker is now a fully functional, visually polished sales tool. Every tab works together to keep your data clean, your pipeline organized, and your metrics up to date.

Secret mission

Add Weighted Forecast and Stale Deal Alerts

Your CRM dashboard shows raw pipeline totals, but how realistic are those numbers? Build a weighted forecast that adjusts deal values by their probability of closing, plus an alert system that flags deals going stale.

Clean Up Your Resources

This project runs entirely in Google Sheets, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your CRM spreadsheet as-is, or delete it if you no longer need it.

Resources you used:

"CRM Tracker" Google Sheet (stored in your Google Drive).

Keep everything running

No action needed. Choose this if you plan to keep using your CRM Tracker to manage real contacts and deals.

Your Google Sheet stays in Google Drive at no cost.
You can continue adding contacts and deals whenever you like.
All formulas and conditional formatting will keep working as you add new data.

Pause - I'll come back to this later

Google Sheets saves automatically, so there is nothing to pause. Your spreadsheet will be exactly as you left it when you return.

Your CRM Tracker remains in Google Drive indefinitely at no cost.
If you want to tidy up, you can move the file into a specific folder in Google Drive so it is easy to find later.

Delete - I don't want to use this again

Remove your CRM Tracker spreadsheet from Google Drive entirely.

Open Google Drive at drive.google.com.
Find the file named "CRM Tracker" in your recent files or by searching for it.
Right-click the file and select Move to trash.
To permanently delete it, click Trash in the left sidebar, then right-click the file and select Delete forever.

Nice Work!

You just built a professional CRM tracker from scratch in Google Sheets. What started as a blank spreadsheet is now a fully functional sales management system with automated reporting that updates the moment you enter new data.

You've learned how to:

Built a multi-tab CRM system with Deals, Contacts, Settings, and Dashboard tabs that work together through data validation dropdowns enforcing clean, consistent data entry.
Written automated KPI formulas using SUMIFS, COUNTIF, and IFERROR that calculate pipeline value, win rate, and average deal size in real time.
Applied conditional formatting rules to visually flag deal stages and overdue opportunities, plus built a pipeline chart that visualizes deal distribution at a glance.
Added a weighted forecast system and stale-deal alerts for smarter pipeline management.

Ready to quiz yourself?