Part Two: How to create a SEARCHABLE drop-down list in Microsoft Excel

Part Two: How to create a SEARCHABLE drop-down list in Microsoft Excel

Making a dynamic drop-down list is one thing. But if the list contains hundreds of thousands of items and you need to select a specific one, it will be both tedious and time-consuming that becomes worse when the list is unsorted.
The solution is to make the list searchable. So, whenever you type in some alphabets and click the drop-down arrow, it should display all the results containing those specific alphabets only.

1 How to make this?

We will start with the same dataset as used in part one (see column A in figure 1)

Figure 1: Given dataset of different car brands

We want to create on this example a drop-down list in cell E2 of a data list in column A.

1.1 Step 1: Search function

Go to C2 cell and apply the search formula =SEARCH($E$2, A2) and press enter. Drag the formula to apply it across the entire column, where:

  • $E$2 is the cell where you want to create a searchable drop(-)down list.
  • A2 is the starting point of your data list.

Figure 2: Search function

Now, when you type in E2 to make a search. The search formula will check the data list (A column) for the typed alphabets and display the index number in the C column wherever it finds those specific alphabets, otherwise value error as below.

Suppose (see figure 3a) we type ‘audi’ in cell E2, we get 1 in the first row because this correspond with the first item of column A. Suppose (see figure 3b) we type a, we get 1;#VALUE;5; #VALUE;5; #VALUE;5;6. This means the letter a can be find in the first item on the first letter; on the third, fifth and seventh item on the fifth letter and on the sixth item on the sixth letter.

Figure 3a: Example on Search function

Figure 3b: Example on Search function

1.2 Step 2: ISNUMBER function / TRUE-FALSE / 0-1

Now, change the formula to =ISNUMBER(SEARCH($E$2,A2)), to display “True” or “False”

Figure 4: ISNUMBER function, result true/false

Now, we want to convert “True”, “False” into 1 and 0

Go to formula and change the formula to =IF(ISNUMBER(SEARCH($E$2,A2)),1,0)

Figure 5: Converting result of ISNUMMBER into 1/0

1.3 Step 3: GEenerating an incremental order whenever the typed alphabets are found

Change the formula to =if(isnumber(search($E$2,A2)),max($C$1:C1)+1,0)

Figure 6: Creating an incremental order in column C when typed alphabets are founded

1.4 Step 4: Indexing number of items (Rows function)

Go to cell B2 and apply the rows formula to count rows: =rows($A$2:A2), press enter and drag the formula.

Figure 7: Indexing Items

1.5 Step 5: XLOOKUP function

Now, we want to apply the XLOOKUP formula. Go to B2 and apply the formula: =XLOOKUP(ROWS($A$2:A2),C2:C9,A2:A9;””)

Figure 8: Applying XLOOKUP function

1.6 Step 6: OFFSET function  – Name Manager

Now, move to cell G2 and apply the formula: =OFFSET($B$2,,,COUNTIF($B$2:B9,”?*”)), copy the formula and press enter.

Figure 9: Applying OFFSET function

Go to Formula Tab → Define Name → click Define Name

Figure 10: Name Manager – How defining name

In the pop-up box, give a name and paste the formula, press Ok

Figure 11: Defining name to offset function

Now, move to the cell where you want to create the drop-down list (e.g. E2)

Go to Data → Data Validation → click Data Validation

Figure 12: Finding the Data validation dialogue box

Go to setting → select List → click in source text box and press F3 key

Select the formula that you created earlier, press Ok.

Figure 13: Data validation – F3 Paste Name dialogue box

Before we can use our list we have to make sure that the error alert checkbox is unchecked. See third tab, figure 14. When this is done, press ok.

Figure 14: Data validation – Alert checkbox

Your searchable drop-down list is created in E2. You can type and search the long list.

Part One: How to make a DYNAMIC drop-down list in Microsoft Excel

Part One: How to make a DYNAMIC drop-down list in Microsoft Excel

A drop-down list is an excellent powerful way to give multiple predefined options to your users and restrict them to select only one at a time. It is widely used on dashboards, websites and in mobile applications. A convenient and easy way to collect easy and quick data from users.

What makes many people crazy is that you always have to update the data source whenever you add a new entry. The more you add data the more frequently you need to update it.

