Mastery is the best goal because the rich can’t buy it, the impatient can’t rush it, the privileged can’t inherit it, and nobody can steal it. You can only earn it through hard work. Mastery is the ultimate status.
Derek SiversHow to Live

 

Webinar Feedback November 2024

Financial Functions Part Two

“This accounting webinar series is one of the best for practical CPD hours. It focuses on things we actually use in our day-to-day work, like Excel models and useful tips, instead of theory or topics we may never touch depending on your area of expertise.

It’s clear, easy to follow, and you come away with skills you can put into practice right away. Highly recommend it for anyone wanting CPD that’s actually helpful.”

10/10
C Walthew
November 2024

Are you Partial to Calculations?

Excel has re-badged one of the Calculation Options in the Formulas tab – see below. This is a change relating to the new Python capabilities.

The middle option used to ignore Data Tables (a What If feature on the Data ribbon tab).

The newly named Partial option also ignores Data Tables plus any Python calculations that may take a long time to calculate.

Python calculations are done in the “cloud” and require an internet connection.

Even if you’re not a teacher, be a teacher. Share your ideas. Don’t take for granted your education. Rejoice in what you learn and spray it.

Source: 9 Life lessons
Tim Minchin

Selecting a Formatted Table in a Formula

You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table.

To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.

Excel Hyperlink Formula Solution

Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken. Try this solution using a formula to create a hyperlink that doesn’t break so easily.

In the image below there is a formula in cell C3 that creates a hyperlink to cell A1.

Here is the formula.

=HYPERLINK("#"&CELL("address",A1),"<link text>")

Simply change the cell reference from A1 to whatever cell you want to link to. This works for cell in the current sheet.

Hyperlinks to other sheets

In the image below is an example of a link to another sheet.

The formula is.

=HYPERLINK("#"&CELL("address",Report!A1),"<link text>")

Again, change the reference to create a hyperlink that doesn’t break if the sheet name changes.

Pro Tip

To return after following a hyperlink press in sequence, function key F5 and then press Enter. Don’t hold them down just press F5 then press Enter.