Excel is such a great tool because it allows you to store, organize, and display vast amounts of data in a clean, crisp format. However, this doesn’t mean that you want to see everything you have in Excel all at once. Sometimes, you may only want to see certain segments of a spreadsheet, while other times it might make it easier to work in Excel without visible formulas.
But just because you don’t want to see all of your data and worksheets simultaneously doesn’t mean that you should delete the unwanted material. Instead, you should take advantage of Excel’s ability to hide and unhide different sections, file types, and values.
So how can you hide specific parts of a single Excel spreadsheet? What about hiding or unhiding entire worksheets? Finally, is there a way to change the visibility of duplicated data? In today’s guide, we will answer all of these questions and much more, but remember, if you’re struggling with Excel, you can always contact the experts at Geeker for Excel help!
Hiding Columns & Rows In Excel
If you know which columns or rows you want to hide, you can go directly to the “Home” tab, then select “Cells” and click on “Format.” In the “Visibility” section, you have the option to hide or unhide rows or columns. This is typically the easiest option, particularly if you remember the letter/number designations for the sections you want to hide. This method allows you to quickly hide or unhide different segments of your spreadsheet in a matter of seconds.
But if you prefer to hide rows and columns directly in your spreadsheet, it’s still very easy. To hide columns manually, just follow these steps:
- Pick the column (or columns) you want to hide
- Select the header that displays the column letter (at the top of the column)
- Right-click on the column header
- From the menu, select “Hide”
Hiding rows is very similar to hiding columns, but you will need to follow these steps instead:
- Pick the row (or rows) you want to hide
- Select the header that displays the row number (to the left of the row)
- Right-click on the row header
- From the menu, select “Hide”
In case you accidentally hide the wrong section or you’re ready to make the row or column visible again, you can follow these steps to unhide your row or column:
- *Select the rows or columns immediately surrounding the hidden row or column by holding CTRL and clicking on the corresponding headers
- Right-click on the selected headers
- From the menu, select “Unhide”
*Note: This option is a little harder, especially if you have a large spreadsheet and you’ve forgotten where the hidden column or row is located. However, you can look through your headers to see where there are any missing letters or numbers. For example, if row 3 is hidden, you will need to select the rows immediately above and below it (rows 2 and 4) and follow the steps above. Similarly, if column B is hidden, you’ll need to select the rows to the left and right of it (columns A and C) and follow the same steps to unhide column B.
Hide An Excel Worksheet
Like hiding or unhiding rows and columns, hiding or unhiding an Excel worksheet is a breeze. Additionally, hiding Excel worksheets can be a good way to protect sensitive data from prying eyes. In any case, you can hide or unhide your worksheet by following these steps:
- Open the Excel workbook with the worksheet you want to hide or unhide
- From the worksheet tabs at the bottom of your window, right-click on the specific worksheet you want to hide or unhide
- In the menu that appears, select “Hide” or “Unhide”
Hide Cell Values In Excel
Hiding cell values in Excel requires a slightly different approach. Rather than simply right-clicking on the section you want to hide, you’ll need to use a custom number format. If you’re unfamiliar with this process, just follow these steps:
- Select the cells with the values you want to hide
- In the “Home” tab, click on the Dialog Box Launcher next to “Number”
- In the “Category” box, choose “Custom”
- Add three semicolons (;;;) to the “Type” dialog box and click “OK”
To make these values visible again, follow Steps 1 and 2 above, but then in the “Category” box select “General” to return to the default format. Keep in mind that hiding cell values does not secure sensitive data, as anyone with access to your spreadsheet could easily make any hidden cell values visible again. If you’re worried about security concerns, consider adding a password to access or make changes to your Excel spreadsheet.
To password-protect an Excel spreadsheet, follow these steps:
- Open the spreadsheet that you want to password protect
- Click on the “File” tab
- In the dropdown menu, click on “Protect Workbook”
- From the available options, select “Encrypt with Password”
- Type your desired password in the dialog box and click “OK”
- Confirm your password in the second dialog box and click “OK”
Hide Excel Formulas
When you click on a cell that contains a formula, the default settings will display the formula in the formula bar. However, if you want your formulas to remain hidden, you’ll need to follow these steps:
- Select the cell (or cells) containing the formula you want to hide
- Go to “Home,” then “Format,” and then “Format Cells”
- Under the “Protection” tab, check the box for “Hidden”
- Click “OK”
- Go to “Review” and then “Protect Worksheet”
- Check the box for “Protect worksheet and contents of locked cells” (if it’s not already checked) and click “OK” to finalize your changes
This change will prevent formulas from appearing in the formula bar, but the results of the formulas will still show up in the cells. Like hidden values, hidden formulas are only protected if the worksheet or workbook is password protected. Otherwise, anyone could restore the default settings and make the formulas appear in the formula bar again.
Hide Duplicates In Excel
Duplicates can occur in an Excel spreadsheet for a variety of reasons, from erroneous entries to formatting issues that appear when importing new data. You may want to delete duplicates permanently, but if you’re unsure whether the duplicate data is valuable, you can simply hide the values for the time being. Here are the steps you need to follow to hide duplicates in Excel:
- Select the range of cells in which you want to hide duplicates (if it’s the entire spreadsheet, you don’t need to make a selection)
- In the “Home” tab, go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
- Select “Duplicate” values with “Custom Format…” in the “Duplicate Values” dialog box
- In the “Format Cells” dialog box, go to the “Font” tab and then the “Color” tab
- Choose a color (like white) that matches the background color of your spreadsheet
- Click “OK”
- With the same range highlighted, go to the “Data” tab and click on “Filter”
- Click on the dropdown arrow in the header of each column in which you want to hide duplicates and hover your cursor over “Filter by Color,” then click the color you previously chose in the “Format Cells” dialog box
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!