Bill Aging Reports: Part 2/5

Learn step-by-step how to update bill aging inventory reports, insert columns, copy formulas, use crosswalks, and filter data in Excel for efficient financial reporting.

March 17, 2026 By Jessica Esslinger

In this guide, we'll learn how to update and organize bill aging reports by copying formulas, managing data across multiple sheets, and filtering client information. This process helps ensure that your reports are accurate and up to date. You will also learn how to clean up data and prepare separate tabs for different review processes.

Let's get started

Okay. After inserting the four columns, I go back to the bill aging reports I previously had.

Step #2: Click on "MAI - Bill Aging Reports" — Click on "MAI - Bill Aging Reports"

I will go to 311 and open last week's bill aging inventory report.

Step #3: Double-click here — Double-click here

Step #4: Double-click on "Aging" — Double-click on "Aging"

I just need to copy and paste the formulas because it's much easier than entering them manually.

Step #5: Click on "Raw Data" — Click on "Raw Data"

I'll take columns C1 and C2 and drag them over to F1 and F2.

I will copy it.

Type "Ctrl + C"

I will paste it, not as values, just as a regular paste.

Step #11: Right-click here — Right-click here

I will also scroll over to column M, right-click, select Insert, and then go back to the 311.

Step #14: Double-click here — Double-click here

Step #16: Right-click on "M" — Right-click on "M"

We will do the same thing. Row M is where we made the insertion on the other sheet.

Step #20: Click on "Aged" — Click on "Aged"

Step #21: Click on "Aged" — Click on "Aged"

Copy it because it contains a formula, then paste it.

Type "Ctrl + C"

Type "Ctrl + V"

From here, for this specific row, you can double-click and let it go all the way down.

Step #27: Click on "Pend Group" — Click on "Pend Group"

Step #28: Double-click on "Days" — Double-click on "Days"

I like to double-click to make sure it's only selecting column L, and not any other column.

Step #29: Click on "05-09 Days # AUDIT1" — Click on "05-09 Days # AUDIT1"

Step #30: Double-click on "," — Double-click on ","

Type "Esc"

Step #32: Click on "15-23 Days" — Click on "15-23 Days"

All right. Then I will go back to the other sheet.

Step #36: Click on "Crosswalk" — Click on "Crosswalk"

I'm going to use this crosswalk.

Step #37: Click on "IMANAGEABILITY" — Click on "IMANAGEABILITY"

I usually move and copy over, but for some reason it doesn't take everything.

Step #38: Right-click on "Crosswalk" — Right-click on "Crosswalk"

Type "Ctrl + A"

I will select all, copy, return to 317, paste the crosswalk, and rename the tab Crosswalk.

Type "Ctrl + A"

Type "Ctrl + C"

Type "Ctrl + V"

Type "Ctrl + A"

Step #51: Double-click on "Client Code - Client Name" — Double-click on "Client Code - Client Name"

Step #52: Double-click on "CLEARCOVER INSURANCE" — Double-click on "CLEARCOVER INSURANCE"

Type "Crosswalk"

Next, go back to the raw tab. Move over to the cells we pasted. This one is fine because it only has the MID formula. The next one is still pulling the crosswalk from 311.

Step #54: Drag "Crosswalk" — Drag "Crosswalk"

Step #55: Click on "Accessibility" — Click on "Accessibility"

Step #56: Drag "M 10/6/2025 - 10/6/2025" — Drag "M 10/6/2025 - 10/6/2025"

Step #58: Drag "CLIENT CODE" — Drag "CLIENT CODE"

Step #59: Click on "ACGA" — Click on "ACGA"

Click here

Highlight the text inside the brackets, press delete, then press enter. Repeat: highlight the bracketed text, delete, and press enter.

Type "Enter"

Step #63: Click on "MAI PIP" — Click on "MAI PIP"

Type "Enter"

Step #66: Click on "MANAGEABILITY" — Click on "MANAGEABILITY"

Next, delete what is in the brackets and press enter.

Type "Enter"

Then I grab all three and drag them down.

Step #71: Double-click on "401875030" — Double-click on "401875030"

From here, I copy and paste, saving text only. Before pasting values, I check if any client names are missing.

Step #73: Right-click on "Raw" — Right-click on "Raw"

Step #74: Click on "Move or Copy" — Click on "Move or Copy"

Step #75: Double-click on "OK" — Double-click on "OK"

Step #76: Double-click on "Raw" — Double-click on "Raw"

Type "Text Only"

Step #78: Click on "MGY - AMMI" — Click on "MGY - AMMI"

Step #80: Click on "ADTE" — Click on "ADTE"

Step #81: Click on "Group" — Click on "Group"

By filtering it earlier, I remove the filter, go all the way down, and there it is.

Step #82: Drag "AFFILIATED FM CO / FM GLOBAL" — Drag "AFFILIATED FM CO / FM GLOBAL"

I will select all and go to NA.

Step #83: Drag "WESTERN MICHIGAN UNIVERSIT" — Drag "WESTERN MICHIGAN UNIVERSIT"

Step #84: Click on "Select All" — Click on "Select All"

Step #86: Click on "# N / A" — Click on "# N / A"

These are tests, so we just need to delete those rows.

Step #90: Right-click here — Right-click here

Then I'll press clear.

Step #92: Click on "Clear" — Click on "Clear"

Go to the client name and scroll down. Check if there are any NAs.

Step #93: Click on "The Auto Club Group" — Click on "The Auto Club Group"

Nope.

Step #95: Click on "Cancel" — Click on "Cancel"

Client system, any NAs?

Nope. This looks good.

Step #97: Double-click on "MUTUAL INSURANCE COM" — Double-click on "MUTUAL INSURANCE COM"

I will select all, copy, and paste values.

Type "Ctrl + A"

100

Type "Ctrl + C"

Step #101: Right-click on "ADTE" — Right-click on "ADTE"

Step #102: Click on "LOL123" — Click on "LOL123"

It's text only.

Step #104: Right-click on "Text Only" — Right-click on "Text Only"

From here, I will move and copy it one more time.

Step #105: Click on "AMMI" — Click on "AMMI"

Step #106: Right-click on "Text Only" — Right-click on "Text Only"

Step #107: Click on "Move or Copy" — Click on "Move or Copy"

Step #108: Click on "Create a copy" — Click on "Create a copy"

Step #109: Click on "OK" — Click on "OK"

Step #110: Double-click on "Text Only ( 2 )" — Double-click on "Text Only ( 2 )"

Call this one CEP.

111

Type "CEP"

Copy this one again and name it Bill Review.

Step #112: Right-click on "CEP" — Right-click on "CEP"

Step #113: Click on "Move or Copy ..." — Click on "Move or Copy ..."

Step #114: Double-click on "OK" — Double-click on "OK"

Step #115: Double-click on "42 MGY031320261111NT" — Double-click on "42 MGY031320261111NT"

I am going to scroll over to the column called Penn Group.

116

Type "Bill Review"

Step #117: Click on "MGY - AMMI - 26106" — Click on "MGY - AMMI - 26106"

Step #120: Click on "TO" — Click on "TO"

We will filter this by going to text filters, selecting "contains CEP," clicking OK, and then deleting all of the rows like this.

Step #122: Click on "..." — Click on "..."

123

Type "CEP"

Step #124: Click on "OK" — Click on "OK"

Step #127: Right-click on "2 / 25 / 2026- 2/25/2026" — Right-click on "2 / 25 / 2026- 2/25/2026"

Delete rows containing CEP to capture only bill review.

Step #128: Click on "Delete Row" — Click on "Delete Row"

Step #129: Click on "Clear" — Click on "Clear"

Click Clear, go to CEP, and do the same thing, except select "does not contain."

Step #130: Click on "CEP" — Click on "CEP"

Step #133: Click on "ITC" — Click on "ITC"

Penn Group filters do not contain CEP.

Step #134: Click on "Text Filters" — Click on "Text Filters"

Step #135: Click on "Does Not Contain ..." — Click on "Does Not Contain ..."

136

Type "CEP"

Step #137: Click on "OK" — Click on "OK"

Okay.

This one is a bit longer, so it will only contain CEP. This video will end in about 20 seconds, so I will stop here. I will continue in the third video, which will likely finish the series.

Step #139: Right-click on "2/17/2026 - 2/17/2026" — Right-click on "2/17/2026 - 2/17/2026"

Step #141: Click on "Clear" — Click on "Clear"

Step #143: Click on "Bill Review" — Click on "Bill Review"

I will save this and all our work. I'll see you in a minute.

Step #147: Click on "Continue" — Click on "Continue"