Archive for July, 2009
Are you excited about this twitter chart?
In this ironically titled “Let’s not get too excited” chart, David shows us how the twitter community is divided. Can you guess what is wrong with this chart?Continue »
Time to showoff your VBA skills – Help me fix ActiveSheet.Pictures.Insert snafu
I am stuck with an excel problem and I need your help. While trying to insert an image in to my excel 2007 workbook using VBA I hit on this weird error and not able to use the ActiveSheet.Pictures.Insert method. Do you know why?Continue »
Build a Retirement Calculator using Excel (learn how to use Goal Seek as a bonus)
In this installment of spreadcheats, we will learn how to use goal seek feature of excel. We will build a retirement savings calculator using excel. We will learn to use Excel’s FV() formula to estimate the corpus that can be accumulated by saving fixed amount every month.Continue »
Charting Lessons from Optical Illusions
The other day while doing aimless roaming on the dotcom alley, I have seen some cool optical illusions. There are so many valuable lessons optical illusions can teach us – chart makers. Don’t believe me? Look at the bubble chart illusion on the left and tell me which orange circle is bigger? What is your answer? Right or left. Well, my friend, the answer is both are of same size. Read the rest of this post to find some cool optical illusions and what they can teach us – chart makers.Continue »
Introducing PHD Forums (and some other updates)
I have an exciting news for you. Yes, finally we have a forum. A place where you can ask questions, get to know each other and learn more. Read the post to know more.Continue »
We cant Cure Cancer, But we can Cure this Medicare Chart [Chart Busters]
In this installment of Chartbusters, we take a look at a poorly constructed choropleth of medicare reimbursements in US and suggest cure for it. The post is from our Guest Buster – Jeff.Continue »
Sumif with multiple conditions [quick tip]
Here is a little formula trick if you need to sum a range of cells based on multiple conditions.
Assuming you have the starfleet, captain and flight data, you can use the good old sum() in an array formula to conditionally sum values meeting multiple criteria. Read on to learn this quick tip.Continue »
Use burn down Charts in your project management reports [bonus post]
A burn down chart is a good way to understand the progress of a project. It is like a run chart that describes work left to do versus time. In this tutorial we will learn how to make a burn down chart using excel. This is a bonus installment to the project management using excel series.Continue »
Generating invoice numbers using excel [reader questions]
Learn how to generate invoice numbers, tax codes etc. using Microsoft Excel. In this example we will take a real life example shared by Michelle and findout how we can generate invoice numbers using excel formulas. Read more to learn and download the example workbook.Continue »
2 Great Pieces of Advice for Chart Makers, Dashboard Designers and Story Tellers Everywhere
The worldwide web is a wonderful place. I am constantly amazed by the simple yet very effective stuff we can learn by just reading. Today I want to share with you two very great pieces of advice: Seth Godin’s 4 Simple Principles for making effective graphs: Seth Godin is probably one of the most remarkable, […]Continue »
Formula 1 Style Sorting of Times (Durations) in Excel
The other day I was watching Formula 1 on TV. I think it is the ideal game to follow for a lazy dude like me. It is on every other weekend. It takes .32 seconds to understand the game and 3.2 seconds to know the points and scoring mechanism. But I am not here to convince you to follow the game. While looking at score boards, it struck me,
“how about writing excel formulas for sorting a list of durations (or numbers) in the formula 1 order?”Continue »
14 Basic Skills for Chart Makers (Big question: How many do you have?)
Blame John Walkenbach if you don’t like this post. There, I said it. He started the 14 basic skills for men. And then added 14 basic skills for women. Not stopping there, he went ahead and added 14 basic skills for dogs. Debra at Contextures blog added her 14 cents by writing 14 basic skills for excel users.
I got jittery and quickly searched 14 basic skills for people writing 14 basic skills posts on google. Alas! nothing found. But being the inveterate non-give-upper I went ahead and prepared my list.
<drum roll> here is the,
14 basic skills for people making charts (or graphs or plots or ok, you get the point)
Make an Impressive Product Catalog [spreadsheets for small business]
It is the customer on the phone again, she wants to know what products we have.
How cool would it be if we can send her a spreadsheet with all the products neatly listed in a table and she can use filters to find what she likes. Alas, we end up sending a biggish PDF brochure that is both difficult to make and maintain.
Well, not any more.
Today we will learn a very useful and fun trick in Excel. We will create a product catalog using Excel that you can send to your clients or boss (and impress them).Continue »
Project Management: Show Milestones in a Timeline [Part 3 of 6]
Learn how to create a timeline chart in excel to display the progress of your project. Timelines are a good way to communicate about the project status to new team members and stake holders. Also, download the excel timeline chart template and make your own timeline charts.Continue »
How to Round and Sort Data using Excel Formulas?
Cheryl asks via e-mail, “I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them.”
Of course we can solve this by simply using array formulas. Curious? Find out more by reading the rest of this post.Continue »