Search results

This is a good example of how the SUMPRODUCT function can help in situations where the COUNTIF or COUNTIFS functions do not work. In this case, the goal is to check all dates in a given range and show a check...Read more

The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.

The...Read more

The goal is to create a formula that returns "Done" in column E when a cell in column D is not blank (i.e., contains a value). In the worksheet shown, column D records the date a task is completed. If column D contains a date (i.e. is not empty), we can assume the task is complete...Read more

The goal is to display a checkmark (also called a "tick mark" in British English) when a task is marked complete. The easiest way to do this is with the IF function and the mark you would like to display. The article below explains several options.

IF with a plain checkmark

The...Read more

The REPT function simply repeats values. For example, this formula outputs 10 asterisks:

=REPT("*",10) // outputs **********

You can use REPT to repeat any character(s) you like. In this example, we use the CHAR function to output a...Read more

In the example worksheet, column D contains a date when a task is completed. If the task is not yet complete, the cell in column D will be empty (blank). In column E, the goal is to display the word "Open" when there is no date in column D. If there is a date in column D, the...Read more

In this example, the goal is to remove the asterisk (*) at the end of each text city/country name in column B. As usual, there are many ways to solve a problem like this in Excel. In the article below, we'll look at two good options. The first is a traditional formula based on the LEFT...Read more

In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was...Read more

Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will automatically return the first day of the current month.

In this example, the goal is to generate a dynamic calendar for any given month, based on a start date...Read more

In this example, the goal is to calculate the difference between two times in Excel in hours and minutes. This basic problem comes up frequently when tracking time and may be described in various ways:

• Calculate elapsed working time in hours.
...Read more

The goal is to remove the forward-slash ("/") from the URLs in column B when it is present as the last character. When a URL does not end with a forward slash ("/") the original URL should be returned without modification. Despite the fact that Excel offers many functions designed to...Read more

This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function to return a check mark (✓) when a logical test returns TRUE:

=IF(...Read more

Working from the inside out, we first "normalize" row numbers to begin with 1 using the ROW function and an offset:

ROW()-offset

In this case, the first row of data is in row 5, so we use an offset of 4:

...Read more