Quickly create a dynamic drop down list in Excel

I have a request from friend to create a dynamic drop down list in excel and I would like to share with my blog reader...

Part  1: 
- Open Excel and we start by creating a defined name, 
- Select the Formulas Tab, then Define
- Enter "SomeRangeName" under the Name text box
- Enter =OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B),1) under the Refers to text box

Note: You have to enter drop down items in single column you want here in above formula I have selected Sheet1 and column B, it will start filtering from Row B2.

Part 2:
To create a data validation list:

- Select the cell you want to show dropdown list eg. A1
- Select Data Validation under the Data Tab
- Select List under Allow
- Place the cursor in the Source text box and type formula "=SomeRangeName"

That's it.. now when ever you add items in "Column B" it will automatically populate in drop down list... 

Create User Define Function and Macro as an Add-in

We work lot in excel and manage our work with functions and macros. I am here creating the process that will save your time and effort to re-create again and again the same functions or macros.

Here we are going to create Add-in that will automatically load when you open your excel, and the function will be available all the time. So let's start.

Step 1: Quickly create an empty add-in for excel

1. Open excel and Save a blank workbook as a Excel Add-In (*.xlam) in your Add-In folder (%UserProfile%\AppData\Roaming\Microsoft\AddIns)
2. I named it PersonalAddIn.xlam (Save it with any name you like)
3. Click Office button.
4. Click "Excel options" button.
5. Click "Add-Ins" tab.
6. Select "Excel Add-ins" from manage dropdown list and click GO
7. Click "Browse" button
8. Select the (.xlam) file that you just saved in step 1
9. Click OK (Make sure you enable)

Step 2: Add custom functions and macros to your personal add-in

1. Go to the Developer Tab on the ribbon.
2. Click "View Code"
3. Click on the project panel you see VBAProject (*.xlam)
4. Click Insert Menu --> Module
5. Copy and paste custom functions and macros to code window.

That's it... we are done.

Step 3: Use your user define function

1. Go to the cell
2. Click "Insert Function" in formula bar
3. Select category "User Defined"
4. Select your custom function
5. Click OK

Step 4: Use your user define Macro

1. Go to "Developer" tab
2. Click "Macros" button
3. Select/Type the macro name
4. Click "Run"

Hope this will help you in you daily work, In case of any query you can write to me. Thanks for reading.

Count the number of spaces of text string in Excel

Simple to calculate the number of spaces of text string in EXCEL with following formula:

=LEN(A1) - LEN(Substiture(A1," ",""))

Above formula will count all the spaces in the text.


Count the number of leading spaces of text string with formula

=FIND(LEFT(TRIM(A2),1),A2)-1

Essential keyboard shortcuts for MS-EXCEL

Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly.

Control-Down/Up Arrow = Moves to the top or bottom cell of the current column Control-Left/Right Arrow = Moves to the cell furthest left or right in the current row

Control-Shift-Down/Up Arrow = Selects all the cells above or below the current cell

Shift-F11 = Creates a new blank worksheet within your workbook

F2 = opens the cell for editing in the formula bar

Control-Home = Navigates to cell A1

Control-End = Navigates to the last cell that contains data

Alt-= = Autosums the cells above the current cell

Add a number of months to a date in Excel

Many of my friends have query about how to add / Subtract month in excel, Simple way of doing as below


=Date(Year(Start_Date) + add_year, Month(Start_Date) + add_month, Day(Start_Date) + add_day)

Random Function

Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

Syntax

=RAND( )

Remarks

To generate a random real number between x and y, use:
RAND()*(x-y)+x

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number.

=IF() Function

How the IF() function works:
Syntex : "=IF(Condition,TRUE,FALSE)"

The Excel IF() checks to see if a certain condition is true or false. If the condition is satisfied then it returns true, else retun false.

The IF() we are using in this tutorial will asks if the value in column A is greater than the value in column B. If the condition get true then it will show value as "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D.
Add some value to Cell A1 and Cell B1 and write following formula in Cell D1.

=IF(A1 > B1,"A is larger","B is larger")

Note: There are two text statements "A is larger" and "B is larger" are in quotations. In order to add text to an Excel IF Function, it must be in quotation marks.

How to write Multiple IF function ?

From above example, if we want to check for 3 different value, we have to use multiple if function... Let's C how can we do it.
example with value : 
Cell A1 =5

Place formula at Cell B1
=IF(A1=5,"Five",IF(A1=10,"Ten","Unknown Value"))

In above examples we check for the value in Cell A1, If it contains value 5 then in Cell B1 you will get "Five", in case if you now change value in Cell A1 to 10 then In Cell B1 it will automatically reflect as "Ten" and other this two value will show "Unknown Value".
As I told you IF check for the Condition and work accordingly, when we have wrote "=IF(A1=5," is the condition check point TRUE="Five",  FALSE = IF(A1=10,"Ten","Unknown Value"). when we have written again IF condition in the IF that means it again going to check for condition and will return to parent function either TRUE or FALSE.