ICAEW.com works better with JavaScript enabled.
Exclusive

# Your Questions Answered #5 – Tips and Tricks Live extended: What is your favourite underappreciated formula?

Author: Ben Ducker

Published: 13 Jul 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
In a recent 'Excel Tips and Tricks live – your questions answered' webinar, Ben Ducker and Ian Pay tackled Excel questions and queries covering best practice, the use of functions and functionality, and application of Excel techniques to accounting scenarios. Ben returns to answer some of the questions from the webinar they were unable to respond to during the session.

Hello all and welcome to a new regular feature from the ICAEW Excel Community where we endeavour to get ‘Your Questions Answered’. In this feature, we will address the Excel questions and worries you send in by providing you with solutions and guidance on the best practice of using Excel. Your community needs your questions. Please send as many as you can to us at:

## The question

What is your favourite underappreciated formula?

For me it's probably HYPERLINK. By using a combination of the CELL and HYPERLINK functions it's possible to create a clickable link to anywhere in a workbook, and the link will follow the target cell even when new rows or columns are added.

This is the complete formula you'd need, and below I'll explain how to use it and how it works.

For example, let's say we have a financial model and we wanted to include a link from the output P&L to the Revenue inputs which start at cell F44 on sheet Sheet1. We could use this formula to make a clickable link:

The Sheet1!F44 part is a link to the cell which we want to hyperlink to, and the function CELL(info_type, reference) can be used to return the address of that target. In this instance we want to get the "address" info type, and the target is going to be cell F44 on Sheet1. This function evaluates to [Book1]Sheet1!\$F\$44, where Book1 is the workbook name. The great thing about this is that because the reference cell is a link, that target will "follow" that cell around when new rows/columns are inserted.

We can then pass that dynamic address to the HYPERLINK function as the link_location, by prefixing it with the # sign. And then finally we can give the link a name, which is what will appear to the user so they know where the link will take them, in this case I've hardcoded it as "Revenue Inputs" but that could be linked to the title of the revenue section if you wanted, or even to the target cell Sheet1!F44 if that was the title for the inputs section.

I know everyone loves the INDEX/MATCH combination, but I think that HYPERLINK/CELL is probably my favourite underappreciated formula because it's "easy when you know how" and it builds really robust hyperlinks which aid navigation and automatically stay up to date.

## The question

Data grouping: How can I extend range without "ungrouping" first and then "re-grouping" the range?

The simple answer to this is that the rows to be grouped (or columns – but this answer will focus on rows) can be selected, click on the Data tab on the ribbon then click on the Group button and the rows will have their outline level updated. The rows won't automatically be shown/hidden until you click on one of the controls; either the numbers above the outline grouping levels, or the [+] or [-] icons next to the grouped rows.

To explain in a bit more detail, each row has an assigned "outline level", this is not shown until some rows are grouped. The default level for ungrouped rows is level 1. When rows are "grouped" they will have their outline level increased to 2, and this can be seen by the position of the dots which are under the '2' at the top.

The [-] button next to row 11 will hide rows 5-10. They could also be hidden by clicking on the number '1' at the top left. (By the way, if we wanted to add rows 3 and 4, I could simply highlight those rows and click on 'Data' > 'Group'.)

What's interesting is that rows can be grouped with various different levels, to create further sub-levels. Say we wanted to hide rows 5-6 at a deeper level, we could highlight those two rows and click on 'Data' > 'Group':

This becomes really helpful when we click on the 1, 2 and 3 at the top, as these will define the level of detail which is shown. For example, clicking on 1 shows only rows with level 1:

We could also click on 2 to show rows with level 2 or lower (ie, levels 2 and 1):

And finally, we can click on the 3 to show rows with level 3 or lower (ie, in this case levels 3, 2 and 1):

The final tip I would like to include here is that you can move the outline grouping buttons to the top of each section. To do this click on Data tab, then on the little square in the bottom right of the Outline tools group and then untick the "Summary Rows Below Detail".

The buttons will work the same, but now the [+] and [-] buttons are shown above the grouping ranges. This can be helpful if you have a header row and then you want to hide the details of a section below:

## The question

What does pasting constants do?