## Aging debtors analysis | Using Today() and vlookups or how to know who you need to send to the Sopranos

Published on June 2, 2014 by in Excel Pivot tables | Introduction, Excel | Case Studies, Excel | Useful tricks

I was recently teaching a class on Powerpivot and the subject came up of how to use Excel to construct an aging analysis. The person had already created some serious serious formulas to do what he needed to do so no point in re-inventing the wheel. His big interest was how to do all that in Powerpivot.

To that end, I suggested he have a look at this article…

But his question got me thinking…how would I do that

So this is what I came up with. You can download the file here: Aging debtors’ analysis. I have to say for me I like to break these formulas down into separate segments ( they are easier to trouble shoot that way…)

1. Add an extra field to my invoices to generate the number of days outstanding (=TODAY()-A3 (or invoice date)
2. Create a vlookup table (see Debtor Lookup) which allocates a specific status to days. As you will see, I watch way too many Mafia programmes…. Important point to note about that is that Range Lookup should be left blank or enter true as you are referencing a range of days…not a specific number of days.
3. Using this vlookup table, create a vlookup formula  that returns the appropriate status for each invoice.
4. The aging analysis sheet uses pivot tables (here is the first tutorial in my pivot table series) to generate totals. Note that I manually re-ordered the listings to give the most logical one i.e. Current at the top.
5. Obviously as the days and invoice mount up, all you need to do is click on Refresh (Options – Refresh) in the pivot table to give you the most up to date profile.

## Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

## MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

## MyExcelOnline.com

### Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper.

## Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of \$10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

#### Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

## INTRODUCTION TO PIVOT TABLES | 08 – USING THE SLICER (EXCEL 2010 ONLY)

Published on February 12, 2013 by in Excel Pivot tables | Introduction, Excel | General

So this is the final installment in the series of pivot tables. You can check out parts onetwothree, fourfive ,six and seven here.

A new feature of Excel 2010 is the Slicer – which looks rather like a grater but which allows you to easily “slice and dice” your data.

So here’s how it’s done..

And as always here is the file for you to practise on.

## INTRODUCTION TO PIVOT TABLES | 02 – PREPARE USING A TABLE

Published on January 16, 2013 by in Excel Pivot tables | Introduction

This is part two of my introduction to pivot tables. You can have a look at part one here. A new feature in Excel 2007 is called the table. While it is not essential to convert your list to a table, doing so means that you can then work more efficiently with your data when you do convert it to a pivot table.

The advantages of converting your list to a table are as follows:

1. Your list is instantly formatted (and you can easily change the formatting – as you will see in the video below)
2. If and when you add further rows/columns to your list, all you have to do is refresh your pivot table and the new data is automatically incorporated. (If you don’t use a table, it means you have to re-select all the data again – which can be very tedious if you have large lists)
3. If you add a formula to your table, the calculations are automatically copied down – the work is done for you. (handy huh?)

OK, so have a look at the video and see how its done. I’m using a large list which I created using the fake name generator and let’s have a look…I’m going to show you how to convert to a table and then take the first step on creating a pivot table from it. (And if you are wondering how I quickly generated the random salary values – here’s the tutorial)

Here is the file to practise on: Pivot Table 02 – PREPARE USING A TABLE