Bottom Line: Learn how to fix an issue with using whole column references with Excel Tables that makes them slow to respond.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
If you'd like a copy of the file I use in the video, you can download it here:
Fixing Slow Response with Excel Tables
If you are working with an Excel Table and you notice a lag when you are scrolling up and down or when you move from sheet to sheet within the workbook, your problem may be whole column references. What is a whole column reference? I'll show you as I walk you through a mistake I made the other day when creating a table.
As I explain, I'll punctuate my story with some Excel tips (★) that I'm sure you'll find useful. Here goes:
Once upon a time, I was creating a table for my Excel Campus friends to enjoy. I copied some data from another sheet and pasted it into a new sheet. It was a relatively simple data set: 7 columns wide by 250 rows deep, with no formulas. It looked like this:
The first thing I wanted to do was make my columns bigger so that I could read the data. To do this I used a trick for expanding multiple columns at the same time.
★ Excel Tip: Autofit Multiple Columns Simultaneously
If you've ever spent time adjusting each column width manually by dragging the column border left or right, then you'll appreciate this shortcut.
Excel has an autofit feature which expands your column to the exact length of the widest text in that column. You simply have to double-click on the column border (the gridline separating it from the next column) within the column's header. As you hover over that border line, your cursor changes to a line with two arrows pointing left and right.
To autofit multiple columns simultaneously, you simply need to select multiple columns before double-clicking on any of the column borders within your selection.
To select multiple columns you can click on a column header and drag your cursor across multiple column headers. Or you can click on a column header and then hold down Shift or Ctrl while selecting more column headers. (Shift for contiguous columns, Ctrl for non-contiguous columns.) You'll know the columns are selected because the color of the header is different.
Once your desired columns are selected, just double-click on any one of the column borders to auto-expand all of them.
So I autofit all seven columns simultaneously and moved on to my next step, which was to turn the data into an Excel table.
★ Excel Tip: Create an Excel Table Out of Raw Data
Making a table is super easy. When you have a block of data of any size, you can simply click on any cell in the data and then go to Format as Table on the Home tab. That gives you a wide selection of table formats and color schemes to choose from.
There are tons of benefits to using Excel Tables, and I outline several of them in this post: Excel Tables Tutorial Video – Beginners Guide.
I chose a light gray color style and it instantly converted my data into a table. But here is where I made my mistake. Instead of clicking on a cell anywhere in my data set before beginning, I started making my table while those 7 columns were still highlighted. Therefore my table was referencing the entire columns instead of just the cells that contained values.
The mistake isn't obvious to detect just by looking at the table. But I began to notice that when I started to scroll down, the scrolling was choppy and delayed. There was lag. This was true using the scroll wheel on the mouse as well as the with the cursor on the scroll bar. There was also a slight delay when I moved between sheets in my workbook.
★ Excel Tip: A Small Vertical Scroll Bar is a Red Flag
In addition to the lag time, one thing that might clue you in to a situation like this is a small vertical scroll bar. The more rows that you have in your data, the smaller the scroll bar appears. You can see what I mean in this comparison image.
When you notice that your scroll bar is small like that, you may have a stray cell with a value or character in it way down in your sheet somewhere that can cause some performance issues. Keep in mind, however, when you save the file, the scroll bar will appear normally again.
Fixing the Issue
Once you've determined that whole column references are slowing performance for your worksheet, fixing it is pretty simple. Start by clicking anywhere in the data set. Then select the Resize Table option on the Table Design tab. That will open a Resize Table window. You can see that it is currently referencing whole columns.
Simply change the referenced range to be the block of used cells instead of the entire columns.
★ Excel Tip: Quickly Select an Entire Block of Data
To select all of the cells in a used range, start by selecting the top leftmost cell in the range and then use the keyboard shortcut Ctrl + Shift + End.
With your table resized to include only the used cells instead of the entire columns, you'll notice improved performance right away. The lag disappears, scrolling is smooth, and you can quickly jump from sheet to sheet.
So, when creating tables, be sure to start by clicking on a single cell anywhere within your data set. Then Excel will automatically detect the used range. I hope that my mistake has been a useful learning experience for you! If you have any questions or comments, feel free to voice them below.
Use CTRL-T to create the table then do the autofit appears to work as well
Thanks Charlie! Yes, that will work. However, if you select the columns before pressing Ctrl+T, then the whole column references will still be used for the Table. Unfortunately, Excel does not recognize the used range when whole columns are selected. At least not yet.
If you select one cell of the data and press CTRL+T, then this will work fine I think.
I think if you select a single range in a proper data set when the Create Table pop up opens it identifies the correct data range. I am running MS Office Home and Student 2016.
Hi Jon,excellent tip, thanks. I use tables often and I wanted to use the unique formula inside a table but I have the spill error all the time. Any suggestions? Have a good one.
Great question! Tables do not handle spill ranges. At least not yet. One possible workaround is to join all of the cells in the spill range into a single cell and separate each value by a character, like a comma.
You can use the TEXTJOIN function for this. You will wrap the UNIQUE formula within TEXTJOIN.
Something like the following.
We’ll add this technique to our list for future posts. I hope that helps. Thanks again and have a nice day!
I have known this problem for long. It also increases the size of the corresponding file, that’s how I discovered it. If, for some reason, do not want your data to be arranged in a table, the solution is to select all the lines after the last line used and to delete all these lines. New clean lines are created that suppress the whole columns reference, the scroll bar increases, the file size is reduced and everyting goes better.
Thanks for sharing your knowledge!
Thanks for sharing the alternate solution!
Hello I always enjoy your videos, thank you. Is it possible to revert from table to normal? If yes could you kindly tell how? Trish
Convert to Range, then Insert Table.
This fixes any issues with a Table.
Jon – thanks as always for your tips! I frequently work with data exported from other applications and have noticed an error that I can’t figure out. If the data has merged cells, I convert it to a table (using your tips and making sure to avoid whole column) and all is well. I write a power query and all is still well. I re-open the file several days later and there is an error about not being able to create part of the table. Excel does a repair and sure enough the table is gone but the data is there. I recreate the table and all is well. With data from other sources without a merged cell, I don’t have that issue. The issue is typically with merged cells in header of the data which I copy into the second row of the table to keep first row of table as column 1, column 2 etc. any idea as to why excel has to repair tables with merged cells? It could be that graphs in the data are also causing the issue. Do I need vba to remove all merged cells or is there any power query way to remove merged cells?
Every thing is great. I’m disabled and can not use keyboard shortcuts that have 3 keys to press. Can you tell where to use regular places to find, either in your descriptions or sent e-mail where to find
Thank You Don
So much appreciated for the Excel Campus Team.
God bless you all.
Re your tip “★ Excel Tip: A Small Vertical Scroll Bar is a Red Flag”. There seem s to be no “resize” option on Excel365 on iMac.
i have a large spread sheet with formulas down to 5000 rows and approx 100 columns, it is not a table, with data to approx 1200 rows. the scroll bar is small and very touchy, ie, it scrolls in large chunks. i cannot find the presence of any data in the other than the 1200 rows.
This worked! You totally made my day better! Thank you!