Hide Data Within a Worksheet in Excel

Add to Favorites

In Excel you can actually hide data that is stored within a worksheet.

This allows you to show data that is useful to a user while hiding data that you don't want them to change or that you simply don't want them to see.  This makes for more robust worksheets, as it becomes harder to mess things up and also makes for nicer looking well-kept spreadsheets.

Hide Data Within Worksheets Method 1 - Hide Columns/Rows

The first method involves simply hiding the columns or rows that contain the data you want to hide.

  1. Select the columns or rows that contain the data to hide:
  2. Right-click over the column/row headings and click Hide:
  3. The result is that the selected columns no longer appear in the spreadsheet:

    Notice how it goes from column E to column J.  That's how you know there are hidden columns in the spreadsheet.

Doing this for rows works exactly the same - select the desired rows, right-click the row headings, click Hide, and that's it.

Unhide Hidden Columns/Rows in Excel

  1. Select the two columns that border the ones that you want to unhide or see again.  We select columns E and J.  Make sure to select the columns by holding the left mouse button down on the left-most column and dragging to the right until the column on the right is also selected.
  2. Move the mouse over the selected column headings and right-click and select the option Unhide:
  3. The hidden columns are now visible:

Unhiding works the same for columns as it does rows.

Hide Data Within Worksheets Method 2 - Font Color Trick

The first method has the problem that it will hide all data in the columns/rows that you hide and this might not work in your spreadsheet.

In this case, we use the font color trick, which is what I call it.  We hide the data in plain site by changing the font color to the same color as the background of the cells.

  1. Select desired cells to hide:
  2. Change the font color to white:
  3. Now, you can't see the data:

Note that the data is still there and you can see it if you select a cell and look to the formula bar, located above the column headings.

Unhide Hidden Cells in Excel

If you want to see the data again, just select the desired cells and change the font color back to black.

Keep the Hidden Data Secure

The problem with this method is that, since the user can't see that there is data in these cells, they might delete the data in them by accident.

As such, you can protect the worksheet so the user cannot delete cells.  This allows you to have data hidden right in front of them.

Notes

Neither of the methods above should be used to protect or hide sensitive information!!!

Each of the above methods can be easily circumvented; the point of hiding data like this is to make the spreadsheet easier to read, less cluttered, and more professional looking.

Download the accompanying workbook to test these methods in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to compl...
Generate Random Numbers within a Range in Excel
Tutorial: How to generate random whole numbers (integers) that are between two numbers.  This allows...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick
Tutorial: Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forename...
Print Only Specific Parts of a Worksheet in Excel
Tutorial: In Excel you can select parts of a worksheet to print while ignoring all of the other data...
Remove Gridlines from Only Part of a Worksheet in Excel
Tutorial: How to remove gridlines from a part of a worksheet in Excel instead of the entire workshe...