Microsoft Excel Tips and Tricks - Computergaga

Home / Archives for Formulas

Create a Searchable Drop Down in Excel – Just Like Google Search

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.

Completed searchable drop down list in Excel

There are a few formulas to write to get this searchable drop down list in Excel created. Everything is shown and provided in this tutorial. If you prefer a video. Check out the video tutorial below.

Watch the Video – Searchable Drop Down List in Excel

[Read more…] about Create a Searchable Drop Down in Excel – Just Like Google Search

CONCATENATE Function in Excel

The most commonly used text function in Excel is CONCATENATE. This brilliant function joins text together into one cell.

This is typically a combination of written text, and text that is contained in cells on the spreadsheet.

The CONCATENATE function in Excel only requires the different text strings or cell references you want to combine.

=CONCATENATE(text1, [text2], [text3], ...)

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.

=CONCATENATE(A2,” “,B2)
Typical example CONCATENATE function in Excel
[Read more…] about CONCATENATE Function in Excel

WEEKDAY Function in Excel – Two Examples

The WEEKDAY function in Excel is one of its many wonderful date and time functions. These functions are some of the most popular in Excel.

The Excel WEEKDAY function returns a number that identifies the day of the week from a given date. It has several options to specify which day is the first day of the week.

This Excel function is very useful. This tutorial will show two examples of the Excel WEEKDAY function helping us to achieve typical Excel tasks.

[Read more…] about WEEKDAY Function in Excel – Two Examples

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.

Calculate date and time difference in Excel

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.

[Read more…] about Excel Formula for Elapsed Time in Days, Hours and Minutes

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 character with an Excel formula
[Read more…] about Count How Many Times a Specific Character Appears – Excel Formula