One key piece of software that is often underused and misunderstood is Excel. The ability to fully comprehend its package and its functions will streamline your business and help support its growth. There are five must know Excel functions that every business should be able to understand and use as if it were second nature.

Which Five Functions and Why?

Knowing what Excel is capable of and how to achieve its full capability is essential to making it work for you. At a first glance, a blank spreadsheet and its array of options can be overwhelming. But, once you know where to start, you really can do miraculous things with Excel. The top five functions that you must be able to use are:

• Count function
• Average function
• Text function
• Match function
• VLOOKUP function

These five functions can help your business make use of a wide array of data, track changes over time, find and provide totals, and help you fully understand what both your business and your customers are spending.

The count function allows you to see how many entries have been made. Not only can the function be used to give the total number of entries, but it can also be used to count specific types of entries.

You can set it to count entries with values less than 5 or greater than 10, for example. The function can also be used to count entries based on more than one set criteria, such as the number of blue entries that also have a value less than 6.

To use the basic count function, you need to type =count(start cell : end cell) into the formula bar. If you want to know the number of values in column A, from the first cell to cell 8, you type =count(A1:A8) and press enter. The count then shows under the filled cells in column A.

To use the count function to find the number of cells of a specific value, you need to change the formula to =countif, and add that value into the brackets by using ?, ? or = signs. For example, to find the number of cells in column A containing values less than 5 you would enter =countif(A1:A8; "?5").

Where multiple criteria are required the initial term is changed to =countifs and the additional information required is entered into the brackets. Therefore, to find the number of cells containing blue entries with values less than 6; where the colours are in column A and values are in column B, you would enter =countifs(A1:A8; "blue"; B1:B8; "?6").

Knowing how much you spend on individual items, or each day, week, or month has its uses, particularly for highlighting particularly high or low values. However, the ability to be able to quickly and effectively find average costs or incomes is essential for forward planning.

If you know that your average spending increases in the early part of the year, you can judge your financial planning accordingly. The Excel average function allows you to notice such patterns by inputting specific details into the formula bar.

To find the average of a set of values you need to type =average(start cell : end cell). To find the average of the values in column A from cells 1 to 9, you would type =average(A1:A9) into the formula bar and press enter.

You can also choose to find the average of specific cells. This is achieved by typing in the specific cell numbers; for example =average(A1;A4;A9).

Understanding the Excel text function

The Excel text function is a useful tool for adding specific formatting to cells and enhancing the readability of your spreadsheets. It allows you to clearly show how values in the cells relate to your business.

While you may not need this information laid out clearly in front of you, others such as bank managers, investors, and new employees might find it particularly useful. It helps distinguish between hourly values, cash values, and customer numbers, to name but a few possible examples.

To add text to a value, you again utilize the formula bar at the top of the spreadsheet. Begin with =text, with the brackets containing the cell column and number and the text you wish to be added. To add a dollar sign in the first cell of column A, enter =text(A1; "$0.00").

More examples of the types of text values that can be incorporated and the formulas required can be found at support.office.com.

The match function allows you to search for a specific value within the spreadsheet. It is useful when you have a lot of data contained within the sheet and need to find an item quickly.

However, the formulas for this function are slightly more complex than the previous functions and how they are used depends on the type of information you have entered and the order in which it is entered.

The key to using the match function is to know how your information is organized. Depending on whether your cell values are in ascending, descending or any order you will need to add a specific value to your match formula:

• Ascending order - add the value 1 to the formula (this is the default setting)
• Descending order - add the value -1 to the formula
• No specific order - add the value 0 to the formula

The basic formula required for the match function is =match(the value being searched for; start cell:end cell; the additional number). For example, if you wanted to find the value 789 in column A, between cells 1 and 9, sorted in descending order, you would enter =match(789; A1:A9; -1). Further examples of how the match function works can be found on techonthenet.com.

The easiest way to understand the VLOOKUP function is as the next level up from the match function. Whereas the match function allows you to find one specific piece of information, VLOOKUP function allows you to use the information you have found to find related information in another column.

A very clear explanation of the function, as well as how this function can benefit businesses of any type is given on fiveminutelessons.com where it is explained in terms of looking up a specific sales person and then being able to pinpoint their specific sales figures within a different column.

To use the VLOOKUP function you need to enter the following formula into the formula bar: =vlookup(lookup_value; lookup_table_range; column; exact). The final parameter is optional and will affect the outcome should the initial value not be found.