1

I frequently need to run operations in big Excel tables (e.g. 100000 rows x 1000 columns - file size >100 MB) and likely due to the file size, when I try to do one calculation across the whole table, it takes longer than "expected" (?) or freezes up. (on the side, I need to have my MS outlook open, some MS WORD files and an internet browser with multiple tabs).

Q1. any suggestion how to operate Excel (i.e. work on a large spreadsheet) faster

Q2. does freeing up RAM help run a faster Excel

Q3. any suggestion how to operate a big Excel table fast or without crashing Excel ?

4
  • 4
    tbh, if you're up to that kind of size you're into database territory, not spreadsheet.
    – Tetsujin
    Dec 19, 2018 at 17:17
  • @Tetsujin, so you are suggesting that I convert the spreadsheet into a format that is compatible with database management?
    – B Chen
    Dec 19, 2018 at 17:22
  • 3
    You're talking 100 million potential entries + calculations... yes. A database would separate the calculation from the entries, at least. Spreadsheets are just not designed for that kind of punishment.
    – Tetsujin
    Dec 19, 2018 at 17:25
  • 2
    I expect you'll find that if you leave the "frozen" excel alone for a while (with high CPU usage), it'll eventually complete its operation and become responsive again. That said... as @Tetsujin has pointed out, what you really want for this volume of data is a database - e.g: MySQL. I'd never even contemplate dealing with a 100 MB Excel file.
    – Attie
    Dec 19, 2018 at 18:15

1 Answer 1

1

First moving to a database is the best course of action as others have pointed out.

More memory helps.

Migrate to an SSD over a hard drive.

These will both help. However, I suggest looking up 1st through 5th normal form in regards to databases and make a database that follows those rules.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .