How to do Conditional Formatting in Excel

You might have come across a situation in which you need to analyze a huge data in Excel within a short time frame. That makes you worried how difficult it could be to analyze thousands of Excel rows with data values and present a final report. Well, there is no need to worry because we are going to tell you about a great feature of Excel called as Conditional Formatting which is going to make your data analysis a breeze.

Let us take an example of a University which has conducted an entrance exam for Management Courses. Below sheet represents the students Application number along with Name, High School Percentages, Certification Exam Score, Entrance Test Score, Sponsorship Status and whether they are applying for scholarship or not.

Refer below image

Now you need to present analysis for the data to the Selection Committee so that they can make a wise decision and publish the results accordingly. To do manual analysis over the data is surely a time-consuming task and might not be accurate. So, we will use the functionality of Conditional Formatting in Excel to bring out various analysis results out of the data. Check the various case scenarios explained below.

To find out the names of all Women Candidates in list who are married (Having Title – Mrs.)

For finding all Married Women candidates from the list we will select the data range from Cell B3:B25 and click on Conditional Formatting under Styles Group in Home Tab. Further click on “New Rule” which will open a pop up box.

Refer image below

Now you will observe different Rule Types. Select the Rule Type “Format only Cells that Contain”. In the section of Edit the Rule Description change the value to Specific Text in the first dropdown, beginning with in the second and our Text “Mrs.” In third dropdown.

Refer below image

Now, you need to set the formatting option. Click on the format box and select the color to highlight the data post application of defined condition and select Italics under Font section. You can also define other formatting options such as making text Bold, Effects, Border etc. Once you have completed the formatting options Click on OK.

Refer below image


Now all those Candidates will be highlighted having Title as “Mrs”. At the beginning of their name.

Note – We did not used the conditional formatting of “Text Contains” since in that case Cell B17 would also become highlighted which does not contain title but similar keywords.

Refer below image

Let us understand another case for finding out the highest scores from each of the Qualifying Exams.

To find out the Top Five High Scores in list.

For finding the Top five scores in respective exams we will use Conditional formatting. Select the data range from C3:C25 and click on Conditional Formatting and New Rule.

Now select the Rule type as “Format only top or bottom ranked values” and change value of drop down to Top and value to 5 in Edit description section. Now select the formatting options from the last section as desired.

Refer below image

On clicking Ok Excel will highlight the Top Five scores under High School Percentage Column.

Refer below image

Similarly, we can find out the top five scores in Certification and Entrance Test by applying conditional formatting and choosing a different color scheme to identify each data uniquely.

Refer below image

Note – In the above image for column D there are seven highlighted entries since multiple candidates have scored same number.

Let us consider a case where we need to highlight scores more than 90, scores in the range of 60 to 89 and scores less than 60.

To utilize the icon set feature of Conditional Formatting.

We will use the icon sets for highlighting the score ranges by selecting all the data range. Select Cell range C3:E25 and click on Conditional Formatting – icon sets menu item – More Rules.

Refer below image

Select the Rule Type as “Format all cells based on their values”, format style to Icon Sets and Icon Style to the desired style, set the range indicators and click Ok. For our case, we will put value as >=90 and type as Number for Right Icon, for exclamation icon we will put value as <90 and >=60 – type – Number and for Wrong it will consider all values <60.

Refer below image

Observe the conditional formatting of icon set which clearly highlights the scores as per the defined criteria.

To Highlight the Data Value of a Column.

Let us now take a scenario in which we need to highlight the data value of a column. Suppose, we need to highlight all the candidates which are industry sponsored. We will create a new rule and in the Rule Type select “Format only cells that contain”, select “Cell Value”, Select “Equal to” and “INDUSTRY SPONS” in the edit section. Choose desired formatting and apply.

Similarly, we can highlight all the candidates who have applied for Scholarships.

Refer below image

To remove the Conditional formatting choose Clear Rules then select among Cells or Sheet. You can also manage the Conditional Formatting Rules from the Manage Rules, Change Rule priority and delete the rules.

Refer below image


Conditional formatting menu has several predefined conditions ready for use such as Top 10, Below 10, Average, Duplicates, Date Occurring, Greater than & Less than etc.



Using Data Bars and Color Schemes.

To highlight the value of cells by usage of bars we will use the Data Bars under Conditional Formatting.

Select the data range and type of Data Bar you will observe that selected cells gets filled up with the data value bar indicating the cells with higher or lower values.

Similarly, you can use the Color scales on the selected data to color cells within a range of defined criteria based on Lowest value to Highest, Number, Percent or Formula.

Refer below image

So, after doing several case analysis we can easily deduce many information from the data set such as Name of Married Women Candidates, Candidates who have failed in all three tests based on the criteria, Top scores achieved, Candidates with Industry Sponsorship etc. It is a very useful feature of Excel which quickly lets you find out any variance in data range or simplify your data for further analysis or presentation.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Combine Cells in Excel

You might be wondering what’s the need of combining cells in Excel when one can simply type the data of both cells to a new Cell. Well, we would say yes you can do it but suppose you have a large dataset consisting of thousands of rows then it would not be an easy task to manually type in the entire data from each cell.

So, to avoid all the manual effort of typing the data we are going to tell you the process of Combining Cells in Excel by using the ampersand sign “&” or by using the formula of Concatenate Cells.

Let us Understand with Example Case

Now let us consider an example case of a Housing Society’s Data. List consists of Name, Surname, Apartment Name, Block, Room No. of all the residents in the Society.

Refer below image


Suppose we need to combine the Name and Surname of the resident in Cell B3 and C3 to a new Cell G3. We will write the formula as =B3&C3 (click on Cell B3, put ampersand sign, click on Cell C3).

This will result into combination of text as “NicolasTheoder” but there is no space in between the text to make it presentable.

Adding Spaces between Combined Cells.

So, to make it readable we will write the formula in Cell G3 as

=B3& ” ” &C3 (click on Cell B3, put ampersand sign, put double quotes with space, ampersand sign and click on Cell C3).

This will result into combination of text as “Nicolas Theoder” (with proper space in between the texts).

Refer below image

Now if you want to combine the names and surname for all entries you do not need to type the formula again. Just hold the cell G3 from bottom right corner and drag towards cell G12. This will copy the formula to all the cells along with corresponding cell references.

Refer below image

Adding text/punctuation in between combined Cells.

In case you wanted to combine the Names & Surname with comma or semi colon or dash in between you may write the formula as

=B4&”,”&C4 or B4&”;”&C4 or B4&”-“&C4 respectively.

Refer below image

Combining Cells along with Custom Text

If you want to combine Cells of Name and Surname along with Salutation in Column H. Then you need to write the formula as

=”Mr./Ms.”&B3&” “&C3 (put = sign on cell H3, put double quotes with text Mr./Ms., put &ampersand sign, click on cell B3, put ampersand sign, put double quotes with space – to give space between name and surname, put ampersand sign &, click on Cell C3).

Refer image below

Combining Multiple Cells

Consider the scenario in which you need to combine Apartment Name, Block and Flat no. from Cells D3:E3:F3 to Cell I3 (Address Column). We will use below formula to combine

=D3&” “&E3&F3

(put equal to sign in Cell I3, Click on Cell D3, put ampersand sign, put double quotes with space, put ampersand sign, click on cell E3, put ampersand sign, click on cell F3).

You can drag the formula to fill in the cell range to I12.

Refer below image

Combining Cells along with Custom Text Before and After Combined Cells

Now consider a scenario where we need to add Custom Text before and after the combined cells. We will combine a custom text “The Address of” with Cell Value H3, Custom Text “is”, Cell Value of I3. We will click on Cell J3 and write formula as

=”The Address Of “&H3&” “&” is “&I3

