How do I hide specific data in Excel?

There are several ways to hide and unhide columns and rows in Excel. One thing you might not be aware of: you can hide and unhide more than one column or row, at the same time, even if they are non-contiguous. Better yet, if they are contiguous you can use Excel's Grouping tool. Below I have outlined a couple of time-saving ways to hide and unhide your Excel columns and rows.

Hide and Unhide Columns and Rows

1. Select the number of columns or rows you are interested in hiding (use the Control key to select additional noncontiguous columns or rows).
2. Click the Format button located on the Home tab / Cells group then choose Hide Columns or Rows (another option is to Right click on a highlighted column or row heading and select hide).
3. Your Columns and Rows are now hidden.

Unhide all Columns or Rows

1. Click on the Format button located under the Home tab / Cells group then choose Unhide Columns or Rows (another option is to drag over the range of columns or rows you are interested in unhiding then Right click in the column or row heading and choose unhide).
2. Your Columns and Rows are now visible.

Hide and Unhide Columns and Rows Using the Group Feature

The following steps below outline how to use the Group option in Excel for versions 2007, 2010, 2013, and 2016. Grouping only works with Columns and Rows that are contiguous (together).

1. Select the number of contiguous columns or rows you are interested in hiding (you cannot use the Control key during this operation).
2. Click the Group button located on the Data tab / Outline group then choose
   
3. Your Columns and Rows are now hidden.

Not only are your columns or rows hidden but Excel has added show hide buttons to help you toggle your view:

Collapsed view of columns:

To Turn the Group Feature Off

1. Select the range of Columns or Rows you would like to Ungroup.
2. Click the Group button located on the Data tab / Outline group then choose
3. The Grouping feature is now removed.

Did you like this step-by-step tutorial? We've got a great video, 10 Microsoft Excel Aha Features You Need to Know. Watch it completely free!

Previously we talked about hiding entire rows or columns [LINK TO PREVIOUS], which is a fairly straightforward endeavor.

• How to save a GIF or video from Twitter
• How to Change Your Mac's Default Screenshot Directory
• How to Uninstall Outlook Add-Ins

Something a little less obvious is how to hide an individual cell within an Excel workbook. Deleting the cell clears it, but you can no longer use its contents for formulas. It’s not the best option. Instead, we’re going to use a little trick most Excel users probably didn’t know, and it all involves a few semicolons.

1. Open an Excel workbook. I’m going to create a new one and enter dummy data, but this will work fine on an existing spreadsheet as well. Save your data first, just in case. 

2. Select the cell you wish to hide, and right-click. 

3. Choose Format Cells option from the dropdown. 

4. Set the format as Custom under the Number tab. 

5. Enter ;;; (three semicolons) as the format. 

6. Press OK. The cell is now hidden, but the data remains. You can still apply this as part of a formula without skewing the data, which is perfect for hiding sensitive information, like a social security number -- although you shouldn’t be storing those in spreadsheets anyway. 

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.

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

Read forum post

See above

Read forum post

please tell me about filters and the sort buttons

Read forum post

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

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

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.

How do I hide certain data in Excel?

Right click on the column you want to hide and then click “Hide.” You can hide multiple columns this way if you have them all selected. One last look at the data set. If you want to see the hidden information again, simply right click on the space the column should be and click “Unhide.”

Can you hide only certain cells in Excel?

There are a few different ways to hide cells in Excel. One way is to simply select the cells you want to hide and then click the "Hide" button on the "Home" tab of the ribbon. Another way is to right-click on the selected cells and choose "Hide" from the drop-down menu that appears.

How do I hide only certain rows in Excel?

Hiding Rows Select a cell within the row(s) to be hidden. On the Home command tab, in the Cells group, click Format. From the Format menu, in the Visibility section, select Hide & Unhide » Hide Rows.

How do I hide rows with certain values?

To do this, first select the data that you want to filter. Then, click the Data tab on the ribbon and click the Filter button. In the drop-down menu that appears, click the column that you want to filter by and then uncheck the box next to the value that you want to hide. Finally, click OK.