Lessons Archive

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 the source data of a pivot table

Preview

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

Protection Examples

Preview

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

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

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

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

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

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

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

Lock Cells but allow column/ row size change

Preview

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. <img class="aligncenter size-full wp-image-9592" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Lock-Cells-but-allow-column-row-size-change-1.png" alt="Lock Cells but allow column/ row size change" width="1128" height="877" /> 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 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>.

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

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

How to protect an Excel Spreadsheet from opening

Preview

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

Intermediate MS Excel

Free Preview

Length: 960 minutesComplexity: Standard

Details about the Intermediate MS Excel Online portion of this bundled course.  With over 133 lessons, 260 video clips and 48 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course builds on the Fundamental knowledge from the beginner course and teaches you to solve problems in Excel with a particular focus on the tools available.

Beginner MS Excel

Free Preview

Length: 480 minutesComplexity: Easy

Details about the Online Beginner MS Excel portion of the Course. With over 60 lessons, 130 video clips, and 28 example exercises with detailed video solutions explaining exactly how you should have got to the answer. This portion of the course will give you the fundamentals to make sure it is easy to learn anything about Excel.

NPV and IRR

Free Preview

Length: 11 minutesComplexity: Easy

NPV and IRR in Excel

Automatic Sorting in Excel Exercise

Free Preview

Length: 15 minutesComplexity: Standard

An exercise on how to create automatic sorting in excel so that the information automatically sorts itself when changes are made (no VBA or Macros)

Vlookup multiple values

Free Preview

Length: 10 minutesComplexity: Easy

Occasionally when performing a VLOOKUP, the lookup values are not all in one column but rather over 2 or more columns. So for example if we are looking up a person we want to use there first name and surname but these are in different columns in the lookup table.

Vlookup #n/a Exercise

Free Preview

Length: 10 minutesComplexity: Standard

An exercise with VLOOKUP to understand why it is not giving the correct answer and one of the causes of a #n/a error when using VLOOKUP.

Anchoring cells in Excel exercise

Free Preview

Length: 10 minutesComplexity: Easy

A useful feature of VLOOKUP is its ability to anchor cells. A common problem is that data appears in different cells at different times (each time you download a report a key cost item you are looking for appears on a different line). You can use VLOOKUP to find the cost item and make it appear on a set row. You can then build ratios and graphs off this static row

Find missing items with Excel Exercise

Free Preview

Length: 10 minutesComplexity: Easy

We can also use VLOOKUP to find items that are missing from a list. This is very useful when reconciling accounts.

Vlookup exercises download

Free Preview

Length: 5 minutesComplexity: Easy

VLOOKUP exercises download for use in the rest of the VLOOKUP course with practical examples and detailed explanations of the solutions.

Vlookup approximate match

Free Preview

Length: 15 minutesComplexity: Easy

How to use vlookup approximate match to categories data, use banding and get Excel to guesstimate results

Join databases with VLOOKUP

Free Preview

Length: 10 minutesComplexity: Easy

A common requirement in Excel is to join databases with VLOOKUP. This often happens when there is a common item in the databases. Examples of this are when you want to show product codes and names or show a client number and client name if they are on different lists.

Bands in excel exercise

Free Preview

In this example we have a situation where different levels of sales result in different commission rates. Calculate the commission % allowed and the commission earned

Vlookup #name? error

Free Preview

Length: 5 minutesComplexity: Easy

Reasons for a vlookup #name? error in a vlookup function and how to correct it

Vlookup #value error

Free Preview

Length: 5 minutesComplexity: Easy

The reason for a vlookup #value error and the likely cases where this might happen

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.