Some Simple Yet Less Common Excel Functions

I love that Excel can do so many things so quickly and since there are hundreds of functions I am always coming across ones I was completely unaware of. In this article I have compiled a few of my favourite, less commonly used functions. A lot of these functions are amusing since there are often two different ways of completing the task, one of which is not a function! Allow me to explain...

Rounding
If you have lots of numbers with lots of decimal places and you would like to show them all as rounded to a specified number of decimal places, normally this can be achieved simply through formatting. Even if you did not want to lose the list of numbers that was the original, you could copy and paste the column and then format it. To achieve this, right click the column or cell, select format cells, choose Number as the data type and then specify the number of decimal places you require. There is also a rounding function to achieve this which is as follows:

= ROUND(number, num_digits)

The number can be the cell you require and the num_digits is the number of digits you would like it rounded to. If cell A1 contained 4.32 for example and the function read = ROUND(A1, 0) then the result would be 4 since no decimal places would round it down to 4. There are also separate ROUNDUP and ROUNDDOWN functions.

Hyperlink
It is true that if you type a web address and then click Enter, Excel will automatically recognise it as a hyperlink. When the link is clicked, the web site will open assuming it is a valid address. Using the Hyperlink formula allows the user to add a link to a specific document stored anywhere on your computer or on the internet. It also allows the sheet designer to give the link a name so that the friendly name will appear as the link rather than the full address of the document. The Hyperlink function is as follows:

Hyperlink (link, display_name)

An example of the formula would be Hyperlink ( "C:\Test Folder\Test.xls", "Test Doc" )

If you are unsure of what pathway to use for your document, open windows explorer and copy the pathway from there and add the filename plus the extension on the end. Always use the quotation marks to enclose your pathway name.

Info
If you require information on your sheet such as the current operating system, the Info formula will retrieve them for you. The formula will return the current directory, the number of active worksheets in the current workbook, version of Excel, the name of the operating system and several other useful titbits of information. The formula is as follows:

=INFO(information you require goes here)

If for example, you require the current operating system then type the following:

=INFO("system")

And this will return either pcdos for Windows or Mac for a Macintosh. If for example you would like to know how many worksheets are in use, type the following:

=INFO("numfile")

There are simply hundreds of Excel formulas and functions which perform all kinds of strange and interesting needs. The chances are if there is anything you need Excel to do then it can be done so give it a go!