Updated: Jan 24
Most of the time excel users are just using this powerful program as a table maker thus frequently make critical errors and do not know how to exploit the mighty features of this incredible software.
I am starting an easy to learn Excel use series on my blog, which I hope will give insight to frequent Excel users, who wanted to know more about it, but either are scared to delve deep into the murky waters or don’t even know where to start.
If you follow all parts of this series, you will get a better understanding of the Excel and hopefully, make use of it in your day to day work. It will give you power, ease of work, and hassle-free management of your work-related with Excel.
Before we begin, let me remind you that Excel is a spreadsheet software and not for tables. If you want to just make a table, use MS Word instead, this will be easier to use than Excel.
I will make this tutorial using the MS Excel office 2010. Most of the functions and uses will be similar, if not the same, to the other versions.
When you open up Excel, it will look like this:
Before starting any work on the sheet, the foremost thing to do is to save your file. Just click File Menu and select Save, or Ctrl + S to save. A Save As dialogue will open up, select your destination and save the file.
Make your acquaintance with the sheet:
Learn which are columns, rows, and cells. Each cell has got a name by default. These names are important and are used in referencing of each cell. In the above picture, see the cursor is on Top-left cell of the sheet. It means that a particular cell is selected. Whichever cell is selected, its name will appear in the “Cell-Name” area. Just at the top of the selected cell.
Note how the name is given to each cell. The column followed by row number, i.e. A1. If you select another cell, by clicking on it, the cell selection will change so do the name in the name box.
Note the name of the selected cell, i.e. D11. You can change this name by clicking in the name box and using any of yours, but it is not a good idea, as one can easily forget which cell has been named what, so it’s better to leave it as default names.
I think a better way to learn any software is to use it for a project. Whatever the demands of the project learning will follow and new a function of Excel will come up and hence researched upon.
We will make a dummy project for ourselves, i.e. a sheet to make data of a class of students in which we will input all their records.
Suppose we will need to make following columns for such a data in the sheet:
This is called the header row. Excel will always take the first row as a header row of the data.
If you have noted that apart from a few, others are partly visible, so we need to adjust the width of the columns. A cumbersome method will be to do it manually one by one. Put your mouse cursor between C and D column, the cursor will change and you can drag it right or left to adjust. You have to do this with each column one by one. But there is an easy way too.
Before adjusting the width, adjust the style of fonts etc. of header cells. Just move your mouse on number 1, i.e. first row. Cursor will change to an arrow, click and the whole header row will be selected.
It will look more like this, but still, the header cells are not completely visible. Don’t worry, they will be.
Now you can see all the header cells. Later when putting data into the cells, we can again select the whole data and do the same.
Now we will start inputting some of the data to learn more, but before doing this. We want to name this sheet.
Go to the sheet tabs here:
There is one more thing to do before inputting the data. We want this sheet to display a title on the pages after printing, e.g. “Undergraduate students list of batch 2020”. Please don’t use the sheet for this. As I have seen most of the people doing such a mistake. Every cell of the Excel is for data input and not for titling. There are a lot of differences between MS Word and Excel. The best way to do this is to use “Header and Footers”. Let’s see how?
Click on “File” and click “Print”
Both will do the same job of opening the Print dialogue. We are not printing yet, we are just preparing for the printing and we want to make it perfect for the output.
You will be brought here:
After clicking the “Page setup” you will see this dialogue box:
Click on the Header/Footer tab and you will see the Header/Footer Portion.
You will see this picture. As the title details should be in the center of the page, therefore, we will click in the “Center section”. After that, we adjust the fonts before entering the desired details here.
After adjusting the fonts of the title and clicking OK, we input our desired text in the center section of the header, like this:
After clicking OK, we will be brought back to the Header/Footer section:
Now, for example, we want to put automatic page numbers on the pages in the footer section. I will click on Custom Footer and will use the Right section for this, by clicking there. After that, I will adjust the fonts for page numbers and click OK.
I want to show you how I do for my datasheets. I write “Page “ (note the space after the word), then I click on “Page number”, then a space and then write “ of ” (again note the spaces before and after the word). It will be like this:
&[Page] will automatically appear when you click here. &[Pages] will appear, if you click here.
The first one is the page number and the second is the total number of pages. After clicking OK two times, you will see the preview like this.
Now, do you remember that header row we discussed before?
This will appear at the top of your datasheet, but what if the data spills onto 2 or more pages, will it show as the header on every page, not unless you do this.
So to make this header row appear on every page automatically and you don’t have to adjust it for every page, you have to go back to the sheet by getting out of Print. Either click on file or press “Esc” on the keyboard to get back.
You will see this dialogue box open up, click on Sheet:
After clicking in “Rows to repeat at top”, move your mouse towards number 1 row, the cursor will change to an arrow, click on “1” and the upper dialogue box will get the first row like this:
Click OK. Now the page title as well as the first header row will be repeated on every page you print automatically.
Time to enter some data into the sheet according to our headings.
In S. No, do not put anything right now. Fill the other columns like this:
I have filled some of the data like this. There are also a few points to learn here. First of all, you might have noticed that the Role #s are in series. If any such value lies in series, no need to manually fill them. There is an easy way to do this. Just select two or more values in one column in series like this:
While keep on pressing the left mouse button, drag the mouse from number 1 value to the third and they all will be selected. Now release the mouse button. Afterward, just move your mouse pointer right here to this point (right bottom corner of your selection):
The mouse cursor will change. Get hold of this point using your mouse left button and drag down. Fill as many rows as you want and you will see that the subsequent rows are being filled up with remaining of the series values.
Now you must have understood why I have asked you not to put anything in S. No. Yes, just enter 1 in 1 cell and 2 in the other, select both and drag them down to the required number and your serial number can be easily put like this.
Isn’t it an easy way to enter the serials? Just tell Excel how to fill the serial. For example, if you put 1 in the first cell and 3 in the next and drag them down, Excel will fill the series like this: 1, 3, 5, 7, 9…..and so on. I hope you got the idea.
Now coming back to data entry:
First name and Last names are unique text entries so we leave them as such. The Date of Birth header is interesting. Look at the format of the DoBs written above. Suppose we don’t want them to look like this. If you are in the US, you want the first one to look like this: Dec-12 1993 or Dec-12 ’93, right?
Not to worry, an easy solution is on the way.
Select the cell of the DoB you want to change the format of:
If you click Home menu, you can see the format of the cells can be changed here as short cut:
But I have another idea for you. Press Ctrl+1 on the keyboard and this dialogue will open up.
If you can’t find the style of the format you want to enter here, not to worry, we have a quick fix:
Just do as shown in the picture below:
We want the date to be written as Dec-12 1993, so for this you have to enter in the above area like this: Dec = month, 12 = days, 1993 = year, so using this understanding, we will put the format like this: mmm-dd yyyy. After than clicking OK will change the format of that selected cell format like this:
Let me explain the above way of entering the date format.
‘d’ is used for days. Single or two d’s can be used depending upon the number of digits you want to show for the dates.
Small ‘m’ is used for months. ‘mm’ is used to put double digits of the months, e.g. 02, 12, etc. If ‘mmm’ are used the month will be written in English as short forms, like “Dec”, “Jan” etc. If you use ‘mmmm” then the whole name of the month will be used, e.g. “December, January” etc.
“y” is used for the year. “yy” for 2 digits of the year, “yyyy” four digits of the year. I think you got the idea. Dashes and spaces are put according to your wish and will be used in your format.
Now that you have changed the format of only one cell, you can change all of them in one go by selecting all of them at first and change them only once, but suppose, the scenario is as above. So you don’t need to go back to format dialogue again and do all those stuff again to just change the format of those two remaining dates. There is another easy way.
Just select the date cell of which you have changed the format of (selecting one cell is just clicking on it).
Then click this tab:
It is called a format painter. The mouse cursor will be changed to a brush. Now use this brush to paint the format of the first cell onto the remaining 2. Put the brush on the second cell and while holding the left mouse button, drag it to the 3rd cell. Leave the mouse button and the format of the first cell will be copied to the rest of them.
Coming back to the data entry
If you have noticed that major for all the students are the same. Whenever you will arrive here to enter “Science”, as soon as you will enter “S”, Excel will give you the rest of the word, so just press enter despite entering the whole “Science” word, thus making your work easy.
You may point out that the batch is also the same for all students, but unfortunately, no suggestions are given for numbers unless any alphabet is present at the start. But it doesn’t mean you have to enter this repetitive entry again and again. Just select one of the batch entries and paste them down in the remaining cells. Or you can select the bottom right of that cell and drag the mouse down to fill as many cells as you want with the same value.
Now the whole of our data will look like this:
Just scroll down either using the arrows in Excel or middle wheel of your mouse and you may notice something like this:
You cannot see the header row, so it is now difficult to tell which column is for what. It is much harder if the sheet is bigger with a huge amount of data.
Again we have an easy fix:
Scroll to the first cell back where you can see the header row. Then select the row immediately below the header row, in this case, the second one. Just move your mouse on number 2, the cursor will change to an arrow. Click and the whole number 2 row will be selected:
Click on the View menu and then click Freeze panes:
After doing that, just unselect the 2nd row by clicking anywhere on the sheet. Now scroll again and you will see as the whole sheet scrolls, but the header row stays there for an easy view.
Sometimes, there is a need of not only freezing the top row as header, but to freeze the left 1 or 2 columns as well. The data is sometimes so huge that not only you scroll down, but also scroll right. To keep both top header row and the left names columns in sight when you scroll, do this trick:
Instead of doing the above process, just select this cell:
You must be wondering why this cell. As you can see, this cell is placed just below the top header row, we want to be visible during the downward scroll, and it is located just to the right of "Last Name" column, we want all columns left to this cell be visible during rightward scroll.
Then Click "View" and Click "Freeze Panes":
Now as you scroll down or to the right, the rows and columns you don't want to hide will always be visible.
While viewing the data you are seeing these lines:
Don’t worry, these are just page break lines and is showing as how your data will print. These lines will not be printed. I think we should move to this part first as to how our datasheet will print on the pages. Of course, we don’t want our hard-worked datasheet to print haphazardly.
So, back to print dialogue box, not for printing but to adjust the sheet as how will it print!
Click on the icon to which red-arrow is pointing and print dialogue will open up.
As you can see here:
That the data header is more what this view of the page can handle, so it will be wiser to change the view of the page.
Click Page Setup
Select the “Landscape” option to change the layout of the page.
Clicking OK will show you how the page is looking.
Now the layout of the page is changed completely, but if you just see that still, the whole data is occurring on 2 pages, and, suppose, we want the whole data header to be placed on 1 page only.
Click here at right bottom of the screen:
Dashed lines will start to appear on and around your landscape
You can move your cursor on the lines like this and it will change to a cross:
And pull the lines to the left or right to adjust the page view.
As you can see below, by playing with the dashed lines, I have adjusted the header row data cells to appear all on just one page.
Now Press Esc key on your keyboard to return.
Now click on any part of the data you have entered, not the empty cell and not outside the data, then press Ctrl+A. Only the data, whole of it, will be selected:
You can now adjust the position of the text, and put table lines throughout the whole of your data. It will look like this:
It’s the easiest way to select the whole of your entered data.
This will end this part of Excel training. In the next part, we will look into some more interesting but complex functions of Excel, which I will make it easy for you to understand and use.
If you have any question, or you want to ask about any function or solution in Excel, please do leave a comment, I will respond quickly.