{"id":30931,"date":"2025-06-03T08:45:54","date_gmt":"2025-06-03T12:45:54","guid":{"rendered":"https:\/\/www.sage.com\/en-us\/blog\/?p=30931"},"modified":"2025-07-15T05:42:04","modified_gmt":"2025-07-15T09:42:04","slug":"create-accounts-receivable-ledger-excel","status":"publish","type":"post","link":"https:\/\/www.sage.com\/en-us\/blog\/create-accounts-receivable-ledger-excel\/","title":{"rendered":"How to create an accounts receivable ledger spreadsheet in Excel"},"content":{"rendered":"<header class=\"entry-header has-dark-background-color entry-header--has-illustration entry-header--has-illustration--generic\">\n\t<div class=\"container\">\n\t\t<div class=\"entry-header__row row align-center\">\n\t\t\t<div class=\"col col-lg-7 col-xlg-6 entry-header__content\">\n\t\t\t\t\t\t\t<div class=\"component component-single-header\">\n\t\t\t\t\t\t\t\t\t\t<div class=\"entry-header__misc text--subtitle text--uppercase text--small\">\n\t\t\t\t\t\t\t<a href=\"https:\/\/www.sage.com\/en-us\/blog\/category\/money-matters\/\" class=\"entry-header__link\">Money Matters<\/a>\t\t\t\t\t\t<\/div>\n\t\t\t\t\n\t\t\t\t<div class=\"entry-title-wrapper\">\n\t\t\t\t\t<h1 class=\"entry-title\">\n\t\t\t\t\t\tHow to create an accounts receivable ledger spreadsheet in Excel\t\t\t\t\t<\/h1>\n\t\t\t\t<\/div>\n\n\t\t\t\t\t\t\t\t\t<p class=\"entry-header__description\">\n\t\t\t\t\t\t\t\t\t\t\t<\/p>\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t<\/div>\n\n\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t<\/div>\n\t<div class=\"single-post-details container\">\n\t\t<div class=\"col\">\n\t\t\t<span class=\"posted-on \"><time class=\"entry-date published\" datetime=\"2025-06-03T08:45:54-04:00\">June 3, 2025<\/time><\/span><span class=\"reading-time\"> min read<\/span>\n\t\t<button\n\t\t\ttype=\"button\"\n\t\t\tclass=\"social-share-button button button--icon button--secondary js-social-share-button\"\n\t\t\tdata-share-title=\"How to create an accounts receivable ledger spreadsheet in Excel\"\n\t\t\tdata-share-url=\"https:\/\/www.sage.com\/en-us\/blog\/create-accounts-receivable-ledger-excel\/\"\n\t\t\tdata-share-text=\"Please read this interesting article\"\n\t\t>\n\t\t\t<span class=\"social-share-button__share-label\">Share<\/span>\n\t\t\t<span class=\"social-share-button__copy-label\" hidden>Copy Link<\/span>\n\t\t\t<span class=\"social-share-button__copy-tooltip\" aria-hidden=\"true\" hidden>Copied<\/span>\n\t\t<\/button>\n\n\t\t\t\t<\/div>\n\t<\/div>\n<\/header>\n\n\n\n<div class=\"wp-block-post-author has-dark-background-color alignfull\">\n\t<div class=\"container\">\n\t\t<div class=\"col\">\n\t\t\t\t\t\t\t<div class=\"co-authors\">\n\t\t\t\t\t\n\t\t<div class=\"entry-author-wrapper\">\n\t\t\t<a class=\"entry-author\" href=\"https:\/\/www.sage.com\/en-us\/blog\/author\/yassirmalik\/\">\n\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"40\" height=\"40\" src=\"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2024\/03\/Yassir-Malik.jpg\" class=\"entry-author__image\" alt=\"yassir-malik-profile-picture\" \/>\t\t\t\t<span class=\"entry-author__name\">Yassir Malik<\/span>\n\t\t\t<\/a>\n\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t<\/div>\n<\/div>\n\n\n\n\n\n<p>Excel is a widely used application that can sometimes be confusing to use. Especially when one small mistake can leave you spending valuable time troubleshooting \u201c<code>#VALUE<\/code>\u201d messages. If you\u2019re interested in a simple solution, we\u2019ve created a step-by-step way to create your own accounts receivable (AR) ledger in Excel.\u00a0<\/p>\n\n\n\n<p><strong>If you want a solution that is even better than Excel, try the free <a href=\"https:\/\/www.sage.com\/en-us\/products\/sage-ar-automation\/\">Sage AR Automation <\/a>that will show you real-time aging and other AR KPIs in minutes.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-formula-to-create-your-own-accounts-receivable-ledger-in-excel\">Formula to create your own accounts receivable ledger in Excel<\/h2>\n\n\n\n<p>Please note that the following directions are only an example of how you can set up your ledger in Excel. Every business is different, so feel free to customize your<a href=\"https:\/\/www.sage.com\/en-us\/accounting-software\/accounts-receivable\/\"> accounts receivable <\/a>spreadsheet accordingly.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-set-up-key-invoice-columns\">Step 1: Set up key invoice columns<\/h3>\n\n\n\n<p>Set up your Excel sheet to include \u201cInvoice Dates\u201d in column A, \u201cInvoice Numbers\u201d in column B, and \u201cDue Dates\u201d in column C to structure your AR spreadsheet. Then, enter your corresponding information.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-add-total-due-and-payment-columns\">Step 2: Add total due and payment columns<\/h3>\n\n\n\n<p>Add a column for \u201cTotal Amount Due\u201d in column E and add the corresponding information. Use Columns F\u2013I for customer payments. If customers are on payment plans, you can add as many or as little payment columns as needed. In Column J, add \u201cBalance Due\u201d to track outstanding amounts.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-calculate-balance-due\">Step 3: Calculate balance due<\/h3>\n\n\n\n<p>In cell J3, the first cell under the \u201cBalance Due\u201d column, add the following formula: <code>=E3-SUM(F3:I3)<\/code>. This formula automatically finds the difference between the total amount due and what the customer has paid so far.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-4-auto-populate-the-excel-formula\">Step 4: Auto-populate the Excel formula<\/h3>\n\n\n\n<p>After the number in cell J3 has been populated, use Excel\u2019s drag-and-drop feature to expand the small green square on the right side of the cell down to the very last customer account in your spreadsheet. This will auto-populate the formula for you, so you do not have to enter it in for every cell.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-5-calculate-total-outstanding-receivables\">Step 5: Calculate total outstanding receivables<\/h3>\n\n\n\n<p>Below the last balance due cell, create a cell for \u201cOutstanding Receivables\u201c. For this cell, you will enter in the following formula: <code>=SUM(J3:J11)<\/code>. This will add up all of your balances due, letting you know the total amount of receivables still owed to your business.\u00a0<\/p>\n\n\n<div class=\"single-cta\">\n\t<div class=\"single-cta__positioner\">\n\t\t<div class=\"single-cta__wrapper has-dark-background-color\">\n\t\t\t<div class=\"single-cta__content\">\n\t\t\t\t\t\t\t\t<h2 class=\"single-cta__title h3\">Subscribe to our Sage Advice Newsletter<\/h2>\n\n\t\t\t\t\t\t\t\t\t<div class=\"single-cta__description\">\n\t\t\t\t\t\t<p>Get our latest business advice delivered directly to your inbox.<\/p>\n\t\t\t\t\t<\/div>\n\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t<a\n\t\t\t\t\t\thref=\"#gate-ab515c6e-7e90-4c2f-a67e-113872516e8b\"\n\t\t\t\t\t\tclass=\"single-cta__button button button--primary\"\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t>Subscribe<\/a>\n\t\t\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t<img decoding=\"async\" width=\"1440\" height=\"810\" src=\"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/GettyImages-1073797282-1440x810.jpg\" class=\"single-cta__image\" alt=\"Working from home with tea in hand\" loading=\"lazy\" srcset=\"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/GettyImages-1073797282-1440x810.jpg 1440w\" sizes=\"auto, (min-width: 48em) 33vw, 100vw\" \/>\t\t\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to build a simple yet effective accounts receivable ledger in Excel with step-by-step instructions and formulas. Plus, discover how Sage AR Automation offers a smarter, real-time alternative for tracking your receivables and key metrics effortlessly.<\/p>\n","protected":false},"author":1774,"featured_media":9193,"menu_order":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_sage_video":false,"post_featured_image_hide":false,"footnotes":""},"categories":[43],"tags":[],"business_type":[312],"lilypad":[],"context":[],"industry":[57],"persona":[150],"imagine_tag":[],"coauthors":[509],"class_list":["post-30931","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-money-matters","business_type-accountants","industry-financial-services"],"sage_meta":{"region":"en-us","author_name":"Yassir Malik","featured_image":"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/GettyImages-643927002.jpg","imagine_tags":[]},"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Sage Advice US","distributor_original_site_url":"https:\/\/www.sage.com\/en-us\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/posts\/30931","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/users\/1774"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/comments?post=30931"}],"version-history":[{"count":0,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/posts\/30931\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/media\/9193"}],"wp:attachment":[{"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/media?parent=30931"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/categories?post=30931"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/tags?post=30931"},{"taxonomy":"business_type","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/business_type?post=30931"},{"taxonomy":"lilypad","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/lilypad?post=30931"},{"taxonomy":"context","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/context?post=30931"},{"taxonomy":"industry","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/industry?post=30931"},{"taxonomy":"persona","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/persona?post=30931"},{"taxonomy":"imagine_tag","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/imagine_tag?post=30931"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/coauthors?post=30931"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}