Best Online Learning Platform and Collect Information

How to create a searchable drop-down in Excel in 5 minutes

Hi friends, Welcome to The Independent learning, I am here with another great topic.

How to insert drop down list in excel

A Facebook friend asked me to create the Searchable drop-down menu in excel. So i have tried and hoped that this will help him. Create a searchable drop down list in Excel just like Google. This trick is fantastic for large lists. Giving the functionality to search within a list goes beyond a standard drop down list. This Article tutorial will show you how to create a combo box control on a spreadsheet and then use formulas to create a dynamic searchable drop down list. This list would look awesome on your Excel dashboards and reports.

The article covers multiple Excel formulas and functions including INDEX, COUNTIFS, and ROWS. It then uses a simple line of VBA code for the drop down combo box

In this article, I will show you how to create a Searchable drop-down menu in excel. This Drop down will work like google search engine and you will be able to fetch data within a second from your resource directory.

How to create a searchable drop-down in Excel in 5 minutes, Data validation in excel, Advance data validation, Google search in excel, How to insert drop down list in excel, How to create a searchable drop down list in Excel?For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values..., Learn how to create an Excel drop down that shows suggestions as you type. A step-by-step tutorial to create an Excel Drop Down List with Search Suggestions

I assume that your excel is compatible with macro and you have enabled the Developer Toolbar, and saved your file as .xlsm format. If you don't get this done, please do it first.



Setting Up things:

In Excel 2010 and 2013

Step 1- Click File Menu.
Step 2- Click Options.
Step 3- And in the Options dialog box, Click Customize Ribbon.
Step 4- Check the Developer box, then click the OK button.


In Excel 2007,

Step 1- Click Office button (Top most left, looks like windows logo)
Step 2- Click Excel Options.
Step 3- And in the Excel Options dialog box, Click Popular Tab.
Step 4- Check the Show Developer tab in the Ribbon box, then click the OK button.

Now you are all set for the preparation on Searchable Drop down list.


Watch the full practical video


Please follow the below mentioned steps carefully:


Step 1. Click Developer bar >> Click Insert >> In ActiveX Controls sector   select Combo Box to insert the same. See below:
Searchable drop down 1


Step 2. Draw the Combo box by Clicking and Dragging. Right Click the Combo box and then click on Properties.
Step 3. In Properties Dialog box please make following changes.
           (a) Select False in the AutoWordSelect field.
           (b) Specify a cell in the LinkedCell field. In this case, we enter G2.
           (c) Select 2-fmMatchEntryNone in the MatchEntry field.
           (d) Type DropDownList into the ListFillRange field then close.
See Below:
Searchable drop down 2


Step 4. Paste all your data in Column A.
Step 5. Paste this formula in the cell C2 =--ISNUMBER(IFERROR(SEARCH($g$2,A2,1),"")) and drag till last data cell In Column A. See Below:
Searchable drop down 3

Step 6. In Cell D2 Put this formula =IF(C2=1,COUNTIF($C$2:C2,1),"") and drag till last data cell In Column A. See below:
Searchable drop down 4

Step 7. In Cell E2 paste this                                                     formula =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"") and drag till last data cell In Column A. See Below:
Searchable drop down5

Step 8. Click On Formulas bar >> Click on Define Name >> Type "DropDownList" in the Name field.
Step 9. Put this =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1) formula in Refers to box. Then Click Ok.  See Below:
Searchable drop down6
Step 10. Dubble click the Combo Box that you inserted. VBA editor window will appear. Copy and paste the below mentioned code in the VBA editor.

Private Sub ComboBox1_GotFocus()
'Made by Vikash kumar
'The Independent Learning
'independentjournals.blogspot.com
'independentlearning.in
    ComboBox1.ListFillRange = "DropDownList"
    Me.ComboBox1.DropDown
End Sub

How to create a searchable drop-down in Excel in 5 minutes

Step 11. Close the VBA editor, And deselect the Design mode. And your Searchable drop down list in excel is ready.

Please Download the practice file from here
Must watch the practice video. this will help you to understand the topic.


Most commonly used Excel formulas.

