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....
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....
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....
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....
The one query that I get a lot is – ‘how to compare two columns in Excel?’. This can be done in many different ways, and the method to use will depend on the data structure and what the user wants from it. For example, you may want to compare two columns and find or highlight all the matching data points (that are in both the columns), or only the differences (where a data point is in one column and not in the other), etc....
Comparing two Excel files (or comparing two sheets in the same file) can be tricky as an Excel workbook only shows one sheet at a time. This becomes more difficult and error-prone when you have a lot of data that needs to be compared. Thankfully, there are some cool features in Excel that allow you to open and easily compare two Excel files. In this Excel tutorial, I will show you multiple ways to compare two different Excel files (or sheets) and check for differences....
Macros allow you to automate many time-consuming tasks and also create some functions and functionalities that are not already built-in in Excel. As a part of the security measure, if you receive a file that has a macro, Excel will disable it by default and you will have to manually enable the macros in the file so that they can be used. In this tutorial, I will show you how to enable macros in Excel and all the important things that you need to know about it....
But you can’t look-up images using these formulas. For example. if I have a list of team names and their logos, and I want to look up the logo based on the name, I can’t do that using the inbuilt Excel function. But that doesn’t mean it can’t be done. In this tutorial, I will show you how to do a picture lookup in Excel. It’s simple yet it’ll make you look like an Excel Magician (all you need is this tutorial and sleight of hands-on your keyboard)....
In Excel, a ‘Workbook’ is an object that is a part of the ‘Workbooks’ collection. Within a workbook, you have different objects such as worksheets, chart sheets, cells and ranges, chart objects, shapes, etc. With VBA, you can do a lot of stuff with a workbook object – such as open a specific workbook, save and close workbooks, create new workbooks, change the workbook properties, etc. So let’s get started....
For the purpose of this tutorial, let’s consider the following: Quarter 1 – Jan, Feb, and Mar Quarter 2 – Apr, May, and Jun Quarter 3 – July, Aug, and Sep Quarter 4 – Oct, Nov, and Dec In case you’re working with financial data where the quarter starts from April, then you can adjust the formulas accordingly (also covered later in this tutorial). Now let’s see how to use simple formulas to find out the quarter from the date....
The Immediate window allows you to quickly do things such as: Get information about Excel files (such as sheet count in a workbook or name of the workbook) Get Debug.Print information quickly Run a line of code in seconds Run a macro (or call a function) Get information about variables (or set the value or variables) While you don’t need to use immediate window to work with VBA in Excel, knowing how to use it well can make you a lot more efficient....
