Cell Styles In MS Excel 2007
For users who extensively use MS Excel, they would certainly like to color the respective cells of a row or a column. Many of us also use coloring cells to denote which row or column means what.
MS Excel 2007 comes with a whole new feature of coloring the cells which can help us easily mark cells and later on recall which color means what.
Let us show you how. Consider the below as a database that we are preparing. We have plotted a few set of numbers in Series 1.
Now let us form a few more series in order to show you how easily we can mark each of the different series.
Series 2: Values are 10% of Series 1
Series 3: Values are 1.5 times of Series 1.
Series 4: Values are calculated as (Series 3 / Series 2).
Series 5: Values are calculated as (Series 1 - Series 4).
Series 6: Values are calculated as the total of all the above series (Series 1 to Series 5)
Series 7: Values are calculated as a Series 1 as a percentage of Series 6.
Now we are ready with our database as shown below:
Next let us start marking our database as per our convenience. Lets see how.
Select Series 1 and point to “Cell Styles” and as this is the base from where we are starting lets call it as "Input". So we click on input as shown below.
Once this is done, now select Series 2 and again point to “Cell Styles” under “Styles” and as series 2 is getting calculated on Series 1 so lets call it as "Calculation" as click on Calculation style as shown below.
Now we move on to Series 3 and now this time lets just shade it with a color named “Accent 6” the one under “Themed cell styles”. Here you will notice that once you select Accent 6 the color of the text automatically gets changed.
Now we select Series 4 and as we see all the values in series 4 are equal so lets call it as "Netural" and click on Neutral.
Now lets move to Series 5, here as we know the values are calculated as a difference of (Series 1 – Series 4) which basically means it is linked to two others cells. So lets call it as “Linked” and click on Linked in the cell styles. Here you can see that the double underline come automatically.
When we look at Series 6 we have the values calculated as a total of all the above series (Series 1 to Series 5). So lets call it as “Total” and click on total in the cell styles.
And at last we move on to Series 7 in which values are calculated as a percentage of Series 1 on Series 6. But you would notice that we have not changed the number format to percentage which you will get to know shortly. Now select the cells of Series 7 to be marked and click on “Cell styles” under “number format” click on “Percent”. The number gets converted into percentage format.
That's it. You are done marking your database very easily using in-build Microsoft Excel feature of cell styling.
Tip: When you want to preview a particular style just point to that style and do not click on it. You can see your cells getting previewed in that particular style.
Now let us form a few more series in order to show you how easily we can mark each of the different series.
Series 2: Values are 10% of Series 1
Series 3: Values are 1.5 times of Series 1.
Series 4: Values are calculated as (Series 3 / Series 2).
Series 5: Values are calculated as (Series 1 - Series 4).
Series 6: Values are calculated as the total of all the above series (Series 1 to Series 5)
Series 7: Values are calculated as a Series 1 as a percentage of Series 6.
Now we are ready with our database as shown below:
Next let us start marking our database as per our convenience. Lets see how.
Select Series 1 and point to “Cell Styles” and as this is the base from where we are starting lets call it as "Input". So we click on input as shown below.
Once this is done, now select Series 2 and again point to “Cell Styles” under “Styles” and as series 2 is getting calculated on Series 1 so lets call it as "Calculation" as click on Calculation style as shown below.
Now we move on to Series 3 and now this time lets just shade it with a color named “Accent 6” the one under “Themed cell styles”. Here you will notice that once you select Accent 6 the color of the text automatically gets changed.
Now we select Series 4 and as we see all the values in series 4 are equal so lets call it as "Netural" and click on Neutral.
Now lets move to Series 5, here as we know the values are calculated as a difference of (Series 1 – Series 4) which basically means it is linked to two others cells. So lets call it as “Linked” and click on Linked in the cell styles. Here you can see that the double underline come automatically.
When we look at Series 6 we have the values calculated as a total of all the above series (Series 1 to Series 5). So lets call it as “Total” and click on total in the cell styles.
And at last we move on to Series 7 in which values are calculated as a percentage of Series 1 on Series 6. But you would notice that we have not changed the number format to percentage which you will get to know shortly. Now select the cells of Series 7 to be marked and click on “Cell styles” under “number format” click on “Percent”. The number gets converted into percentage format.
That's it. You are done marking your database very easily using in-build Microsoft Excel feature of cell styling.
Tip: When you want to preview a particular style just point to that style and do not click on it. You can see your cells getting previewed in that particular style.
Concatenate Function In MS Excel 2007
In case you wish to combine all the characters in different columns, MS excel 2007 comes with an inbuilt function of concatenating.
Let us show you how. Consider the example below.
Now let us show you how to do that. Just type “=concatenate” open a bracket and select the first cell you need to combine and then put a comma and in order to insert a space between words we need to put a space between apostrophe (“ “).
Again put a comma and do the same for the remaining cells as shown below.
As you see above we have put up the concatenate function for all the cell to be combined. Now as soon as you press enter, all the cells will be combined.
Now just copy paste the same formula below and you are done combining the cells.
Now let us show you how to do that. Just type “=concatenate” open a bracket and select the first cell you need to combine and then put a comma and in order to insert a space between words we need to put a space between apostrophe (“ “).
Again put a comma and do the same for the remaining cells as shown below.
As you see above we have put up the concatenate function for all the cell to be combined. Now as soon as you press enter, all the cells will be combined.
Now just copy paste the same formula below and you are done combining the cells.
Splitting Text In a Cell To Columns - MS Excel 2007
In Microsoft Excel 2007, we can split the characters within a particular cell into different columns. It can be done in two ways:
• Fixed Width
• Delimited
Fixed width: This option is basically used when the characters in a particulars cell are equal in format.
Delimited: This is used basically when you need to split characters by way of any condition such as a symbol etc.
You will get a better clarity on both once we explain you taking an example.
Fixed width:
We will now take you through as to how we do fixed width text to column splitting.
Consider the below data that we are considering for our example.
Now in the above data we have a combined data available in one cell itself in different rows. We have No, Name and Place in one cell itself. Let us now see how we can split it into different columns.
Here you can see that all the data in different rows are of the same format i.e. the No is of 5 characters, Name of 4 characters and the last one doesn’t matter as it has to go into the last column. Therefore we will apply a fixed width here.
Select the entire data that needs to be split, point to Data and then point to “Text to Columns”
Once you click on “Text to Columns” a dialog box opens up, as shown below.
As you can see Delimited is selected by default. But here we need Fixed width. Click on "Fixed width" and click "Next".
Once you click "Next" you will be taken to the next step of “Convert Text to column wizard”.
Now just click on from where you need to split the cell to columns. Once you click you will be able to see arrows appearing.
Once done just click on "Next" and then click "Finish".
As soon as you click "Finish" the data gets split into different columns.
Here we split cells into columns by “Fixed width” option.
Delimited:
Now let us explain you how to splt cells to columns by “Delimited” option.
For this we will be considering the same data but let us change the size of the characters. This means Delimited is basically used when characters in a cell are not of the same size.
Consider the below.
Now follow the same step we did for “Fixed width”, instead just click on Delimited and click "Next". It takes you to as shown below:
Unselect the preselected “Tab” option and select “Other” and in the box provided type a hyphen (-) as shown below.
Now click on "Next" and then "Finish".
As soon as you click "Finish" the cells get split up into different columns. The only difference you will see here is that the hyphen (-) marks do not get split, however they are used as means to split.
That all with learning as to how we can split cells to columns using “Text to column” function in MS Excel 2007.
In order to join or combine the different columns into one cell you can use the “Concatenate” function.
Tip: While doing “Fixed width” it is not necessary to bring arrows after each character. You can click only for one split portion and rest remains intact. And while doing “Delimited” you can use predefined options such as Tab, semi colon etc in case your text has those.
Now in the above data we have a combined data available in one cell itself in different rows. We have No, Name and Place in one cell itself. Let us now see how we can split it into different columns.
Here you can see that all the data in different rows are of the same format i.e. the No is of 5 characters, Name of 4 characters and the last one doesn’t matter as it has to go into the last column. Therefore we will apply a fixed width here.
Select the entire data that needs to be split, point to Data and then point to “Text to Columns”
Once you click on “Text to Columns” a dialog box opens up, as shown below.
As you can see Delimited is selected by default. But here we need Fixed width. Click on "Fixed width" and click "Next".
Once you click "Next" you will be taken to the next step of “Convert Text to column wizard”.
Now just click on from where you need to split the cell to columns. Once you click you will be able to see arrows appearing.
Once done just click on "Next" and then click "Finish".
As soon as you click "Finish" the data gets split into different columns.
Here we split cells into columns by “Fixed width” option.
Delimited:
Now let us explain you how to splt cells to columns by “Delimited” option.
For this we will be considering the same data but let us change the size of the characters. This means Delimited is basically used when characters in a cell are not of the same size.
Consider the below.
Now follow the same step we did for “Fixed width”, instead just click on Delimited and click "Next". It takes you to as shown below:
Unselect the preselected “Tab” option and select “Other” and in the box provided type a hyphen (-) as shown below.
Now click on "Next" and then "Finish".
As soon as you click "Finish" the cells get split up into different columns. The only difference you will see here is that the hyphen (-) marks do not get split, however they are used as means to split.
That all with learning as to how we can split cells to columns using “Text to column” function in MS Excel 2007.
In order to join or combine the different columns into one cell you can use the “Concatenate” function.
Tip: While doing “Fixed width” it is not necessary to bring arrows after each character. You can click only for one split portion and rest remains intact. And while doing “Delimited” you can use predefined options such as Tab, semi colon etc in case your text has those.
Formatting Tables In MS Excel 2007
How about moving on next to formatting tables in MS Excel 2007! Here we will demonstrate how you can quickly format them with the help of Microsoft Excel 2007, which comes with an inbuit feature.
Consider the small table shown below.
Select the entire table to be formatted.
And now do the following:
• Once you select the entire table to be formatted, Under “Styles” point to “Format as Table”
• Select your required table format from the pre defined ones.
We are selecting the 4th one in the first row under Medium head.
As soon as you select the format it will give you a range to select. Here as you had already selected your range jus press ok.
And there, you are done. As soon as you press ok your fully formatted table is ready to present.
Quite simple, isn't it?
Select the entire table to be formatted.
And now do the following:
• Once you select the entire table to be formatted, Under “Styles” point to “Format as Table”
• Select your required table format from the pre defined ones.
We are selecting the 4th one in the first row under Medium head.
As soon as you select the format it will give you a range to select. Here as you had already selected your range jus press ok.
And there, you are done. As soon as you press ok your fully formatted table is ready to present.
Quite simple, isn't it?
Lock Rows and Columns In MS Excel 2007
Let's see here as to how we can lock rows and columns in MS Excel 2007. Locking rows and columns in an excel language is referred as “Freeze Panes”. You can either lock the entire row or lock the entire column or lock both row and column together.
Below you can see the database which we will be using to demonstrate how we lock rows and columns.
Now suppose you need to enter more data, below the last entry which in this case is 25th. So, as soon as you scroll down, the heading of the database gets hidden as it also gets scrolled up.
This is what happens:
In order to prevent this from happening, Microsoft Excel has a wonderful feature called “Freeze Panes”. In order to freeze panes or what generally people refer to as locking rows and columns, this is what you need to do:
From the place where you need to lock the row take your cursor just below that row in the first column. So here we need to lock the first row therefore lets take our cursor to the second cell in the first column just below the row which needs to be locked. Here the cell is A2.
Once you have placed you cursor at the right place. Follow the steps mentioned below:
• Point “View” from the menu.
• Click on “Freeze Panes” under “Window” option
• Select “Freeze Panes”
And you are done! You have successfully locked the top row. Now no matter how much you scroll down you wont loose out on the header.
In order to lock columns you need to follow the same steps which we did to lock rows.
We have an option to lock the first column. In order to lock the first column, which here is, “Sr No” do the following:
• Irrespective of wherever your cursor is placed, Point to View .
• Click on “Freeze Panes” under “Window” option.
• Select “Freeze First Column”
And you are done with, locking the first column of your sheet.
In order to lock a column just take the cursor to the first cell of the column next to the column which has to be locked. Here we are trying to lock the “Age” column, so let us take the cursor to cell E1.
After pointing cell E1 point to “View” then click on “Freeze Panes” option and click on “Freeze Panes”
And here we are done with freezing the columns. How much ever we may scroll right, we will be able to see the age column.
Just a point to be kept in mind: Whenever we freeze either a row or a column all the rows above it and all the columns to the left of the freezed ones also remain freezed.
Next we will demonstrate how do we freeze both row and column together. This can be done the following way: Once you have decided which row and which column you need to lock take your cursor to the cell just below their intersection.
Let’s say for example we need the "Sr No" and "Name" column always visible and the top row always visible. In order to do that go to the cell below their intersection. Here it is cell C2 and point to “View” menu and click on “Freeze Panes” Option and click “Freeze Panes”.
Now how much ever you may scroll right or how much ever you may scroll down, you will never lose out on the header or on the Name column.
Tip: The shortcut to lock (Freeze) and unlock (Unfreeze) either rows or column or both is Alt+W+F+F
Now suppose you need to enter more data, below the last entry which in this case is 25th. So, as soon as you scroll down, the heading of the database gets hidden as it also gets scrolled up.
This is what happens:
In order to prevent this from happening, Microsoft Excel has a wonderful feature called “Freeze Panes”. In order to freeze panes or what generally people refer to as locking rows and columns, this is what you need to do:
From the place where you need to lock the row take your cursor just below that row in the first column. So here we need to lock the first row therefore lets take our cursor to the second cell in the first column just below the row which needs to be locked. Here the cell is A2.
Once you have placed you cursor at the right place. Follow the steps mentioned below:
• Point “View” from the menu.
• Click on “Freeze Panes” under “Window” option
• Select “Freeze Panes”
And you are done! You have successfully locked the top row. Now no matter how much you scroll down you wont loose out on the header.
In order to lock columns you need to follow the same steps which we did to lock rows.
We have an option to lock the first column. In order to lock the first column, which here is, “Sr No” do the following:
• Irrespective of wherever your cursor is placed, Point to View .
• Click on “Freeze Panes” under “Window” option.
• Select “Freeze First Column”
And you are done with, locking the first column of your sheet.
In order to lock a column just take the cursor to the first cell of the column next to the column which has to be locked. Here we are trying to lock the “Age” column, so let us take the cursor to cell E1.
After pointing cell E1 point to “View” then click on “Freeze Panes” option and click on “Freeze Panes”
And here we are done with freezing the columns. How much ever we may scroll right, we will be able to see the age column.
Just a point to be kept in mind: Whenever we freeze either a row or a column all the rows above it and all the columns to the left of the freezed ones also remain freezed.
Next we will demonstrate how do we freeze both row and column together. This can be done the following way: Once you have decided which row and which column you need to lock take your cursor to the cell just below their intersection.
Let’s say for example we need the "Sr No" and "Name" column always visible and the top row always visible. In order to do that go to the cell below their intersection. Here it is cell C2 and point to “View” menu and click on “Freeze Panes” Option and click “Freeze Panes”.
Now how much ever you may scroll right or how much ever you may scroll down, you will never lose out on the header or on the Name column.
Tip: The shortcut to lock (Freeze) and unlock (Unfreeze) either rows or column or both is Alt+W+F+F
HLookUp In MS Excel 2007
Sometime back we came up with a post on VLookUp In MS Excel 2007. Now we will move on and explain how to do a HLookUp in MS Excel 2007.
Before continuing further, lets see what makes VLookUp different from HLookUp.
VLookUp vs HLookUp:
VLookUp is the term used to refer vertical lookup whereas in HLookUp stands for horizontal lookup. This means when we do a VLookUp, the values are searched vertically whereas when we do a HLookUp, the values are searched horizontally.
How to do a Hlookup?
In the figure below you can see that we have data available in "values" and "colour" for different categories.
We would require extracting data for the output.
As the data is available in horizontal format, in order to extract it in vertical format in the output header we need to use Hlookup function.
Lookup_value : This is the value on which you want to do a Hlookup.
Table_array: This is the area from which the data has to be extracted.
Row_index_num: This denotes from which row is the data to be extracted.
Please ensure that you always lock the table array cells. This will help you to copy paste the formula easily because the table array is the fixed place from where we want to extract the data for all hlookup values.
The above formula once done will extract the data for the Hlookup value (G)
Now in order to extract the data for colour of the Hlookup value, do the below. This is similar to what we did for values column.
And once done, just copy paste the formula of both "values" and "colour" below in the range.
And that’s it. You are done with Hlookup.
We would require extracting data for the output.
As the data is available in horizontal format, in order to extract it in vertical format in the output header we need to use Hlookup function.
Lookup_value : This is the value on which you want to do a Hlookup.
Table_array: This is the area from which the data has to be extracted.
Row_index_num: This denotes from which row is the data to be extracted.
Please ensure that you always lock the table array cells. This will help you to copy paste the formula easily because the table array is the fixed place from where we want to extract the data for all hlookup values.
The above formula once done will extract the data for the Hlookup value (G)
Now in order to extract the data for colour of the Hlookup value, do the below. This is similar to what we did for values column.
And once done, just copy paste the formula of both "values" and "colour" below in the range.
And that’s it. You are done with Hlookup.
Cell Referencing In Excel 2007
Cell referencing is a methodology in which the user can give reference to a particular cell in a sheet or to another sheet or a workbook can give reference to a particular cell in a sheet or to another sheet or a workbook.
There are two types of cell referencing:
1. Relative cell referencing
2. Absolute cell referencing
Relative cell referencing: while doing a relative cell referencing when you copy to another cell the formula will change relative to that cell address.
Absolute cell referencing: while doing an absolute cell referencing when you copy the formula to another cell the formula will NOT change relative to that cell address. The formula remains constant to the cell which the user has made an absolute reference.
Below Dummy Essentials explains both the formats of cell referencing.
Relative & Absolute cell referencing:
Example: If salary of people A to D is to be apportioned into savings on the percentage decided, Then we need to keep the percentage same (absolute) and the salary needs to change for every person (relative). Hence when entering the formula we need to lock the cell address of the percentage of savings. A cell can be locked by using the “$” symbol in the formula.
In the above formula we can see that the cell E5 has been locked which will keep the cell E6 as an absolute cell reference. Now once you copy the formula below the respective salaries will get multiplied to the cell E6. The cell E6 does not change once you copy the formula below.
In a given case if you need to provide the savings of the highest salary to the fixed percentage you need to do an absolute cell referencing i.e. both the cells needs to be fixed.
Now where ever you copy this formula you will get the same value in all the cells. Its primarily because both the cells have been made an absolute reference.
The logic and concept behind making a cell absolute and relative is the row and the column address in the cell. Any row or column can be made absolute and relative. For instance you want to keep the column same but need to change the rows put a “$” sign before the column name only.
In the above picture you can see the “$” sign is only stated. Now once the formula is copied in the next column “F” as well the values do not change. It happens because the column reference has been made a absolute.
The same can also be done for rows as well. And now that you have followed it all, you are an expert in cell referencing.
Listed/Ranked:
The Tech Directory
Cluster Maps