Excel Page 7
Welcome to Blaisdell's Little Corner of the Web


MsOffice Index 1
| MsOffice Index 1I | Microsoft Mac Office 8.0 | Microsoft PowerPoint 97 | Microsoft Office XP | Microsoft Bug of the Month | Microsoft Macro Virus from MacOffice |      Updated 11/26/05

  1. Sumproduct Multiplies Cells, Adds 'Em Up
  2. Transpose Trick Keeps Tables in Sync
  3. Renaming Ranges - A Refresher
  4. More coming:

Renaming Ranges - A Refresher

Excel offers no simple way to rename a range. Instead you must use a two-step process: copy the range with a new name, then go back and delete the original. Here's the undocumented technique we use:

  1. Use the Insert/Name command from the main menu.  Choose Define from the submenu (alternatively, press Ctrl + F3).

  2. Select the name of the existing range you want to rename.

  3. In the "Names in workbook" field, enter the new name and click the Add button.

  4. Repeat step 1.

  5. Choose the original name of the range and click the Delete button.

Changing Cell References To Range Names

While it's often easy to set up formulas using cell addresses, Excel also supports using range names within a formula. For example, if you have data in cells A1 through E1, and the formula =SUM(A1:E1) in cell F1, you could just as easily give the cell range A1:E1 a name (such as sales) and rewrite the formula as =SUM(sales).   We won't go into the advantages of range names here (there are many, first among them for us is readability). We do want to explain how to get from cell references to range names without having to do a lot of typing.   Let's assume that you've created the range name sales and entered the formula in F1 using the explicit cell addresses (A1:E1). To change the cell addresses into a range name:

  1. Move to the cell(s) containing the formula(s) you want to change.  In our example, move to cell F1.

  2. Use the Insert/Name command from the main menu, and choose Apply from the submenu.

  3. In the Apply names dialog box, select the range names you want to apply.  You can choose as many as you wish.  In our example, select the range name "sales."

  4. Click on OK.

Excel looks through the cell formulas and translates cell ranges into range names whenever it can. The net result in our example is that Excel converts the formula in cell F1 to =SUM(sales). 

If Excel can't find a match between a cell range in your original formula and any of the range names selected, it will display a warning message and leave the original formula alone.


Transpose Trick Keeps Tables in Sync
Works with Excel 97 and above

If you often create long tables that report sales by date and by agent. try puting the dates across the top (as column headings) and the agent names in rows, then fill in sales figures where the appropriate date column and agent row intersect.

Sometimes, however, you may want to see the organization reversed -- that is, columns become rows and rows become columns. It's easy to do. Just copy the cell range, then use the Edit/Paste Special command and check the Transpose button (at the bottom of the dialog box).

Excel now displays the table with agent names across the top and dates going down -- the data (such as sales for Agent Anderson in July 2004) is kept in the proper place.

If you change Anderson's sales figure for July in the original table, your transposed table displays the original figure. Thus, you have two similar tables with unsynchronized data. Uh, oh.

Here's how to create a transposed, synchronized table:

First, note the dimensions of your original table. Include the header row (containing dates) and header column (containing agent names) and all the data; our original table was in cells A1 through J30 -- that is, 10 columns by 30 rows. The transposed table will thus be 30 columns by 10 rows.

Select a cell range of that "transposed" dimension for the destination table (If you picked cells A41 to AD50 -- 30 columns, 10 rows). Now enter the formula 

=TRANSPOSE(A1:J30)

and instead of pressing Enter, press Shift + Ctrl + Enter to create an array formula.

Now we have a transposed table. Enter a new value in original table and you'll see that the corresponding cell in the new table is updated.

NOTE: There are two limitations to the transpositions we've been talking about.

  1. If you add another Agent or another Sale Date, you have to start over -- adding a column or row in the original table doesn't automatically add a column or row to the transposed table.
  2. If you make a change to the destination (transposed) table, the change isn't reflected in the original table. The "synching" goes one way only, from the original range to the transposed range. For that reason, you may want to protect the cell range of the transposed table to prevent accidental changes.

Sumproduct Multiplies Cells, Adds 'Em Up

If you need to do some quick multiplication and total the results, Sumproduct is an extremely handy function. With this simple function you can multiply and add values in one fell swoop.

For example, suppose you have a three-column table in Excel. The first column contains the name of the stock you own. The second column contains the number of shares you own, while the third column contains the current market price. The question is: how much is your portfolio worth?

You could, of course, add a fourth column that contains a formula to multiply the shares by the share price, then total this fourth column. There's a simpler way.

For purposes of this discussion, let's assume you have the following table. The values for cells A1 through C4 are shown below.

Stock  Shares  Price
MSFT    100      27
PG       50      55
SYMC     50      52

In cell D5, enter this formula:

=SUMPRODUCT(B2:B4,C2:C4)

Cell D5 now displays the value of your portfolio.

Shortcut: Cell Range Names:

Alternatively, you can assign the cell range B2 through B4 the name Shares, and assign cell range C2 through C4 the name Price.

There are two ways to do that.  Here's the slow way:

1. Select B2 through B4. 
2. Use the Insert/Name/Define command.
3. Enter Shares in the "Names in workbook" field, then click on Add.
4. Click Close.
5. Select C2 through C4.
6. Use the Insert/Name/Define command.
7. Enter Price in the "Names in workbook" field, then click on Add.
8. Click Close.

Note: At Step 4, you could modify the "Refers to" field with the values of C2 through C4 and add the Prices name.

Now here's the much more efficient way.

Excel's help file tells you to select the range you want to name, including the row or column labels. Since we don't care about row labels in our example, we only select B1 through C4 -- which picks up both column headings and all share and price data.

Use the Insert/Name command, then choose Create. When the Create Names box appears, select Top row. Click on OK and both range names are defined.

With the range names established, the portfolio-value formula can be shortened to:

=SUMPRODUCT(Shares,Price)