Functions

/Functions

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 [...]

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 [...]

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 [...]

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 [...]

 Display Sheet Name

Display Sheet Name Is there a formula to insert the sheet name into a cell? By Neale Blackwood Yes. In cell A1 insert the formula =CELL("filename", A1). In another cell, enter =MID(A1,SEARCH("]",A1)+1,LEN(A1)-SEARCH("]",A1)) The first formula will display the file's full path. The second formula will display the sheet name. This is also a useful [...]

Functions|

 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 [...]

Functions|

 Dynamic Headings

Dynamic Headings Is there a way to change headings based on a cell that has the current month? By Neale Blackwood Yes. If cell A1 contains Feb 03 as a date, then the following formula will create the heading - Report for Feb 03. ="Report for " &TEXT(A1, "mmm yy") Note the space after [...]

Functions|