CODEWITHSUNDEEP
excelexcel-formula
Ravi
Ravi

Reputation: 601

Hide entire table in excel

I have a requirement where I need to either show or hide entire table (in cells N47 - S52) based on the value of a cell in P35

Ex : If P35="Yes", show entire table, else hide entire table

I can apply the formula for each cell seperately, but is there a way to do it at once?

Upvotes: 0

Views: 5538

Answers (1)

Bharat Anand
Bharat Anand

Reputation: 484

Here is one way to go about it, with the below assumptions that:

  1. It is okay to move your actual table into a different worksheet and instead leave a reference to the actual table to be shown/hidden
  2. The size of the actual table (no of rows and columns) is static. Changes to the size will require changes to your formula in Step#2 on cell B2.

Step#1: Set up a table in a separate worksheet (Sheet2). Check the table name in Design tab (Excel 2016) on the top left corner while one or more cells of your table is selected. in our example below it is Table1 (default).

enter image description here

Step#2: Next, select the block of cells in Sheet1 where you want your table to "appear"/"disappear" (In this example it is Sheet1 B2:D5) and while you have the selection on, press F2, to enter the below formula in one of the cells. Once done, hit Ctrl+Shift+Enter to enter this as an array formula.

=IF(G2="Yes",Table1[#All],"")

So when G2="Yes", your table will be visible, any other values and it will disappear!

enter image description here

Upvotes: 1

Related Questions