Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Start your bar charts from zero – Excel geeks screaming at you from mountain top

Here is a simple but vital charting rule.

Start your bar (or column) charts from zero.

To illustrate why you should do this, let me share a personal example.

Over the weekend, the Jon Peltier visited Wellington. He is staying with Jeff (who occasionally guest blogs on Chandoo.org). On Sunday, we all decided to hike up a small mountain near my house for a leisurely family picnic.

While on the top of the mountain, Jo (my wife) took a few pics of us three Excel geeks. As we were standing on a sloping mountain face this is how the pictures look.

Looking at the picture on left, you would confidently say that I am way shorter than other two. But picture on right tells a different story.

Read This »

May 15, 2017

Selective Sub-totals in Pivot Tables [Quick Tip]

Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like above.

How to show selective sub-totals in Pivot Tables

Read This »

May 2, 2017

Avoid Hiring Boo-boos with Excel – COUNTIFS for the win

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.

Read This »

April 26, 2017

Relative References in Excel Tables

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.

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.

Read This »

April 21, 2017

Modelling Inventory Run Rate & Cash Flows using Excel

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.

Read This »

April 19, 2017

There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs]

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.

Read This »

April 14, 2017

How many employees are on leave during Easter holidays [Homework]

Easter is around the corner. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and almost forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.

Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.

Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. A snapshot of your data (table name: lvs) is shown above.

Click here to download the sample file.

You want to answer below three questions:

How many employees are on leave during Easter holidays (14th of April to 28th of April)?
How many employees are on approved vacation during Easter holidays?
How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range ninja

Read This »

April 7, 2017

Hide columns one one tab same way as they were in another place [quick tip]

One of the regular reporting tasks I do involves a manual step I hated. It goes like this:

Dump several columns of data in the template file.
Hide a particular set of columns (these are not together, so must be done one at a time or with CTRL+selection)
Save and publish the file.

After doing this manually for last few fortnights, today I wanted to automate the column hide process. I was about to write a VBA macro to clone the hide settings from one workbook to another. But then I thought, may be paste special can be of use.

And what do you know. It does exactly that.

Read This »

April 5, 2017