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
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
Evaluate Formula
VBA

UDF for Evaluate a Formula in Excel

In this article you will learn how to create a User Defined Function to evaluate a formula in Excel. You will also learn how to get text of formula using a User Defined Function and FORMULATEXT (Excel 2013 and above). Below is the code to create the User Defined Function to evaluate a formula in Excel Function Evaluate_Formula(formula_text As String) As Variant      Application.Volatile     Evaluate_Formula = Application.Evaluate(VBA.Trim(formula_text))
Fill Blanks
Excel Tips and Tricks

Excel Tip : Fill the blanks cells in your data

Many times we have zig-zag data which has to be formatted in proper manner to create some report or dashboard. In this article you will learn how we can fill the blanks in our data. Below is the data in which blanks cells have to be filled: [caption id="attachment_7049" align="aligncenter" width="507"] Data Sets[/caption]  We can fill the blanks using 4 differed tricks. 1-Using Filter: We can fill the blanks using
Highlight the data point
Charts and Visualization

Highlight the Specific data point in a Line Chart Dynamically

In this chart we have highlight the Minimum, Maximum and Selected data points in a beautiful line chart. This chart can be used in Business Dashboard or Presentation. Below is the snapshot to highlight the Minimum data point [caption id="attachment_6991" align="aligncenter" width="943"] Highlight the Minimum data point[/caption]    Below is the snapshot to highlight the Maximum data point [caption id="attachment_6994" align="aligncenter" width="950"] Highlight maximum data point[/caption] Below is the snapshot
Highlight Top and Bottom Performer
Excel Tips and Tricks

Highlight Top and Bottom Performer using conditional formatting

In this article you will learn how to use Conditional Formatting to highlight the Top/bottom performer on the base of performance score. Top performer has been highlighted in green color and Bottom performer has been highlighted in red color. A drop-down of Top Performer and Bottom performer has been given on cell C1 using Data Validation list. In the below image, top performer has been highlighted. To highlight the top
Charts and Visualization

Project Plan (Gantt Chart) in Excel

Project Plan (Gantt chart) has been created in MS Excel. MS Excel cells has been used to create this. We have not used any type of chart to create this Project Plan. This is a beautiful creation and can be used in Project Management Dashboard or Business presentations.  We have used conditional formatting to create this project plan. There is a highlight option available. We can highlight any date by using this
Templates

Dynamic Calendar in Excel

This calendar template is fully dynamic. If you will change the month or year in the drop down calendar will be change automatically. To creating the month and year drop down Form Control combo box has been used. It has been created purely in Excel with the help of formulas and conditional formatting. No VBA has been used. [caption id="attachment_2434" align="aligncenter" width="641"] Dynamic Calendar in Excel[/caption] Click to download Dynamic
Age Calculator in Excel
Templates

Age Calculator in Excel

Age Calculator template tells your exact age till today. You need to enter your date of birth in the DOB box. It will given you your age like "You are 26 Years, 3 Months and 4 days old." [caption id="attachment_2384" align="aligncenter" width="982"] Age Calculator in Excel[/caption] To create the Age calculator Datedif formula has been used. on the sheet1 you will find the calculation that how to calculate the age in excel