Data Analysis in Excel for Retail Inventory: Step-by-Step

Most retail analysts will tell you the hardest part of inventory analysis is knowing which formulas to use. That's not actually the problem. The hardest part is getting Excel to see your POS data as data — not as a formatted mess of merged headers, blank rows, and inconsistent SKU codes that no formula on earth wants to touch. I've watched analysts spend three hours filtering raw exports by hand when a ten-minute Power Query setup would've solved it permanently. Once you understand that, data analysis in Excel for retail inventory stops being a formula problem and starts being a structure problem.

This guide fixes the structure first, then builds the analysis on top of it. By the end, you'll have a workflow that cleans your POS export automatically, surfaces turnover rates and reorder gaps in a pivot table, ranks your SKUs by revenue impact using ABC-XYZ logic, and flags shrinkage before it shows up in your quarterly count. That last piece is something most Excel inventory tutorials skip entirely, and it's a gap worth closing.

What You'll Be Able to Analyze (and What to Have Ready Before You Start)

After working through every phase below, you'll have a live retail inventory management spreadsheet that refreshes when new POS data drops in, ranks SKUs by revenue tier, and visually surfaces your three most urgent problems the moment a buyer opens the file. No plugins. No Power BI license required. Just Microsoft Excel doing more than most people think it can.

Before you touch a formula, you need two things: a raw POS export (CSV or XLSX) that includes at minimum SKU, product name, category, units sold, units on hand, and date — and Excel 2019 or Microsoft 365. Power Query is non-negotiable here. If you're on an older version without it, the cleaning phase won't work.

The One Spreadsheet Habit That Makes Every Analysis Step Easier

Format your cleaned data as an Excel Table (Ctrl+T) before you build anything else. Tables expand automatically when new rows come in, which means your pivot table, your ABC tiers, your conditional formatting rules — all of it updates without you manually adjusting ranges. I've seen analysts skip this and then spend twenty minutes every month wondering why their pivot table is missing the last three weeks of sales.

What a Clean POS Export Looks Like Before You Touch a Formula

A usable export has one header row, no merged cells, no subtotal rows baked into the middle of the data, and a consistent SKU format. That means "SKU-1042" shouldn't randomly appear as "1042" in row 300. If your Shopify or other POS system export doesn't look like that, Power Query is about to become your favorite thing in Excel.

---

Phase 1: Clean Your POS Export in Power Query Before Any Retail Inventory Analysis Will Work

This phase is what every competitor article skips. They assume your data is clean. It never is — not from a real POS system.

Load Your POS Data into Power Query and Remove the Junk Rows

