Excel Speed Dismal

Felix1980

New Member
Joined
May 16, 2018
Messages
40
Hello,
I'm looking for any help on my specific issue. I work with data sets that usually have a minimum of 130,000 rows, and find that I cannot do almost anything with these files without breaking them down into workbooks of 10,000 rows most of the time. My specs :

Processor : Intel Core i7-8565U CPU @ 1.8GHz
RAM : 16GB , 15.8 usable
System : 64 bit OS Windows 10 Pro
Excel Version : 2109 (Build 14430.20342) Microsoft 365

Workbook :
142,061 rows
21 columns
No formulas
No conditional Formatting
Just raw data

I want to check if there are any duplicate IDs so :
Add conditional formatting to only column A for duplicate values
Press the button on column A for the filter and get hung up.

It sits there spinning for minutes upon minutes. When it finally does finish, the drop down filter shows on screen for a second, then disappears. No I did not press on anything, or do anything after pressing the filter button.

Everything I read suggests I should be able to work with almost 9x this data just fine. Does anyone have any advice?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Are your files stored on the local machine or on a network share/sharepoint/other? If you can, try saving the file to your local machine first.
 
Upvote 0
That doesn't really surprise me, are you putting the conditional formatting on the entire column, or just the used rows?
 
Upvote 0
This sort of thing typically is much easier in a database product like Microsoft Access.
 
Upvote 0
@Candyman8019 I have attempted both with the same result
@Fluff The whole Column. Never thought to attempt just used range. Just tried, same result.
@Joe4 I have tried using Access in the past, but end up having the same issue. Spend hours uploading data from different workbooks, wait for extended periods of time for a query to process. Of course, that said, it's always on our work's servers. Perhaps, they are sub-par?

@Fluff You're not surprised? I thought that excel is supposed to be able to handle a million rows, no problem?
 
Upvote 0
Of course, that said, it's always on our work's servers. Perhaps, they are sub-par?
Sounds like that could certainly be the case.

Note that in Access you can do some things which made speed it up, like indexing the field that you are trying to find the duplicates on.

It should be able to do that without two much trouble. If everything seems to be slow, I would lean toward saying it is environmental issues, as you suspect.
 
Upvote 0
Are you using the same workbook each time ? Can you try a new clean workbook ?
Do have access to another computer to see if it is just your machine ?
How are you getting the data into your workbook?
 
Upvote 0
It's not just the amount of rows, it's the conditional formatting that causes the problem.
If your happy to use a macro, you could use
VBA Code:
Sub Felix()
   Dim Ary As Variant
   Dim r As Long
  
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), r + 1
         Else
            Range("A" & r).Interior.Color = 13551615
            Range("A" & .Item(Ary(r, 1))).Interior.Color = 13551615
         End If
      Next r
   End With
   Range("A1").AutoFilter 1, RGB(255, 199, 206), xlFilterCellColor
End Sub
It takes anything from 3 to 14 secs for ~186,000 rows depending on the number of duplicates.
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,775
Members
452,353
Latest member
strainu

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