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

Latest Tips & Tutorials

Excel Howtos

Lenient lookup [Advanced Formula Trick]

We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?

Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can’t convince VLOOKUP to act nice.

Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here?

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let’s learn how to do lenient lookups.

Read This »

September 13, 2018

Master Class

5 tips: Power Query for Accountants (and finance people) – Free Masterclass

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.

In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.

For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

Read This »

September 3, 2018

Excel Challenges

When is the next Monday? [Homework]

Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.

If so, take up this homework problem and post your answers in comments.

Assuming you have an input date in cell A1,

What is the formula for finding next Monday?
What is the formula for finding first Monday of next month?

Post your answers in comments

Read This »

August 31, 2018

Excel Howtos

Performing Maths in Microsoft Word (In an Excel Blog)

Learn to how perform Maths on Tables of Numbers in Microsoft Word.

Read This »

August 28, 2018

Power BI

What is Power BI, Power Query and Power Pivot?

In this installment of Power Mondays, let’s go the very basics of it all.

What is Power BI?

Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc.

Read This »

August 27, 2018

Power Query

How to get a random sample of data with Power Query

Today’s Power Monday trick is about Power Query. This is based on my experience of working with large volumes of data.

Today I have been building a hotel dashboard (more on this later). As part of the dashboard, I wanted to show a random sample of user reviews. Reviews database had quite a few rows, so I wanted to extract a randomized sample of 100 reviews and show them in the report. When you refresh the report (Data > Refresh), then a new set of reviews will be fetched and shown.

Let’s learn how to generate a random sample with Power Query in this article.

Read This »

August 20, 2018

Charts and Graphs

Make funky and creative hand-drawn chart in Excel – Quick tutorial

Charts are great way to tell story about what is going on in your business. But they also feel a bit too impersonal and meh. How about adding your personality to them? I don’t mean making them tall, dark and pretty. I mean using hand-drawing style to make them pop out. Something like above example of hand-drawn chart.

The best thing is, You don’t need to actually draw these charts by hand. We can use a powerful charting trick to get these charts automatically generated (and linked) to your data. Interested? Read on to learn how to create hand-drawn charts in Excel.

Read This »

August 16, 2018