We have recently completed an assignment which involved applying formula to 4 columns on over 500 000 rows of data (in Excel’s terms this is Big Data). For various reasons we ended up having to use Excel 2010.
We found that simply copy and pasting the formula to the 500 000 rows resulted in Excel complaining about memory issues and not completing the job.
The way we got around this was as follows:
- Firstly we switched the calculation method to manual
- Secondly we copy and pasted the formula to all the cells.
- We then ran the formula (Shift F9).
Where Excel could not handle the copy paste with Automatic calculation on, it handled the separation of the 2 processes better.
In order to avoid future problems we them copied and pasted as values all cells except the top row.
The top row we coloured in a different colour and left the formula in them in case we needed to re run the formula.