Running totals are commonly used for keeping track of sales figures and monitoring the balance on a bank statement.
But how can you create one in Excel?
There are multiple methods, but let's look at two contrasting ones (as shown in the video).
@globalexcelsummit Running totals are commonly used for keeping track of sales figures and monitoring the balance on a bank statement. But how can you create one in Excel? There are multiple methods, but let's look at two contrasting ones (as shown in the video). A table called tblSales houses some sales data, and we want to return the running total of the Total (£) column. 𝗧𝗿𝗮𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗠𝗲𝘁𝗵𝗼𝗱 =𝚂𝚄𝙼($𝙱$𝟹:𝙱𝟹) The first cell in the Total (£) column is referenced inside SUM as part of an expanding range. The left part is made absolute to prevent it from shifting as the formula is copied down using the fill handle. On the other side of the colon, the ending reference is relative to ensure the row number increases incrementally. 𝗠𝗼𝗱𝗲𝗿𝗻 𝗠𝗲𝘁𝗵𝗼𝗱 =𝚂𝙲𝙰𝙽(0,𝚝𝚋𝚕𝚂𝚊𝚕𝚎𝚜[𝚃𝚘𝚝𝚊𝚕 (£)], 𝙻𝙰𝙼𝙱𝙳𝙰(𝚊,𝚋,𝚊+𝚋)) SCAN applies a custom LAMBDA to each value in an array and returns another containing the intermediate values created after scanning. 0 is the 𝚒𝚗𝚒𝚝𝚒𝚊𝚕_𝚟𝚊𝚕𝚞𝚎, whilst the Total (£) column is the 𝚊𝚛𝚛𝚊𝚢. The LAMBDA takes two parameters: accumulator (𝚊) and value (𝚋). 𝚊 starts at the 𝚒𝚗𝚒𝚝𝚒𝚊𝚕_𝚟𝚊𝚕𝚞𝚎 and iteratively totals up the figures, whilst 𝚋 contains the current one that gets added to 𝚊. 𝗥𝗲𝗺𝗮𝗿𝗸𝘀 The obvious upside of the newer method is only a single formula is required, whereas 10 are needed for the old approach. Using fewer formulas is more efficient — there's less to go wrong. However, the downside is dynamic array formulas are incompatible with tables, meaning they can only live outside. There are other table-compatible methods that use structured references, but they still require one formula per value. Will spilled formulas become compatible with tables in the future? Watch this space. #exceleration #excel #globalexcelsummit ♬ original sound - globalexcelsummit
A table called tblSales houses some sales data, and we want to return the running total of the Total (£) column.
Traditional Method
=𝚂𝚄𝙼($𝙱$𝟹:𝙱𝟹)
The first cell in the Total (£) column is referenced inside SUM as part of an expanding range.
The left part is made absolute to prevent it from shifting as the formula is copied down using the fill handle.
On the other side of the colon, the ending reference is relative to ensure the row number increases incrementally.
Modern Method
=𝚂𝙲𝙰𝙽(0,𝚝𝚋𝚕𝚂𝚊𝚕𝚎𝚜[𝚃𝚘𝚝𝚊𝚕 (£)],
𝙻𝙰𝙼𝙱𝙳𝙰(𝚊,𝚋,𝚊+𝚋))
SCAN applies a custom LAMBDA to each value in an array and returns another containing the intermediate values created after scanning.
0 is the 𝚒𝚗𝚒𝚝𝚒𝚊𝚕_𝚟𝚊𝚕𝚞𝚎, whilst the Total (£) column is the 𝚊𝚛𝚛𝚊𝚢. The LAMBDA takes two parameters: accumulator (𝚊) and value (𝚋).
𝚊 starts at the 𝚒𝚗𝚒𝚝𝚒𝚊𝚕_𝚟𝚊𝚕𝚞𝚎 and iteratively totals up the figures, whilst 𝚋 contains the current one that gets added to 𝚊.
Remarks
The obvious upside of the newer method is only a single formula is required, whereas 10 are needed for the old approach.
Using fewer formulas is more efficient — there's less to go wrong.
However, the downside is dynamic array formulas are incompatible with tables, meaning they can only live outside.
There are other table-compatible methods that use structured references, but they still require one formula per value.
Will spilled formulas become compatible with tables in the future?
Watch this space.
For more Excel tips and tricks like this, check out our Video Tutorials page.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.