<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**LOOK! Free Excel Training **<< **Back to**Excel Formulas Index **<< Back to** Excel Named Ranges

**Named Formulas**

Now we have seen how easy Named Ranges and** Named Constants ** are, we can look at a little known ability of Names where they can used to create **custom formulas**. For example, let's suppose we frequently use a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is copied across many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;

1) Select cell A101 (**this is vital**).

2) Go to I**nsert>Name>Define** and in the "**Names in workbook**" box type: **SalesLessCosts**

3) Now click in the "**Refers to**" box and type: **=SUM(A1:A100)-SUM(B1:B100)** then click** Add**.

Now you can replace the formula in cell A101 with: **=SalesLessCosts**. You can also copy this across row 101 and it will change its **relative references** just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected **A101 before going to** **Insert**>**Name**>**Define** and used relative references in =SUM(**A1:A100**)-SUM(**B1:B100**) when we added it to the "**Refers to**" box.

You could force full Absolute simply by using: =SUM(**$A$1:$A$100**)-SUM(**$B$1:$B$100**) or relative row absolute column like: =SUM(**$A1:$A100**)-SUM(**$B1:$B100**) or, of course any mix of absolute/relative row/column you desire. This can be a bit confusing at first, but some trial and error along with being aware of the selected cell when going to **Insert**>**Name**>**Define** will help a lot!

**Use the Named Formula on Another Worksheet**

Let's say you wanted to have these results only on another sheet to where the $A$1:$A$100 and $B$1:$B$100 ranges are. For a standard formula you would use a formula like: =SUM(**Sheet1!**$A$1:$A$100)-SUM(**Sheet1!**$B$1:$B$100). However, we cannot use: **=Sheet1!SalesLessCosts** as Excel will assume you mean the Workbook name and change it accordingly. Without it, Excel will sum the ***relative*** cells on the same Worksheet as you **Enter**: **=SalesLessCosts**

What we need to do is **precede** the formula name in the "**Names in workbook**" box of Insert Name dialog with: **'Sheet1'!** The single apostrophes are not required when the sheet name has no space, but **are** when a space character is used as part of the Worksheet name, i.e. "Sheet 1". **With this in mind, it pays to use them anyway as it won't matter.**

1) Select cell A101 (again, this is vital).

2) Go to **Insert**>**Name**>**Define** and in the "**Names in workbook**" box replace: **SalesLessCosts** with **'Sheet1'!SalesLessCosts** and click **Add**.

Now, in the required cell simply **Enter: ='Sheet1'!SalesLessCosts**

<< **Back to**Excel Formulas Index **<< Back to** Excel Named Ranges

**Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money**

**Special! Free Choice** of Complete Excel Training Course **OR **Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 **gets you BOTH! Purchases MUST be made via this site**. Send payment proof to [email protected] 31 days after purchase date.

**Instant Download and Money Back Guarantee on Most Software**

**Excel Trader Package**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**