How to Set the End of an Excel Spreadsheet (3 Effective Ways)

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.


Download Practice Workbook

You can download and practice from our workbook here for free!


3 Effective Ways to Set the End of an Excel Spreadsheet

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.

Sample dataset to Set the End of an Excel Spreadsheet

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.

Last Cell of the Spreadsheet

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.

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.

Select All Cells Below B13 Cell

  • Now, press the Ctrl + Shift + Right Arrow. It would select all the cells right to the B13 cell.

Select All Cells Right to the B13 Cell

  • Afterward, scroll to the 1st row.
  • Subsequently, hold the Ctrl button and click on the D1 cell.

Select the D1 Cell

  • Now, press Ctrl + Shift + Down Arrow to select all the cells below the D1 cell.

Select the Cells Below the D1 Cell

  • Subsequently, press the Ctrl + Shift + Right Arrow to select all the cells right to the D1 cell.

Select 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.

Clear the Selected Cells to Set the End of an Excel Spreadsheet

  • Afterward, go to the File tab.

Access the File tab

  • Subsequently, click on the Save tool from the expanded File tab.

Save Your Spreadsheet to Set the End of an Excel Spreadsheet

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.

New End of an Excel Spreadsheet


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.

Select Rows to Set the End of an Excel Spreadsheet

  • Afterward, click your right mouse button.
  • Subsequently, choose the Hide option from the context menu.

Choose the Hide Option to Set the End of an Excel Spreadsheet

As a result, you will see the selected rows are hidden. And, the end of the spreadsheet has been set to the C12 cell.

New End of the Excel Spreadsheet

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.

Choose the Unhide Option to Unhide Rows

Consequently, you will see the hidden rows will appear again.

All Rows Visible Again


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.

Access Visual Basic Tool to Write the Code

  • 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

Write the Code to Set the End of an Excel Spreadsheet

  • Afterward, press Ctrl + S to save the code.

Save the Code to Set the End of an Excel Spreadsheet

  • Now, close the visual basic window and go to the File tab from the Excel spreadsheet.

Access the File Tab

  • At this time, choose the Save As option from the expanded File tab.

Choose the Save As Option

  • As a result, the Excel Save as window will appear.
  • Click on the Browse option.

Browse to Save the Excel File

  • 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.

Save the File as .xlsm Format to Enable Macro

Consequently, you will see that the last cell of your spreadsheet has moved to the C13 cell.

New End of an Excel Spreadsheet


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!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo