How to Apply Conditional Formatting in Excel


How to Apply Conditional Formatting in Excel

Viewers we described formatting of cells and Excel worksheets of Microsoft Excel in previous articles. During which we had discussed formats for digits, dates, text and special codes values. New we will require revised formats after inserting new data in excel datasheet having different digits, dates and text etc. So, we will again change our format. Suppose we have to set the eldest student in our class. Then we will analyze birthdate of every student. But Microsoft Excel facilitates to highlight eldest student of the class. We can set all types of these options through setting conditional formatting in excel. Following is the method to apply conditional formatting in MS Excel:-

How to Apply Conditional Formatting in Excel

Conditional formatting assists to analyze data as answer specified in mind. For instance, we have to collect rupees 259600 from students on account of monthly fees. If we may set it according to our target, but it is received lesser than the specified amount then it will be highlighted as per given settings. We can apply conditional formatting on a cell, table or pivot table as per our need. We can set conditional format in numbers, icons, colors and other format changes based on conditions. If the set conditions are true then prescribed formatting will be shown as set by us otherwise it will show other formats. We will describe to apply conditional formatting in succeeding paragraphs.

Suppose we have not to receive monthly fees from the students of Class 6th from 1500, and then we have to set the conditional formatting if fees amount is less than 1500. We will follow under mentioned method to apply this condition.

Select whole column H upto last row of student fees, then click on conditional formatting. Options showing tables formats, icons format etc will be shown and where you see at end also “New Rules. Click on “New Rules” which will open following window:-


You can see many rules in above window, but click on “format only cells that contain”, which will extend to following window:-

From the second dropdown box, select option “less than” instead of between option. Type 1500 in next box option box, later click format button shown on bottom center of the window. Which will further open formats window; from where select desired formats of cells and text, and then click OK. Which will lead back to main rules window from where press ok. Your desired conditional formatting in excel will be functional on selected cells of column.

Find Eldest Student through Conditional Formatting in Excel

To find the eldest student through conditional formatting in excel, we can find it through only birth date columns. So, we will apply conditional formatting on “ Birth Date” Column i.e. Column “E”.

We will highlight here that Microsoft Excel or any other database software considers time and dates as digit. For instance we have write “22 Dec 20” in date formats, MS Excel will give value it equal to 44187; or we type 5-May-05 then; excel will consider it equal to 38477. These values define that lesser value is elder date and higher value is equal to the youngest date. Apply following method to apply to find eldest student in school or class.

Highlight whole birthdate column i.e Column “E”; then click on conditional formatting from home tab. Click “New Rule” Option which will open “New Formatting Rule” window. Click on the option “Format only top or bottom ranked values”. This command will lead to following window having options top or bottom. Later, select bottom option and type 1 next option button. Later click on format button shown on bottom right corner of the window. This command will open formats window from where you can select desired formats and then click ok. Which lead back to New Formatting rule; where you will click OK. The eldest date will be highlighted as per desired formats.

Conditional Formatting, Top Value

There are many other procedures for applying conditional formatting in MS Excel on summaries and tables of different accounts, which we shall explain in our succeeding articles.. For more details watch the video on our YouTube Channel Suhni Sindh and watch video on this link

Post a Comment