If you have actually ever opened a Microsoft Excel workbook to find no columns, rows and/or scrollbars, this is probably why: The workbook’s author concealed some part of the Excel worksheet from view so users can concentrate on the workspace without interruptions. When it looks like whatever is missing out on, it’s often due to the fact that the owner of the document has disabled properties and choices to secure the working area. SEE: The Complete Microsoft Workplace Master Class Bundle(TechRepublic Academy)In this tutorial, I’ll reveal you how to hinder a number of worksheet residential or commercial properties and choices so user focus remains on the workspace. This procedure is easy to carry out and takes really little time. I’m utilizing Microsoft 365 Desktop on a Windows 10 64-bit system, but you can likewise use older variations. Excel’s online variation lets you switch off gridlines and the heading rows. You can download the Microsoft Excel demo file for this tutorial. Jump to: Why conceal unused locations in Excel? You generally conceal a column or row to conceal or secure data and formulas, so you may be wondering why anyone would wish to hide everything else. The factor? Concealing whatever but the workspace is an excellent way to obscure data and formulas you don’t
want usersto see or try to change. Another good factor to hide unused locations is to make your
worksheet function likewise to dashboards, which are
growing in popular usage. Viewers, or end consumers, might click around to concentrate on something in the sheet or to filter a report or chart like they would in a control panel, however they won’t be able to make changes to the underlying data. When you’re trying to make your Excel worksheet function like a control panel, you won’t want to see a lot of Excel’s conventional sheet aspects. SEE: 30 Excel tips you need to understand(TechRepublic Premium )Whether you’re protecting information or eliminating interruptions, concealing white area and other areas of the worksheet can help. However, concealing parts of the worksheet includes one intrinsic behavior that’s difficult to work around: Concealing rows and columns displaces the workspace. For example, if you conceal 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 usage case, but in case it does, we’ll cover a 2nd method that permits you to focus the work area by switching off the gridlines. Bear in mind that many of the tips we’re covering today, consisting of the hidden gridlines pointer, really involve inhibiting the screen of some sheet homes and turning things off rather than truly concealing anything. Concealing columns and rows in Excel Concealing unused columns and
rows within the sheet is a great way to keep users from exploiting the space and/or keep them concentrated on pertinent info. It’s likewise a great method to spruce up a control panel so it looks professional and total. SEE: The very best keyboard faster ways for rows and columns in Microsoft Excel(TechRepublic)To show, we’ll utilize the sample worksheet displayed in Figure A, which has a little workspace and a lot of lost space– unused locations that may tempt a user to wander around. Figure A Hide the unused rows above the workspace.
To hide unused rows, take the following steps: 1. Click any cell in the very first unused row above the workspace and press Shift+Spacebar to pick that row. If you’re dealing with the demonstration file, click a cell inside row 1. 2. Press Ctrl +Shift+ Down Arrow to pick every row between the selected row and the bottom of the sheet. 3. If Excel chooses the header row( row 6), hold down the Shift secret and press the Up
Arrow to eliminate 
row 6 from the choice. 4. Click the Home tab.
5. In the Cells group, click the Format dropdown and pick Hide & Unhide. Then, choose Hide Rows (Figure A)or right-click the selection and choose Conceal from the resulting submenu. You could also just push Ctrl+9. Hiding the unused rows above the workspace moves the workspace to the top of the sheet, as displayed in Figure B. This is the displacement problem I discussed previously.
Figure B Excel conceals the rows above the work area. Repeat the actions above to pick all the rows listed below the workspace. Begin by clicking a cell in row 8. Figure C reveals the outcomes.
Figure C Conceal the unused rows below the work area. Now it’s time to conceal 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 pick the whole column. 3. Press Ctrl+ Shift+Down to add columns B and C to the choice. 4. If Excel chooses the very first column in the work area
, hold down the 
Shift key and press the Left Arrow
secret to eliminate it from the selection. 5. In the Cells group, click the Format dropdown and select Hide & Unhide, and after that select Conceal Columns. You can also right-click the choice and choose
Hide from the resulting submenu or merely press Ctrl+ 0. Repeat the process
above by very first clicking any cell in column I. Figure D reveals the results. As you can see, the workspace is now in the top-left corner
of the screen. You can’t quickly access the covert rows and columns if you choose to make modifications. Figure D Hiding rows and columns displaces the work area. 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 rapidly 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 require, you can seemingly hide empty rows and columns by hindering other sheet elements, such as gridlines. To prevent 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 Uncheck Gridlines to prevent them. The gridlines are gone. This basic visual change will help the viewer move directly to the work area and remain there. Admittedly, it’s still white area, however the absence of the gridlines is an excellent start.
However,
the view still shows a couple of other sheet
components that you might want to inhibit. Next, let’s look at hiding header rows and the formula bar. In spite of the lack of gridlines, the window still looks like an Excel sheet. Preventing 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 shut off both header rows and the formula bar the same method you did the gridlines. Click the View tab and then uncheck Formula Bar and Headings in the Show group. Figure F reveals the outcomes.
Figure F< img src= "https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/12/tr-12122-ExcelHideEverything_F-770x300.jpg "alt ="Shut off the header rows and the formula bar. "width="770" height="300"
/ > 
Turn off the header rows and the formula bar.
At this point, users with limited Excel skills most likely will not make any effort to roam beyond the working area.
You can still pick the header cells although you can’t see them. If you want to include or erase columns or rows, you still can. In Figure F, you can see that I inserted a column and a number of rows to better center the workspace.
For much 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 shut off. For that reason, you might not want to turn it off, especially if users will not know how to turn it back on.
Concealing the sheet tabs in Excel
The sheet tabs provide fast access to other sheets within the same Excel file. If you don’t want to see them, you can inhibit these sheet tabs also. To switch off the display of sheet tabs, follow these actions:
1. Click the File tab.
2. In the left pane, click Options.
3. In the left pane, click Advanced.
4. In Display Alternatives For This Workbook, uncheck the very first three options (Figure G). You might also turn off the scroll bars while you’re at it, too.
Figure G
Shut off the scroll bars and the sheet tabs. 5. Click OK. You can toggle the ribbon, but more than likely, the file will open with the ribbon exposed. This is another application-level setting that you actually can’t manage from one use to another.
At this point, you’re done. Figure H reveals a basic sheet with few interruptions.
Figure H
Users will go directly to the workspace. Restoring your initial display You’ve made a great deal of changes, but all of them are simple to execute and to reset. You can finish this whole reset in under 5 minutes. To restore the display screen, simply repeat the instructions noted above in reverse.
Moving toward Excel’s protection function
If you choose that you need to hinder the Solution bar and the ribbon, you need to use the WorkBook_Open() Sub treatment. 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 have actually done throughout this tutorial is a simple bit of impression. Sometimes that will suffice, and frankly, it looks great. Nevertheless, absolutely nothing in this short article secures any of the sheet’s cells. For that, you’ll want to utilize Excel’s protection function.
To find out more about Excel security, checked out the following short articles:
Do you have other Microsoft questions or functions that you wish to discover more about? TechRepublic has thousands of Microsoft tutorials and resources readily available to help you take advantage of your Microsoft technologies. We likewise offer a range of Microsoft education programs and certifications through TechRepublic Academy.
