Archive for August, 2009

5k Giveaway – Excel is a Superhero – Winners

Published on Aug 31, 2009 in blogging

Recently our blog has reached the 5000 RSS subscriber milestone and to celebrate the occasion I have announced a giveaway. All you had to do was to complete the sentence “if excel is a superhero …”. 70+ people have participated in it.

Read more to find the list of randomly chosen winners.

Continue »

Nightmarish Pie Charts [because it is weekend]

Nightmarish Pie Charts [because it is weekend]

Nothing like a weekend making fun of something worthless. So we will pick on some of the plump, overloaded and visually disgusting pie charts featured on various extremely popular websites. As a bonus, you get to see my hand-drawn pie chart on the usage of pie charts.

Continue »

Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]

Published on Aug 27, 2009 in Charts and Graphs
Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]

Yesterday we have posted how to use excel combo charts to group related time events. Today we will learn how to change the event grouping dynamically using form controls.

This effect can be easily achieved with a cup of coffee, one combo box form control and the good old IF formula. Read more to learn how to do this.

Continue »

Using Combo Charts to Group Related Time Events [Charting Goodness]

Published on Aug 26, 2009 in Charts and Graphs
Using Combo Charts to Group Related Time Events [Charting Goodness]

In his latest book, Now You See It, on pages 165 and 166 of the book, Stephen Few discusses how grouping related time intervals can facilitate analysis of data. As an illustration he explains that when viewing data of daily website visits, it helps in separating weekdays and weekends to differentiate expected traffic during these periods. The use of this technique would make it easier for the analyst to identify any anomalous movement in ether the weekend or the week day.

Fortunately excel combo charts can help you do that. In this guest post, Paresh explains to us how to do this.

Continue »

Make a Pivot Table in Excel [15 Second Tutorial]

Published on Aug 25, 2009 in Excel Howtos
Make a Pivot Table in Excel [15 Second Tutorial]

Last week we have posted a simple to understand excel pivot table tutorial here. Today I am going to supplement the tutorial with a 15 second video tutorial on Making excel pivot tables.

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Continue »

50 Best Cities for Finding a Job [Incell Dashboard using Excel]

Published on Aug 20, 2009 in Charts and Graphs, Learn Excel
50 Best Cities for Finding a Job [Incell Dashboard using Excel]

We all know that incell charts are a very cool way to explore and visualize data. Personally I like them so much that I have written several tutorials on it here. Today we will see how a Job dashboard on “50 best cities for finding a job” originally prepared by Indeed job search engine can be recreated in Excel using In-cell charts. The final outcome is something like this.

Continue »

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Published on Aug 19, 2009 in Excel Howtos, Featured, Learn Excel, Pivot Tables & Charts
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.

In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.

Continue »

Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]

Published on Aug 17, 2009 in Learn Excel

Here is a quick excel formula tip to start your week. Use ROWS() and COLUMNS() formulas next time you need sequential numbers. What does ROWS() excel formula do? ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10. How can you […]

Continue »

Only 2 more days to get prizes worth $425, Hurry

Published on Aug 14, 2009 in blogging

The PHD 5k Giveaway which includes exciting prizes like Excel dashboard bundle, Power Utility Pack Licenses, Amazon gift card and Excel 2007 Formulas book is going to end by tomorrow midnight (that is 15th August people). To participate, all you got to do is, either send a tweet with your favorite formula tip or complete […]

Continue »

Hide Grid Lines [Quick Tip]

Published on Aug 13, 2009 in Excel Howtos
Hide Grid Lines [Quick Tip]

I like to hide grid lines on my spreadsheets and charts whenever possible. I think removing gridlines makes the charts and worksheets more presentable. In case you are wondering how to remove (or hide) gridlines from your worksheet or chart, follow these simple instructions.

Continue »

Member of month, Excel links and Cooked HDD

Published on Aug 11, 2009 in blogging, excel links
Member of month, Excel links and Cooked HDD

Starting this month I will announce one member of our little community as member of month. It is to honor the contribution they made.

Jeff Weir is our member of month for July, 2009. He not only commented more than 40 times in the last month, but he even wrote a marvelous guest post on the chart busters series. I have learned several valuable excel and charting tips from him in the past few weeks. I am sure some of have too. Thank you Jeff.

Also in the post we have some excel links worth checking.

Continue »

Waterfall Charts using Excel

Published on Aug 10, 2009 in Charts and Graphs, Featured
Waterfall Charts using Excel

Learn how to create waterfall charts in Excel in this tutorial. Our guest author, Aaron, explains how to create cool looking waterfall charts with connectors. Waterfall charts are great, especially for visually showing the contribution of parts to a whole.

Continue »

What would you do if a co-worker makes ugly chart? [weekend poll]

Published on Aug 7, 2009 in Charts and Graphs
What would you do if a co-worker makes ugly chart? [weekend poll]

We talk alot about making better charts and perils of bad charts here.

I want to know what you usually do when a co-worker or boss makes an ugly chart?

  1. You tell them the chart sucks
  2. You gently point out the mistakes of their chart and tell them some nicer and cooler ways to tackle it
  3. You stay calm and send them an e-mail later (may be with a link to PHD chart pages or something like that)
  4. You don’t care (and may be continue doodling)
  5. Any other

Vote your option using comments.

Continue »

Count the number of unique values in a range [Quick Tip]

Published on Aug 6, 2009 in Excel Howtos, Learn Excel
Count the number of unique values in a range [Quick Tip]

Here is an excel formula quick tip that can come handy when you need to count the number of unique values in a range of cells. Assuming we have a list of values in the range:B5:B15 and we want to know how many unique values are there,
you can use the almighty SUMPRODUCT formula like this: SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15)).

Read the rest of this post to understand how the formula works. You can also find resources to work with duplicate values in excel.

Continue »