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

Latest Tips & Tutorials

Conditionally Format Chart Backgrounds

Learn two techniques to conditionally format the background of a chart based on some external value.

March 27, 2015

Formatting shortcuts for keyboard junkies

A lot of analysts swear strong allegiance to keyboard shortcuts. But when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting is a mouse-heavy activity.

But we can use a few simple & effective shortcuts to zip through various day to day formatting tasks. Let me share my favorite formatting shortcuts.

Read This »

March 17, 2015

Share your favorite Excel tip & you could win Beats Headphones [Podcast Anniversary Celebrations]

Yay, bring out the birthday hats, candles, cake & confetti. Chandoo.org podcast is 1 year old.

In this one year, we had 31 sessions, 4 guest appearances, 390,000 downloads & lots of 5 star reviews. Thank you so much for making it a grand success so far.

To celebrate the occasion, I want to reward 3 lucky listeners of our podcast.

How to participate in this contest?

Simple. Figure out what your favorite Excel tip is.
Open your smart phone’s voice recorder app
1. on iPhone use the Voice Memos app
2. on Andriod use sVoice or voice recorder widgets
3. If you cant find these apps, just Google record audio <your phone name>
Record the tip (less than 3 mins duration)
Please say your name at the end of recording.
Send the audio file by email to hello@chandoo.org
Do so before end of day 16 March 2015 (Monday)

Once I collect all the tips, I will feature some of them in next episode of Chandoo.org podcast.

Read This »

March 13, 2015

Check if 2 ranges have same values (set equality problem)

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?

Read This »

March 10, 2015

Celebrate Holi with this colorful Excel file

Today is Holi, the festival of colors in India. It is a fun festival where people smear each other with colors, water balloons, tomatoes and sometimes rotten eggs. This year we wanted to play with only water guns, but kids vetoed that idea vehemently. So we ended up driving to my sister-in-law’s place to play with colors (there were no rotten eggs or tomatoes, thankfully).

Let me smear a few colors on you

I would love to splash a jug full of color water on you and say Happy Holi. But the internets have not advanced thus far. So I am going to give you the next best option.

An Excel workbook to play holi

Read This »

March 6, 2015

CP031: Invisibility Tricks – How to make things disappear in Excel?

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 31st session of Chandoo.org podcast, let’s disappear.

What is in this session?

Spreadsheets are complex things. They have outputs, calculation tabs, inputs, VBA code, from controls, charts, pivot tables and occasional picture of hello kitty. But when it comes to making a workbook production ready, you may want to hide away few things so it looks tidy.

That is our topic for this podcast session.

In this podcast, you will learn

Quick announcements first anniversary of our podcast etc.
Hiding cells, rows, columns & sheets
Hiding chart data points
On/off effect with form controls, conditional formatting
Making objects, charts, pictures disappear
Disabling grid-lines, formula bar & headings
Hiding things in print

Read This »

March 5, 2015

CP030: Detecting fraud in data using Excel – 5 techniques for you

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.

What is in this session?

In the wake of hedge fund scams, accounting frauds and globalization, We, analysts are constantly second guessing every source of data. So how do you answer a simple question like, “am I being lied to?” while looking at a set of numbers your supplier has sent you.

That is our topic for this podcast session.

In this podcast, you will learn

Quick announcements about 50 ways & 200k BRM
Introduction to fraud detection
5 techniques for detecting fraud
- Benford’s law
- Auto correlation
- Discontinuity at zero
- Analysis of distribution
- Learning systems & decision trees
Implementing these techniques in Excel
A word of caution

Read This »

February 19, 2015

Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.