Warehouse Inventory Audit Tool

Build a Google Sheets audit tool that flags inventory variances by severity.

Introduction

30 Second Summary

One wrong count in a warehouse can cascade into a failed shipment, a missed SLA, or a full audit investigation. The difference between reactive counting and proactive risk detection is having a system that flags problems before they reach a supervisor's desk.

In this project, you will build a professional warehouse inventory audit tool in Google Sheets that automatically calculates variances, color-codes discrepancies by severity, tracks QA compliance, and displays a live risk dashboard.

What You'll Build

You paste in your inventory data, and within seconds the tool highlights every count discrepancy in red, yellow, or green while a dashboard shows your overall accuracy rate and the five materials that need your attention right now.

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

  • A working audit tool where you paste 50+ SKUs and instantly see which materials have count discrepancies, color-coded by severity so you can spot critical issues in under two seconds.
  • A professional dashboard showing your inventory accuracy percentage, QA compliance rate, and the top 5 highest-risk materials that need immediate attention.
  • Built-in error prevention using data validation that blocks bad entries at the source, catching negative counts, missing sign-offs, and duplicate entries before they corrupt your audit.
  • Secret Mission: Trend detection that spots repeated small variances on the same material over 30 days, flagging potential shrinkage or systemic issues before they become critical.

Are there any prerequisites?

You need a free Google Account to use Google Sheets. Basic spreadsheet familiarity (entering data and navigating tabs) is helpful, but every formula and step is explained in full.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters for your role as an Inventory Specialist.

Set Up Your Audit Spreadsheet

Every good audit tool needs a clear structure before any data goes in. Without separate areas for raw data, analysis, and reporting, your spreadsheet quickly becomes an unmanageable mess where errors hide in plain sight.

In this step, you'll create the foundation of your audit tool. You'll set up five dedicated tabs and add column headers that mirror how warehouse data actually flows, from SAP exports through physical counts to final audit results.

In this step, get ready to:
  • Create a new Google Sheet with 5 named tabs.
  • Add formatted column headers to each tab.
  • Set up data validation to prevent bad data entry.
Create your spreadsheet and add tabs

Your audit tool needs separate workspaces for different types of data. Each tab serves a specific role in the audit workflow.

  • In Google Sheets (already open at sheets.google.com), click the Blank spreadsheet tile with the + icon to create a new sheet.
  • Click the title in the top left (it currently says "Untitled spreadsheet") and rename it to Warehouse Inventory Audit Tool.

You should see your new spreadsheet title appear at the top of the page.

  • Right-click the default tab at the bottom (named Sheet1) and select Rename.
  • Type SAP Data and press Enter.

Now create the remaining four tabs.

  • Click the + icon at the bottom left of the sheet to add a new tab.
  • Double-click the new tab name and rename it to Physical Count.
  • Repeat: click + and rename the tab to Audit Check.
  • Repeat: click + and rename the tab to QA Log.
  • Repeat: click + and rename the tab to Dashboard.

You should now see five tabs along the bottom of your sheet: SAP Data, Physical Count, Audit Check, QA Log, and Dashboard.

Don't see all five tabs?

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

If a tab has the wrong name, double-click the tab name to edit it directly. Make sure each name matches exactly (including spaces). Help me fix my tabs.

Add column headers to each tab

Headers define what data goes into each column. These mirror the fields you would see in a real warehouse management system.

  • Click the SAP Data tab at the bottom.
  • Click cell A1 and type the following headers across row 1, pressing Tab between each to move to the next cell: Material, Description, Location, SAP Qty, UoM, Movement Type, Date.
  • Select all 7 header cells (A1 through G1) and click the Bold button in the toolbar (or press Cmd+B on macOS / Ctrl+B on Windows).

What does UoM mean?

UoM stands for Unit of Measure. In SAP, materials are tracked in specific units like EA (each), KG (kilograms), or PAL (pallets). This column records which unit applies to each material's quantity.

  • Click the Physical Count tab.
  • In row 1, type these headers across cells A1 to E1 (pressing Tab between each): Material, Location, Counted Qty, Counted By, Date/Time.
  • Bold the header row (select A1:E1, then Cmd+B / Ctrl+B).

You should see 5 bold headers across the top of the Physical Count tab.

  • Click the Audit Check tab.
  • In row 1, type these headers across cells A1 to H1: Material, Description, Location, SAP Qty, Counted Qty, Variance, Variance %, Severity.
  • Bold the header row.
  • Click the QA Log tab.
  • In row 1, type these headers across cells A1 to F1: Check Area, Completed, Sign-off Name, Date, Notes, Flag.
  • Bold the header row.

Leave the Dashboard tab blank for now. You'll build it in a later step.

What does the Audit Check tab do?

This is where the tool compares SAP system quantities against your physical counts. The Variance and Severity columns will automatically calculate how far off each material is and flag it by risk level.

You'll write the formulas for these in the next step.

Headers not lining up?

If your headers ended up in the wrong cells, click on cell A1 first, then type each header and press Tab (not Enter) to move right. Enter moves down to the next row, while Tab moves right to the next column.

Help me fix my header layout.

Add data validation to prevent bad entries

