Running totals and Running balance in Excel – How to

Method 3: Running totals/balance with Excel Tables

Tables are one great thing happened to excel back in 2007 and with every new release of excel’s version this feature is getting stronger and better. One of the coolest thing about tables is that they grow as your data grows and if you have formula placed inside table then Excel handles it quite well (most of the time) and spares us the hassle of updating the formula or copying/pasting formula down the rows etc. So we can utilize tables to achieve the automation.

However, there are pitfalls in the methods we have learnt so far and when combined with tables then new old/new problem can arise.

Running totals/balance – using method 1 and 2 with tables.

Step 1: Open tab named Method 3 – Sales if you want running totals. If you like to do running balance then open tab named Method 3 – Cash. Select the whole range of data by having an active cell within data and hitting Ctrl+A shortcut. This will select Months column and also Sales column.

Step 2: Convert the data in to tables either by hitting Ctrl+T shortcut or from the ribbon above clicking Insert tab>Table button in the tables group.

Step 3: Create table dialogue box will appear asking for the range of data to be converted to tables and if data has headers. By default it selects the whole range of data. Make sure “my data has headers” option is checked and click OK. This will convert the whole data in tables that is preformatted and number of other options enabled specifically for this table alone.

Step 4: In the first cell of cumulative sales column type in the formula you wish you learnt in above methods. The excel will paste the same formula for all the rows immediately and save you the hassle of copying the formula down manually.

Problem: If you use method 1 with tables, everything will work fine and even if you include new records. But method 1 still haunts us with #REF! error and also to escape the tables’ auto calculate is a bit tricky. Method 2 worked flawlessly if we had to do things manually but when used with tables its behaviour is unpredictable as data grows with new record inputs.

In essence method 3 fails miserably! However, this is not the end.

Solution to all these problems is using tables’ own referencing system called structured references which is our Method 4