(put equal to sign in cell J3, enter the custom text in double quotes with space at end, put ampersand sign, click cell H3, put ampersand sign, put double quotes with space, put ampersand sign, type custom text within double quotes with a space at end, put ampersand sign, click on cell I3).

Drag the formula to cell range I12.

Refer below image

Combining Cells along with Formula

Now let us consider a scenario where in we need to combine two cells with one having a formula. In that case, also we will simply use the ampersand sign to combine both cells and result will be displayed accordingly.

Combining Cells Using Concatenate Function

Excel has a built-in function to combine cell values by using function “Concatenate”. You need to put equal to sign in the cell where you want to combine the cells and then type concatenate followed by cell separated by comma within brackets. As you press enter the cell will be combined. You can multiple cells in the same manner.

If we have used the function for combining Surname along with name, then formula would be

=CONCATENATE(C4,B4)

Similarly, if we want to put space between concatenated texts then we will use ampersand sign in between the formula. The formula would look as below =CONCATENATE(C4&” “&B4).

Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Add Up a Column of Numbers in Excel

You might be surprised to know that major use of Excel spreadsheet is only done for summation of large data ranging across all levels of Organizations, Business and Personal Lists.

Adding up of cells can be an important part of data analysis where in your cell contains some sales figure or expenditure or credit/debit bills etc. So, let us start learning summation process of columns in the simplest way.

Assume you have a data record of Students Marksheet as shown below in image


Column A shows the Subject Names and Columns B,C,D,E,F,G shows students marks respectively.

Now to sum up each student marks we will add up the numbers present in respective Columns of student. We will use the below formula in Cell B11 for adding of numbers in Column B

=B4+B5+B6+B7+B8+B9+B10

Similarly, for adding the numbers in Column C we will use below formula in Cell C11

=C4+C5+C6+C7+C8+C9+C10

Refer below image showing formula usage in Cell B11


Subsequently similar formula can be written for other columns summation where in Column letter will change and Cell range would be from 4:10

Excel takes care of any modification in the cell ranges defined, in this case if there will be any change in students’ marks then the formula would instantly reflect the new total. So, you do not need to type in the formula again and again.

So wasn’t it an easier method to add up the numbers in a column?

Alternative to above method of writing Cell ranges to add up you can also click and define them by below method.

Select the Cell B11 put an equal to symbol and now click on cell C4 then press the + symbol now again click on Cell C5 then + symbol till you reach the end of range.

But you might be wondering what if I have multiple records do I need to type in the formula again and again? Well the answer is No. Excel itself is a smart software having many in built features. You only need to write down the formula once let’s say for Cell B11 only and then hold the bottom right corner of the cell and drag towards Cells – C11, D11, E11, F11, G11.

Refer image showing applied formula in Cell C11


This will copy the summation formula you typed to these cells and auto arrange the reference cells. Which means that Summation of cell C11 will add numbers from the range C4:C10 only irrespective of the fact that the formula was copied from B11 cell defining range of B4:B10.

Isn’t it a smart feature!

Now let’s go to next level – What if I have thousands or Lac’s of records for addition, do I need to type in the addition formula for first column comprising of thousand entries?

Well the Answer is again NO. For handling, large volumes of data addition excel has a built-in function called as SUM. This function can be called by selecting the cell and put equal to symbol followed by SUM with brackets for defining ranges of data to be added.

Now let us use this formula in our previous case. We will select cell B11 and put equal to symbol along with function SUM defining range from B4:B10

=SUM(B4:B10)


On pressing enter Excel will add up the numbers in column B. Similarly, we can use this function for all other Cell ranges to add up the student’s marks. Alternatively, you can also drag the formula to all relevant cells for auto summation.

Another interesting feature of addition function in Excel is that you can define any range while writing the formula or using the SUM function. Refer below image for example.

Cells ranges have been defined from each of the columns and the Excel adds up each cell value as desired.


