Clear formula cells that return 0 value

[Nandy7071]

Is there a way to clear all cells which have formulas but return 0 value? Im trying to reduce the file size of my spreadsheet.

Ive seen some suggestions to use "Find and Replace" and replace all cells from "0" to "" but Excel doesn't allow find and replace on values, only formulas.

 

[Rick Rothstein]

@Nandy7071,

I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

1) Select the columns with your formula numbers in them

2) Press CTRL+F to bring up the Find dialog box

3) Put 0 in the "Find what" field

4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

5) Click the "Find All" button

6) Press CTRL+A (this will select all of the found cells)

7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

Now here is where the time saver comes in...

8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).

 

[RossTattersall]

Couldn't you also add to the start of that to press F5, Special, Formulas, to limit your selection just formulae?

 

[Nandy7071]

need some more knowledge on what is being done to know if this would be OK,

faster if suitable would be to sort the data so that all the zero values are in one block
identify that block, and clear all in one hit
if needed, sort again to original order (may need temporary field for this to know the original order)

This worked. Reduced 85mb file down to 35mb in 1.5 hours. Great idea fazza. Thank you

 

[Nandy7071]

@Nandy7071,

I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

1) Select the columns with your formula numbers in them

2) Press CTRL+F to bring up the Find dialog box

3) Put 0 in the "Find what" field

4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

5) Click the "Find All" button

6) Press CTRL+A (this will select all of the found cells)

7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

Now here is where the time saver comes in...

8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).

Going to try this out too and see if it’s faster than fazza’s idea. Will keep you posted.

 

[Nandy7071]

@Nandy7071,

I just had a thought that could possibly speed up your process greatly depending on how your data is laid out. You said earlier that you have 10 columns of 4500 formulas on 30 sheets. I don't know if you have other cells with either constant numbers or text on those sheets or not, but can you manually select just the columns with formula numbers only? If so, the following procedure should be quite a lot faster even though it is an entirely manual procedure. For the first sheet...

1) Select the columns with your formula numbers in them

2) Press CTRL+F to bring up the Find dialog box

3) Put 0 in the "Find what" field

4) Click the "Options>>" button and make sure the "Look in" drop down has "Values" selected and put a check mark in the checkbox labeled "Match entire cell contents"

5) Click the "Find All" button

6) Press CTRL+A (this will select all of the found cells)

7) Leave the dialog box open and click the "Clear" button on the Ribbon (Home tab, Editing panel) and select "Clear All" from the drop down that appears

Now here is where the time saver comes in...

8) Select the next sheet and select the formula columns on it, then repeat steps 5, 6 and 7 (you do not have to repeat the earlier steps for the rest of the sheets).

I am pretty sure the above procedure should move along at a remarkably fast pace so long as you can pick columns of data that do not contain cells with the constant (non-formula) 0 in them (they would be cleared along with the formula 0's if they were included in your selection).

Ricks method was even faster. 45 minutes for the 30 sheets. ?

 

[Rick Rothstein]

Ricks method was even faster. 45 minutes for the 30 sheets. ��

Great... although I was hoping it would have been even faster than that. I am not sure this will make a difference or not, but it is something you might try... set calculations to "Manual'" before you start the procedure and turn it back to "Automatic" after the last sheet has been processed. I don't think it will speed up the overall process as all of the delayed calculation will eventually have to take place, but I am thinking it might lessen the time spent on each individual sheet and put the time delay inherent in calculating each sheet after the cells are cleared until after the last sheet has been attended to when you no longer have to "baby sit" the workbook any more (it will finish up whenever it does so, but that would not require you to sit at the workbook while it happens).

Side Note: What is particularly annoying is the creators of Excel did not expose the code behind the "Find All" button to VBA programmers, so all the speed advantage for that operation is not available to programmers.

 

[Nandy7071]

Great... although I was hoping it would have been even faster than that. I am not sure this will make a difference or not, but it is something you might try... set calculations to "Manual'" before you start the procedure and turn it back to "Automatic" after the last sheet has been processed. I don't think it will speed up the overall process as all of the delayed calculation will eventually have to take place, but I am thinking it might lessen the time spent on each individual sheet and put the time delay inherent in calculating each sheet after the cells are cleared until after the last sheet has been attended to when you no longer have to "baby sit" the workbook any more (it will finish up whenever it does so, but that would not require you to sit at the workbook while it happens).

Side Note: What is particularly annoying is the creators of Excel did not expose the code behind the "Find All" button to VBA programmers, so all the speed advantage for that operation is not available to programmers.

I will try that out next time. The find part only takes about 10 sec. The part of the process that took the longest is the CTL+A selecting all the fields which takes approximately 20-30 sec on one sheet.

Next time I will try selecting all the columns for all 30 sheets and running the find and clear operation on all of them at once. That should speed it up as long as the program doesn’t crash

 

[Rick Rothstein]

Next time I will try selecting all the columns for all 30 sheets and running the find and clear operation on all of them at once. That should speed it up as long as the program doesn’t crash

You cannot do that because you can only select cells on the active sheet.

 

[Nandy7071]

You cannot do that because you can only select cells on the active sheet.

Ok. Would’ve been nice but I don’t mind doing it sheet by sheet.

Thanks again for your help. Im loving this forum!

 

[Fazza]

This worked. Reduced 85mb file down to 35mb in 1.5 hours. Great idea fazza. Thank you

I wonder if that was using VBA?