Right now, anyone could type anything into any cell. Data validation adds rules that block invalid entries before they corrupt your audit calculations.

  • Click the Physical Count tab.
  • Select cells C2 through C100 (the Counted Qty column below the header).
  • Click Data in the top menu bar.
  • Click Data validation.
  • Click Add rule.
  • Under Criteria, select Is greater than or equal to.
  • Enter 0 in the value field.
  • Click Done.

Test it by clicking cell C2 and typing -5, then pressing Enter. The cell should reject the input or show a warning since negative counts are not valid.

Why block negative counts?

A physical count represents how many items you see on the shelf. That number can never be negative. Blocking negatives catches typos (like accidentally adding a minus sign) before they throw off your variance calculations.

  • Delete the -5 test value from cell C2 so it's empty again.

Now add a dropdown to the QA Log tab so people can only enter YES or NO for completion status.

  • Click the QA Log tab.
  • Select cells B2 through B100 (the Completed column below the header).
  • Click Data in the top menu, then Data validation.
  • Click Add rule.
  • Under Criteria, select Dropdown.
  • Type YES in the first value field.
  • Click Add another item and type NO.
  • Click Done.

Test it by clicking cell B2. You should see a small dropdown arrow appear in the cell. Click the arrow and you'll see YES and NO as the only options.

  • Clear cell B2 so the column is empty and ready for real data.

Finally, add text validation to the Location column on the Physical Count tab.

  • Switch back to the Physical Count tab.
  • Select cells B2 through B100 (the Location column below the header).
  • Click Data, then Data validation.
  • Click Add rule.
  • Under Criteria, select Text and choose Is valid text.
  • Click Done.

Why validate the Location column?

Location codes like BIN-A01 or BIN-C05 must be text, not numbers. This validation ensures someone does not accidentally enter a numeric warehouse zone code that Sheets would interpret as a calculation.

Data validation option not appearing?

Make sure you selected the cells first (B2:B100 or C2:C100), then opened Data from the menu bar. The data validation panel opens on the right side of the screen.

If you do not see the Add rule button, scroll down in the panel. Help me set up data validation.

Before you move on, do a final check. What do you expect to see if you click through each of your five tabs?

Click through each tab at the bottom. Every tab except Dashboard should have bold headers in row 1. The Physical Count tab's Counted Qty column should reject negative numbers, and the QA Log tab's Completed column should show a dropdown with YES and NO.

Your audit tool's foundation is ready. Next up, you'll load sample inventory data and write the variance formulas that make this tool actually calculate discrepancies.

Load Sample Data and Calculate Variances

Your audit tool has structure, but structure without data is just an empty shell.

In this step, you will load realistic warehouse inventory data and write the formulas that automatically detect discrepancies between what SAP says you have and what a physical count reveals. These variance calculations are the core engine of any inventory audit.

In this step, get ready to:
  • Enter 15 sample materials with realistic warehouse data into the SAP Data and Physical Count tabs.
  • Write SUMIFS lookup formulas that pull quantities from both tabs into the Audit Check tab.
  • Build a severity classification formula that labels each variance as PASS, MINOR, WARNING, or CRITICAL.
Enter sample materials into the SAP Data tab

Your audit tool needs data that mirrors what you would actually see in a warehouse. This sample set includes a realistic mix of materials, bin locations, and quantities.

  • Switch to the SAP Data tab in your spreadsheet.
  • Starting in row 2 (below your headers), enter the following 15 materials:
MAT-1001 | Steel Bolts M8x25    | BIN-A01 | 500  | EA  | 101 | 2026-07-15
MAT-1002 | Copper Wire 2.5mm    | BIN-A02 | 200  | M   | 101 | 2026-07-15
MAT-1003 | Safety Gloves L      | BIN-A03 | 1000 | EA  | 101 | 2026-07-14
MAT-1004 | Hydraulic Hose 10m   | BIN-B01 | 75   | EA  | 311 | 2026-07-14
MAT-1005 | Bearing SKF 6205     | BIN-B02 | 300  | EA  | 101 | 2026-07-13
MAT-1006 | Lubricant Grease 5kg | BIN-B03 | 150  | KG  | 101 | 2026-07-13
MAT-1007 | PVC Pipe 50mm        | BIN-B04 | 400  | M   | 311 | 2026-07-12
MAT-1008 | Welding Rod 3.2mm    | BIN-C01 | 2000 | EA  | 101 | 2026-07-12
MAT-1009 | Cable Tie 200mm      | BIN-C02 | 1500 | EA  | 101 | 2026-07-11
MAT-1010 | Filter Cartridge     | BIN-C03 | 60   | EA  | 101 | 2026-07-11
MAT-1011 | Gasket Ring DN50     | BIN-A04 | 800  | EA  | 311 | 2026-07-10
MAT-1012 | Stainless Sheet 2mm  | BIN-A05 | 50   | PC  | 101 | 2026-07-10
MAT-1013 | Pneumatic Valve      | BIN-B05 | 120  | EA  | 101 | 2026-07-09
MAT-1014 | Insulation Tape      | BIN-C04 | 600  | ROL | 311 | 2026-07-09
MAT-1015 | Sealant Silicone     | BIN-C05 | 250  | EA  | 101 | 2026-07-08

What does this data represent?

Each row represents one material in one bin location, matching what you would export from an SAP transaction like MB52 (stock on hand).

