May 15, 2018 Step 1, Open Excel on your PC or Mac. It’s in the Start menu on a PC (usually in the All Apps folder), and in the Applications folder on a Mac.Step 2, Open the file you want to edit. An easy way to do this is to press Control+O, select the file, then click Open.Step 3, Click and drag the mouse over the data range. This highlights and selects the cells. I am running a QueryTable in Excel for Mac 2016, with destination to range 'datavalue', name for cell A1 in spreadsheet 'hs', QueryTable assigns the name 'ExternalData1' to the same cell A1 after extracting the data, I want to delete name 'ExternalData1' so I can refer to the value in 'datavalue' at the end of the script but can't.
You can create formulas that are easy to understand by using descriptive names to represent cells, ranges of cells, formulas, or constant values.
Use the provided sample data and the following procedures to learn how to assign names to cell references and create formulas that use them.
To better understand the steps, copy the following sample data to cell A1 on a blank sheet.
Create a blank workbook or sheet.
Select the following sample data.
Note: Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.
Selecting sample data in Help
Region | Projected | Actual |
---|---|---|
East | $5,000 | $5,325 |
West | $1,500 | $1,477 |
South | $2,500 | $2,633 |
North | $500 | $1,211 |
Midwest | $3,500 | $3,322 |
Central | $2,000 | $2,120 |
Press + C .
In the sheet, select cell A1, and then press + V .
Select cells C2 through C5, which are the actual sales for the East, West, South, and North regions.
On the left of the formula bar, in the Name box, type MyRegions and then press RETURN .
The name 'MyRegions' is assigned to the cells C2 through C5.
Select cell C9, and then type Average sales for my regions.
Select cell C10, type =AVERAGE(MyRegions), and then press RETURN .
The result is 2661.5.
Select the cell that contains 2661.5.
On the Home tab, in the Number group, click the Number Format menu , and then click Currency.
The result is $2,661.50, which is the average actual sales for the East, West, South, and North regions.
Tips:
To review and manage the names that you have assigned, on the Insert menu, point to Name, and then click Define.
You can create a list of all the names that are assigned to cells in a workbook. Locate an area with two empty columns on the sheet (the list will contain two columns — one for the name and one for the cells referenced by the name). Select the cell that will be the upper-left corner of the list. On the Insert menu, point to Name, and then click Paste. In the Paste Name dialog box, click Paste List.
Select cells C2 through C5, which are the actual sales for the East, West, South, and North regions.
On the left of the formula bar, in the Name box, type MyRegions and then press RETURN .
The name 'MyRegions' is assigned to the cells C2 through C5.
Select cell C9, and then type Average sales for my regions.
Select cell C10, type =AVERAGE(MyRegions), and then press RETURN .
The result is 2661.5.
Select the cell that contains 2661.5.
On the Home tab, under Number, click Currency .
The result is $2,661.50, which is the average actual sales for the East, West, South, and North regions.
Tips:
To review and manage the names that you have assigned, on the Insert menu, point to Name, and then click Define.
You can create a list of all the names that are assigned to cells in a workbook. Locate an area with two empty columns on the sheet (the list will contain two columns — one for the name and one for the cells referenced by the name). Select the cell that will be the upper-left corner of the list. On the Insert menu, point to Name, and then click Paste. In the Paste Name dialog box, click Paste List.
Guideline | Description |
---|---|
Allowed characters | The first character of a name must be a letter or an underscore character (_). Remaining characters in the name can be letters, numbers, periods, and underscore characters. In some languages, Excel may replace certain characters with underscores. |
Syntax | Spaces are not allowed. Underscore characters and periods can be used as word separators — for example, Sales_Tax or First.Quarter. |
Cell reference conflicts | Names cannot be the same as a cell reference, such as Z$100, BIN9, or R1C1. |
Maximum number of characters | A name can contain up to 255 characters. If a name that is defined for a range contains more than 253 characters, you cannot select it from the Name box, but you can still use it in formulas. |
Case sensitivity | Names can contain uppercase and lowercase letters, but Excel does not differentiate between them. For example, if you have named one cell 'Sales' and then name another cell 'SALES' in the same workbook, the second name will replace the first name. |
By assigning descriptive names to cell ranges, you can keep on top of the location of important information in an Excel 2016 worksheet. When assigning range names to a cell or cell range in Excel 2016, you need to follow a few guidelines:
Range names must begin with a letter of the alphabet, not a number.
For example, instead of 01Profit, use Profit01.
Range names cannot contain spaces.
Instead of a space, use the underscore (Shift+hyphen) to tie the parts of the name together. For example, instead of Profit 01, use Profit_01.
Range names cannot correspond to cell coordinates in the worksheet.
For example, you can’t name a cell Q1 because this is a valid cell coordinate. Instead, use something like Q1_sales.
To name a cell or cell range in a worksheet, follow these steps:
Select the single cell or range of cells that you want to name.
Click the cell address for the current cell that appears in the Name Box on the far left of the Formula bar.
Excel selects the cell address in the Name Box.
Type the name for the selected cell or cell range in the Name Box.
When typing the range name, you must follow Excel’s naming conventions: Refer to the bulleted list of cell-name do’s and don’ts earlier in this section for details.
Press Enter.
To select a named cell or range in a worksheet, click the range name on the Name Box drop-down list. To open this list, click the drop-down arrow button that appears to the right of the cell address on the Formula bar.
You can also accomplish the same thing by selecting Home→Find & Select (with the binoculars icon)→ Go To or by pressing F5 or Ctrl+G to open the Go To dialog box (see the figure). Double-click the desired range name in the Go To list box (alternatively, select the name followed by OK). Excel moves the cell cursor directly to the named cell. If you select a cell range, all the cells in that range are selected as well.