Posts

Your Excel Total Includes the Rows You Filtered Out

Image
SUM does not know the filter exists. You filter a list to one region, read the total at the bottom, and quote a figure that includes every row you just hid. One function fixes it: =SUBTOTAL(109, D2:D21) The 109 matters more than most people realise, and the rest of this explains why. Six rows are on screen. SUM is still adding all twenty, and nothing about the total says so. Why nobody catches it Because Excel tells you the right answer somewhere else. Select the filtered column and look at the status bar along the bottom. The Sum it shows there counts visible cells only . So the status bar says one number, the cell holding =SUM() says another, and the two disagree by however much you filtered out. Almost nobody has both in view at once. You read the cell, because the cell is the one that goes in the report. ⚠️ Filtering hides rows. It does not remove them, and no ordinary formula treats a hidden row differently from a visible one. SUM , AVERAGE , COUNT , MAX and...
Post a Comment
Read more

How to Pull Data From Another Google Sheet Automatically

Image
One function pulls data from another Google Sheets file: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC.../edit", "Sheet1!A1:D100") It will return #REF! the first time. That's expected, and the fix is one click — covered immediately below, because it's where everyone gets stuck. The formula is correct. This is Sheets asking permission to connect the two files, and it only asks once. The step nobody mentions The first time you import from a particular file, the formula returns #REF! rather than data. Hover over the cell and a small prompt appears: "You need to connect these sheets" , with an Allow access button. Click it. The data appears within a second or two. This is a deliberate security step, not a bug. It means nobody can silently pull data out of a file you own by knowing its URL — someone has to explicitly authorise the connection, and only someone with edit access to both files can do it. ⚠️ The authorisation ...
Post a Comment
Read more

Why Your Totals Are Wrong: Numbers Stored as Text

Image
SUM silently ignores text. A column that looks entirely numeric can contain a few text entries, and your total will simply be wrong — no error message, and no indication in the total itself that anything was left out. One formula tells you whether it's happening: =COUNTA(D2:D500) - COUNT(D2:D500) COUNTA counts anything; COUNT counts only numbers. Anything other than zero is how many cells are lying to you. Fourteen invoices, five of them invisible to SUM. The total is understated by 7,735 and looks entirely reasonable. Why this one matters more than the others Every other error in Excel announces itself. #N/A , #REF! , #VALUE! — you can see them and go and fix them. This one produces a plausible number. In the example above the sheet reports 10,247 where the true figure is 17,982 — understated by more than 40%. The only hint is a small green triangle in the corner of five cells, and a difference in which side of the cell the figures sit on. Neither appears anyw...
Post a Comment
Read more

Fix the #SPILL! Error in Excel

Image
#SPILL! means the formula worked and had nowhere to put the answer. Something is sitting in the cells it needed. Click the cell, then click the warning triangle beside it. Excel names the specific reason — and there are six, with different fixes. Most people never open that menu and start guessing instead. The formula asked for ten rows and one of them was occupied. Excel names the reason and offers to select the cell in the way. What a spill is Modern Excel formulas can return more than one value. =SEQUENCE(10) returns ten numbers, =UNIQUE(A2:A500) returns however many distinct values it finds, =FILTER(...) returns however many rows match. You write the formula in one cell and the results spill into the cells below or beside it. A blue border marks the spilled area. Only the top-left cell contains a formula; the rest are its output. If any of those cells isn't empty, nothing spills, and you get #SPILL! instead. ⚠️ This error only exists in Microsoft 365 and Exc...
Post a Comment
Read more

How to Recover an Unsaved Excel File

Image
File → Info → Manage Workbook → Recover Unsaved Workbooks. That's the answer if you closed a file you never saved. Excel keeps a copy for four days , so do this now rather than later — and don't wait until you've finished reading. If the file had been saved before and you've lost recent changes, the route is different, and it's below. Which situation are you in? What happened Where to look Never saved at all — new file, closed without saving Manage Workbook → Recover Unsaved Workbooks Excel crashed with the file open The Document Recovery pane on restart Saved, but you want an earlier version File → Info → Version History Saved over it with the wrong content Version History, or Windows Previous Versions Deleted the file Recycle Bin, then OneDrive's own bin ⚠️ Before anything else: don't keep opening and closing Excel . Each restart can overwrite the temporary files you're trying to recover from. Work through one...
Post a Comment
Read more

Why Excel Is Slow: 9 Fixes for Large Files

Image
Before fixing anything, press Ctrl+End. That jumps to the last cell Excel thinks you're using. If your data ends at row 4,000 and Excel lands you on row 600,000, you've found the problem in three seconds — and it's the most common cause of a slow file that isn't actually big. The nine fixes below are ordered by how often they're the culprit and how much they recover. The same three answers, computed two ways. The difference is which rows Excel was asked to look at. First, find out what's slow Check What it tells you Ctrl+End Whether the used range has run away File size against row count 20MB for 5,000 rows means something other than data Ctrl+G → Special → Formulas The status bar shows how many formula cells you have Formulas → Calculation Options → Manual , then F9 How long one full recalculation takes That last one is the measurement that matters. Set calculation to Manual, press F9, and count. If F9 takes eight secon...
Post a Comment
Read more

Excel Shows the Formula Instead of the Result — Fix It

Image
If every formula on the sheet is showing as text, press Ctrl+` — the grave accent key, above Tab, sharing with the tilde. That's Show Formulas, and it's on. If only some cells are affected, it's a different problem with a different fix, and the rest of this guide is about those. Three cells that never computed anything, and one that did. The only difference is what was in the cell before the formula went in. Which problem do you have? Symptom Cause The whole sheet, and the columns went wider Show Formulas is on One cell or column, text sitting to the left The cell holds text, not a formula Some sheets affected, others not Show Formulas — it's a per-sheet setting The widened columns are the giveaway for Show Formulas. Excel widens them to fit the formula text, and narrows them again when you turn it off. ⚠️ Show Formulas is saved with the file and set per sheet, not per workbook. A colleague can send you a file already in that stat...
Post a Comment
Read more