Kategorienarchive: Chandoo.org

How to find sum of top 3 values based on filtered criteria [video]
Zum Original Blog Chandoo.org

Today, lets tackle an interesting problem.
Lets say you are looking at some data as shown aside and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?
This article is inspired from a question asked by acpt22 in our forums.
Sum of top 3 values based on filtered criteria [video]
Watch below video to understand how to find sum of top 3 values using formulas & pivot tables.

Watch this video on our YouTube channel.
Download Example Workbook
Click here to download example file and play with it. …
Zum Original Blog
Autor: Chandoo

How to transpose a values in a row to column using formulas… [Quick tip]
Zum Original Blog Chandoo.org

This is interesting, I am in Columbus to meet one of my college friends. I remember him as a very meticulous person from college days. So it is no surprise when he showed me his massively impressive finance tracker last night. He has been tracking expenses, income, credit card payments and gas (petrol) consumption since 2008. Very impressive indeed.
Then out of blue he said, he has a problem with his spreadsheet. In this own words,
When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in …
Zum Original Blog
Autor: Chandoo

I am meeting Mr. Excel aka Bill Jelen tomorrow. What do you want to ask him? (book giveaway too)
Zum Original Blog Chandoo.org

One of the beautiful things about working on internet is you know so much about people even before you meet them first time. I think I first heard about Mr. Excel in 2006, when I started my career as business analyst. I landed on mrexcel.com while searching for something related to doing cluster analysis using Excel. In a way, mrexcel.com inspired me to share my thoughts and techniques on Chandoo.org.
So it wont be an understatement when I say, I feel like a kid in candy store knowing that Bill Jelen aka Mr. Excel is just a few …
Zum Original Blog
Autor: Chandoo

Excel Links – from USA edition
Zum Original Blog Chandoo.org

Hello there,
My travel to US was fantastic and fun. Many thanks to Jocelyn & Rob Collie, who found a nice apartment for us and made sure we are settled down in almost no time. Weather has been excellent so far too with lots of sunshine and blue skies. We went to a nearby park the other day and kids really loved walking on trials and exploring.
Gentle reminder
Today is last day to sign-up for my USA Masterclass
Today is last day to join my USA Advanced Excel & Dashboards masterclass with early bird discount. If you live …
Zum Original Blog
Autor: Chandoo

How to create a column chart with background image in Excel ?
Zum Original Blog Chandoo.org

Tony sends this chart and asks if it can be done in Excel.
It sounded like a good challenge for a lazy Sunday morning. So here we go. (Posting it on Monday).

Now I could not get an oil rig photo or that data. So I made up few numbers and used a photo of Flinders street station I took when I was in Melbourne last year.
Step 1: Arrange the data.
Arrange the data like this.

