The FIND function can be used with spreadsheets to locate a character or a string within another cell. It generally locates the starting point of a string. The syntax for the FIND function for usage in spreadsheets is as follows:

=FIND(”find_text”,”within_text”,start_num)

This tells the Excel FIND function to find text - thus a substring within text – thus the string and indicates where to look - thus the first position.

What will it return?

The FIND function returns the value which is associated with the start point of the string of characters within a cell.

If you want to locate a specific letter within a cell for instance, the letter “d” you will use =FIND(“b”,B1,1). By stating the starting point as 1 which is the start number in our syntax for the FIND function we ensure that we get the first instance of the letter “b” in the results. The FIND function as such is used for specific character finding and is thus more specific than the SEARCH function which is also available in Excel.

Quick notes about the FIND function:

• It is case sensitive.
• If no match is found a #VALUE! Error is returned.

What is the main difference between FIND and SEARCH?

The main difference lies in the fact that the SEARCH function is not case sensitive whereas the FIND function is. The FIND function does not support wild card usage where the SEARCH function does. As such the FIND function is more specific.

View our free online training video on using the FIND function in spreadsheets.

Working with financial software such as Excel often entails dealing with external links which can include links to another workbook or links from another workbook to the current workbook. This is one of the features that make Excel an excellent financial software package.

Oftentimes external links can pose risks for an accountant working with financial software and such a few tips are shared below to help you create and maintain external links.

Linking data from another workbook to a current workbook

To link data in the above manner you will make use of a formula with the following syntax:

[workbook_name] [worksheet_name] !Reference

The workbook name will be that of the source workbook. This is the workbook where the data will be linked from and the worksheet name is the worksheet in that source workbook from where you will link from to the current workbook and worksheet. The exclamation mark acts as divider between the worksheet reference and the specific cell reference. You will input the name of the cell reference or more specifically range in the reference section. The workbook with the formula is called the dependent or destination workbook. If a source workbook is already linked to another workbook it is recommended that you rename it.

Steps for creating an external link

First open the workbook or the worksheet where the formula should be created in and then select the cell where the formula must appear. In the Formula bar you will then type = which will start the specific formula. Now activate the source workbook and then the relevant worksheet. Go to the cell that holds the information to be used from that specific worksheet and press Enter on the keyboard. The result will then appear in the cell selected in the worksheet where you want to create the formula in.

Maintaining links between workbooks

Select Edit on the Menu bar and then Links. Select the source workbook from the Source file list (this is the workbook that will be used to redirect links from). Now click the Change Source option and select the relevant workbook (this is the workbook where you want links to redirect to – also called the destination or dependent workbook). Select OK. This will return you to the Links window and then select OK again.

View our online video on working with External Links for a step by step guide on working with external links in Excel financial software.

Users often struggle with Excel spreadsheet problems caused by the hiding of cells, rows or columns.

Although and exceptionally useful feature, if a user doesn’t know how to hide or unhide specific cells or columns spreadsheet problems can follow. We will look at these functions to avoid spreadsheet problems below.

Ways to hide or unhide columns or rows

Select the row or column that you want to hide and then select Format on the Menu bar. Select Row or Column and then Hide. As an alternative select the specific row or column to hide, right-click, and select Hide.

If you want to unhide a column you would select the two columns adjacent to the hidden column and then select Format on the Menu bar, then select Column. Lastly select Unhide. The same steps are followed to unhide a row or rows except for selecting the rows, then Format, Row, Unhide.

What is important to note is that even though the column or row is hidden it will still be included in a calculation range. If you for example hide Column C and you use Autosum for the range A:D the entries in column C will still be used for the calculation.

Hiding Sheets

It is also possible to hide sheets by following the same procedure although the right-click option does not work with sheets. Simply select the sheet, then Format on the Menu bar and then Sheet. Select Hide or Unhide as required.

Why is the Unhide function grey?

If there are no hidden rows, columns or sheets then the unhide function is grey and cannot be used.

How to hide single cell content

Select the relevant cell and then select Format on the Menu bar. Select Cells and then the Number tab on top of the current window. Scroll down and select Custom. In the Type field you will then type three semicolons for example ;;;

