All articles with 'downloads' Tag

Introducing Excel Dashboard Templates from Chandoo.org

Introducing Excel Dashboard Templates from Chandoo.org

Friends and readers of Chandoo.org, my newest download, Excel Dashboard Templates are available for your consideration. Click here if you are ready to get them. Read on to know more.

What are Excel Dashboard Templates?

As the name suggests, these are ready to use Excel templates for creating awesome, informative, easy to understand & quick dashboards with your data. You just enter your data, set up few calculation options, decide how your dashboard should look & bingo, a beautiful & insightful dashboard will be created for you. These dashboards are optimized to look great, convey key information & prompt correct action.

Who should buy these templates?

Analysts, managers, reporting professionals, business owners & executives. These templates are designed to help anyone preparing Excel based dashboards, scorecards or KPI reports.

Benefits for you

  • Design awesome dashboards in minutes, not months: Don’t waste hours trying to figure out formulas, chart settings and layouts. Instead focus on what your users want, how your business works and use those insights to tell the ‘Excel Dashboard Template’ what you want. Save time and look awesome.
  • Accommodate change elegantly: People change. Businesses change. What your boss wanted last month is no longer what she wants today. So when someone asks you, “hmm, can you show me YTD data instead of last 13 months”, or “Can I see top 10 KPIs instead of all”, you just smile and tap a few clicks and bingo, the new dashboard will be ready. These templates are designed to handle change (data, requirements, design preferences and insights).
  • Beautiful & Informative: These templates are designed with a delicate balance of beauty & insight. Everything is seamlessly designed, perfectly aligned and precisely calculated so that you & your boss can focus on the insights & decisions. Time tested and well honored visualization principles & guidelines are observed thru-out the reports.
  • Built for starters, built for pros: Whether you are just starting with Excel or a seasoned pro, you can find the templates right up your alley. For advanced users, we have many customizations to whet your appetite. Want to build a custom calculation or include another type of sorting, you can do it by tinkering with ‘calculations’ sheet.
Continue »

Looking up when data won’t play nice – few more alternatives

Published on Nov 12, 2014 in Learn Excel, VBA Macros
Looking up when data won’t play nice – few more alternatives

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.

Continue »

Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos
Looking up when the data won’t co-operate (case study)

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Continue »

The ultimate VLOOKUP trick – Multi-condition Lookup

Published on Oct 28, 2014 in Learn Excel
The ultimate VLOOKUP trick – Multi-condition Lookup

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Continue »

A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart

Recently, I saw this chart on Economist website.

It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).

Best cities to live - Chart from Economist.com

As you can see, this chart is not the best way to visualize “Best places to live”.

Few reasons why,

  • The segregated views (blue, gray & red) make it hard to look for a specific city or region
  • The zig-zag lines look good, but incredibly hard to understand % changes (or absolute changes)
  • Labels are all over the place, thus making data interpretation hard.
  • Some points have no labels (or ambiguous labels) leading to further confusion.

After examining the chart long & hard, I got thinking.

Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.

Continue »

Charmed Price Problem

Published on Oct 6, 2014 in Excel Howtos
Charmed Price Problem

Here is a charming little problem to kick start your day.

Lets say you run a cute little bakery around the corner. Since you want your prices to look charming, you have a policy to round them down or up based on below rule.

If the price ends with 0, 1 or 2 cents, round it down to 9 cents.

If the price ends with 3, 4 or 5 cents, round it up to 5 cents.

If the price ends with 6, 7, 8 or 9 cents, round it up to 9 cents.

For example,

So how do you round to nearest charmed price? You could do it manually. But you would rather bake a few more of those Tiny Cup Cakes than waste time rounding the prices. So you want an automatic way to round prices. This is where Excel helps.

Continue »

ABC Inventory Analysis using Excel

Published on Oct 1, 2014 in Charts and Graphs, Learn Excel
ABC Inventory Analysis using Excel

ABC analysis is a popular technique to understand and categorize inventories. Imagine you are handling inventory at a plant that manufactures high-end super expensive cars. Each car requires several parts (4,693 to be exact) to assemble. Some of these parts are very costly (say few thousand dollars per part), while others are cheap (50 cents per part). So how do you make sure that your inventory tracking efforts are optimized so that you waste less time on 50 cent parts & spend more time on costly ones?

This is where ABC analysis helps.

We group the parts in to 3 classes.

  • Class A: High cost items. Very tight control & tracking.
  • Class B: Medium cost items. Tight control & moderate tracking.
  • Class C: Low cost items. No or little control & tracking.

Given a list of items (part numbers, unit costs & number of units needed for assembly), how do we automatically figure which class each item belongs to?

And how do we generate above ABC analysis chart from it?

Continue »

3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel
3D Max Formula for Excel

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Continue »

CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions

Published on Sep 4, 2014 in Chandoo.org Podcast Sessions
CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions
Play

In the 19th session of Chandoo.org podcast, lets talk about modeling best practices.

What is in this session?

I am very happy to interview my good friend, blogger, author, excel trainer & business-women – Danielle Stein Fairhurst for this session. I first met Danielle when I went to Sydney, Australia in April 2012. Our friendship & collaboration grew a lot in the last 2.5 years. She is a great speaker & trainer. This episode is loaded with her trademark style commentary, explanation & tips for better modeling. I hope you will enjoy it.

In this podcast, you will learn,

  • Introduction to Danielle & her work
  • 6 Tips for Best Practice Modeling
    • Write consistent formulas
    • Avoid hard-coding
    • Smart referencing
    • Ditch the bad habits
    • Document assumptions
    • Format & label things
  • Resources for learning more
Continue »

What is the average speed of this road trip? [Solution & Discussion]

Published on Aug 18, 2014 in Excel Howtos, Formula Forensics
What is the average speed of this road trip? [Solution & Discussion]

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Continue »

Mapping relationships between people using interactive network chart

Published on Aug 13, 2014 in Charts and Graphs
Mapping relationships between people using interactive network chart

Today, lets learn how to create an interesting chart. This, called as network chart helps us visualize relationships between various people.

Demo of interactive network chart in Excel

First take a look at what we are trying to build.

Looks interesting? Then read on to learn how to create this.

Continue »

A dashboard to visualize this FIFA worldcup [guest post]

This is a guest post by Krishna, a football lover & one of our readers.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. :)

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

FIFA 2014 Worldcup Excel dashboard

Continue »

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.

So what is this all about?

Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.

It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.

Continue »

How fireworks animated chart is made [video tutorial]

Published on Jul 17, 2014 in Charts and Graphs, VBA Macros

On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.

So here is a video explaining the construction of fireworks.

(You can see this video on our YouTube Channel too)

Continue »

CHOOSE() me, an introduction to Excel CHOOSE function

Published on Jul 16, 2014 in Learn Excel
CHOOSE() me, an introduction to Excel CHOOSE function

Today lets learn about Excel CHOOSE() function.
CHOOSE eh? What does it do?
To understand CHOOSE() and appreciate its uses, lets invent an imaginary boos-subordinate pair.

Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.

Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.

Enter Jasmine’s scarf problem:

Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.

No wonder she is peculiar.

Continue »