1. Go to Data → Get Data → From File → From Text/CSV (or From Workbook if it's XLSX).
2. In the Power Query editor, check the top rows. If your export has a title row above the headers (common in Shopify exports), click Remove Top Rows and trim accordingly.
3. Use Use First Row as Headers if Power Query didn't pick them up automatically.
4. Filter the SKU column to remove any blanks. These are usually subtotal rows or section breaks your POS system inserted.
5. Click Close & Load To → Table. Name the sheet something like Inventory_Clean.

Next time you get a new export, right-click the query and hit Refresh. The whole clean table regenerates. That's the payoff for setting this up once.

Standardize SKU Columns and Fill Down Missing Category Values

In Power Query, select the SKU column and use Transform → Format → Trim and UPPERCASE to normalize it. Inconsistent casing and trailing spaces are the silent killers of VLOOKUP accuracy.

If your category column has blanks where the same category repeats (a common POS export pattern), right-click the column header and select Fill Down. Power Query fills each blank with the value above it. Thirty seconds. Done. Your stock level tracking logic downstream won't break on empty cells.

---

Phase 2: Build a Pivot Table That Shows Inventory Turnover Rate and Stock Levels at a Glance

With a clean, Table-formatted dataset behind you, building the pivot table is the fast part.

Calculate Inventory Turnover Rate Inside the Pivot Table

Insert a pivot table from your Inventory_Clean table. Put Category and SKU in Rows, Units Sold in Values (Sum), and Units On Hand in Values (Average). Then add a Calculated Field via PivotTable Analyze → Fields, Items & Sets. Name it Turnover Rate and set the formula to =Units_Sold / Units_On_Hand.

A quick benchmark: grocery and consumables typically run turnover ratios between 12 and 20 annually. Apparel sits closer to 4 to 6. Specialty retail lands somewhere in between. If a SKU's turnover is below 2 and it's not a planned slow-mover, that's a conversation worth having before the next buying cycle.

These turnover benchmarks are general industry ranges. Your category, region, and buying cycle may shift them. Use them as a starting point for internal comparison, not a hard target.

Add a Reorder Point Column So the Pivot Table Flags Low Stock Automatically

Add a helper column to your clean table (not inside the pivot) called Reorder Point. The stock replenishment formula is:

(Average Daily Sales × Lead Time in Days) + Safety Stock

Pull average daily sales from an AVERAGEIF against your date and SKU columns. Lead time usually lives in a separate supplier reference table you VLOOKUP against. Once that column exists, your pivot table can surface it alongside on-hand units, and the comparison tells a buyer exactly where they stand. If you want a deeper look at formula logic like this, the Excel formulas and functions guide walks through the building blocks.

---

Phase 3: Run ABC Analysis in Excel to Rank Your Inventory by Revenue Impact

Turnover tells you how fast stock moves. ABC analysis tells you which stock actually matters. Those aren't the same question.

Sort and Cumulate Revenue Share with a VLOOKUP-Friendly Helper Column

In a new sheet, paste in your SKU list with total revenue per SKU. Sort descending by revenue. Add a cumulative revenue column: in the first row, it equals that SKU's revenue. In every row below, it's the previous cumulative total plus the current row's revenue. Then add a Revenue Share % column dividing each cumulative value by total revenue.

Assign tiers with an IF formula:

=IF(E2<=0.8,"A",IF(E2<=0.95,"B","C"))

SKUs in the top 80% of cumulative revenue are A-items. The next 15% are B. Everything else is C. Keep this tier column VLOOKUP-friendly by making sure SKU codes match your clean table exactly (see the Power Query standardization step above).

Layer in XYZ Analysis to Flag Which A-Items Have Unpredictable Demand

ABC alone can mislead you. An A-item with volatile demand needs a completely different replenishment strategy than a steady one. XYZ analysis adds that layer by classifying SKUs on demand variability using the coefficient of variation:

=STDEV(monthly_sales_range)/AVERAGE(monthly_sales_range)

A CV below 0.5 is X (stable). Between 0.5 and 1.0 is Y (variable). Above 1.0 is Z (unpredictable).

An AX item is a high-revenue, predictable product: your easiest reorder decision. An AZ item is a high-revenue, wildly unpredictable product — the one that'll either clear out in a weekend or sit for six weeks. Demand forecasting for AZ items needs buffer stock built in. For help structuring reference tables that make the ABC-XYZ matrix easier to query, check out the guide on working with Excel tables and ranges.

---

Phase 4: Use Conditional Formatting to Surface Shrinkage, Dead Stock, and Reorder Alerts

Conditional formatting is the most underused power feature in Excel. Not VLOOKUP, not pivot tables. When it's applied well to an inventory report, a buyer can scan 400 rows and find the three SKUs that need action in about eight seconds. When it's applied badly (or not at all), they're hunting.

A misconfigured conditional formatting rule can send the wrong signal to every person who reads the report. Before you share any color-coded file, manually verify each rule fires on the right cells with test values. One wrong threshold means decisions get made on bad data.

Apply three rules to your inventory table:

1. Red fill using a formula rule: =$D2<$F2 (on-hand units less than reorder point)
2. Amber fill for dead stock: =$G2=0 on a "Units Sold Last 60 Days" column — zero movement flags a problem
3. Color scale on a shrinkage variance column showing Expected Units − Actual Units, so negative variances darken automatically

Flag Negative Variance Between Expected and Actual Stock for Shrinkage Tracking

Expected units come from your opening count plus received stock minus recorded sales. Actual units come from your most recent physical count or POS system sync. The gap is your shrinkage signal. If it's consistently negative on specific SKUs, you're looking at waste, theft, or a receiving error — none of which show up in a standard inventory turnover rate calculation.

For the visual side of presenting this data clearly (charts, color scales, the squint test), the Excel charts and data visualization for retail inventory guide goes deeper on the design side.

---

Common Mistakes in Excel Retail Inventory Analysis (and How to Catch Them Before They Skew Your Numbers)

The most expensive mistake is skipping Power Query and filtering your raw POS export by hand. It seems faster in the moment. Then you update the report the following month, forget which rows you manually deleted, and your pivot table includes a subtotal row as if it's a SKU. Your turnover numbers are wrong and you don't know it yet.

Second: mixing sell-through units and on-hand units in the same pivot field. These are different things. Sell-through is what left the store. On-hand is what's still there. Combining them in a single Values field produces a number that means nothing to anyone.

Third, and this one trips up experienced analysts: using average inventory in the turnover formula without accounting for seasonal peaks. If you average twelve months of on-hand units for a product that sells 80% of its annual volume in Q4, your turnover ratio looks terrible for eleven months and misleadingly great in December. A seasonal inventory analysis approach breaks the calculation into periods. The advanced Excel basics guide covers period-based calculations if that math is new territory.

Fourth: forgetting to refresh your ABC tier assignments after loading new POS data. The tiers are calculated once from a snapshot. If you pull in three months of new sales and don't re-sort and recalculate cumulative revenue share, last year's C-items might be quietly pulling A-item revenue and getting ignored at reorder time.

Fifth: assuming a shrinkage variance is a VLOOKUP mismatch. It's easy to spend twenty minutes debugging a formula when the actual problem is a receiving discrepancy that made it into the data. Before you fix the formula, verify the source numbers are right. Check the SKU match first. If that's clean, the variance is real and worth investigating.

If you're building this kind of workflow from scratch and want to make sure your foundational Excel skills are solid first, the Excel for beginners guide is a good starting point before tackling pivot tables and Power Query together.

---

---