You will now see that the content of the selected cell is hidden. Note that the content will still be visible in the formula bar although it is hidden in the cell. To unhide simply follow the same steps up to Number tab and then select the appropriate display. View our training videos on handling spreadsheet problems and for step by step tutorials.

When you import data or copy content from another application into Excel spreadsheets more often than not, you will be left with some characters that you want to remove. The Left, Right and Mid functions available in Excel are useful for this purpose. You can also use the Left, Right and Mid functions to cut up the content of a specific cell in order to use it for another purpose. As such these are excellent functions. If you understand their uses it will allow you to manipulate the content of cells to ensure accurate and usable Excel spreadsheets.

When to use each of the functions in Excel spreadsheets

If you import information from another application and you want to for instance, split up the data you will use the functions as follows:

RIGHT: to identify and remove unwanted characters on the left side of the data that you want to keep.

LEFT: to identify and remove unwanted characters on the right side of the data you want to keep.

MID: to identify and remove unwanted characters in the middle of data you wish to retain.

Not only can you remove characters, but you will also be able to copy those characters to cells where you want and need them. You may for instance, import a specific set of account numbers into a worksheet and now wish to make the data usable for comparison with other reports. The account numbers are made up of for instance city, customer number, and then the department number. If we want to separate the information to keep or copy the information for the city or the department to other worksheets or cells we can make use of the above functions.

View our training video on the steps to use the LEFT, RIGHT and MID functions in Excel spreadsheets.

Excel offers several useful functions to calculate averages in Excel models. We will briefly discuss the statistical functions available to ensure accurate Excel models.

The statistical functions are useful for the location of the mean, median as well as mode for a specific data set. Their main purpose is to identify the values set upon which the calculation will then be based. Averages for example, can be calculated through a process whereby the sum of values with a set is divided by number of values of present.

If you are a lecturer you will appreciate the ability to find averages in Excel models. It is a simple process whereby Excel adds up the values of a range as selected by the user. This is then divided by the number of cells in that selected range. Not only can the average for a group exam be established, but it can be applied to any situation where you require the average including that of prices, speed, number of respondents, temperatures, and time taken to complete a task etc.

You should however, proceed with caution when it comes to dates since the average for a date range can only be obtained if all the entries in that range are dates. Just a note on the not completely blank cells – if there are blank cells within a range you should proceed with caution as cells with spaces are not seen as empty cells. In general through it should also be noted that the Average function ignores completely empty cells as well as cells that contain text or logical values. Also note that any arguments which contain both text and numbers cannot be calculated in the normal way. For this purpose you should make use of the AVERAGEA function.

Syntax for the use of the average function is:

=AVERAGE(data set range)

A formula will thus look like this:

=AVERAGE(B3:B30)

(Thus calculate the average for cells in the range B3 to B30).

To learn more about the use of statistical functions such as AVERAGE and AVERAGEA in Excel models view our training section.

Excel offers various spreadsheet auditing tools including that of the spreadsheet auditing toolbar. The main function is to help you identify cells worked with and for which purposes as this will help you track errors and find the cause of the errors. The spreadsheet auditing toolbar is thus most helpful to identify causes of errors and to then rectify them. It can be viewed by following a few simple steps:

· On the Menu bar select Tools and then Auditing where after you select Show Auditing Toolbar or as alternative select View on the Menu, then Toolbars and select Formula Auditing from the dropdown menu.

The Trace Precedents button on the toolbar is used for identification of cells being used for a formula. When you click on a cell and then on the Trace Precedents option a blue line will appear that will indicate what cells are used in the formula in the highlighted cell through the use of line arrows. If you want to remove the arrows simply select Remove Precedent Arrows or make use of the Remove All Arrows option.

What if the cell selected looks at a worksheet other than the one currently on?

A dotted line appears and you can be taken to the relevant worksheet by double clicking on the dotted line. The GO TO option dialogue box opens and you can select the worksheet where you want to go to. Once selected, it will take you to the relevant spreadsheet.

You can follow the steps through our Tips & Tricks Video on using Spreadsheet Auditing. The short video also addresses other buttons on the auditing toolbar such as the Trace Dependants tool which is useful for identifying patterns in a worksheet.