All articles with 'downloads' Tag

Mustache and Excel?!? Sounds as unlikely as 3D pie charts & Peltier. But I have a story to tell. So grab a cup of coffee and follow me.

Today, lets talk about how to construct a dynamic chart that can show us how much progress we have made against a financial goal (in this case, accumulating a big chunk of money). I call this growing mustache chart, inspired from the wonderful Mr. Money Mustache.

So who is up for a challenge? Can you use only formulas and extract dates buried inside text?

1. Download this file.
2. In column C, write a formula such that you can extract the date in column B
3. If you succeed, post your solution here as a comment.
4. If you fail, drink some coffee, start afresh.

During London 2012 Olympics, Usain Bolt reached the 100mts finish line faster than anyone in just 9.63 seconds. Most of us would be still reading this paragraph before Mr. Bolt finished the race.

To put this in perspective, NY Times created a highly entertaining interactive visualization. Go ahead and check it out. I am sure you will love it.

So I wanted to create something similar in Excel. And here is what I came up with.

Watching the Olympic athletes run & jump all I could think of is,

• What should I eat to jump & sprint like that?
• How come I never heard about steeple chase?
• Should we really have 3 bullet points in all lists?

But I digress. Coming back, when watching one of those hurdles events, I got an idea as sharp as Chinese table tennis team.

Why not create a hurdles game in Excel to measure how good you are with keyboard?

So ladies & gentleman, let me present you our very own Olympics hurdle run.

Whenever we deal with large amounts of data, one of the goals for analysis is, How is this data distributed?

This is where a Box plot can help. According to Wikipedia, a box plot is a convenient way of graphically depicting groups of numerical data through their five-number summaries: the smallest observation (sample minimum), lower quartile (Q1), median (Q2), upper quartile (Q3), and largest observation (sample maximum)

Today, let us learn how to create a box plot using MS Excel. You can also download the example workbook to play with static & interactive versions of box plots.

Ladies & gentleman, put on your helmets. This is going to be mind-blowingly awesome.

See how many different ways are there to analyze Excel salary data. Look at these 66 fantastic, beautifully crafted dashboards and learn how to one up your dashboard awesomeness quotient.

When looking at a big table of analysis (or data), it would make our life simpler if the selected cell’s column and row are highlighted, so that we can instantly compare and get a sense of things. Like above.

Who doesn’t like a little highlighting. So lets learn how to do highlighting today.

Did I tell you I love tennis? Some of my personal heroes & motivators are tennis players. And as you can guess, I admire Roger Federer. Watching him play inspires me to achieve more. So last night when he lifted Wimbledon trophy for 7th time, I wanted to celebrate the victory too, in my style. So I made an interactive timeline chart in Excel depicting his victory.

Jo (wife) likes to watch Masterchef Australia, a cooking reality show every night. Even though I do not find contestant’s culinary combats comforting, occasionally I just sit and watch. You see, I like food.

The basic premise of the program is who cooks best in given time. To tell people how much time is left, they use a clock that indicates how much time is left (much like a stop clock, with a small twist).

One day, while watching such intense battle, my mind went

It be cool to make such a clock using hmm… Excel?

While I cannot share my snapper (or pretty much any other food item) with you, I can share my Masterchef style Excel clock with you. So behold,

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like above.

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.

Today, lets learn a simpler formula to check if a list has duplicate numbers.

Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.

Last week we discussed how to extract numbers from text in Excel using formulas. In comments, quite a few people suggested that using VBA (Macros) to extract numbers would be simpler.

So today, lets learn how to write a VBA Function to extract numbers from any text.

Its contest time again! Put on your creative hats & bring your Excel skills to the game.

Analyze more than 1900 survey responses & present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundle (valued at $299).

Sounds interesting? Read on.

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

During a recent training program, one of the students asked,

Thermo-meter chart is very good to show how actual value compares with target (or budget). But how can we add another point for say Last Year value to the chart with out cluttering it.

Something like above.

Sounds interesting? Read on