The many uses of VLOOKUP

Length: 1 minutesComplexity: Easy

Vlookup can be used in a variety of areas and is therefore a very important function that is well worth getting to know. Its uses include: <ul> <li>Joining/ merging databases and tables e.g. attach employee names to their salaries, address details etc</li> <li>Finding missing items (useful for reconciliations)</li> <li>Comparing 2 columns (useful for reconciliations)</li> <li>Extract information from data to be used regularly in reports</li> <li>Let Excel guestimate an answer</li> <li>Create categories for employee performance, grouping of sales, grouping employees into age brackets</li> <li>Age invoices, stock and debtors into bands- aging anything</li> <li>Sales commission bands</li> <li>Tax bands</li> <li>Different interest rates at different bank balance levels</li> <li>Create costs that effect certain months depending on business activity</li> <li>Calculate step up costs and fixed costs in a financial model</li> </ul> Once you understand how VLOOKUP works, you will also understand better how Excel's more complex functions work and you will find that the other ones are a little easier to learn. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Protect cells but allow filter/ pivot table/ sort

Length: 20 minutesComplexity: Easy

If you need to protect a sheet (stop people changing inputs and formula) but you want to allow them to continue to use the Sort, Filter and Pivot Table options, you need to use different settings. For the most part these options don't affect the numbers but it would be difficult for a user to work with the data if they can't use these tools on a protected sheet.  

Trick: Protect rows and columns without protecting sheet

Preview

Length: 20 minutesComplexity: Easy

The problem with the Protect Sheets is that it does apply some limits and some users want everything to be open EXCEPT that they don't want users to insert or delete rows and columns (this is a very common cause of spreadsheet errors or 'templates' corrupting). This is a trick to disable the insert/ delete option without protecting the sheet and no VBA. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

How to protect an Excel Spreadsheet from opening

Length: 20 minutesComplexity: Easy

At a file level you can put a password on so that it is required before the spreadsheet is opened. So when someone wants to open the file, they first must enter the correct password. This means that only people who know the password can open/ change the file. <img class="size-full wp-image-5673" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-1.jpg" alt="Different levels of protecting excel spreadsheets" width="857" height="636" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Hide and protect a sheet in a spreadsheet

Length: 20 minutesComplexity: Easy

If you are worried about changes to the structure of your workbook (the sheet names, number of sheets, the order they are in, whether they are hidden or visible etc), you can even protect this (and sometimes this is recommended when you want to hide the source information).

VLOOKUP explained in simple terms

Length: 5 minutesComplexity: Easy

Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP

VLOOKUP Examples

Length: 20 minutesComplexity: Easy

VLOOKUP Examples <h2>VLOOKUP Ex1</h2> In this example we want to join the address list of our students with a list of students who have not paid their fees to determine who has money outstanding. We also want to see whether our address list is complete by checking if everyone who owes money is on the address list. <ul> <li>In cell C8 use VLOOKUP to link the address list (Column A to C) to the outstanding bills (columns G to L) list and pull through the total outstanding bill.</li> <li>In L8, use a VLOOKUP to check whether all the students listed as having money outstanding are shown on the Address List.</li> </ul> <h2>VLOOKUP Ex2</h2> In this example we have 2 lists which contain similar information. The problem is that each list contains some unique items and we need to know which items are unique in each list. <ul> <li>Using VLOOKUP, in column G, create a formula that checks whether the transaction ID in column B, exists in the transaction ID of List 2 (column L)</li> <li>Again using VLOOKUP, in column Q check whether the transaction ID in column L exists in List 1 (column B)</li> </ul> <h2>VLOOKUP Ex3a & B</h2> In this example you receive regular reports on the cash flows for a project. However, each month and each project can have varying amounts of data. You want to do a simple calculation of the total receipts to the total payments (D55 row). Ideally you want to get the key lines of information to pull through to the same row each time so that your ratio will work each time. <ul> <li>Use VLOOKUP in cell D46 to pull through the amount for Period 1 (column D) for the cell in C46 (Total Opening Bank Balance). Build the formula so that it is easier to copy and paste elsewhere. Also make sure it includes the database up to the red line as this is the maximum amount of data we expect.</li> <li>Finish the rest of the yellow block.</li> <li>As an alternate to entering the column number each time into the VLOOKUP formula, try linking to the numbers in row 45. This saves time in setting up big sheets and is easy to change.</li> <li>Compare your answers in D55 row to the answers in D57 row</li> <li>Now copy the yellow block (D46 to J51) and paste it into the relevant section on the next sheet (VLOOKUP Exb- Anchor Info).</li> <li>Note that the data in the blue cells is on different lines but your formula should correctly pull through the information.</li> <li>To check compare the answers in Row 55 to row 57</li> </ul> <h2>VLOOKUP Ex4</h2> Simple question. Why are the VLOOKUP’s in B14 and C14 giving an error message when student A111 clearly exists in the table? <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Protect the source data of a pivot table

