Excel Tricks with Tejraj

In this blog post we will learn about “DATEDIF” function in excel. In excel DATEDIF function is used to calculate the difference between two dates in the form of days, moths and years. Syntax of “DATEDIF Function”: The syntax of DATEDIF function in excel is as below: =DATEDIF(Start Date, End Date, Unit) Arguments of “DATEDIF Function”: There are total 03 arguments required to get desired result with this function. Start Date : This is the 1st date or the starting date from the period from which we want to calculate the difference between dates. End Date : This is the 2nd date or the ending date from the period from which we want to calculate the difference between dates. Unit : The output of DATEDIF function will depend on this argument. There are total 06 units available for this arguments as listed below. We have to enter this argument in between two double inverted commas ex: “Unit”. “Y”: With this unit, DATEDIF function will return number of complete years between Start Date and En...

In this blog post we will see to create “Dynamic Progress Chart in Excel" from scratch.  This chart will help you to present your data in a very dynamic way.  For this we will take a simple example. Let’s say we want to show how many males and females (in percentage) are present in our country. We have used dummy data as shown in below image to create “Dynamic Progress Chart in Excel”. Let us say we have 47% of Males and remaining 53% are Females. First we will write 47% in front of Male. To calculate the % of females we will use formula (1-Cell address of % of Male). First we will see the steps to create “Dynamic Progress Chart in Excel” for Male and then by using these steps you have to create “Dynamic Progress Chart in Excel” for Female by your own. So let us start… 1. First we have to create helper column just beside the cell where we have inserted % for male. Use formula (1- Cell address of % of Male). 2. Go to Insert Tab. Select “Insert Pie or Doughnut Chart” command an...

In our previous blog post we have learned to insert Sparklines in excel . Now, we will go little advanced in this blog post and learn to customize the Sparklines. If you have not visited our blog post on inserting Sparklines in excel then please click here before proceeding for this blog post, as we are going to use the same data which we have used previously. We have crated 03 types of Sparklines: Line, Column and Win/Loss as shown in below image. To customize Sparklines in excel, we have to first enable the “Design” tab on the ribbon. Design tab will get enabled if and only if we click on the Sparklines. If we click on any portion where Sparklines are not created then Design tab will get disabled. Once we click on the “Design Tab” then we can see the commands which are divided in various groups. In this blog post of “Customizing the Sparklines”, we will see all the commands “Show Group” which are highlighted with Pink color and the commands to change the “Sparkline Color” and “Mark...