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

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. Let’s set the end of the sample spreadsheet at the C12 or C13 cell.

Last Cell of the Spreadsheet


Method 1 – Clear All Unnecessary Blank Rows and Columns

Steps:

  • Click on the B13 cell.

Click on the B13 Cell

  • Press Ctrl + Shift + Down Arrow. All the cells below the B13 cell including it will be selected.

Select All Cells Below B13 Cell

  • Press Ctrl + Shift + Right Arrow. It should select all the cells right to the B13 cell.

Select All Cells Right to the B13 Cell

  • Scroll to the 1st row.
  • Hold the Ctrl button and click on the D1 cell.

Select the D1 Cell

  • Press Ctrl + Shift + Down Arrow to select all the cells below the D1 cell.

Select the Cells Below the D1 Cell

  • Press the Ctrl + Shift + Right Arrow to select all the cells right to the D1 cell.

Select the Cells Right to the D1 Cell

  • You have selected all the cells of the spreadsheet right below your dataset.
  • Go to the Home tab and the Editing group.
  • Select the Clear tool and choose the Clear All option.

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

  • Go to the File tab.

Access the File tab

  • Click on Save.

Save Your Spreadsheet to Set the End of an Excel Spreadsheet

  • Press the Ctrl + End button. You will see the C13 cell is activated as the last cell in the file.

New End of an Excel Spreadsheet

Read More: How to Limit Columns in Excel


Method 2 – Hide Unwanted Rows and Columns

Steps:

  • Select some row headings below your dataset as much as you want. You have to make sure the current last cell’s row header is selected.

Select Rows to Set the End of an Excel Spreadsheet

  • Right-click and choose Hide from the context menu.

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

  • You will see the selected rows are hidden. The end of the spreadsheet has been set to the C12 cell.

New End of the Excel Spreadsheet

Note:

If you want to unhide all the rows of your spreadsheet for further use, perform the following tasks.

  • Select any one of the rows surrounding the hidden rows.
  • Right-click and choose the Unhide option.

Choose the Unhide Option to Unhide Rows

  • You will see the hidden rows will appear again.

All Rows Visible Again

Read More: How to Limit Number of Rows in Excel


Method 3 – Use VBA Code

Steps:

  • Go to the Developer tab and select Visual Basic.

Access Visual Basic Tool to Write the Code

  • The Microsoft Visual Basic for Applications window will open.
  • Select the Sheet4 option from the VBAProject options.
  • The code window for Sheet4 will appear.
  • Copy the following code in the code window.
Sub SetTheEnd()
ActiveSheet.UsedRange
End Sub

Write the Code to Set the End of an Excel Spreadsheet

  • Press Ctrl + S to save the code.

Save the Code to Set the End of an Excel Spreadsheet

  • Close the visual basic window and go to the File tab from the Excel spreadsheet.

Access the File Tab

  • Choose the Save As option.

Choose the Save As Option

  • Click on the Browse option.

Browse to Save the Excel File

  • In the Save as type options, choose the .xlsm format.
  • Click on the Save button.

Save the File as .xlsm Format to Enable Macro

  • You will see that the last cell of your spreadsheet has moved to the C13 cell.

New End of an Excel Spreadsheet


Download Practice Workbook


Related Article


<< Go Back to Row and Column Limit | Rows and Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo