{"id":30773,"date":"2025-06-03T08:34:30","date_gmt":"2025-06-03T12:34:30","guid":{"rendered":"https:\/\/www.sage.com\/en-us\/blog\/?p=30773"},"modified":"2025-06-03T08:34:32","modified_gmt":"2025-06-03T12:34:32","slug":"create-aging-report-excel","status":"publish","type":"post","link":"https:\/\/www.sage.com\/en-us\/blog\/create-aging-report-excel\/","title":{"rendered":"How to create an aging report &#038; formulas 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 aging report &#038; formulas 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:34:30-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 aging report &#038; formulas in Excel\"\n\t\t\tdata-share-url=\"https:\/\/www.sage.com\/en-us\/blog\/create-aging-report-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<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\/elizabethryan\/\">\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\/08\/lizryan-350x350.png\" class=\"entry-author__image\" alt=\"Elizabeth Ryan\" \/>\t\t\t\t<span class=\"entry-author__name\">Elizabeth Ryan<\/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<p>Excel can be tricky at times, and it may even feel like you\u2019re back in high school algebra, dealing with long strings of parentheses and if-then statements. This guide breaks down the simple steps for creating an aging report in Excel. It includes all the formulas you\u2019ll need to figure out who\u2019s the most overdue and how much you\u2019re owed in receivables.<\/p>\n\n\n\n<p>Want to level up your operations with something even better than Excel? Try <a href=\"https:\/\/www.sage.com\/en-us\/accounting-software\/accounts-receivable\/\">Sage Accounts Receivable Software<\/a> and get real-time aging and other AR KPIs in minutes.<\/p>\n\n\n<?xml encoding=\"utf-8\" ?><div class=\"wp-block-yoast-seo-table-of-contents yoast-table-of-contents\"><ul><li><a href=\"#h-aging-report-cheat-sheet-a-step-by-step-guide\" data-level=\"2\">Aging report cheat sheet: A step-by-step guide<\/a><ul><li><a href=\"#h-step-1-label-your-columns\" data-level=\"3\">Step 1: Label your columns<\/a><\/li><li><a href=\"#h-step-2-add-additional-headers\" data-level=\"3\">Step 2: Add additional headers<\/a><\/li><li><a href=\"#h-step-3-calculate-days-outstanding\" data-level=\"3\">Step 3: Calculate &ldquo;Days outstanding&rdquo;<\/a><\/li><li><a href=\"#h-step-4-copy-the-formula-down\" data-level=\"3\">Step 4: Copy the formula down<\/a><\/li><li><a href=\"#h-step-5-add-conditional-formatting\" data-level=\"3\">Step 5: Add conditional formatting<\/a><\/li><li><a href=\"#h-step-6-set-up-the-color-scale\" data-level=\"3\">Step 6: Set up the color scale<\/a><\/li><li><a href=\"#h-step-7-calculate-not-due\" data-level=\"3\">Step 7: Calculate &ldquo;Not due&rdquo;<\/a><\/li><li><a href=\"#h-step-8-calculate-0-30-days\" data-level=\"3\">Step 8: Calculate &ldquo;0-30 days&rdquo;<\/a><\/li><li><a href=\"#h-step-9-calculate-31-60-days\" data-level=\"3\">Step 9: Calculate &ldquo;31-60 days&rdquo;<\/a><\/li><li><a href=\"#h-step-10-calculate-61-90-days\" data-level=\"3\">Step 10: Calculate &ldquo;61-90 days&rdquo;<\/a><\/li><li><a href=\"#h-step-11-calculate-gt-90-days\" data-level=\"3\">Step 11: Calculate &ldquo;&gt;90 days&rdquo;<\/a><\/li><li><a href=\"#h-step-12-sum-up-the-values\" data-level=\"3\">Step 12: Sum up the values<\/a><\/li><\/ul><\/li><\/ul><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-aging-report-cheat-sheet-a-step-by-step-guide\"><strong>Aging report cheat sheet: A step-by-step guide<\/strong><\/h2>\n\n\n\n<p>Follow these simple steps to set up and calculate your receivables:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-1-label-your-columns\"><strong>Step 1: Label your columns<\/strong><\/h3>\n\n\n\n<p>Start by labeling the following cells:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A1: customer<\/li>\n\n\n\n<li>B1: order #<\/li>\n\n\n\n<li>C1: date<\/li>\n\n\n\n<li>D1: amount due<\/li>\n<\/ul>\n\n\n\n<p>Then, enter the corresponding information for your customers and their orders beneath these headers.&nbsp;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-2-add-additional-headers\"><strong>Step 2: Add additional headers<\/strong><\/h3>\n\n\n\n<p>Next, add these additional headers:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>E1: Days outstanding<\/li>\n\n\n\n<li>F1: Not due<\/li>\n\n\n\n<li>G1: 0-30 days<\/li>\n\n\n\n<li>H1: 31-60 days<\/li>\n\n\n\n<li>I1: 61-90 days<\/li>\n\n\n\n<li>J1: &gt;90 days<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-3-calculate-days-outstanding\"><strong>Step 3: Calculate \u201cDays outstanding\u201d<\/strong><\/h3>\n\n\n\n<p>Now, you\u2019ll need to input a formula for the \u201cdays outstanding\u201d column to track the number of days each invoice has been unpaid since the due date.\u00a0<\/p>\n\n\n\n<p>In <strong>cell E2<\/strong>, enter the following formula:<\/p>\n\n\n\n<p>= IF(TODAY()C2,TODAY()-C2,0)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-4-copy-the-formula-down\"><strong>Step 4: Copy the formula down<\/strong><\/h3>\n\n\n\n<p>To apply this formula to the entire column, click the fill handle (the small square in the bottom-right corner of cell E2) and drag it down to the last customer. This step will spread the formula across the entire column.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-5-add-conditional-formatting\"><strong>Step 5: Add conditional formatting<\/strong><\/h3>\n\n\n\n<p>You need to add some color to your aging report to make it easier to read. This step will help you quickly spot overdue invoices.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Highlight all the rows in column E.<\/li>\n\n\n\n<li>Go to the \u201cHome\u201d tab, click \u201cConditional Formatting,\u201d and select \u201cNew Rule.\u201d<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-6-set-up-the-color-scale\"><strong>Step 6: Set up the color scale<\/strong><\/h3>\n\n\n\n<p>A separate window will open titled \u201cNew Formatting Rule.\u201d<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>In the \u201cFormat Style\u201d dropdown, select the 3-Color Scale.<\/li>\n\n\n\n<li>In the \u201cType\u201d dropdown, select \u201cNumber.\u201d<\/li>\n\n\n\n<li>For \u201cValues\u201d, enter:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>0 for the minimum,<\/li>\n\n\n\n<li>60 for the midpoint,<\/li>\n\n\n\n<li>90 for the maximum.<\/li>\n<\/ul>\n\n\n\n<p>Finally, choose three easy-to-distinguish colors. These will represent different aging categories (for example, green for not due, yellow for 0-30 days, and red for &gt;90 days).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-7-calculate-not-due\"><strong>Step 7: Calculate \u201cNot due\u201d<\/strong><\/h3>\n\n\n\n<p>In <strong>cell F2<\/strong>, use the following formula to find out which invoices aren\u2019t due yet:<\/p>\n\n\n\n<p>= IF(E2=0,D2,0)<\/p>\n\n\n\n<p>Drag the fill handle down to apply this formula to the whole column.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-8-calculate-0-30-days\"><strong>Step 8: Calculate \u201c0-30 days\u201d<\/strong><\/h3>\n\n\n\n<p>This formula checks if the difference between today\u2019s date and the due date is 30 days or less.<\/p>\n\n\n\n<p>In <strong>cell G2<\/strong>, enter the following formula:<\/p>\n\n\n\n<p>= IF(C2&lt;TODAY(),(IF(TODAY()-C2&lt;=30,D2,0)),0)<\/p>\n\n\n\n<p>Again, drag the fill handle down to fill the rest of the column.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-9-calculate-31-60-days\"><strong>Step 9: Calculate \u201c31-60 days\u201d<\/strong><\/h3>\n\n\n\n<p>This formula uses an AND statement to check if the difference is between 31 and 60 days.<\/p>\n\n\n\n<p>In <strong>cell H2<\/strong>, enter:<\/p>\n\n\n\n<p>= IF(AND(TODAY()-$C2&lt;=60,TODAY()-$C2&gt;30),$D2,0)<\/p>\n\n\n\n<p>Then, drag the fill handle down to apply the formula.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-10-calculate-61-90-days\"><strong>Step 10: Calculate \u201c61-90 days\u201d<\/strong><\/h3>\n\n\n\n<p>This formula is similar to the one in step 9, just for the 61-90 day range.&nbsp;<\/p>\n\n\n\n<p>In <strong>cell I2<\/strong>, enter:<\/p>\n\n\n\n<p>= IF(AND(TODAY()-$C2&lt;=90,TODAY()-$C2&gt;60),$D2,0)<\/p>\n\n\n\n<p>Once again, drag the fill handle down to fill the column.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-11-calculate-gt-90-days\"><strong>Step 11: Calculate \u201c&gt;90 days\u201d<\/strong><\/h3>\n\n\n\n<p>For invoices that are more than 90 days overdue, this formula checks if the difference is greater than 90 days.<\/p>\n\n\n\n<p>In <strong>cell J2<\/strong>, enter:<\/p>\n\n\n\n<p>= IF(TODAY()-$C2&gt;90,D2,0)<\/p>\n\n\n\n<p>Drag the fill handle down to apply this formula to the column.<\/p>\n\n\n\n<div class=\"single-cta gated-content\">\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\">8 Signs you have outgrown Excel for budgeting and planning<\/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><!-- wp:paragraph --><\/p>\n<p class=\"\">E-book<\/p>\n<p><!-- \/wp:paragraph --><\/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-0d43b105-8e3b-4d76-813a-e4e72d5f299e\"\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>Download now<\/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\/2023\/06\/shutterstock_2081844814_c2222-1-1440x810.jpg\" class=\"single-cta__image\" alt=\"people in office\" loading=\"lazy\" srcset=\"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2023\/06\/shutterstock_2081844814_c2222-1-1440x810.jpg 1440w\" sizes=\"auto, (min-width: 48em) 33vw, 100vw\" \/>\t\t\t<\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-step-12-sum-up-the-values\"><strong>Step 12: Sum up the values<\/strong><\/h3>\n\n\n\n<p>To calculate the total amount for each column, select the first empty cell beneath the \u201cNot Due\u201d column, then press ALT+ = to sum the column. Repeat this for the \u201c&gt;90\u201d column to get the total amounts for each age group.<\/p>\n\n\n\n<p>Did you know you can automate this activity? With Sage Accounts Receivable Software, your aging reports won\u2019t be limited to static Excel files. Take the product tour, discover how to automate real-time data, and effortlessly generate reports.<\/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>Creating aging reports is essential for managing your business accounting. Learn the steps to master the process and keep track of overdue invoices with ease.<\/p>\n","protected":false},"author":1806,"featured_media":20124,"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":[],"lilypad":[],"context":[],"industry":[],"persona":[],"imagine_tag":[],"coauthors":[584],"class_list":["post-30773","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-money-matters"],"sage_meta":{"region":"en-us","author_name":"Elizabeth Ryan","featured_image":"https:\/\/www.sage.com\/en-us\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/GettyImages-1385092066.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\/30773","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\/1806"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/comments?post=30773"}],"version-history":[{"count":0,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/posts\/30773\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/media\/20124"}],"wp:attachment":[{"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/media?parent=30773"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/categories?post=30773"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/tags?post=30773"},{"taxonomy":"business_type","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/business_type?post=30773"},{"taxonomy":"lilypad","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/lilypad?post=30773"},{"taxonomy":"context","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/context?post=30773"},{"taxonomy":"industry","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/industry?post=30773"},{"taxonomy":"persona","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/persona?post=30773"},{"taxonomy":"imagine_tag","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/imagine_tag?post=30773"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.sage.com\/en-us\/blog\/api\/wp\/v2\/coauthors?post=30773"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}