Excel remains the most widely used planning tool.
No debate there.
Even with advanced planning systems in place, planners still download data into Excel for easier manipulation and analysis.
Excel offers unmatched flexibility. Spreadsheets are here to stay.
In this article, I cover 10 essential Excel functionalities every demand planner should know.
For each one, you’ll find:
• A real-world situation
• A challenge
• A practical solution using Excel
Let’s dive in!
1. Cleaning Messy Data
TRIM in Action
- Situation: Distributor files arrive with customer names padded with extra spaces.
- Challenge: VLOOKUP and Pivot Tables don’t recognize “ Walmart” as the same as “Walmart”.
- Solution: Use TRIM to remove unnecessary spaces.
=TRIM(A2)
Now “ Walmart ” becomes “Walmart”, and your formulas match correctly.
CLEAN in Action
- Situation: Exported files sometimes include hidden line breaks or system characters.
- Challenge: The SKU looks fine but formulas break because of invisible characters.
- Solution: Use CLEAN to strip out non-printable characters.
=CLEAN(A2)
Turns “SKU123□” into “SKU123”, fixing hidden errors.
SUBSTITUTE in Action
- Situation: Some files list product codes as “SKU-123”, while your system uses “SKU123”.
- Challenge: Codes don’t align across systems.
- Solution: Use SUBSTITUTE to replace unwanted characters.
=SUBSTITUTE(A2,”-“,””)
Now “SKU-123-456” becomes “SKU123456”, standardizing your data.
2. SUMIFS – Aggregating Demand
Situation: A beverage planner needs to know how much Walmart ordered in January across multiple SKUs.
Challenge: Filtering rows and adding totals manually takes hours.
Solution: Use SUMIFS to total sales by conditions.
=SUMIFS(Sales, Customer, “Walmart”, Month, “Jan”)
This instantly returns total sales for Walmart in January.
3. TEXT – Standardizing Dates
Situation: A cosmetics planner prepares a monthly review, but sales data comes in daily format.
Challenge: Leaders want month-to-month trends, not daily noise.
Solution: Use TEXT to reformat dates.
=TEXT(A2,”MMM-YYYY”)
Groups daily data into months for clear charts and analysis.
4. FORECAST.LINEAR – Building Baselines
Situation: A snack company wants a quick projection for next month’s sales.
Challenge: Manual guesses from sales or marketing are biased.
Solution: Use FORECAST.LINEAR to project demand from history.
=FORECAST.LINEAR(NextMonth,Month,Sales)
Provides a neutral baseline forecast you can adjust with market intelligence.
5. Measuring Accuracy & Bias
ABS in Action
- Situation: A planner needs to know how far off the forecast was for each SKU.
- Challenge: Negative errors cancel out positive ones if you just subtract.
- Solution: Use ABS to calculate absolute error.
=ABS(Forecast-Actual)/Actual
Shows the error % without direction.
IFERROR in Action
- Situation: Some SKUs have zero sales, causing divide-by-zero errors.
- Challenge: Reports are filled with #DIV/0! messages.
- Solution: Use IFERROR to replace errors with 0.
=IFERROR((Forecast-Actual)/Actual,0)
Keeps reports clean and usable.
AVERAGEIFS in Action
- Situation: Leadership wants accuracy by region.
- Challenge: Need to calculate the average error only for the North region.
- Solution: Use AVERAGEIFS to calculate averages with criteria.
=AVERAGEIFS(ErrorRange,Region,”North”)
Now you can see accuracy broken down by market segment.
6. Conditional Formatting – Adding Market Intelligence
Situation: Reviewing Black Friday SKUs across hundreds of rows.
Challenge: Hard to spot risky items in raw numbers.
Solution: Apply Conditional Formatting rules:
- Red if error >30%
- Yellow if forecast missing
- Green if accurate
This makes risky SKUs stand out instantly, and you can add notes for context.
7. Pivot Tables – Building the Demand Review Pack
Situation: A frozen food planner needs to prepare a demand review for S&OP.
Challenge: Raw sales and forecast data has thousands of rows.
Solution: Insert a Pivot Table to summarize by SKU, region, or customer.
Leadership quickly sees performance gaps and growth opportunities without digging into raw files.
8. Tracking Plan vs Actual
IF in Action
- Situation: Leadership wants a quick flag showing if actual sales beat the forecast.
- Solution: Use IF to mark performance.
=IF(Actual>Forecast,”Above”,”Below”)
Quickly flags SKUs that over- or under-performed.
Sparklines in Action
- Situation: Hundreds of SKUs need trend visibility.
- Challenge: Building full charts for each SKU would clutter the file.
- Solution: Use Sparklines (tiny row-level charts).
Insert → Sparklines → Line
Shows demand trends in a single cell, compact and clear.
9. Power Query – Handling Large Data Sets
Situation: Forecast files arrive from 10 markets, each with different formats.
Challenge: Manual cleanup takes days and delays reviews.
Solution: Use Power Query to clean, standardize, and merge automatically.
With Refresh All, you instantly get a consolidated dataset.
10. Automating Repetitive Work
VBA in Action
- Situation: Every week a planner refreshes data, updates charts, and emails a PDF report.
- Challenge: Repetition wastes hours.
- Solution: Record a VBA macro to do it in one click.
LET in Action
- Situation: A complex formula repeats the same calculation several times.
- Challenge: Hard to read and slow to update.
- Solution: Use LET to define a variable once and reuse it.
=LET(Error,(Forecast-Actual)/Actual,ABS(Error))
Cleaner, faster formulas.
SEQUENCE in Action
- Situation: Planner needs a rolling list of weeks (1–52) for the forecast sheet.
- Challenge: Typing them manually is error-prone.
- Solution: Use SEQUENCE to generate them instantly.
=SEQUENCE(52,1,1,1)
One formula fills all week numbers automatically.
✅ Conclusion
All 10 Excel functions target different pain points:
- TRIM / CLEAN / SUBSTITUTE → Fix messy inputs
- SUMIFS → Aggregate demand
- TEXT → Standardize dates
- FORECAST.LINEAR → Build unbiased baselines
- ABS / IFERROR / AVERAGEIFS → Measure accuracy & bias
- Conditional Formatting → Highlight risks
- Pivot Tables → Summarize demand
- IF / Sparklines → Track plan vs actual
- Power Query → Clean large datasets
- VBA / LET / SEQUENCE → Automate repetitive work
Together, they form a complete Excel survival kit for demand planners.
