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

Latest Tips & Tutorials

How to highlight overdue items

We, adults can’t escape three things:

Deadlines
Demanding bosses (replace with customers or nagging spouses or naughty kids)
Taxes

While I can’t help you with demanding bosses or taxes, when it comes to deadlines, I have the right tool for you.

A tracker that highlights all overdue items so that you know where to focus your attention.

Let’s learn how to use awesome powers of Excel to find-out which items are due. You can apply these concepts to nail down over due invoices, pending project tasks or scheduling workforce.

Read This »

August 3, 2015

How to create dynamic sparklines for latest 30 days

Sparklines are fun and very insightful. They are easy to create, easy to maintain and fit into any dashboard.

But there is one tiny problem with them. Usually we have a lot of data, but we don’t to visualize all of it. We just want to visualize latest 30 days trend or last 12 months trend or QTD or something similar. What then?

In this video, learn a powerful and very simple way to create dynamic sparklines using Excel.

Read This »

August 2, 2015

Clean data quickly with Flash Fill

Excel has many powerful & time-saving features. Even by Excel’s standard, Flash Fill is magical. Introduced in 2013, Flash Fill is a rule engine to Excel’s fill logic. Every time you type something in a cell, Excel will try to guess the pattern and offers to fill up the rest of cells for you. That is some serious time saving magic.

Let’s understand what Flash Fill is and few sample use cases.

Read This »

August 1, 2015

CP040: Intro. to Power Query – What is it and how to get started – with Miguel Escobar

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 40th session of Chandoo.org podcast, Let’s talk about Power Query. I have the pleasure and fortune to catch up with Miguel Escobar (who along with Ken Puls runs PowerQuery.Training website) and talk about this very exciting piece of technology and how it can make our life simpler.

What is in this session?

In this podcast,

Welcome
Miguel’s introduction, background and current projects
What is Power Query
How to install it
Sample use cases of Power Query
What is Power BI
Resources for learning Power Query – Books & Courses

Read This »

July 30, 2015

Shading an area chart with different colors for up & down movements [case study]

We all know that area charts are great for understanding how a list of values have changed over time. Today, let’s learn how to create an area chart that shows different colors for upward & downward movements.

The inspiration for this came from a recent chart published in Wall Street Journal about Chinese stock markets.

We will try to create a similar chart using Excel. We are going to create the above chart in Excel.

Looks interesting? Read on…

Read This »

July 29, 2015

Announcing Awesome August

Hi folks,

I am excited to announce Awesome August. It is a month-long effort to make you awesome in various aspects of Excel.

Awesome August… What is it?

For each and every day of August 2015, I am going to publish a new piece of content – an Excel tip, a formula technique, a charting solution or a productivity booster. 31 days – 31 pieces of awesome content.

Sounds Yummy? Participate in Awesome August:

There are 4 ways to participate in Awesome August

Sign-up to Chandoo.org newsletter. Get weekly emails with links to all the Awesome August content.
Comment & Win: Comment on any of the Awesome August posts. 31 random commenters will each get $31 Amazon gift-card.
Follow Awesome August in social media – on twitter, facebook, youtube and iTunes
Ask your friends & colleagues to participate. Send them to Chandoo.org/AwesomeAugust

Read This »

July 28, 2015

CP039: May the FOR Loop be with you – Introduction to For Loops in Excel VBA

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 39th session of Chandoo.org podcast, Let’s learn about FOR loops.

There is a special giveaway in this podcast. It is a workbook with several FOR loop VBA code examples. Listen to the episode for instructions.

Introduction to Excel VBA FOR Loops - What are they, how to use them - Chandoo.org Podcast - Session 039

What is in this session?

In this podcast,

Announcements
What is a loop – plain English & technical definitions
For Loop vs. other kind of loops (While & Until)
For Next loops
For Each loops
Nested For loops
Special tips on For loops
Performance issues & infinite loops
Conclusions & giveaway

Read This »

July 23, 2015

How to find out if a text contains question? [Excel formulas]

On Wednesday (15th July), I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it). It was a huge success. More than 1,100 people attend the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

Read This »

July 17, 2015