EP 01Episode 01
Fix a Messy Excel Order Export
Fix a messy Excel order export with a sample workbook, a reviewed duplicate, an exact amount conversion, and a reconciled $1,015 total.
Open the guideExcel and Google Sheets fixes.
Import data without losing IDs, make formulas return useful answers, and check the result before trusting a report.
Written steps + video
Choose a specific problem.
Each guide includes a fictional example, practice material, and a result to check. New guides appear here as they are published.
EP 01
EP 04Episode 04
Keep Leading Zeros and Long IDs When Importing CSV to Excel
A quick double-click can turn ZIP 02108 into 2108 and damage 17-digit reference codes. Practice with fictional data and check the result.
Open the guide
EP 05Episode 05
Stop Status Typos in Google Sheets With One Dropdown
Create a status dropdown in Google Sheets, then test that a misspelled value is rejected. Practice with fictional data and check the result.
Open the guide
EP 08Episode 08
Fix a PivotTable That Ignores New Rows After Refresh
A PivotTable still shows $300 after two sales lines totaling $100 were added. Practice with fictional data and check the result.
Open the guide
EP 11Episode 11
Find Missing Order Prices with Excel XLOOKUP
Match six fictional order SKUs to a five-item catalog with Excel XLOOKUP. Practice with fictional data and check the result.
Open the guide
EP 14Episode 14
Find Duplicate Order IDs in Google Sheets and Keep the Latest Update
A fictional order export has eight rows but only five unique order IDs. Practice with fictional data and check the result.
Open the guide
EP 17Episode 17
Highlight Overdue Excel Tasks Without Coloring Blanks or Done Rows
Make one Excel for the web rule that highlights overdue open tasks while leaving blank dates, today, future dates, and Done rows alone. Practice with fictional data and check the result.
Open the guide
EP 18Episode 18
Sum One Month and Category in Google Sheets With SUMIFS
Calculate September Software spend in a fictional Google Sheets ledger using SUMIFS, a category criterion, and inclusive/exclusive date boundaries. Practice with fictional data and check the result.
Open the guide
EP 22Episode 22
Excel #SPILL! Error: Find the Blocking Cell Without Losing Data
Use a fictional status list to fix one common Excel #SPILL! cause: an output range blocked by an existing note. Practice with fictional data and check the result.
Open the guide
EP 23Episode 23
Google Sheets IMPORTRANGE #REF!? Fix Access and File Type
Diagnose Google Sheets IMPORTRANGE #REF! by reading its exact message. Practice with fictional data and check the result.
Open the guide
EP 26Episode 26
Excel Formula Shows as Text? Check These 3 Causes
See the difference between Show Formulas across a sheet, a cell formatted as Text, and a leading apostrophe. Practice with fictional data and check the result.
Open the guide
EP 27Episode 27
Filter a Shared Google Sheet Without Changing Everyone Else's View
Use a filter view to isolate your rows in a shared Google Sheet without changing the ordinary filter for collaborators. Practice with fictional data and check the result.
Open the guide
EP 28Episode 28
Protect Formula Cells in Google Sheets While Leaving Inputs Editable
Build a fictional three-row order sheet, put exact line-total formulas in D2:D4, and protect only that formula range while B2:C4 remains editable. Practice with fictional data and check the result.
Open the guide
EP 31Episode 31
Excel Dates Won't Sort Correctly? Fix Dates Stored as Text
Excel dates sorting A to Z instead of oldest to newest? Follow five fictional orders, prove the imported dates are text, convert them with DATEVALUE, and sort the entire table by real dates. Practice with fictional data and check the result.
Open the guide
EP 32Episode 32
Google Sheets FILTER Says No Matches? Check Criteria and Range
A Google Sheets FILTER #N/A can mean the criterion finds no rows. Practice with fictional data and check the result.
Open the guide
EP 36Episode 36
Excel Formulas Not Updating? Check Manual Calculation
Change one quantity in a fictional order workbook, identify a stale formula total, recalculate the workbook, and restore Automatic calculation. Practice with fictional data and check the result.
Open the guide
EP 41Episode 41
Excel SUM Skips Numbers Stored as Text: Fix the Total
Repair an Excel total that skips text-formatted amounts. Practice with fictional data and check the result.
Open the guide
EP 42Episode 42
Google Sheets: Calculate New Rows Automatically with ARRAYFORMULA
Use one ARRAYFORMULA in Google Sheets to calculate line totals for existing and newly added fictional orders. Practice with fictional data and check the result.
Open the guide
EP 50Episode 50
Freeze a Google Sheets Report With Paste Values Only
Make a stable weekly report on a second tab of the same Google spreadsheet; prove its copied values do not change when source formulas recalculate. Practice with fictional data and check the result.
Open the guide
EP 51Episode 51
Excel Shows ##### Instead of a Date? Fix the Column Width
Fix Excel cells that display ##### instead of a date or number.
Open the guide
EP 52Episode 52
Excel Dropdown Missing a New Item? Expand Its Source Range
Add a new source-list item to an Excel dropdown in Excel for the web.
Open the guide
EP 53Episode 53
Split Names into Columns in Excel for the Web with TEXTSPLIT
Split comma-separated names into adjacent columns in Excel for the web with TEXTSPLIT.
Open the guide
EP 54Episode 54
Excel Formula Changes When Copied Down? Lock the Rate Cell
Keep a single tax rate fixed when filling an Excel formula down.
Open the guide
EP 55Episode 55
Excel Sort Mixed Up Your Rows? Keep the Whole Table Together
Sort an Excel order list by date without separating IDs, contacts, and amounts.
Open the guide
EP 56Episode 56
Color an Entire Google Sheets Row When Status Is Done
Use a custom conditional-format rule to color a full row when its status cell says Done.
Open the guide
EP 57Episode 57
Google Sheets Split Text to Columns: Separate Name and Email
Split a comma-separated name and email field into two Google Sheets columns.
Open the guide
EP 58Episode 58
Count Checked Boxes in Google Sheets with COUNTIF
Count completed tasks from Google Sheets checkboxes using COUNTIF with TRUE.
Open the guide
EP 59Episode 59
Repeat Header Rows on Every Printed Google Sheets Page
Keep column labels visible on every printed page in Google Sheets.
Open the guide
EP 71Episode 71
Sum Only the Visible Filtered Rows in Google Sheets
Use SUBTOTAL to total only visible filtered rows in a fictional six-order Google Sheet, then test East, West, and manually hidden rows.
Open the guide
EP 74Episode 74
Fill an Excel Formula Down Without Dragging
Fill a line-total formula through four rows in Excel without dragging the fill handle, and check that relative references update correctly.
Open the guide
EP 75Episode 75
Fix VLOOKUP #N/A Caused by a Hidden Space in Google Sheets
Diagnose an exact-match VLOOKUP that returns #N/A because a fictional SKU has one trailing space, then verify the corrected price.
Open the guide
EP 77Episode 77
Freeze the Top Row in Excel for the Web
Freeze the first row of a long fictional Excel list, check the header at row 35, and unfreeze it when needed.
Open the guide
EP 81Episode 81
Excel #DIV/0! from a Blank or Zero Cell? Check the Denominator
Find the blank or zero denominator behind an Excel #DIV/0! result, then show a blank only while a fictional production rate is unavailable.
Open the guide
EP 82Episode 82
Excel #REF! After Deleting a Column? Undo Before Rebuilding
Reproduce a broken explicit-reference total on a disposable Excel copy, restore the deleted data with Undo, and verify the original total.
Open the guide
EP 88Episode 88
Excel Circular Reference: Fix a Total That Includes Itself
Diagnose a self-including SUM range in a fictional Excel order sheet, correct the total formula, and verify every source row and the final sum.
Open the guide
EP 89Episode 89
Google Sheets SUM Ignores Text Amounts? Convert a Practice Column
Show why a Sheets total can miss numbers held as text, convert three controlled text values with VALUE, and check the exact 25-unit result.
Open the guide
EP 91Episode 91
Remove Duplicate Orders in Excel but Keep the Newest Update
Keep the latest status for each fictional order ID in Excel for the web by sorting the full data range before removing duplicates.
Open the guide
EP 92Episode 92
Find Missing IDs Between Two Google Sheets Lists
Compare two fictional ID lists in Google Sheets without assuming matching row positions, and identify records missing in either direction.
Open the guide
EP 95Episode 95
Excel XLOOKUP Shows One Order? Return Every Match with FILTER
Use Excel FILTER to return all fictional orders for one client, confirm the three matching rows, and show a clear no-match result.
Open the guideNo guides match that search. Try a shorter phrase.
Continue browsing