This static drop-down list can be converted in a dynamic drop-down list. Here you do not need to update the data source again and again. A dynamic drop-down list has an advantage over static drop-down list because it automatically updates itself whenever you make a change in the source data cells.

There are different ways to make a drop-list dynamic. I will discuss two methods, namely the Table function and the Offset function.

Suppose, you have a data set as shown below.

Figure 1: Given dataset of different car brands

1 Method 1: The TABLE function

An easy way to develop dynamic drop-down list is using an Excel table for source data.

  • Select at least an item of your list.
  • Go to ➜ Insert ➜ Table.
  • Click OK.
  • Shortcut key= CTRL + T

Figure 2: Excel table method

When you have created the table, give the table an appropriate name. For example tbl_BRAND

Figure 3: Table name

Our next step is to refer to the table range data source. For this we need to use the INDIRECT formula (see INDIRECT blog).


Figure 4: Data validation: Indirect function

2 Method 2: The offset function

For more detail of how this Offset function work, I refer to the blog of February 2020. The syntax of this function is:

= OFFSET(reference, rows, cols, [height], [width])

Follow the next steps to create an Excel drop-down list using the OFFSET function.

  • Select the cell where you want to create the drop-down list (e.g. C3)
  • Go to Data → Data Tools → Data Validation

Figure 5: Finding the Data validation dialogue box

  • In the Data Validation dialogue box, under the setting tab, select “list” as the validation criteria.
  • A source field will appear
  • In the source field, enter the formula: =OFFSET(A2,0,0,COUNTA(A:A)-1)
    The breakdown of this is:

    • Reference is set to the first data value, A2.
    • Row is 0, indicating no movement or shift of rows.
    • Column is 0, indicating no movement or shift of columns
    • [Height] uses a COUNTA function. Select the entire column (A: A). The COUNTA function counts all the non-empty cells. This selection includes the cell ‘Brand’ which we do not want. So(,) we have to exclude this from our selection (-1).
    • [With] is 1. This is the default parameter and can be omitted.
  • Check “In-cell dropdown” (if it is not)
  • Click Ok

Figure 6: Data validation: Offset function

A drop-down list containing all car brands’ name will be created in the cell (e.g. C3)

Figure 7: Offset function: Drop-down list

Now with each brand item you add in both methods to the list in column A, the drop-down list will automatically add this new item to the list without any special adjustments!

Excel SEARCH functions: Part Two: The SEARCH function

Excel SEARCH functions: Part Two: The SEARCH function

As explained in part one, the SEARCH function in Excel is very similar to FIND because it also returns the location of a substring in a text string.

The syntax and arguments are akin to those of FIND.

1 Syntax

=SEARCH (find_text, within_text, [start_num])

The breakdown of this is:

  • Find_text indicates the text to find.
  • Within_text indicates the text to search within.
  • Start_num is an optional argument that specifies from which position the search shall begin in the text. If omitted, the search starts from the 1st position in the text.

If the SEARCH function does not find the find_text character(s), a #VALUE! error is returned.

2 Difference with find function

  • Not case-sensitive.
  • Allows the use of wildcards in find_text (i.e. “?”, “*” and “~”).
    • “?” is used to find a single character.
    • “*” is used for match sequence.
    • If you want to search the “*” or “?” the use the “~” before character.

3 Examples

  1. Suppose you want to find the word “car” in “motorcar”.
    The formula =SEARCH (“car”; “motorcar”) returns 6 because the substring begins at the sixth character of the word “motorcar”.
  2. Suppose you want to find the letter “e” in “Examples”.
    The formula =SEARCH (“e”; “Examples”) returns 1 because “e” is the first character in the word “Examples”, ignoring the case. The default search position = 1. Suppose the formula was = SEARCH (“e”; “Examples”;2). This returns 7 because the second “e” in the word “Examples” is the seventh character.

When a value is not found, the result will be an error. This was also the case in the FIND function. The function =SEARCH(“F”;”OPERAHOUSE”) gives an error as result!

Figure 1: Examples in practice – SEARCH

Excel SEARCH functions: Part One: The FIND function

Excel SEARCH functions: Part One: The FIND function

Excel provides a wide variety of text and numeric data processing tools. Two popular search functions are FIND and SEARCH. These are similar functions. First, I will discuss the FIND function.