How to use Vlookup in Excel.
How to import data from the web into Excel.
How To Make Shared Excel File And Allow To Edit By Multiple Users at one time.

Thanks For reading, I hope you got your problem. Please like and share this article on facebook if you found this useful. You can also comment your queries for fast response.  Stay tuned for further videos
Feel free to reach us. Info.indelearning@gmail.com

Thanks in Advanced.
Happy Learning

Share:

Hack your Whatsapp - How to unblock Yourself on Whatsapp

Hi Friend its been long time to see you. Believe me, I missed you a lot. I am here to tell you how to unblock yourself on Whatsapp.


"Disclaimer - This post is for educational purpose only, please do not misuse it. Happy Leaning."
How to hack Whatapp


Follow the below mentioned steps to Unblock yourself on Whatsapp:


  1.  Go to Setting.

    How to hack Whatapp
  2.  Click On Account.
    How to hack Whatapp
  3.  Now Click on Delete my account.
    How to hack Whatapp
  4.  Enter Your Phone number and
    How to hack Whatapp
  5.  Press DELETE MY ACCOUNT.
After Deleting your account, Uninstall the Whatsapp from your device. After uninstalling, restart your device. After restarting the device follow the below mentioned steps.
  1.  Download WhatsApp again from Google Play store or other app stores.
  2.  Install With the same number. Fill Other details.
  3.  Now You have a new Whatsapp Account with the same number and its      unblocked from all accounts they have blocked you. Enjoy.

Happy learning.

This post is only to let you know the possibilities. This is not a hack or a hacking idea. It is a general Whatsapp Trick that we all should know. In case of any emergency we can use it.

Its better to ask your friend for the region behind it, Try to understand his/Her concerns, feel sorry, ask apology. After ask his/her to unblock rather than unblocking yourself with a trick. Friendship is more important than our ego.




Share:

I am sure, You don't know these Excel shortcuts !

Hi Friends welcome again, i am here with an another great topic.


We all uses many of shortcut keys for our daily tasks and we all knows very well all the basic shortcuts. But i have something that a am sure you don't know these shortcuts.What happens if we are writing these ™ ©  ♣  ♠  . So In this article we are going to discuss about the shortcut keys for entering Symbols in Excel. We can use these Shortcuts keys any where on windows platform like Excel, Word, Crome, IE, Notepad etc.
Shortcuts for symbols


Pin this article to your workstations dashboards, note down in your notebook, or visit again to my blog for these shortcuts keys:

Alt + 0153   = TradeMark
Alt + 0169   = © CopyRight

Alt + 0174   = ® Registerd TradeMark

Alt + 0176   = °  Degree

Alt + 0177   = ± Plus Or Minus

Alt + 0182   = ¶ Paragraph Mark
Alt + 0190   = ¾ Fraction, Three-fourths
Alt + 0215   = × Multiplication Sign
Alt + 0182   = ¢ Cent Sign
Alt + 0161   = ¡  upside down Exclamation
Alt + 0191   = ¿ upside down Question Mark
Alt + 1        = ☺ Smiley Face
Alt + 2        = Black Smiley Face
Alt + 3        =  ♥ Heart
Alt + 4        =  ♦ Diamond
Alt + 5        =  ♣ Club
Alt + 6        =  ♠ Spade
Alt + 7        =  • Dark Dot
Alt + 9        =  ○  Clear Dot
Alt + 15      = ☼ Bright Sun
Alt + 12      = ♀ Female Sigh
Alt + 11      = ♂ Male Sigh
Alt + 13      = ♪ Eighth Note/Music
Alt + 14      = ♫ Beamed Eighth Note
Alt + 8721  = ◄ Backward
Alt + 251    = √ Sqiure Root / Check Mark
Alt + 24      = ↑ Up Arrow
Alt + 25      = ↓ Down Arrow
Alt + 26      = → Right Arrow
Alt + 27      = ← Left Arrow
Alt + 18      = ↕  Up And Down Arrow
Alt + 29      = ↔ Left Right Arrow


And many more, try yourself and share if you find any other Symbol by Pressing Alt + (Numbers)


Happy Learning
Share:

Hack your Excel ! Creating - Editing your own Custom list

