How to Build an Issue Log in Excel With Status and Aging
By ExcelReady TeamUpdated 6 min read
The short answer
Give each issue one row with an ID, owner, priority, status and the date it was opened. Add an Age column with =IF(D2="Closed",F2-E2,TODAY()-E2) to show how many days it has been open. Summarise the log with COUNTIFS, such as open High issues, and use conditional formatting to turn old open issues red.
Key takeaways
- One row per issue, with ID, Owner, Priority, Status, Opened and Closed columns.
- Age in days is TODAY() minus the opened date for open issues, or Closed minus Opened for closed ones.
- A drop-down list for Status keeps the values consistent, so COUNTIFS counts them correctly.
- COUNTIFS answers questions like how many High issues are still open.
- A formula rule in conditional formatting highlights open issues older than a set number of days.
On this page+
An issue log in Excel is a table with one row per problem, showing who owns it, how urgent it is, its status and how long it has been open. Add an aging formula, a few COUNTIFS summaries and a red highlight for old open issues, and you can run a weekly review straight from the sheet. This guide is for project coordinators, support teams and anyone asked to "keep track of the issues" without a dedicated tool.
What columns does an issue log need?#
Seven columns cover most issue logs. Each column holds one kind of fact, which keeps filters and formulas simple:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Owner | Priority | Status | Opened | Closed | Age |
| 2 | I-101 | Asha | High | Open | 05/10/2026 | 29 | |
| 3 | I-102 | Ben | Low | Closed | 08/10/2026 | 15/10/2026 | 7 |
| 4 | I-103 | Asha | Medium | In progress | 20/10/2026 | 14 | |
| 5 | I-104 | Chen | High | Open | 28/10/2026 | 6 | |
| 6 | I-105 | Ben | High | Closed | 12/10/2026 | 19/10/2026 | 7 |
| 7 | I-106 | Chen | Low | Open | 30/09/2026 | 34 |
Dates are day/month/year, and the ages are worked out on 3 November 2026. Add a Description and a Next action column on the right for the details. Keep IDs unique and never reuse one, so people can refer to "I-104" in emails without confusion.
Turn the range into a table with Ctrl+T (Mac: ⌘T). New issues added at the bottom then pick up the formulas and formatting automatically.
How to keep the status values consistent#
A drop-down list stops "open", "Open " and "OPEN?" from creeping in, which would break every count. Select the Status cells, go to Data > Data Validation, choose List, and type:
Open,In progress,ClosedDo the same for Priority with High,Medium,Low. Now each cell offers only those choices, and COUNTIFS can count them reliably. The task tracker guide uses the same drop-down idea for task status.
How to calculate issue aging in days#
Issue aging is the number of days an issue has been open. Dates in Excel are numbers, so you subtract one from another. In G2 type:
=IF(D2="Closed",F2-E2,TODAY()-E2)Fill it down. For a closed issue, the age is the closed date minus the opened date: I-102 took 7 days. For an open issue, it is today minus the opened date. On 3 November 2026, I-101 is 29 days old and I-106 is 34.
TODAY() returns the current date and updates every time the file recalculates. The TODAY function guide covers it in detail. If the result shows as a date, change the column to General or Number format.
For a report that must not change after you send it, put the report date in one cell, such as H1, and use $H$1 instead of TODAY(). The guide to counting days until a deadline uses the same date subtraction.
How to group issues into aging buckets#
Buckets make a long list easier to read in a review. Add a column with:
=IF(G2>30,"Over 30 days",IF(G2>14,"15-30 days",IF(G2>7,"8-14 days","0-7 days")))I-106 (34 days) lands in "Over 30 days", I-101 (29) in "15-30 days", and I-104 (6) in "0-7 days". The same banding is used for unpaid invoices; the aging report guide shows the finance version.
How to summarise the issue log with COUNTIFS#
COUNTIFS counts the rows that meet every condition you give it, so it answers most status questions in one formula. With the report date in H1:
| Question | Formula | Result |
|---|---|---|
| Issues not closed | =COUNTIFS(D2:D7,"<>Closed") | 4 |
| High priority and not closed | =COUNTIFS(D2:D7,"<>Closed",C2:C7,"High") | 2 |
| Asha's issues not closed | =COUNTIFS(B2:B7,"Asha",D2:D7,"<>Closed") | 2 |
| Not closed and older than 14 days | =COUNTIFS(D2:D7,"<>Closed",E2:E7,"<"&H1-14) | 2 |
"<>Closed" means "not equal to Closed", so it counts both Open and In progress. In the last formula, H1-14 is 20 October 2026, and "<"& joins the operator to that date. Only I-101 and I-106 were opened before then and are still open. I-103 was opened exactly 14 days earlier, so it doesn't count.
The COUNTIFS function guide explains the range and criteria pairs, and COUNTIFS and SUMIFS with multiple conditions has more examples.
How to highlight old open issues#
Conditional formatting can colour a whole row when an issue is open too long. Select A2:G7, then:
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter the formula below, pick a red fill and click OK.
=AND($D2<>"Closed",$G2>14)The $ before D and G keeps the rule looking at the Status and Age columns for every cell in the row, while the row number changes. I-101 and I-106 turn red. Add a second rule with =$D2="Closed" and a grey font to fade finished issues. The guide to highlighting overdue dates covers more date-based rules.
Tips for running a weekly issue review#
A log is only useful if it stays current. A few habits help:
- Sort by Status, then Age, so the oldest open issues sit at the top (Data > Sort).
- Filter by Owner before each person's update (Data > Filter, or Ctrl+Shift+L on Windows and ⌘+Shift+F on a Mac).
- Fill in the Closed date the day an issue is closed; otherwise the age keeps growing.
- Freeze the header row with View > Freeze Panes > Freeze Top Row.
- Move closed issues to an archive sheet once a month, keeping their IDs, so the main log stays short and the counts stay fast to read.
- Agree what each priority means, for example that High blocks work today, so owners rate issues the same way.
Practise it#
An issue log combines three skills job tests love: COUNTIFS, date maths and conditional formatting. Practise COUNTIFS and SUMIFS in a live spreadsheet, practise days between dates, and practise conditional formatting rules: you type the formula and it checks it instantly. Or try the exercise on this page and count the issues left open too long.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
How many issues are not Closed and were opened more than 14 days before the report date in H1? In H2, use COUNTIFS on the Status column (D2:D7) and the Opened column (E2:E7), then press Enter.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Opened | ||||||||
| 2 | Old open issues | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 |
Tip: Use "<>Closed" for the status, and "<"&H1-14 for dates more than 14 days before the report date.
Practise this in a lesson
Frequently asked questions
What should an issue log include?
At minimum: an issue ID, a short description, the owner, the priority, the status, the date opened and the date closed. Many teams also add a next action and a due date.
How do I calculate issue aging in Excel?
Subtract the opened date from today for open issues and from the closed date for closed ones: =IF(D2="Closed",F2-E2,TODAY()-E2). Format the result as a number, not a date.
How do I count open issues in Excel?
Use COUNTIFS with a not-equal condition: =COUNTIFS(D2:D7,"<>Closed") counts every issue whose status is not Closed. Add more pairs to filter by priority or owner.
How do I highlight old open issues in Excel?
Select the rows, go to Home > Conditional Formatting > New Rule > Use a formula, and enter =AND($D2<>"Closed",$G2>14). Pick a red fill and every open issue older than 14 days turns red.
Why does my aging column show a date instead of a number?
Excel sometimes copies the date format into the result. Select the column and choose Home > Number Format > General or Number to show the days.