Create Range Name in Excel

Today I am sharing quickest way to create Range Name in Excel. Just 3 easy steps

1. Select the cell(s) you want to name
2. Type Range name in the name box
3. Press Enter

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1. Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.

Name length A name can contain up to 255 characters.

Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name

Check out screen cast below to see it happening.

Named ranges is part of defined names in excel. It is just not limited to a cell on the worksheet or range of cells, it can also be a constant or a formula in your workbook.

It has many uses in Excel, some of them are :-

1. Make your formulas much easier to understand.
2. Refer to dynamic ranges in excel by creating dynamic range names
3. Can be used in data validation based on contents on another worksheet
4. Can be used for conditional formatting based on contents on another worksheet.
5. These are best used in Excel Macros. It makes your macros more robust
6. Use them as constant value without referring to contents on your worksheet.

Will be covering some of the topics in my upcoming posts, so stay tuned to learn more uses of defined names in excel.

Add Spin Buttons Active X Excel

In my previous posts I have shared EMI Calculator with you. This was mode using Excel PMT function and Spin Buttons ActiveX Controls in Excel. In this post I am sharing how to add spin buttons ActiveX controls to excel worksheet.



Step 1 Have Developer Tab visible in your Excel 2007. In case you already have it proceed to step 2
Step 2 Click on Insert under controls group
Step 3 Click on Spin Buttons under ActiveX Controls
Step 4 Draw Spin Button on your worksheet.
Step 5 Rename the Spin Button Object
Step 6 Add VBA code to the Spin Button

Checkout Screen Cast Below

In this screen cast we have added spin button next to loan amount and renamed the spin button object as Loan

Similarly add two more spin buttons next to Interest Rate and Years. Rename them as Interest and Years respectively.

To make these buttons work we need to add VBA code. Separate macros are required for each button that is why we have renamed the spin buttons. It is to give them a name that can be related later on by us when we look at the macro code. Macro code will be using the names we have given to them

Now to add the VBA code , double click on the spin button object, it will open VBA editor. Copy following VBA code to worksheet module

Private Sub Loan_SpinDown() With Range("C4") .Value = WorksheetFunction.Max(100000, .Value - 100000) End With End Sub Private Sub Loan_SpinUp() With Range("C4") .Value = WorksheetFunction.Min(10000000, .Value + 100000) End With End Sub Private Sub Interest_SpinDown() With Range("C5") .Value = WorksheetFunction.Max(0.03, .Value - 0.0025) End With End Sub Private Sub Interest_SpinUp() With Range("C5") .Value = WorksheetFunction.Min(0.25, .Value + 0.0025) End With End Sub Private Sub Years_SpinDown() With Range("C6") .Value = WorksheetFunction.Max(1, .Value - 1) End With End Sub Private Sub Years_SpinUp() With Range("C6") .Value = WorksheetFunction.Min(25, .Value + 1) End With End Sub

With this your EMI Calculator with Spin Buttons ActiveX Controls is ready

Visit EMI Calculator page if you want to download excel file complete with macros and Spin Buttons Activex controls

Show Developer Tab in Excel 2007 Ribbon

In case you are working with ActiveX Controls or Macros in Excel , you need to have Developers Tab visibile in your Excel 2007. When you start using Excel 2007, developers Tab is not visible , however you can set it visible with following steps



Step 1 - Click MS Office Button
Step 2 - Click Excel Options
Step 3 - Click Populor
Step 4 - Tick Show Developer Tab in the Ribbon check box
Step 5 - Click Ok

Excel Formula : PMT Function

In my previous post I shared EMI Calculator with you. In this post I am sharing how to make EMI Calculator. This has been done using PMT Function in excel to calculate EMI amount and Spin Buttons ActiveX Controls to fine tune the amounts.

Using PMT Function in Excel

PMT Function Calculates the payment for a loan based on constant payments and a constant interest rate. For calculating EMI you need 3 inputs.

Loan Amount , Annual Interest Rate, Loan Period in Years. Using these figures EMI can be calculated with PMT Function in Excel

Syntax : PMT(RATE,NPER,PV,FV,TYPE)

RATE : is the interest rate for the loan. In case you are making monthly payments devide the annual rate by 12 to get monthly rate.

NPER : is the total number of payments for the loan. When payments are made on monthly basis you need to multiply number of years with 12 to arrive at total number of payments.

PV : is the present value, or the total amount that a series of future payments is worth now; also known as the principal. We take this figure as (-) Negative in our formula since it is receipt of funds.

FV : is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. In our formula we omit this figure as we need to reach zero balance at the end of term.

Type : is the number 0 (zero) or 1 and indicates when payments are due. If payments are due at the end of the period use 0 or omit this figure. use 1 if payments are made at the beginning of the period. In our formula we have omitted this.

We have calculated our figures with following formulas

EMI : =PMT(Interst/12,Years*12,-LoanAmt)

Total Amount with Interest - This is total outgo during the loan term. This can be calculated using : = EMI*Years*12

Flat Interest Rate : = (Total Amount with Interest - LoanAmt)/LoanAmt/Years : Many financiers talk about Flat interest rate while landing money. They calculate simple interest on entire loan amount for entire period of the loan. Many people go for the loan without understanding the actual interest rate. Flat interest rate looks very attractive as it is much lower than the actual interest rate which is calculated on reducing balance. You can see the difference in the figures above. So be careful while borrowing on flat interest rate.

In next post I will cover Adding Spin Buttons ActiveX Controls to Worksheet.