When to use Excel COUNTIFS Function COUNTIFS function can be used when you want to count the number of cells that meet a single or multiple criteria. What it Returns It returns a number that represents the number of cells that met a specified criteria in the specified range(s). Syntax =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) Input Arguments criteria_range1 – The range of cells for which you want to evaluate against criteria1....

When it comes to looking up data in Excel, there are two amazing functions that I often use – VLOOKUP and INDEX (mostly in conjunction with the MATCH function). However, these formulas are designed to find only the first instance of the lookup value. But what if you want to look-up the second, third, fourth or the Nth value. Well, it’s doable with a little bit of extra work. In this tutorial, I will show you various ways (with examples) on how to look up the second or the Nth value in Excel....

24 Excel Tricks for Day-to-day Work In this blog post, let me unveil 24 Excel tricks that will save you a lot of time and energy. If you work with excel, sooner or later you get into trouble with something that seems impossible. Here are some quick fixes that will make it possible. Excel Trick #1 – Copy-Paste Formulas without Changing References It happens all the time. You try to copy a cell with a formula to another cell and it gets all mixed up....

One way of getting the VBA code is to record the macro and take the code it generates. However, that code by macro recorder is often full of code that is not really needed. Also macro recorder has some limitations. So it pays to have a collection of useful VBA macro codes that you can have in your back pocket and use it when needed. While writing an Excel VBA macro code may take some time initially, once it’s done, you can keep it available as a reference and use it whenever you need it next....

In 2010, my roommate got me addicted to watching football. We would keenly follow all the matches in English Premier League and La Liga. I even played fantasy football with a couple of football junkies. I still follow these matches and I still suck at playing it. 🙂 But I am better at making dashboards, and in this tutorial, I will show you how to create an Excel Dashboard that analyses English Premier League games of the 2014-2015 season....

Now, if you’re thinking that there must be an inbuilt feature to do this in Excel, I’m afraid you’d be disappointed. While there are multiple ways you can flip the data in Excel, there is no inbuilt feature. But you can easily do this using simple a sorting trick, formulas, or VBA. In this tutorial, I will show you how to flip the data in rows, columns, and tables in Excel....

One such useful (and less-known) feature in the Custom Lists in Excel. Now, before I get to how to create and use custom lists, let me first explain what’s so great about it. Suppose you have to enter numbers the month names from Jan to Dec in a column. How would you do it? And no, doing it manually is not an option. One of the fastest ways would be to have January in a cell, February in an adjacent cell and then use the fill handle to drag and let Excel automatically fill in the rest....

The regular way of doing this is: Enter 1 in cell A1. Enter 2 in cell A2. Select both the cells and drag it down using the fill handle. Sometimes it may get a bit irritating to drag the fill handle to the last cell which can be many folds below the current cell. In this tutorial, I’ll show you a faster way to fill numbers in cells without any dragging....

I often need to use this when I’m creating tutorials where I need a random set of numbers or items. It could also be useful if you’re creating a random team in your office or classroom. In this tutorial, I will show you three easy ways to quickly shuffle a list of names or items in Excel. So let’s get started! Randomize List Using SORTBY Formula Suppose you have a list of names as shown below, and you want to randomize this list....

For example, I have the dataset as shown below, and I want to get the sum of all orange and yellow color cells. Unfortunately, there is no in-built function to do this. But never say never! In this tutorial, I will show you three simple techniques you can use to sum by color in Excel. Let’s dive in! SUM Cells by Color Using Filter and SUBTOTAL Let’s start with the easiest one....

There are many different ways to select a cell in Excel – such as using the mouse or the keyboard (or a combination of both). In this article, I would show you how to select multiple cells in Excel. These cells could all be together (contiguous) or separated (non-contiguous) While this is quite simple, I’m sure you’ll pick up a couple of new tricks to help you speed up your work and be more efficient....

Sort Data in Alphabetical Order In this post, I will show you various ways to sort data in alphabetical order using formulas. This means you can add data, and it will automatically sort it for you. When the Data is all Text with No Duplicates Suppose you have a data as shown below: In this example, all the data is in text format (no numbers, blanks or duplicates). To sort this, I will use a helper column....

For example, in the below example, the value in column A and B is the same, but these are displayed differently because of the format. And one of the things many people need in Excel is to convert the time to decimal (such as hours or minutes or seconds). For example, instead of having the date and time as 01-01-2020 6:30 AM, you may want the get: Number of hours as 6 Number of minutes as 30...

Office 365 brings with some awesome functions – such as XLOOKUP, SORT, and FILTER. When it comes to filtering data in Excel, in the pre-Office 365 world, we were mostly dependent on Excel in-built filter or at max the Advanced filter or complex SUMPRODUCT formulas. In case you had to filter a part of a dataset, it was usually a complex workaround (something I have covered here). But with the new FILTER function, it’s now really easy to quickly filter part of the dataset based on a condition....

These could be files the that you open every day in the morning (such as tracking sheet or timesheet), or some project related files that you need to open as soon as you start Excel. While you always have the option to open these files manually, it’s just one of those small time savings things you can do by automating the process. And this automation doesn’t need any VBA code or complex steps....

People who work with a lot of worksheet tabs find it useful to give a specific color to a sheet tab. This could be useful in showing the grouping of similar tabs (for example, all tabs for a specific year or about a specific product can be shown in the same color). It’s also useful if you want to highlight one specific tab by giving it a different color (such as a summary tab or dashboard tab)....

This means that while you may see a date such as “10 January 2020” or “01/10/2020” in a cell, in the back-end, Excel is storing that as a number. This is useful as it also allows users to easily add/subtract date and time in Excel. In Microsoft Excel for Windows, “01 Jan 1900” is stored as 1, “02 Jan 1900” is stored as 2, and so on. Below, both columns have the same numbers, but Column A shows numbers and Column B shows the date that’s represented by that number....

And sometimes, you can come across dates that are formatted as text (or look like dates but are not in the acceptable date formats, so Excel treat them as text strings). Since dates are stored as numbers in the backend in Excel, it allows you to format dates in different ways as well as use them in calculations. But when a date is stored as a text string in Excel, you won’t be able to use it in calculations or format it like a date....

Gantt Chart is a simple yet powerful project management tool that can be used for creating a schedule or tracking the progress. Let’s say Bruce Wayne (Batman) wants a new Batsuit and instructs Alfred (the butler and his confidante) to get it made. Alfred, who is an avid project management student, comes up with a simple Gantt Chart in Excel to get the plan ready. And this is what he shows Mr....

When there is no dedicated function to get the first day of a given month, you can easily use a workaround to find it. In this Excel tutorial, I’ll show you some really simple formula that you can use to get the first day of a month in Excel based on a given date. So let’s get started! Get the First Day of the Month Using EOMONTH EOMONTH Function gives you the last day of the month for a given date (hence called the end-of-month function)....