Conditional formatting in Excel is a powerful feature to visualize data or output in dashboard. There are multiple method or procedures to apply conditional formatting in excel. Apply formats in excel is very simple and user friendly to work, you will find easy by simply applying your thoughts with the help of wizards. In this article you can find method of applying the conditional formatting to your table or data that you are going to present and is explained in very simple procedure. The shortcut command is ALT + H L to enable Conditional Formatting Palate.
As you open excel you will find on HOME Tab on ribbon "Conditional Formatting icon having multiple options in drop-down of the button.
Normally few questions arises into mind of user, I will try to explain in a very simple language.
What is Conditional Formatting in Excel?
- Conditional formatting is nothing but the method of visualizing or focusing the value to audience. for example we can say the if you want to highlight or focus the value that is higher in the set of column. Visualizing the value you need to highlight the greater value such a way that attracts the audience or reader of the data set.
Why and When to use Conditional Formatting?
- The answer to this question is simple, when you want to publish the summary report to audience or when you want to analyze the data which is in large number. As said in above example, to highlight value that is greater than any conditional value.
What are the ways or methods to apply conditional formatting?
- Once you click on HOME tab on your Ribbon, you will see Conditional formatting feature available, there you can find multiple options available, which we will try to learn one by one.
How can I apply conditional formatting in my dashboard?
- We will learn the procedure of applying condition formatting in dashboard.
How to create Conditional Formatting rules
- This depends on your data or how you are going to apply.
What is Conditional Formatting in Excel?
- Conditional formatting is nothing but the method of visualizing or focusing the value to audience. for example we can say the if you want to highlight or focus the value that is higher in the set of column. Visualizing the value you need to highlight the greater value such a way that attracts the audience or reader of the data set.
Why and When to use Conditional Formatting?
- The answer to this question is simple, when you want to publish the summary report to audience or when you want to analyze the data which is in large number. As said in above example, to highlight value that is greater than any conditional value.
What are the ways or methods to apply conditional formatting?
- Once you click on HOME tab on your Ribbon, you will see Conditional formatting feature available, there you can find multiple options available, which we will try to learn one by one.
How can I apply conditional formatting in my dashboard?
- We will learn the procedure of applying condition formatting in dashboard.
How to create Conditional Formatting rules
- This depends on your data or how you are going to apply.
Basically you can apply conditions in two ways.
- In-built conditional formatting rules
- You can create your own rule with the help of conditional formatting wizards.
There are multiple methods to apply own rule. As you click on Conditional formatting you will get the drop-down as shown in below image.
Here you can see multiple in-built options available.
Highlight Cells Rules and on right the side you can again see >Greater Than,
Now you have a data set showing some values in a set of series and you want to analyze and find out outlier in the series then follow below steps.
Select the range of data series
Click on Conditional formatting drop-down small icon available on right side below of icon
Select Greater than or Less than to show your outlier value
In result you will see color is automatically applied on those values may it be greater or less the rest of values.
You can go through below animated example or you can download Workbook
2. Highlighting the Top 10 or bottom 10, Above or below average values in selected series of data range. See below Image
Even We can highlight top 10 Items or below 10 items may it be in numbers or percentages. You can also change color of your choice as shown in example in above animated sheet
3. Data Bars - Highlighting the cells based on value from high to low from the given range of data set.
This helps us to fill the color in cells based on highest values to lowest value as shown in below image. Of course you can apply any in-built rule that suits your requirement or available data set. The functionality of applying color is very user friendly. Simple practice will build your analytical presentation skills.
Color Scales - This is another alternative to design your data presentation. This type of formatting fills the color in gradient format from light color to dark color based on values in given range of data series.
Icon Sets - IF you are going to highlight the data by flagging the values or traffic light circles on even small graphs. Options are
- Directional
- Shapes
- Indicator
- Ratings
By default the color are based on by default rules. We will come across how we can apply our own rules.
How you are now comfortable to apply conditional Formatting to your workbook.
No comments:
Post a Comment