The FIND function in Excel is used to return the position of a specific character or substring within a text string.

1 Syntax

= FIND(find_text, within_text, [start_num])

The breakdown of this is:

  • Find_text indicates the character or substring you want to find.
  • Within_text indicates the text string to be searched within. Usually it is supplied as a cell reference, but you can also type the string directly in the formula.
  • Start_num is an optional argument that specifies from which character the search shall begin. If omitted, the search starts from the 1st character of the within_text string.

If the FIND function does not find the find_text character(s), a #VALUE! error is returned.

2 Examples

I will explain this with three easy examples.

  1. Suppose you want to find the letter “O” in “OPERAHOUSE”.
    The formula for this is :
    = FIND(“O”; ”OPERAHOUSE”) or you can directly refer to the cell =FIND(“O”; A3).
    This formula returns 1 because “O” is the first letter in the word “OPERAHOUSE”. If you want to find the second “O” of this word, you have to adapt this formula to = FIND(“O”;”OPERAHOUSE”;2). This returns 7 as result.
  2. Suppose now that you want to find the small letter “o” instead of the big letter “O”.
    Using the formula =FIND(“o”; ”OPERAHOUSE”) or =FIND(“o”;A9) results in an error because the FIND function is case sensitive.
  3. Last, we want to check if there is an “F” in the word “OPERAHOUSE”.
    The formula =FIND(“F”;”OPERAHOUSE”) or =FIND(“F”;A12) returns an error because there is no “F” in “OPERAHOUSE”.

Figure 1: Examples in practice – FIND

Functions that make your file DYNAMIC: Part Two: The OFFSET function

Functions that make your file DYNAMIC: Part Two: The OFFSET function

In the first part we discussed the INDIRECT function. In this second part, we discuss the OFFSET function.
The function of this OFFSET is to return a reference to a range that is a specified number of rows and columns from cell or range of cells.
This function is not easy to create because of the number of arguments involved.

1 Syntax

= OFFSET(reference, rows, cols, [height], [width])

The breakdown of this is:

  • reference indicate the starting point, supplied as a cell reference or range
  • rows indicate the number of rows to offset below the starting reference
  • cols indicate the number of columns to offset to the right of the starting reference
  • [height] (optional) indicates the height in rows of the returned reference
  • [width] (optional) indicates the width in columns of the returned reference

The first 3 arguments are required and the last 2 are optional. All the arguments can be references to other cells or results returned by other Excel formulas.

2 How does this formula work?

Think of it as a GPS:
You can give it a starting point and then you tell it to:

  • How many rows to go down
  • How many columns to move across
  • And what range you want returned

Visualizing this, you would get the situation in figure 1.

Figure 1: Offset – GPS visualization

3 Example

Next, I will discuss this deeper with the practical example we used for the INDIRECT function (see below).

Figure 2: Car specifications

Suppose you want that column H (from cell H5 till cell H13) is referring to the width of the following car (car 2 till car 10). You could use a static formula, namely H5=B6 (ceteris paribus =B7 .. B14).

Figure 3: Car specifications – Static formula

Suppose you want to insert a row under car2. You will get, next situation.

Figure 4: Car specifications – situation after inserting row

Now, the cell H6 does not relate to B7 but to B8. I have a formula which result I do not want. I am interested in the row below the current row! The static function H5=B6 (ceteris paribus =B7 .. =B14) is not robust enough for me. Here, the best solution is to use the OFFSET function.

Figure 5: Car specifications – Dynamic formula – solution

The formula for this solution is =OFFSET(B5;1;0).
The breakdown of this is:

  • B5 is the reference is set to the first row
  • 1 is indication that there is 1 movement shift down of rows
  • 0 is indication that there are no movement columns

When we now insert in new row below car 2, we will get our obtained result.

Figure 6: Car specifications – situation solution after inserting row

The OFFSET function allows you to make more robust spreadsheets in a situation where people might insert rows or columns in the middle.

Functions that make your file DYNAMIC: Part One: The INDIRECT function

Functions that make your file DYNAMIC: Part One: The INDIRECT function

This blog is divided into two parts. First, we discuss the indirect function. Not many people use this formula. The benefits of using this are huge!
The function of this INDIRECT is to facilitate the search of values within a spreadsheet using text references.

