All articles with 'downloads' Tag

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 […]

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.

Let’s say you got some text values and want to extract the amounts from them. Something like above.

How to go about it?

We could use a variety of techniques to extract the values.

• Formulas – not easy given the unstructured nature of data. But almost possible. See this for an example.
• VBA – possible, read this forum discussion few ways to do it.
• Power Query – at first glance it might seem tricky, but PQ makes this all too easy. Read on.

Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.

Take a look at final outcome above. Read on to learn more.

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.

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.

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

• Introduction to Excel tables
• How to use structured referencing
• Tables and Relationships in Excel
• Using lookups and other formulas with Excel tables
• Simple way to get absolute references in Tables
• Customizing table styles for awesome usability

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

Imagine you run an office furniture company. You want to stop reordering two brands of furniture – Relaxer (a type of chair) and Boca Top (a type of table). You currently have 20,000 Relaxer chairs and 5,000 Boca Tops. These are valued at $200,000 and $100,000 respectively. When sold, they will yield $100,000 and $25,000 gross profit. You are hoping to sell them off in 2 or 3 years. You forecast that we can sell off these as per some yearly schedule.

You need to analyze this and prepare a cash flow model.

Let’s learn how to answer such open ended questions using various analysis techniques in Excel.

It is Easter time, and that means time for another fun Easter Egg hunt in the spreadsheet. For the last 8 years (since 2009), I have been running Easter Egg hunt at Chandoo.org. This year too, I have prepared an exciting egg extraction enigma for you. Check it out.

Can you find all the 5 hidden cells in this workbook?

First step. Download this workbook. There are five worksheets, each containing one egg. You need to find the hidden cell in each worksheet.

Few days ago, I saw a beautiful homemade science experiment on Sand Pendulums on Bruce Yeany‘s YouTube channel. Go ahead and check it out. It is a cool project to do with your kids.

I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.

Simulating sand pendulum pattern in Excel

Take a look at the final simulation above. This is what we will create in Excel.

Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly.  One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”

The idea is that when you have a direct, simple way to do something, you should use it.

Nose and pivot tables… how are they connected?

We are coming to the point. Read on for full case study and solution.

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. 

I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said ministry of MAGIC!!! ). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”

I explained him how to do this while chewing mouthfuls of rice & veggies.

But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.

So here we go.

In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.

Thanks Chandeep.

We recently went on a road trip around parts of New Zealand’s north island. We have been to Taupo, Rotorua, East Cape and Napier. It took us 2 weeks, we drove more than 2,000 km and spent almost NZ $3,000 on the trip. Of course, being a data nerd, I made a plan of the trip in Excel and that helped us budget for this.

After getting back to home, I thought it would be fun to polish the planner workbook and share it with you all so you too can plan a fabulous road trip. So here we go.