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

2017-05-30T11:58:16+00:00 Excel 2016, Formatting, Functions, Productivity|

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

2017-03-08T13:10:40+00:00 Data Handling, Functions, Productivity|

## 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|

## Last Used Cell In A Row

Last Cell used on a Row Find the reference of the last cell used in a row By Neale Blackwood One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles. Someone asked if there [...]

2017-03-08T03:53:55+00:00 Arrays, 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 [...]

2017-03-08T03:53:55+00:00 Excel 2013, Excel 2016, Financial Modelling, Functions|

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

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

## Select Formula Cells Only

Select Formula cells only Is there a way to select all cells that have a formula? By Neale Blackwood, 1 Dec 02 Yes. Hold the Ctrl key and press the "a" key to select the whole sheet. Then press F5 (GOTO), click the Special button, then click the Formulas radio button and select the [...]

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

## Rounding To 5 Cents

Rounding to 5 cents Is there a formula to round to the nearest five cents? By Neale Blackwood, 22 Jun 09 You can use the ROUND function to do it. If the number is in cell A1, this formula will work: =0.05 * ROUND(A1/0.05,0)  Example: This formula rounds 2.5 cents and 7.5 cents up [...]

2017-03-08T03:53:56+00:00 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 [...]

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