1 Syntax

= INDIRECT (ref_text; [a1])

The breakdown of this is:

  • Ref_text indicate to a text string that contains the reference to a cell or a named range. This must be a valid cell reference, or else the function would return a #REF! error.
  • [a1] (optional) is a logical value that specifies what type of reference to use for ref text.

This could either be

o TRUE (indicating A1 style reference) or
o FALSE (indicating R1C1-style reference).
If omitted, it is TRUE by default.

2 How does this formula works?

To get the base idea of the indirect function, let us write a simple formula.
Suppose you have in cell A1 50 and in cell B1 text A1.

Figure 1: Base example – a

In cell C1 we put the INDIRECT function which refer to the cell B1 which is A1.
The function will lead to cell A1 where it picks the value to return, which is 50 (see figure 2).

Figure 2: Base example – b

3 Example

Next, I will discuss a more practical example. Below I have some data specifications about cars (see figure 3).

Figure 3: Car specifications

Suppose you want that column G (from cell G5 till cell G14) is referring to the width of the car (from car 1 till car 10).
You could use a static formula, namely cell G5=B5 (ceteris paribus = B6 .. B14)

Figure 4: Car specifications – Static formula

But maybe the end-user wants to know the height of a specific car. So instead of column B, he is interested in column C. That means that I must change all individual formulas because this is a static function. I could use the INDIRECT function to solve this issue.

How can I do this?

Figure 5: Car specifications – Dynamic formula – result column C

Solution: In cell G5 till G14, we want to refer indirect to the cell C5 to C14.

  • C = the column which the end-user wants to determine. We use cell G1 to refer it.
  • The rows where the specific cell is located, we will refer with the ROW function.Combining this gets the formula INDIRECT(G$1&ROW())


Now If I change G$1 to column D, I will get the results in next figure:

Figure 6: Car specifications – Dynamic formula – result column D

It allows for much more versatility. This is very useful.

I hope the INDIRECT formula helps you a lot from now on!

The Excel Table Lookup Functions – Part One

The Excel Table Lookup Functions – Part One

One of the most useful functions available in Excel is the LOOKUP function. This allows you to take any value entered, find it in a data range, then return a value or information from that same data range without having to scroll through a list.

1 Usage of LOOKUP in Excel

The widely used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP enables you to look through an information extend that is set up vertically. HLOOKUP is precisely the same capacity, however looks into information that has been designed by rows rather than column.


VLOOKUP and HLOOKUP have some downsides that could prompt potential blunders, particularly for people who are new to using a spreadsheet or financial model.


In specific cases, particularly when there is an unsorted range, a mix of INDEX and MATCH capacities could be a more accurate way of finding data than the LOOKUP capacities.

INDEX(MATCH) can be a more dominant solution in fiscal models than the old school VLOOKUP/HLOOKUP, and can be utilized to build the vigor of models in very developed and complex circumstances.

2 VLOOKUP, HLOOKUP in detail


VLOOKUP scans for values in the furthest left column of an information range, and after that returns a value in a same row from a row you selected in the range. VLOOKUP is utilized rather than HLOOKUP when your examination values are situated in a column to the left side of the information you are looking for.

The syntax for VLOOKUP will be VLOOKUP (lookup_value, table_array, column_index_num, range_lookup).

‘range_lookup’: If TRUE (or precluded), an estimated match is returned (i.e., if an exact match is not found, the next biggest value that is not as much as ‘lookup_value’ is returned). If FALSE, VLOOKUP will locate an exact match.

Prices of 2019 of the Golf range is shown in the image below as an example.

The first column is the model code, the second and third column represents the model description and fuel type. In the fourth and fifth columns prices are shown.

VLOOKUP equation is utilized for (a) solution as (it) appears in the image above. The “column_index_num” for prices 2019 inclusive charge would be “5”, for prices 2019, no charges mentioned, the number would be “6”. Remember that the “FALSE” is excluded from the “range_lookup” in this sample because the data in the first column are sorted in ascending order.


HLOOKUP functions the same way as VLOOKUP, however looks into data that has been organized by rows. HLOOKUP looks for a value in the top row of a table, and afterward returns a value in a similar column from a row you determine in the table.

The syntax for HLOOKUP is HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)