Length: 20 minutesComplexity: Easy

It is common for users to send summarised reports in the format of Pivot Tables where confidential information is hidden in the source data, but it is assumed that it is not visible in the Pivot Table. However, even if you created the pivot table from information in another workbook, Excel will remember the data source. So if you want to make sure that no one can see the raw data in a Pivot Table you need to follow some steps.

SUMIFS et al Examples- partial matches

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 11</h2> In this example we have a listing of job descriptions that aren't standardised. We are trying to count how many people match the general descriptions in D4 to H4. <ul> <li>In D5 to H5, count how many people have the descriptions in D4 to H4 in their job title/ description</li> <li>In D6 to H6, count how many people DON'T have the description in their title</li> <li>In D9 count how many people have General Labour but NOT General Labour Supervisor in their description</li> <li>In E9 count how many people have the word Skilled in their description ignoring the Semi Skilled and Skilled Supervisor</li> </ul> <h2>SUMIFS et al Ex 12</h2> In column H we have a partial lookup to pull through the selling price of a product based on a shortened version of the name that the market knows them as. The problem is that some shortened versions may be a match for more than one product. In I10 to I34, count how many matches there are for the shortened name. <h2>SUMIFS et al Ex 13</h2> In this example we want to pull through the employees matching email address. However, the 2 systems use different versions of a name. HR has separate columns for First Name and Surname while IT have a combined Initial and Surname. In D4 column we have used a partial VLOOKUP to pull through the email address, but in E4 column we want to build a count to see how many matches there would have been for that surname (have a look in the VLOOKUP function to see what it is looking at). To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Protect cells with different passwords to allow levels of authority

Length: 20 minutesComplexity: Easy

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don't and they can't change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells.  

Build your first VLOOKUP

Length: 20 minutesComplexity: Easy

Build your first VLOOKUP and understand what each component means and how to use it.

Handling Error Messages in Excel

Length: 20 minutesComplexity: Easy

