How to protect your worksheet in Excel

2019 3 Tip Friday Top Of Post Images (1).png

Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we cover a tip, a shortcut and a function - making it super easy for you to get better at Excel. 

Protecting specific areas of a sheet 

Excel has the functionality to protect specific areas of a sheet.

To do this first we will remove any current protection rules. Select the whole sheet (you can do this by pressing CTRL+A or click on the little triangle on the top left of the sheet:

Protecting specific areas of a sheet

Then in the Font area, under the Home section in your toolbar, click the little arrow in the bottom right corner:

Protecting specific areas of a sheet

In the Format cells dialogue box that will pop up, click on the Protection tab and unclick both options Locked and Protected. Once you are done, click Ok to close the window and confirm:

Protecting specific areas of a sheet

Next, select the areas that you want to protect. In the example below I want to protect A1:B4 and C1, by holding the CTRL down I can select different areas:

Protecting specific areas of a sheet

Now we will go back to Home-Font – Click the arrow in the corner, In the Protection Tab in the pop-up window, click on:

Locked   - This will make sure the selected cells are locked

Hidden – Click here if you do not want the users to see the formulas in the locked cells.

When you are done click on OK.

image11.pngProtecting specific areas of a sheet

Protect the sheet by going to Review in the toolbar and click on Protect Sheet:

Protecting specific areas of a sheet

Enter a password and confirm, click OK:

Protecting specific areas of a sheet

Now if you try and enter a value in cell A1, the following message will display:

Protecting specific areas of a sheet

But you will be able to easily enter data in cells C2:C4.

You read more about this functionality here.

Shortcut to align the text in a cell

To align the text in a cell in Excel, we can use a quick shortcut: ALT+H

First, select the cells that you want to align and press ALT +H:

image3.png

Press AC to align the text in the middle of the cell (horizontally).

You will have a few different options available to align the text, please choose the right one for your scenario.

To get a full list of Excel shortcuts, check out the Microsoft help article


How to use the Vlookup Function in Excel

The Vlookup function is one of the most used functions in excel.

This function is used to locate and retrieve a value from a vertical range, by using a known value located in a column on the left of this range.

Let’s explain this with an example:

We want to retrieve the price for the Trousers in the table below:

image7.png

The syntax for the formula is:

image5.png

lookup_value- this is the known value that we will use to find in which row our price is, in our example this value is “Trousers”.

table_array- this is the range where our lookup_value, as well as the value we want to retrieve, is. It is very important that the first column of this range is the column where our lookup_value is. In our example, the table_array is : A1:B4

col_index_num – this is the column number where the value we want to retrieve is. This number is relative to our range, so if our lookup_value is in column D and the value we want to retrieve is in column F, then the col_index_num is 3. In our example below is 2.

range_lookup- here we can specify if we want an exact match ( 0-TRUE) or an approximate match ( 1-False) to our lookup_value. In our example, we want an exact match to the “Trousers”, so our range_lookup will be 0.

So the formula for our example will be:

image6.png

It will return 12.

As useful as this formula is, it can only retrieve values that are on the right of the lookup_value.

So if the Price was in column A in our example, and the Product in column B, we wouldn’t be able to retrieve the price for the “Trousers” using Vlookup.  

You can use Index-Match on this occasion, please see our previous articles for a detailed explanation on this function.

To learn more about this function please follow this link.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit