In this article, you will learn how to get count of persons from a Date of Birth data whose birthdays are in the specific month. You will also learn how to highlight the data using Conditional Formatting whose birthdays are in This Month, Last Month and Next Month. We have below given Date of Birth data for Employees- [caption id="attachment_14951" align="aligncenter" width="435"] Date of Birth data[/caption] We have created a
You can create very attracting info-graphics with the help of Auto shapes. Here we are giving an example of this. We have created stylish and beautiful Traffic lights info-graphics using Auto Shapes and Conditional Formatting in Excel. You can use these traffic lights in your Business Dashboard or Project Management Dashboard to display project health. Below is the data set which we have used to create these Stylish Traffic Lights-
Custom formatting is very useful in Excel. You can display some Icon with colors with the numbers. Here we have taken an example wherein we have displayed designer up/down arrow (➷➹) for Variance %. We have a table for Market wise sales for Current Year and Previous Year. We have displayed Variance % here with designer arrows. [caption id="attachment_12838" align="aligncenter" width="617"] Market Wise Data[/caption] In the Variance % column we
In this article you will learn how to highlight the Unmatched Cells values from another range using Conditional formatting. For Example, we have Sales and Product Master worksheets. In the Sales worksheet we need to highlight the Products which are not available in Product Master. [caption id="attachment_12307" align="aligncenter" width="1496"] Sales Data and Product Master Worksheets[/caption] Below are the steps to put the Conditional Formatting to highlight Product Name in Sales
In this article you will learn how to highlight the Consecutive Duplicates in Excel using Conditional Formatting. You will also learn how to highlight consecutive weekly off in an Attendance Sheet. Let’s say we have month wise sales wherein we have month name duplicate. We want to highlight the consecutive duplicate month as given in below image [caption id="attachment_12290" align="aligncenter" width="282"] Consecutive duplicate month[/caption] Below are the steps to put
Rather than showing the simple numbers , you can display some symbols in your Excel table. Here we have taken an example of Employee Rating Table for 1 to 5. We have used Conditional Formatting with Icon set to create this Employee rating table. You will select the rating using drop-down and it will be changed in symbol automatically. [caption id="attachment_10772" align="aligncenter" width="783"] Employee Rating Table[/caption] Click here to
Check box control in Form Controls is very useful in Microsoft Excel. Here we have created a table in each row one check box is available. We have used conditional formatting to highlight the checked row. Calculation of Total is changed according to checked rows. Below is the table with Check boxes [caption id="attachment_8641" align="aligncenter" width="514"] Table with Check boxes[/caption] Click here to download this Excel file
The template of Quality Check list has been designed to use for quality team.It can be use for other purposes also. Here we have taken the drop-down as Pass/Fail. You can use it as Yes/No, Pending/Completed ect. This template can we used for multiple purposes. It automatically change the text with Icons on the base of conditional formatting. There is no VBA code has been used. Pass/Fail drop down in
Using the Conditional Formatting in any chart is very useful. It helps to audience to identify which category is meeting the target and which is not meeting. Here, we have used the Conditional Formatting in a beautiful Lollipop Chart. We also have created 4 variants of this chart with some different shapes which make it more eye catching. Days wise sales are available on range “A1:B8” and target is available
Using Conditional Formatting in a Line chart is very nice idea. This chart is very useful to show the trends of the data. Here we have showcased the month on month Service Level and Target has been connected from and Excel cell. It can be easily analyzed that which months are not meeting the target. This is a dynamic chart, if we will change the target value then Red and