Use caution if any rows or columns are added, moved or erased:

  • If any columns or rows are added, moved or deleted within the range, the VLOOKUP and HLOOKUP will return the position of the new column or row in the lookup column/row index number.
  • For example, if a section is added between column D and E in the image above, at that point you must make sure to change the ‘column_index_num’ for prices 2019 inclusive charge changes from ‘5’ to ‘6’.


VLOOKUP could look for a value in the first left column of data range. Thus, HLOOKUP could look for values in the top line of the range:

  • At the point when the key field is to right side of the range you need to recover, VLOOKUP will not work.


Value in the first row/column of range should be arranged if range_lookup is ‘TRUE’ (or excluded), except if the range_lookup is ‘FALSE’:

  • This could prompt potential blunders, particularly when the user is new to the function.



The VLOOKUP/HLOOKUP are often used before a LOOKUP function. Nonetheless, the LOOKUP function is cleaner and very transparent, as it only needs two ranges and a LOOKUP value. The syntax for LOOKUP is LOOKUP (lookup_value, lookup_vector, result_vector).



As discussed above, VLOOKUP and HLOOKUP functions have some downsides that could prompt potential blunders, particularly for users who are new to the model. In spite of the fact that the basic LOOKUP function could be used some of the time to replace VLOOKUP/HLOOKUP, it is constrained to a single row or column to hold results. In some situations, utilizing a mix of INDEX(MATCH) functions might be the only solution, because they are more powerful and adaptable than VLOOKUP/HLOOKUP.

INDEX returns the value dependent on the particular row and column number indexes: INDEX (array, row_num, column_num).

MATCH returns the position of the coordinated items in a one-dimensional list: (lookup_value, lookup_array, match_type) ‘match_type’: If match_type is 0, MATCH finds the first value that is actually equivalent to lookup_value.

The steps to solve the example using INDEX(MATCH) are:

  • Find the ‘row_num’ for selected model BQ13CX using the MATCH function – the solution is row # ‘6’
  • Find the ‘column_num’ for prices 2019 exclusive charge using the MATCH function – the solution is column # ‘4’
  • The sales forecast can then be solved using the INDEX function, using the ‘row_num’ and ‘column_num’ solved using the MATCH functions above

How to Create Pivot Tables in Microsoft Excel

Sometimes, when you hear about Microsoft Excel, it seems too good to be true. Doesn’t it? You simply enter some number with a formula, and basically anything that you would need to do yourself, can be done automatically by this software. Do you need to combine two sheets with comparable information? Excel will do it for you. Do you need to do straightforward math? Excel will do it for you. Do you need to consolidate data in various cells? Excel will do it for you.

Excel has so many different features from formulas to chart and tables. Pivot Table is one of the most used tables in Excel that are usually required for different cases. But, how actually can you create a Pivot Table in Excel?

Pivot Tables are utilized to reorganize information in a spreadsheet. They will not change the information that you have, however they can total up the numbers and look at changed data in your spreadsheet, depending upon what you would like them to do.

Here is an example of a Pivot Table. Suppose you need to investigate what number of individuals there are in each house at Hogwarts. You might feel that you have very little information, yet for longer data sets, this will prove to be useful.

To make the Pivot Table, you should go to Data > Pivot Table. Excel will automatically draw your Pivot Table; however, you can generally change around the order of your data. At that point, you have four choices to look over.

Report Filter

This enables you to analyze a specific row in your dataset. For instance, you need to make a filter by house, you could decide to just incorporate students in Gryffindor rather than all of the students.

Column Labels

These could be your headers in the dataset.

Row Labels

These could be your headers in the dataset. Both Row and Column names can contain information from your sections, for example First Name can be moved to either the Row or Column mark – it just relies upon how you need to see the information.


This area enables you to analyze your information in a different way. Rather than simply pulling in any numeric value, you can total, tally, average max, min, count numbers, or complete a couple of different controls with your information. By default, when you drag a field to Value, it generally completes a count.

Since you needed to include the number of students in each house, you can go to the Pivot Table and drag the House section to both the Row Labels and the Values. This will total up the quantity of students related with each house.

Below animation explains the whole process and will help you follow along in creating a Pivot Table in Microsoft Excel.

Creating pivot tables in Excel

