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

Latest Tips & Tutorials

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.

Read This »

August 26, 2016

Analyzing 300,000 calls for help [case study]

Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?

Finding the first responded team is tricky.

Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.

Read This »

August 24, 2016

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,4, more 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.

Read This »

August 12, 2016

Excel Links – Getting used to life in Windy Wellington Edition

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.

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…

Read This »

August 10, 2016

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.

Read This »

August 5, 2016

Correlation vs. Causation [Charting Chatter]

Here is a trap that is easy to fall in to. Confusing correlation as causation. As analysts, it is our job to see the data as it is rather than imply causation that doesn’t exist.

Let’s sample a chart, recently featured in Economist’s graphic detail under the title Measuring well-being.

Read This »

August 3, 2016

Add any number of days, months or years to a date with this simple trick

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

=A1 + DATE(2,4,9)
=EDATE(A1, 2*12+4) + 9
=A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

Read This »

August 2, 2016

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.

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.
Tuesday CTRL + T: Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.
Wednesday CTRL + W: Close the current workbook, while keeping Excel open.
Thursday ALT + T O: Opens Excel options. Very easy to remember too.
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.