Archive for June, 2015

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 37th session of Chandoo.org podcast, Let’s debug ’em #VALUEs & #N/As.

What is in this session?

In this podcast,

• Introduction to Excel formula errors
• The easy kind: syntax errors
• The triky ones: # ERRORs
• Fixing errors – using IFERROR & ISERROR
• Error checking & debug options
• Using Errors deliberately – charts & data validation
• A challenge for you – produce #NULL error
• Conclusions

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)

Here is a fun way to use Paste Special to quickly multiply everything in a range with 1.1 (why 1.1? Well, imagine you have a report with everything in US $s and your boss wants to see the numbers in Australian $s…)

Since your report has different formulas for each cell, you can’t multiply first cell with a rate variable and drag it down. You have to manually edit each formula and add *rate at the end of it.

Oh wait…, you can use Paste Special.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 36th session of Chandoo.org podcast, Let’s follow the trend.

What is in this session?

In this podcast,

• A quick trip to down under
• What is trend analysis
• 4 types of common trends
  • linear
  • curve
  • cyclical
  • strange
• Doing trend analysis in Excel – the process
• How to use trend analysis results
• Conclusions

What functions you believe Excel is missing?

Here is a simple but tricky problem. Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy. As this is a one person production facility, Billy has the flexibility to choose his working hours. At the […]