All articles in 'Learn Excel' Category
Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.Continue »
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 »
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 SumproductContinue »
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 »
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 problemContinue »
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 !
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.
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)
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 »
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 »
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 »
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.
- Books on Dashboards
- Websites for Learning about Dashboards
- Dashboard Training Programs
- Add-ins & Software to Make Dashboards
- Dashboard Tutorials & Downloads on Chandoo.org
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 »
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.
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 »