• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove entire column of formulas

P

patsfan

Member
Does anyone know how to efficiently remove an entire column of formulas?
I have about 45,000 rows in one column with an "IF, IF, COUNTIF, MAX" formula and I cannot seem to quickly remove it.
I've tried, copy/paste values, find/replace "=" to a text character, turning off calculation, deleting the entire column. Still it takes far too long to calculate and usually locks up Excel. The only thing I can do is remove about 1000 rows at a time and wait for it to finish calculating.
I figure the problem with the long calculation time is probably in my formula which references some cells in the same column (still no circular references). These are usually from older spreadsheets (2007) but I have since learned a more efficient and effective way to obtain the same results.
Can anyone suggest other options to remove these columns?
 
Hi ,

Are there other cells which are referring to this column of formulae ?

Without discussing deletion of this column of formulae , what is the recalculation time of your worksheet ( with this column still in place ) ?

Narayan
 
Yes, the results from this column were referenced (index/match) on a couple other sheets. usually took about 5-6 seconds to calculate.
 
Hi ,

In that case , have you tried breaking the dependencies first , and then deleting the column ?

First remove all references to the formulae in this column which you are going to delete , and then delete the column ; see if the time taken is still on the higher side.

Narayan
 
Thanks but that isn't speeding up the process. I removed all references to this column from the other formulas.
I am assuming since my formula references all other cells above each one, in the same column, is why it's taking so long.
I was using this formula below to capture a unique list (with counts). However, I've since found a better and more efficient method to obtain my data.
i.e "IF(COUNTIF(C$9:C13904,C13904)>1,"-",MAX(B$6:B13903)+1)). As you can see, it would take a while to calcuate.
 
Hi ,

I am able to see that the column deletion is taking inordinately long , compared to simple worksheet recalculation.

I inserted your formula down to about 35000 rows ; recalculation took only a minute , but the column deletion has been going on for more than 10 minutes and Excel has stopped responding.

The only solution is that prior to deleting the column , you need to clear all of the cells , starting with the bottom-most one , and working your way upwards. This should not take much time.

Narayan
 
Thanks Narayan for taking the time to help me.
As you mentioned, I also noticed that if I clear the cells from the bottom up, it reduces the time to update. Thanks to all you guys, I am constantly learning and applying more efficient methods to building my spreadsheets.
 
In addition you could also set excel options to manual calculation. Once you are done with deletion you can turn it back to automatic calculation.

This will make excel not to recalculate every time there is change in any cell.

Regards,
Prasad DN
 
You must log in or register to reply here.
Back
Top