Tanjim Reza
An Excel spreadsheet has 1,048,576 rows and 16384 columns. So, you can realize that it has an enormous number of rows and columns that you won’t always need. In these cases, you will need to set the end of an Excel spreadsheet. Now, if you are looking for this solution, then you have come to the perfect place. In this article, I will describe to you 3 effective ways to set the end of an Excel spreadsheet.
How to Set the End of an Excel Spreadsheet: 3 Effective Ways
Say, you have a dataset of 10 employees’ salaries in an Excel spreadsheet. So the end of the spreadsheet should be the last value of your dataset (C12 cell) or immediately next to the last cell (C13 cell) of your dataset.
Now, you can find the end of your cell by pressing the Ctrl + End button. In the given dataset, if you press the Ctrl + End button, you will find the last cell is C15. Now, you want to set the end of your spreadsheet at the C12 or C13 cell. I have demonstrated 3 effective ways below to accomplish this.
We have used the Office 365 version of Microsoft Excel here. You can follow all of these ways to any version of Excel. If you find any problems regarding versions, please leave a comment below.
1. Clear All Unnecessary Blank Rows and Columns
You can clear all the unnecessary blank rows and columns to set the end of your spreadsheet. Go through the steps below to do this.
📌 Steps:
- At the very beginning, click on the B13 cell.
- Afterward, press Ctrl + Shift + Down Arrow. As a result, all the cells below the B13 cell including it will be selected.
- Now, press the Ctrl + Shift + Right Arrow. It would select all the cells right to the B13 cell.
- Afterward, scroll to the 1st row.
- Subsequently, hold the Ctrl button and click on the D1 cell.
- Now, press Ctrl + Shift + Down Arrow to select all the cells below the D1 cell.
- Subsequently, press the Ctrl + Shift + Right Arrow to select all the cells right to the D1 cell.
- As a result, you have selected all the cells of the spreadsheet right below your dataset.
- Now, go to the Home tab >> Editing group >> Clear tool >> Clear All option.
- Afterward, go to the File tab.
- Subsequently, click on the Save tool from the expanded File tab.
Thus, you will see that you have successfully set the end of the spreadsheet at the immediate next cell to the last cell of your dataset. To understand this, press the Ctrl + End button. You will see the C13 cell is activated.
Read More: How to Limit Columns in Excel
2. Hide Unwanted Rows and Columns
Besides, you can set the end of a spreadsheet by hiding rows or columns. Follow the steps below to accomplish this.
📌 Steps:
- First and foremost, select some row headings below your dataset as much as you want. But, you have to make sure the current last cell’s row header is selected.
- Afterward, click your right mouse button.
- Subsequently, choose the Hide option from the context menu.
As a result, you will see the selected rows are hidden. And, the end of the spreadsheet has been set to the C12 cell.
Note:
Now, if you want to unhide all the rows of your spreadsheet for further use, perform the following tasks.
- Select the upper next and lower next rows of the hidden rows.
- Afterward, right-click on your mouse.
- Subsequently, choose the Unhide option.
Consequently, you will see the hidden rows will appear again.
Read More: How to Limit Number of Rows in Excel
3. Use a VBA Code
Moreover, you can use a VBA code to set the end of an Excel spreadsheet. Go through the steps below to achieve this.
📌 Steps:
- Initially, go to the Developer tab >> Visual Basic tool.
- As a result, the Microsoft Visual Basic for Applications window will open.
- Now, select the Sheet4 option from the VBAProject options.
- At this time, the code window for Sheet4 will appear.
- Subsequently, write the following code in the code window.
Sub SetTheEnd()
ActiveSheet.UsedRange
End Sub
- Afterward, press Ctrl + S to save the code.
- Now, close the visual basic window and go to the File tab from the Excel spreadsheet.
- At this time, choose the Save As option from the expanded File tab.
- As a result, the Excel Save as window will appear.
- Click on the Browse option.
- As a result, the Save As dialogue box will appear.
- Afterward, choose the Save as type: option as .xlsm format.
- Last but not least, click on the Save button.
Consequently, you will see that the last cell of your spreadsheet has moved to the C13 cell.
Download Practice Workbook
You can download and practice from our workbook here for free!
Conclusion
To conclude, I have shown 3 effective ways to set the end of an Excel spreadsheet in this article. You can also learn here practically by practicing with our free workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, feel free to comment here.
And, visit ExcelDemy to find more articles like this. Thank you!
