Microsoft Excel Tutorial
Data Analysis - What is conditional Formatting and How to use it?
The main purpose of using Microsoft excel for data analysis is its various easy to use tools due to which comparing the date becomes very easy. Excel is widely used in offices, schools to simplify the process of handling large data. Such as information about students of a college, employees of an organization. Excel has so many powerful tools like data validation and conditional formatting.
1. Conditional formatting is a function that allows us to differentiate the data on the basis of various conditions that we apply, hence known as conditional formatting.
Conditional Formatting allows you to apply different formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.
For example, you can have a cell appear bold only when the value of the cell is greater than 50 or if the value of the cell is less than 50.
This tool can highlight the cells that are chosen with different formatting options like color of the cell; the font style of the cell can apply font effects like strike-through effect and can change the cell border as well as cell color.
2. You can change or add new rules as well as manage the existing rules.
3. Conditional formatting can be used to find the top and bottom values, to find cell that contain a certain letter or word, to find duplicate values, to find variances in a range of values with a quick glance, to find numbers between a given range and to differentiate them, to find cell with a certain date occurring in it,
This function highlights a particular cell or cells.
This tool will save your hours of time that would have been wasted in visualizing the big data that is spread in thousands of rows.
We can visualize data with the help of data bars, color scales and icons.
At the same time we can apply 2-3 rules to the same group of cells.
To completely understand the tool I would suggest you to create a sheet with some values in the cells and then try each of the option available in the conditional formatting tool. It may seem to be time consuming but trying it by your own will clear your doubts and will make you a pro.
However first watch this video to get the idea of this tool and then try it. I have tried to give all information including the options available in it.
Various options available in conditional formatting are as follows:
1) Highlight cell rules= "greater than", "less than", "between", "equal to"
2)Top/bottom rules="top 10", "top 10%", "bottom 10 items", "above and below average" etc.
3) Data bars =
A] solid fill – it shows a colored data bar in the cell which is proportional to the value in that cell. If the value of the cell is greater then the length of the data bar will be more and if the value in the cell is smaller then the bars will be of short length. In short the bar will be long if the cell has bigger value.
B] Gradient fill- This is same as solid fill but instead of solid color gradient with varying color shading is used.
4) Color scales-
In the color scale a range of cells is selected and gradients of two to three color are shown and the shade of the color represents the value.
5) Icon sets-
There are many symbols such as three arrows, traffic lights or ratings used to describe values of cells. Each symbol represents a certain value in a cell.
The symbols used are mainly grouped –Directional, Shapes and Ratings.
You can also create new rules according to your requirement and also delete or manage these rules.
Microsoft excel expert
Microsoft excel data analysis
Microsoft excel tools
Microsoft excel programs
Microsoft excel tutorials
Tip: - To try how it works download excels sheets already on the web and try to apply conditional formatting options.
Or create a new worksheet and manually add some information in tabs.
This video on creating a worksheet will help you=
Microsoft Excel Tutorial - How to Create And Edit Worksheet.