Tjyylli

How to deal with a team lead who never gives me credit?

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

Should I use a zero-interest credit card for a large one-time purchase?

Why is "Consequences inflicted." not a sentence?

How to Merge Multiple Columns in to Two Columns based on Column 1 Value?

Can a USB port passively 'listen only'?

What does this icon in iOS Stardew Valley mean?

Why do we bend a book to keep it straight?

How to react to hostile behavior from a senior developer?

Is it fair for a professor to grade us on the possession of past papers?

What is Arya's weapon design?

Output the ŋarâþ crîþ alphabet song without using (m)any letters

Why aren't air breathing engines used as small first stages

Check which numbers satisfy the condition [A*B*C = A! + B! + C!]

How to align text above triangle figure

What is the role of the transistor and diode in a soft start circuit?

Identifying polygons that intersect with another layer using QGIS?

Do I really need recursive chmod to restrict access to a folder?

51k Euros annually for a family of 4 in Berlin: Is it enough?

Why did the rest of the Eastern Bloc not invade Yugoslavia?

Is it true that "carbohydrates are of no use for the basal metabolic need"?

Apollo command module space walk?

Book where humans were engineered with genes from animal species to survive hostile planets

How does debian/ubuntu knows a package has a updated version

Excel filtering very slow

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

5

1

At the company I work at we have a Excel spreadsheet to keep track of the purchases the company made in the last year. This sheet has aproximately 1500 rows and 30 columns.

A few days ago filtering in this sheet has started to become very slow. It takes around 5 seconds to filter any column for any keyword. Which is pretty long because this sheet gets filtered a lot and it gets annoying.
The sheet doesn't have any formulas or references to other sheets or files.
There are two macros running on cell changes, but disabling both macros doesn't make it any faster.

I also tried to delete every row except one, but applying the filter to the remaining row still takes 5 seconds to complete.

Does anyone have a idea of why this could be so slow?

microsoft-excel microsoft-excel-2013

share|improve this question

asked Sep 21 '16 at 14:45

miusmius

35114

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Copy the data only over to new workbook and see if the problem still exists.
  
  – CharlieRB
  Sep 21 '16 at 15:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How much RAM do you have? how fast is your CPU? Defragment your hard drive. Open it from a thumb drive to eliminate the hard drive or not.
  
  – cybernard
  Sep 22 '16 at 4:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Okay I tried to copy the data to a new workbook and something interesting happened. When I copy the data by selecting it with CTRL+A the problem remains and filtering is very slow. If however I copy the data by manually selecting all the rows, it's as fast as it should be again. I guess this fixes my problem, but does anyone know why this could happen? Thanks anyways
  
  – mius
  Sep 22 '16 at 5:52
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Very interesting indeed. Try this on for size. Copy the file from it's folder and paste it as a copy. I'm interested in that test. Also, where is your file located? C-drive? USB thumb drive? Server? NAS? Also has meaning.
  
  – ejbytes
  Sep 22 '16 at 9:19
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Moving the file doesn't change anything. Originally the file is located on a server in the company, but moving it to the local ssd of my computer doesn't change anything. I also tried it on multiple computers with different hard drives.
  
  – mius
  Sep 22 '16 at 9:39
  

  
  
  
  

add a comment | 

5

1

At the company I work at we have a Excel spreadsheet to keep track of the purchases the company made in the last year. This sheet has aproximately 1500 rows and 30 columns.

A few days ago filtering in this sheet has started to become very slow. It takes around 5 seconds to filter any column for any keyword. Which is pretty long because this sheet gets filtered a lot and it gets annoying.
The sheet doesn't have any formulas or references to other sheets or files.
There are two macros running on cell changes, but disabling both macros doesn't make it any faster.

I also tried to delete every row except one, but applying the filter to the remaining row still takes 5 seconds to complete.

Does anyone have a idea of why this could be so slow?

microsoft-excel microsoft-excel-2013

share|improve this question

asked Sep 21 '16 at 14:45

miusmius

