Overview

You may want to hide certain rows, columns, or even sheets in a report based on particular outputs. Normally, restricting what is returned in a report would be done with filters on range lists, but there may be more complex requirements, or filtering out what you need to hide may not be an option. In this article we cover how to use a special Cosmos "Hide" function in Excel to give you full control over hiding things in your report.

Details

As this is considered a more advanced piece of functionality, the Cosmos function to hide rows, columns, or sheets will be entered directly into the report as an Excel function rather than through the Cosmos Excel Add-In. 

To conditionally hide things in your report, you will add a =COSMOS.HIDE() function in Excel. This function has three parameters:

=COSMOS.HIDE(rows,columns,sheets)

• Rows: If this first parameter resolves to TRUE then the row will be hidden.
• Columns: If this second parameter resolves to TRUE then the column will be hidden.
• Sheets: If this third parameter resolves to TRUE then the sheet will be hidden.

For example, in cell A8 below, a COSMOS.HIDE function has been added and, in the first parameter for "Rows", there is an IF() statement looking at the total sales for a customer and, if the amount is zero, the row will be hidden:

In the example above there is a "FALSE" argument added if the value isn't equal to zero but this isn't required, the only thing that matters is that the parameter contains TRUE if you want the row, column, or sheet hidden.

Multiple parameters can be used at the same time as well, so if you wanted to use an IF() statement to sometimes hide the row but you also always wanted the column hidden then you would add a TRUE into the second parameter for Columns as well:

Once you have added in the COSMOS.HIDE function you will see a special icon with the word "Hide" letting you know that there is a Cosmos Hide function present in that cell:

Clicking on the icon will display a card showing which values are currently set to be hidden:

Once the report runs, any COSMOS.HIDE functions that return TRUE for any of the parameters will have the respective rows, columns, or sheets hidden.