I have a data range with just under 100,000 lines by about 60 columns. In one of the final columns I used the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0) in order to set a flag the first time that each unique value in Column A appears. (I know about the remove duplicates function but the data was sorted in a certain order and I needed information specifically from the first row in which each unique value appears.) The formula works - it's not very quick, but it works.

The issue I'm encountering though is that now I want to remove that formula so it doesn't keep recalculating and every technique I use either takes forever or stalls Excel entirely. I've seen the same issue in two different spreadsheets where I used the same technique. Techniques I've tried: selecting and deleting the entire column, copying and pasting values over the formula, Selecting all the cells and pressing delete, copying and pasting an empty cell over them, Selecting and cutting and pasting the cells to another workbook. I've tried search and replacing text to break the formula and stop it calculating. I've done these while disabling automatic recalculation and even turning off Data Filtering.

When I try any of the above, the Excel instance starts using one CPU core at 100%. I've had the same issue in another spreadsheet, but that only had 70,000 rows and I was able to delete it, it took a while.

I just can't understand why a simple delete operation should take so long, it shouldn't matter what is in the cell because all I want to do is delete it.


Thanks for any help!