All articles with 'Learn Excel' Tag

Mapping Spread of Obesity [Chart Critique and Alternatives]

Mapping Spread of Obesity [Chart Critique and Alternatives]

Over at Flowing Data, Nathan has published an interesting visualization about spread of obesity.

While the above chart is quite interesting, it doesn’t offer much insight in to the data. There are a few drawbacks,

  • Understanding obesity trends for a given state over the years is hard due to the layout and format of the chart.
  • Finding which states are experiencing most obesity growth rates is not possible
  • Ironically, the chart itself is obese. It takes too much space to explain the data.

Nevertheless, the chart looks cool and can be reused with smaller data-sets (quarterly trends or just for a few years). So let’s recreate the same in Excel. While we are at it, let’s also build an alternative visualization to explore the obesity data.

Continue »

Visualizing target vs. actual progress – Biker on a hill chart

Visualizing target vs. actual progress – Biker on a hill chart

Over the years, we have discussed a whole heap of techniques to visualize budget vs. actual charts. Today let’s take a ride on this slope again and learn another fun, silly & awesome way to depict target vs. actual progress.

Introducing biker on a hill chart

Tada!!!

Biker on a hill!?! Don’t worry, I didn’t fall down on a descent and lose my brain. I am talking about an Excel chart to visualize target vs. actual progress on a time line with biker on a hill analogy. See the above chart, you will know.

Looks interesting? Read on to learn how to create this in Excel.

Continue »

Stacked Bar/Column Chart with Indicator Arrows – Advanced

Published on Sep 15, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar/Column Chart with Indicator Arrows – Advanced

Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.

Continue »

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Published on Sep 13, 2016 in Analytics, Financial Modeling
Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.

What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.

Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.

  1. Build a model to estimate profit per given number of copiers & demand values
  2. Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Continue »

Stacked Bar and Indicator Arrow Chart – Tutorial

Published on Sep 12, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar and Indicator Arrow Chart – Tutorial

Learn how to develop a Stacked Bar chart with Indicator Arrow in this Tutorial

Continue »

Tell me about an analysis problem that you couldn’t solve with Excel?

Published on Sep 9, 2016 in Analytics

Time for a quick show & tell.

Tell me about an analysis problem that you couldn’t solve with Excel?

It can be because you didn’t know how to solve the problem or Excel isn’t the tool for it or any other reason.

Go ahead and speak up. Post your tricky analysis problems in the comments section.

Continue »

PSA: Don’t let auto correct spoil your party

Published on Aug 26, 2016 in Learn Excel
PSA: Don’t let auto correct spoil your party

So here is a news from strange but true department. Microsoft Excel blamed for gene study errors [bbc.com].

Microsoft’s Excel has been blamed for errors in academic papers on genomics.
Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.
Gene symbols like SEPT2 (Septin 2) were found to be altered to “September 2”.

Aah, classic!

This is what happens when you spend countless hours learning genome sequencing and very little about the software tools where your data goes. May be we need clippy back to warn people about such sticky situations.

Continue »

Weekend open mic – Share your one hand Excel shortcuts

Published on Aug 12, 2016 in Keyboard Shortcuts
Weekend open mic – Share your one hand Excel shortcuts

At Chandoo.org, we are big believers of keyboard shortcuts. There are several posts (1,2,3,4more and even more) discussing useful Excel shortcuts. Today I want to introduce a new kind of keyboard shortcuts. One hand shortcuts.

One hand shortcuts – Half the work, double the fun

The idea is simple. When you can use only one hand to complete the shortcut key presses, it is called as a one hand shortcut.

Continue »

Excel Links – Getting used to life in Windy Wellington Edition

Published on Aug 10, 2016 in excel links, personal

So we moved to Wellington, New Zealand few weeks back (on 17th of July 2016, to be precise). After spending first 3 weeks in Jeff’s house and a hotel, we moved in to our rental home over the weekend (on 6th of August). Around the same time, the worst of Wellington winter waved welcome to us. We quickly learned how to stay warm indoors (layers, hot water bottles, rugs and more layers). Kids started going to school few days back and they are loving it. I bought a bike and managed to go out on few rides on the hilly roads of Wellington and found a strange for sale sign too.

For sale: Pony poo and pine cones

Anyhow, Since we didn’t have internet connection until today, I thought I will start by sharing a few Excel links with you. Check them out to get your fix of spreadsheets.

Read on…

Continue »

Find the seals [Excel puzzle]

Published on Aug 5, 2016 in Excel Challenges
Find the seals [Excel puzzle]

First a little back story:

Last Friday (on 29th of July), we (Jo, kids & I) went on a day walk to Red Rocks. It is a rugged coastal walk near Owhiro bay in Wellington. It was a windy & cold day. So why did we brave the elements of nature on this 10km walk? To see seals of course. And we did find a few of them. We also caught glimpses of snowy peaks in Southern Island of New Zealand.

3 Seals Excel Puzzle

Now I can’t take you on the same walk thru internet. There is no Excel function that can teleport you from your office (or home) to Owhiro bay. So I made the next best thing.

An Excel puzzle with 3 hidden seals.

Continue »

5 Shortcuts for you, one for every weekday [Awesome August]

Published on Aug 1, 2016 in Keyboard Shortcuts, Learn Excel
5 Shortcuts for you, one for every weekday [Awesome August]

Let’s kick start our Awesome August 2016 with a simple but very useful set of shortcuts. 5 shortcuts, one for each weekday.

  1. Monday ALT + M N: Open name manager. Very handy, if you have quite a few range names and want to edit / manage them. Remember, this is a sequence shortcut, that means, you press ALT M, let go of both keys and then press N.
  2. Tuesday CTRL + T: Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.
  3. Wednesday CTRL + W: Close the current workbook, while keeping Excel open.
  4. Thursday ALT + T O: Opens Excel options. Very easy to remember too.
  5. Friday CTRL F: Fridays can be hard to concentrate. Use CTRL+F to find what you want in the current workbook. Use CTRL+H if you wish to do a find replace.
Continue »

How to get Maps in to Excel charts & dashboards [Master Class]

Published on Jul 21, 2016 in 3D Maps (Power Maps), Charts and Graphs, Master Class
How to get Maps in to Excel charts & dashboards [Master Class]

This is third episode of our Monthly Master Class.

In this one, you will learn how to get maps in to your Excel workbooks. Understand 5 key techniques for making maps based visualizations in Excel – from regular charts to cell grids to VBA to Power Maps, everything is covered in this intense Master Class.

Continue »

Find out how many times a value is present in a cell [formulas]

Published on Jul 19, 2016 in Excel Howtos, Learn Excel
Find out how many times a value is present in a cell [formulas]

Here is an interesting problem to start your day.

Let’s say you work as DNA sequencing engineer at The Enterprise. And you just unlocked the sequence that is responsible for all male problems. The early onset of baldness. The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.

So how do you write the formula?

Continue »

On / Off conditional formatting with this simple trick

Published on Jul 13, 2016 in Learn Excel
On / Off conditional formatting with this simple trick

Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.

Take a look at above demo to understand what I mean.

Continue »

How to visualize multiple variables over several years? [Contest]

Published on Jun 24, 2016 in Charts and Graphs, Excel Challenges
How to visualize multiple variables over several years? [Contest]

Our newest contest is inspired from a question asked by Kaushik, one of our forum members, interesting problem.

Need to quickly visualize 3 variables ( Company, years, Financials) in a single […] chart.

Create a chart to understand multiple variable data and you could win $100 Amazon gift card. Do send your charts before 4th of July to qualify for the prizes.

Continue »