VLOOKUP explained in simple terms

5 minutes Easy

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

Hide and protect a sheet in a spreadsheet

20 minutes 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).

Protect cells but allow filter/ pivot table/ sort

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

 

Hide and protect a sheet in a spreadsheet

20 minutes 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).

SUMIF et al Examples 2

20 minutes Easy

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

Build your first VLOOKUP

20 minutes Easy

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

Allow only certain values into a cell

20 minutes 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%.

Hide formulas in Excel

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

Protect cells with different passwords to allow levels of authority

20 minutes 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

20 minutes Easy

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

How to protect an Excel Spreadsheet from opening

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

VLOOKUP explained in simple terms

5 minutes Easy

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

Build your first VLOOKUP

20 minutes Easy

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

Trick: Protect rows and columns without protecting sheet

20 minutes 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

5 minutes 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?

vlookup-explained-in-simple-terms

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:

  1. Identified what is being looked up (Manchester United)
  2. Identified where the list of team names were and looked down them
  3. When it found the exact match it stopped and looked across the row
  4. When it got to the 9th column it recorded that Manchester United received 64 points in the 2013/ 2014 season.

vlookup-explained

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

To see the full course contents click here.

 

VLOOKUP explained in simple terms

5 minutes Easy

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

Allow only certain values into a cell

20 minutes 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%.

Build your first VLOOKUP

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

Vlookup-exact-match

Based on a step by step process we need to tell Excel the following

What must you look up and where will you find it

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.

Once you have found it, what must you bring back

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.

The answer

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.

Vlookup-exact-match

To see the full course contents click here.

Protect the source data of a pivot table

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

The many uses of VLOOKUP

1 minutes 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:

  • Joining/ merging databases and tables e.g. attach employee names to their salaries, address details etc
  • Finding missing items (useful for reconciliations)
  • Comparing 2 columns (useful for reconciliations)
  • Extract information from data to be used regularly in reports
  • Let Excel guestimate an answer
  • Create categories for employee performance, grouping of sales, grouping employees into age brackets
  • Age invoices, stock and debtors into bands- aging anything
  • Sales commission bands
  • Tax bands
  • Different interest rates at different bank balance levels
  • Create costs that effect certain months depending on business activity
  • Calculate step up costs and fixed costs in a financial model

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.

To see the full course contents click here.

Protect cells but allow filter/ pivot table/ sort

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

 

Some Useful Tools when protecting spreadsheets

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

SUMIF et al Examples 1

20 minutes Easy

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

VLOOKUP Examples

20 minutes Easy

VLOOKUP Examples

VLOOKUP Ex1

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.

  • 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.
  • In L8, use a VLOOKUP to check whether all the students listed as having money outstanding are shown on the Address List.

VLOOKUP Ex2

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.

  • 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)
  • Again using VLOOKUP, in column Q check whether the transaction ID in column L exists in List 1 (column B)

VLOOKUP Ex3a & B

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.

  • 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.
  • Finish the rest of the yellow block.
  • 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.
  • Compare your answers in D55 row to the answers in D57 row
  • Now copy the yellow block (D46 to J51) and paste it into the relevant section on the next sheet (VLOOKUP Exb- Anchor Info).
  • Note that the data in the blue cells is on different lines but your formula should correctly pull through the information.
  • To check compare the answers in Row 55 to row 57

VLOOKUP Ex4

Simple question.

Why are the VLOOKUP’s in B14 and C14 giving an error message when student A111 clearly exists in the table?

To see the full course contents click here.

SUMIF et al Examples

20 minutes Easy

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

Handling Error Messages in Excel

20 minutes 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

  • Tell it where to look, and then
  • What must happen if it is an error (note that you can't control what happens if it is not an error)

IFERROR

 

IFERROR Ex 1

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.

To see the full course contents click here.

Lock cells but allow colour formatting or comments

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

 

Protect cells with different passwords to allow levels of authority

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

 

Lock Cells but allow insert or delete of columns/ rows

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

 

Protect only certain cells/ formula from everything except input changes

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

Next