Home

Excel counta is counting blank cells as if they had data. A lengthy fix is given

%3CLINGO-SUB%20id%3D%22lingo-sub-771247%22%20slang%3D%22en-US%22%3EExcel%20counta%20is%20counting%20blank%20cells%20as%20if%20they%20had%20data.%20A%20lengthy%20fix%20is%20given%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771247%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20mega%20spreadsheet%20is%20full%20of%20invisible%20junk%20scattered%20across%20cells.%20It%20is%201300%20rows%20deep%20by%20500%20columns%20wide.%20Each%20bit%20of%20junk%20appears%20in%20the%20totals%20when%20I%20counta%20the%20rows%20or%20columns.%20I%20discovered%20it%20by%20accident%20when%20looking%20at%20a%20row%20with%20a%20counta%20of%203%20yet%20only%20two%20filled%20cells.%20The%20junk%20needs%20to%20be%20found%20and%20cleared...the%20cells%20must%20not%20be%20deleted.%3C%2FP%3E%3CP%3ENothing%20on%20any%20help%20page%20on%20excel%20or%20the%20internet%20told%20me%20how%20to%20deal%20with%20the%20problem.%20People%20have%20the%20problem%20but%20there%20is%20no%20automatic%20way%20to%20deal%20with%20it%20and%20no%20help%20is%20described%20as%20far%20as%20I%20can%20see.%3C%2FP%3E%3CP%3ETo%20find%20empty%20cells%20Excel%20has%20decided%20there%20is%20something%20in%20is%20Find%20%26amp%3B%20Select%20then%20Constants.%20This%20paints%20every%20occupied%20cell%20blue.%20Do%20not%20touch%20any%20cell%20with%20the%20cursor%20or%20you%20have%20to%20do%20that%20bit%20again.%3C%2FP%3E%3CP%3EThen%20in%20rows%20of%2025%2C%20jump%20a%20page%20view%20a%20time%20using%20the%20linear%20page%20mover%20(not%20a%20key%20)%20look%20though%20each%20visible%20page%20and%20note%20the%20coordinations%20of%20the%20blank%20but%20blue%20cells.%20Also%20record%20each%20set%20of%2025%20rows%20you%20have%20looked%20at.%3C%2FP%3E%3CP%3EJump%20down%20to%20move%20down%20a%20page%20then%20do%20the%20next%2025%20rows.%3C%2FP%3E%3CP%3EYou%20cannot%20delete%20from%20the%20blue%20celled%20screen.%3C%2FP%3E%3CP%3EWhen%20back%20in%20normal%20spreadsheets%20find%20each%20cell%20and%20right%20click%20clear%20contents.%3C%2FP%3E%3CP%3EOnly%20way%20I%20can%20find%20to%20clear%20invisible%20stuff.%3C%2FP%3E%3CP%3ECopying%20the%20sheet%20to%20a%20new%20spreadsheet%20using%20only%20text%20does%20not%20work.%3C%2FP%3E%3CP%3EFilling%20all%20the%20blanks%20using%20replace%20all%20blanks%20and%20e.g.%20x123%20and%20then%20deleting%20all%20x123%20filled%20cells%20does%20not%20work.%3C%2FP%3E%3CP%3EIt%20is%20wise%20to%20counta%20all%20your%20rows%20first%20and%20have%20that%20column%20on%20the%20left%20all%20the%20time%20so%20you%20can%20see%20the%20count%20fall%20as%20you%20clear%20the%20empty%20cell.%20Save%20every%20action.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-771247%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771633%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20counta%20is%20counting%20blank%20cells%20as%20if%20they%20had%20data.%20A%20lengthy%20fix%20is%20given%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771633%22%20slang%3D%22en-US%22%3ECan%20you%20please%20include%20some%20sample%20data%20from%20your%20file%3F%20Preferably%20anonymized.%3C%2FLINGO-BODY%3E
PondyDave
Occasional Visitor

My mega spreadsheet is full of invisible junk scattered across cells. It is 1300 rows deep by 500 columns wide. Each bit of junk appears in the totals when I counta the rows or columns. I discovered it by accident when looking at a row with a counta of 3 yet only two filled cells. The junk needs to be found and cleared...the cells must not be deleted.

Nothing on any help page on excel or the internet told me how to deal with the problem. People have the problem but there is no automatic way to deal with it and no help is described as far as I can see.

To find empty cells Excel has decided there is something in is Find & Select then Constants. This paints every occupied cell blue. Do not touch any cell with the cursor or you have to do that bit again.

Then in rows of 25, jump a page view a time using the linear page mover (not a key ) look though each visible page and note the coordinations of the blank but blue cells. Also record each set of 25 rows you have looked at.

Jump down to move down a page then do the next 25 rows.

You cannot delete from the blue celled screen.

When back in normal spreadsheets find each cell and right click clear contents.

Only way I can find to clear invisible stuff.

Copying the sheet to a new spreadsheet using only text does not work.

Filling all the blanks using replace all blanks and e.g. x123 and then deleting all x123 filled cells does not work.

It is wise to counta all your rows first and have that column on the left all the time so you can see the count fall as you clear the empty cell. Save every action.

2 Replies
Can you please include some sample data from your file? Preferably anonymized.

Hello @PondyDave,

 

Are these cells with invisible junk formatted so that the text is white (or matches the cell background color?)

 

If so, you can press ctrl+F (brings up the Find and Replace menu) > Select Replace > In "Find what:" leave contents blank but click Format and change the Font color to white (or whatever color your invisible cells font is) > Click OK > In "Replace with:" leave the contents blank > Click "Replace All".

 

Hope this helps!
PReagan

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies