All articles with 'Learn Excel' Tag

To make friends in a new town hit the bars – Old saying.
To make sense of a new data-set, make bar charts – New saying.

Bar charts (or column charts if you like your data straight up) are vital in data analysis. They are easy to make. But one problem. By default, a bar chart show the original data in reverse order.

See the above example.

Unfortunately, we humans read from top to bottom, not the other way around.

Conditional formatting is one of the most powerful & awesome features of Excel. It is very easy to setup. Naturally, people use it extensively. But the default conditional formatting rules can clutter your reports. Here is one tip that can declutter your reports.

Just show the formatting, not values.

See the above report.

Here is a simple way to enhance your Excel charts – use shapes & pictures in your charts.

We will learn how to create something like above.

Looks interesting? Read on…

By now, we know how to remove duplicates from data. You can use the Remove Duplicates button to do that.

But do you know that we can use remove duplicates button to get rid off duplicate combinations too?

Remove duplicate combinations – Tutorial

To remove duplicate combinations in your data, just follow below 4 steps:

1. Select your data
2. Click on Data > Remove Duplicates button
3. Make sure all columns are checked
4. Click ok and done!

See this demo:

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 41st session of Chandoo.org podcast, Let’s take a trip to data hell and meet 6 ugly, clumsy, confusing charts. I am revisiting a classic Chandoo.org article – 6 Charts you will see in hell.

What is in this session?

In this podcast,

• Quick announcement about Awesome August
• 6 charts you should avoid
• 3D charts
• Pie / donut charts with too many slices
• Too much data
• Over formatting
• Complex charts
• Charts that don’t tell a story
• Conclusions

A significant portion of our time in Excel is spent navigating between various ribbon tabs and clicking buttons. Wouldn’t it be better if you can combine all the frequently used features in to one ribbon? Something like above:

Read on to learn how to create, edit and share custom ribbons in Excel.

Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.

Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores.  Read on to learn the techniques.

We all know that area charts are great for understanding how a list of values have changed over time. Today, let’s learn how to create an area chart that shows different colors for upward & downward movements.

The inspiration for this came from a recent chart published in Wall Street Journal about Chinese stock markets.

We will try to create a similar chart using Excel. We are going to create the above chart in Excel.

Looks interesting? Read on…

On Wednesday (15th July), I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it).  It was a huge success. More than 1,100 people attend the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 38th session of Chandoo.org podcast, Let’s optimize data to ink ratio of your charts.

What is in this session?

In this podcast,

• Announcements
• What is Data to Ink Ratio?
• Obvious ways to optimize Data to Ink Ratio
• More ways to optimize Data to Ink ratio
• Highlighting what is important
• Conclusions

Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.

So today let me share with you 25 shortcuts, productivity hacks and tricks to help you be even more awesome.

It is almost weekend. I am sure most of you have plans (if you are USA, wish you happy 4th of July). As for me, I am going on a 80KM (50 mile) bicycle trip to a nearby lake to watch birds on Saturday morning. On Sunday, we (kids & I) are planning to make a scrapbook from our Australian experiences.

So let me keep this nice & simple.

What is the coolest thing you made with Excel?

Go ahead and share your answers in the comments area.

Here is a situation all too familiar.

You are looking at a spreadsheet full of data. You need to analyze and tell a story about it. You have little time. You don’t know where to start.

Today let me share 15 quick, simple & very powerful ways to analyze business data. Ready? Let’s get started.

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

1. Add region as report filter and filter for the region you want.
2. Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

Read more to learn all about slicers

Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.

So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.

Calculating Bill’s total working hours (video)