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

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

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.

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.

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

SUMIF et al Examples

20 minutes Easy

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

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.