And one of the common tasks most Excel users have to do is to go to the end of the data in the column (i.e., the last filled cell). While you can quickly go to the end of the column to the last filled cell, or select the entire column till the last filled cell with an easy keyboard shortcut, things can get a bit complicated if you have blank cells in the column. In this tutorial, I will show you a couple of simple methods (including keyboard shortcuts) that you can use to quickly select the end of the column in Excel. The method you choose would depend on how your data is structured, and I’ll make sure to mention the pros and cons of each method that I covered in this tutorial.
Keyboard Shortcut to Select the End of the Column (CONTROL + SHIFT + Arrow Key)
Below, I have a data set where I have the items in column A and I have the expenses made every day in column B, and I want to select all the expense values in column B. If your data set has no blank cells in any of the cells in the column, you can easily select till the end of the column by using the below keyboard shortcut: To use the above keyboard shortcut: With the above keyboard shortcut, Excel would magically start the selection from the first selected cell and extend it till the last filled cell in the column. But life ain’t perfect, and the same can be said for your data in Excel. Often, data in columns have blank cells that can complicate selecting the entire column till the last filled cell.
When there are Blank Cells in the Column
Below I have the same data set but there are some blank cells in column B, and I want to start with cell B2 and select till the end of the data in the column. With this data set, if you try and use the above keyboard shortcut, you would notice that the selection is made only till the cell before the first empty cell. So we need to use the same keyboard shortcut a little differently. Below are the steps to select till the end of the data in a column when you have blank cells in the column: While this is not the most elegant solution, it gets the work done, and if you do not have a lot of blank cells in your column, it could be quite fast. One scenario where this may not be the best method to use is when you have a lot of blank cells (say a blank cell in every other row or after every two or three rows). While the method would still work in such a scenario, it could take a few more seconds (which in my experience is not something most Excel users are willing to give).
Keyboard Shortcut to Select the End of the Column (CONTROL + SHIFT + End)
Below, I have a data set where I want to select all the expense values in column B. Here is another keyboard shortcut that you can use to select the end of the data in a column: Below are the steps to use this keyboard shortcut: The above steps would start the selection from cell B2, and select all the cells till the last used cell. In most cases, the last cell in your column would also be the last used cell, so this keyboard shortcut should work perfectly in selecting the end of the data in the column. And the great thing about this method is that you do not need to worry about blank cells, as it selects all the cells between the first selected cell and the last used cell.
When You Have More Data On the Right
When you use Control + Shift + End, it starts the selection from the cell that you selected (B2 in our example), and extends the selection till the last cell in the used range. So if you have more data on the right of the column, using the above keyboard shortcut would not only select the existing column but also any data that Is to the right, And sometimes, the used range would remain in the memory of Excel even if you have deleted that data. For example, if I enter some value in the cell E20 and then delete it, the used range in excel would be A1:E20 (as E20 is the last used cell in the memory of Excel). So there is a possibility that when you use the above keyboard shortcut, it selects more cells on the right and below the column that we intend to select. In such a scenario, you can use the same keyboard shortcuts with a minor twist: While this is not the most elegant solution, when you get used to it, you will find it faster than manually selecting a column till the end of the data.
Using the Name Box
Name Box allows you to quickly select a range of cells by entering the reference in the name box. You will find the name box on the left of the formula bar, just below the formula bar. Let’s say that I want to select column A (starting from cell A2) till the last filled cell, where I have blank cells in the column (which will make using the keyboard shortcut Control + Shift + Down arrow key challenging). Below are the steps to use the name box to select the entire column A till the end of the data (i.e., till the last filled cell): The above steps would select all the cells in column A till the last filled cell. Note: This method works in Google Sheets as well (you can use the exact same steps)
Using Go To Dialog Box
Just like the Name Box, you can also use the Go To dialog box to quickly select a range of cells by specifying the reference of that range Let’s say I want to select the range B2:B100. Below are the steps to do this using the Go To dialog box There were steps that would instantly select the range that you specified in the Go-To dialog box. So these are some of the shortcut ways you can use to quickly select data in a column till the end of the data. While the methods I’ve covered here have been shown using data in a column, you can use the same methods to select data till the end of the rows as well While you always have the option to do this manually using the mouse, if you need to do this quite often, knowing these shortcuts will significantly improve your efficiency. Other Excel articles you may also like:
7 Easy Ways to Select Multiple Cells in ExcelHow to Select Non-adjacent cells in Excel? (4 Easy Ways)How to Deselect Cells in Excel (An Easy Way)[Quick Tip] How to Select 500 cells/rows in Excel (with a single click)3 Quick Ways to Select Visible Cells in ExcelHow to Select Every Third Row in Excel (or select every Nth Row)How to Quickly Select a Far-off Excel Cell or Range
