Using Wildcard Characters in Excel Formulas

Wildcard characters can be used in Excel to perform partial matches on text. This can be extremely useful as you can use them in filters, the find and replace tool and especially in formulas.

This blog post explores some examples of using wildcard characters in formulas to find, sum or count cells containing partial matches to what we are searching for.

If you prefer a video tutorial then check it out below, otherwise please continue for the written tutorial.

Continue reading

4 Amazing Tips for the CONCATENATE Function

The most commonly used text function of Excel is CONCATENATE. This brilliant function join text together into one cell. This is typically a combination of written text, and text that is contained with cells on the spreadsheet.

The example below is a typical example of CONCATENATE. In this example, it is being used to join the first name and last name and insert a space in between. The formula below is written in cell C2.

Using the CONCATENATE function in Excel

This blog post uncovers 4 amazing tips to take your CONCATENATE functions to the next level. Continue reading

The WEEKDAY Function in Excel – Two Examples

Excel has many wonderful date and time functions. These functions are some of the most popular in Excel.

One that drifts under the radar a little is the WEEKDAY function. This function will return a number that identifies the day of the week of a date.

This can be very useful. This blog post will look at two examples of what the WEEKDAY function can do for you. Continue reading

Excel Formula for Elapsed Time in Days, Hours and Minutes

Having to calculate date and time difference in Excel is a common task. Unfortunately, depending on your requirements it is also not always a simple one.

Take this example where column A contains a start date and time, and column B an end date and time. We wish to calculate the elapsed time in days, hours and minutes e.g. 11 days 4 hours 9 minutes.

Difference between two date and times

There are multiple ways of calculating date and time difference in Excel. In this scenario we will need to get a little clever.

As you may well know, date and time values are stored as numbers in Excel. For example, the 05/01/2017 10:10 is stored as 42740.42.

Therefore, if I write the formula as =B2-A2, then the result is returned as 2.993056.

To return a result that makes sense to us, we will tackle the date and time parts of the cell separately. Continue reading

Count How Many Times a Specific Character Appears – Excel Formula

In this blog post we will uncover an Excel formula to count how many times a specific character appears in a cell.

The formula used in this tutorial will work for any text character, and can also be used to count the occurrences of specific words in a cell.

In this example, we used the formula to count the occurrences of the asterisk in the cells of column A.
Count occurrences of a specific character
Continue reading

Create an Interactive Checklist in Excel

In this blog post we look at creating an interactive checklist in Excel. The checklist will automatically mark the items in a list when they are checked.

To do this we will first need to insert checkboxes onto the spreadsheet, we then need to be able to highlight an item when it is completed.

In this tutorial I am using the idea of a Christmas shopping list of names (shown below). Your checklist could however be for any list of tasks, inventory or products.
Interactive checklist in Excel Continue reading

Display a Symbol Conditionally using the IF Function

This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.

This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.

Display thumbs up symbol using IF function Continue reading

Excel Formula to Display the Sheet Name in a Cell

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. Continue reading

5 Alternative Reasons to Use COUNTIF Function

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;

  1. Prevent duplicates in a range.
  2. Uniquely rank items in a list.
  3. Count the unique entries in a list.
  4. Compare two lists.
  5. Identify names that occur 3 times or more.

Continue reading

Essential Conditional Formatting Tricks

Conditional Formatting is an amazing feature of Excel. It is sure to create a spark of interest and questions during training. People see the potential in their spreadsheets and how easy their team could visualise their data and create engaging reports.

This tutorial looks at the two most requested Conditional Formatting tricks asked by Excel users.

Continue reading