Hide Data in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, we will learn how to

– Hide the Entire row and column

– Hide data using different features from the toolbar

– Hide data using the Hide command and Format option.

– Apply conditional formatting to change the color and hide data

– Hide formulas and particular cell

Excel adds various features in different versions as a result all the features are not available in every version. However, all the methods used in this tutorial are available in Excel 2013, Excel 2016, Excel 2016, Excel 2019, Excel 2021, and Microsoft 365 versions.

Therefore, we will use hidden data in Excel for better visualization, a better way to analyze, and an easy way to protect confidential data.

Here is the overview image of this article, Follow more and go through the total article to know more about hiding data in Excel.

The overview image

What Are the Ways to Hide Data in Excel Using Keyboard Shortcut?

To hide data, here we will use the keyboard shortcut. But there are different shortcuts depending on rows or columns.

1. How to Hide Entire Row?

Therefore, in this method, we will hide the data of an entire row.

  • Initially, select the row by clicking on the row bar, then press CTRL + 9 to hide the data. Use from the typing keys instead of the Neumeic keypad.

Hiding data row-wise in Excel

  • Finally, get the final output with a hidden row.

The final output of hiding row-wise data in Excel

2. How to Hide the Entire Column?

Here, in this method, we will hide an entire column using the keyboard shortcut.

  • Select the column by clicking the column bar, and press CTRL + 0 to hide the data in the column. Therefore, select from the typing keys instead of the numeric keypad.

The final output of hiding row-wise data in Excel

  • Once this process is complete, the final output will be similar to the one below.

Hiding data in Excel column wise

What Are the Methods to Hide Data Using Toolbar in Excel?

To hide data in Excel using the Toolbar you can use four different methods. Now you can hide the data using any of these methods, as per your requirements.

1. How to Use Format Group?

If you want to hide an entire column’s data then select the entire column and follow the below steps.

  • Initially, go to the Home tab >> Cells >> Format >> Hide & Unhide >> Hide Columns to hide the entire column.

Using format option to hide data in Excel column wise

  • To hide the entire row’s data, follow the steps already shown here, but instead of selecting Hide Columns from the Format dropdown, select Hide Rows to hide the entire row.

Hiding data row wise

2. How to Apply Filter Command?

There is an option in Excel called “Filter” and you can filter any sort of data using this option.

  • Select the heading cells from the dataset and click the Data tab >> Sort & Filter >> Filter as below.

Selecting filter from the toolbar

  • Open the filter option from Gross Profit and select the data that will be visible.

Using filter option to hide data in Excel

  • Finally, click OK to complete the process. Once the process is complete the data will be hidden but the hidden data will be still in the dataset.

Final Output after filtering data.

Read More: How to Hide Cells in Excel Until Data Entered

3. How to Apply the Advanced Filter Option?

There is another option, which is the Advanced Filter. With this option, the data can be filtered according to criteria. Suppose you need to hide all the data where the net profit is less than $10,000.

  • Now click on the Data tab >> Sort & Filter >> Advanced Filter.

Applying advanced filter option

  • The Advanced Filter dialog box will open. Select list range B4:G12 and Criteria range B14:15.

Advanced filter dialog box appeared

  • Finally, click OK to complete the process, and the final output will show only the data with more than $10,000 in net profit.

Final output after applying Advenced filter option to hide data in Excel

4. How to Use Group Command?

Here, In this method, we will create a group and hide the data in Excel.

  • To create a group, select the cells and go to the Data tab >> Outline >> Group as below.

Creating group

  • The Group dialog box will open up and allow you to select rows if the group is row-wise or columns if the group is column-wise.

Group dialog box pop up.

  • Now, to hide the entire group, click on the (-).

A group is created

  • Once the full process is done, the final output with the hidden data will be below.

The entire group is hidden

Read More: How to Hide Part of Text in Excel Cells 

How to Hide Data Using Hide Command in Excel?

You can hide the required data using the options bar as well. There are different methods for executing the processes. To hide data, use the Hide command in Excel as below.

  • Select the column and right-click the mouse to open the options bar. Select the Hide option to hide the data.

