By: Fherd Mar
EXCEL CLASS TUTORIAL – BASIC UNDERSTANDING OF AN EXCEL WORKSHEET, COLUMNS, ROWS, CELLS.
After completing this tutorial, you will be able to:
- Create a new workbook/worksheet
- Add, delete and hide columns or rows
- Format and merge cells
Before we start, let’s first understand the difference between an excel workbook and worksheet.
Worksheet – is a single spreadsheet that contains cells organized by columns and rows. By default, the column starts with alphabet A while a row starts with number 1.
In above photo, the yellow cell location can be called as cell 1A or A1 while the green cell is cell 7D or D7.
Workbook – is a file that contains one or more worksheets. Each worksheet is separated by tabs at the bottom of an Excel window.
The photo above shows an excel workbook that contains 3 worksheets. The “+” sign gives you an option to add more sheets if necessary.
Creating a new workbook and worksheet.
First, you must have the Microsoft Office installed on your computer or laptop.
Then, go to the start menu and find the excel icon as below. Click on it and it will automatically guide you to a new workbook.
Once excel workbook is open, then you can now start working on your worksheets. Sometimes, you would like to rename the worksheet’s name for tracking purposes. If this is the case, then just follow the simple steps below to do it.
- Mouse right clicks the sheet and it will show options below, select rename.
2. You can now change the tab/sheet’s name per your preference.
You can also select tab color if you want to highlight a specific sheet. Follow the same steps as above but choose “Tab Color”. This is very useful in sending out vital information so that the receiver will immediately take notice and do prioritization as needed.
Adding, deleting or hiding columns or rows
Now that you already have a basic understanding of a worksheet, let’s start learning about the columns and rows.
In some cases, we are the task to work on an existing worksheet where is already an information or data available. This may require us to add, delete, hide or move columns and rows to have the expected result we need.
However, this also applies to our own worksheet that we need to revise based on our manager or client’s feedback.
In below example, our client requires us to add more information about the number of social media followers, hide the email address and delete the home address.
To add the number of social media followers, it’s more organize if we insert a column beside the Facebook (column B) and Twitter (Column C) accordingly instead of just adding it directly in column F and G.
So, we start inserting Facebook followers by just mouse right click on column C and it will show option as below. Select insert and it will add a new column C and you will notice that the Twitter, emails and home address information are moved to column D, E, F respectively.
Once you have the new column C, then you can proceed to add a header “Facebook Followers” and their numbers.
Next is to add the number of Twitter followers, and what you’ll do is same as above. Mouse right clicks the column E and select insert. Then proceed to add a new header “Twitter Followers” and their numbers.
Now that you’re done adding social media followers, the next task is to hide the email address.
This is almost similar steps with inserting/adding a new column. You just need to mouse right-click the column where the header “Email” is located, for this example is column F.
Select “Hide” and it will hide the entire column F. Please take note that the data or information in column F is not lost, it’s still there just hiding. You can always get and show that information anytime you need.
If for some reasons, you need that information to be shown then you can unhide it. It’s very easy to do, just mouse right click column E and G and it will show options. Select “Unhide” and it will show back the entire column F.
The last task is to delete the home address information. To do this, just also mouse right-click the column where the header “Home Address” is located which is column G and below options will appear. Select “Delete” and it will delete the entire information in column G.
Please be extra careful in doing this, make sure you are deleting the correct column because once you saved the file and closed if you can’t anymore get back that information
But if the file is not yet closed, then you can always undo it by clicking below icon or type command “Ctrl + Z”.
So, you’re now done with your task. Don’t forget to always save your file.
The task above teaches us how to add or insert, hide and delete columns but sometimes we also need to work on rows. Don’t panic if you’re an Excel beginner because we’re here to help.
Working on excel rows is completely the same as working in columns. Only difference maybe is because you’re doing it horizontally.
Same steps apply for adding, hiding or deleting rows. It always starts with mouse right click and options you need will appear.
Its time now to learn the smallest part of an excel worksheet – the cell. If you can remember, we partially mention about a cell and cell location in the introduction.
And as said, an excel worksheet contains cells organized by columns and rows. This means that the information or data are shown in single or multiple cells. It’s the main point of our task so we need to make sure each cell information is correct and precise.
Now, let’s understand how we can format a cell so that the required information is provided.
By using mouse right click on a specific cell, below options will appear.
Select “Format Cells” and it will open another window as below.
There are 6 tabs available to choose from: Number, Alignment, Font, Border, Fill, and Protection.
Number tab – gives you an option to modify the value or specific information you want to show. The most commonly used category are number, currency, date and time.
In below example, we want to show the weight of each part number in 2 decimal places only.
So, follow the simple steps below.
- Select the first cell then press shift and select the last cell, then mouse right click.
2. Select format cells and go to number category then set decimal places to 2 and press OK.
3. Now, your weights value is changed to 2 decimal places. Take note that this follows the Roundup system.
Almost the same steps are needed if you want to use category currency, date, time, etc.
Alignment tab – gives you an option to modify the alignment, orientation or control of each cell information.
By selecting the drop-down menu of horizontal and vertical alignment, you can change the position of the text or data in each cell. See sample below where we change the header’s horizontal position to be a left-indent and vertical position to be center.
You can also change the text orientation if you need, just click your preferred orientation and the text or data in your selected cells will change accordingly. See example below.
Font tab – gives you an option to modify the fonts of each cell information. You can also do some effects if necessary. The most common I used is “strikethrough” which signifies that the information of a cell is canceled or not used.
You can select from a variety of font styles, sizes, and colors available.
In below example, I will change the font color to red and use strikethrough because I want to emphasize that this part XXX is already canceled and not available.
Border tab – gives you an option to modify the border lines and line color of each cell information.
In below example, I want to change the borderline to be thicker and line color to blue.
- Select the line style you want to use
- Select the line color you like
- Click the border lines you want to change
Fill tab – gives you an option to modify the background color of each cell per your preference. There are a variety of colors you can select.
In below example, I want to change the cell color to green to emphasize that I’m done checking and it’s correct.
Protection tab – gives you an option to lock the cells or hidden the formulas within the cells. But this has no effect until you protect the worksheet. This is a more complex topic so I will discuss this in future tutorials.
So now that you already understand how to format cells and the basic functionality of each tab.
We’ll discuss also how to merge cells and its purpose. Actually, this function can be found in alignment tab but I prefer to discuss it separately in details because this could be a very useful tool when we are organizing data information.
In below example, we are a task to organize the data according to Twitter followers so that we can prioritize which leads we need to contact immediately.
Those with 500k+ followers will be priority 1, then 100K – 499K will be priority 2 and 99K below will be priority 3. To do this, we need to sort first the number of twitter followers from highest to lowest.
**Sort and filter topics will be discussed in future tutorials.
After sorting, then we can add header “Priority” in column G. Then, by following the priority criteria above we can start to merge those cells into column G.
Select the cells accordingly then click merge icon as shown below.
And the result will be like this, you can write number 1 to signify that it’s priority #1.
Then, just repeat the same steps for the rest of the priority until you’re finished.
Another useful application of merge cells is when we are presenting information that requires merging cells so that we can provide a more concise information that will be easy to understand. A sample below in red box are merge cells.
Cells AF5 to AS5 are merged together so that we can add a header “Action Schedule Progress Ratio” and under it are the months Jan – July. We’re doing this so we can present the data in a concise and effective manner because we are tracking the progress of the countermeasures per month.
That is some of the purposes why we need to merge cells. It can vary in every application depending on the necessity to do it. But the common goal is to be able to present the data or information in a more concise and effective way so that it will be easy for our colleagues, managers, and clients to comprehend and understand.
We’re now done with our tutorial, I hope that you’ve learned something from it. You can always read it again and familiarize until you’ve mastered it. May this help you with your daily task in school and work.
Please don’t hesitate to provide your feedback or comments about the tutorial. I’m always looking forward to continuous improvement so your feedback means a lot. You can share this with your friends, peers or colleagues to help them as well.
Until the next tutorial, thank you for your time. Have a nice day and God bless!