Sheet with VERY LARGE data sets and complex formulas.

Lurkily

New Member
Joined
Nov 30, 2011
Messages
22
I'll start with my question, first. What operations tend to bog excel down, and how can I speed up handling of very large data sets?

My circumstances are as follows:

I have several sheets, each of which are upward of 10K rows and 20 columns, but one of which is over 60K rows.

These are reports from various sources. I use a "Lookup" sheet to do an index/match on all those sheets, and get a combined view of all these reports. Enter a unique ID, and it populates with the information EVERY sheet provides (or doesn't) on that ID. It also does a number of QOL tasks, like combining first/last names, stripping prefixes or suffixes from usernames to get real first/last names (to compare various sources of tracking, make sure they are accurate), and so some math on dates, like to see if the last login was a long time ago.

Is there anything that might help? Maybe, for instance, using helper columns to do a single index and match for a unique ID, then using a direct reference in each cell for that row based on those results, instead of doing an index/match in every single cell for a row? Like, instead of index/match, just do one match and then use indirect() to go to A-(match result), B-(match result), C-(match result), etc?

I use a lot of index and match, of course, I use a lot of right(), left(), len(), and concatenate() to reformat fields or emails into full names (our emails and usernames are full-name-based), but I suspect the biggest problem is just the amount of data, and I also suspect that nothing will solve that.

EDIT:I should add that I also use a fair amount of conditional formatting, but only on the lookup sheet, which is typically less than 200 rows at all times, never more than 1K.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I'm looking back at my post and realizing that this is only a large data set for me, and that my formulas are probably only complex for me.

Still, these are probably the source of the problem I need to solve, the number of formulae and the sheer number of entries they operate on.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top