Calculate sum of top 10 values [formulas + homework]

Calculate sum of top 10 values [formulas + homework]

Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.

Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores.  Read on to learn the techniques.

Continue »

How to highlight overdue items [video]

How to highlight overdue items [video]

We, adults can’t escape three things:

  1. Deadlines
  2. Demanding bosses (replace with customers or nagging spouses or naughty kids)
  3. 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.

Continue »

How to create dynamic sparklines for latest 30 days [video]

How to create dynamic sparklines for latest 30 days [video]

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.

Continue »

Clean data quickly with Flash Fill

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.

Continue »

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

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

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
Continue »

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

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…

Continue »

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 - 31 days, 31 awesome posts - from Chandoo.org

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

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