Archive for April, 2010

Wow, I am really overwhelmed by the support and encouragement you all have shown when I announced that I am quitting my day job to pursue PHD as a full time venture. Thank you. Thank you so much. I have received 68 comments (they are still pouring) and almost 100 emails from our readers telling […]

In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back. It only works when you have data for one year. In that post, Martin said, … […]

Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it? There are 2 options: You can make a pivot report from the data and then group dates in that to find totals by […]

We all know that SUMIF formula can be used to find the sum of values meeting a criteria. Like this, But I was pleasantly surprised to realize that SUMIF works equally well for 2D ranges too, like this: During a recent consulting work with a client I had a requirement to sum up values that […]

Some of you know that I have a full time job as a business analyst with one of the largest IT companies in the world. I help insurance companies do their IT better in that job. Almost a week ago, I have resigned from my day time job. Back story: As far back as I […]

Ok, it is Friday again. That means poll time. I would like to know what color choices you prefer for your charts?
My Favorite Colors for Charting are,

These are the colors I use when making charts in Excel 2007 (it has capability to set any color for chart elements).

In this post we are going to look at a simple example of the VBA behind the Dynamic Dashboard. Essentially we will learn to write macros for doing this. Read the rest of this post to find code samples and downloadable files to play with.

This is continuation of our earlier post Preparing Quarterly and Half yearly P&L using grouping option. You can also do budget v/s actual comparison using Pivot Tables. For this we will use calculated items feature of Excel PivotTables.

To begin, we have to add one more column to our data. I have added column Data Source to the end of data table. Existing data is marked as Actual and I have added more data rows which are marked as Budget. You can download new file with updated data and basic Pivot P&L.

Bar chart or Line? Scatter plot or box plot? These are the questions we ask ourselves when we set out to make a chart. Because, “Selecting right chart for our data” is very important to tell our story.

In this article, we will learn how to “select the right chart” based on our data and situation.

These days I rarely use Excel 2003. But when I do open the trusty old software, I always look for opportunities to improve my productivity with it. And I am pleasantly surprised to find a shorter and faster way to turn off grid lines on spreadsheets in Excel 2003. (I like my spreadsheets without any […]

Remember Excel School? It was all I talked about between Jan last week and middle of Feb. Then as if someone has pressed mute button, there was not even a single mention about the school. So I thought, why not give you all an update on Excel School and tell you how the classes are […]

I have come to Bangalore to attend the TechEd 2010 conference by Microsoft. Today (April 13th), I will be speaking on “How to select the right chart for your data”. A short talk telling people how to choose the correct chart based on the message they want to convey. [You can see the schedule and […]

In a follow up to last week’s how to visualize survey data in excel, we will explore how you can create an incell dot plot panel chart. Please follow the links in the article to get more commentary and insights from established persona in the visualization world (including Stephen Few, Naomi, Jon etc.)

We all know that legend can be added to a chart to provide useful information, color codes etc.

Today we will learn how to make the chart legends smarter so that they provide more meaning and context to the chart, like this:

This type of legend can be more useful than a plain legend as this provides more useful information without taking up too much space.

Sometimes we think there is very little we can improve in something and then we come across an idea that would change our perceptions. I have been writing excel formulas for such a long time that it was easier to remember when I first shaved than when I first wrote a formula. (may be, because […]