fbpx
Search
Close this search box.

Archive for April, 2010

Wow!!!, Thank You :)

Published on Apr 30, 2010 in personal

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 […]

Continue »

Quarterly totals when you have multi-year data [SUMPRODUCT again]

Published on Apr 30, 2010 in Excel Howtos, Learn Excel
Quarterly totals when you have multi-year data [SUMPRODUCT again]

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, … […]

Continue »

Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula]

Published on Apr 29, 2010 in Excel Howtos, Learn Excel
Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula]

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 […]

Continue »

SUMIF works in 2D too [quick tip]

Published on Apr 27, 2010 in Learn Excel
SUMIF works in 2D too [quick tip]

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 […]

Continue »

Childhood Dream comes true [personal story]

Published on Apr 26, 2010 in personal
Childhood Dream comes true [personal story]

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 […]

Continue »

What are your favorite colors for charts?

Published on Apr 23, 2010 in Charts and Graphs
What are your favorite colors for charts?

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).

Continue »

Making a Dynamic Dashboard in Excel [Part 3 of 4]

Published on Apr 22, 2010 in Charts and Graphs, VBA Macros
Making a Dynamic Dashboard in Excel [Part 3 of 4]

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.

Continue »

Budget vs. Actual Profit Loss Report using Pivot Tables

Published on Apr 21, 2010 in Learn Excel, Pivot Tables & Charts
Budget vs. Actual Profit Loss Report using Pivot Tables

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.

Continue »

How to pick a chart type – Charting 101

Published on Apr 19, 2010 in Charts and Graphs
How to pick a chart type – Charting 101

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.

Continue »

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

Published on Apr 15, 2010 in Excel Howtos
Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

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 […]

Continue »

Updates on Excel School [and 3 free lessons]

Published on Apr 14, 2010 in Learn Excel, products
Updates on Excel School [and 3 free lessons]

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 […]

Continue »

Speaking at TechEd 2010 on “How to Select the Right Chart for your Data”

Published on Apr 13, 2010 in blogging, Charts and Graphs
Speaking at TechEd 2010 on “How to Select the Right Chart for your Data”

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 […]

Continue »

Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]

Published on Apr 9, 2010 in Charts and Graphs
Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]

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.)

Continue »

Make your Chart Legends Legendary

Published on Apr 8, 2010 in Charts and Graphs
Make your Chart Legends Legendary

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.

Continue »

Use CTRL+Click to speed up your formula entry [Quick Tips]

Published on Apr 7, 2010 in Learn Excel
Use CTRL+Click to speed up your formula entry [Quick Tips]

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 […]

Continue »