35114

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Copy the data only over to new workbook and see if the problem still exists.
  
  – CharlieRB
  Sep 21 '16 at 15:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How much RAM do you have? how fast is your CPU? Defragment your hard drive. Open it from a thumb drive to eliminate the hard drive or not.
  
  – cybernard
  Sep 22 '16 at 4:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Okay I tried to copy the data to a new workbook and something interesting happened. When I copy the data by selecting it with CTRL+A the problem remains and filtering is very slow. If however I copy the data by manually selecting all the rows, it's as fast as it should be again. I guess this fixes my problem, but does anyone know why this could happen? Thanks anyways
  
  – mius
  Sep 22 '16 at 5:52
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Very interesting indeed. Try this on for size. Copy the file from it's folder and paste it as a copy. I'm interested in that test. Also, where is your file located? C-drive? USB thumb drive? Server? NAS? Also has meaning.
  
  – ejbytes
  Sep 22 '16 at 9:19
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Moving the file doesn't change anything. Originally the file is located on a server in the company, but moving it to the local ssd of my computer doesn't change anything. I also tried it on multiple computers with different hard drives.
  
  – mius
  Sep 22 '16 at 9:39
  

  
  
  
  

add a comment | 

At the company I work at we have a Excel spreadsheet to keep track of the purchases the company made in the last year. This sheet has aproximately 1500 rows and 30 columns.

A few days ago filtering in this sheet has started to become very slow. It takes around 5 seconds to filter any column for any keyword. Which is pretty long because this sheet gets filtered a lot and it gets annoying.
The sheet doesn't have any formulas or references to other sheets or files.
There are two macros running on cell changes, but disabling both macros doesn't make it any faster.

I also tried to delete every row except one, but applying the filter to the remaining row still takes 5 seconds to complete.

Does anyone have a idea of why this could be so slow?

microsoft-excel microsoft-excel-2013

share|improve this question

asked Sep 21 '16 at 14:45

miusmius

35114

share|improve this question

asked Sep 21 '16 at 14:45

miusmius

35114

share|improve this question

asked Sep 21 '16 at 14:45

miusmius

35114

asked Sep 21 '16 at 14:45

miusmius

35114

asked Sep 21 '16 at 14:45

miusmius

35114

4 Answers
4

active

oldest

votes

6

This usually happens because Excel thinks the data set is much larger than it actually is. For instance, if someone had navigated to cell A1048576 and formatted it, even though it might not look different, now Excel thinks you have over a million rows. Test this by selecting cell A1 and then hitting CTRL + End. This will take you to what Excel believes is the last cell in the worksheet. If this is the issue you can delete all the rows and/or columns after where your actual data ends, save the file, close it, and reopen it. This should stop Excel from seeing all those empty cells as part of the data. If this doesn't work, selecting your data and pasting it into a new file should fix it.

share|improve this answer

edited Aug 28 '18 at 16:39

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

add a comment | 

0

Step1 :Select All (Ctrl+A)

Step2: Go to Home>Clear> Clear Formats.

Step3: Save file and re-apply filter.

You're done. It works for me every time; please let me know if it works for you!

share|improve this answer

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

add a comment | 

0

It's probably too late, but turn off your automatic calculations. In VBA, I use these two turn off the automatic calcs, apply filters, then turn back on auto calcs.

Sub automatic_update_on()



   Application.Calculation = xlAutomatic



End Sub



Sub automatic_update_off()



   Application.Calculation = xlManual



End Sub

share|improve this answer

answered Jul 6 '18 at 16:43

IorekIorek

1

add a comment | 

-1

In my case it helped to remove all comments. Filter time came down from 1.5 minutes to less than a second for a table with 100,000+ entries.

share|improve this answer

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1126745%2fexcel-filtering-very-slow%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

6

This usually happens because Excel thinks the data set is much larger than it actually is. For instance, if someone had navigated to cell A1048576 and formatted it, even though it might not look different, now Excel thinks you have over a million rows. Test this by selecting cell A1 and then hitting CTRL + End. This will take you to what Excel believes is the last cell in the worksheet. If this is the issue you can delete all the rows and/or columns after where your actual data ends, save the file, close it, and reopen it. This should stop Excel from seeing all those empty cells as part of the data. If this doesn't work, selecting your data and pasting it into a new file should fix it.

share|improve this answer

edited Aug 28 '18 at 16:39

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

add a comment | 

6

