everydayExcel Newsletter Header

The SMALL Formula Isn't Small After All

I have known the SMALL formula for many years. I always thought that its use is limited to reporting the value of the smallest number in a range such as the lowest priced product in the company, for a particular month, or the cheapest component in the BOM (Bill Of Material). But my appreciation of the SMALL formula drastically changed when I saw how it could be incorporated into a longer formula to work like a FIND function. This SEARCH function is different from the original Excel FIND function as shown in this Customer Database template created by Ayushman, an expert Excel user. This custom-made SEARCH function allows any user to just enter part of the name into the input cell and the formula will list down all the names containing that partial name. It reduces the SEARCH effort to just one step, whereas for the FIND Function, you have to activate the function, enter part of the name into the find box and then find the results one by one.

This SEARCH function is also unlike the AUTOFILTER function which works only on your master list. The SEARCH function allows you to display the results in another worksheet so that the master list is protected from any accidental changes. This is especially useful if you do not want anyone to touch the master list in case they mess it up. You can convert the above-mentioned Customer Database template into a product search function. To download a copy of the template, click here.

Question and Answer

Question: You have an opportunity to purchase an investment note that still has 4 yrs to run. You can buy today at 2700. It will pay u 750 per year and you will receive the 1st payment in a year. Shoud u buy this note assuming you can earn 6% on alternative investment?

Answer: To a layman, this look like a good investment since the total paid out is $3000 for an investment of $2700, a net gain of $300. But if you use Excel NPV formula to calculate the investment based on alternative investment rate as the discount rate for NPV calculation, you will get negative $101. This means that the alternative investment is better.

Question: Why is it that when I click on my Excel Icon to start a new spreadsheet, it pulls up my last saved spreadsheet? It's been doing this for about a week now! It's driving me crazy!

Answer: You have accidentally saved the file into the XLStart folder. Remove the file from the folder and it not appear again.

If you are using Windows 7, the folder is found in C:\Users\Jason Khoo\AppData\Roaming\Microsoft\Excel. Remember to replace Jason Khoo with your own login name.

Question: I am trying to use freeze pane to freeze the first top couple of rows in my worksheet, but I only want these rows to show down to a certain row number. Specifically: when you scroll down, rows 1 and 2 show on the screen up until row 65...that's where they stop. Is this possible to make happen?

Answer: You can hide all the rows after 65. In this case, they will not be able to scroll beyond 65 rows. If need be, add in a worksheet protection so that the rows cannot be unhidden.

Question: I have 2 excel sheets, one has serial number and name, the another has only names not in order, i want to put same serial no to the another sheet where the names are same as first sheet, is this possible???

Answer: You can do so using the VLOOKUP worksheet function.

If you have any question regarding Excel, please feel free to send it to us.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"


Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.