Today's topic is creating a Custom List

Today we are going to discuss about custom lists, first you need to know that what is a Custom List in Excel? When ever we write Sunday or any day name and after we drags down, the Excel simply creates a list of days like Sunday, Monday, Tuesday etc. Its also happens when we write and drag a Month name. Like below mentioned video.


So how its happens ? All these questions are comes into our mind.

Q1. What is a Custom List ?
Q2. Can we create a Custom List of ours?
Q3. How to Edit an Custom List ?
Q4. How a Custom List works ?

You don't worry. all these questions will be answered in this article.

"see the video for full example and and practice session"



Follow the below mentioned steps carefully

Step 1 - Click on files tab in (Excel 2010). If you are using Excel 2007 then click on Office Button (right-top button).






Step 2 - Click on Options. Excel option window will appear.
Step 3 - Click on the Advanced tab. Left sixth from top. And drag till last. You will able t see Edit Custom list button as shown below image. Click Edit Custom Lists.




Step 4 - Write your entries by separating commas. (Sun, Moon, Earth, etc)




Step 5 - Click Add and Then Click OK.

Congratulations Your custom list has been added
Let's check it, Close Excel and open a saved file then write any of the words of the list and drag. Smile if working and do let me know if any error occurs.
Check now.

You can also delete any custom list my click Delete (Down to OK)


Happy Learning
Share:

Excel Basics Class 1- Customising the Excel Interface as per our need

Customising the Excel Interface as per our need



As You all are asked and most of you registers for Basics of MS Excel, So we are starting from MS Excel basics. This course is designed for Excel beginners. We will discuss only basics and we will start from custom setting of Excel. You can also join the live classes by subscribing our email lists. We will discuses each and every single point witch we need to learn. You can also view the Full video of class.


Today's topic is Customising the Excel Interface as per our need

So lets start 

Go to Excel Option by clicking File Tab (left of the Excel Sheet) --> Option
Excel Option window will appear as shown below:-

this is for the excel 2010 user if you are using 2007 you can go by clicking Office button (read the article for Excel 2007 training)

In the Excel options tab there are 10 sections you will able to see.
  • General
  • Formulas
  • Proofing
  • Save
  • Languages
  • Advanced
  • Custumize Ribbon
  • Quick Access Toolbar
  • Add ins
  • Trust Center

So lets start with 

1. General Excel option

Simply click the General then you will be able to see a some options as shown in above picture.
  • User interface option
  • When creating new workbooks
  • Personalise your Microsoft office
User interface option
Show Mini Toolbar- If we unchecked this option we will not able to see mini toolbar as shown in below image (please see the video for full clarification)


Enable live preview- when we do formatting we can see live effects by do checked this option.
I personally recommend Please do not Unchecked the both of options.

When creating new workbooks
when creating new workbooks as the point is saying itself, when we open the new workbook what we want in that. like witch font colour, which font style, and which font format you want a new sheet. we can customising these options as per our requirements. We can set that how many sheet we want in each new workbook, we can increase or decrease the sheets counts by minimum 1 to maximum 256.

Personalise your Microsoft office
You can write your name here  and Excel will change the property and set your name as the author for the each workbooks.
"Formulas and proofing we can skip because we do not need to discuss these point at this moment we will discuss it later in advanced Excel classes."

2. Save 

Click the save next to the Proofing and you will able to see the Save tab's area as shown in image below:-

in this option only two points are very important and we will discuss about only these. Please do not change any other option left as default except these.
  • Save workbooks in this format
  • Default file location
Save workbooks in this format
in this section we can set and change a the file Extensions (.xls, .xlsx, .csv) for our all new workbooks. Like we are working for a client and and our client operates Excel 2003. And we are using Excel 2010 or 2013. Then our files will save as .xlsx Format by default. Hence it will be not function fully and will be corrupt also some time. So by using this option we ca set a Default File type.

Default file location
In Ms Office default file save location is My Documents. But we can change it by pasting the required location in the bar.
"next is Languages tab and we can skip, we do not need to discuss about this because we all are using English language for Excel."

