Dave Bruns

The goal in this example is to reformat names that appear in mixed upper and lower case letters into "proper case", defined as each word in the name beginning with a capital letter. This can easily be done in Excel with the PROPER function.

PROPER function

The...Read more

All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this:

COUNTIF(rng,"*"&substrings&"*"))...Read more

The COUNTIF function counts cells that meet supplied criteria, and returns a count of occurrences found. If no cells meet criteria, COUNTIF returns zero.

The asterisk (*) is a wildcard for one or more characters. By ...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

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

Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that equal a specific value. However, for more flexibility, you can use your own formula, as explained in this article.

If you want to...Read more

The goal is to identify invoice numbers in range D5:D11 that are missing in range B5:B16 (named list). Two good ways to solve this problem in Excel are the COUNTIF function and the MATCH function....Read more

The aim is to mark records with an "x" if a score is greater than 80 and leave the cell blank if the score is less than 80. This can be achieved using the IF function in Excel.

IF function

The IF function runs a logical test and returns one...Read more

In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found.  In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:

...Read more

The goal is to return "Small" when the value in column D is "S" and "Large" when the value in column D is "L". In other words, if the value in column D is "S" return "Small" else return "Large".

If else in Excel

The concept of "If else" in Excel is handled with the...Read more