Using hide option to hide data in Excel

Note: You can hide an entire row using the same process.

Applying the hide option to hide the rows

What Are the Methods to Hide Confidential Data in Excel?

To hide the confidential data in Excel you can use 5 methods. Once you protect the sheet, it is unchangeable. So, these processes are safer and more accurate.

1. How to Apply Format Cells?

Therefore, we will learn how to apply format cells in Excel to hide data.

  • Initially, select the cell and right-click on the mouse. Then the options bar will open, and select Format cells as below.

Using format cell option to hide data in Excel

  • Now, write down (;;;) in the type option and click on OK.

Changing format

  • After completing the process, the cells will be hidden as below.

Final output after using format cells

2. How to Apply Cell Formatting to Change Font Color?

Here, in this method, we will learn how to hide data by applying cell formatting to change color.

  • To change the Text font color to hide the data in a particular cell, go to the Home tab and click on the drop-down menu in the font section.
  • Select White so that the text color matches the background and no data is visible.

Changing font color

  • However, after selecting the cell, the hidden data will be visible in the formula bar as below.

Final output after changing font colour.

3. How to Hide Formulas?

Therefore, we will hide formulas in this method.

  • Initially select the cell and open the Format cell.
  • Then go to Protection >> Locked and Hidden >> OK to lock and hide the cells. But this option will not activate unless the sheet is protected.

Selecting Locked & Hidden to protect the sheet

  • Now, protect the sheet by clicking Review >> Protect >> Protect Sheet.

Protecting the sheets to hide data in Excel

  • After that, select a password and click OK.
  • Lastly, confirm the password and that the sheet is protected.

Adding password to protect sheet

  • Finally, the sheet is protected, and the formula is hidden in the formula bar.

Final output after protecting the sheet

4. How to Hide Particular Cells?

Sometimes we need to hide particular cells in Excel, and this process is the most critical to execute. Now, follow the steps below to hide particular cells in Excel in an easy way.

  • Select the cell and change the font color to white to hide.

Hiding particular data by protecting sheet

  • Open the format cell option lock and hide the cell to hide the text from the formula bar as well.

Final output after protecting the sheet and hide particular data

Similar Readings

5. How to Hide Total Worksheet?

Now, we will hide the total worksheet in Excel using the below process.

  • Initially, lock the entire sheet using the process already shown before.
  • Then, right-click on the leaf bar and select Hide from the bar to hide the entire sheet.

Hiding total sheet to hide data in Excel

Things to Remember

  • Protect the sheet after you hide any data so that the data remains unchanged.
  • Always keep a backup of the original data, just to be safe.
  • If you use Excel simple or direct option then you can not hide selected data in Excel. In that case, you need to hide or unhide an entire row or column.
  • You get better visualization after hiding data from the dataset. however you print the dataset you will get to see the hidden data on the printed copy.

Frequently Asked Questions

Q1: Will hidden data permanently be removed in Excel?

Ans: No, hidden data is not removed from Excel. You can easily unhide the data and continue.

Q2: How to unhide cells in Excel?

Ans: Once you hide the cells, you need to unhide them. Select CTRL + SHIFT + 9 to unhide cells row-wise and press CTRL + SHIFT + 0 to unhide cells column-wise.

Q3: How do I print with hidden data?

Ans: If you print with hidden data, then the data will be shown in the printed sheet. So, print in the normal procedure to get the hidden data.

Download Practice Workbook


From this article, we have learned how to hide data in Excel in different ways, such as applying keyboard shortcuts, the toolbar, the Filter option, the Advanced Filter option, the Hide command, and the options bar. Here, we covered every possible way to hide data, especially if the data is confidential. These methods are very helpful. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions.

Afrina Nafisa
Afrina Nafisa

Hey! This is Afrina Nafisa. Graduated from Ahsanullah University of Science and Technology. Currently working as a content developer in ExcelDemy. Working on myself for being better every day to make better content.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF