Since the earliest days of Excel, it has been very good at calculating and analyzing numbers. But manipulating arrays has traditionally been a challenge. With the new dynamic array functions, WRAPCOLS and WRAPROWS, you can transform a column or row into a 2D array in no time. Continue reading
When it comes to calculating an arithmetic mean of a group of numbers in Excel, you'd generally use the AVERAGE function. To average cells that meet a certain condition, AVERAGEIF comes in handy. To find an average with multiple criteria, AVERAGEIFS is the way to go. Continue reading
Microsoft Excel has a few different functions to calculate an arithmetic mean of numbers. When you are looking to average cells that meet a certain condition, AVERAGEIF is the function to use. Continue reading
In Excel, there exist a handful of different formulas for calculating average. This tutorial focuses on the most frequently used one - the AVERAGE function. Continue reading
Microsoft Excel 365 has introduced several new functions to perform various manipulations with arrays. With TOROW, you can perform range-to-row transformations in no time. Continue reading
Excel conditional formatting is all about visualizing data with colors. You may use contrasting colors to represent data categories or gradients to "map" data with some intrinsic order. When a certain pallet is used to visually represent data, it becomes a color scale. Continue reading
To compare different categories of data in your worksheet, you can make a chart. To visually compare numbers in your cells, colored bars inside cells are a lot more useful. Excel can show bars along with cell values or display only the bars and hide the numbers. Continue reading
The ability to transpose data from columns to rows and in reverse has been in Excel for quite a while. But converting a range of cells into a single column was a tricky task to crack. Now, that's finally changing. Continue reading
Microsoft Excel users have inserted pictures into worksheets for years, but that required quite a lot of effort and patience. Now, that's finally over with. With the newly introduced IMAGE function, you can insert a picture in a cell with a simple formula, move, copy, resize, sort and filter cells with images just like normal cells. Continue reading
As simple as it may sound, highlighting blank cells with conditional formatting is quite a tricky thing. Basically, it's because a human understanding of empty cells does not always correspond to that of Excel. Continue reading
Combining two or more ranges has traditionally been a daunting task in Excel. Not any longer! The two functions recently introduced in Excel 365, VSTACK and HSTACK, have made the job incredibly easy. Continue reading
An Excel formula to see if two cells match could be as simple as A1=B1. However, there may be different circumstances when this obvious solution won't work or produce results different from what you expected. Continue reading
To check if a given value is between two numbers, you can use the AND function with two logical tests. To return your own values when both expressions evaluate to TRUE, nest AND inside the IF function. Detailed examples follow below. Continue reading
There may be various situations when you want to split cells in Excel. Now, we finally have a special function for this, TEXTSPLIT, that can separate a string into multiple cells across columns or/and rows based on the parameters that you specify. Continue reading
When working with long strings, you may often want to extract a specific part of them for closer examination. In Microsoft Excel and Google Sheets, there are several ways to get text between two characters or substrings. In this article, we'll discuss the fastest and most effective ones. Continue reading
When working with complex datasets in Excel, you may often find yourself in a situation when you need to extract data to the right of a certain character or word. This tutorial will show you the fastest way by using the span-new TEXTAFTER function. Continue reading
In earlier Excel versions, extracting text before a space, comma or any other character was quite tricky. In Excel 365 and Excel for the web, we now have a brand-new TEXTBEFORE function that does the magic! Continue reading
For large datasets, you may often need to calculate a total amount for a given range between two numbers. This can be easily done with the SUMIFS function both in Excel and Google Sheets. Continue reading
The SUMIF and SUMIFS functions available in Microsoft Excel and Google Sheets are case-insensitive by nature. To conditionally sum cells treating lowercase and uppercase letters as different characters, you'll have to come up with something else. Continue reading
If you feel overwhelmed or confused about the extensive content of your worksheet, you can organize the columns in groups to easily hide and show different parts of your sheet, so that only the relevant information is visible. Continue reading
In some situations, you may need to sum specific numbers in a range, say top 3, 5, 10 or n. That might be a challenge because Excel has no inbuilt function for this. But as always, there is nothing that would prevent you from constructing your own formulas :) Continue reading
Doing a conditional sum in Excel is a piece of cake as long as all the values to be totaled are in one column. Summing multiple columns is a problem because both the SUMIF and SUMIFS functions require the sum range and criteria ranges to be equally sized. Continue reading
Microsoft Excel has a number of inbuilt features to create graphs. Regrettably, a heat map is not on board. Luckily, there is a quick and simple way to create a heat map in Excel with conditional formatting. Continue reading
When building complex formulas in Excel, you may sometimes need to get a column letter of a specific cell or from a given number. This can be done in two ways: by using inbuilt functions or your own one. Continue reading
VLOOKUP is one of the most confusing Excel functions plagued with many issues. Using VLOOKUP with ISERROR can help you catch all possible errors and handle them in a way most appropriate to your situation. Continue reading
When you write a formula that Excel does not understand or cannot calculate, it draws your attention to the problem by showing an error message. The ISERROR function can help you catch errors and provide an alternative when an error is found. Continue reading
When working with large datasets, it can be helpful to see a few areas of the same worksheet at a time to compare different subsets of data. This can be done by using Excel's Split Screen feature. Continue reading
Having spreadsheets in two different windows makes many Excel tasks easier. One of the possible solutions is viewing workbooks side by side, but this eats a lot of space and isn't always the best option. Continue reading
Getting a lot of #N/A errors in your worksheets and are curious to know if there is a way to replace them with a user-friendly message? IFNA formula is the solution you need. Continue reading
The tutorial talks about how to return a column number in Excel using formulas and how to number columns automatically. Continue reading