New Note Feature in Office 365 Excel


Microsoft is always trying to make its products be more than a match for its competitors. Microsoft office is a pioneer in productivity niche and still remains the leader.

Microsoft office has recently launched an update with some new features. There has been some changes in all of its Office product including MS Word, PowerPoint and Excel.

There is a noticeable change in Office 365 Excel which we believe will help boost your work efficiency and effectiveness. Microsoft Office 365 has changed the way comments in Excel used to work.  

You would notice two different features which are comments and notes. Although they used to be considered as the same thing. The difference between both of these is that comments now allow you to leave a reply. Therefore, called threaded comments. While notes are meant to be used for annotations.

Comments in Excel  365

The comment now has a reply box which allows you to mention a co-worker. This allows you to start a conversation within the worksheet. Hence, increases the work efficiency.

The mentions will let your co-worker know that you need their input on that particular cell. Your co-worker can leave a reply by mentioning you in that cell or they can simply follow the comment left by you.

Notes in Excel 365

Notes used to be called comments in the earlier versions of Excel. Unlike comments, notes do not have the reply box and you could not mention your coworkers from Notes. Notes can be used to simply add a reminder without requiring an input from your coworkers.

How to insert a Comment in Office 365 Excel?

To insert a note in Office 365 Excel, follow the given step.

Step 1:

Right-click the cell and then click New Comment.

Step 2:

Use the comment box to leave your comment. You can mention a specific user to assign that comment to a user by using the @ symbol after the user name, or select a user from the dropdown list that appears.

Step 3:

Click the Post button or press Ctrl+Enter.

Your comment should be successfully posted in Excel.

What Else You Should Know?

Edit A Comment

You can click the Show Comment icon after selecting the cell, to edit a threaded comment. After clicking the show comment icon, hover over to your comment and click on the Edit button.

Delete A Comment

You can also delete a comment by right clicking on the cell and press Delete Comment button.

Edit/Delete A Reply

You can delete a reply by selecting the cell and then clicking on the Show Comments button or the little comment icon in that cell. Hover over the desired reply you want to edit or delete. Click on Edit button to edit your reply or click the Delete button to delete you reply.


How to use dropdown calendar and date picker in Microsoft Excel

Have you ever wanted a calendar to show next years date? It’s easy and convenient in most of the cases.

It can be a headache to maintain the data integrity when you are working with bigger and shared Excel worksheets. Especially, entering dates can become a nasty work. It always confuses the people if they should be mm/dd/yy or dd/mm/yy or mm-dd-yyyy? Or can you type a date like “10 Oct 2018”? Oh, and what would be the date of the third Friday in October this year?

Adding a drop down calendar in your excel sheet can solve these problems. A drop down calendar allows your users to fill the dates on a click of mouse. This guide shows you a trick that might work for you and save you tons of your time.

Before we start, you have to make sure the developer tab is available. If the developer tab is not already available in your Excel ribbons, you can follow these steps.

The first thing you need to do is click on the file in the top left of your screen and select “Options” at the very bottom of it.

This opens a new “Excel Options” box with bunch of options to select. Now click on “Customize Ribbon” which will bring out some more settings.

You should check the “Developer” option in the right hand box, under main tabs.

TADA! Now you have developer tab in your Excel ribbons. This is where all the magic is going to happen.

Now that you have the developer tab, let’s jump right into creating calendar and using it as a date picker.

In the developer tab, click on insert and go to the very bottom, where it says more controls. More controls allow you to insert a control from the set of controls available on your computer. The control we are going to use is available by default.

Once you click the more controls it open a new box with a list of options. In the list, scroll down the Microsoft date and time picker and select it.

Now you need to place the calendar somewhere on the screen. So select some part on your excel sheet and place it.
In the next step, turn off the design mode under developer tab. Now when you click on the little arrow it will bring out the calendar.

If you want to use calendar as a date picker you need to take a few more steps. Don’t worry we are almost there.

Now, turn back the design mode on again and click properties under developer tab. Under the properties click on LinkedCell and enter the cell number of your date. In my case which is B2.

Once you have assigned the date cell, turn off the design mode off again.
Now when as you pick the date from calendar it changes the date in your assigned cell.

This way you can share your excel sheet with other users and they can pick a date from calendar to fill the dates.