Home » Learn how to hide rows and columns using Named Ranges

Learn how to hide rows and columns using Named Ranges

Do you have a workbook that could do with some neatening up?  Being able to hide rows and columns in a report that contains data you don’t need to view is a great way to do this.  An example of a report like this is a financial statement, which has account level detail and monthly columns with zero balances that don’t need to be shown.

It’s also a great idea to hide the rows and columns you don’t need to see automatically. This can be done using Macros.  Following on from a previous tip; How to hide zero rows using Excel functionality, we have tweaked the Macros to make use of Named Ranges.  Now, at the click of a button, all the rows and columns in your layout will update to reflect all accounts which have amounts against them—hiding those which don’t.

To ensure the workbook is completely up to date, the following procedures are run:

  1. Unhide all rows
  2. Hide zero rows
  3. Unhide all columns
  4. Hide zero columns

The below image shows an example of a workbook where rows and columns have not been hidden:

And the following shows the result after the Macros have been applied:

If this tip is what you’ve been looking for and you would like to better get to grips with it, take a look at the following webcast that will guide you through the steps:

The X-Factor Report Writers Series, Part 8: Final touches to your layout with Macros

The webcast also makes use of the following supporting documents which you can download here:

FRD Hide Zero Rows (Excel)

FRD Hide Zero Rows – Static values (Excel)

Final Macros (txt)

Neatening your workbook makes it easier for you to analyze your data, and saves you time in finding the information you need to make that business decision.

The post Learn how to hide rows and columns using Named Ranges appeared first on Sage Intelligence.

Source: Sage Intelligence

Leave a Reply

Your email address will not be published. Required fields are marked *