Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Formula Forensics 042: Reverse Text – A Formulaic Solution

Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.

January 4, 2017

Merry Christmas & Happy New Year 2017 [Holiday Greeting]

A big, warm & pleasant hello to you.

I wish you a merry Christmas & Happy New Year 2017. May your holidays be filled with joy, togetherness, celebrations and fulfillment. May your new year be filled with hope, energy and awesomeness.

I want to tell you how thankful I am for all your support in this year. Every time you visit our website, read an article, leave a comment, enroll in a course, purchase a product, read one of my books, listen to a podcast episode, watch a video or tell your friends about Chandoo.org, I feel nothing but gratitude, thankfulness and amazement. 2016 has been a remarkable year in our journey and I owe this to you and your support.

Read This »

December 23, 2016

An odd lookup problem [Formulas]

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

Read This »

December 6, 2016

Sorting to your Pivot table row labels in custom order [quick tip]

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.

Read This »

November 29, 2016

We Want Your Ideas

Chandoo.org is looking for your ideas!
What would you like to see discussed in future posts at Chandoo.org ?

Read This »

November 24, 2016

Finding the closest school [formula vs. pivot table approach]

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.

Read This »

November 18, 2016

Can you solve this blood pressure problem? [IF Formula Homework]

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.

Read This »

November 4, 2016

How to add a line to column chart? [Charting trick]

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?

Read This »

November 2, 2016