It is Your Week @ PHD. You get to see all the excel tips shared by PHD readers like you. I am very excited about this. Today I rushed home from work and opened the google docs to see the tips submitted by you, and found several new and fun things. You can be part of this excitement too: just click here to share your tips with us.
Here is the part 1 of the series.
The first and foremost chart construction tip by Jon Peltier
Before you take any steps towards constructing a chart, get the data right. This will save time and effort, reduce frustration, and amaze your boss, clients, and that cute young thing in accounting. Well, your boss and clients anyway.
Consider it as a set of data extracted from a database: each row is a record, each column is a field, the first row is the field names.
First row of the data range: series names as column headers.
First column of the data range: category labels or X values.
Intersection of first row and first column: overcome your natural tendency for order and uniformity, and leave this cell blank.
Data is Series in columns.
A Flurry of Keyboard Shortcuts and Productivity Hacks by Barbara
I use several shortcuts
- F12 for ‘Save As’
- F2 for ‘Edit’
- F4 to switch between absolute and relative formula references, whilst in edit mode.
- Control & 1 (using the number keys not the number pad) for ‘format cells’
If you want to copy part of a spreadsheet to a powerpoint:
Highlight the area, hold shift, click edit in the menu, copy picture, choose ‘as shown on screen’ or ‘as shown when printed’. Paste into the powerpoint slide.If you put the insert rows, insert columns & group icons in you toolbar you can use them for the opposite function as well. Press & hold the shift key, then click on one of the tools, it will do the opposite. This not only saves space in your toolbar, but I find it useful as I can not easily tell the difference between the insert & delete or group & ungroup icons.
Several ways to search text using formulas by Vishy
Download these examples and play with them.
Lookup into SubstringLet’s say you want to find Age in Column D by referring to Part of First Name in cell G3 as below
First Names are in B3:B7
Ages are in D3:D7
Part of First Name Reference in G3
{ =INDEX($D$3:$D$7,MATCH(TRUE,FIND(G3,$B$3:$B$7)>0,0)) } Must be entered as an array (Ctrl + Shift + Enter)
Use SEARCH instead of FIND for case-insensitive lookup
You can also use regular expressions – Download the examples
* refers to any number of characters, including null (i.e. no character)
? refers to exactly one character, not null
Multiple Criteria Lookup
Let’s say you want to find Age in Column D by referring to First Name in cell G4 and Last Name in H4 as below
First Names are in B3:B7
Last Names are in C3:C7
Ages are in D3:D7
First Name Reference in G4
Last Name Reference in H4
{ =INDEX($D$3:$D$7,MATCH(1,($B$3:$B$7=G4)*($C$3:$C$7=H4),0)) } Must be entered as an array (Ctrl + Shift + Enter)
Some Resources to help you understand these tips:
Introduction to Excel Array Formulas
INDEX Excel Formula – Learn by Example (Used to construct a dynamic chart)
MATCH Excel Formula – Tutorial & Examples
Absolute and Relative Formula References – A Beginner Guide
Jon, Barbara and Vishy, You are truly amazing human beings. We at PHD love you for these wonderful tips.
13 Responses to “Using pivot tables to find out non performing customers”
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
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
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,
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
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
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.
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
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
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
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.
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.
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)
[…] Finding non-performing customers using Pivot Tables […]