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>.

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. 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.

Protect cells with different passwords to allow levels of authority

Preview

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>.

VLOOKUP Examples 1- categorise, bands

Length: 20 minutesComplexity: Easy

<h2>VLOOKUP Ex5</h2> In this example we want to take the employees individual ratings and categorize them into one of 5 bands. <ul> <li>In D11 to D30, create a formula that will lookup the rating in column C to match the appropriate description from the table in K11 to L15 so for example a rating of 3.6 should bring back an ‘Inconsistent’ description.</li> </ul> <h2>VLOOKUP Ex6</h2> You need to group your employees into various age categories for your reporting. <ul> <li>In E14 column, create a lookup the compares the age in column D, to the table in A4 to B8 and brings back the relevant description, e.g. an age of 36 would return ‘Between 35 and 50’</li> </ul> <h2>VLOOKUP Ex7</h2> In this example you are calculating the tax liability based on various salaries. The tax table is provided (D20 to F26) and a series of salaries are provided (A30 to A34). You need to calculate what the tax liability will be on these amounts. <ul> <li>In B30 use VLOOKUP to determine what the fixed portion will be based on the salary in A30 and the tables shown in the A30 table</li> <li>In C30 use VLOOKUP to determine what the % for that salary level</li> <li>In D30 use VLOOKUP to determine what the lower limit is of the band (HINT: You can use 1 as your column number)</li> <li>In E30 calculate the amount to apply the % to e.g. a salary of R150 000 would be R150 000 less the band it falls into (R140 000) so R10 000.</li> <li>In F30 calculate the % portion of the tax (based on E30 and C30)</li> <li>In G30 pull through the fixed portion from B30</li> <li>In H30 add the % portion and fixed portion together to get a total tax bill</li> <li>Copy down to row 34</li> <li>In H35 calculate the total tax bill and compare it to H36. If they are not the same find out what the problem is</li> </ul> Why is the VLOOKUP’s in J8 to L8 pulling through information relating to SP010 instead of the correct SP0101? 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

Preview

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 column/ row size change

Length: 20 minutesComplexity: Easy

When you lock cells you need to be particularly careful about how much you lock. If the size of the numbers within a cell may change significantly, a locked spreadsheet may not allow the user to see the numbers as they will appear as ###### and the user won't be able to resize the column or row.

Trick: Protect rows and columns without protecting sheet

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>.

VLOOKUP Examples 2- categorise, bands

Length: 20 minutesComplexity: Easy

<h2>VLOOKUP Ex 8</h2> Why is the VLOOKUP’s in J8 to L8 pulling through information relating to SP010 instead of the correct SP0101? Before you watch the solution, have a look again at the sort order of column A. Does it make sense to you? <h2>VLOOKUP Ex 9</h2> Create the following spreadsheet with the relevant inputs In the first yellow block, use a VLOOKUP to determine the interest rate to use. In the second yellow block calculate the interest earned 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

Preview

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). <img class="aligncenter size-full wp-image-5680" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-8.jpg" alt="Different levels of protecting excel spreadsheets" width="719" height="409" /> 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).

Trick: Allow user to only see their information in Excel

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>.

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.

Hide formulas in Excel

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.

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.

Allow only certain values into a cell

Length: 20 minutesComplexity: Easy

Everything before is focused on allowing or not allowing a user to do something in a cell. So they either can change a cell or not. But what if you want them to change the contents of a cell but limit the options e.g. they must enter a number bigger than 10%. <img class="aligncenter size-full wp-image-5684" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-12.jpg" alt="Different levels of protecting excel spreadsheets" width="892" height="554" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

Hide formulas in Excel

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.

Hide formulas in Excel

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>.

Trick: Protect rows and columns without protecting sheet

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.  

VLOOKUP explained in simple terms

Length: 5 minutesComplexity: Easy

The first place to start is what VLOOKUP is trying to achieve and to understand it in human terms. Look at the table below and tell me the number of points that 'Manchester United' obtained in the 2013/ 2014 season? <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-explained-in-simple-terms.jpg"><img class="aligncenter size-full wp-image-3110" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-explained-in-simple-terms.jpg" alt="vlookup-explained-in-simple-terms" width="350" height="479" /></a> Congratulations you have performed your first VLOOKUP. The process you followed is exactly what Excel will do to perform a V(ertical) LOOKUP. Consider the process your brain followed and look at the picture below. Your brain: <ol> <li>Identified what is being looked up (Manchester United)</li> <li>Identified where the list of team names were and looked down them</li> <li>When it found the exact match it stopped and looked across the row</li> <li>When it got to the 9th column it recorded that Manchester United received 64 points in the 2013/ 2014 season.</li> </ol> <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-introduction.gif"><img class="aligncenter wp-image-3107 size-full" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-introduction.gif" alt="vlookup-explained" width="360" height="480" /></a> This is all that is required to do a VLOOKUP in Excel. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/beginner-excel-course/">here</a>.