That's all for the First class next five options we will discuss in the next classes.
please join the live sessions  because in which i will try to answer your questions right there. You can also watch full class video on my Youtube channel by clicking here. join our mailing list to get each single information on your email.

Happy Learning
Share:

Uses of SMALL function in MS Excel

In this tutorial we are going to discuss about SMALL Function.

This article will help you to 

  • Understand the Small function.
  • Uses and requirements of Small function.
  • Syntax of SMALL function.
  • Description of small function.


Small Function:-

Formula name  :-    Small
Formula Syntax:-    =SMALL(array, k)
                         Array (Required) An array is a range of numerical data for which you want to                         determine the k-th smallest value. 
                         K (Required)  numerical value position (from the smallest) in the range of data                       to return.

Uses of SMALL Function:- this formula is basically uses to determine the #number of smallest number from a data set. 
see the video example below:


Please right your questions in to comment box.


Happy learning
Vikash kumar

Share:

Uses of Left and Right Formula

In this tutorial we are going to discuss about Left And Right formula.



This tutorial will help you to


  • Understand the meaning Left and Right formula.
  • Uses Of Left and Right Function.
  • Syntax and requirements of Left and Right formula.
Watch the full practice video for better understanding:

Left Function:-

Formula name  :-    Left
Category          :-    Text Formula
Formula Syntax:-    =LEFT(text, num_chars)

     In this formula Text can be a word or group of numbers and it is mandatory factor of the formula and num_chars must be a numeric value and it is optional factor of formula also.

Uses of Left Formula:- This formula is basically uses for to copy characters from left from a cell. Like we have a data like below and we want their initials or first character so either we copy each row manually or by use the Left formula.

Example :-


Left Formula Excel Vikash Kumar




Right Function:-

Formula name  :-    Right
Category          :-    Text Formula
Formula Syntax:-    =RIGHT(text, num_chars)

Requirements and Limitations of Right formula:-
     In this formula Text can be a word or group of numbers and it is mandatory factor of the formula and num_chars must be a numeric value and it is optional factor of formula also.

Uses of Right Formula:- This formula is basically uses for to copy characters from Right from a cell. Like we have a data like below and we want their few last of characters or only last character. So either we copy each row manually or by use the Right formula.


Example :-
Right Formula Excel Vikash Kumar

Thanks and happy learning
Neeraj kumar

Share:

Free Advance MS Excel Course online

Hi friends hope you are doing well !

Thanks for your Suggestions and enquiries, It is nice to answering you.

As I have so many questions, so I am thinking to start some free MS Excel training courses. Course will start from 5 July 2016.
Learn Advance MS Excel For Free by Vikash Kumar


These training courses are free of cost and i will also provide a document of completion the course. Any one can register and take course for free. Course will be online. I will Publish chapters on the web, videos on youtube and also share links with you on your emails. I will also available Live on web on Saturdays And Sundays via Go To Meeting for answer your questions practically, and i will share meeting codes on your email ids. So please register if you wish to get course materials on your emails and take this course.

Courses descriptions are mentioned below :-

1. MS Excel basics 
       This course is for excel beginners. For whom they are not familiar with excel, And don’t know more about the MS Excel, its uses and benefits. We will discuss about Customising the Excel as per our need and requirements.

2. MS Excel level 2        
         In this course I am planning to put all type of basic formula like (text, date and time, lookup and references and mathematical). General Usage of all Menu Tabs. 

3. MS Excel level 3
         In this module we will discuss about formatting, number, text, cell and conditional formatting. We also discuss about the Charts, Graphs and Dashboards.

4. Advanced MS Excel
          Nested if formula, functions, formula merging and other advanced tricks. All formula in details. All Menu tabs In details. Advanced protection methods. Sheet sharing methods.

5. Basics of Excel Macros
         In this Module we will cover basics of MS Excel Macros, VBA coding, and all macro creation methods.
                                   Click here to Register for Free

After taking these courses you will able to manage MS Excel Easily. You can create your own functions formula. I am providing this for free because i also need improvement as a trainer

If you think it will be helpful for you, you can register for free by clicking hereCourse will start from 5 July 2016.

Thanks for reading.
Share:

Popular Posts