The columns are separated by pipes (|) for readability here. Enter each value into its own cell, one column at a time. Movement Type 101 means goods receipt and 311 means transfer posting.

  • Confirm you have 15 rows of data (rows 2 through 16) by checking that the last entry is MAT-1015 in cell A16.

Don't see 15 rows?

Count your rows in column A. If you have blank rows between entries, select them and delete (right-click > Delete row). Each material should sit in consecutive rows with no gaps.

If you accidentally started in row 1, select all data, cut it (Cmd+X or Ctrl+X), click cell A2, and paste.

Enter physical counts with deliberate variances

In a real audit, physical counts often differ from what the system says. You will enter counts that deliberately include a mix of exact matches, small discrepancies, and serious errors.

  • Switch to the Physical Count tab.
  • Starting in row 2, enter the following 15 physical count records:
MAT-1001 | BIN-A01 | 500  | J. Torres | 2026-07-16 09:15
MAT-1002 | BIN-A02 | 198  | J. Torres | 2026-07-16 09:22
MAT-1003 | BIN-A03 | 990  | J. Torres | 2026-07-16 09:30
MAT-1004 | BIN-B01 | 72   | M. Chen   | 2026-07-16 10:05
MAT-1005 | BIN-B02 | 300  | M. Chen   | 2026-07-16 10:15
MAT-1006 | BIN-B03 | 148  | M. Chen   | 2026-07-16 10:25
MAT-1007 | BIN-B04 | 385  | M. Chen   | 2026-07-16 10:40
MAT-1008 | BIN-C01 | 2010 | R. Patel  | 2026-07-16 11:00
MAT-1009 | BIN-C02 | 1500 | R. Patel  | 2026-07-16 11:15
MAT-1010 | BIN-C03 | 54   | R. Patel  | 2026-07-16 11:25
MAT-1011 | BIN-A04 | 808  | J. Torres | 2026-07-16 13:00
MAT-1012 | BIN-A05 | 48   | J. Torres | 2026-07-16 13:10
MAT-1013 | BIN-B05 | 105  | M. Chen   | 2026-07-16 13:30
MAT-1014 | BIN-C04 | 580  | R. Patel  | 2026-07-16 14:00
MAT-1015 | BIN-C05 | 220  | R. Patel  | 2026-07-16 14:15

Why are some counts different from SAP?

This data deliberately includes 3 exact matches (MAT-1001, MAT-1005, MAT-1009), 5 minor variances under 2%, 4 moderate variances between 2-5%, and 3 critical variances over 5%.

This distribution is realistic. In most warehouses, the majority of items count correctly, a handful are slightly off, and a few need immediate investigation.

  • Confirm your last entry is MAT-1015 in cell A16 with a counted quantity of 220.

Data validation rejecting your entry?

If you see a rejection message when entering Counted Qty, make sure you are entering a plain number with no spaces or text characters. The validation you set up in Step 1 requires a number greater than or equal to 0.

Help me troubleshoot data validation errors in my Physical Count tab.

Write variance formulas in the Audit Check tab

The Audit Check tab is where your raw data becomes actionable insight. SUMIFS formulas will pull quantities from both tabs, and simple arithmetic will reveal every discrepancy.

  • Switch to the Audit Check tab.
  • In columns A, B, and C (rows 2 through 16), enter the same Material, Description, and Location values from your SAP Data tab. These act as lookup keys for the formulas.
  • In cell D2, enter this SUMIFS formula to pull the SAP quantity:
=SUMIFS('SAP Data'!D:D, 'SAP Data'!A:A, A2, 'SAP Data'!C:C, C2)
  • In cell E2, enter this SUMIFS formula to pull the physical count:
=SUMIFS('Physical Count'!C:C, 'Physical Count'!A:A, A2, 'Physical Count'!B:B, C2)

What do these SUMIFS formulas do?

SUMIFS looks up a value based on multiple criteria. The formula in D2 says: from the SAP Data tab's column D (quantities), find the row where column A matches this row's Material AND column C matches this row's Location. Then return that quantity.

Using both Material and Location as criteria ensures you get the right quantity even if the same material exists in multiple bins.

  • Check that cell D2 shows 500 (the SAP quantity for MAT-1001) and cell E2 shows 500 (the physical count).

Seeing 0 instead of a quantity?

Double-check that the Material value in cell A2 of the Audit Check tab exactly matches cell A2 of the SAP Data tab. Even a trailing space will cause the lookup to fail. Also confirm the Location in column C matches.

Help me debug my SUMIFS formula returning 0.

Now add the formulas that calculate the actual variance.

  • In cell F2, enter the variance formula:
=E2-D2
  • In cell G2, enter the variance percentage formula:
=IFERROR(F2/D2, 0)

Why use IFERROR here?

If the SAP quantity in D2 were ever 0, dividing by it would produce a #DIV/0! error. Wrapping in IFERROR catches that case and returns 0 instead, keeping your spreadsheet clean.

For MAT-1001, the variance is 500 - 500 = 0, and the percentage is 0/500 = 0. This means a perfect match.

  • Confirm cell F2 shows 0 and cell G2 shows 0 (no variance for MAT-1001 since both counts match).
  • Select cells D2 through G2. Drag the fill handle (the small blue square at the bottom-right of the selection) down to row 16 to copy all four formulas for all 15 materials.

You should now see a mix of 0s, negative numbers (under-counts), and positive numbers (over-counts) in column F.

