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 reading
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:
One of the regular reporting tasks I do involves a manual step I hated. It goes like this:
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 reading
Few days ago, I saw a beautiful homemade science experiment on Sand Pendulums on Bruce Yeany‘s YouTube channel. Go ahead and check it out. It is a cool project to do with your kids.
I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.
Simulating sand pendulum pattern in Excel
Take a look at the final simulation above. This is what we will create in Excel. Continue reading
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 reading
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?
You see, churn is tricky to figure out.
So why not invite the pros? ie Power Query. Continue reading
As part of a my ongoing consulting gig, I often run painfully long queries on SQL Server to fetch data. This data obviously ends up in Excel for further analysis. Now, some of these queries return NULL values in several columns (did I tell you that the queries have a gazillion left joins on them, oh yeah, […] Continue reading
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),
How would you write formulas to get these answers? Continue reading
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 reading
I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said ministry of MAGIC!!! ). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”
I explained him how to do this while chewing mouthfuls of rice & veggies.
But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.
So here we go. Continue reading
