## Who else wants to know the secret to Excel Formulas?

Invest in yourself in 2015 and reduce your frustration with formulas in Excel for just $19.95 - Click here to enroll todayA colleague of mine came running over to my desk the other day in quite a pickle! He said “The New Paperclip… how on Earth do I name a range in Excel 2007?”

“Simple my friend” I replied. Little did he know that I have no idea where it was, but I put my faith into the beauty that is the Ribbon to figure it out.

For the uninitiated, Naming a range is a great tool you can use in Excel. It allows you to give a descriptive name to a range (like ‘Expenses’) instead of using the normal notation like (A10:A25). Very handy if you work with quite a few formulas in a sheet.

So back to solving the problem. Firstly, why would you name a range in Excel? The answer is to make it easier to work with formulas. So straight away I went looking for the Formulas tab in the Ribbon. And there it was, a whole section on Named cells, which included the Name Manager (which enables you to control all the name references you have added to your workbook), Name a Range, and a few other Name functions.

Problem solved, in about 3 seconds flat. The Ribbon is definitely going to make people more productive, just as long as they start thinking in a “Results Orientated” way. Not too much of a shift if you ask me!

[tags]Excel 2007, Named Range[/tags]

Funny, where your screen shot shows “Name a range” mine says “Define name”. I’m using Beta 2 TR.

You got me… I have been lazy and havn’t updated to TR2 yet 🙁 *Kicks Off Upgrade!*–TNP

I just worked out you can also name a range by typing a name in the textbox to the left of the formula bar. Removing it – that is another matter, you have to go to the Name Manager.

Thank you very much! Actually, your post initially had been absolutely usefulness to solve my issue but the steps you described had helped me very well so my problem was successfully resolved – thank you again 😉

usefulness, I suppose is similar to useless.

anyway, in the release version of Excel 2007 it’s also called “Define name”.

Sorry, but the ribbon stinks if you know the menus after 20 YEARS of using Office. Been using this PRETTY interface for 2 years and still not as good as menus. Thank GOD they left MOST of the keyboard shortcuts.

Results oriented thinking comment – I take issue. I wanted to use a named range in a lookup list for data validation, so thinking results, I went to the data ribbon, where I would naturally expect to see validation – found it there, but when got into selecting list, was no way to get to named ranges in the workbook, or to create one. So one person’s intuitive ribbon is not necessarily the size that fits all. The non-customizable ribbon is way oversold… The underlying power of the office products can’t ever be expected to be supported by one user interface.

Alright, for years I have been using named ranges to calculate my monthly utilities payments from a special checking account. I built the spreadsheet in Exce1 2003 and named ranges for Date, Transaction name, and Amount using fixed cell references for the beginning of the ranges ($A$2, $B$2, $C$2) and floating cell references for the ends of the ranges ($A150, or whatever, etc). I always named a blank row at the end of each range. Every month I import the last month’s transactions from my bank account and insert them at the end of the listing of previous months’ transactions. The formulas below my ranges give what I want the next month’s amounts to be into that checking account to cover expected expenses. Some utilities, like gas and electric, compute an average dollar amount for the last twelve months, others use the latest transaction amount, others use the largest amount in the range for the named transaction. This always worked in Excel 2003. In Excel 2007, when I insert the latest transactions, it messes up the range values and now tells me my formulas have a circular cell reference. This is not am improvement. What can I do to make named ranges work in Excel 2007 as they did in Excel 2003?