Updated: Jan 24
We continue our journey into the murky waters of Excel. Today we’ll jump further and will increase our understanding of this awesome software a bit more.
In the previous part, we have learned to adjust the data within the cells and how our data will print.
This part will take us a bit further, concentrate:
Copy and Paste Special
Everybody knows about the "Copy and paste", but I am taking a little further. Now suppose you have to copy something from another sheet, workbook or even the word document and it appears like this:
You can see that the formatting and the font size and font style are different. One way is to copy it and paste it in your sheet, but then you have to adjust all formatting, there is another easy way to copy and paste it as the same formatting as your previous data is:
So select this data and press Ctrl+C on your keyboard, then select your datasheet. Now instead of pasting it right away, just wait, first do this:
Select the cell where you want to paste this data, and click paste like this
And click the first 123.
See for yourself.
Data is pasted, formatting is not.
Wrapping the cells
In the above picture as you can see, the last name we entered is not fully visible. We can make it visible by stretching the column, but then our settings of all header columns will be disturbed. So we have a solution. Place your cursor on that name and do as described in the picture below:
You see the result:
The name has been wrapped into the cell. You can do the same for each cell, whose data is not completely visible, or you can click on any entered data cell and press Ctrl+A and then click Wrap Text. All the data will be adjusted.
Numbers do not wrap
Word of caution about numbers in the cell. When the number is large enough so that it can’t be housed within the cell, Excel will automatically change it into scientific notation, like this:
And of course, you want to show a complete number. It can be a phone number or role number etc. Just press Ctrl+1 on your keyboard and change the format of the number to your desired one.
But when you will press OK, the number will look like this:
Whenever a number is larger than what a cell can house, it will be shown like this. Unfortunately, using “Auto-fit” will disturb your column width alignment on the page. Wrap text will not affect the number as it has on the text. The only solution may be is to either use the scientific notation, or to decrease the font of this cell or cells which contain larger numbers, or if possible, adjust the column width appropriately. These marks may also appear with Dates, even that the dates are not long and can be housed within a cell, but still, Excel will show these marks instead of dates. I need to tell you this, because sometimes, users will have lots of issues with these marks. So don’t panic, it’s just the length of the number is too much.
Merge and Center
Ok, coming back to our data, but before going further, there is this one more tab on the toolbar, “Merge and Center”:
I think it is self-explanatory. Just select two or more cells and all of them can be merged. You sometimes need this function for the data.
Using simple formulas
We will use another kind of data:
As you can see above, the marks of a primary class. On the right, there are these 3-columns which we need to fill.
The total, %age and Position columns.
Using SUM function
We have to add formulas in each of the cells of these columns. We’ll start with the total first. To write a formula, always start with the “=” sign. As we want to sum up all the subject marks, we’ll use the formula in the first cell of the total column as:
Notice, we have started with = and then have written SUM. (As you start writing SUM, help will be provided as to which function you want to use in this formula). You keep on writing SUM, and then press the Tab key. By pressing this key, excel will automatically complete the function and will put the start parenthesis “(“. So, you should understand the format of the formula. It should start with “=”, followed by the function name, then a parenthesis, within which you will put the conditions of the functions and end the function with a closed parenthesis “)”. Without this format, Excel will not accept the formula and error will be shown.
As you might have noticed that after we pressed Tab key, not only function is completed and a parenthesis is put, but also help is provided on that particular function as how this function can accept conditions. In our case of “SUM”, we can put numbers, separated by commas or a series. Let me explain:
=SUM(2,3,4,6,7,8) will add up all these numbers together, but this can also be done this way too =2+3+4+6+7+8 and the answers to both of these statements will be the same. That is not what SUM function is all about. It is a powerful function. Instead of putting numbers, we can use Cell Addresses to add up the cell values. You remember the “Cell Addresses” we talked about in part 1 of this series? Now is the time to apply here. For example, in our above data, we can put the formula as under:
Look at the formula we have entered, it will sum up the values in C2, D2, E2, F2, and G2 (each cell reference is represented by different colors to differentiate between values). You don’t have to write the cell address, just click on C2, then add a “+” sign and click on D2 and so on. Excel will automatically put the cell addresses. One thing you must have noticed that from C2 to G2, the addresses are in series, whenever the cell addresses are in series, you don’t have to put each cell address. You can change your formula like this:
So the formula will become =SUM(C2:H2), again you don’t need to write the cell address, just after the parenthesis, click on C2 and while left mouse button still pressed, pass it over to H2 and release the button and series addresses will be put by the Excel, an animated dotted line will be displayed showing which cells are added into this series. Do not forget to put an end parenthesis “)” when you are done with the function. Then press “Enter”. You will get sum values of all the subjects in this cell, like this:
Building percentage of total marks
You have seen we have just done this for one student, before I tell you how to ease the work for other students, let’s first do the %age cell for the same first student. There are a total of 6 subjects and each carries 100 marks, so total marks will be 600. For the percentage we have to enter the following formula:
And the result of this formula will be:
But this is not the percentage, is it? So what we need to do is to just change the format of the %age cell. Click on the cell and then press Ctrl+1:
Click Percentage here and adjust the number of digits you want after the point, then press OK and see the result:
Copying the formula down
In our data, there are just 8 students, but if these are 50 or more than doing this for every student will be a cumbersome work, don’t worry we have an easy fix. Just forget about the position column, for now, we’ll come to that later.
Select the 2-cells of total and %age on which we have just worked:
Bring your mouse cursor to this point, and hold it with your left mouse button. Now drag this point, by moving the mouse cursor, downwards to the end student. Then leave the mouse button and voila:
Each cell of total and %age will have its respective student’s total and percentage, interesting, isn’t it?
Now the data will look like this:
Sorting the data
Next, you want it to be arranged in descending order, so that the highest mark should be on the top and then the lower and so on.
Select the whole data by clicking on any filled cell and pressing Ctrl+A.
Then click on Data and then Sort:
This dialogue box will open up:
In the first box, labeled Column, Select “Total”
The second box, labeled “Sort on”, leave it as Values.
The third box, labeled as Order, select “Largest to Smallest”, as this is the order you want the data should be arranged.
Then click OK and watch the data being arranged in descending order in a jiff.
Note that S. No column will also re-arranged. You can correct it by entering 1 in the first cell and 2 in the other and dragging down. Another way to avoid this is: not to include S. No column when sorting, as I did.
Please remember, when sorting; always include the header row, so that you can easily identify the columns.
Now the data will look like this:
Coming to the position column, now it is easy to fill. Just put 1st in the first cell of Position column, 2nd in the other, like this:
Select both cells, and drag, it as you have done with the total column, downward to the end student and the positions will be labeled, like this:
See, how Excel can learn to fill the series if you just tell it how to do that.
Inserting a column in between
Suppose an error has occurred in making the subjects data, we forgot to put History subject, which should come in between “Geography” and “Astronomy”. Right! So just select “Astronomy” (E1 cell) and click on Insert:
And select “Insert Sheet Column”
A new column will be inserted in between “Geography” and “Astronomy”. Please remember, a new column will always be inserted to the left of the selected cell.
Now enter here “History” and input the marks for each student.
You don’t have to worry about the “Total” and “percentage” columns, they are automatically adjusted and the history column marks are already included within total marks and percentage.
You can see this by selecting any Total cell and pressing F2 on the keyboard (this will show you the formula of that cell).
As you can see, now it is summing C to I columns, compared to previous from C to H columns and for the percentage, it is taking the total column into the formula. All this has been done automatically, if you insert a column in between two columns.
But as a new subject is added, so now we have to make changes to %age. Before the total was 600, we have to change it to 700. For this, just click on K2 cell and press F2.
Change 600 to 700 and press enter.
Then drag the result of this one cell from here to be copied to all others:
Instead of using “I” to make %age (as we have input before) now it is taking the “J” column for this. So this work was all done automatically, thanks to Excel.
But…….., maybe there is some change in position, as a new subject has been added. So to adjust that we will sort the data again and correct the position as under.
Ok, that is all fairly done.
Now suppose you want all those students, who had scored equal or more than 70 marks to become prominent in the list, say the background color of such students should change.
For this we will use a powerful command in Excel called “Conditional formatting”, which you can find under “Home” menu here:
As we want to change the background color of marks for all the subjects which are more than 70, so we will first select the entire marks of subjects:
Remember, just to select those cells in which we want to make changes, not others.
Now click on Conditional formatting:
And click on Manage rules:
Then click on “New Rule”:
The following dialogue box will open up. Don’t be intimidated:
Click on “Format only cells that contain”
You will see as follows:
Leave the first box as “Cell value” because it is what we are using. Click the second box and select greater than or equal to, because we want to highlight all students who are greater or equal to 70 in each subject.
Put in the 3rd box the value, i.e. 70.
Then click on “Format”
Then select a color, here I have selected light orange, as selecting a dark color will mask the font color.
Click OK thrice, and see the results:
Any student who has 70 or more marks has got their background color changed.
Now suppose you want students between 70-79, 80-89, and 90 or more in different colors, you can adjust your conditional formatting like this.
As you can see, 70-79 are all light orange, 80-89 are all light blue, 90 or more are all light green. I have used the same conditional formatting tool and put new rules:
There are three rules used to conditional format, as is seen in the above picture. The arrangement and hierarchy of these rules are important. If you change the hierarchy, suppose the last one comes at the top, then the results will not be what you might expect. It is because, Excel will see the first rule and put all marks into the green which are 90 and above, then it comes to look at 80 and above and so on. So if you are having trouble in the conditional formatting, always watch for this hierarchy.
Conditional formatting is used for lot bits of work if you want a certain amount of data to have different fonts, different style, or background. It’s just that you have to learn how to make use of it.
Let’s see some of its other functionalities.
Let’s look at the above picture. I have dropped down the first box and a lot of conditions we can play with here. We can change the format of cells with certain cell values, with a specific text in the cell, or dates occurring between two dates or on a specific date, cells which are blanks or not blanks, cells with errors in formula or those which have no errors.
So you can see, even with this one option, you can have a lot of functionalities and if you play with it, you will go on learning that how powerful it is when you are handling a huge amount of data and you want to look into the data which has been highlighted or shown to you automatically.
This other box is self-explanatory, you can choose any of these functions to choose the value of the cell to be formatted.
If you choose the 3rd option in conditional formatting dialogue:
You can well imagine what it can do. It can format the cells by ranking the values; this is a powerful function to be used in marketing and sales.
And using the 4th option of conditional formatting for statistical tables are of a lot of value:
This 5th option is really interesting:
Format unique or duplicate values:
Suppose in the above example, we want to see, say, in a science subject, who has got the same numbers, so we conditionally format that column by using this unique/duplicate option.
First, just select the Science column:
Select unique/duplicate options, select duplicate, click format to select a color and click OK and see the results:
As you can see the value which are repeated will be highlighted:
I think you got it. OK, moving on:
This last option as is shown above is used to format according to your unique needs, but putting formulas in them.
We will leave this part for now, when I will be using some complicated data, I will use this option and explain that later.
Let’s see at the conditional formatting dialogue once more, I want to show you something more about the rules used:
If you select any of the cell for which we have used conditional formatting and then click “Conditional formatting”, this dialogue will be shown:
As you can see, all of the rules, which we have used on the sheet are shown here:
These are the 3-rules which we have applied:
These are the formats which we have applied, if the conditions for each cell is true:
And these are the cells to which the rules are being applied, the addresses of the cells are shown here, for example, see the first one “=$C$2:$I$9”, meaning the cell on C2 to I9, this rule will be applied:
This last option needs further mentioning:
“Stop if True” is used when you want Excel to stop and do not go to the other rule, if the conditions are true, otherwise go to subsequent rule. You can see that I have not selected this for any rule, because I want Excel to apply all the rules, whether the conditions are true or otherwise.
One more thing to emphasize here is that the background color which this command has given to the cell in which conditions of the rules are true, cannot be modified manually by applying different colors using this tab in the toolbar, you can see for yourself:
The conditional format only changes if you either remove the rule or the condition becomes false.
The last thing about this command is as how to remove the conditional formatting, it is easy:
Either you can just use the following command:
Just click on clear rules from the Entire Sheet and all rules will be deleted, or if you want to delete a particular rule, click on Conditional format, manage rules:
Select the rule and click delete rule.
Of course, you can undo it by undo command, as you can in MS Word.
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.