Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Named Formula

| | Information Helpful? Why Not Donate.

Excel Named Formula - Named Ranges as Excel Formulas

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 Insert>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. ALL purchases 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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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