Posts

There are multiple ways to calculate the number of months between two dates (all using different formulas). In this tutorial, I will give you some formulas that you can use to get the number of months between two dates. So let’s get started! Using DATEDIF Function (Get Number of Completed Months Between Two Dates) It’s unlikely that you will get the dates that have a perfect number of months. It’s more likely to be some number of months and some days that are covered by the two dates....

Download File Watch Video – Scenario Manager in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Goal Seek in Excel. Excel Solver. Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change. Suppose you have a dataset as shown below and you want to calculate the profit value:...

Yes, I mean everything and there’s a lot. This post will tell you about all the awesome features Excel Tables have and why you should start using them. What is an Excel Table? Excel Tables are containers for your data. Imagine a house without any closets or cupboards to store your things, it would be chaos! Excel tables are like closets and cupboards for your data, they help to contain and organize data in your spreadsheets....

When to use Excel MATCH Function Excel MATCH function can be used when you want to get the relative position of a lookup value in a list or an array. What it Returns It returns a number that represents the position of the lookup value. Syntax =MATCH(lookup_value, lookup_array, [match_type]) Input Arguments lookup_value – The value for which you are looking for a match in the lookup_array. lookup_array – The range of cells in which you are searching the lookup_value....

A MsgBox is nothing but a dialog box that you can use to inform your users by showing a custom message or get some basic inputs (such as Yes/No or OK/Cancel). While the MsgBox dialog box is displayed, your VBA code is halted. You need to click any of the buttons in the MsgBox to run the remaining VBA code. Anatomy of a VBA MsgBox in Excel A message box has the following parts:...

It gives you an idea of where a value lies in the dataset (i.e., its position/rank in the dataset). In practical life, I have seen the percentile value being used in competitive exams, where on the given score, you get the percentile value. This tells you where you stand in comparison to all the other people who appeared for that exam. In this tutorial, I will explain everything you need to know about the percentile function in Excel, and show you examples of how to calculate the 90th percentile or 50th percentile in Excel....

In a previous post I showed you How To View Actual Versus Target With A Thermometer Style Chart and in this post we’re going to look at how to display similar information in a gauge chart. Excel doesn’t have a gauge chart option, but we can use a pie chart to create something that looks like a gauge. In this example we’ll chart the current amount raised against a fundraising goal....

This is the third and final article of the three-part tutorial series on Creating a KPI Dashboard in Excel. Part 1: Creating a Dynamic Scatter Chart in Excel Part 2: Spotting the Data Point and Creating a Dynamic Interpretation for the Chart Part 3: Extract List of Companies from the Scatter Chart + Company comparison( using Bullet Charts) If you have followed Part 1 and Part 2 of this KPI Dashboard in Excel series, by now you would know how to create a dynamic scatter chart, spot a data point in the chart, and create a dynamic interpretation for the chart....

While entering or editing data in Excel it is possible to add line breaks in the cell using Alt + Enter. This can be a convenient way to display data in Excel, but is often not great for any type of data analysis. For example, it might be more convenient for the reader to display an address in a cell like this: Rather than like this: In this post I’ll show you how to separate the data in your cells based on the in-cell line breaks....

In the ribbon, we have different tabs (such as the ‘Home’ tab or the ‘Insert’ tab), and within each tab, we have the relevant commands. Most Excel users would find everything they need somewhere in the ribbon in Excel. But what if you do not want to show the ribbon (as it takes considerable space on your screen), or you do not see the ribbon and you want to get it back?...

One common example of this is when you have to sum the positive numbers in Excel (or only sum the negative numbers). And this can easily be done using the in-built formulas in Excel. In this tutorial, I will show you how to sum only positive or negative numbers in Excel using simple formulas. So let’s get started! SUM Positive Numbers Only Suppose you have a dataset as shown below and you want to sum all the positive numbers in column B....

While one obvious difference is the placement of the row versus column in Excel, there is more to it. In this article, I will cover the differences between rows and columns in Excel (starting with the most basic difference and then talking about some more nuanced aspects of it) Difference Between Row and Column in Excel Let’s look at some basic differences between rows and columns in Excel. The Placement of Rows and Columns in Excel Let me first start with the most obvious one, the placement of rows and columns in the worksheet in Excel....

When you come across time series data analysis such as sales or stock trading. You have to calculate the period of time and play around with the Excel Date and Time functions. There are different methods to add months to a date in excel. This blog post explains each one with examples to help you understand how to do it. In this blog post, you will use built-in functions EDATE, DATE, and learn how to use Power Query to add months to a date....

Something as shown below: He wanted that the serial number for India should be 1 wherever it occurs. Similarly, US is the 2nd country and should always have 2 as its serial number. This got me thinking. And here are the two ways that I could come up with to avoid duplication in serial numbers in Excel. The first way is to use our beloved VLOOKUP function. To do this, we first need to get a unique list of countries....

With a basic understanding and a little drag and drop, you can get a bucket-load of work done in a few seconds. While a lot can be done with a few clicks in Pivot Tables, there are some things that would need a few extra steps or a little bit of work around. And one such thing is to count distinct values in a Pivot Table. In this tutorial, I will show you how to count distinct values as well as Unique Values in an Excel Pivot table....

An interesting section in the weekly meetings was impromptu speaking. In this part of the meeting, a person was given a topic and he/she had to speak on it for 2 minutes. There was a designated person who used to time the speech and shows a green card at 1 minute, a yellow card at 1:30 minutes, and a red card after 2 minutes. Usually, a smartphone or a watch is used to time the speech, and the time taken is recorded manually on a paper....

When you create/record a macro in a workbook in Excel, it can only be used in that workbook. But what if you have a list of useful Excel macros that you want to use in all the workbooks? In such a case, it’s a good idea to save these in your Personal Macro Workbook. Doing this would allow you to access the macro code from any workbook on your system....

It helps you calculate the payment you need to make for a loan when you know the total loan amount, interest rate, and the number of constant payments. For example, suppose you buy a house for USD 200,000. Since you don’t have that kind of cash, you get a home loan at a 4% annual interest rate. Now, you have to pay the loan installments every month for the next 20 years....

A chart gets difficult to read if it has a lot of data plotted on it. While it is a good practice to plot only the relevant data, there are situations where you need to show a lot of data points on a single chart. If you are stuck in such a situation, it is a good idea to have a dynamic chart that highlights the selected series so that it is easier to read and compare....

For example, below I have a data set where I have the tasks in column A and their due dates in column B. It would be useful if there was a way to automatically highlight dates before today, so that I can visually see the tasks for which the due date has already passed. Thanks to the awesome Conditional Formatting feature in Excel, you can easily highlight dates before today (i....