All rows showing 0?

If every row in columns D and E shows 0, your lookup keys in columns A and C likely do not match the source tabs. Select a cell in column A of the Audit Check tab, press F2 to enter edit mode, and check for invisible spaces at the end of the text.

If only some rows show 0, compare those specific Material and Location values character by character against the SAP Data and Physical Count tabs.

Classify variances by severity

Numbers alone do not tell you which items need attention right now. A nested IF formula will automatically label each variance so you can prioritize your response.

  • In cell H2, enter this severity classification formula:
=IF(ABS(G2)=0, "PASS", IF(ABS(G2)<=0.02, "MINOR", IF(ABS(G2)<=0.05, "WARNING", "CRITICAL")))

What does this formula do?

  • ABS(G2) takes the absolute value of the variance percentage, so both over-counts and under-counts are evaluated the same way.
  • If the variance is exactly 0, the material gets a PASS label.
  • If the absolute variance is 2% or less, it is classified as MINOR.
  • If it is between 2% and 5%, it gets a WARNING.
  • Anything above 5% is flagged CRITICAL and needs immediate investigation.
  • Select cell H2 and drag the fill handle down to row 16 to classify all 15 materials.

Before you review the results, take a moment to predict: based on the physical counts you entered, how many materials do you expect to see labeled CRITICAL?

You should see 3 items labeled PASS (MAT-1001, MAT-1005, MAT-1009), 5 labeled MINOR, 4 labeled WARNING, and 3 labeled CRITICAL (MAT-1010, MAT-1013, MAT-1015).

  • Format column G as a percentage: select column G's data cells, click Format > Number > Percent.

Column G should now display values like -1.0% and -10.0% instead of decimal numbers.

Seeing CRITICAL for everything?

If all rows show CRITICAL, column G likely contains whole numbers instead of decimals. Check that cell G2 shows a decimal like 0 or -0.01, not a whole number like -1 or -10. The formula divides variance by SAP quantity, so the result should be a small decimal.

If G2 shows the correct decimal but H2 still says CRITICAL, check that you typed 0.02 and 0.05 in the formula (not 2 and 5).

Help me fix my severity formula.

✔️ Awesome, I've got everything!

Great. Double check that your Audit Check tab has formulas in all 15 rows (D2:H16) and that you can see a mix of PASS, MINOR, WARNING, and CRITICAL labels in column H.

ⓧ I'd like to double check the full formulas

Here are all five formulas that should be in row 2 of your Audit Check tab. Each one is dragged down through row 16.

Cell D2: =SUMIFS('SAP Data'!D:D, 'SAP Data'!A:A, A2, 'SAP Data'!C:C, C2)
Cell E2: =SUMIFS('Physical Count'!C:C, 'Physical Count'!A:A, A2, 'Physical Count'!B:B, C2)
Cell F2: =E2-D2
Cell G2: =IFERROR(F2/D2, 0)
Cell H2: =IF(ABS(G2)=0, "PASS", IF(ABS(G2)<=0.02, "MINOR", IF(ABS(G2)<=0.05, "WARNING", "CRITICAL")))

Your audit engine is live. You can now see exactly which materials have count discrepancies and how severe each one is. Next up, you will add color-coding that makes critical issues jump off the screen without reading a single number.

Add Visual Flagging with Conditional Formatting

Your Audit Check tab now calculates variances and labels every row as PASS, MINOR, WARNING, or CRITICAL.

The problem is that raw numbers in a spreadsheet are hard to scan quickly. Looking at 50+ rows of percentages trying to spot problems is slow and error-prone. In this step, you will add color-coding that makes critical issues jump off the screen instantly.

In this step, get ready to:
  • Apply conditional formatting to the Audit Check tab so rows are color-coded by severity.
  • Enter QA check data and add visual flagging to the QA Log tab.
  • Format the Variance % column as a percentage and freeze header rows for easy scrolling.
Color-code severity levels in the Audit Check tab

Conditional formatting lets you set rules that automatically change a cell's background color based on its value. You will create four rules that format entire rows based on the Severity column.

  • Switch to the Audit Check tab in your spreadsheet.
  • Select the range A2:H16 (all your data rows).
  • Click Format in the top menu bar.
  • Click Conditional formatting. A panel opens on the right side of the screen.

You will now create the first rule. This rule highlights rows red when the Severity column says CRITICAL.

  • In the Apply to range field, change the range to A2:H100. This extends the rule to cover future data rows.
  • Under the Format cells if dropdown, select Custom formula is.
  • In the formula field, enter this formula:
=$H2="CRITICAL"
  • Click the paint bucket icon under Formatting style and choose a red background color.
  • Click Done.

Any rows where column H contains CRITICAL should now turn red. You should see your 3 critical-variance rows highlighted immediately.

Why the dollar sign in front of H?

The $ before H locks the formula to column H (Severity) no matter which column the formatting checks. The row number stays relative so the rule shifts down for each row.

Now add the remaining three rules. For each one, click Add another rule at the bottom of the conditional formatting panel.

  • Add rule 2 with range A2:H100, formula below, and a yellow background:
=$H2="WARNING"
  • Add rule 3 with range A2:H100, formula below, and a light yellow background:
=$H2="MINOR"
  • Add rule 4 with range A2:H100, formula below, and a green background:
