Hiding Data In Excel Using Conditional Formatting – Step By Step

loading=

The tool Excel, we can process different types of data ; sometimes we need to hide information in a cell, its contents or to hide a formula in an Excel . So today we will teach you to hide data in Excel using conditional formatting.

How to hide data in Excel, and what is its use?

You can hide rows or columns , including also a specific cell or hide an Excel with a macro. There are 2 ways to prevent content is visible, altering the size of the cells or camouflage the text with the same background color.

For that you just have to make the selection, and go to ” start & gt; Cells & gt; Format & gt; Visibility & gt; Hide and show “; that option we can choose to hide and display the row or column marked. You can also display the context menu on the column or row and choose the Hide option.

 hide a column in excel

We can camouflage the cells by selecting the” start & gt option; Source & gt; Font color “where we can change the color to white text. If you prefer to change the background of the cells, you must go to” start & gt; Cells & gt; Format & gt; Protection & gt; Format Cells & gt; Fill “.

These methods leave hidden data indefinitely. But conditional formats are the best option , to display when a condition is met that have defined yourself; implementation very easy.

What it is a conditional format?

It’s a tool located” start & gt; Styles & gt; Conditional Formatting “which change the look or & nbsp; style of one or more cells , as long as a defined criterion is met by a rule.

There are 6 types of rules to apply A format cells, depending on the value or formula containing; each rule in turn allows apply styles to a table or cell in Excel (type and font size, borders, background colors, scale 2 or 3 colors, data bars and icons to).

You can change the color to a cell with conditional formatting . For example, you can make a cell red when the value of a note based on 10 to be less than 5 (indicating that a student has failed). The styles and criteria you can define are almost endless.

How to hide data in Excel using a conditional format?

The best way to understand how this is done is through an example. First make a table sales, with the number of units and unit price of a product as well as total and subtotal of sales made.

We will build another table with a list of discounts depending on the quantity of products sold; and we want to hide or show this second table, using a conditional format that is activated by pressing a box verification.

hide data in Excel using a conditional format

We’ll have to make visible the Developer tab “File & gt; Excel Options & gt; Customize Ribbon & gt; Main tabs & gt; Developer “. Put the box in an area close to the sales table in “Developer & gt; Insert & gt; Form Controls & gt; Box “.

Now link the box with a cell to save your status (if enabled show off true and if your will be FALSE). For that right click on the box and select “Format Control”; a window post the chosen cell, for example the B10 will open.

We’ll create a rule take the value in cell B10 to show or hide the table of discounts, depending on whether the box is checked or not. For that select the entire range of cells in the table to hide and go to the” start & gt option; Styles & gt; Conditional formatting & gt; New Rule “.

In the window that opens, select the last option “Use a formula to determine which cells to format” introduce the formula=$ B $ 10=FALSE and ended with “OK”. With this, we have defined a rule that will make invisible table until the box is checked.

Now you know how to hide data with conditional formatting, your work in Excel will be more dynamic . If you enjoyed this post, make your comment and share it with your contacts to continue to grow as professionals.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

Deja un comentario