{"id":3453,"date":"2018-04-20T08:10:33","date_gmt":"2018-04-20T14:10:33","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3453"},"modified":"2021-11-01T10:04:10","modified_gmt":"2021-11-01T16:04:10","slug":"create-a-running-balance-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/create-a-running-balance-in-excel.html","title":{"rendered":"Create a Running Balance in Excel that Allows you to Insert, Delete, and Move Rows"},"content":{"rendered":"<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/create-a-running-balance-in-excel-thumbnail.png\" alt=\"Use DATEDIF to Calculate Age in Excel\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/create-a-running-balance-in-excel-thumbnail.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>One of the most important tasks in personal finance is to keep a running balance of your checking and credit accounts, and spreadsheets are a common tool for doing that. In fact, tracking accounts is one of the original uses for the spreadsheet dating as far back as when the ancients etched ledgers on clay tablets.<\/p>\n<p>The ancients may have occasionally made math errors when they recorded a running total or a running balance. We may think our modern spreadsheets are far superior because we let Excel formulas do the math for us, but we can run into other types of errors instead. The ability that Excel gives us to insert rows, delete rows, and move rows via cut and paste, can introduce hard-to-detect errors.<\/p>\n<p>In this article, I'll explain the problems with the basic running balance formula and provide two robust solutions.<\/p>\n<p>To see the examples in action, download the Excel file below.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/RunningBalance.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'RunningBalance.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (RunningBalance.xlsx)<\/p>\n<div class=\"contents\">\n<p>This Article (bookmarks):<\/p>\n<ul>\n<li><a href=\"#problems\">Problems with the Basic Running Balance<\/a><\/li>\n<li><a href=\"#offset\">Solution #1: Use the OFFSET Function<\/a><\/li>\n<li><a href=\"#relative-named-range\">Solution #2: Use a Relative Named Range<\/a><\/li>\n<li><a href=\"#cellabove\">Bonus Trick: Create a range named cell_above that works everywhere in the workbook<\/a><\/li>\n<\/ul>\n<\/div>\n<h2 id=\"problems\">Problems with the Basic Running Balance<\/h2>\n<p>Consider the following very simple example showing deposits and withdrawals and a running balance. The basic running balance would be a formula that adds deposits and subtracts withdrawals from the previous balance using a formula like this: =<b>SUM<\/b>(D15,-E15,F14).<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/basic-running-balance-in-excel.png\" alt=\"Basic Running Balance in Excel\" width=\"375\" height=\"147\" class=\"imgshadow\"><\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> <b>Why use SUM instead of =D15-E15+F14?<\/b> Answer: The formula in the first row would lead to a #VALUE! error because of trying to add a text value (=5-1+\"Balance\"). The SUM function ignores text values. Another approach is to leave a blank row underneath the columns labels, or use the first row to enter the carry over balance instead of using a formula.<\/p>\n<\/div>\n<h3>Problem #1: <b>Deleting a Row<\/b> Causes a #REF! Error<\/h3>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/running-balance-deleted-row-ref-error.png\" alt=\"Running Balance Error Due to Deleting Row\" width=\"570\" height=\"117\" class=\"imgshadow\"><\/p>\n<p>In this example, row 2 was deleted. The balance formula in row 3 now contains a <b>#REF!<\/b> error because the reference to the balance in row 2 was deleted. All dependent formulas also contain #REF! errors.<\/p>\n<p>This error is very obvious, and can be fixed fairly easily by fixing the balance formula in row 3. While annoying to fix, it's not as scary as the errors that are harder to detect.<\/p>\n<h3>Problem #2: <b>Inserting a Row<\/b> Causes an Incorrect Running Balance<\/h3>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/running-balance-error-when-inserting.png\" alt=\"Running Balance Error Due to Inserting a Row\" width=\"590\" height=\"165\" class=\"imgshadow\"><\/p>\n<p>In this example, a new row was inserted above row 3 and the balance formula was then copied down. The problem is that the balance formula in row 3 is still referencing the balance from row 2.<\/p>\n<p>The error checking feature in Excel was designed to help identify these types of errors. In this case, a little green triangle indicates an inconsistent formula. It isn't wise to rely on the error checking feature because it doesn't catch all errors.<\/p>\n<h3>Problem #3: <b>Moving a Row<\/b> Causes Hard-to-detect Errors<\/h3>\n<p>In this example, row 3 was cut and inserted above row 2. <b>Are there any errors?<\/b><\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/running-balance-errors-after-moving-a-row.png\" alt=\"Running Balance Error After Moving a Row\" width=\"356\" height=\"136\" class=\"imgshadow\"><\/p>\n<p>The scary thing about this example is that there is no obvious indicator of the errors. In fact, the running balance is wrong in both rows 2 and 3, because the formulas are not referencing the correct previous balance.<\/p>\n<p>If I have an error in a spreadsheet, I would much rather see a glaring #VALUE! or #REF! error than have errors I can't see.<\/p>\n<h3>Design for Robustness to Common Usage<\/h3>\n<p>If you are designing a spreadsheet with a running balance, it's likely that you or somebody else may want to insert, delete, or move rows. So, you need to use formulas that don't cause hidden or hard-to-detect errors.<\/p>\n<p>There are two fairly simple solutions for creating a robust running balance that don't break when you insert, delete or move rows.<\/p>\n<h2 id=\"offset\"><b>Solution #1<\/b>: Create a Running Balance using the <b>OFFSET<\/b> Function<\/h2>\n<p>The OFFSET function allows you to create a reference by specifying the number of rows and columns offset from a particular reference.<\/p>\n<pre class=\"XLformula\">Syntax: =<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">reference<\/span>,<span class=\"arg\">rows<\/span>,<span class=\"arg\">columns<\/span>,[<span class=\"arg\">height<\/span>],[<span class=\"arg\">width<\/span>])<\/pre>\n<p>To refer to the previous balance, we can use the current balance (F15) as the <i>reference<\/i> and use -1 for the <i>offset rows<\/i> and 0 for the <i>offset columns<\/i> like this: <b>=OFFSET(F15,-1,0)<\/b>. Nice and simple.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/running-balance-in-excel-using-offset-function.png\" alt=\"Running Balance in Excel using the OFFSET Function\" width=\"477\" height=\"186\" class=\"imgshadow\"><\/p>\n<p>The OFFSET function does not directly reference cell F14, so if you delete row 14, no problem. You can insert, delete and move rows and the balance formula will always reference the cell above the current balance.<\/p>\n<p>To see the OFFSET function used within functioning templates, take a look at the <a href=\"\/ExcelTemplates\/excel-checkbook.html\">Checkbook Register<\/a> and <a href=\"\/ExcelTemplates\/credit-account-register.html\">Credit Account Register<\/a>, both of which include a running balance.<\/p>\n<h3>Pros:<\/h3>\n<p><i>OFFSET is compatible with Google Sheets and OpenOffice.<\/i> The need for compatibility is one of the main reasons I use OFFSET in many of my templates. However, if you only use Excel, you might try Solution #2.<\/p>\n<h3>Cons:<\/h3>\n<p><i>OFFSET can make formulas difficult to understand.<\/i> Notice that in the image above, the OFFSET formula highlights the <i>reference<\/i> (cell F15) rather than the cell that OFFSET refers to (cell F14).<\/p>\n<p><i>OFFSET is a <a href=\"\/blog\/excel-formulas\/volatile-functions.html\">volatile function<\/a><\/i>, which means it re-evaluates every time the worksheet is recalculated. This particular use is very efficient, so it likely won't cause a problem unless you have a lot of other inefficient formulas that depend on the results of the OFFSET formula.<\/p>\n<h2 id=\"relative-named-range\"><b>Solution #2<\/b> - Create a running balance using a <b>Relative Named Range<\/b><\/h2>\n<p>When you create a named range via Formulas &gt; Define Name, you can use a relative reference like =F14 (no dollar signs) instead of an absolute reference like =$F$14. In this example, I've created <i>prev_balance<\/i> as a named range that refers to the cell immediately above the current balance.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/running-balance-using-a-relative-named-range.png\" alt=\"Running Balance in Excel using a Relative Named Range\" width=\"475\" height=\"186\" class=\"imgshadow\"><\/p>\n<p>The named range was created using a relative reference, so when the running balance formula is copied down, <i>prev_balance<\/i> will always refer to the cell immediately above the current cell, even if you insert, delete, or move the rows.<\/p>\n<p>Not familiar with named ranges? Check out the references listed at the end of this article to learn more.<\/p>\n<h3>Pros:<\/h3>\n<p><i>Named ranges can make formulas easier to understand.<\/i> Notice that unlike OFFSET, Excel highlights cell F14 (the cell that <i>prev_balance<\/i> is referring to).<\/p>\n<h3>Cons:<\/h3>\n<p><i>Most Excel users do not know how to use or create named ranges.<\/i> Even advanced users may not know about relative named ranges. So, even though the formula may be easy to read, most people are not going to know why or how the formula works.<\/p>\n<p><i>Named ranges have their own set of complexities and problems<\/i>, especially when it comes to duplicating or moving worksheets.<\/p>\n<p><i>Relative named ranges are not compatible with Google Sheets.<\/i> Currently, Google Sheets treats all named ranges as absolute references (even though the references don't show dollar signs).<\/p>\n<h3>How to Create the Relative Named Range<\/h3>\n<p>This can be tricky. The most important step is the very first one: selecting the correct cell prior to defining the name.<\/p>\n<ol>\n<li>Select cell A2 or B2 or F2 or ZZ2 (any cell in row 2)<\/li>\n<li>Go to Formulas &gt; Define Name<\/li>\n<li>Enter <i>prev_balance<\/i> in the Name field<\/li>\n<li>Set the Scope to the current worksheet<\/li>\n<li>In the Refers To field, enter <b>=$F1<\/b> (no dollar sign before the 1)<\/li>\n<\/ol>\n<p>In our example, the Balance is in column F, so we want <i>prev_balance<\/i> to always refer to column F. That is why we place a dollar sign in front of the F. It is also why it doesn't matter what cell in row 2 we choose in step 1.<\/p>\n<h3>Take it a Step Further: Create relative named ranges for <i>deposit<\/i> and <i>withdrawal<\/i>.<\/h3>\n<p>Although not necessary for making a robust running balance, you might be interested in making your formula even more readable, as shown in the image below.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/readable-formula-using-relative-named-ranges.png\" alt=\"Example of a Readable Formula using Relative Named Ranges\" width=\"459\" height=\"186\" class=\"imgshadow\"><\/p>\n<p>The relative named ranges for <i>deposit<\/i> and <i>withdrawal<\/i> refer to columns D and E in the <b>current<\/b> row. To create these named ranges, follow similar steps as above, except enter <b>=$D2<\/b> and <b>=$E2<\/b> in the Refers to field for <i>deposit<\/i> and <i>withdrawal<\/i>.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> If readable formulas is the goal, you might consider using <a href=\"https:\/\/support.office.com\/en-us\/article\/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e\" target=\"_blank\" rel=\"noopener noreferrer\">Excel Tables with structured references.<\/a><\/p>\n<\/div>\n<h2 id=\"cellabove\"><b>Bonus Trick<\/b>: Create a range named <i>cell_above<\/i> that works everywhere in the workbook<\/h2>\n<p>For the sake of being tricky, you could create a range named <i>cell_above<\/i> that always returns the cell in the previous row, anywhere you use it in the workbook.<\/p>\n<ol>\n<li>Select cell A2<\/li>\n<li>Go to Formulas &gt; Define Name<\/li>\n<li>Enter <i>cell_above<\/i> in the Name field<\/li>\n<li>Set the Scope to Workbook<\/li>\n<li>In the Refers To field, enter <b>=!A1<\/b> (no dollar signs, don't forget the exclamation mark)<\/li>\n<\/ol>\n<p>A discussion of Scope is beyond the scope of this article, but the articles listed in the references below cover the topic well. Including the exclamation mark without the name of the worksheet is a trick I learned from Mynda Treacy that makes the relative named range work throughout the workbook.<\/p>\n<h3>Questions and Comments<\/h3>\n<p>If you have questions about how to apply these techniques or know of other ways they can be applied, feel free to comment below.<\/p>\n<p>An example of another application would be to use OFFSET in the # column for sequential numbering that allows rows to be deleted without #REF! errors, like this: =1+OFFSET(<em>current_cell<\/em>,-1,0).<\/p>\n<h3>References<\/h3>\n<div class=\"refblock\">\n<ul>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/Define-and-use-names-in-formulas-4D0F13AC-53B7-422E-AFD2-ABD7FF379C64\" target=\"_blank\" rel=\"noopener noreferrer\">Define and use names in formulas<\/a> - <i>support.microsoft.com<\/i> - A good general overview of how to create and use named ranges.<\/li>\n<li><a href=\"https:\/\/www.myonlinetraininghub.com\/relative-named-ranges\" target=\"_blank\" rel=\"noopener noreferrer\">Relative Named Ranges<\/a> - <i>myonlinetraininghub.com<\/i> - This is an excellent article about relative named ranges, including some very advanced uses.<\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>A basic running balance formula can lead to hard-to-detect errors. Learn two ways to create a running balance in Excel that doesn&#8217;t break when you delete a row, insert a row, or move rows via cut and paste.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3453","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3453"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3453\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}