=$H2="PASS"
  • Click Done after each rule to save it.

Look at your Audit Check tab. Every row should now be color-coded: red for CRITICAL, yellow for WARNING, light yellow for MINOR, and green for PASS.

What if my colors look wrong?

Rules are evaluated in the order they are listed. The first rule that matches wins. If you see unexpected colors, open the conditional formatting panel and drag the rules to reorder them. CRITICAL (red) should be at the top, followed by WARNING, MINOR, then PASS.

Not seeing any color changes?

Make sure the Apply to range field says A2:H100 for each rule. If it still shows A2:H16, your data rows might not be covered.

Check that your formula starts with = and uses =$H2 (with the dollar sign). Without the equals sign, the rule will not evaluate.

Help me troubleshoot my conditional formatting rules.

Enter QA data and add formatting to the QA Log tab

The QA Log tracks whether warehouse quality checks have been completed. You will enter 8 common check items, then add formatting so incomplete checks stand out visually.

  • Switch to the QA Log tab.
  • In column A (Check Area), enter these 8 items in rows 2 through 9:
  • Staging verification
  • Equipment pre-shift check
  • WMS document update
  • Count verification
  • Receiving inspection
  • Put-away confirmation
  • Label accuracy check
  • Zone assignment check
  • In column B (Completed), enter YES for 5 of the items and NO for the remaining 3. This gives you a mix to test the formatting.
  • For the rows where Completed is YES, enter a name in column C (Sign-off Name). Leave column C blank for at least one YES row to test the incomplete-check detection later.

Now add the Flag formula in column F. This formula automatically labels each row based on completion status and whether a sign-off name is present.

  • Click cell F2 and enter this formula:
=IF(B2="NO","MISSING",IF(AND(B2="YES",C2=""),"INCOMPLETE","PASS"))
  • Press Enter, then copy cell F2 and paste it down through F9 to cover all 8 rows.

What does this formula do?

The formula checks two conditions in order. If Completed is NO, the check is flagged as MISSING. If Completed is YES but the Sign-off Name cell is empty, it is flagged as INCOMPLETE. Otherwise it returns PASS.

You should see MISSING, INCOMPLETE, and PASS values in column F depending on the data you entered.

Now add conditional formatting to the QA Log tab. You will create two color rules for the Completed column.

  • Select the range A2:F9.
  • Click Format, then Conditional formatting.
  • Set the Format cells if dropdown to Custom formula is and enter this formula for a red background:
=$B2="NO"
  • Choose a red background color and click Done.
  • Click Add another rule and enter this formula for a green background:
=$B2="YES"
  • Choose a green background color and click Done.

Your QA Log now shows red rows for incomplete checks and green rows for completed ones. The combination of color-coding and the Flag column gives you two layers of visibility into QA compliance.

Flag column showing errors?

If you see #VALUE! or unexpected results, check that your Completed column uses exactly YES and NO (all uppercase, no extra spaces). The formula matches these strings exactly.

Help me fix my QA Log Flag formula.

Format percentages and freeze header rows

Two finishing touches make the spreadsheet easier to read. Formatting the Variance % column as an actual percentage removes confusion, and freezing the header row keeps column labels visible while you scroll.

  • Switch back to the Audit Check tab.
  • Select the entire column G (Variance %) by clicking the column letter G at the top.
  • Click Format in the menu bar, then select Number, then Percent.
  • With column G still selected, click the Decrease decimal places button in the toolbar until you see 1 decimal place (e.g. 5.0%).

Your variance percentages now display as clean, readable values like 2.5% or -4.0% instead of raw decimals.

  • Now freeze the header row. Click View in the menu bar.
  • Select Freeze, then choose 1 row.
  • Switch to the QA Log tab and repeat: click View, then Freeze, then 1 row.

Before you check your work, take a moment to predict. If you scroll down on the Audit Check tab, what do you expect to stay visible at the top?

  • Scroll down on the Audit Check tab to test the freeze.

The header row (Material, Description, Location, etc.) stays pinned at the top while your data scrolls beneath it. You can now identify any row's column without scrolling back up.

Why freeze headers?

In a real warehouse audit with hundreds of materials, you would be scrolling constantly. Frozen headers mean you always know what each column represents. This is especially important when sharing the spreadsheet with a supervisor who needs to review results quickly.

Your audit tool now screams at you when something is wrong. Red rows demand attention, green rows confirm accuracy. Next up, you will build a dashboard that rolls all these individual flags into a big-picture view of your inventory health.

Build the Risk Dashboard

Your Audit Check tab now flags every variance by severity, and the QA Log catches incomplete checks. But scanning through individual rows to understand the overall health of your inventory is slow.

A supervisor or auditor wants the big picture in a single glance. In this step, you will build a dashboard that answers three questions instantly: how accurate is our inventory, what are the top risks right now, and are our QA processes being followed?

In this step, get ready to:
  • Create a KPI summary showing inventory accuracy rate and severity counts.
  • Build a Top Risk Items section listing the 5 materials with the highest variance.
  • Add QA compliance metrics and format the dashboard professionally.
Create the KPI summary section

The top of your dashboard will show the most important number for any audit: your overall inventory accuracy rate. Below that, a severity breakdown lets anyone see how many items fall into each category.

  • Switch to the Dashboard tab in your spreadsheet.
  • Click on cell B2 and type:
