Amalgamating and ranking your customers in Excel
If you want to analyse your customer (or supplier) base, Excel’s SumIfs function is going to become a firm friend. But there are a few little tricks that you’ll want to play along the way. And you may find that some of Excel’s newer functions (Unique and Sortby) will also become helpful for you – so you might want to make sure you’re happy with those ones too.
First: get your data in one block, and tag it
You can see below that I’ve assembled all my data into one block, which is going to make it easier to run functions over it in subsequent steps. Then I’ve got a series of columns to one side where I’ve ‘tagged’ the data i.e. assigned a label that will dictate how the data could get amalgamated using SumIfs.
You can see that, to start, out to the right I’m looking at how long customers have been with us, and when they’re coming up for contract renewal (the ones that have only been with us a short time, and are on a short contract, are a particular risk).
Having the data in one big block, with each row of data tagged, makes it easy to perform the analysis I want, using SumIfs.
Using Unique to create a master list of categories to amalgamate data by
In that first example I was making use of 2 tags (for each of longevity and duration) assigned to each row of data. But you could often have more.
You could have a big list of customer names and you might not be exactly sure how many are in the list and how many duplicates you have (where you have done repeat business or multiple pieces of business with one customer).
Unique will help you. It will create a master list of e.g. customer names, eliminating any duplicates. Here I’ve also sorted the customer numbers (if you were using customer names you might want to use to sort to display them in an alphabetical list).
Note Unique is a newer class of ‘dynamic array’ Excel function where you point to the range (= array) of data you want it to process, usually expecting the function to create an array as its output.
Now we can put SumIfs to work again, amalgamating data so all the business conducted with one customer appears next to that customer.
Using Sortby to rank our customers from largest to smallest
Next we can use Sortby to reorder our customer data, so that the biggest one appears at the top and we can list out all of our top 10. It all becomes easy with Sortby (other solutions are available but usually seem to become more complex than this one here).
It all becomes easy (if you know how!)
Analysing (‘cutting’) our customer base starts to become easy. As long as we:
- Group data in one big block, tagging each row according to the features we want to amalgamate by (e.g. contract length, customer longevity, customer number or name), and using SumIfs to amalgamate our data into the layers we want
- Make use of Excel’s Unique function when we find we need to establish a master list of e.g. customer names (with no duplicates), allowing us to use SumIfs to amalgamate data by individual customer name
- Use Sortby to rank data from top to bottom.
With those few functions under your belt (SumIfs, Unique, Sortby) amalgamating and ranking data in Excel suddenly becomes easy!
