Author Archive

Hiya folks… Got an exciting news to share with you all. Over the weekend, my YouTube channel hit 50,000 subscriber milestone.

Thank you so much for making me a part of your journey to awesomeness.

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?

Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?

You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?

Worry not, you can use Power Query to transform original text to new one by replacing all matching values.

In this page, learn how to do that with the excellent List.Accumulate() Power Query function.

Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

So today, lets talk about best charts to show % progress against a goal.

Do you run an e-commerce website? You are going to love this simple, clear and easy website metrics dashboard. You can track 15 metrics (KPIs) and visualize their performance. The best part, it takes no more than 15 minutes to setup and use. Here is a preview of the dashboard.

Click to download the template.

Of all the charting features in Excel, Sparklines are my absolute favorite. These bite-sized graphs can fit in a cell and show powerful insights. Edward Tufte coined the term sparkline and defined it as,

intense, simple, word-sized graphics

Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. This page provides a complete tutorial on Excel sparklines.

Slope line is very useful for spotting which values have changed from two sets. You can add a slope line to XY chart (scatter plot) using simple techniques. In this post, learn how to add them.

Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

Welcome to annual Easter Egg hunt at Chandoo.org. This year I have a feast for you. I hid 20 Easter Eggs in this workbook. Go ahead and find them. Share your solutions, clues or struggles in the comments box.

Happy hunting.

Are you ready for a fun Excel challenge? Read on then…

You are a nautical transport manifest analyst at New Donk city harbor. But you also have a secret identity. You are a spy for Global Intelligence Organization. As part of routine inspection of cargo details, you came across a list of shipping codes that look suspicious.

Ever wanted to convert numbers to words in Excel? For example, 456,123 becomes four hundred fifty-six thousand one hundred twenty-three.

Microsoft recently introduced LET function to Excel. This allows us to create variables on the fly to use within a formula. I just made a words from number formula using LET function and bit of good old INDEX.

Read on to learn how this all works.

How are you? I want to say hello and check in with you in these strange and scary times.

We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting and 10 other tips.

So go ahead and become a formatting pro.

Do you want to create a simple, elegant and useful tracker using Excel? You can make trackers with features like tables, data validation rules and conditional formatting. In this page, I will explain the process for creating an Excel tracker.