All articles with 'downloads' Tag

Analyzing 300,000 calls for help [case study]

Published on Aug 24, 2016 in Analytics, Learn Excel, Pivot Tables & Charts, Power Pivot
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.

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 »

Introduction to Forecasting in Excel 2016 [Charts & Visual Analysis]

Published on Jul 27, 2016 in Charts and Graphs
Introduction to Forecasting in Excel 2016 [Charts & Visual Analysis]

One of the coolest features of Excel 2016 is forecasting. Today, let’s understand how it works with a sample data set.

Watch below video to understand forecasting in Excel 2016.

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 »

Teach coding to your kids with this maze game [VBA]

Published on Jun 21, 2016 in excel apps, VBA Macros
Teach coding to your kids with this maze game [VBA]

My twins (Nishanth & Nakshtra) are now almost 7. They are super keen to learn how computers work. So the other day, I showed them Code.org where there are several coding exercises disguised as games. They loved those games … err coding exercises. So that got me thinking… why not make a game in Excel that teaches kids simple programming concepts.

So I built a Snowman & Hot Chocolate Maze game. In this post, let’s understand how to build such a game using Excel VBA.

Continue »

Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

Published on Jun 8, 2016 in Excel Howtos, Learn Excel
Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

We know how to use SUMIFS function to answer questions like, “What is the sum of values for ‘A’?”  But how would you answer questions like,

  • What is the sum of values that are neither “A” nor “B”?

We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.

Continue »

Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Published on Jun 2, 2016 in Excel Howtos
Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Last night I got an email from Joshua, one of our readers with the subject – Hard Excel problem. Hard?!?, at this stage of summer, the hard problems seem to be (in no particular order),

  1. Lack of good quality mangoes to eat
  2. Intense heat and humidity
  3. Lack of good quality mangoes to eat

Yes, I like mangoes.

Any how, back to Joshua’s email, So I got curios and read it. He is facing a curious problem.

Continue »

Fish Eye Effect for highlighting selection – Is it effective? [Advanced Charting]

Published on May 26, 2016 in Charts and Graphs
Fish Eye Effect for highlighting selection – Is it effective? [Advanced Charting]

A few days back, WSJ ran a visualization titled “What’s your pay gap?” It depicts median pay gap between female & male workers in 422 different professions in USA. The chart uses fish eye effect to highlight the selected profession. See below demo to understand the effect.

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

Continue »

Earth Venus cosmic dance – Animated chart in Excel

Published on Apr 25, 2016 in Charts and Graphs, VBA Macros
Earth Venus cosmic dance – Animated chart in Excel

Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.

Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.

I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?

Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.

Continue »

Sumerian Voter Problem [IF formula homework]

Published on Apr 22, 2016 in Excel Challenges

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Continue »

Advanced Interactive Charts using Excel [Master Class]

As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]

Continue »

A slicer that doesn’t slice [Pivot Table Tricks]

Published on Mar 30, 2016 in Pivot Tables & Charts, Power Pivot
A slicer that doesn’t slice [Pivot Table Tricks]

Mary Ellen, one of our readers, has an interesting conundrum,

I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?

See above demo to understand:

This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).

How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.

  • Old school method: Two pivot tables, some formulas & a line chart
  • New Excel method: Power Pivot and a line chart

Read the rest of this article to know more.

Continue »