# Simple Excel 97 Question **URL:** \ **Category:** Factual Questions\ **Created:** [January 26, 2003, 7:10am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005 "2003-01-26T07:10:52Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 26, 2003, 7:10am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/1 "2003-01-26T07:10:52Z")
Suppose I have two columns of numbers in a worksheet. How do I add each number of the first column to each number of the second column? For example, if I have a 2x3 matrix of numbers [A1, A2, A3, B1, B2, B3], what I would like to do is to create a new worksheet containing this: ```auto A1+B1 A1+B2 A1+B3 A2+B1 A2+B2 A2+B3 A3+B1 A3+B2 A3+B3 ``` It should be simple, and I used to be able to do it, but I now totally forgot. TIA. ---
**Author:** ![splatterpunk](https://avatars.discourse-cdn.com/v4/letter/s/3be4f8/32.png) [@splatterpunk](https://boards.straightdope.com/u/splatterpunk)\ **Post date:** [January 26, 2003, 7:56am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/2 "2003-01-26T07:56:21Z")
If I’m understanding your question, this should work. Enter the formulas in the first row exactly as follows: =$A1+$B$1 =$A1+$B$2 =$A1+$B$3 You can then select these three cells and drag them down. Of course, you’ll have to make allowances for the sheet name of the source data (e.g., Sheet1!$A1+Sheet1!$B$1), but I think you know what I mean. Good luck! ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 26, 2003, 8:56am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/3 "2003-01-26T08:56:55Z")
I am actually thinking something like a crosstab. I can do it manually for a small sheet, but what if I have several hundred numbers in each column? ::scared:: ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 26, 2003, 1:57pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/4 "2003-01-26T13:57:55Z")
Please? ---
**Author:** ![SandWriter](https://avatars.discourse-cdn.com/v4/letter/s/8491ac/32.png) [@SandWriter](https://boards.straightdope.com/u/SandWriter)\ **Post date:** [January 26, 2003, 3:17pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/5 "2003-01-26T15:17:11Z")
Well I guess it wasn’t such a simple question after all! ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 26, 2003, 3:28pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/6 "2003-01-26T15:28:31Z")
I could have sworn that there’s some kind of tool in Excel that does it, but I just couldn’t remember what it is. ☹ ---
**Author:** ![flex727](https://avatars.discourse-cdn.com/v4/letter/f/258eb7/32.png) [@flex727](https://boards.straightdope.com/u/flex727)\ **Post date:** [January 26, 2003, 6:05pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/7 "2003-01-26T18:05:58Z")
> [@](#): > > \*Originally posted by Urban Ranger \* > \*\*I am actually thinking something like a crosstab. I can do it manually for a small sheet, but what if I have several hundred numbers in each column? ::scared:: \*\* I don’t understand why you don’t like **splatterpunk** ’s answer. It’s the correct answer. It doesn’t matter how many numbers are in each column. Just grab the corner of the selection and drag them down. It’s easy and quick. ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 27, 2003, 4:04am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/8 "2003-01-27T04:04:07Z")
It works well for my hypothetical example. It doesn’t work so well if I have tens let alone hundreds of numbers in each column. ---
**Author:** ![Erika](https://avatars.discourse-cdn.com/v4/letter/e/47e85d/32.png) [@Erika](https://boards.straightdope.com/u/Erika)\ **Post date:** [January 27, 2003, 4:18am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/9 "2003-01-27T04:18:11Z")
Type =A1+B1 in the upper left cell. then copy and paste over the entire block. both the A number and the B number will change with reference to the pasted cell, and you _should_ be okay. ---
**Author:** ![splatterpunk](https://avatars.discourse-cdn.com/v4/letter/s/3be4f8/32.png) [@splatterpunk](https://boards.straightdope.com/u/splatterpunk)\ **Post date:** [January 27, 2003, 4:34am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/10 "2003-01-27T04:34:38Z")
> [@](#): > > \*Originally posted by Erika \* > \*\*Type =A1+B1 in the upper left cell. then copy and paste over the entire block. both the A number and the B number will change with reference to the pasted cell, and you _should_ be okay. \*\* That won’t work. Using this method would give **Urban Ranger** the following: ```auto A1+B1 B1+C1 C1+D1 A2+B2 B2+C2 C2+D2 A3+B3 B3+C3 C3+D3 ``` … clearly not what he/she is looking for. **Urban Ranger** , it seems I am confused about what you are trying to do. Can you be a little more explicit? You mentioned a 2x3 matrix, so I gathered you only wanted to add 3 values to the first column. ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 27, 2003, 4:58am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/11 "2003-01-27T04:58:44Z")
Sorry. I thought I would use a simple example to illustrate my problem. Okay, as I said, I have two columns of numbers in a worksheet: A: A1, A2, A3. . .Am B: B1, B2, B3. . .Bn I would like to create a matrix C from these two columns, so that C{i,j} is the sum of A{i} + B{j}. My example is what it looks like when both A and B contain 3 numbers. ---
**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\ **Post date:** [January 27, 2003, 5:30am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/12 "2003-01-27T05:30:12Z")
Excel 2000 (not sure about 97) lets you do array/vector type stuff if you hit… you read? ctrl-shift-enter when you are ready to enter your formula. I have a feeling this is what you are after, but I’m not sure. I’ll have to play around with it to see if I can get it to do exactly what you want, but I know it can be done that way. It is a real kludge if you ask me, but there you have it. I have 97 at home so I’ll check it and see about reporting back if it works. ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 27, 2003, 6:31am UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/13 "2003-01-27T06:31:13Z")
Thanks ---
**Author:** ![mrcrow](https://avatars.discourse-cdn.com/v4/letter/m/838e76/32.png) [@mrcrow](https://boards.straightdope.com/u/mrcrow)\ **Post date:** [January 27, 2003, 1:29pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/14 "2003-01-27T13:29:31Z")
A1+B1 A1+B2 A1+B3 A2+B1 A2+B2 A2+B3 A3+B1 A3+B2 A3+B3 A1+$B$1 A1+$B$2 A1+$B$3…copy and paste down the sheet i also may not have picked up your question but this would give A1 A2 A3 A4 A5 etc + B1 and so forth ---
**Author:** ![mrcrow](https://avatars.discourse-cdn.com/v4/letter/m/838e76/32.png) [@mrcrow](https://boards.straightdope.com/u/mrcrow)\ **Post date:** [January 27, 2003, 1:33pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/15 "2003-01-27T13:33:15Z")
100 10 110 120 130 200 20 210 220 230 300 30 310 320 330 400 410 420 430 500 510 520 530 600 610 620 630 ran this off to check it out does this conform ---
**Author:** ![mrcrow](https://avatars.discourse-cdn.com/v4/letter/m/838e76/32.png) [@mrcrow](https://boards.straightdope.com/u/mrcrow)\ **Post date:** [January 27, 2003, 1:35pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/16 "2003-01-27T13:35:25Z")
sorry i will put in the spaces 100 10 110 120 130 200 20 210 220 230 300 30 310 320 330 400 410 420 430 500 510 520 530 600 610 620 630 :eek: ---
**Author:** ![Shade](https://avatars.discourse-cdn.com/v4/letter/s/2bfe46/32.png) [@Shade](https://boards.straightdope.com/u/Shade)\ **Post date:** [January 27, 2003, 2:07pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/17 "2003-01-27T14:07:02Z")
I can think of a couple of ways of doing it with formulae… The OFFSET function lets you specify coordinates directly. ROW and COLUMN give the row and column of the cell they are in. Try something like: =OFFSET(Sheet1!$A$1,COLUMN()-1,0)+OFFSET(Sheet1!$B$1,ROW()-1,0) Also, the first term in each expression is A(current row+const), so could be written =$A1, a relative reference, in the top left cell, and then dragging will give the appropriate values. Or, and this is probably the most natural, you could put osme row/column headings in and use hlookup and vlookup… I can’t be bothered to look these up in the help, but if you do, they’ll proabbly do what you want. It does sounds a bit like a crosstabby thing, but I don’t know anything about those, so I don’t know 🙂 ---
**Author:** ![Urban\_Ranger](https://avatars.discourse-cdn.com/v4/letter/u/e9c0ed/32.png) [@Urban\_Ranger](https://boards.straightdope.com/u/Urban_Ranger)\ **Post date:** [January 27, 2003, 5:58pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/18 "2003-01-27T17:58:08Z")
It would be easier if I could somehow turn a column of numbers into a row. But there’s no automatic way to do it that I could find. ---
**Author:** ![Terminus\_Est](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/terminus_est/32/3087_2.png) [@Terminus\_Est](https://boards.straightdope.com/u/Terminus_Est)\ **Post date:** [January 27, 2003, 6:13pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/19 "2003-01-27T18:13:47Z")
Paste Special | Transpose Be careful, you might run into the Excel column limit: 255, IIRC. ---
**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\ **Post date:** [January 27, 2003, 7:58pm UTC](https://boards.straightdope.com/t/simple-excel-97-question/151005/20 "2003-01-27T19:58:00Z")
I played more with the ctrl-shift-enter function, and I don’t think it will do what you want. It does Matrix Multiplication great, though. If your columns of data are less than 255, you can do what Terminus Est said: Move data from A1:Ax to A2:Ax+1 Select A1:Ax Edit -\> Cut Move Cursor to A2 Edit -\> Paste Move data from B1:Bx to B1: (??)1 Select B1:Bx Edit -\> Cut Move Cursor to B1 Edit -\> Paste Special -\> Transpose A1 should now be empty. In B2, enter this formula: =$A1+B$1 Move cursor to B2 -\> Edit -\> Copy Highlight B2 through the last cell in your matrix Edit -\> Paste If this isn’t clear, let me know. I tried this, and this does work. Another Hint: To quickly select all the occupied cells in a continuous column, move the cursor to the first cell, then press Shift, Down arrow, then End. Dragging works, but if you have really long columns it can take a while. [Next page](https://boards.straightdope.com/t/simple-excel-97-question/151005.md?page=2)