Step 2: Create a column chart
Select the data, insert a stacked column chart (why not a regular column chart?, you will understand in a …
Zum Original Blog
Autor: Chandoo

Thank you, we are flying to US tonight
Zum Original Blog Chandoo.org

This is a personal note, not an Excel tip. So grab a cup of coffee, relax and read on.
As I pack our bags and run the last checks, one thought keeps crossing my mind. ‘How impossible all this sounded just a few years ago’.
When I quit my well paying corporate job in April, 2010 to embark on this exciting journey of making you awesome in Excel, I never thought I would one day take my family and visit USA. I had bigger worries. I just became a dad to twins. Both Jo & I quit our jobs. …
Zum Original Blog
Autor: Chandoo

10 Rookie mistakes to avoid when making dashboards [video]
Zum Original Blog Chandoo.org

Are you making these 10 rookie mistakes when creating dashboards?
Watch below to video to understand what these mistakes and how to avoid them.
10 Rookie mistakes to avoid when making dashboards [23 mins]

More resources on Excel dashboards
Excel dashboards – Information, examples & tutorialsCreating a KPI dashboard using Excel – 6 part tutorialSales dashboards – 32 examples & downloads
Do you make any of these mistakes?
When I made my first dashboard in Excel (a balance scorecard for our department), I made almost all of these mistakes. Today, I am more careful. But I still forget to do print settings or make small …
Zum Original Blog
Autor: Chandoo

How to create an Interactive Chart in Excel? [Tutorial]
Zum Original Blog Chandoo.org

Imagine you have a worksheet with lots of charts. And you want to make it look awesome & clean.
Solution?
Simple, create an interactive chart so that your users can pick one of many charts and see them.
Today let us understand how to create an interactive chart using Excel.
PS: This is a revised version of almost 5 year old article – Select & show one chart from many.
A demo of our interactive Excel chart
First, take a look at the chart that you will be creating.

Feeling excited? read on to learn how to create this.
Solution – …
Zum Original Blog
Autor: Chandoo

Chart for wall hygrometric physic (or how to create a chart with custom x axis intervals?)
Zum Original Blog Chandoo.org

Livio, one of our readers from Italy sent me this interesting problem in email.
I would like to prepare an xy linear graphic as representation of the variation of temperature trough a wall between two different bulk temperature i.e. outside and inside a house. This graphic should show the temperature gradient trough the wall thickness. The wall is normally made by different construction materials (different layers, as bricks, insulation, …..) and so the temperature change but not as a straight line with only one slope, instead as few lines with different slopes (see below figure) Calculations are not difficult, …
Zum Original Blog
Autor: Chandoo

Learn Top 10 Excel Features
Zum Original Blog Chandoo.org

Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,
I downloaded all the 120+ comments data
I home brewed a large cup of coffee and started gulping it.
I started analyzing the comments
So here are the top 10 features in Excel according to you.

1. Excel Formulas
63 people (50%) said Formulas are their favorite feature in Excel. Of course, you can say, Formulas & Functions are Excel!!! . They are what Excel is made of. But then …
Zum Original Blog
Autor: Chandoo

Some charts try to make you an April fool all the time (or why 3d pie charts are evil)
Zum Original Blog Chandoo.org

Recently I saw a big screaming ad that said “the chartbuster rules”. Of course, I know that chartbusters rule. Not just because I was one of them
So I got curious and read on. And I realized the ‘chartbuster’ is actually a car, not some cool, spreadsheet waving, goatee sporting dude like Jon Peltier. What a bummer!
And then to my horror of horrors, I saw the exploding 3d pie chart, with reflection effects & glossy colors. And the sole purpose of the chart is to create an impression that Verna sells better than any car …
Zum Original Blog
Autor: Chandoo

How to create interactive calendar to highlight events & appointments [Tutorial]
Zum Original Blog Chandoo.org

One of the popular uses of Excel is to maintain a list of events, appointments or other calendar related stuff. While Excel shines easily when you want to log this data, it has no quick way to visualize this information. But we can use little creativity, conditional formatting, few formulas & 3 lines of VBA code to create a slick, interactive calendar in Excel. Today, lets understand how to do this.
But first, a reminder to join my Advanced Excel masterclass in USA
As you may know, I am running my first ever Advanced Excel & Dashboards Masterclass in USA this …
Zum Original Blog
Autor: Chandoo

What is your favorite feature of Excel? [poll]
Zum Original Blog Chandoo.org

Its Friday, time for another poll.

This weeks topic is inspired from a discussion Jordan started in our forums (#).
What is your favorite feature of Excel?
I will go first.
My favorite features are,
Conditional formatting: Quickly highlight something that is not alright (or meets conditions), see trends with data bars or heat maps.
Pivot tables: Turn data in to understandable information with just a few clicks. When combined with slicers & conditional formats, becomes very powerful.
Formulas: Ofcourse, with out formulas, Excel would be a glorified notepad!
What about you? What are your favorite features in Excel? Go ahead …
Zum Original Blog
Autor: Chandoo

How well do you know your LOOKUPs? – Quiz Answers & Discussion
Zum Original Blog Chandoo.org

Last week, we had our very first quiz – “How well do you know your LOOKUPs?“. I hope you have enjoyed it.
Today lets understand the answers & explanations for this quiz.
First a summary of results (and controversies):
More than 3,200 people attempted the quiz.  Roughly 45% of you got correct answers. That said there were 4 questions where 90% of you made mistakes. This is mainly because these questions & answer choices were ambiguous. As this is the first time I ran a quiz on Chandoo.org, it was a learning experience for me too. I promise to keep …
Zum Original Blog
Autor: Chandoo

10 things that wowed me in Excel 2013
Zum Original Blog Chandoo.org

As you may new, the newest version of Excel is out for a while. I have been using it since last 6 months and enjoying it. Today, lets understand 10 things in 2013 that wowed me (and probably you too).

 
Flash Fill
Imagine Flash (the super hero, not browser add-in) is using Excel to extract the middle names of all his villains. Now, flash being flash, do you think he will slowly type out the middle names one at a time? Of course, he can learn Excel formulas and do it in one stroke. But he is too busy running around & …
Zum Original Blog
Autor: Chandoo

Unlock “Angy Formulas” – an Angry Birds like game hidden in Excel using this trick!!!
Zum Original Blog Chandoo.org

Sometimes you think you know something and then suddenly you are surprised. Yesterday was such a moment for me. I have been using Excel for almost a decade now. So naturally I assumed that I know it well. But then yesterday, while doing something I stumbled on a strange screen in Excel that looked like very popular Angry birds game. So I got searching. But there was no mention of it anywhere on net. Then I asked my friend Rollf ‘O’ Pai, who is in Micros0ft Execl team. First he denied such a thing. But we knew …
Zum Original Blog
Autor: Chandoo

There is an Easter egg in this chart!
Zum Original Blog Chandoo.org

Do not worry, you are not time traveling or seeing things. Its just that, this year I have decided to publish our Easter Egg a few days early.
And oh, I have 3 reasons for it:
2 of my favorite festivals – Easter & Holi (a festival of colors, celebrated in India) are this week. Holi is today (Wednesday) & Easter on Sunday.
My kids are super excited about Holi as this is the first time they will be playing it. So we have family time from today until Wednesday and I do not feel like writing a blog entry on Friday
…
Zum Original Blog
Autor: Chandoo

Join my Excel Dashboard Masterclass in USA [May-June 2013]
Zum Original Blog Chandoo.org

Hello folks,
I have a super exciting news to share with you all. I will be conducting a series of Masterclasses on Advanced Dashboards & Excel in USA this Summer (May – June 2012). So if you live in (or nearby) Chicago, New York, Washington DC or Columbus OH, and want to one up your dashboard skills, you know what to do next.
I am conducting these masterclasses in partnership with PowerPivotPro.

What is in this Masterclass & Who should attend?
This 2 day masterclass is aimed at managers, analysts, reporting professionals, executives in sales, marketing, customer service or anyone who wants …
Zum Original Blog
Autor: Chandoo

How well do you know your Lookups? [Quiz]
Zum Original Blog Chandoo.org

Its quiz time folks.
So you think you know VLOOKUP formula? Well, test your knowledge.
Click here to take our first ever quiz – on Look up formulas.
Hint: Check out Comprehensive guide to LOOKUP formulas for help
Once you finish the quiz, enter your name & email address to record your results in leader-board.
PS: More quizzes & problems in Excel.

Zum Original Blog
Autor: Chandoo

Finding Nearby Zipcodes using Excel Formulas
Zum Original Blog Chandoo.org

Recently, I had a peculiar problem. I have a list of zip codes and I wanted to find out nearest zip codes for each of them.
Now, If I wanted to find out near by zip codes for one area, I could go and search in Google. But, how to do it for dozens of them?
Today, lets understand how you can use Excel (that’s right) to do this automatically. We will be using Excel 2013 for this.

A little background – Excel 2013 Web Formulas
In Excel 2013, Microsoft has introduced 3 powerful new formulas. These will help you fetch & …
Zum Original Blog
Autor: Chandoo

How to remove all cells containing John (or anything else) [Quick tip]
Zum Original Blog Chandoo.org

Here is an interesting question someone asked me recently,
If I have to delete all rows with “John” in it. Do you know how to do it? 
Well, it looks like they really hate John. But it is none of my business.
So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.

Select all your data
Press CTRL+F and search for “John”
Click on “Find all” button
Now, select the first result.
Scroll down, holding SHIFT key, select the last …
Zum Original Blog
Autor: Chandoo

Finding Conversion ratio using Pivot Table Calculated Items
Zum Original Blog Chandoo.org

Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.
This is inspired from a question posted by Nicki in our forums,
I have a spreadsheet source data full of sales enquiries which have the Status – Lost, Booked or Pending. Each sales enquiry relates to a particular location. I have created a pivot table which counts the enquiries and displays them with the Locations in rows and the Status in the columns. …
Zum Original Blog
Autor: Chandoo

Work with several Excel files everyday? – Save them as a workspace [Quick tip]
Zum Original Blog Chandoo.org

If you work with multiple Excel workbooks everyday, then here is a handy tip.

Open all the files you frequently use together.
Go to View ribbon and click on Save Workspace
Give a name to your workspace
Now, whenever you need to open all these files together, just double click on workspace file in your explorer.
You are done!

Workspace saves not just file collection, but also window layouts. That means, if you like to arrange your Excel windows in a specific fashion, you can save this arrangement too.
Bonus tip: Use specific filter criteria all the time? Try …
Zum Original Blog
Autor: Chandoo

Use Advances vs. Declines chart to understand change in values
Zum Original Blog Chandoo.org

Lets say you are responsible for sales of 100s of products (which belong to handful of categories). You are looking at sales of each product in last month & this month. And you want to understand whether sales are improving or declining by category. How would you do it?
Turns out, this is not a difficult problem. In fact, this question is asked every day & answered using Advances vs. Declines chart.
You may have seen this chart in financial newspapers or websites. Shown below, Advances vs. Declines chart tells us how many items have advanced & how many …
Zum Original Blog
Autor: Chandoo

Last day for enrollments – Join our Power Pivot course & become awesome analyst
Zum Original Blog Chandoo.org

Hi folks,
I have a quick announcement & an awesome Power Pivot technique to share with you. First the announcement.
Only few hours left to join our Power Pivot course…
As you may know, I have opened enrollments for our inaugural batch of Power Pivot course few days ago. The aim of this course is to make you awesome in Excel, Advanced Excel, Dashboards & Power Pivot.
We will be closing the doors of this program at midnight, today (11:59 PM, Pacific time, Friday, 15th of February).
If you want to join us, click here and enroll now.
How many …
Zum Original Blog
Autor: Chandoo