Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification.
And you want this.
Except, there is a teeny tiny problem.
The sort order on the classification is all messed up.
Here is a quick fix to get custom sort order on your pivot table row labels. Continue reading
Chandoo.org is looking for your ideas!
What would you like to see discussed in future posts at Chandoo.org ? Continue reading
First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.
Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.
There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both. Continue reading
Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem.
Let’s simplify Kristin’s problem.
You have some data in the format shown above.
And you want to find out the BP category for each reading, using some rules. Read on to solve the problem. Continue reading
Let’s say you work in super hero factory as floor manager. You are looking at the recent time sheet data submitted by your underlings and want to know who works more. So you did what any self respecting floor manager does. You made yourself a large cup of hot chocolate, whipped open Excel and created a column chart.
But now, you want to add a line to it at 6:00 PM (or some other arbitrary point) so you can clearly see which superheros are over working.
So how do you go about it? Continue reading
It’s Halloween time. As adults, we can’t go trick or treating. We can of course dress up in costumes and entertain others. But what about the poor spreadsheets. Don’t they deserve some of this fun too?
Hell yeah! So I made a spider web generator in Excel. Just use it to make a spooky cob web pattern and add it to your report / dashboard / time sheet or whatever else. Surprise your colleagues. Continue reading
In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. How many formulas should you care to learn?
What is in this session?
In this podcast,
Here is a quick Pivot table tip. Let’s say your work at ACME inc. requires some fancy pants analysis of product sales. Imagine looking at below data & trying to find out the earliest & latest date for each product sale.
Of course, we can concoct a version of MINIFS & MAXIFS to answer the question. But why bother, when you can answer the question with just a few clicks. Continue reading
Over at twitter, @for_the_moves asks,
@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.
— Chandoo.org (@r1c1) October 12, 2016
That got me thinking. How many functions should you care to learn? Continue reading
Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.
How would you go about it?
If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?
Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic. Continue reading
