Looking for more? Try chatting with the Exceljet Chatbot.

Course

Excel Tables

Excel Tables offer an easy way to create dynamic ranges that adjust when data changes. This makes tables perfect for pivot tables, charts, and dashboards that need to show the latest data. This course covers the key benefits of tables, including a detailed review of structured references, the...Read more

Function

EXACT Function

The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.Read more

Function

SEARCH Function

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of find_text inside within_text as a number. SEARCH supports wildcards, and is not case-sensitive....Read more

Function

IF Function

The Excel IF function performs a logical test and returns one result when the logical test returns TRUE and another when the logical test returns FALSE. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...Read more

Formula

Dynamic date list

Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.

In the example shown, the date in the named range "start" is provided by the TODAY...Read more

Function

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count...Read more

Formula

All cells in range are blank

When working with Excel, there are times when you need to determine if a range of cells is empty. This can be useful in various scenarios, such as data validation, error checking, or report preparation. In this article, we'll explore a couple of formulas that can help you check if all cells in a...Read more

Article

Excel Data Validation Guide

Data validation can help control what a user can enter into a cell. You can use data validation to make sure a value is a number, a date, or to present a dropdown menu with predefined choices to a user. This guide provides an overview of the data validation feature, with many examples.Read more

Function

WEEKDAY Function

The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.Read more

Function

ISNUMBER Function

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

Article

Named Ranges in Excel

Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets, and better formulas.Read more

Formula

Count cells between dates

In this example, the goal is to count the number of cells in column D that contain dates that are between two variable dates in G4 and G5. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. For convenience, the worksheet contains two...Read more

Video

How to filter a list

In this video we show you how to filter a list to display just the items that meet criteria that you specify.Read more
Formula

Cell contains one of many with exclusions

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".

In the right SUMPRODUCT, SEARCH looks for all strings in the named range "exclude".

In both...Read more

Puzzle

Formula challenge - multiple OR criteria

One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. For example, perhaps you need to analyze data and count orders in Seattle or Denver, for items that are Red, Blue, or Green? This can be surprisingly tricky, so naturally it makes a good challenge! Read more
Formula

Repeat fixed value every 3 months

The first thing this formula does is check the date in column B against the start date:

=IF(B4>=start

If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the...Read more

Formula

Fixed value every N columns

The core of this formula is the MOD function. MOD takes a number and divisor, and returns the remainder after division, which makes it useful for formulas that need to do something every nth time.

In this case, the number is created with the COLUMN function, which return the column...Read more

Pages