All articles in 'Learn Excel' Category

Making your dashboards interactive [Dashboard Essentials]

Published on Aug 2, 2012 in Charts and Graphs, Excel Howtos, Learn Excel
Making your dashboards interactive [Dashboard Essentials]

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Continue »

Welcome to Chandoo.org – A short introduction to our site

Published on Jul 27, 2012 in blogging, Learn Excel
Welcome to Chandoo.org – A short introduction to our site

Welcome to Chandoo.org. Thank you so much for taking time to visit us.

Over the last few weeks, we have quite a few new members to the site. Its good time I said hello and introduced this site to you.

PS: If you have been following chandoo.org for a while, you can still find useful information in this post. So read on.

Continue »

How do you explain Excel to a small kid? [poll]

Published on Jul 13, 2012 in Learn Excel
How do you explain Excel to a small kid? [poll]

When I was in Perth, I visited Hui’s house one day. Lovely, Hui’s daughter (who is about 14) asked Hui how he knew me. So Hui told that we both share a passion for Excel and that;s how we got to know each other. Then she asked, What is Excel?

At this point, we both tried to explain what Excel is to her in a few ways with no success. Later Hui came up with a brilliant explanation.

He said, Excel has lots of small calculators all interconnected, so that you can do any sort of calculation.

So here is a challenge for you. How would you explain Excel to a small kid (computer literate, but not yet in college).

Continue »

Find the last date of an activity

Published on Jul 3, 2012 in Formula Forensics, Learn Excel
Find the last date of an activity

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like above.

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

Continue »

How to make a Spoke Chart

How to make a Spoke Chart

Have you ever wanted to make your own Custom Chart type ?
MarnieB was asked by her boss to make a Spoke Chart !

This post will explain how you can make a custom Excel Spoke Chart and introduce you to techniques that can be used to make other custom chart types.

Continue »

Check if a list has duplicate numbers [Quick tip]

Published on Jun 28, 2012 in Excel Howtos, Learn Excel
Check if a list has duplicate numbers [Quick tip]

A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.

Today, lets learn a simpler formula to check if a list has duplicate numbers.

Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.

Continue »

Do you work on Excel? How much salary you make? [Surveys]

Published on May 25, 2012 in Learn Excel
Do you work on Excel? How much salary you make? [Surveys]

Ever wondered how much your Excel skills are worth?

Here is 2 step procedure to find out.
1) Complete this survey. Tell me how much you make & what you do.
2) Wait for a week and we will tell you how much Excel professionals are earning all over the world :)

PS: You can be anonymous.

Continue »

Highlight due dates in Excel – Show items due, overdue and completed in different colors

Published on May 22, 2012 in Excel Howtos, Learn Excel
Highlight due dates in Excel – Show items due, overdue and completed in different colors

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?

Continue »

Highlight Employees by Performance Rating – Conditional Formatting Challenge

Published on May 18, 2012 in Excel Challenges, Learn Excel
Highlight Employees by Performance Rating – Conditional Formatting Challenge

So who is up for an Excel challenge?

Shelly, who is an HR Manager sent this distress call last week,

“I have a group of employees- lets say 100 employees. Each employee has a performance rating attached to them. I want to divide the group by 5%, 15%, 65%, 10%, 5% based on their performance rating.

And that is our challenge today. Read on & help Shelly.

Continue »

Please Enroll in our Excel & Dashboards Masterclass – Melbourne

Published on May 16, 2012 in Excel Workshops, Learn Excel

Hello folks,

I have a quick announcement. As you may know, I am doing a set of Excel & Dashboards Masterclasses in Sydney, Melbourne & Brisbane. We had a fantastic session in Sydney (with 18 delegates). We are sold out in Melbourne & have 2 spots left in Brisbane. But we kept getting requests for more in Melbourne. So we have added an additional session in Melbourne. This is right after Queen’s birthday – on 12th & 13th of June. Please use below links to enroll for the masterclass if you are interested.

Reviews from our Sydney masterclass delegates
We asked the delegates of our Sydney masterclass to tell us how they liked it. Here is a short video with their reviews (4 min):

Continue »

VBA Move data from one sheet to multiple sheets

Published on May 14, 2012 in Automation, Excel Howtos, Learn Excel, VBA Macros
VBA Move data from one sheet to multiple sheets

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

Continue »

Excel Links – Live from Sydney Edition

Published on May 1, 2012 in excel links
Excel Links – Live from Sydney Edition

Hello folks,

My flight to Sydney has been lengthy but fun. I have reached here on Sunday morning (8AM) and spent most of the day with Danielle’s family. (Danielle is the founder of Plum Solutions. She is the one who invited me to Australia and planned this whole experience for me).
On Monday (30th April), I went exploring the city on foot. I had coffee in the beautiful Queen Victoria Building, attended 1PM church service at the magnificent St. Mary’s cathedral, walked thru Hyde park, went to Sydney central station, took at sneak peek at the new Apple store in down town, got back to my hotel, walked to Opera house to meet up with our readers.

Reader meetup at Sydney
We had 6 people turn up for the meetup. It was fun talking about Excel & our journey with these wonderful folks. Here is a pic (you can see the harbor bridge in background & 8 awesome Excel users in foreground, Click on it to enlarge).

Continue »

Send mails using Excel VBA and Outlook

Published on Apr 23, 2012 in Automation, Excel Howtos, Learn Excel, VBA Macros
Send mails using Excel VBA and Outlook

Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Read on…

Continue »

Creating Customer Service Dashboard in Excel [Part 3 of 4]

Published on Apr 18, 2012 in Charts and Graphs, Learn Excel
Creating Customer Service Dashboard in Excel [Part 3 of 4]

Welcome back. In third installment of our Customer Service Dashboard series, we will learn how to construct the charts in our dashboard. We will understand the sparklines, traffic lights & dynamic chart setup. To help you learn better, I have recorded a short video too. Go ahead and enjoy.

Continue »

Comprehensive Guide to VLOOKUP & Other Lookup Formulas

Published on Mar 30, 2012 in Learn Excel
Comprehensive Guide to VLOOKUP & Other Lookup Formulas

This week many Excel bloggers are celebrating VLOOKUP week. So I wanted to chip in and give you a comprehensive guide to VLOOKUP & Other lookup formulas. Read on …,

What is VLOOKUP Formula & how to use it?

I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.

Continue »