How to hide data in Excel?

Sometimes you may wish to hide data in Excel. For example, in a complicated spreadsheet, it may be safer to hide formulas or personal details from view.

There are several ways to hide data in Excel.

1) Hiding an entire column or row which can then be un-hidden at a later stage should a user require to edit the data.

To hide a column, right click the column header and choose Hide.

Hiding columns and rows can simplify a worksheet or workbook by removing the detail and showing only the important information. Hiding columns containing formulas can also prevent accidental deletions making the workbook easier to update.

2) Hide data in Excel by changing the font colour of the data to the same as the background colour of the cell.

To prevent accidental deletion of this data you will have to protect the sheet, making sure that the cells containing the hidden data are 'locked'.

3) An entire worksheet can also be hidden and if it contains very sensitive data it can be made 'very hidden' (yes, it's really called that!) through the VBE (Visual Basic Editor) and then protected using a password.

To hide a worksheet, right click on the sheet tab, select Hide.

For example, an HR department might have a worksheet with personal information such as salaries, date of births and addresses that are used for company business. Hiding the worksheet could prevent the data being easily viewed by everyone.

4) Using the Autofilter/Advanced Filter options also will temporarily hide rows of data which have been filtered out.

To Filter data, select one cell in the list or table. Select Data, then Filter.

This feature is used to filter out data and quickly display what a user is looking for. Filtering was enhanced in Excel 2007 where it is even possible to filter by colour.

5) Creating a Group Outline either for columns, rows or both. This allows the hiding of data in columns/rows as chosen by the user.

To create a Group Outline automatically, click at the top of a worksheet containing formulas and values. Select Data, Group, Auto Outline.

Grouping creates little numbered buttons on the screen, allowing one-click summaries of the data. For example, a balance sheet could be viewed to show different 3 levels of detail; annual, quarterly or monthly figures.

See the related forum posts below.

Related forum posts:

Excel outlines

Outlines, why use them? When you could maybe hide or freeze?
Read forum post

How do I group and outline my spreadsheet?

See above
Read forum post

Excel: filters and sorting

please tell me about filters and the sort buttons
Read forum post

Filters

why is it when you filter a table, and you delete those items that are filtered, when you show all again you do not always show the complete remaining records (some are missing) ie. i have a table that shows a number of invoice details in rows, some are marked X, some are marked C. I filter showing only the rows with an X. then delete these. when I show all not all the C records remain (I have deleted some of them) is there a way to avoid this?
Read forum post

How To Insert, Delete, Format, Hide, Copy And Protect Worksheets In Excel

A new workbook contains 3 tabs in the same colour and with standard names. When creating advanced worksheets, you may need more or less tabs with coloured tab names. This article examines tabs and how they can be added, taken away, protected and altered.
Read article

Customize Your Gridlines And Borders In Excel 2007

If you are a regular user of Microsoft Excel 2007 then you will know how important gridlines are. If you are working with a large, dense amount of data then it is vital that that data is set out in the clearest and most accessible fashion. Excel’s gridlines provide a clear border between sets of information so that no data is confused or misinterpreted.
Read article

Training courses