# Microsoft Excel Essentials

The information on this page references the resource Party_Budget_Workbook_2 [XLSX, 11kB]. Download this workbook, if you haven’t already, and use it to work through the information below. This workbook has been formatted according to the instructions on the Document formatting page of this module.

One of the many useful ways to use Excel is to take advantage of the formulas and functions it can perform on the data.

You can create formulas in Excel to perform calculations, typically by referring to values in cells in your workbook. For example, we can add together the amounts paid and amounts owing to find the total cost of each item in the list using formulas. We will do this in column G, which you should first give the heading TOTAL_COST. Now click on cell G2 and type the = sign, then either select cell D2 or type D2, type the + sign and finally either select cell F2 or type F2. Pressing Enter should then give you the total cost, which is $100 in this case. If it doesn’t, check that you have entered the formula correctly by clicking on the cell where you entered it (ie. G2) and looking at the formula bar: You can always make edits to the formula in the formula bar if you need to. ## Reference types Something important to note when creating formulas in Excel is the different reference types. The default in Excel is a relative reference, meaning that any references to cells you make in your formula are relative to the location of the cell where the formula is. For example, the formula that we have just written is a formula that refers to the cells three places and one place to the left of it respectively. If we copy and paste the formula in cell G3, for example, the formula adds together the values in cells D3 and F3 instead to give a total cost of$300. This is very useful in instances such as this, when we are wanting to perform the same operation for different sets of cells. Indeed, we can now copy the formula to the rest of the cells in the TOTAL_COST column to find the total cost of the remaining items by clicking on cell G3, hovering the mouse over the bottom-right corner of the cell until the black + symbol shows, and double clicking. Doing this should make the page look like this: Alternatively, if you want the formula to remain exactly the same in the new cell you are copying it to you need an absolute reference. You can convert to this reference type by either manually adding $signs to the front of all the column and row references in the formula bar, or by highlighting it in the formula bar and clicking F4. Try doing this to the formula in cell G20: If you then press Enter and copy and paste the formula in cell I2, for example, the result should be the same total of$213 (delete the value in cell I2 when you have confirmed this).

Finally, you might sometimes want to use a mixed reference to keep either the row or the column absolute, but the other relative. You can do this by typing the $sign only in front of either the row or the column reference as appropriate, or by highlighting the formula and pressing F4 until you have the desired result. ## Built-in functions For anything beyond the simple calculations described above, making use of Excel’s built-in functions in your formulas is a must. The following sections outline a few of the most commonly used ones, but there are hundreds to choose from. You can find them by going to the Formulas tab and browsing through the lists of functions grouped into various categories, or by selecting Insert Function and searching for a function using a brief description. Either way, you can see details of the pieces of information (known as arguments) that are required to be entered as part of the function, and can click on Help on this Function for a detailed description of it if required. Alternatively, if you are not sure of the exact name of a function but know what it starts with you can type the = sign in a cell and then start typing to see a list of functions, which you can then select from by double clicking. You can also find a list of all functions, along with detailed descriptions, on this Microsoft Support page link. ## AutoSum functions The AVERAGE, COUNT and SUM functions are three commonly used functions when working with numbers. They are as follows: AVERAGE finds the average (mean) of values. It takes up to 255 arguments, which can be numbers, cell references, or ranges. COUNT counts how many values are numbers. It takes up to 255 arguments, which can be items, cell references, or ranges. SUM adds values together. It takes up to 255 arguments, which can be numbers, cell references, or ranges. Each of these functions can be performed by typing the function as part of a formula, or by using AutoSum or Quick Analysis. To type the AVERAGE function to average all the amounts paid, for example, first select the cell where you want the average displayed. Do this in cell D21 by typing the following (or at least start typing it, then double-click on the name of the function when it appears): =AVERAGE( You can then average the values in the column either by entering all the cell references as individual arguments or, more efficiently, by entering the range of cell references as a single argument (recommended). To do the latter either type D2:D20 or highlight the relevant cells D2 to D20; either way, the result when you press Enter should be$31.75 and the formula should look like this: To use AutoSum to perform the COUNT function to count of the number of items that still have amounts owing, for example, highlight cells F2 to F20 then click on the Formulas tab, select AutoSum and choose Count Numbers. A count of the total amount of numbers in the highlighted cells will appear in cell F21, although as this entire column was previously formatted as currency it appears as $4.00. To change the value to 4, which is the number of items still requiring some payment, simply reformat the cell as General using the method described previously. To use Quick Analysis to perform the SUM function to add together all of the total costs, for example, highlight cells G2 to G20 then click on the Quick Analysis icon in the bottom right hand corner: Select the Totals tab and choose Sum, and the sum of$1095.70 should appear in cell G21. You can edit any formulas created using AutoSum or Quick Analysis in the formula bar if needed.

## Logical functions

Logical functions, which check conditions and return results accordingly, are further examples of commonly used functions in Excel. Three examples of such functions are AND, IF and OR, which are often used together. Each function is as follows:

AND checks multiple conditions at the same time. It takes up to 255 arguments, which are the conditions, and returns TRUE if they are all true and FALSE if they are not.

IF makes a logical comparison and returns a value depending on the result. It takes three arguments; the logical expression to be evaluated (which can include AND, OR functions), the value to return when the expression is TRUE, and the value to return when the expression is FALSE.

OR checks multiple conditions at the same time. It takes up to 255 arguments, which are the conditions, and returns TRUE if any of them are true and FALSE if they are not.

As an example of using the IF and OR functions together, consider that we want to categorise the items in the list to see which ones were purchased from a supermarket and which were not. We will do this in column H, which you should first give the heading SUPPLIER_TYPE. Now click on cell H2 and type the following (or at least start typing it, then double-click on the name of the function when it appears):

=IF(

You then need to enter three arguments, the first of which is the logical test. In this case this requires use of the OR function, as you need to check whether the supplier is Woolworths or Coles or IGA (i.e. if it is any of the supermarkets). So the first argument, which you can create by typing or by a combination of typing and selecting cell C2, should be as follows:

OR(C2 = “Woolworths”, C2 = “Coles”, C2 = “IGA”)

The second and third arguments of the IF function will then be the values you wish to return if this is TRUE or FALSE respectively (it will be TRUE if any of the three conditions are TRUE, and FALSE if they aren’t). So enter a comma and a space after your previous argument and then type “Supermarket”, a comma and a space, and “Not supermarket”, for example. be sure to add a closing bracket at the end. When you press Enter the value in the cell should read ‘Not supermarket’, as this item was not purchased from a supermarket, and the formula should look like this: Copy this relative reference to the rest of the cells in the Supplier Type column to find the supplier type of the remaining items by clicking on cell H2, hovering the mouse over the bottom-right corner of the cell until the black + symbol shows, and double clicking.

As another example, consider that you can use the AND and IF functions together to indicate which supermarket items cost more than $20. Do this in cell I2 by typing the following formula, which checks whether an item was purchased from a supermarket (using the new value in cell H2) and cost more than$20 (using the total cost in cell G2), and returns text if it was and no text if it wasn’t: