Highlighting Repeat Customers using Conditional Formatting [Part 2 of 2]

Share

Facebook
Twitter
LinkedIn

This is second part of 2 part series on conditionally formatting dates in excel.

Highlighting Repeat Customers using Conditional FormattingIn yesterday’s post we have learned how to conditionally format dates using excel. In this article, you will learn how to use these conditional formatting tricks to highlight repeat customers in a list of sales records.

The problem: Highlighting repeated customers in a list

Let us say you run a small retail store. And you want to give special discounts to all the repeat customers.

Repeat customer is someone who bought at least twice from you in last 30 days. (If the person bought twice but the gap between 2 purchases is more than 30 days they are not repeat customers).

The Data:

Let us assume your sales data has these 2 columns  – customer ID and purchase date. I have shown first few rows here. Let us assume the data is in the range B4:C53.

Highlighting Repeat Customers using Conditional Formatting - DATA

Finding if a customer is repeat – The Formula:

If we just want to highlight without considering the purchase dates,

we can use a simple formula like =COUNTIF($B4:$B53,$B3)>1 in the conditional formatting applied over the range B4:C53.

But we need to consider the date as well,

hmm, now that is tricky !??

May be time for a sip of that coffee. Go take it, I am waiting..,

How about the SUMPRODUCT? We all know that sumproduct formula can be used to test more than one condition.

The formula:

=IF(C4>TODAY()-30,IF(SUMPRODUCT(--($B$4:$B$53=B4),--($C$4:$C$53>(TODAY()-30)))>1,"R","N"),"N")

Now that is one lengthy badass formula. Like Sergeant Martin Riggs. Bad, but still cool.

So what is this formula really doing? It is going to give us “R” if a customer is repeat and “N” if not. See this explanation to understand how it works.

Highlighting Repeat Customers using Conditional Formatting - Formula

Note: the double dashes “–” make the values as 0s and 1s from a bunch of “trues” “falses”. To know why sumproduct is such a beautiful and robust formula look no further.

The Conditional Formatting

Now that you have figured out the formula to determine a customer is repeat or not, applying conditional formatting is a piece-o-cake.

Just select the range B4:C53, go to conditional formatting and select “formula” option. Now specify the above formula and check if its output is “R” and apply formatting.

I am not telling you how to do this. It is your homework. Go figure!

That is all. Your workbook now highlights repeat customers in the last 30 days. Remember, as you reopen the file a week later, the highlighting logic changes since the date has changed.

Download the example workbook

Click here to download the example workbook and understand how to highlight repeat customers using conditional formatting. The file works in Excel 2003+.

What is your experience?

Share your tips and ideas on using conditional formatting with dates. What are the situations you usually face and how to solve them? I am never too old for this, so please share.

Learn more about conditional formatting

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

13 Responses to “Using pivot tables to find out non performing customers”

  1. David Onder says:

    To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales).  Now I can show more than one year, I can summarize - I can do many more things with it.  ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format.  Much easier in my opinion.

    David 

    • Chandoo says:

      Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.

  2. Martin says:

    David, I was just about to post the same!
    In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
     
    Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
     
    Rgds,

  3. JMarc says:

    Chandoo,
    If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :  =IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))
     This formula will sum the sales from Selected Year to 2012.

    JMarc

  4. Elias says:

    If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
     
    Regards

  5. RichW says:

    I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
    Change the helper column to:
    =IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
    And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.

  6. Kevin says:

    Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
     
    Cheers

  7. Kevin says:

    Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious?  I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
     
    Thanks again

  8. Kevin says:

     
    worked it out thanks...
    when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated 
     
    Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.

  9. Bermir says:

    Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula

    =2000+MATCH(1000000,E5:P5)

    will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.

    • Bermir says:

      Somewhat longer but perhaps a bit more solid (with the column titles in row 4):

      =RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)

Leave a Reply