We need to quintuple the size of a database sheet. What are the do's and don't's to make this run smoothly?

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all,

We have a master database of thousands of rows (3,200 so far), each row is a product with the necessary information. We are looking at implementing a new set of products, which will require a new row per product. It's likely we will be adding somewhere between 10,000 to 20,000 rows.


  1. Each row has data up to column AF (32 cols).
  2. There are no live formulas
  3. There is conditional formatting on each row up to column M
  4. The sheet is already somewhat sluggish to use

My question is, how do I stop this from getting unwieldly? Currently my plan is to remove all conditional formatting and replace it with a macro that applies a format (which is usually coloured cells or text) correctly based on the status of the product. I feel like 3,200 cells across 13 columns (41,600 cells) of conditional formatting already makes the sheet sluggish so this is the first step.


Obviously, it's not ideal to keep a database for this purpose in Excel, but these are the tools we have and I have to make the best of the situation. Any more advice given would be appreciated. Thank you!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
it would be worth checking that VBA is actually faster than CF - I'm not sure that it is.

Others may disagree, but I wouldn't have thought your existing database to be too large nor would i expect it to be sluggish. That said, it also depends on the hardware you have available - something I could'nt really comment on.

One question is where is the data coming from? 20000 is a lot of data to import manually! I would suggest investigating Powerquery to gather and process your information. Its also worth considering how much information is common between various rows. For example a particular manufacturer is likely to supply numerous components - you only need an identifier for the manufacturer with all other information being held in a separate table. You can then use either Powerquery to create a big table (not the best strategy) and/or use PowerPivot to group records in a pivottable.

Not sure these comments help, but perhaps give some different direction.

HTH
 
Upvote 0
it would be worth checking that VBA is actually faster than CF - I'm not sure that it is.

Others may disagree, but I wouldn't have thought your existing database to be too large nor would i expect it to be sluggish. That said, it also depends on the hardware you have available - something I could'nt really comment on.

One question is where is the data coming from? 20000 is a lot of data to import manually! I would suggest investigating Powerquery to gather and process your information. Its also worth considering how much information is common between various rows. For example a particular manufacturer is likely to supply numerous components - you only need an identifier for the manufacturer with all other information being held in a separate table. You can then use either Powerquery to create a big table (not the best strategy) and/or use PowerPivot to group records in a pivottable.

Not sure these comments help, but perhaps give some different direction.

HTH


Hi bud, thanks for the help, I'll go through your comments:

The VBA formatting would be updated probably only a few times per day, compared to Conditional Formatting which is recalculated every time any data set changes. To put this in perspective with 3,200 rows this is 41,600 cells. With 20,000 rows it's 260,000 cells to calculate.

The hardware we are using are pretty decent 4 core, 8 thread machines with 16GB of DDR4 RAM, all on SSD's. They're pretty good actually.

The data isn't loaded manually, at least not on our end. It's loaded into a database by another team, and our database excel sheet looks into an output file to gather the product codes, then it fills in the remaining information from a host of other sheets. I think the issue is that the VBA code that hooks all the sheets together and checks them is good, but the CF is a weight around its neck that it doesn't need.


Thanks.
 
Upvote 0
Understand the logic of what you propose, and certainly worth investigating.

...and our database excel sheet looks into an output file to gather the product codes, then it fills in the remaining information from a host of other sheets.

However that comment really makes me think the answer is to import and combine the data using PowerQuery - it can collect data from a very wide range of sources, combine it using a variety of database operations and generally transform your source into the format you need. My goto reference for PQ is 'M is for (DATA) MONKEY' by Ken Puls and Miguel Escobar.

Definitely worth investigating.

HTH
 
Upvote 0
As long as the CF is only applied to the rows with data & not the entire column, I would not expect it to be a problem.
Do you use any change or selection change events?
 
Upvote 0
As long as the CF is only applied to the rows with data & not the entire column, I would not expect it to be a problem.
Do you use any change or selection change events?

What are selection change events?


So our regular update takes about 20 seconds to execute with 3,200 rows. When I duplicate the rows up to 20,000 this update now takes 1 minute 28 seconds. If I remove all the CF, it still takes 1 minute 28 seconds.

With blank formatting, the sheet is very sluggish and even clicking on "file" to access the menu takes a helluva time to update the screen.


I've double checked and there's no live formulas. The book has several tabs but none are anywhere near as big as the main sheet. It's just become very sluggish.
 
Upvote 0
If you remove the all the CF does it make any difference?
 
Upvote 0
If you remove the all the CF does it make any difference?

Literally none :P

In your experience, do sheets become quite this sluggish when there are 20,000 rows with 32 columns? Most columns are only partially filled.
 
Upvote 0
If there are no formulae & removing the Cf makes no difference, then it sounds like your macros.
 
Upvote 0
If there are no formulae & removing the Cf makes no difference, then it sounds like your macros.

Okie dokie. So once the sheet has updated (used to take 20 seconds with 3200 rows, now takes 1:30 with 19,000) then I don't understand why it is still sluggish, if the sheet is an unformatted chunk of text. There's nothing acting upon it to take CPU cycles away from, for instance, scrolling up and down or across, nor applying filters, or going into menus, right? So I'm wondering what it is that is continuing to make it slow. Cheers

EDIT: It's at the point now where even just clicking from one cell to another causes Excel to freeze up for 3-4 seconds. Basically unusable.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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