Functions

Home/Functions

Dynamic Data Validation with Tables in Excel

Dynamic Data Validation with Tables in Excel Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong? The answer to these common questions may surprise you. Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” in a [...]

Master INDEX MATCH in 60 seconds

INDEX MATCH is great but GETMATCH would be better I've written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP. However, it is a trickier formula and therefore not as widely used, so I've added a new formula suggestion to the Excel [...]

Fix MYOB Account Numbers Exported to Excel

Fix MYOB Account Numbers Exported to Excel It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won't find them.  Here's how you can fix MYOB account numbers. The Cause When MYOB [...]

2017-03-13T14:49:44+00:00 Data Handling, Functions|

58 Crazy Excel Stories (that are probably happening in your company right now)!

58 Crazy Excel Stories (that are probably happening in your company right now)! Crazy things happen when you mix untrained users with Excel. It’s not really rocket science.  Crazy things would happen with any complex system if you don’t train people properly. But many companies simply assume that everyone knows Excel, so they [...]

See Celsius in your cells (try saying that quickly 3 times!)

I recently was asked if there's a way of adding the Celsius symbol to a value in Excel. The answer is yes, and the method can be applied to all sorts of useful symbols / notation.  e.g. bullet points, m2, km/hr, bbl, currency symbols etc. This is how.... Pick a blank cell then go to [...]

2017-03-08T03:53:54+00:00 Formatting, Functions|

Hyperlink Formulas & how to use them

Hyperlink Me! The HYPERLINK Formula (who knew that formula existed?) can be really useful when navigating around large spreadsheets The simplest format is along the lines of =HYPERLINK("#A1","Any Helpful Message") This would jump you to cell A1 of the current sheet.  But that's no different to a normal old Hyperlink (via RIGHT-CLICK > Hyperlink or Insert [...]

2017-03-08T03:53:55+00:00 Functions|

If you’re not Modelling Depreciation like this, you’re doing it the hard way!

Depreciation is one of those concepts that sounds easy and simple … until you try to put it into a financial model! In this blog, we start with Excel’s standard depreciation functions, then show two much better, more functional and more elegant ways to calculate depreciation. 1. Excel Functions Excel has some [...]

 3D Sum Formula

3D Sum formula Is it possible to easily add up the same cell across spreadsheets? By Neale Blackwood Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then [...]

2017-03-08T03:53:56+00:00 Functions|