All articles with 'Learn Excel' Tag

SUMPRODUCT Vs. Power Query on Mt. KauKau

Published on Jun 15, 2017 in Learn Excel, Power Query
SUMPRODUCT Vs. Power Query on Mt. KauKau

When faced with tough problems I react in one of three ways

  1. Come up with ingenious solutions
  2. See if a simpler cheat solution is possible
  3. Sit back and ignore

For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.

When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.

Continue »

Jo’s first keyboard shortcut

Published on Jun 12, 2017 in Keyboard Shortcuts

Jo, my lovely wife quit her job as my partner in crime at Chandoo.org recently and took up a lucrative position at NZ govt. agency. The other day I asked her “how was your day?” when she got home. She smiled and said, “I learned my first Excel shortcut!”.
Guess what it is?

F4.

That is right. The mighty F4 key. You can use it to repeat any action.

Jo was using it to insert rows in her workbook. After inserting first row (using CTRL+ of course), she would press F4 to add more rows as needed.

Continue »

Selective Sub-totals in Pivot Tables [Quick Tip]

Published on May 2, 2017 in Excel Howtos, Pivot Tables & Charts
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

Continue »

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

Published on Apr 26, 2017 in Excel Howtos, Learn Excel
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

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.

Continue »

Modelling Inventory Run Rate & Cash Flows using Excel

Published on Apr 19, 2017 in Analytics, Charts and Graphs
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.

Continue »

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

Published on Apr 14, 2017 in Excel Challenges
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.

Continue »

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

Published on Apr 5, 2017 in Excel Howtos
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.

Continue »

Reshaping your data easily – Case study [Pivot tables FTW]

Published on Mar 16, 2017 in Learn Excel, Pivot Tables & Charts
Reshaping your data easily – Case study [Pivot tables FTW]

Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly.  One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”

The idea is that when you have a direct, simple way to do something, you should use it.

Nose and pivot tables… how are they connected?

We are coming to the point. Read on for full case study and solution.

Continue »

Figuring out Employee Churn with Power Query [HR Analytics]

Published on Mar 13, 2017 in Power Query
Figuring out Employee Churn with Power Query [HR Analytics]

Let’s say you are the people manager at ACME Inc. You are looking staff list for the months – January and February 2017. You see that we had 4,000 employees in Jan and 4,200 employees in Feb. So what is the churn?

  • Is it just 200?
  • Or is it the sum of people who left and who joined?
  • What if you want to find out how many people moved to new designations / departments or groups?

You see, churn is tricky to figure out.

So why not invite the pros? ie Power Query. 

Continue »

Lookup most frequent item [Homework]

Published on Feb 17, 2017 in Excel Challenges
Lookup most frequent item [Homework]

Here is an interesting problem to keep your brain cells fight boredom on this Friday & weekend.

Let’s say you have some data like above.

And you want to know, for a given customer name (in cell G4),

  • What is the most frequent quantity?
  • What is the most often purchased item?

How would you write formulas to get these answers?

Continue »

Use CTRL to make copies of worksheets quickly

Published on Feb 15, 2017 in Keyboard Shortcuts, Learn Excel
Use CTRL to make copies of worksheets quickly

The other day, I found myself making copies of a templated report worksheet. After trying the usual route of “right click on source sheet, select move or copy, check create a copy and press OK” a few times, I thought “well that is asinine.” So I figured, may be CTRL+Drag will create a copy. And what do you know, it does.

So that is our quick tip for the day. Whenever you need to make a copy of something, simply hold CTRL key and drag the thing.

It works for charts, drawing shapes, worksheets and even ranges.

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 »

Check for two out of three conditions (Homework)

Published on Jan 13, 2017 in Excel Challenges
Check for two out of three conditions (Homework)

Time for some logic check.

Suppose, you have three logic values in A1:C1 (TRUE or FALSE values in each cell)

You need to find out if ONLY two of these values are TRUE.

How would you write the formula?

Got an answer? Awesome. Just post your formula in the comments. Let’s see how much variety we can get from all of our readers.

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 »