Excel Tricks with Tejraj

In continuation to series of posts on Conditional Formatting, we will learn how to use Data Bars in Conditional Formatting through this post. Conditional Formatting command can be found in Styles group under Home tab as shown in below image. Click on the Conditional Formatting option and select “Data Bars” option from the list of appeared options. We can now see all the “Data Bar” options available in excel. These “Data Bar” options are categorized into two groups.  1. Solid Fill 2. Gradient Fill: There are six different “Data Bar” colors available in each group. These colors are listed below: 1. Blue Data Bar 2. Green Data Bar 3. Red Data Bar 4. Orange Data Bar 5. Light Blue Data Bar 6. Purple Data Bar Each group with their respective colors are shown in below image: Solid Fill: Gradient Fill: Now we will see how we can apply these “Data Bars” on our data. For this we have created dummy data of few employees and Incentive received by them. Example with Gradient Fill: 1. Select all...

This blog post is in continuation to our series of posts on “Conditional Formatting”. In this blog post we will learn how to highlight “Bottom 10%” values from selected set of values with the help of “Conditional Formatting”. Conditional Formatting option we can find in Styles group under Home Tab.  Now the main question is what does it mean by highlighting “Bottom 10%” values from selected set of values? It means “Conditional Formatting” will highlight Bottom 10% values from total no. of values. If our total values are 10 then excel will highlight Bottom 1 values. If our total values are 20 then excel will highlight Bottom 2 values. If our total values are 30 then excel will highlight Bottom 3 values. This is the basic concept behind this option. So let’s learn how we can highlight these Bottom 10% values using “Conditional Formatting” in excel. To explain this topic in detail, I have listed total 50 values as shown in below image. We will see how we can highlight Bottom 10% value...

This blog post is in continuation to our series of posts on “Conditional Formatting”. In this blog post we will learn how to highlight “Top 10%” values from selected set of values with the help of “Conditional Formatting”. Conditional Formatting Top 10% option we can find in Styles group under Home Tab.  Now the main question is what does it mean by highlighting “Top 10%” values from selected set of values? It means “Conditional Formatting” will highlight Top 10% values from total no. of values.  If our total values are 10 then excel will highlight Top 1 values. If our total values are 20 then excel will highlight Top 2 values. If our total values are 30 then excel will highlight Top 3 values. This is the basic concept behind this option. So let’s learn how we can highlight these Top 10% values using “Conditional Formatting” in excel.  To explain this topic in detail, I have listed total 50 values as shown in below image. We will see how we can highlight Top 10% values fr...

This blog post is continuation to our series of posts on “Conditional Formatting”. In this blog post, we will learn about highlighting values which are “Above Average” and “Below Average” of the selected values. To learn this post in an easy way, I have randomly listed few numbers as shown in below image. We will learn how to highlight the numbers which are above the average of all the selected numbers and which are below the average of all the selected numbers. For your easy understanding I have already calculated average value of all these numbers numbers (Which is 65). This will help us to easily cross check our output. Highlighting Above Average Values: Select all the values from which we wish to highlight the “Above Average Values”. Go to Conditional Formatting (which is available in Home Tab under Styles group) and select “Above Average” option as shown in below image. “Above Average” dialog box will appear on screen. Select the Formatting style from available list and hit OK but...