March 19, 2014

Conditional Formatting in Microsoft Excel

Conditional formatting in Microsoft Excel is the process by which you highlight specific data based on a condition. You can highlight students with marks greater than 90% in Maths. You can colour the salaries of employees who get a salary greater than or equal to 10000. You can highlight employees whose sales in a quarter are equal to or greater than a specified threshold. You can also highlight the duplicate entries in a range of Excel spreadsheet cells using conditional formatting. This feature of the Microsoft Office Excel suite is indeed very useful to analyze data. The training video above explains many of these aspects.
Note: The idea of placing the conditional formatting icon in the quick Access toolbar via the Microsoft Office Button using the Excel Options was to show how the process simplifies the use of a feature in the program.
How to implement conditional formatting step by step:

  • Select the data B4:B12 under the header Salaries
  • In the Styles Group select Conditional formatting
  • Go to Highlight Cells Rules and then select Greater Than…
  • In the new window ‘Greater than’ under ‘Format cells that are GREATER THAN:’ enter 10000
  • The formatting of the cells selected is ‘Light Red Fill with dark Red text
  • Click on OK

All values greater than 10000 are highlighted accordingly and the user can quickly view all the employees whose salary is greater than 10000.

Now you perform another conditional formatting by repeating the above process but this time you wish to highlight all salaries between 7500 and 9900.
Using the same procdure you can also Highlight Duplicates. In our example, the data entry operator has entered the employee name Barbara twice and now it is highlighted with a red border. Of course you can also do a ‘custom fromat’.
You will also notice that using conditional formatting on a single data range you have now three different information:

  1. All employees getting a salary greater than 10000 are highlighted with ‘Light Red Fill with dark Red Text’
  2. The employees drawing a salary between 7500 and 9900 are highlighted in green
  3. Duplicate entries have a green colour with a red border,

Conditional formatting therefore offers a great tool to highlight data and extract important information quickly and easily.
View the video to learn quickly and easily about conditional formatting:

Further reading:
Quick start: Apply conditional formatting

2 thoughts on “Conditional Formatting in Microsoft Excel

  1. Dear Sir,
    It was very nice and useful. Thanks lot!
    I’d one doubt, If there are multiple duplicate values I want to highlight them with different colours. Like same way I want to fill with different colour borders for the multiple duplicate values. Is it possible?

Comments are closed.