When to use Excel AVERAGEIFS Function Excel AVERAGEIFS function can be used when you want to get the average (arithmetic mean) of all the cells in a range that meets multiple criteria. What it Returns It returns a number that represents the average (arithmetic mean) of the arguments. Syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Input Arguments average_range – the range of cells you want to average. criteria_range1, criteria_range2,.. – the ranges against which the criteria are evaluated....

When to use Excel MID Function MID function can be used to extract a specified number of characters from a string. What it Returns It returns the sub-string from a string. Syntax =MID(text, start_num, num_chars) Input Arguments text – the text from which you want to extract the string. start_num – the starting position within the text argument, from where the extraction should begin. num_chars – the total number of characters that you want to extract....

And many of these are simply awesome. The amount of work you can get done with a few formulas still surprises me (even after having used Excel for 10+ years). And among all these amazing functions, the INDEX MATCH functions combo stands out. I am a huge fan of INDEX MATCH combo and I have made it pretty clear many times. I even wrote an article about Index Match Vs VLOOKUP which sparked a little bit of debate (you can check the comments section for some firework)....

What is a good design for the Source data for Pivot Table? Let’s have a look at an example of good source data for a Pivot Table. Here’s what makes it a good source data design: The first row contains headers that describe the data in the columns. Each column represents a unique data category. For example, Column C has product data only and column D and month data only....

You can use this function to test multiple conditions at once and then return the result based on it. This is helpful as you don’t have to create those long nested IF formulas that used to get confusing. Note that the IFS function is available only the Excel 2016 Windows version (and not the Mac version). When to Use Excel IFS Function IFS function can test multiple conditions and returns the value as soon as it finds the first condition that is fulfilled....

For example, A1 would refer to the first row (specified as 1) and the first column (specified as A). Similarly, B3 would be the third row and second column. The power of Excel lies in the fact that you can use these cell references in other cells when creating formulas. Now there are three kinds of cell references that you can use in Excel: Relative Cell References Absolute Cell References Mixed Cell References...

With a lot of data…comes a lot of duplicate data. Duplicates in Excel can cause a lot of troubles. Whether you import data from a database, get it from a colleague, or collate it yourself, duplicates data can always creep in. And if the data you are working with is huge, then it becomes really difficult to find and remove these duplicates in Excel. In this tutorial, I’ll show you how to find and remove duplicates in Excel....

When you create a regular chart in Excel, it usually has only one X-axis and one Y-axis. But with combination charts, you can have two Y-axis, which allows you to have two different type of data points in the same chart. For example, you may be interested in plotting the annual revenue numbers of a company, and at the same time, also be able to show how the profit margin has changed....

And one common task many Excel users have to do is to cut the cell values from one cell and paste it to some other cell in the same worksheet or in another worksheet/workbook. While this is a basic operation that most Excel users are often aware of, in case you’re a beginner, I’ll show you everything you need to know about how to cut a cell value in Excel....

It helps you analyze data by getting different views by dates, weeks, months, quarters, and years. For example, if you have credit card data, you may want to group it in different ways (such as grouping by months or quarters or years). Similarly, if you have a call center data, then you may want to group it by minutes or hours. Watch Video – Grouping Dates in Pivot Tables (Grouping by Months/Years)...

That’s how it’s supposed to work. But what if you want to show formulas in the cells and not the calculated values. In this Excel tutorial, I will cover the following topics: How to Show Formulas in Excel instead of the values. How to Print the formulas in Excel. How to Show Formulas in Excel in Selected Cells Only. What to Do when Excel Shows Formulas Instead of the Calculated Values....

When to use Excel RANK Function RANK function can be used when you want to rank a number against a list of numbers. What it Returns It returns a number that represents the relative rank of the number against the list of numbers. Syntax =RANK(number, ref, [order]) Input Arguments number – the number that you want to rank. ref – the list of numbers against which you want to rank the number argument....

When working with financial data and creating accounting reports, one common number format that needs to be used is to show negative numbers in brackets (parentheses). While the default setting in Excel is to show negative numbers with a minus sign, it’s quite easy to change the format to show negative numbers in brackets/parenthesis. In this tutorial, I will show you different ways to show negative numbers in brackets. I’ll also cover all the other formatting options that are available to you (such as adding or removing the – or changing the color of the negative or positive numbers)...

If you have an Excel workbook with many worksheets, there is no easy way to split each of these sheets into separate Excel files and save separately. This could be needed when you sheets for different months or regions or products/clients and you want to quickly get a separate workbook for each sheet (as an Excel file or as PDFs). While there is a manual way to split sheets into separate workbooks and then save it, it’s inefficient and error-prone....

There is a similar function in that also does the same – the UCase function. The VBA UCase function takes a string as the input and converts all the lower case characters into upper case. Syntax of the VBA UCASE Function Below is the syntax of the VBA UCase function ‘String’ is the text in which you want to convert all the lowercase to uppercase. You can use a text string, a range reference that contains the text string, or a variable that has the text string....