Birthday Count
Excel Tips and Tricks

Highlight and get birthdays count from Date of Birth

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
Stylish Traffic Lights
Charts and Visualization

Stylish Traffic Lights in Excel

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
Excel Tips and Tricks

Arrows Symbols (➷➹) with Custom Formatting in Excel

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
Highlight Unmatched Cells
Excel Tips and Tricks

Highlight Unmatched Cells using Conditional Formatting in Excel

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
Highlight Consecutive Duplicates
Excel Tips and Tricks

Highlight the Consecutive Duplicates in Excel using Conditional Formatting

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
Emp Rating Table
Charts and Visualization

Employee Rating Table in Excel

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
Smart Check Box
Excel Tips and Tricks

Excel Tip: Checkbox in Excel Table

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  
Quality Check List

Quality Check List in Excel

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
Conditional Formatting in Lollipop chart
Charts and Visualization

Conditional Formatting in a Lollipop Chart in Excel

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