All articles in 'Learn Excel' Category

10 Excel Formula Myths – Busted!

Published on Jun 7, 2011 in Learn Excel
10 Excel Formula Myths – Busted!

Many of us start using Excel to keep track of something. And along way, we realize that Excel has a powerful feature called formulas, using which we can automate a lot of things. BOOM! Before we realize, we are in the thick of VLOOKUPs and SUMIFs.

But, along way, we also pick up a few bad habits or believe a few myths. Today, lets bust 10 Excel formula myths that we hear often.

Read this post to learn what these myths and why you should not trust them.

Continue »

Advanced Sumproduct Queries

Published on May 26, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Advanced Sumproduct Queries

How often have you had a simple table where you want to lookup a value or add up some values meeting a criteria?

This post looks at a simple way to do a 2D Lookup or 2D Sum of a values from within a 2D range with multiple criteria, without complex Sum, Offset, Index, Match, Sumifs or other functions, simply using Sumproduct

Continue »

Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]

Published on May 24, 2011 in Financial Modeling, Learn Excel
Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]

You take an apartment on rent at $1000 per month and the owner puts an escalation clause saying 10% increment each 3 years. How do you model this in excel? In this tutorial we understand how escalations at certain frequency can be implemented using the mod function in excel. What is the mod() function Simply […]

Continue »

Use Copy & Paste to Preserve References to Tables [Quick Tip]

Published on May 23, 2011 in Learn Excel
Use Copy & Paste to Preserve References to Tables [Quick Tip]

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

Continue »

Using Solver to Assign Items to Buckets

Published on May 11, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
Using Solver to Assign Items to Buckets

Ok, You’ve just finished the mother of all Spreadsheets. Not only does it solve your companies budget, it tracks production, reports variances, makes a mean cup of coffee and some say, “it May Even Solve World Peace ?”.
But the boss walks in and asks “I have a list of items which I need to distribute evenly into colored buckets”
I had never thought of that option, Maybe I can constrain my model and let it work it out for me ?
Solver I need your help !

Continue »

Dummy Data – How to use the Random Functions

Published on May 4, 2011 in excel apps, Excel Howtos, Huis, Learn Excel, Posts by Hui
Dummy Data – How to use the Random Functions

Don’t know which way to go ?
Can’t make up your mind between alternatives?

Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model. This post will look at some options for setting up Dummy Data using Excels Random number functions.

Continue »

80% Discount on PUP & PowerPivot Contest – Hurry up!

Published on May 3, 2011 in excel links
80% Discount on PUP & PowerPivot Contest – Hurry up!

Since I am busy with background work on VBA Classes launching next week, today I want to give you two quick updates.

1. John Walkenbach is offering 80% discount on his Power Utility Pack Excel add-in, only today (3rd May) between 11AM-1PM EST.
2. You can win an XBOX 360 + Kinect by participating in PowerPivot Nerdtastic Quiz on their facebook page.

Read more to get the details about these two.

Continue »

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

Last week Chandoo presented Give more details by showing average and distribution

At the top of the post was a small screen capture from Amazon.com showing a 5 Star chart showing that Twilight had a 3.5 Star Rating (way over-rated if you ask me).

I received an email shortly afterwards from Rajiv, “How can I make one of those charts ? ” with the Stars Circled

It’s actually very simple and this post will show you how.

Continue »

Excel Links – Congratulations Team India Edition

Published on Apr 4, 2011 in excel links
Excel Links – Congratulations Team India Edition

Wow, what a weekend it has been. Indian cricket team has won Cricket world cup 2011. It has been a highly entertaining tournament and the finals were just incredible.

Congratulations to Team India for winning Cricket World-cup 2011.

I am tempted to make a dashboard of sorts to show the journey of Team India in this world cup. But I could not get much time to work on anything. I will post something this week to celebrate the victory.

Read the rest of this post to get 5 recommended Excel resources to learn new things.

Continue »

Form Controls – Adding Interactivity to Your Worksheets

Published on Mar 30, 2011 in excel apps, Excel Howtos, Huis, Learn Excel, Posts by Hui
Form Controls – Adding Interactivity to Your Worksheets

Excel can be a bland collection of cells with text and numbers or it can be a playground where you can have interactive engagement with your models.

The choice is yours!

Excel provides a small number of tools to allow you to interact with your models.
These tools are called Form Controls and can be added into and linked to your models data.

This post will look at the various types of Form Controls and discuss their application and use.

Continue »

Learning Dashboards? – Go thru these 33 Recommended Resources

Published on Mar 25, 2011 in Charts and Graphs, Learn Excel
Learning Dashboards? – Go thru these 33 Recommended Resources

During last one week, we had a gala time with Dashboard Week on chandoo.org. To wrap-up the week, I am sharing a list of recommended resources, websites, tutorials & ideas for making dashboards.

Recommended Resources on Making Dashboards:
I have broken down this post in to various sections. Click on the links to quickly access the part you want to know or just keep scrolling to get the whole thing.

  1. Books on Dashboards
  2. Websites for Learning about Dashboards
  3. Dashboard Training Programs
  4. Add-ins & Software to Make Dashboards
  5. Dashboard Tutorials & Downloads on Chandoo.org
Continue »

IFERROR Excel Formula – What is it, syntax, examples and howto

Published on Mar 11, 2011 in Learn Excel
IFERROR Excel Formula – What is it, syntax, examples and howto

If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

Continue »

Excel Challenge #1 – Make Nuts without Going Nuts

Published on Mar 9, 2011 in Excel Challenges, Learn Excel
Excel Challenge #1 – Make Nuts without Going Nuts

We have a new series on chandoo.org. – Excel Challenges. From now, every 1-2 months, I will post an interesting Excel Challenge. These are tricky problems for which elegant solutions should be found. To keep the challenges exciting, we will have a small prize for a winner (if more people answer correctly, we pick one randomly)

Excel Challenge #1 – Find Overlaps in Machine Schedule Dates

We have an Excel Table, with the following data (snapshot above). You need to write formulas to display Ok or Not Ok in the adjacent column based on the following criteria:

1. Display Ok if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
2. Display Not Ok otherwise.

Continue »

Quickly Remove Data Validation Rules using Paste Special

Published on Mar 4, 2011 in Learn Excel
Quickly Remove Data Validation Rules using Paste Special

Here is a quick tip on removing data validation rules. We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it? The normal approach is, Select the cells with […]

Continue »

3+1 Ways to Learn Advanced Excel

Published on Mar 2, 2011 in Financial Modeling, Learn Excel, products
3+1 Ways to Learn Advanced Excel

Many of us want to learn advanced Excel and make progress in our career. But how to do it? In this post, I show 3+1 ways in which you can learn advanced excel.

Last week I did an interview with Robert Mundigl of clearlyandsimply.com. Robert is an Excel wizard. You may know him thru the KPI Dashboard articles he has written on chandoo.org a while ago.

We spent about 90 minutes discussing some really cool & advanced Excel stuff. The interview will be available shortly on Excel School for our Dashboard students. But here is a snapshot of the dashboard we discussed in the interview. Robert taught me how to make such a dashboard using Excel.

Continue »