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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
498
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
That doesn't really surprise me, are you putting the conditional formatting on the entire column, or just the used rows?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
This sort of thing typically is much easier in a database product like Microsoft Access.
 

Felix1980

New Member
Joined
May 16, 2018
Messages
40
@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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
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.
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,126
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
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:

Felix1980

New Member
Joined
May 16, 2018
Messages
40
Macros are completely great! Was going to look towards that if there were no obvious fixes. Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,176,298
Messages
5,902,365
Members
434,968
Latest member
TutanRamon

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
Top