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

Latest Tips & Tutorials

CP002: VTALKUP – 5 Excel lookup functions demystified + 4 Resources for you

Subscribe: Apple Podcasts | Spotify | RSS

In the second session of Chandoo.org Podcast, We will be learning how to use 5 Excel lookup functions.

What is in this session?
In this session, we tackle one of the most important areas of Excel. The lookup functions.

You will learn,

Why lookup functions are necessary
5 Important lookup functions in Excel – VLOOKUP, HLOOKUP, LOOKUP, MATCH & INDEX
When & how to use each of these 5 functions?
Extreme scenarios:
- What happens when the value you are looking up is not there?
- What if too many items match the lookup value?
- What if you have too many conditions in the lookup criteria?
Using IFERROR function
Re-cap of the new powers you acquired
4 Resources for you to learn lookup functions better

Read This »

March 13, 2014

You can move formula help box with your mouse!!! [quick tip]

One of the most useful features of Excel is formula help box. You know the little yellow box that appears as soon as you start typing a formula in a cell. I use this all the time to understand what the syntax of a particular function is, what parameters to pass etc.

Although I love it, sometimes it does get in the way when writing formulas. Because the help box sits on top of my data, often I find it hard to know which cell to link to.

Solution?!?

Simple. Use your mouse to move away the help box wherever you want.

Read This »

March 12, 2014

CP 001: Chandoo.org Podcast First Episode – Introduction, What to expect, Show formalities & Special gift

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Chandoo.org Podcast is here….

Friends, fans & supporters of Chandoo.org,

I am so happy to add another dimension to our website. Chandoo.org podcast is finally here. You can listen to the inaugural episode by using the audio player above.

Read This »

March 6, 2014

Excel links – What is your next Excel book edition?

Outside of a dog, a book is a man’s best friend. Inside of a dog it’s too dark to read. -Groucho Marx

We don’t have a dog (yet), but we have a lot of books. Anytime I am in a bookshop, I feel like a mosquito in fat-camp. So you can imagine me smacking my lips when I saw your suggestions for favorite Excel books recently. There were more than 50 different books recommended by our readers. Of course I cannot buy all the 50 in one go. That would make Jo (my wife) angry. So I bought one that most of you recommended.

Ctrl + Shift + Enter by Mike Girvin

It is a book about array formulas, advanced formulas and data analysis techniques. I am eagerly waiting for the delivery to devour it. Once I am done with it, I will post a review here.

What about you? What Excel book are you reading now? Please share in comments.

Read This »

March 5, 2014

Chandoo.org Podcast: Launching on March 6th

Hi folks,

I am super-excited to tell you that Chandoo.org Podcast will be launching on 6th of March (Thursday). First episode is already recorded and I am getting ready for the big launch.

Name of the podcast

Last week I asked you what should we call our podcast. And I got more than 250 suggestions (150+ thru comments another 100 thru email). While the suggestions ranged from funny (chanpoo, addict.xls) to word plays (Excelent cast, Lord of the cells, Speaking Cells) to outright brilliant (XL.AM remember AM radio?). Some of the suggestions were crazy too: Word on the sheet, YouChandooIt, Didasko.xlsx – greek word “to impart knowledge”.

Thanks everyone for the ideas and suggestions.

A majority of you suggested that we call it Chandoo.org Podcast. This keeps the name generic, sticks to our brand and sets us apart. So that is what we will call it.

The full name of the show is Chandoo.org Podcast – Become Awesome in Data Analysis, Charting, Dashboards & VBA using Excel

And our first episode will be launched on Thursday, 6th of March.

Read This »

March 4, 2014

Handle Volatile Functions like they are dynamite

If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.

In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!

So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?

Read This »

March 3, 2014

Can you extract first name & last name from email address? [Formula Challenge]

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Read This »

February 27, 2014

Robust Dynamic (Cascading) Dropdowns Without VBA

Recently I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like this: …and we saw that users could

Read This »

February 25, 2014