INVENTORY ACCURACY RATE
  • Click on cell C2 and enter this formula:
=1-(COUNTIF('Audit Check'!H:H,"CRITICAL")+COUNTIF('Audit Check'!H:H,"WARNING"))/COUNTA('Audit Check'!H2:H100)
  • Press Enter to confirm the formula.

You should see a decimal number appear in C2. This is your accuracy rate, but it needs to be displayed as a percentage.

  • With C2 selected, click Format in the top menu.
  • Select Number, then Percent.

The cell now shows your inventory accuracy as a percentage. If your sample data has 3 CRITICAL and 4 WARNING items out of 15 total, you will see around 53%.

What does this formula do?

The formula counts how many items are CRITICAL or WARNING (the problem items), divides by the total number of audited items, then subtracts from 1.

This gives you the percentage of items that are NOT problematic. It is the same logic used in professional inventory accuracy KPIs.

Now add the severity breakdown below it. Enter these labels and formulas into the cells listed:

  • Click cell B4 and type Total Items Audited.
  • Click cell C4 and enter:
=COUNTA('Audit Check'!A2:A100)
  • Click cell B5 and type PASS.
  • Click cell C5 and enter:
=COUNTIF('Audit Check'!H:H,"PASS")
  • Click cell B6 and type MINOR.
  • Click cell C6 and enter:
=COUNTIF('Audit Check'!H:H,"MINOR")
  • Click cell B7 and type WARNING.
  • Click cell C7 and enter:
=COUNTIF('Audit Check'!H:H,"WARNING")
  • Click cell B8 and type CRITICAL.
  • Click cell C8 and enter:
=COUNTIF('Audit Check'!H:H,"CRITICAL")

Check that column C now shows numbers that add up to your total items audited. For 15 sample materials, C4 should show 15, and C5 through C8 should add up to 15.

Seeing 0 in your cells?

Make sure you typed the sheet name exactly as 'Audit Check' with the single quotes and space. Google Sheets requires quotes around sheet names that contain spaces.

Also check that your Audit Check tab has data in column H (the Severity column). If column H is empty, the formulas from Step 2 may not have been entered correctly.

Help me troubleshoot my dashboard formulas showing zero.

Create the Top Risk Items section

A severity breakdown tells you how many problems exist. But the next question is always: which specific materials need attention right now? A Top Risk Items list answers that instantly.

  • Click cell B10 and type TOP RISK ITEMS as a section header.
  • In row 11, set up column headers across B11 to E11: Material, Location, Variance %, Severity.

Now you need to identify the 5 materials with the highest absolute variance percentage from your Audit Check tab.

  • Switch to the Audit Check tab.
  • Look at column G (Variance %) and identify the 5 rows with the largest absolute percentage values. Note their Material (column A), Location (column C), Variance % (column G), and Severity (column H).
  • Switch back to the Dashboard tab.
  • Starting in row 12, manually enter those 5 materials into cells B12:E16. Each row gets one material with its Location, Variance %, and Severity.

Why enter these manually?

Google Sheets does not have a simple built-in way to extract the top N rows by value from another sheet without complex array formulas. For a 15-item audit, manually listing the top 5 is practical and keeps the dashboard easy to understand and maintain.

In a production environment with thousands of SKUs, you would sort the source data or use a script to automate this.

Now make the CRITICAL items stand out visually with conditional formatting.

  • Select the range B12:E16 (your 5 risk items).
  • Click Format in the top menu.
  • Select Conditional formatting.
  • Under Format rules, choose Custom formula is.
  • Enter this formula:
=$E12="CRITICAL"
  • Set the formatting style to a red background.
  • Click Done.

Any row in your Top Risk Items list that has CRITICAL severity is now highlighted in red. You should see at least 2-3 red rows depending on your sample data.

Conditional formatting not applying?

Check that your formula starts with =$E12 (with the dollar sign on E but not on the row number). The dollar sign locks the column reference so the rule checks column E for every row in the range.

Also confirm the range is set to B12:E16 in the conditional formatting panel.

Help me fix my conditional formatting rule.

Add QA compliance metrics and format the dashboard

The final section tracks whether your warehouse QA processes are being completed. A low compliance rate is a leading indicator of future inventory problems.

  • Click cell B18 and type QA COMPLIANCE RATE.
  • Click cell C18 and enter this formula:
=COUNTIF('QA Log'!B:B,"YES")/COUNTA('QA Log'!B2:B100)
  • Format C18 as a percentage: click Format > Number > Percent.

You should see a percentage. If 5 out of 8 QA checks are marked YES, the cell shows 63%.

  • Click cell B20 and type Missing Checks.
  • Click cell C20 and enter:
=COUNTIF('QA Log'!F:F,"MISSING")
  • Click cell B21 and type Incomplete Checks.
  • Click cell C21 and enter:
=COUNTIF('QA Log'!F:F,"INCOMPLETE")

Check that C20 and C21 show numbers that match what you see in your QA Log's Flag column.

What does QA compliance tell you?

Missing checks mean a required QA step was never done. Incomplete checks mean someone marked it done but did not sign off. Both are audit findings that indicate process gaps.

Tracking this alongside inventory accuracy helps you spot whether count errors correlate with skipped QA steps.

