All articles in 'Featured' Category
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]Continue »
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.Continue »
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 […]Continue »
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.Continue »
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…Continue »
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.Continue »
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.Continue »
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.Continue »
Here is a 2010 new year gift to all our readers – a free 2010 calendar excel file (a little secret: just change the year in “outline” sheet from 2010 to 2011, to get the next years calendar. It works all the way up to year 9999). You can even get a Printable PDF Calendar. There are 14 different sheets in the calendar file. First one is an outline calendar for the entire year. The next 12 are monthly calendars. And the last sheet shows a mini-calendar for the year 2010.
Read the rest of this post to know more and download the calendar.Continue »
Today we will learn a new and exciting excel formula – the all powerful SUMPRODUCT.
At the outset SUMPRODUCT formula may not seem like all that useful. But once you understand how excel works with lists (or arrays) of data, the SUMPRODUCT’s relevance becomes crystal clear.Continue »
We all have atleast one story of how that one time the boss / co-worker / classmate / cat ruined the carefully crafted excel spreadsheet by mucking up the formulas or disturbing the formatting. Read this post to learn 10 awesome excel tips to make better and boss-proof excel sheets.Continue »
I have a super exciting news for all our members. During the last several weeks, I have been working on making 24 strikingly remarkable and easy to use excel templates for better project management. Finally the bundle is ready. You can get a copy of the bundle starting today.Continue »
Project management dashboards, project status reports help stakeholders, project sponsors and team-members can understand project status very quickly. In the last installment of project management using excel, learn how to make project management dashboard using excel in this tutorial. Also download the excel project status dashboard template.Continue »
Over the last few years, there has been much debate about the merits and perils of Microsoft Ribbon UI in Excel 2007. Personally I think ribbon is a good way to explore an application. I have gotten used to it since I tested excel 2007 for first time. Now, during the rare occasions I work […]Continue »
Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.
What is an excel table?
Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”Continue »