Some Useful Tools when protecting spreadsheets

Preview

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>.

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). <img class="aligncenter size-full wp-image-5680" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-8.jpg" alt="Different levels of protecting excel spreadsheets" width="719" height="409" /> 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 cells but allow filter/ pivot table/ sort

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>.

Build your first VLOOKUP

Length: 20 minutesComplexity: Easy

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

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. <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>.

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.  

Allow only certain values into a cell

Preview

Length: 20 minutesComplexity: Easy

Everything before is focused on allowing or not allowing a user to do something in a cell. So they either can change a cell or not. But what if you want them to change the contents of a cell but limit the options e.g. they must enter a number bigger than 10%. <img class="aligncenter size-full wp-image-5684" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-12.jpg" alt="Different levels of protecting excel spreadsheets" width="892" height="554" /> 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

Length: 5 minutesComplexity: Easy

The first place to start is what VLOOKUP is trying to achieve and to understand it in human terms. Look at the table below and tell me the number of points that 'Manchester United' obtained in the 2013/ 2014 season? <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-explained-in-simple-terms.jpg"><img class="aligncenter size-full wp-image-3110" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-explained-in-simple-terms.jpg" alt="vlookup-explained-in-simple-terms" width="350" height="479" /></a> Congratulations you have performed your first VLOOKUP. The process you followed is exactly what Excel will do to perform a V(ertical) LOOKUP. Consider the process your brain followed and look at the picture below. Your brain: <ol> <li>Identified what is being looked up (Manchester United)</li> <li>Identified where the list of team names were and looked down them</li> <li>When it found the exact match it stopped and looked across the row</li> <li>When it got to the 9th column it recorded that Manchester United received 64 points in the 2013/ 2014 season.</li> </ol> <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-introduction.gif"><img class="aligncenter wp-image-3107 size-full" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/vlookup-introduction.gif" alt="vlookup-explained" width="360" height="480" /></a> This is all that is required to do a VLOOKUP in Excel. Watch the video clip of how we create the VLOOKUP for the above example <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>

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

Trick: Protect rows and columns without protecting sheet

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.  

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. <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>.

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.  

Allow Spreadsheet to be viewed but not changed (read only)

Preview

Length: 20 minutesComplexity: Easy

If you want to have more control over the people that can open the file and only view it versus people who can open the file AND make changes you need to add a Password to Modify. <img class="aligncenter size-full wp-image-5674" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-2.jpg" alt="Different levels of protecting excel spreadsheets" width="288" height="171" /> 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

Length: 20 minutesComplexity: Easy

Build your first VLOOKUP and understand what each component means and how to use it. The VLOOKUP command allows you to LOOKUP information and based on what it finds, bring some other information back. VLOOKUP stands for Vertical Lookup and will search down a column and then extract information from another column. You can also use the HLOOKUP (Horizontal Lookup) command which searches along a rows (covered later). For now we are going to focus on finding EXACT matches. In this example we want to find a certain student and pull through their result and symbol. The yellow cells should bring through the results relating to student number A111. <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/Vlookup-exact-match-1.jpg"><img class="aligncenter size-full wp-image-3519" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/Vlookup-exact-match-1.jpg" alt="Vlookup-exact-match" width="479" height="351" /></a> Based on a step by step process we need to tell Excel the following <h2>What must you look up and where will you find it</h2> The first part is telling Excel what it must look up and where it must go to find it. In the example below we are in the first yellow cell and have told Excel that the Lookup Value is in cell A14. Now that Excel knows that it is looking for the contents of cell A14 ( student A111 in this case) it needs to know where to go and look. To a human this is obvious, but Excel has thousands of columns and rows where it can look so we need to specify where. In this case it is the table shown in cells A4 to C8. <h2>Once you have found it, what must you bring back</h2> Once Excel has found the student you are looking for, it needs to know what to bring back for you. In this case we want to see the students percentage. The way to tell Excel what to bring back is by specifying which column OF THE TABLE ARRAY you are interested in. <h2>The answer</h2> When you click OK you get an answer of 34%. Excel started at the top at student number A100 and went down cell by cell until it found student A111. It then went to the right to the second column and extracted the result. <a href="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/Vlookup-exact-match-4.jpg"><img class="aligncenter size-full wp-image-3522" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2014/11/Vlookup-exact-match-4.jpg" alt="Vlookup-exact-match" width="389" height="286" /></a> <strong>To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/intermediate-excel-course/">here</a>.</strong>