Pivot Table Tips
Many Excel experts believe that Pivot Tables are the single most powerful tool in Excel. This article provides more than 20 tips you should know to work productively with Excel Pivot Tables....Read more
Many Excel experts believe that Pivot Tables are the single most powerful tool in Excel. This article provides more than 20 tips you should know to work productively with Excel Pivot Tables....Read more
In this example the goal is to count numbers longer than 15 digits with a formula. The COUNTIF function may seem like this logical choice. However, if you try to count very long numbers (16+ digits) in a range with the COUNTIF function, you may see...Read more
Excel contains over 500 functions, with more functions added every year. That is a huge number, so where should you start? This guide provides a walkthrough of over 100 important functions in Excel with many examples and links. Click function names for details and more examples....Read more
Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.Read more
In this example, the goal is to count rows where the value in column one is "A" or "B" and the value in column two is "X", "Y", or "Z". In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to...Read more
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.Read more
You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from...Read more
In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character first...Read more
This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).
The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:
COUNTIF(range1,B5) // count in range1
COUNTIF(
...Read more
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors when data is...Read more
COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection...Read more
MOD is nerdy, but cool. If you've ever struggled to create a more complex formula in Excel, you'll like this one. It shows you how to build a more complex formula step-by-step, without going crazy....Read more
We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we try to add criteria looking for 2 items across 3 columns, it isn't going to...Read more
The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.Read more
We asked users how important Excel shortcuts are to them and also about their favorite shortcuts. This is what we learned...Read more
In this example, the goal is to use the COUNTIFS function to count data with "OR logic". The challenge is the COUNTIFS function applies AND logic by default.
The COUNTIFS function returns the count of cells...Read more
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.Read more
At the core, this formula uses INDEX to create an expanding reference like this:
INDEX([Color],1):[@Color] // expanding range
On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column...Read more
The Unix time stamp tracks time as a running count of seconds. The count begins at the "Unix Epoch" on January 1st, 1970, so a Unix time stamp is simply the total seconds between any given date and the Unix Epoch. Since a day contains 86400...Read more
In this example, the goal is to count numbers that contain leading zeros. In cell E5, we have the code "009875" and we want to count how many times this code appears in the range B5:B16. The challenge is that Excel can be finicky with leading zeros. Technically, the values in B5:B16 are...Read more
The Unix time stamp tracks time as a running count of seconds. The count begins at the "Unix Epoch" on January 1st, 1970, so a Unix timestamp is simply the total seconds between any given date and the Unix Epoch. Since a day contains 86400...Read more
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:
=AVERAGE(first:last)
where "first" is a...Read more
In this example, the goal is to test if a given range contains duplicate values and return TRUE if duplicates exist and FALSE if not. This is essentially a counting problem and the solution is based on the COUNTIF function, which counts values in a range...Read more
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.Read more
The COUNTIF function counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the...Read more
The Excel BYROW function applies a LAMBDA function to each row of a given array and returns one result per row in a single array. In the example shown, data is the named range C5:H9.
Note:...Read more
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.Read more