1

I don't know if this is the right forum to ask this

I have the following table in excel it is formatted as a table except the total

 -----------------------------
| Item  | Description | Price |
 -----------------------------
| 1     | Descrip 1   | £1000 |
 -----------------------------
| 2     | Descrip 2   | £1000 |
 -----------------------------
| 3     | Descrip 3   | £1000 |
 -----------------------------
| 4     | Descrip 4   | £1000 |
 -----------------------------
              | Total | £4000 |
               ---------------

Say I want to add a new line, as I type it automatically adds the line being a table however it does not move cells below it down automatically as I would like the total row to go down and accommodate the new line in the table and update the total. How is this possible? E.G

 -----------------------------
| Item  | Description | Price |
 -----------------------------
| 1     | Descrip 1   | £1000 |
 -----------------------------
| 2     | Descrip 2   | £1000 |
 -----------------------------
| 3     | Descrip 3   | £1000 |
 -----------------------------
| 4     | Descrip 4   | £1000 |
 -----------------------------
| 5     | Descrip 5   | £1000 | < New line
 -----------------------------
              | Total | £5000 | < Total moves down and updates
               ---------------
Improve this question
1
  • Have you added that total yourself manually? An excel table should have the total automatically calculated and placed there (it is toggleable via the table toolbar) – Paul Aug 8 '12 at 7:10
3

Open a new worksheet.

Write down in the adjacent cells a part of your table, eg:

 -----------------------------
| Item  | Description | Price |
 -----------------------------
| 1     | Descrip 1   | £1000 |
 -----------------------------
| 2     | Descrip 2   | £1000 |
 -----------------------------
| 3     | Descrip 3   | £1000 |
 -----------------------------
| 4     | Descrip 4   | £1000 |

Select the cells and press Ctrl+L to create a list. In the dialog which appears be sure to check "My list has headers". Press Enter. Now you have a list.

With the cursor inside of your list go to Table Tools tab, Design tab and check the Total Row check-box. An automatically generated total row should appear for your list.

Now go in the cell right bellow the last cell of your list is. In the example above it should be the cell under the cell which holds the "Descrip 4" value which, if you followed my steps, should have the word "Total" in it. Here press Ctrl + (Control and Plus key - usually I use the one from the numeric keypad).

In this way you will have a new line in your list. Enter the desired values and the total will be updated automatically.

Improve this answer
5
  • Thank You! This will reduce my invoicing and estimating by half! – Aasim Azam Aug 8 '12 at 7:40
  • Nice answer, John. – CharlieRB Aug 8 '12 at 11:46
  • @john is this solution written for 2010? The list references sound like 2003. – dav Aug 8 '12 at 16:18
  • @DavidVandenbos: Yes, it is for 2010. In fact I used XL2010 when I wrote the answer. But it works also for older versions. It might differ only the placement of the check box which enables the Total row singe XL2003 has toolbars while 2007-2010 has ribbons. HTH – John Thomas Aug 9 '12 at 6:40
  • In Excel 2013 I needed to select the whole row to insert a new line using the CTRL+ PLUS. – Daniel Katz Nov 4 '15 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.