Speed up data-entry tasks by using End Mode
The other day, I was working on a huge worksheet with lots of rows in Excel table columns that I needed to fill out, often with the same text or values. To do this as quickly as possible, I used something called End mode. Many of my colleagues have never heard of End mode, so I thought that you might not know about it either. It’s a handy status bar option, and in my opinion it’s easier to use than fill down when you have a lot of rows of data to change. Let me walk you through the steps for using End mode.
When you right-click the status bar, you’ll see the list of all status bar options. A check mark next to End mode indicates that it is available for use. This is the default setting for End mode in Excel. If there’s no check mark, simply click End mode to activate it.
To use End mode to quickly select the cells you want to change in a column, it helps to first display only those cells. You can do this by filtering the column. For example, to replace cells that contain data, there shouldn’t be any blank cells in the column, and to enter new data in blank cells, there shouldn’t be any cells with data in the column. So before you start, you may want to filter for data you want to change or for just the blank cells. Sometimes, I filter on related data in other columns to display the cells I want to fill out with the new data.
When you have the right cells displayed, simply select the first cell in the column, and then press the End key once to start End mode. End mode should appear in the status bar to show you that it’s turned on. Pressing End turns End mode on and off so it’s helpful to see this indicator there!
When you press Shift+Down Arrow in End mode, the selection quickly extends to the very last cell in the column. Then you can type the text or value you want in the active cell, but instead of pressing Enter, press Ctrl+Enter (another handy shortcut that my colleague Gary just told me about). The same text or value is immediately entered and displayed in all selected column cells. Done!
It’s more likely that you want to fill out columns this way, but you can also use End mode to enter the same data in the cells of a row. In that case, you’d press End and then use Shift+Right Arrow to select all cells in that row. Of course, you won’t be able to filter for specific data the same way you can in a column, and you may have to temporarily hide columns to show only data you want to change.
End mode is also great for finding the last cell in any row or column in a range of worksheet data, the first and last cell in any other non-contiguous ranges, as well as the very last cell in the row or column.
Simply click anywhere in your data, press End once to turn on End mode followed by the Right Arrow or Down Arrow to find the last cell that contains data in the row or column. If you repeat pressing End followed by the arrow key, you’ll find the first and last cell that contains data in other ranges on the worksheet, and eventually the very last cell in the row or column.
Just remember to press End to turn End mode on again because it will automatically turn off after pressing the arrow keys.
My colleagues tell me they’ll be using End mode more often. I use it all the time, and Gary has gotten me used to using Ctrl+Enter. I love it when we learn from each other!
— Frederique