Learn to create Google in Excel
Excited ? Confused ? ... Don't be, as you are one click away from learning how to create your own "Google" in Excel.
You can make necessary customization at your end and leverage it to Impress your boss and colleagues.
1. First open normal excel file and your web browser.
Please download the ready made file by clicking on the link provide below and keep it open. Please refer to this file for further steps. Name of the file is Google In Excel
2. In web browser please open "www.goglogo.com" and enter the Heading that you want to give to your own Google, like I have given "Excel" as Heading and then click on the "Create my search page now" button present there.
Congrats ! You have achieved the first milestone. You can Print Screen and save the image editing it with Paint or Photo shop etc. and finally saving it at some location in your PC or directly pasting in your excel file.
BTW, It gives the logo in old Google format ... not the new one ... anyways...
BTW, It gives the logo in old Google format ... not the new one ... anyways...
3. Now come to Excel file. Name 3 Tabs as 1. Google, 2. Calculations and 3. Result
.
We would refer these names in this article later directly.
4. In Google tab Paste the Picture, if not pasted already, and below the picture draw a Text Box (ActiveX Control) from developer panel.
5. Now switch to design mode and in the properties window of this Text Box, assign the Calculations!A1 cell address in "LinkedCell" option under "Misc" category. Basically you link this Text Box with A1 cell of next tab i.e. Calculations. Now come out of the design mode, by click on design mode button in the developer panel. Try typing something in the text box and see if that is getting reflected in Calculations Tab's Cell A1. It will definitely.
6. Now Draw a List Box(Form Control) below the Text Box of equal width but more height.
7. Now Draw two buttons (using shapes) below the List Box and remove the grid lines from sheet using option in the View Panel. Now your file would be looking like :
8. Do a right click on the "Google" tab name in the bottom of Excel sheet and click on View Code; Embed the following code ... by simply pasting :
If TextBox1.Value = "" Then
Sheets("Google").Shapes("List Box 2").Visible = False
Else
Sheets("Google").Shapes("List Box 2").Visible = True
End If
End Sub
The code would make the List box visible only when the Text box is not blank.
Just check if the name of the List and Text box are same as mentioned in above vba script. You can check the name by clicking on the item and the name appears in box in left to formula bar.
Just check if the name of the List and Text box are same as mentioned in above vba script. You can check the name by clicking on the item and the name appears in box in left to formula bar.
9. Now let's go to Calculations Tab and populate the data in Columns V to Y. Columns are Name, Class, Major Subjects, Minor Subjects as it is there in Google In Excel. You can simply copy and paste it from my file.
In Column H Unique Names of Students should be populated (as done in Google In Excel).
Now populate two columns Cumm Yes and Yes/No in Column F and G using the formula mentioned in fGoogle In Excel.
Yes/No basically searches the A1 in the start of text of list.
Cumm Yes calculates the cumulative of the Yes/No series.
10. In Column M we simply put 1 to 20 in series and then look up Column H values against Cumm Yes in Column N ( Please do refer to Google In Excel). The list thus populated in Column N should be saved in a Name Search_recco ( Please check the Name Manager under Formulas Panel in Google In Excel).
11. Come back to Google Tab, write something in the Text Box so that list box is visible. Do a right click on list box and under Format Control >> Control >> Input Range give the name Search_recco (as defined in the step 10). Cell link should be Calculations!$A$6.
12. You can now see that the moment you start typing anything (try "p" and "j") in Text Box, the search result, if any, would start reflecting in List Box. You can select option that come in the list box.
13. Come back to Calculations Tab and use the formula "=INDEX(search_recco,A6)" in Cell A7.
14. So ultimately whatever we have done till now, value of cell Calculations!A7 is the result of all that exercise.
When we search something in Text Box in Google Tab, list Box show the search recommendations like the real Google. We select one of the suggestions; the selected option's value goes to Calculations!A7 cell of Calculations Tab.
15. We have used this Calculations!A7 cell finally in Result tab to populate a matrix. You can think your own creative way to create some amazing stuff.
16. The button on Google tab "Excel Search" can be hyperlinked to Results Tab so the same be be done by assigning following Macro to the button.
Sub goto_result()
Sheets("Result").Select
End Sub
Finally save this file in .xlsm format. Your Final Product would look like this:
I hope this is clear to you ... If not please do let me know though comments section. I can upload a video on its making on public demand.
Enjoy reading our other articles and stay tuned with ...
Kindly do provide your feedback in the 'Comments' Section and share as much as possible.
A humble appeal : Please do like us @ Facebook
12. You can now see that the moment you start typing anything (try "p" and "j") in Text Box, the search result, if any, would start reflecting in List Box. You can select option that come in the list box.
13. Come back to Calculations Tab and use the formula "=INDEX(search_recco,A6)" in Cell A7.
14. So ultimately whatever we have done till now, value of cell Calculations!A7 is the result of all that exercise.
When we search something in Text Box in Google Tab, list Box show the search recommendations like the real Google. We select one of the suggestions; the selected option's value goes to Calculations!A7 cell of Calculations Tab.
15. We have used this Calculations!A7 cell finally in Result tab to populate a matrix. You can think your own creative way to create some amazing stuff.
16. The button on Google tab "Excel Search" can be hyperlinked to Results Tab so the same be be done by assigning following Macro to the button.
Sub goto_result()
Sheets("Result").Select
End Sub
Finally save this file in .xlsm format. Your Final Product would look like this:
I hope this is clear to you ... If not please do let me know though comments section. I can upload a video on its making on public demand.
Enjoy reading our other articles and stay tuned with ...
Kindly do provide your feedback in the 'Comments' Section and share as much as possible.
A humble appeal : Please do like us @ Facebook