If you ever have one Microsoft Excel workbook not finding columns, rows, and/or scrollbars, this is probably why: the workbook author has hidden part of the Excel worksheet so that users can focus on the work area without distraction. If everything seems to be missing, it’s often because the owner of the document has disabled properties and options to protect the work area.
TO SEE: The Complete Microsoft Office Master Class Bundle (TechRepublic Academy)
In this tutorial, I’ll show you how to freeze various sheet properties and options so that the user’s focus remains on the work area. This process is easy to implement and takes little time. I use Microsoft 365 Desktop on one Windows 10 64-bit system, but you can also use older versions. With the online version of Excel, you can turn off gridlines and header rows.
You can download the Microsoft Excel demo file for this tutorial.
Jump to:
Why hide unused areas in Excel?
Usually you hide a column or row to hide or protect data and formulas, so you might wonder why anyone would want to hide everything else. The reason? Hiding everything but the work area is a good way to hide data and formulas that you don’t want users to see or try to change.
Another good reason to hide unused areas is to make your worksheet work in a similar way to dashboards, which are becoming more and more popular. Viewers, or end users, can click around to focus on something in the sheet or filter a report or chart like in a dashboard, but they can’t make any changes to the underlying data. When you’re trying to make your Excel worksheet function as a dashboard, you don’t want to see many of Excel’s traditional sheet elements.
TO SEE: 30 Excel Tips You Should Know (Tech Republic Premium)
Whether you’re protecting data or removing distractions, hiding white space and other parts of the worksheet can help. However, hiding parts of the worksheet has one inherent behavior that is hard to get around: hiding rows and columns moves the work area. For example, if you hide all unused rows and columns, the work area will move to the upper left corner of the sheet instead of the center.
That may not matter for your particular use case, but if it does, let’s discuss a second method that allows you to center the work area by turning off the gridlines. Keep in mind that many of the tips we’ll cover today, including the hidden gridlines tip, are actually about inhibiting the display of some sheet properties and disabling things rather than actually hiding anything.
Hide columns and rows in Excel
Hiding unused columns and rows in the sheet is a good way to prevent users from taking up space and/or keeping them focused on relevant information. It’s also a great way to spruce up a dashboard to make it look professional and complete.
TO SEE: The best keyboard shortcuts for rows and columns in Microsoft Excel (TechRepublic)
To demonstrate we use the sample worksheet in Image Awhich has a small work area and a lot of wasted space – unused areas that can tempt a user to wander.
Image A
Follow these steps to hide unused rows:
1. Click a cell in the first unused row above the work area and press Shift + Space to select that row. If you are working with the demonstration file, click a cell in row 1.
2. Press Ctrl + Shift + Down Arrow to select each row between the selected row and the bottom of the sheet.
3. If Excel selects the header row (row 6), hold down the Shift key and press the up arrow to remove row 6 from the selection.
4. Click the Home tab.
5. In the Cells group, click the Format drop-down list and choose Hide and Unhide. Then choose Hide Rows (Image A) or right-click the selection and choose Hide from the resulting submenu. You can also just press Ctrl + 9.
Hiding the unused rows above the work area moves the work area to the top of the sheet, as shown in Figure B. This is the displacement problem I mentioned earlier.
Figure B
Repeat the steps above to select all rows below the work area. Start by clicking a cell in row 8. Figure C shows the results.
Figure C
Now it’s time to hide all unused columns:
1. Click any cell in column A.
2. Press Ctrl + Down Arrow to select the entire column or click the header cell to select the entire column.
3. Press Ctrl + Shift + Down to add columns B and C to the selection.
4. When Excel selects the first column in the work area, hold down the Shift key and press the left arrow to remove it from the selection.
5. In the Cells group, click the Format drop-down list and choose Hide & Unhide, then choose Hide Columns. You can also right-click the selection and choose Hide from the resulting submenu or just press Ctrl + 0.
Repeat the above process by clicking any cell in column I first. Figure D shows the results. As you can see, the work area is now in the top left corner of the screen. You cannot easily access the hidden rows and columns if you choose to make changes.
Figure D
To unhide all columns and rows in the sheet, click the sheet selector at the intersection of the row and column header cells. Doing so will select the entire sheet. Press Shift + Ctrl + 9 and Shift + Ctrl + 0 to quickly unhide everything.
How to brake columns and rows in Excel
If the move that happened in our previous examples doesn’t work for what you need, you can hide seemingly empty rows and columns by blocking other sheet elements, such as gridlines.
To freeze the display of the gridlines in Excel, please do as follows:
1. Click the View menu.
2. In the Show group, uncheck Gridlines (Figures E).
Figures E
The grid lines are gone. This simple visual change helps the viewer go straight to the stage and stay there. Granted, it’s still white space, but the absence of the gridlines is a good start. However, the view still displays some other sheet elements that you may want to block. Next, let’s look at hiding header rows and the formula bar.
Despite the absence of gridlines, the window still looks like an Excel sheet. Blocking the display of the header rows and formula bar dilutes the “this is an excel sheet, wander around and do what you want” mentality and keeps users in the work area.
For this part of our tutorial, disable both header rows and the formula bar in the same way you disable the gridlines. Click the View tab and uncheck Formula Bar and Headings in the Show group. Figure F shows the results.
Figure F
At this point, users with limited Excel skills probably won’t bother wandering outside the work area.
You can still select the header cells, even though you can’t see them. If you want to add or remove columns or rows, you can still do so. In Figure Fyou can see that I have inserted a column and a few rows to better center the work area.
For better or worse, the formula bar is an application level feature. The next Excel file you or your users open will do so with the formula bar disabled. For that reason, you may not want to disable it, especially if users don’t know how to turn it back on.
Hide the sheet tabs in Excel
The sheet tabs provide quick access to other sheets within the same Excel document. If you don’t want to see them, you can also block these sheet tabs. Follow these steps to disable the display of sheet tabs:
1. Click the File tab.
2. In the left pane, click Options.
3. In the left pane, click Advanced.
4. In Display options for this workbook, uncheck the first three options (Figure G). You might as well disable the scrollbars while you’re at it.
Figure G
5. Click OK.
You can switch the ribbon, but most likely the file will open with the ribbon visible. This is another application level setting that you really can’t control from one use to another.
At this point you are done. Figure H shows a simple sheet with few distractions.
Figure H
Restore your original view
You’ve made a lot of changes, but they’re all easy to implement and reset. You can complete this full reset in less than five minutes. To restore the display, simply repeat the above instructions in reverse order.
Towards the security feature of Excel
If you decide that you need to freeze the formula bar and ribbon, you must use the WorkBook_Open() subprocedure. This procedure runs the code when the user opens the workbook. You can then enable them again using the Before_Close() Sub procedure.
What we’ve done in this tutorial is a simple piece of illusion. Sometimes that’s enough, and frankly, it looks pretty. However, nothing in this article protects the cells of the worksheet. For that, you want to use Excel’s security feature.
For more information about Excel security, read the following articles:
Do you have other Microsoft questions or features you want to know more about? TechRepublic has thousands of them Microsoft tutorials and resources available to help you get the most out of your Microsoft technologies. We also offer a variety Microsoft training programs and certifications through the TechRepublic Academy.
Cool. I spent a long time looking for relevant content and found that your article gave me new ideas, which is very helpful for my research. I think my thesis can be completed more smoothly. Thank you.