Now give the dashboard a professional look so it reads clearly at a glance.

  • Select cells B2, B10, and B18 (your three section headers). Hold Cmd (macOS) or Ctrl (Windows) to select multiple cells.
  • Make them bold by pressing Cmd+B (macOS) or Ctrl+B (Windows).
  • Set the text color to a dark navy by clicking the Text color button (A with colored underline) in the toolbar and choosing a dark navy shade.
  • Also bold the column header row B11:E11 in the Top Risk Items section.
  • Select the entire data area B2:E21 and add borders by clicking the Borders button in the toolbar and selecting All borders.

Your Dashboard tab now looks like a professional report with clear sections, bold headers in navy, and borders separating each area.

Before you move on, predict what will happen: if you go back to your QA Log tab and change one of the YES entries to NO, what should happen on the Dashboard?

  • Switch to the QA Log tab and change one YES in the Completed column to NO.
  • Switch back to the Dashboard tab.

The QA Compliance Rate drops, and the Missing Checks count increases by 1. Your dashboard updates automatically because every formula references the source data directly.

  • Change the QA Log entry back to YES to restore your original data.

Dashboard not updating when you change data?

Check that your formulas reference the correct tab name. The formula in C18 should contain 'QA Log'!B:B with single quotes around the tab name.

If the compliance rate shows #DIV/0! instead of a percentage, the COUNTA portion found no data. Confirm your QA Log has entries starting in row 2.

Help me fix my dashboard formulas that are not updating.

Your dashboard now gives anyone a complete picture of inventory health in under 5 seconds. Next up, you will add data validation rules that prevent bad data from entering the system in the first place.

Add Error Prevention with Data Validation

Your audit tool can now catch errors after they happen. The dashboard highlights variances, and conditional formatting makes problems visible instantly.

Catching errors after the fact is good, but preventing them from entering the system is better. Data validation stops bad data at the source, before it ever corrupts your audit calculations. This is a key skill employers look for in an Inventory Specialist.

In this step, get ready to:
  • Add validation rules that reject invalid entries (negative counts, missing names, bad dates).
  • Detect duplicate material+location combinations automatically.
  • Enforce sign-off requirements on QA checks.
Strengthen data validation on the Physical Count tab

Right now, basic validation exists on the Physical Count tab from earlier setup. You will strengthen it with custom error messages and strict rejection so invalid data cannot slip through.

  • Switch to the Physical Count tab in your spreadsheet.
  • Select the entire Counted Qty column (column C), from C2 down through your data.
  • Click Data in the top menu bar.
  • Click Data validation.
  • Click Add rule (or edit the existing rule if one is already applied).
  • Under Criteria, select Number and then Greater than or equal to.
  • Enter 0 as the value.
  • Click Advanced options.
  • Under If the data is invalid, select Reject input.
  • Check the Show validation help text box and enter this exact message:
Count cannot be negative. Please re-count and enter the correct quantity.
  • Click Done.

Now test it. Click on any cell in the Counted Qty column and try typing -5, then press Enter.

You should see your custom error message appear and the entry is rejected. The cell stays empty.

Why reject input instead of show a warning?

Google Sheets offers two modes for invalid data. "Show a warning" lets the data through with a small triangle indicator. "Reject input" completely blocks the entry.

For audit data, you want full rejection. A negative count would corrupt your variance calculations silently if it slipped through with just a warning.

  • Now select the Counted By column (column D), from D2 down through your data.
  • Click Data, then Data validation, then Add rule.
  • Under Criteria, select Text and then Is valid text.
  • Click Advanced options, select Reject input, and enter the help text:
Enter the counter's name.
  • Click Done.
  • Finally, select the Date/Time column (column E), from E2 down through your data.
  • Click Data, then Data validation, then Add rule.
  • Under Criteria, select Date and then Is valid date.
  • Click Advanced options, select Reject input, and click Done.

Validation rule not blocking entries?

Make sure you selected Reject input under Advanced options, not Show a warning. The warning mode lets invalid data through with just a small indicator.

Also confirm you applied the rule to the correct column range (starting at row 2, not row 1). Row 1 is your header row.

help me fix my data validation rules on the Physical Count tab

Add duplicate detection for material and location combinations

Counting the same material at the same location twice is a common data entry mistake during audits. A formula can flag these duplicates automatically so you catch them before they skew your variance numbers.

  • Stay on the Physical Count tab.
  • Click on cell F1 and type the header Duplicate Check.
  • Click on cell F2 and enter the following formula:
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>1,"DUPLICATE","")

What does this formula do?

COUNTIFS counts how many times the current row's material (column A) AND location (column B) combination has appeared in the rows above it.

The A$2:A2 range expands as you drag the formula down, so each row only checks against rows above it. If the same combination appears more than once, it shows "DUPLICATE".

  • Copy cell F2 and paste it down through F16 (or however many rows of data you have).

If all your sample data has unique material+location pairs, every cell in column F will be blank. To test the formula, temporarily duplicate one of your entries.

  • In an empty row (such as row 17), type the same Material and Location values as row 2.
  • Check column F for that row. You should see DUPLICATE appear.

Now add conditional formatting to make duplicates visually obvious.

  • Select the entire data range on the Physical Count tab (A2 through F17 or your last row).
  • Click Format in the top menu, then Conditional formatting.
  • Under Format rules, select Custom formula is.
  • Enter this formula:
