|

Excel: Smooth Scrolling for Large Cells

By
September 1, 2021

When I use Excel, most of my cells contain a short bit of text or a number. But I’ve met people who store very large amounts of text in a cell. In particular, I remember a company that would store hundreds of project updates in a single cell. Even though Excel allows 32,767 characters in a cell, it has always been tough to deal with that much data in one cell.

 

Here’s an example. Open a blank workbook and enter the formula =CON CAT(“Word ”&TEXT(SEQUENCE(3276), “0000 ”)) in cell A1. Your goal is to see the text “Word 3276” at the end of the cell.

 

If you use the maximum row height of 409.5 and the maximum column width of 254.9, you’ll only see the first 7,020 characters in the cell, assuming you’re using the Calibri font set to 11-point size. Even with Arial Narrow 6-point, you can only see 20,280 of the characters in the cell. When you reduce the font size to somewhere between 4 and 5 points, all of the characters appear, but who can read a 4-point font?

 

Figure 1 shows a cropped image of a cell containing five paragraphs of text in cell A1 displayed in Calibri 11-point font. Cell A2 contains the first of 32,000 characters in Arial Narrow 4-point. As you can see, a font size that small is effectively unreadable.

 

 

Yet even though it isn’t practical to store 32,767 characters in a cell, Excel lets you do so. And there are people who are typing 10,000 or 15,000 characters in a cell and then making the cell as large as possible in order to display all the text.

 

Perhaps only one person out of 100 is using that much text in a cell. But the people who do this frequently have been vocally lobbying Microsoft through excel.uservoice.com to improve the experience. Microsoft is rolling out its new smooth scrolling feature to Microsoft 365 customers to alleviate some of the pain. With smooth scrolling, you can use the scroll wheel on your mouse or the scroll bars within the program to scroll one line at a time through a cell and to leave the cell with a portion of the text scrolled out of view.

 

(As an aside, some of you may be wondering, “Why are they still using Excel when they can switch to Word for this?” That’s a good question, but since the sign on my office door says “MrExcel” and not “MrWord,” I wholeheartedly embrace those who want to hang on to Excel long after they should have switched to something else.)

 

MOUSE WHEEL SCROLLING

 

If you have a mouse with a scroll wheel, you know that you can scroll up and down your document by rolling the wheel. Just recently, Excel also added the ability to press Ctrl+Shift to make the mouse wheel scroll horizontally.

 

By default, Windows is set up so each “click” of the wheel will scroll three rows or columns. When you have 1,000 rows of tabular data, scrolling three rows at a time is great. But when each row has a height of 409, you can probably only see one or two rows on your screen. When you scroll one click of the wheel, you’ll jump from row 1 to row 4 and completely miss row 3 and the bottom half of row 2.

 

If you open the Control Panel in Windows and search for Scroll, you’ll find a setting called “Change How Far You Scroll With the Mouse Wheel.” This setting has options to scroll “Multiple Lines At A Time” or “One Screen At A Time.” If you choose the multiple lines option, there’s a slider where you can choose from 1 to 100 lines at a time. Changing to one line at a time will help with the scrolling of large cells.

 

SCROLLING PARTWAY

 

Prior to the smooth scrolling feature, if you wanted to scroll part of the way through a large cell, you either needed to invest in a precision scrolling mouse or switch to dragging the scroll bars.

 

But even if you could use the scroll bar to scroll halfway through the cell, there was no way to leave the view there. When you let go of the scroll bar, Excel would always jump so the top of a cell is at the top of the grid. If you start in A1 and scroll less than halfway through the cell, Excel would jump so you see the top of row 1. If you start in A1 and scroll more than halfway through the cell, Excel would jump so you see the top of row 2. Before the new feature, there was no way to let go of the scroll bar and leave Excel where it would display starting a few lines below the top of the cell.

 

The new smooth scrolling feature was released to Microsoft 365 in August 2021. It allows you to scroll one line vertically at a time or one character horizontally. Figure 2 shows the Excel grid starting on line 4 and about 25 characters from the left edge of cell A1.

 

 


0 No Comments
You may also like