Excel gives another smart feature of Auto Sum for those who find typing formula cumbersome or difficult.

To use the Auto Sum feature, select the cell you wish to calculate the sum in and then click on Auto Sum button present under Home Toolbar (highlighted). Function of SUM will be automatically applied to the given cell for entire table range.


If you want to select specific cells in a column for addition, then select those cells and then press Auto Sum button.

If you want to apply the Auto Sum function to complete numeric data, let’s say in our case we need to calculate the Total Marks of all students at once then select Cell ranges from B4 to G10 and press the Auto Sum button.



All columns will display the total marks below the selected range cells i.e. in Cell Range B10:G10

Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How to Blank Out Cells in Excel

You might be wondering what is the need to Blank out cells in excel when you can simply use the work area in sheet and ignore rest of the cells. Well the answer is you do need to blank out the unused cells in excel when you do not want that users should enter any data or information into cells other than defined work area or you wish to conceal your sensitive information or formula.

So, to blank out the cell completely leaving the work area select the row from where you want to blank out cells and Press Ctrl Key + Shift key + Up/Down Arrow this will select all the rows in excel. Now Right Click on any row and click on Hide. In the same way select the column from where you need to blank out cell press Ctrl Key + Shift key + Right/Left Arrow. This will blank out all the cells and you work area will be the only highlight. You can also select the Hide option from the Home Toolbar, Cells Group- Format button. Clicking on the format button opens the menu which has a visibility section with a sub menu to Hide Rows or Columns. This will have the same action as selecting Hide from the Right Click menu.

Refer below image


 

You can also blank out empty cells in Rows and Columns by reducing the default height or width to zero for selected rows and columns. To blank out cells in selected row click on Format button in Home toolbar under Cells group. Now select the Row Height entry under cell size heading. You will observe below screen. Just enter 0 and press Ok. Your selected cells of Row will be reduced to 0 height which is like using Hide Row option. Similarly, to blank out cells of a column you can reduce the Column width to 0 and achieve the same results using Hide Columns option.

Refer below image


 



 

 

Now Let us understand this with an example of a University in which you need to fill in a form in excel get it printed, approved and submit to the Administrative Department.


The form has a small work area and lot of unnecessary rows and columns which makes the work area non-focusable. Some students might also start typing text or requests in empty area which might create issues in alignment of text while printing.

To keep the students focused on form and avoid any use of space outside work area we will Blank out the cells from each of the unused Rows and Columns. Select Column K since our work area is till column J only and press Ctrl Key + Shift Key + Right Arrow Key this will select all the cells till it reaches the end column of a worksheet. In case you want to blank out selected columns only then you can manually select the columns as desired.

Refer image below


Now Right click on any column this will open the menu screen and select Hide from the Menu screen.

Refer image below

 


 

On clicking Hide option all the selected columns will get hidden and you will be presented with below screen.

Refer below image


Now select Column C and press Ctrl Key +Shift Key + Left Arrow key to select all cells from Column A to C

Right click to get the menu and select Hide option. You will observe that now all the columns are hidden and cells blanked out.

Refer image below


 

Now to blank out the empty cell in Rows we will follow the similar procedure. In this case we will select Row 3 since below this row our work area starts and then press Ctrl Key + Shift Key + Up Arrow Key this will select all the rows from 1-3. Now right click on any row and select the Hide Option from the Menu.

You will observe that empty cells above the work area are now blanked out.

Refer below image


 

Now we are left with empty cells of rows down to work area. So, we will select the Row number 21 since it is the row just after the last workspace row and then press Ctrl Key + Shift Key +Down Arrow Key.

This will select all the Rows till the end of limit of worksheet. Now right click on any row and select the Hide option from the menu.

Refer below image

 


You will observe that all unwanted cells are now blanked out and you have only your workspace area left on the worksheet.

Refer below image


 

Now our form is well focused on the worksheet and there is no empty cell to write any unwanted text.

 

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!