=$F2="DUPLICATE"
  • Set the formatting style to an orange background color.
  • Click Done.

Your test duplicate row should now be highlighted in orange. The "DUPLICATE" text plus the color makes it impossible to miss.

  • Delete the test duplicate row (row 17) to clean up your sample data.

Formula showing DUPLICATE on the first entry?

Check the dollar signs in your formula. The range must be A$2:A2 (with the dollar sign on the 2 in A$2), not A2:A2. The dollar sign anchors the start of the range so it only expands downward.

help me fix my duplicate detection formula on the Physical Count tab

Enforce sign-off requirements on the QA Log tab

A QA check marked as completed but missing the person's name is an incomplete record. In a real warehouse audit, unsigned checks can fail compliance reviews. You will add validation that requires a sign-off name whenever a check is marked as done.

  • Switch to the QA Log tab.
  • Select the Sign-off Name column (column C), from C2 down through your data.
  • Click Data, then Data validation, then Add rule.
  • Under Criteria, select Custom formula is.
  • Enter this formula:
=OR(B2="NO",C2<>"")

What does this formula do?

This validation passes (allows the entry) when either condition is true: the Completed column says "NO" (no sign-off needed), OR the Sign-off Name cell is not empty.

It only fails when Completed is "YES" and the Sign-off Name is blank. That combination triggers the rejection.

  • Click Advanced options, select Reject input, and click Done.

Now add conditional formatting to highlight any existing rows that are already incomplete.

  • Select the data range on the QA Log tab (A2 through F9 or your last row).
  • Click Format, then Conditional formatting.
  • Select Custom formula is and enter:
=AND($B2="YES",$C2="")
  • Set the formatting style to an orange background color.
  • Click Done.

Any rows where Completed is "YES" but Sign-off Name is blank should now be highlighted in orange. The Flag column formula from earlier already shows "INCOMPLETE" for these rows.

Why use both validation and conditional formatting?

Validation prevents new bad entries going forward. Conditional formatting catches existing bad data that was entered before the rules were in place. Together they cover both past and future errors.

Conditional formatting not highlighting incomplete rows?

Make sure you used dollar signs on the column letters: $B2 and $C2. Without dollar signs, the formula shifts column references as it moves across the row, checking the wrong cells.

Also check that the Completed column value is exactly YES in uppercase. Google Sheets text comparisons are case-sensitive in formulas.

help me fix my QA Log conditional formatting and validation

Before you move on, predict: what do you think will happen if you try to enter -10 in the Counted Qty column on the Physical Count tab?

  • Switch back to the Physical Count tab.
  • Click any empty cell in the Counted Qty column and type -10, then press Enter.

The entry is rejected and you see your custom message. Now try entering a duplicate material+location combination in a new row. The helper column flags it as DUPLICATE and the row turns orange.

Your audit tool now stops bad data at the source. Negative counts, blank fields, duplicates, and unsigned checks are all caught before they can corrupt your audit calculations. This is the final layer of defense that makes the tool production-ready.

Secret mission

Trend Detection for Predictive Risk

Your audit tool catches today's errors. But can it predict tomorrow's failures? Add trend detection that spots materials with repeated variances over 30 days, flagging potential shrinkage or systemic issues before they become critical.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Google Sheets, so there are no ongoing costs. Decide whether to keep your spreadsheet in Google Drive, make a working copy for your job, or delete it entirely.

Resources you used:

  • 1 Google Sheet ("Warehouse Inventory Audit Tool") stored in your Google Drive, free indefinitely.

Keep everything running

No action needed. Choose this if you want to share the tool on your resume or LinkedIn as a portfolio piece.

  • Your Google Sheet stays in your Drive at no cost.
  • To share it, open the spreadsheet, click Share, and set access to Anyone with the link as a Viewer.

Pause - I'll come back to this later

Make a copy to use as a live working template at your job, keeping the original as your clean portfolio version.

  • Open your Warehouse Inventory Audit Tool spreadsheet.
  • Click File in the top menu bar.
  • Select Make a copy.
  • Rename the copy to something like Warehouse Audit - Working Copy and click Make a copy.
  • Use the copy for real inventory data at work. Keep the original untouched as your portfolio version.

Delete - I don't want to use this again

Remove the spreadsheet from your Google Drive entirely.

  • Go to drive.google.com.
  • Find the file named Warehouse Inventory Audit Tool.
  • Right-click the file and select Move to trash.
  • To permanently delete it, click Trash in the left sidebar, then click Empty trash.

Nice Work!

Nice Work!

You just built a professional warehouse audit tool that goes far beyond counting stock. Your spreadsheet automatically catches errors, flags risk, and presents the big picture on a live dashboard.

You've learned how to:

  • Build a multi-tab audit system that calculates inventory variances and classifies them by severity using nested IF formulas and conditional formatting for instant visual flagging.
  • Design a live risk dashboard with KPIs like inventory accuracy rate and QA compliance rate, powered by COUNTIF and COUNTA formulas that update automatically.
  • Implement data validation rules that prevent bad data at the source, including duplicate detection, numeric constraints, and required sign-off enforcement.
  • Secret Mission: Built trend detection that identifies materials with repeated variances over 30 days, flagging potential shrinkage or systemic issues before they escalate.

Ready to quiz yourself?