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>

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

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

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

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.  

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

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.