If you’ve ever opened a Microsoft Excel workbook to find no columns, rows and/or scrollbars, this is probably why: The workbook’s author hid some portion of the Excel worksheet from view so users can focus on the working area without distractions. When it looks like everything is missing, it’s often because the owner of the document has disabled properties and options to protect the working area.
SEE: The Complete Microsoft Office Master Class Bundle (TechRepublic Academy)
In this tutorial, I’ll show you how to inhibit several worksheet properties and options so user focus stays on the working area. This process is easy to implement and takes very little time. I’m using Microsoft 365 Desktop on a Windows 10 64-bit system, but you can also use older versions. Excel’s online version lets you turn off gridlines and the heading rows.
You can download the Microsoft Excel demo file for this tutorial.
Jump to:
Why hide unused areas in Excel?
You usually hide a column or row to conceal or protect data and formulas, so you might be wondering why anyone would want to hide everything else. The reason? Hiding everything but the working area is a good way to obscure data and formulas you don’t want users to see or try to change.
Another good reason to hide unused areas is to make your worksheet function similarly to dashboards, which are growing in popular use. Viewers, or end consumers, might click around to focus on something in the sheet or to filter a report or graph like they would in a dashboard, but they won’t be able to make changes to the underlying data. When you’re trying to make your Excel worksheet function like a dashboard, you won’t want to see many of Excel’s traditional sheet elements.
SEE: 30 Excel tips you need to know (TechRepublic Premium)
Whether you’re protecting data or removing distractions, hiding white space and other areas of the worksheet can help. However, hiding parts of the worksheet comes with one inherent behavior that’s difficult to work around: Hiding rows and columns displaces the work area. For instance, if you hide all unused rows and columns, the work area will end up in the top-left corner of the sheet rather than the middle.
That might not matter for your particular use case, but in case it does, we’ll cover 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’re covering today, including the hidden gridlines tip, actually involve inhibiting the display of some sheet properties and turning things off rather than truly hiding anything.
Hiding columns and rows in Excel
Hiding unused columns and rows within the sheet is a good way to keep users from exploiting the space and/or keep them focused on relevant information. It’s also a great way to spiff up a dashboard so it looks professional and complete.
SEE: The best keyboard shortcuts for rows and columns in Microsoft Excel (TechRepublic)
To demonstrate, we’ll use the sample worksheet shown in Figure A, which has a small working area and a lot of wasted space — unused areas that might tempt a user to wander around.
Figure A
To hide unused rows, take the following steps:
1. Click any cell in the first unused row above the work area and press Shift + Spacebar to select that row. If you’re working with the demonstration file, click a cell inside row 1.
2. Press Ctrl + Shift + Down Arrow to select every 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 dropdown and choose Hide & Unhide. Then, choose Hide Rows (Figure A) or right-click the selection and choose Hide from the resulting submenu. You could also simply 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 issue I mentioned earlier.
Figure B
Repeat the steps above to select all the rows below the work area. Begin by clicking a cell in row 8. Figure C shows the results.
Figure C
Now it’s time to hide all of the 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. If Excel selects the first column in the work area, hold down the Shift key and press the Left Arrow key to remove it from the selection.
5. In the Cells group, click the Format dropdown and choose Hide & Unhide, and then choose Hide Columns. You can also right-click the selection and choose Hide from the resulting submenu or simply press Ctrl + 0.
Repeat the process above by first clicking any cell in column I. Figure D shows the results. As you can see, the work area is now in the top-left corner of the screen. You can’t 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 inhibit columns and rows in Excel
If the displacement that occurred in our previous examples won’t work for what you need, you can seemingly hide empty rows and columns by inhibiting other sheet elements, such as gridlines.
To inhibit the view of the gridlines in Excel, do the following:
1. Click the View menu.
2. In the Show group, uncheck Gridlines (Figure E).
Figure E
The gridlines are gone. This simple visual change will help the viewer move straight to the work area and stay there. Admittedly, it’s still white space, but the absence of the gridlines is a good start. However, the view still displays a few other sheet elements that you might want to inhibit. 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. Inhibiting the view of the header rows and the formula bar will tone down the “this is an Excel sheet, wander around and do whatever you like” mindset and keep users in the working area.
For this part of our tutorial, you’ll turn off both header rows and the formula bar the same way you did the gridlines. Click the View tab and then 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 make any effort to wander beyond the working area.
You can still select the header cells even though you can’t see them. If you want to add or delete columns or rows, you still can. In Figure F, you can see that I inserted a column and a couple of rows to better center the work area.
For better or worse, the Formula bar is an application-level property. The next Excel file you or your users open will do so with the Formula bar turned off. For that reason, you might not want to turn it off, especially if users won’t know how to turn it back on.
Hiding 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 inhibit these sheet tabs as well. To turn off the display of sheet tabs, follow these steps:
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 turn off the scroll bars while you’re at it, too.
Figure G
5. Click OK.
You can toggle the ribbon, but most likely, the file will open with the ribbon exposed. This is another application-level setting that you really can’t control from one use to another.
At this point, you’re done. Figure H shows a simple sheet with few distractions.
Figure H
Restoring your original display
You’ve made a lot of changes, but all of them are easy to implement and to reset. You can complete this entire reset in under five minutes. To restore the display, simply repeat the instructions listed above in reverse.
Moving toward Excel’s protection feature
If you decide that you must inhibit the Formula bar and the ribbon, you should use the WorkBook_Open() Sub procedure. This procedure will run its code when the user opens the workbook. Then, you can turn them back on using the Before_Close() Sub procedure.
What we’ve done throughout this tutorial is a simple bit of illusion. Sometimes that will be enough, and frankly, it looks nice. However, nothing in this article protects any of the sheet’s cells. For that, you’ll want to use Excel’s protection feature.
To learn more about Excel protection, read the following articles:
Do you have other Microsoft questions or functions that you want to learn more about? TechRepublic has thousands of Microsoft tutorials and resources available to help you make the most of your Microsoft technologies. We also offer a variety of Microsoft education programs and certifications through TechRepublic Academy.
Source link
Leave a Reply