Handling Error Messages in Excel Excel treats error messages as a serious issue. So serious in fact that if you have one DIV!0 for example, all cells linked to it change to error messages. You can tell Excel to ignore this. As shown below in D7, sometimes there are error messages in Excel which are correct but we need to ignore them. In this case the other income is 0 so the DIV/0 is correct. But this error affects other cells lower down and we would prefer if it was changed to a 0. IFERROR is fairly new to Excel. The syntax is simple in that you <ul> <li>Tell it where to look, and then</li> <li>What must happen if it is an error (note that you can't control what happens if it is not an error)</li> </ul> <img class="aligncenter size-full wp-image-8933" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/07/IFERROR-1.png" alt="IFERROR " width="841" height="548" /> <h2>IFERROR Ex 1</h2> In C8 to C and L8 to L35 we have VLOOKUPs looking for mismatches. The problem is that in C85 and L36 we need to add up the totals but because of the N/A’s we can’t add it. Correct the formulas in C8 to C84 and L8 to L35 so that if it is an error it results in a zero. <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

Lock cells but allow colour formatting or comments

Length: 20 minutesComplexity: Easy

You can lock cells but allow colour formatting or comments which don't affect the security of the formula but allow a user to provide some sort of feedback to you. Note that adding comments is different from formatting so it requires a different setting.  

Some Useful Tools when protecting spreadsheets

Length: 20 minutesComplexity: Easy

When you are working with password protection, you often need to differentiate which cells are inputs and which are formula. You can manually do it (check every cell manually) or use some of Excel's inbuilt tools to make the process faster.

SUMIFS et al Examples- multiple conditions

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 8</h2> In this example you want to complete the matrix in E346 to I349 (yellow cells) based on the data above. Using SUMIFS and AVERAGEIFS, link the criteria and criteria ranges so that they pull from the cells in D346 to D349 and E345 to I345. Your sum in F351 should equal the amount in E342 <h2>SUMIFS et al Ex 9</h2> In row 5 you have a row of information that groups periods into common time frames e.g. Jan to June has a 1 indicating that these all belong to the first half year, and the next 6 months have a 2 which indicates they belong to the second half year. In D18 to G20 we want a formula that will group all the periods that belong together based on cells D15 to G15. So anything that has a one in row 5 must be summed into D18, D19 and D20 and so on. Steps <ul> <li>Create a SUMIF in cells D18 to D20 that will add the relevant information together based on the numbers in row 5</li> <li>Now change the numbers in row 5 so that they represents quarters e.g. Jan to March should have a 1 in row 5, April to June should have a 2 etc.</li> </ul> <h2>SUMIFS et al Ex 10</h2> In this example we have calculated which bank account numbers are duplicated (column E). In column F we want to work out the instance number of the bank account i.e. the first time we see the bank account number, the second time etc. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Lock Cells but allow insert or delete of columns/ rows

Length: 20 minutesComplexity: Easy

We find that most problems are caused by users inserting or deleting rows and columns when this is not allowed, so we normally don't allow this option. But if you want to lock cells but allow insert or delete of columns/ rows then this is the way to do it. <img class="aligncenter size-full wp-image-9594" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Lock-Cells-but-allow-insert-or-delete-of-columns-rows-1.png" alt="Lock Cells but allow insert or delete of columns/ rows" width="274" height="308" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Lock cells but allow colour formatting or comments

Preview

Length: 20 minutesComplexity: Easy

You can lock cells but allow colour formatting or comments which don't affect the security of the formula but allow a user to provide some sort of feedback to you. Note that adding comments is different from formatting so it requires a different setting. <img class="aligncenter size-full wp-image-9590" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Lock-cells-but-allow-colour-formatting-or-comments-1.png" alt="Lock cells but allow colour formatting or comments" width="1068" height="847" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

SUMIF et al Examples 2

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 4</h2> In this example you are sending a file off to the bank to make the salary payments. However, you just want to check that all the bank account numbers are unique in case of finger trouble or worse phantom employees. Using a COUNTIF, in E7 column, create a formula that will check whether the bank account number in column B for the employee appears as anyone else’s bank number as well. <h2>SUMIFS et al Ex 5</h2> In our example we have a dump of the cash flow report from a system. You will notice that SUMIF a and SUMIF b have the same information but they are not on the same lines e.g. the Total Closing Bank Balance on SUMIF a is on row 38 and for SUMIF b it is on row 31. We want to determine the ratio of receipts to payments on row 55 but would normally have to create the formulas on each sheet to take into account the row numbers being inconsistent. We want formula in rows 46 to 51 that will search through the information and pull out the information we need. Once we have the information static, we can then run the ratios. Steps <ul> <li>On SUMIF a create a formula in D46 to J51 that will find and add the items described in C46 to C51 for each period.</li> <li>Once you have create this you should be able to copy and paste these formula into SUMIF b (same area) and it will work even though the rows with the relevant information are not identical.</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Protect cells with different passwords to allow levels of authority

Length: 20 minutesComplexity: Easy

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don't and they can't change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells. <img class="aligncenter size-full wp-image-5678" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-6.jpg" alt="Different levels of protecting excel spreadsheets" width="915" height="712" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Trick: Allow user to only see their information in Excel

Preview

Length: 20 minutesComplexity: Easy

This is a trick to be able to send out one spreadsheet which contains everyone's information, but set it up so that a single person can only see their data and not the other users. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

SUMIFS et al Examples- Bigger or less than

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 6</h2> In this example we want to automatically count how many months the Gross profit is negative and how many months are positive <ul> <li>In E13, use COUNTIF to count how many numbers in row 10 are negative numbers. In this case the criteria needs to be in quotes e.g. “<0”</li> <li>In E14 use COUNTIF to count how many numbers are positive</li> </ul> <h2>et al Ex 7</h2> In this example we want to avoid a ‘unique’ number being used twice. <ul> <li>In Column G (yellow cells), create a formula that will check if Transaction ID (column B) for that row, is duplicated somewhere else in column B (that is the invoice number has been entered twice accidentally)</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

VLOOKUP explained in simple terms

Free Preview

Length: 5 minutesComplexity: Easy

Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP

Remove all formulas from entire spreadsheet

Preview

Length: 20 minutesComplexity: Easy

If you need to protect a sheet (stop people changing inputs and formula) but you want to allow them to continue to use the Sort, Filter and Pivot Table options, you need to use different settings. For the most part these options don't affect the numbers but it would be difficult for a user to work with the data if they can't use these tools on a protected sheet. <img class="aligncenter size-full wp-image-9595" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Protect-cells-but-allow-filter-pivot-table-sort-1.png" alt="Protect cells but allow filter pivot table sort" width="274" height="308" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Protect only certain cells/ formula from everything except input changes

Length: 20 minutesComplexity: Easy

Another form of protection is to protect certain cells from accidental or purposeful changes. This protection level stops everything except allowing you to enter information into a cell. You can't format cells, insert or delete columns or rows, use Pivot Tables or anything else. This can be added to a spreadsheet that is workbook protected or not. <img class="size-full wp-image-5675" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-3.jpg" alt="Different levels of protecting excel spreadsheets" width="660" height="661" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Protect cells with different passwords to allow levels of authority

Length: 20 minutesComplexity: Easy

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don't and they can't change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells.  

SUMIF et al Examples 1

Length: 20 minutesComplexity: Easy

<h2>SUMIFS et al Ex 1</h2> In B11 to F26 we have data per sales person. In C32 to C34 and F32 to F34 we want to summaries the Goals and Sales based on the business description. In C35 and F35 add up the totals and check that they match to the totals in C27 and F27. <h2>SUMIFS et al Ex 2</h2> In this example you want a summary of students bills based on the monthly amounts outstanding. <ul> <li>In B8 column, use COUNTIF to calculate how many times the student number in A8 appears in the table of B24 to B48.</li> <li>In C8, D8 and E8, use SUMIF to add up all the relevant bills for the student by extracting it from the database below.</li> <li>Check that your answers match up.</li> </ul> <h2>SUMIFS et al Ex 3</h2> You have 2 lists on your spreadsheet with transactions. Although they should be exactly the same you have discovered each list is missing some information that is contained in the other list. You need to quickly determine which transactions are not common or repeated too many times. Steps <ul> <li>In Column G, create a formula that COUNTS IF the transaction ID as per column B, also exists in the second list (column L)</li> <li>In Column Q, create a formula that COUNTS IF the transaction ID as per column L, also exists in the second list (column B)</li> <li>Using the autofilter, check each list to see the items that are not common or appear too often.</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Build your first VLOOKUP

Free Preview

Length: 20 minutesComplexity: Easy

Build your first VLOOKUP and understand what each component means and how to use it.

VLOOKUP explained in simple terms

Free Preview

Length: 5 minutesComplexity: Easy

Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP

Some Useful Tools when protecting spreadsheets

Length: 20 minutesComplexity: Easy

When you are working with password protection, you often need to differentiate which cells are inputs and which are formula. You can manually do it (check every cell manually) or use some of Excel's inbuilt tools to make the process faster. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Protect only certain cells/ formula from everything except input changes

Length: 20 minutesComplexity: Easy

Another form of protection is to protect certain cells from accidental or purposeful changes. This protection level stops everything except allowing you to enter information into a cell. You can't format cells, insert or delete columns or rows, use Pivot Tables or anything else. This can be added to a spreadsheet that is workbook protected or not.

Protect only certain cells/ formula from everything except input changes

Preview

Length: 20 minutesComplexity: Easy

Another form of protection is to protect certain cells from accidental or purposeful changes. This protection level stops everything except allowing you to enter information into a cell. You can't format cells, insert or delete columns or rows, use Pivot Tables or anything else. This can be added to a spreadsheet that is workbook protected or not. <img class="size-full wp-image-5675" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-3.jpg" alt="Different levels of protecting excel spreadsheets" width="660" height="661" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Build your first VLOOKUP

Free Preview

Length: 20 minutesComplexity: Easy

Build your first VLOOKUP and understand what each component means and how to use it.

Protection Examples

Length: 20 minutesComplexity: Easy

<h2>Protection Ex 1</h2> <ul> <li>Find all the Input Cells and make them blue and unlock them (watch out for the overwritten formula - you need to fix it)</li> <li>Protect the sheet so that you can widen rows and columns if necessary</li> <li>Create a different password for the Inflation Rate in cell C6</li> <li>Make sure no one can enter a marketing percentage less than 10%</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Lock Cells but allow insert or delete of columns/ rows

Length: 20 minutesComplexity: Easy

We find that most problems are caused by users inserting or deleting rows and columns when this is not allowed, so we normally don't allow this option. But if you want to lock cells but allow insert or delete of columns/ rows then this is the way to do it.  

VLOOKUP Examples- fuzzy logic

Length: 20 minutesComplexity: Easy

<h2>VLOOKUP Ex11</h2> In D4 to D52 we need to pull through the matching email address from column I and J. You can use the surname in column B as the lookup but it will need to be a partial lookup as column I has the persons name and initial. Because of similar names, in column E check how many times Excel would complete the partial match (hint- you can use the same logic in COUNTIF and COUNTIFS- this is covered later as well) To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

SUMIF et al Examples

Length: 20 minutesComplexity: Easy

SUMIFS et al Examples- sales by area, recon lists, duplicate bank accounts

Hide formulas in Excel

Preview

Length: 20 minutesComplexity: Easy

When you protect a sheet you typically stop users from changing your formulas. However, you can also control whether a user can see a formula on a cell. If you want the formula to be hidden you need to follow a slightly different process. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.