Excel allows us to give Worksheet ranges names that can make our formulas easier to read. For instance if we use the above example that we used for the SUMIF Function, we could name our Criteria range (B1:B7) "Names" and our Sum_range (A1:A7) "Amounts". This would make our formula a bit easier to read.
There are however some basic rules for naming cells that we must adhere to. These are listed below and are from the Excel help file.
Guidelines for naming cells, formulas, and constants in Microsoft Excel
There are a couple of ways we can name ranges so let's jump straight in with an example.
You should have the formula =SUMIF(Names,"D*",Amounts) giving the result of 120.
Using the Create names dialog is the easiest method to use if we are going to be calling our ranges the same names as the column and/or row headings as it saves typing and typos. We could, if we wanted, type the names in ourselves, by typing them directly into the Name box. The Name box is on the left of the Formula bar. If we click the small drop arrow on the Name box, we should see both our named ranges Amounts and Names. If you select either one Excel will take you straight to the chosen named range and select it. It is in this box that we can type a name in directly.
To see what I mean select the range A1:B7 and click in the Name box. Type the word Data and push Enter. Now select any cell outside of range A1:B7 and select the name Data from the Name box, you will be transferred automatically to the Data selection.
The other thing we need to know when dealing with named ranges is how to delete them. This can only be done in one way and this is via the Insert name dialog box.
You will notice that our SUMIF formula is now retuning #NAME? This is Excels way of telling us the formula within the cell contains text is does not recognise. Delete the #NAME?
More on Named Ranges Here
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
|Lesson 1 - Excel Fundamentals|
|Lesson 2 - Starting Excel and Excel Workbooks|
|Lesson 3 - Excel Toolbars and Task Panes|
|Lesson 4 - Excel Worksheets|
|Lesson 5 - Excel Cells and Navigating a Worksheet|
|Lesson 6 - Excel Cut/Copying and Pasting Data|
|Lesson 7 - Excel Copying with the Fill Handle|
|Lesson 8 - Excel Paste Special|
|Lesson 9 - Excel Insert Command|
|Lesson 10 - Excel's default options|
|Lesson 11 - Excel's Undo and Redo|
|Lesson 12 - Excel's Format Painter|
|Lesson 13 - Excel's Dates and Times|
|Lesson 14 - Excel's Custom Formats|
|Lesson 15 - Excel Formulas|
|Lesson 16 - Excel Cell References|
|Lesson 17 - Excel: Avoid Typing|
|Lesson 18 - Excel Formulae Arguments & Syntax|
|Lesson 19 - Excel Autosum Formula|
|Lesson 20 - Excel Auto Calculate|
|Lesson 21 - Excel's Insert Function|
|Lesson 22 - Excel's Useful Functions|
|Lesson 24 - Excel's Constants and the Paste Name Dialog|
|Lesson 25 - Excel's Calculations|
|Lesson 26 - Excel Comments Cell|
|Lesson 27 - Excel Find and Replace|
|Lesson - 28 - Clear Excel Cell Contents|
|Lesson 29 - Effective Excel Printing 1|
|Lesson 30 - Effective Excel Printing 2|
|Lesson 31 - Sorting in Excel|
|Lesson 32 - Hide/Show Row/Columns in Excel|
|Lesson 33 - Auto-Formats in Excel|
|Lesson 34 - Creating a Basic Excel Spreadsheet|
|Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet|
|Lesson 36 - Excel Worksheet Protection|
|Lesson 37 - Excel IF Formula Nesting|
|Lesson 38 - Excel Function Now/Today Formulas|
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.