Excel Challenge 35: Create an Invoice Tracker

Are you in the mood to flex your Excel formula and conditional formatting muscles? 

Everything you need to participate in the challenge can be found on this page. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

---

The challenge

Here’s the scenario to be solved from the download file:

We work in finance, and we’ve downloaded an invoice file from our financial system and imported it into Excel. 

The imported file is not in the most readable and user-friendly format, and it contains some blank rows. 

Our challenge is to reformat this data and add formulas into the empty columns that tell us if an invoice is ‘Past Due’, ‘Due Soon’ or ‘OK’. We then need to apply conditional formatting to highlight all invoices with a status of ‘Past Due’. 

🏋️Task 1: Add the Current Date

All calculations in this spreadsheet will use the current date so we need to add today’s date into cell C2 using a formula. 

🏋️Task 2: Rearrange the Layout

Currently, the data is running horizontally across the worksheet, with the column headings in the rows. We need to flip this around, so the column headings are in the columns instead. 

• There are a couple of different methods to change the layout of data. 
• If you choose to use a formula, ensure the underlying formulas are removed from the result. 
• Reapply formatting to the data where necessary. 
• Remove any zero values from the empty columns. 

🏋️Task 3: Format as a Table

Next, we need to convert the data to an Excel table with the following properties:

• Name the table ‘Invoices’. 
• Apply the table style ‘Dark Teal, Table Style, Light 9'.
• Remove banded rows. 

🏋️Task 4: Add Calculations

In the ‘Days Left’ column, add a formula that calculates the number of days left between the ‘Due Date’ and the current date. 

Copy the formula down. 

NOTE: If you see hash symbols in this column after filling the formula down, check the formatting applied to the column. It should be set to ‘General’. 

In the ‘Status’ column, add a formula that uses the ‘Days Left’ column to calculate the following:

• If the invoice is less than 0 days, output the text ‘Past Due’. 
• If the invoice is less than 30 days, output the text ‘Due Soon’. 
• If the invoice is greater than or equal to 30 days, output the text ‘OK’. 

🏋️Task 5: Apply Conditional Formatting

The final step is to apply conditional formatting to the entire row wherever the invoice has a status of ‘Past Due’. You can choose the formatting style. 

• Remember, the formatting should apply to the entire row and not just the selected cell. 
• We will need to use a formula and a rule to do this. 
• Think about how cell referencing might affect this formula. 

How quickly can you complete this challenge? Fifteen minutes? Ten? Less?

If you’re just getting started with Excel, all the knowledge you need to solve this challenge has been shown in the following resources: 

---

 Read:

	Understanding Excel number formats
	How to use the IF function in Excel

 

Watch:

---

If you’re already proficient with Excel, this should be quite easy.

We hope you'll enjoy taking part in this challenge! Have fun!

---

The solution

Did you remember to share your method in our Slack channel? If you're stumped, check out how other learners solved this problem. And don't forget to watch Deb Ashby's solution below.

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical real-world Excel skills.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial