All articles with 'videos' Tag

May the POWER BI with you [Star Wars Day Viz]

Published on May 4, 2018 in Power BI
May the POWER BI with you [Star Wars Day Viz]

It is star wars day. Let’s celebrate the ultimate nerd day of the known universe in a way that resonates with us. Let’s dress up as storm troopers and make exploding 3d pie charts.

Just kidding. Let’s just make a cool visualization in Power BI instead.

Continue »

Top 5 keyboard shortcuts for Excel Charts

Published on Apr 24, 2018 in Charts and Graphs, Keyboard Shortcuts
Top 5 keyboard shortcuts for Excel Charts

We all know that learning a few keyboard shortcuts can speedup your Excel game. Most pro users rely on a handful shortcuts when working with large spreadsheets. But when it comes to charting, we automatically reach for mouse. But do you know that you can use few simple shortcuts to do most day to day chart related things?

Ready for top 5 keyboard shortcuts for Excel charts? Read on.

Continue »

Which power plan is best for me? Excel for everyday problems

Published on Nov 9, 2017 in Analytics, Financial Modeling
Which power plan is best for me? Excel for everyday problems

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Continue »

D’oh – Visualizing Homer’s favorite sayings in Power BI

Published on Sep 29, 2017 in Power BI, Power Pivot, Power Query
D’oh – Visualizing Homer’s favorite sayings in Power BI

Before we begin:

Today is the last day for enrolling in our Power BI Play Date. Don’t miss out on this amazing opportunity to learn, use and benefit from Power BI at your work. Check out my online class and sign up before the doors close at midnight. Click here.

Let’s get our Simpsons on then.

D’oh, How often Homer says his favorite things?

Here is the visualization to explore Homer’s (and other character’s) favorite sayings in 27 years worth of Simpsons episode. Click on the image to play.

Continue »

Epic Introduction to Power BI [video]

Published on Sep 13, 2017 in Power BI

Yay, finally our first ever full length Power BI tutorial is up. In this 30 minute video tutorial, learn how to get some data in to Power BI and make a quick report to understand it. We will be creating this.

customer-service-db-pbi-v1

Let’s get started then.

Continue »

Convert unevenly spaced list to table [Data from Hell]

Published on Aug 30, 2017 in Power Query
Convert unevenly spaced list to table [Data from Hell]

Introducing Data from Hell:

Watch out, its data from hell. In this new video series, we are going to examine some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.

For our first installment, let’s reshape unevenly spaced list of values to a table.

Continue »

Employee Performance Panel Charts in Power BI with R

Published on Aug 11, 2017 in Power BI, Power Query
Employee Performance Panel Charts in Power BI with R

Yesterday we saw a beautiful example of panel charts with R. Today let me show you how to create the same (or even better) with Power BI & R. What you need: Power BI Desktop and R Raw data set – rem-data.csv Creating Panel Charts in Power BI with R Load CSV data in to […]

Continue »

Employee Performance Panel Charts – Excel vs. R [video]

Published on Aug 10, 2017 in Charts and Graphs, R programming
Employee Performance Panel Charts – Excel vs. R [video]

Recently, I had to make a bunch of panel charts. After wrangling with Excel (and a tiny bit of VBA) to create them, I wondered if we are suffering needlessly by being too loyal to Excel. I switched to R and could create these panel charts in almost no time (well, first I had to learn how to pivot the data using dplyr). Today, let me share the experience.

Continue »

SUMPRODUCT Vs. Power Query on Mt. KauKau

Published on Jun 15, 2017 in Learn Excel, Power Query
SUMPRODUCT Vs. Power Query on Mt. KauKau

When faced with tough problems I react in one of three ways

  1. Come up with ingenious solutions
  2. See if a simpler cheat solution is possible
  3. Sit back and ignore

For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.

When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.

Continue »

Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Published on Apr 26, 2017 in Excel Howtos, Learn Excel
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.

This is because you are hiring a person for new temp role even before their current one ended. See above picture.

So how to avoid making such hiring boo-boos.

Simple, using Excel of course.

Continue »

Figuring out Employee Churn with Power Query [HR Analytics]

Published on Mar 13, 2017 in Power Query
Figuring out Employee Churn with Power Query [HR Analytics]

Let’s say you are the people manager at ACME Inc. You are looking staff list for the months – January and February 2017. You see that we had 4,000 employees in Jan and 4,200 employees in Feb. So what is the churn?

  • Is it just 200?
  • Or is it the sum of people who left and who joined?
  • What if you want to find out how many people moved to new designations / departments or groups?

You see, churn is tricky to figure out.

So why not invite the pros? ie Power Query. 

Continue »

Decorate your TPS reports with spooky spider web chart [Halloween Fun]

Published on Oct 28, 2016 in Charts and Graphs
Decorate your TPS reports with spooky spider web chart [Halloween Fun]

It’s Halloween time. As adults, we can’t go trick or treating. We can of course dress up in costumes and entertain others. But what about the poor spreadsheets. Don’t they deserve some of this fun too?

Hell yeah! So I made a spider web generator in Excel. Just use it to make a spooky cob web pattern and add it to your report / dashboard / time sheet or whatever else. Surprise your colleagues.

Continue »

A quick tip about data analysis while on bike [Video + Personal]

Published on Oct 3, 2016 in Pivot Tables & Charts
A quick tip about data analysis while on bike [Video + Personal]

Wellington(NZ) sure is beautiful on a clear day. There are so many cool bike tracks, walking trails and beaches to keep you busy. Today, I went cycling on my usual route towards Makara beach. At the turn off point, I thought, “This is selfish. I can’t keep all this beauty to myself. I must share it with you.” So here we go.

A quick video about data analysis while on bike

Technically, I was not on bike when recording this video.  Watch it below or on our YouTube Channel.

Continue »

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 »

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 »