# Excel Challenge 35: Create an Invoice Tracker

Deb Ashby

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

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!

Take the challenge!

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’.

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.

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:

### 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!

Take the challenge!

## 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

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.