Tweet

# Excel For Noobs Tutorial: Cell Range Names References and Intersects

Just as you can name individual cells and formulas, you are also able to name cell references. Below you can see that I have a simple sales report that shows the quarterly sales for surf shops located in three different states, Florida, California and Hawaii. I am going to define names for cell ranges in this worksheet. I will give a name for each state's cell range of quarterly sales, and I will name each quarters range of cells.

To name a cell range follow the steps below:

Step 1: First we select the range of cells that we are going to name.

Step 2: Now we right-click and select Define Name... from the short-cut menu. The New Name dialog box will appear. Since we have already label the row Florida, Excel recognized that the content in the row label is most likely the name we want to give to our cell range. In our case we are going to keep this name but we could change it to something different if we wanted to.

Step 3: Now we simple click OK and we have named our cell range.

We are going to continue doing this for all cell ranges for each column and each row.

After we have named all cell ranges in our worksheet we can refer to cells by using cell names and reference operators for cell ranges.

## Cell Reference Operators for Cell Ranges

: (colon) Specifies a range of cells
, (comma) Separates formula arguments, cell references and cell range references.
(space) Specifies and intersect of cell ranges.

If I were to enter =California Qtr_2 into a cell, Excel would return the value inserted into the cell where those two ranges intersect. My result will be the value inserted in cell C3 because cell C3 is the cell that the cell range Califonia and the cell range Qtr_2 intersect. In our cell reference we separated our two cell references with a space.

If I we to enter =sum(Florida Qtr_1,California Qtr_1) into a cell, Excel would give me the sum of Florida's quarter 1 sales and California's quarter 1 sales. Notice the we used a comma to separate our two arguments. Florida Qtr_1 is cell B2 where Florida and Qtr_1 intersect and California Qtr_1 is cell B3 where California and Qtr_1 intersect. Basically our formula is finding the sum of cell B2 and cell B3.

Find the next tutorial in the links below.