Highlight the Cells that Contain a Specific Word (and it Matches Case)

In this blog post we look at how to highlight the cells that contain a specific word. We will also ensure that the word in the cell matches the case of the word being looked for.

For this example we have a list of the Ballon d’or winners of all time. Column B contains 3 letters in uppercase (after the name) which identify the country that the player represented at the time of winning the award.

In cell E1 I have entered the 3 digits for a country. I would like to automatically change the colour of all the cells that contain the country written in E1.

Highlight cells containing specific text

There is a good chance that the 3 digits identifying a country could also occur in a players name. For example, the letters for France – FRA do occur in the name Franz Beckenbauer.

To prevent this happening we will match the case of the word we are searching for, as it is always written in upper case. Continue reading

4 Excel VLOOKUP Examples – Why You Should Use VLOOKUP Function

When learning the VLOOKUP function, one of the things people can struggle with is seeing why this function is so useful. In this blog post we look at 4 VLOOKUP examples.

The purpose of VLOOKUP is to look for a value and return some information about that value. Although quite specific in its role, this function can be used in some very clever and interesting ways. Continue reading

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

5 Groovy SUMPRODUCT Examples – Advanced Excel Formula Tutorials

In this blog post we look at 5 groovy examples of the SUMPRODUCT function. This is one of the great functions of Excel. A function that can turn you from being an Intermediate/Advanced Excel user to an Excel guru instantly.

The SUMPRODUCT function is powerful, versatile and expansive. It is the go to function when looking for an alternative to array formulas.

If you are a newbie – check out our SUMPRODUCT function guide to get an understanding of how it works.

Ok, are you ready to rock on with these 5 awesome SUMPRODUCT examples?

Let’s do this.

If you prefer to watch videos, check out this video covering the tutorials from this blog post.

Continue reading

5 Excel INDIRECT Function Examples

In this blog post we look at 5 examples of the INDIRECT function of Excel. This is a very misunderstood function, which can be incredibly useful and powerful.

Prefer to watch the video? The video tutorial below will demonstrate all 5 INDIRECT function examples.

Continue reading

Create a Searchable Drop Down List Just Like Google Search – Excel Trick

This blog post will take you through the steps to create a searchable drop down list in Excel – just like Google search. This is a great Excel trick for working with large drop down lists.

In this tutorial we will use a list of 87 names that as we type into the drop down list, it searches the names, and the list shortens to show only those names containing that string of characters.

searchable drop down list in Excel

There are a few formulas to write to get this done. Everything is shown and provided in this tutorial. If you prefer a video. Check out the video tutorial below.

Watch the Video

Continue reading

Group Worksheets in Excel – Time Saving Excel Tip

This blog post looks at grouping the sheets of a workbook, and a couple of reasons why you would want to do that. This awesome little technique can save tons of time and seriously improve your productivity.

Why would you want to group worksheets?

By grouping worksheets you can make the same change to multiple worksheets at the same time. This can be a formatting change, a structural change such as inserting a row, or even a formula. 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