All articles in 'Featured' Category

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel.
We will learn how to compare 2 lists of data in 3 + 1 different ways. (click on links to jump to that section of post)

Highlight [...]

The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard.

Now, if she wants average of all values, we can use AVERAGE() formula
if she wants top 5 values to be [...]

Office 2010, the latest and greatest version of Microsoft Office Productivity applications is going to be available worldwide in the next few weeks. I have been using Office 2010 beta since November last year and recently upgraded my installation to the RTM version. I was pleasantly surprised when I ran Microsoft Excel 2010 for first time. It felt smooth, fast, responsive and looked great on my comp.

This week, I want to celebrate the launch of Excel 2010 and write about the following new features in Excel 2010.

Also, you can win a copy of Office 2010 – home & student edition by leaving a comment on this or any of the other excel 2010 posts scheduled for this week

In this article we will learn what a Panel Chart is and how you can construct a panel chart in Excel.
What is a Panel Chart?

A panel chart is a set of similar charts neatly aligned in panels to help us understand some data which has multiple variables in it. Panel charts are also called by names “trellis displays” or “small multiples”. They are an effective way to display multi-variable data.

Here is an example panel chart showing the total defects per module over the last 4 weeks.

If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!

How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost”

This post is going to detail the use of the Data Table function within Excel, which can help you answer that question and then so so much more.

A panel chart (often called as trellis display or small-multiples) shows data for multiple variables in an easy to digest format. It lets users compare in any way and draw conclusions with ease.

Today, I want to discuss how the principles of panel chart can be applied to visualize a complex set of survey results. For this we will use the recent survey conducted by Gartner on how various customers use BI (Business Intelligence) tools.

In this and next 3 posts, we will learn how to make a Dynamic Dashboard using Microsoft Excel.

At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using excel formulas and simple VBA macros.

In October 2008, I have started an ambitious series of posts on this blog called – Spreadcheats. These are little tricks, nuggets, tutorials on using Excel that would make anyone a spreadsheet guru.

The spreadcheats series has been wildly successful. I am compiling all this useful information and articles in to one big post so that anyone can follow the links and become good in Excel. Read on,

[Note: This is not for beginners. If you know what a formula is, you would enjoy this 31 articles]

I have a new community project for all the members of PHD. It is a simple yet intuitive challenge. Make your own country’s flag using Excel Charts. To start the project, I have made the Indian flag using a bar and pie chart. Go ahead and see the rest of this post to findout how this chart is made and then participate in the “flag project” by making your own country’s flag. All the best.

Few weeks back I have invited all of you to share your excel keyboard shortcuts in a open thread. More than 50 people commented on that post and shared a hundred excel keyboard shortcuts with us. There were so many wonderful keyboard shortcuts and tricks buried in the comments section of that post. During the [...]

We, data junkies, love pivot tables. We think pivot tables are solution for everything (except for may be global warming and that broken espresso machine down stairs).

Today, we are going to learn 5 awesome pivot table tricks that will make you a star.

Last week I have reviewed Google’s flu trends chart and told you that is it is very well made. Out of curiosity I made a similar chart in Excel. In this post, I am going to share the experience and results with you. Interested? Read on…

Today we will build a mortgage payment calculator (and amortization schedule) using excel. But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.

A mortgage payment is a monthly installment that you pay towards a loan. Any mortgage loan will typically have, (1) loan amount, (2) duration of the loan in years, (3) interest rate per year

Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure)

We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this.

Sales reports and dashboards are very common in any company. There are several ways in which you can visualize sales data to understand the trends and sales performance. So in November, I have asked you to visualize sales data using sample data. The visualization challenge #2, sponsored by Zoho Reports generated a huge buzz around the community and fetched 32 incredible entries. The response was so overwhelming that it took me almost 24 hours to write this post. Thanks everyone for participating and making this a huge learning experience for everyone. Personally I have learned several useful dashboard and charting tricks. I will be sharing some of these lessons with all of you in the coming weeks.

Comparing values is one of the main reasons we make charts. Yesterday we have a post on using thermometer charts to quickly compare actual values with targets. Today we follow up the post with 10 charting ideas you can use to compare actual values with targets. Check out how bullet charts, thermometer charts, traffic lights, gauges, column charts, area charts can help you compare targets with actual performances. A review of best charting options when you need to compare.

Read the rest of the post to see the options and participate in poll.