All articles with 'downloads' Tag

Find them and Extract them – VBA Macro

Published on Feb 10, 2017 in VBA Macros
Find them and Extract them – VBA Macro

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.

Continue »

Designing awesome financial metrics dashboard [tutorial]

Designing awesome financial metrics dashboard [tutorial]

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.

Continue »

Road Trip Planner Template [Excel Downloads]

Published on Feb 1, 2017 in Learn Excel
Road Trip Planner Template [Excel Downloads]

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.

Continue »

Untrimmable Spaces – Excel Formula

Published on Jan 12, 2017 in Excel Howtos, Learn Excel
Untrimmable Spaces – Excel Formula

Let’s talk about the untrimmable spaces.

We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.

So for example, if A1 has ” something and    one   more    ”

TRIM(A1)

will give “something and one more”

We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31).  Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.

The untrimmable spaces…?

The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”

Naturally I tried to TRIM().

But the data won’t budge. See above.

Hmm, let’s investigate why.

Continue »

An odd lookup problem [Formulas]

Published on Dec 6, 2016 in Excel Challenges, Learn Excel
An odd lookup problem [Formulas]

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

Continue »

Finding the closest school [formula vs. pivot table approach]

Published on Nov 18, 2016 in Excel Howtos, Learn Excel, Pivot Tables & Charts
Finding the closest school [formula vs. pivot table approach]

First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.

Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

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 »

Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

Published on Oct 11, 2016 in Charts and Graphs, Pivot Tables & Charts, VBA Macros
Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]

It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.

Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.

My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?

But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.

So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.

Continue »

Currency format Pivot fields with one click [Friday VBA]

Published on Oct 7, 2016 in Pivot Tables & Charts, VBA Macros
Currency format Pivot fields with one click [Friday VBA]

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

  1. Right click on any value field
  2. Go to Value field settings
  3. Click on “Number Format” button
  4. Choose Currency format
  5. Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

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 »

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 »

Visualizing Financial Metrics – 30 Alternatives

Published on Aug 30, 2016 in Analytics, Charts and Graphs
Visualizing Financial Metrics – 30 Alternatives

Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel.  30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.

First a quick note:

I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.

Continue »