★ Excel Tip: Quickly Select an Entire Block of Data Simply change the referenced range to be the block of used cells instead of the entire columns. You can see that it is currently referencing whole columns.
Then select the Resize Table option on the Table Design tab. Start by clicking anywhere in the data set.
Once you've determined that whole column references are slowing performance for your worksheet, fixing it is pretty simple. Keep in mind, however, when you save the file, the scroll bar will appear normally again. 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. You can see what I mean in this comparison image. The more rows that you have in your data, the smaller the scroll bar appears. In addition to the lag time, one thing that might clue you in to a situation like this is a small vertical scroll bar. ★ Excel Tip: A Small Vertical Scroll Bar is a Red Flag There was also a slight delay when I moved between sheets in my workbook. This was true using the scroll wheel on the mouse as well as the with the cursor on the scroll bar. But I began to notice that when I started to scroll down, the scrolling was choppy and delayed. The mistake isn't obvious to detect just by looking at the table. Therefore my table was referencing the entire columns instead of just the cells that contained values. 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. I chose a light gray color style and it instantly converted my data into a table. There are tons of benefits to using Excel Tables, and I outline several of them in this post: Excel Tables Tutorial Video – Beginners Guide. That gives you a wide selection of table formats and color schemes to choose from. 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. ★ Excel Tip: Create an Excel Table Out of Raw Data So I autofit all seven columns simultaneously and moved on to my next step, which was to turn the data into an Excel table. Once your desired columns are selected, just double-click on any one of the column borders to auto-expand all of them. ( Shift for contiguous columns, Ctrl for non-contiguous columns.) You'll know the columns are selected because the color of the header is different. Or you can click on a column header and then hold down Shift or Ctrl while selecting more column headers. To select multiple columns you can click on a column header and drag your cursor across multiple column headers. To autofit multiple columns simultaneously, you simply need to select multiple columns before double-clicking on any of the column borders within your selection. As you hover over that border line, your cursor changes to a line with two arrows pointing left and right. You simply have to double-click on the column border (the gridline separating it from the next column) within the column's header.
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.Įxcel has an autofit feature which expands your column to the exact length of the widest text in that column. ★ Excel Tip: Autofit Multiple Columns Simultaneously
To do this I used a trick for expanding multiple columns at the same time. The first thing I wanted to do was make my columns bigger so that I could read the data. It was a relatively simple data set: 7 columns wide by 250 rows deep, with no formulas. I copied some data from another sheet and pasted it into a new sheet. Once upon a time, I was creating a table for my Excel Campus friends to enjoy. 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.Īs I explain, I'll punctuate my story with some Excel tips (★) that I'm sure you'll find useful. 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. Table-Performance-Whole-Column-References.xlsx Fixing Slow Response with Excel Tables
#EXCEL SMART SCROLL SLOW DOWNLOAD#
If you'd like a copy of the file I use in the video, you can download it here: Watch on YouTube & Subscribe to our Channel Download the Excel File
0 kommentar(er)