This usually happens because Excel thinks the data set is much larger than it actually is. For instance, if someone had navigated to cell A1048576 and formatted it, even though it might not look different, now Excel thinks you have over a million rows. Test this by selecting cell A1 and then hitting CTRL + End. This will take you to what Excel believes is the last cell in the worksheet. If this is the issue you can delete all the rows and/or columns after where your actual data ends, save the file, close it, and reopen it. This should stop Excel from seeing all those empty cells as part of the data. If this doesn't work, selecting your data and pasting it into a new file should fix it.

share|improve this answer

edited Aug 28 '18 at 16:39

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

add a comment | 

This usually happens because Excel thinks the data set is much larger than it actually is. For instance, if someone had navigated to cell A1048576 and formatted it, even though it might not look different, now Excel thinks you have over a million rows. Test this by selecting cell A1 and then hitting CTRL + End. This will take you to what Excel believes is the last cell in the worksheet. If this is the issue you can delete all the rows and/or columns after where your actual data ends, save the file, close it, and reopen it. This should stop Excel from seeing all those empty cells as part of the data. If this doesn't work, selecting your data and pasting it into a new file should fix it.

share|improve this answer

edited Aug 28 '18 at 16:39

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

share|improve this answer

edited Aug 28 '18 at 16:39

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

answered Oct 25 '16 at 17:32

user2597747user2597747

1315

0

Step1 :Select All (Ctrl+A)

Step2: Go to Home>Clear> Clear Formats.

Step3: Save file and re-apply filter.

You're done. It works for me every time; please let me know if it works for you!

share|improve this answer

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

add a comment | 

0

Step1 :Select All (Ctrl+A)

Step2: Go to Home>Clear> Clear Formats.

Step3: Save file and re-apply filter.

You're done. It works for me every time; please let me know if it works for you!

share|improve this answer

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

add a comment | 

Step1 :Select All (Ctrl+A)

Step2: Go to Home>Clear> Clear Formats.

Step3: Save file and re-apply filter.

You're done. It works for me every time; please let me know if it works for you!

share|improve this answer

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

share|improve this answer

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

edited Jun 28 '18 at 11:14

LudvigH

227110

edited Jun 28 '18 at 11:14

LudvigH

227110

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

answered Dec 22 '17 at 4:37

Rakesh DhedhiRakesh Dhedhi

1

0

It's probably too late, but turn off your automatic calculations. In VBA, I use these two turn off the automatic calcs, apply filters, then turn back on auto calcs.

Sub automatic_update_on()



   Application.Calculation = xlAutomatic



End Sub



Sub automatic_update_off()



   Application.Calculation = xlManual



End Sub

share|improve this answer

answered Jul 6 '18 at 16:43

IorekIorek

1

add a comment | 

0

It's probably too late, but turn off your automatic calculations. In VBA, I use these two turn off the automatic calcs, apply filters, then turn back on auto calcs.

Sub automatic_update_on()



   Application.Calculation = xlAutomatic



End Sub



Sub automatic_update_off()



   Application.Calculation = xlManual



End Sub

share|improve this answer

answered Jul 6 '18 at 16:43

IorekIorek

1

add a comment | 

It's probably too late, but turn off your automatic calculations. In VBA, I use these two turn off the automatic calcs, apply filters, then turn back on auto calcs.

Sub automatic_update_on()



   Application.Calculation = xlAutomatic



End Sub



Sub automatic_update_off()



   Application.Calculation = xlManual



End Sub

share|improve this answer

answered Jul 6 '18 at 16:43

IorekIorek

1

Sub automatic_update_on()



   Application.Calculation = xlAutomatic



End Sub



Sub automatic_update_off()



   Application.Calculation = xlManual



End Sub

share|improve this answer

answered Jul 6 '18 at 16:43

IorekIorek

1

answered Jul 6 '18 at 16:43

IorekIorek

1

answered Jul 6 '18 at 16:43

IorekIorek

1

-1

In my case it helped to remove all comments. Filter time came down from 1.5 minutes to less than a second for a table with 100,000+ entries.

share|improve this answer

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

-1

In my case it helped to remove all comments. Filter time came down from 1.5 minutes to less than a second for a table with 100,000+ entries.

share|improve this answer

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

In my case it helped to remove all comments. Filter time came down from 1.5 minutes to less than a second for a table with 100,000+ entries.

share|improve this answer

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this answer

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 18 hours ago

Martin BemmannMartin Bemmann

1

New contributor

Martin Bemmann is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 18 hours ago

Martin BemmannMartin Bemmann

1