In this tutorial, I am going to show you 3 quick tricks on how to show only the working area in excel. Removing the unused cells from the worksheet makes it easier to focus on the dataset that you are working with. It also prevents the users from wandering into unnecessary parts of the document. You may also need to show only the working area to present the data to others. The methods shown in this tutorial are very simple and require minimum amount of time.
Download Practice Workbook
You can download the practice workbook from here.
3 Quick Tricks to Show Only Working Area in Excel
1. Use Excel Page Break Preview to Show Only Working Area
In this method, we have a dataset of the marks of six students. As you can see, our working area only extends up to cell C10. We will change the view style of the excel workbook using Page Break Preview to remove unused cells from the screen and show only the dataset. Let us see how we can do that.
- First, go to the View tab and then to the Workbook Views section.
- Now, under this section, select the option Page Break Preview.
- As a result, excel will reduce the worksheet to your working area or the dataset.
- Next, move the mouse pointer to the corner of the window.
- Here, you will see a Double-Head Arrow. Using this arrow, you can now adjust the working area of the worksheet.
Read More: What Is Page Break View in Excel?
2. Hide Cells Outside Working Area in Excel
In excel we can easily hide entire rows and columns that are outside our working area. But note that, we can not hide or show a single cell. In this method, we will go through the steps to hide unused cells.
- To begin with, select the cell that is immediately below your dataset. In my case, this is cell A11.
- Now, press Ctrl+Shift+Right Arrow and then Ctrl+Shift+Down Arrow.
- After that, all the rows below cell A11 will be selected.
- Note that, the last available row in Excel 365 is 1048576.
- Next, right-click on any of the row headers and select Hide.
- Consequently, excel will hide all the rows below the dataset that we selected.
- Similarly, select the cell immediately to the right of your dataset which is cell D1 in my case.
- Now, press Ctrl+Shift+Down Arrow and Ctrl+Shift+Right Arrow.
- As previously, this operation will select all the columns up to the last available column which is XFD.
- Afterward, right-click on any of the column headers and select Hide.
- Finally, excel will hide all the cells outside of your working area.
Read More: Remove Unused Cells in Excel (8 Easy Ways)
- What Is Page Layout View in Excel? (Detailed Analysis)
- How to Create Different Views for Different Users in Excel
- What Is Datasheet View in Excel?
3. Fill Cells with Grey Colors That Are Beyond Working Area
In this method, we will grey out everything on your worksheet except the working area or the dataset. The Fill Color option in excel is generally used to format cells. But we will use it here to clean up our work area and show the dataset more clearly.
- First, select cell A11 which is immediately below the dataset.
- Next, as I showed earlier, select all the rows below the dataset using the Ctrl+Shift+Right Arrow/Down Arrow.
- Then, navigate to the Home tab and select a Fill Color from the available options.
- Now, all the selected rows will have the fill color.
- Then, click on cell D1 and using the previous methods select all the columns outside of the dataset.
- Here, apply Fill Color to all the selected columns.
- After this, all the cells outside your working area will appear grey colored.
Restrict Scrolling to Working Area in Excel Using VBA Window
The ScrollArea property in excel allows us to set the working area within our worksheet. Although we will use the Visual Basic option, you don’t have to write any code for this method. We will only insert the cell range inside Visual Basic. Let us see how easily we can do that.
- First of all, go to the Developer tab and select Visual Basic.
- Now, click on the sheet that you want to work with and go to the Properties window.
- Note that, you can bring the Properties window by pressing the key F4.
- Next, inside the ScrollArea option, type in the range of cells that you want to keep in the working area.
- Finally, close the Visual Basic window and excel will now restrict the worksheet only to the range that you specified.
I hope that you understood the methods that I showed in this tutorial on how to show only the working area in excel. I have shown multiple methods so that you can use the one that suits your specific situation. Also, download the workbook and practice the methods a few times. Make sure to use the methods in your own datasets. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.