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

Filter all records for November or 11AM or 2017 [quick tip]

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

Read This »

December 4, 2017

Thank you, we have a home in New Zealand

Time for a quick residential break from all the Excel awesomeness. I have an exciting news to share with you all.

We have a home in New Zealand.

In the middle of 2016, we moved to New Zealand. We have been living in windy, lovely and beautiful Wellington ever since. After renting a house and sampling life in NZ, we decided to call it home for next few years. The next natural step is to purchase a home and move in.

Read This »

November 22, 2017

Which power plan is best for me? Excel for everyday problems

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Read This »

November 9, 2017

Conditional Rank, the easy way [quick tip]

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Read This »

October 20, 2017