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:
Then in the Font area, under the Home section in your toolbar, click the little arrow in the bottom right corner:
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:
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:
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.
Protect the sheet by going to Review in the toolbar and click on Protect Sheet:
Enter a password and confirm, click OK:
Now if you try and enter a value in cell A1, the following message will display:
But you will be able to easily enter data in cells C2:C4.
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:
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.
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:
The syntax for the formula is:
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:
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.