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.
Formulas
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:
=IF(AND(H2= “Supermarket”, G2 > 20), “Supermarket item more than $20”, “”)
Again, you can copy this relative reference to the rest of the cells in column I to see the result for all items in the list.
Finally, three variations of the IF function that may be of interest are the AVERAGEIF, COUNTIF and SUMIF functions. Each of these functions test if cells in a certain range (the function’s first argument) meet a set criteria (the function’s second argument) and average, count or sum, respectively, those that do. For example, typing the following formula in cell B21 will count the number of items in the list that have been categorised as Food in the TYPE column:
=COUNTIF(B2:B20, “Food”)
This formula should give the result of 8.
If you’ve completed all the previous pages of this module, at the end of this page your spreadsheet should look something like this:
VLOOKUP
The function VLOOKUP can be useful when you want to look things up in a spreadsheet arranged as per the Party_Budget spreadsheet; that is, arranged with columns representing fields and rows representing records. For data arranged the other way around, you will need to either transpose it or to use HLOOKUP.
As an example, consider that we want to find out the date we paid for fruit for the party. To use VLOOKUP for this we first need to choose the cell where the result is to be displayed. Use B22 for example, although note you should first format this cell as a Date, using the method described in the Document formatting page of this module, to ensure that the value displays correctly for this particular example. You may also want to type the word Fruit in cell A22, to show that this is the item the date refers to (you can also use this to help in step 1 below). Now click in cell B22 and type the following (or at least start typing it, then double-click on the name of the function when it appears):
=VLOOKUP(
You will then be prompted to enter four arguments as follows:
lookup_value: this is, as the name suggests, the value you want to look up. In this case we want to find out the date we paid for the fruit, so “Fruit” is our look_up value. We can enter it like this (i.e. in quotes), or if you have typed Fruit in cell A22 then just click on that cell and the cell reference will appear as part of the function.
table_array: this is the range of cells that make up the search area. This needs to include both the column that the function will search in for the lookup_value (column A in this case), and the columns to the right of it where you want it to search for the value it will return (note that VLOOKUP only searches columns to the right, so you will need to rearrange your data if you wish to search in a column to the left). Since the dates are in column E we can just use the data up to and including this column, and all rows from 2 to 20 (we don’t want to search our heading row) as our table_array. To tell Excel this, first enter a comma and a space after your previous argument (“Fruit” or A22), then either type A2:E20, or highlight the relevant part of the table and again the array will appear as part of the function for you.
col_index_num: this is the column you want the function to search in for the result. The columns are numbered from left to right with the first column in the table_array being column 1, so since you want the function to search the fifth column in this case, the DATE_PAID column, you should enter a comma and a space after your previous argument (A2:E20) and then type the number 5.
range_lookup: this argument specifies whether you want the VLOOKUP function to find an exact match for your look_up value, or just the closest match. The default (if you don’t enter anything for this argument, or if you type TRUE or 1) is for it to find the closest match, which is sometimes helpful in the case of numbers, but which won’t work for a text lookup_value (as in this case) unless your data is sorted properly in ascending order by lookup_value. Much easier is to ensure it searches for an exact match instead, by entering a comma and a space after your previous argument (5), and then typing the word FALSE.
Now press Enter, and your VLOOKUP should return the result; 21/03/2019 as the date the fruit was paid for in this case. If it doesn’t, check that your formula looks like this:
or like this:
Note that a limitation of VLOOKUP is that if the look_up value occurs more than once, a result is only returned for the first occurrence. For example, typing the following formula in cell B23 will only return Coles as the supplier for the soft drinks, as this is the value associated with the first occurrence, even though they were also supplied by IGA:
=VLOOKUP(“Soft drinks”, A2:E20, 3, FALSE)
Another limitation is that VLOOKUP only searches for values in columns to the right of the look_up value; so we couldn’t look up the name of the item that cost $23.40, for example. However, this issue can be resolved by rearranging the columns as required.