Updated: Jan 24
Thank you for following this article. I hope you are as much of a learning enthusiast as I love to teach these lessons.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
So we continue to a bit more complex functionality of Excel.
Protecting the Sheet
Excel is designed for data handling. When you input data and you send the sheet to someone to view it or you put it at the front end for evaluation, or data entry, for example, you don’t want anyone to change your precious data. For this, you need to lock your sheet.
For this we will use the “Protect Sheet” function, which is found here under the Format tab:
But before we go on to protect the sheet or lock it for any editing, we need to know that the protecting sheet function is tied up with another property of the cell, which is called “Locked cell”. Let me explain. First, we go to Excel and select a cell, and press Ctrl + 1 on the keyboard. We will see this dialogue:
You can see that the “Locked” is ticked, which means this cell is locked. When a cell in the sheet is locked and you use the protect sheet function, no entry to that cell is accepted. If you un-tick it and then protect the sheet, this particular cell will remain unlocked and anyone can change the content of the cell. I hope you are getting the usefulness of this function. If you don’t want users to edit or change values of particular cells, but you want users to input in some other cells, you will mark them locked and unlocked respectively.
Suppose in this particular sheet we want all the cells to be locked, so that this data, when sent to others, is for viewing only and no one can tamper with the data. For this, you select a cell within the data and press Ctrl + A to select all the cells containing data.
Then Press Ctrl + 1
Click on Protection, and then check that there is a tick present. Even if one cell is unlocked, then the tick will look like this.
It always means that some cells are locked and some are unlocked. Just click it to change it to a tick and now all the cells you have selected are changed to locked property.
Now the cell properties are changed to Locked, but they are not protected yet. For this, you have to protect the sheet.
Let’s now go to the Protect sheet function.
Click it and you will see this dialogue:
You see that “Protect sheet and content of locked cell” is selected, so leave it as such. Come down and you will see a blank input field, this is for the password, labeled “password to unprotect sheet”. This field is optional, it depends upon you whether you put a password or not. If you choose to put a password and press ok, it will prompt to enter the password again and then after inputting, you have protected the sheet with a password. If you don’t enter a password, the sheet will still be protected. The difference is that if it is protected with a password, no one else can unprotect it by using the “unprotect” function unless he or she knows the password, otherwise, anyone can click on the unprotect sheet and it will be unlocked. Password will be shown in asterisk. Please remember, if you use a password and forgot it, there is no way you can retrieve it, so be very careful in choosing a password.
Then come to “Allow all users of this worksheet to:”
You can see, select locked cells, and select unlocked cells that are already checked by default. What do they mean? For example, the first one; if you check the select locked cells, the users can click on the locked cells and thus can select them. If you uncheck it, the users can’t even click on locked cells and thus there is no way they can select them. This is a good way to discourage users to even copy your data.
For the second one, select unlocked cells. So if you check this, users can select unlocked cells and enter data into them. But if you don’t even want them to do that, you can uncheck it.
Coming to the third option and then the others can be easily understandable. If you check format cells, the users will be able to format, e.g. change font size, style, etc., even on a protected sheet. But if you don’t want them to do this, leave it unchecked. The same explanation goes for other options, just two need special mentioning.
If you want to allow users to sort the data, check it, otherwise leave it to uncheck. The other option is for a data filter. If you have applied filter on your data (we will discuss filters later), and you want your users to use them in order to go through data, then check it, otherwise, the users will not be able to use the filters that are put on the data.
The Hidden you see with Locked on Cell property dialogue is for hiding any formula to be seen in the formula bar, when the user clicks on it.
Finding or Replacing
These are very useful functions, especially if the data is huge and you want to find anything on the sheet. It is very difficult to locate any piece of data on the sheet manually. Let’s first see the Find command.
Either using Ctrl + F or on the home menu you can find the “Find” function here:
This box will open up, which is labeled Find and Replace:
To find anything, you just type it inside the “Find what” box and click “Find Next”, and, if the data is present on the sheet, you will immediately be taken to that cell containing whatever you have searched. If you click on “Find All”, then a whole list of cell addresses will be as shown, by clicking on each of them you will be taken to that cell, and you can see whether it is your desired piece of data.
If you click on the “Options” button, you will see additional options, which I am going to explain one by one:
Look at “Within”, if you click it, you will see two options, “Sheet” or “Workbook”. If you want to find anything within the sheet on which you are working select the “Sheet” option, but if you want to find on all the sheets of your active Excel file, you can select “Workbook. By workbook it is meant the Excel file containing all your sheets. Search “By Rows” or “By Columns” implies that whether you want to search downward first or horizontal first. Under “Look in” box, you will see three options: Formulas, Values, and Comments. It is obvious, isn’t it? If you want to search within formulas, select this option, otherwise, if you want to search in the result of the formulas, select values. If you have comments in your sheet and you want to find something within comments, select this option. One important point here is that please note which options are already selected by default, usually you will be using the default settings, but you can always use the powerful magic of Excel by tempering with default values.
The “Match Case” option is easy to understand, select it if you just want to search exactly the case you have entered, if unchecked, the case will be ignored and capital and small letters will all be searched equally.
The “Match Entire Cell Content” is an interesting option. Suppose you want to search for Mathew as one value in the cell, if you put Mathew and click find next, it will start searching all names having Mathew in them, and if there are a lot of such names, then it will take a lot of time before you can find the desired one. So selecting this option will check the Mathew only in cells that have just this value and nothing else.
There is another interesting option in the Find command which is sometimes very useful:
Suppose you want to find a cell or a piece of data with a certain font size, style, background color, number format, e.g. date format, etc. You can use this option. By clicking the first option, you have to manually select the format, by clicking on the second; you can choose the format from another cell. This option is most useful in the Replace command.
Now concentrate on the Replace command. Find and Replace go hand in hand, but you can get it as a keyboard shortcut by using Ctrl + H. The mechanism is nearly the same, except that this command will find the mentioned data and replace it with another data which you have mentioned.
Just enter the data you want to replace in the “Find what” box and then enter the data you want to replace within the “Replace with” box. Clicking “Replace” will just replace the first instance Excel will find, and clicking Replace All will lead to replacing of all the instances, Excel can find. If the data is not found, a message will be displayed that no search is found. Other options are the same as for the Find. One interesting thing about Replace is that it will replace the find data with exactly whatever you have put in the “Replace with” box, for example, you want to replace mathew with a small “m” with Mathew with a capital “M”, it will replace the small “m” with the capital one.
Go to and Go to Special
Go to command is used to get to a particular cell. Just click on Go to…. At the same place where you have clicked Find (or press Ctrl + G)
and this box will open up:
In the reference box, just put the name of the cell, for example, B620 and you will immediately be taken to that cell. You can also go to a cell reference by inputting the name of the cell here:
The more interesting one is the Go to Special function. Let’s see its dialogue box and I will explain the options one by one:
This is useful if you want to jump to a specific piece of data in a huge dataset.
Go to comments is self-explanatory, Excel will select all Comments if you have any on your sheet.
The second option of constants and formulas can be used if you want to go to all those cells with formula and the sub-options will be used if you want Excel to go only to cells returning the selected format value. You can search for cells with formulas that return Numbers, Text, Logical (TRUE and FALSE), and Errors. These checkboxes are also available if you select Constants.
Selecting “Blanks” will take you to all blank cells. The current region means selecting the whole region where your cursor is and the data is connected. This way you know where the breaks in data lie. Leaving the other options as we have not covered them yet, but as we go further, these options will become self-explanatory and you can use Go to Special function efficiently.
If there are certain objects on the sheet, for example, drawing objects, pictures, etc. Sometimes they are put on the datasheet in such a way that selecting them is difficult, and then you must use this function to select such an object easily.
When you click on Select-Object, your cursor will be a permanent arrow, with which you can select any object on the sheet, but you can’t select the cell. To get out of that option, you have to press the Esc key on your keyboard. There is another thing along with that, “Selection Pane”. Using the Selection pane is much useful when you have got a lot of objects on the sheet, and you don’t know which one is which. So just open the selection pane and click the object there and your object will be selected on the sheet, as well as you will be able to know the name of the object.
Using Sum command on Home Menu
We have already used the =SUM function in our previous part, here I want to show you that the sum function can be used through this command easily, if you don’t want to input the formula manually, but I always encourage my users to practice inputting all formulas manually to get into practice.
Suppose we have got this data, as we have used in previous parts:
As you can see in the total column we need the sum of all the subject cells on the left. So we can just go to the first cell under the total column and select it by clicking on it. Then we will click on the arrow of “Auto Sum” and click on Sum. You will see that all the cells left to it will be summed up like this:
After pressing Enter you can get the sum of all left cells having numbers into it. This is a quick method of summing up the numbers whether left or above the selected cells.
Sort & Filter
On the Home Menu, there is this one last command to check, which to me is really useful and very important. We will do it in detail and will check its different aspects.
First, we will look into the Sort function. Clicking on the arrow of Sort & Filter will drop down the different commands and we can see Sort is among them:
We can see it “Sort A to Z”, “Z to A”, and “Custom sort”. A to Z means from up to down or from smallest to largest, conversely Z to A means from down to up or largest to smallest. We will look into Custom sort so that we can understand the full functionality of this command. Selecting the Custom Sort will give us the following dialogue:
You can also find this Sort function under Data Menu here:
Both are the same. Before you can use the Sort function, you have to select the data you want to sort. If you don’t select any particular set of data, Excel will select all the data you have. But you can always have a choice to sort some columns and leave other columns out of the sort (sometimes it is useful, for example, leaving the serial number out of the sort function).
Now, look at the functionality of the Sort command. First of all look at this
By default, Excel will take the first row as the header row. Every data must have a header row. This way it is easy to sort a column of choice, but if you are sorting the data from in between, where u can’t have a header row then you can easily use the Column Alphabet number.
Now look at Column, Sort on, and Order:
If you click Sort by under Column, you will see all of the values of your header row, i.e. the first row heading values.
Whichever column you want to sort on, you can select here. We will select here the “First Name” column, as we want to put the whole date sorted in alphabetical order according to the first name.
Then move to Sort on. We will leave it as Values. By values, it is meant that whatever is visible in the cells of the column first name.
Then the third one Order, we will select A to Z, because this is what we want that data should be sort on.
After clicking OK, we can now see that the whole data is sorted alphabetically according to the first name field.
One thing you can see is that the serial numbers are disturbed, so we have to redo them again. As mentioned above, you can keep it out of sort by not selecting it in the data to be sorted, to save effort and time to redo the serial numbers.
Try sorting the data using the Phone number and it will sort it going from 1 to 1000 as such.
You can always sort the numbers, but then the choice will be smallest to largest or vice versa.
This will bring us to a very useful function called “Auto Filter”. We will discuss this in detail and with this, it will conclude this part of Excel too.
When you click on Filter, either under Data or Home Menu, you can see small arrows being put in all the columns of your header row.
You can use these arrows to shortlist the data using any type of filter you want.
Let’s try with gender. You just want in this data that females are shown, so just click on the small arrow in the gender field and deselect everything except the female:
Pressing OK will remove all the males from the data and you will just get the females in the data. Don’t worry, the data with males is just hidden and not gone anywhere.
When a filter is applied the small arrow on the field where the filter is applied will change to show that the data is applied in this field.
Note that in all other headers, it is still small arrows, except the gender field which is changed to a funnel, showing filter is applied in this field. This way just looking at the data will give you an idea of where the filters are applied.
If you want to remove the filters, click on the funnel and
It will remove all the filters on that field. But if you want to remove filters altogether from the whole data, just click on the Auto Filter button and turn it off.
In the filter option (when you click on a small arrow, for example in the first name field), you can see all these options:
I encourage you to play with them to understand them more, later it may become part of your routine use of Excel.
Like for example we click on the small arrow (in University field) and click on Text Filter and then click Equals, and we see this:
In the box we can put any text we want the data to shortlist:
If you have noticed that we have put an asterisk (*) sign after Lond, because we want to shortlist in University field that starts with Lond (e.g. London), etc.
And we came up with these two:
Likewise, if you want to search all Universities having Technology at the end of the name you can filter the list using this property: *Technology. Watch the asterisk sign position. If it is coming at the end, it means the cell value will start with the letters given by you plus anything. If it starts with an asterisk, it means anything but ending at the letters you provided. This is called “Wild cards”. If you want to filter the data by the letters coming within the text of any cell, you can use wild cards like this *London*. Notice asterisk coming before and after the word “London”. The list will be filtered by all values having London in the cell values.
Likewise, you can filter the data with numbers. For example, take the date of the birth column:
You can see that for the date, there are lots of options provided. If you want to shortlist the data between such and such dates, or before such date, or after such date, you can use any option. Go ahead and play with it. This is a very important and useful function, list makings will be the work of magic for you if you learned this properly.
Suppose you have already applied conditional formatting to your data as I did in the University field:
You can filter your data very easily by the colors of the cell:
This is useful if you have not a purposeful filter to get, but due to conditional formatting, your list is already made by conditional formatting and now you want to shortlist
Likewise, you can play with the number of filter options. Just go ahead, don’t be scared. The more you use it, the more you will be comfortable with this.
Interestingly you can use filters on more than 1 field at one time. Suppose you filter university filled for “London” and you filtered gender for “Female”, so you will get a shortlist of all females in universities in London.
I hope you got this. This is a tremendously powerful tool you can use to make lists.
This will end this part of our tutorial. Please let me know, if you have any questions, or you need further explanation on something we discussed.