All articles with 'Learn Excel' Tag
Last week, we learned how to create win-loss charts in Excel. In the comments, Dan said,
“Incidentally, the fastest way to do this would be using SFE, just reflect your data with 1 for a win, – 1 for a loss. There’s even an option to automatically invert negative numbers.”
Of course, we can use the beautiful Sparklines for Excel addin to do this and several other charts. But if you just have a series of Wins and Losses, like below, you can use a column chart to create win loss charts too.
Today, we will learn how to create a win loss chart from a set of win, loss data in Excel.Continue »
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 »
We have some home work folks! Today, lets test your Excel formula skills by giving some data related to a fund.
You manage a fund for a non-profit. You have donors who pledge certain amount at the start of the year. As you go thru the year, the donors donate money to your fund. At the end of the year, you have a table like above. And you need to summarize the fund’s performance by calculating various statistics.
Win Loss Charts are an interesting way to show a range of outcomes. Lets say, you have data like this:
win, win, win, loss, loss, win, win, loss, loss, win
The Win Loss chart would look like this:
Today, we will learn, how to create Win Loss Charts in Excel.
We will learn how to create Win Loss charts using Conditional Formatting and using In-cell Charts.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 »
I have happy news for you.
Paramdeep (from Financial Modeling School) and I am going to organize an Excel Workshop in Singapore during first (or second) week of July.
We want to know if you are interested in this. So please take a few minutes and go thru this small post.
Who is this workshop for?
If you are a financial or business analyst, this workshop is for you. We will be discussing various Excel & Financial Modeling topics during the 12 hour workshop (spread across 2-3 days)Continue »
Slicers are my new favorite feature in Excel. Introduced in Excel 2010, Slicers are like visual filters.
Now, we can use slicers creatively to make an interactive scenario manager in Excel, as you can see below. We will learn how to create this in Excel in today’s post.Continue »
We have a new Excel Challenge folks!
I know our friends in US are away celebrating Memorial Day weekend. But that should not leave rest of us from fun. So, we have a new Excel Challenge. This time, you need to make a chart, to visualize product sales data.
And what more, one lucky person could get an Amazon Kindle Reading Device (Wi-fi version) by making the chart.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 »
I have a quick announcement for you.
Registrations for our VBA Class will be closing in a few hours
As you may know, we have opened registrations for our first batch of Online VBA classes on May 9th. This program is aimed at beginners & intermediate level VBA users. The aim of this course is to make you awesome in VBA. We will be closing registrations for this program in few more hours (exactly at 11:59 PM, Pacific time, on 20th May 2011)Continue »
Here is a cool count-down timer application made in VBA to remind you about our VBAClasses registration closing time!
Read the rest of this post to learn how this particular app is built.
If you want to learn VBA, then click here to join our class.Continue »
– Cue, alluring music.
Just like Jack and the crew of Oceanic Airlines flight 815, Excel has a number of LOST functions.
This post will look at some of Excel’s Undocumented, Unloved and Rarely used Functions
We have a challenge in our VBA class. Many of the students who join our VBA program have no formal programming background. They may have written a few simple programs long time ago, but most of them lack basic understanding of programming. Teaching VBA can be difficult if we do not address this problem.
So, we have added a lesson on “Introduction to Programming”. In this lesson, our aim is to introduce programming to non-programers.
Since many of you are considering to join our VBA classes, it is appropriate that we give this introduction to programming lesson as a demo lesson. Please watch it below.Continue »