Does the 'List' tool in Excel 2003 slow down the file?

**AnnK** · 10-27-2010, 05:11 PM

I'm wondering if anyone has any experience with the list tool in Excel? I'm talking about the one from the Data menu, where you choose list and convert from a regular range to a list. This ends up giving you the autosort arrows on each column (which you can add without using the list tool). The only other discernable difference is you get a pretty blue box around the data.

My workgroup has been complaining that a file we all use, that right now has about 25000 rows and 25 columns is very slow to open. I'm wondering if the unnecessary use of the list tool might be a contributing factor. (Can you tell it wasn't my idea to use the list?) There really isn't much going on in the file- it's just a data repository, no calculations except each row has one cell that adds up 3 others. So I'm not sure why else it would be working so slowly.

I am getting ready to add some VBA action to this file, which I assume will slow it further...so if anyone has any data on what this list thing is doing resource-wise, I'd be very grateful to hear about it.

**royUK** · 10-28-2010, 02:20 AM

The List feature is much more than adding autofilter automatically & the blue line, e.g. formulas are copied down automatically as data is added. Try reading up on it.

I imagine that what would really be slowing the workbook down is the number of formulas, especially if it uses array formulas.

Properly constructed VBA should not slow the workbook, but be sure that there is no inbuilt Excel feature first, i.e. don't add VBA unless it is necessary

**squiggler47** · 10-28-2010, 03:10 AM

I would think the slow down is more from the size of the data since you have 625000 cells combined with 25000 formulas. I would think lists perhaps slow the sheet down by a very small percentage over just putting them in cells, but removing them wouldn't make much difference. A lot of sheets I wrote for my former employers all used lists, as they negate having to use dynamic ranges and the inbuilt sorting and filtering save a lot of time.

When you start getting to this amount of data, you should be thinking about a database which is better at handling large lists!

And again as Roy said, dont add VBA unless necessary UDF's have a large overhead over a regular function in terms of time!

**AnnK** · 10-28-2010, 11:48 AM

I know lists are made to do more...but we don't need any of the functionality. I'm sure they're great when you are really in need of segregating data within a sheet- but this list contains ALL the data on the sheet, so it is kind of superfluous. I didn't mean to malign the tool, just the use of it for other than it's purpose.

I was concerned that those functionalities would be actively trying to do things while the file was opening. I figure any uneeded extras could be causing the problem. I'll take out some of those formulas and see if that helps. Are there any other things we might have inadvertently doen to slow it down? What about cell formatting? We do have several colors.

I would love to get my users to switch over to Access, but they are very change resistant. The VBA is actually going to be used to carry out actions in Access with the Excel data so they can carry on with the program they are comfortable with, and the rest of us can actually use the data too

**squiggler47** · 10-28-2010, 12:16 PM

i would imagine its just the sheer size of the sheet, rather than the lists themselves, and if you have any formulas in the table I would think it would be more optimized rather than less.