fbpx

All articles with 'Excel Howtos' Tag

Format Charts Faster in Excel 2007 [quick tips]

Published on Nov 30, 2009 in Charts and Graphs, Excel Howtos
Format Charts Faster in Excel 2007 [quick tips]

Most of us use chart formatting options to change the way grid-lines, data series, labels, axis, titles, plot areas look. Chart formatting is one of the areas where people spend most time. Today I want to teach you a quick productivity hack to speed up chart formatting. In excel 2007 and above, when you click […]

Continue »

Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]

Published on Nov 25, 2009 in Learn Excel
Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]

Every year, on 4th Thursday of November, folks in US celebrate Thanksgiving day. A similar holiday exists in Canada too, they celebrate it on Second Monday of Every October.We will celebrate thanksgiving in PHD style, by sharing a wacky formula tip.

Today, we are going to learn how to use excel formulas to find out thanksgiving day’s date for any year.

Continue »

Reverse a List using Formulas [Using Excel INDEX() Formula]

Published on Nov 19, 2009 in Excel Howtos, Learn Excel
Reverse a List using Formulas [Using Excel INDEX() Formula]

Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list. In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down. Boom, you get the reversed list. Here is how the formula works: In the reversed list, first item […]

Continue »

Transpose Excel Rows and Columns [Quick Tip + Video]

Published on Nov 18, 2009 in Excel Howtos, Learn Excel
Transpose Excel Rows and Columns [Quick Tip + Video]

Transposing rows and columns in a table is one of the most useful tricks when you are pasting data. Yet, it always surprises me that very few people actually know this. So here it is…

Continue »

What is Excel SUMPRODUCT formula and how to use it?

Published on Nov 10, 2009 in Featured, Learn Excel
What is Excel SUMPRODUCT formula and how to use it?

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 »

Switch Rows and Columns in Charts [Quick Charting Tip]

Published on Nov 2, 2009 in Charts and Graphs, Excel Howtos
Switch Rows and Columns in Charts [Quick Charting Tip]

Let us say you have built a nice chart showing your sales and profits for the top 5 products (learn how to highlight top 5 products in a list), with products on X axis. Suddenly your boss wants to switch the rows to columns (or transpose the chart) so that she can see metric level grouping instead of product level grouping. No need to freak out and rush to Espresso machine, You can do it very easily with Excel Charting features.

In today’s quick tip you will learn how to swap chart rows and columns in excel.

Continue »

Copy Data Validations from one cell to another [quick tip]

Published on Oct 28, 2009 in Excel Howtos
Copy Data Validations from one cell to another [quick tip]

If only there is paste special for everything in life, things would be much more smoother and fun. Alas, the paste special is limited only to excel.

Here is a handy trick you can use to quickly scale up data validations set up in one cell to say a range of cells.

Just copy the cell by pressing CTRL+C and then go to the target cell(s) and press ALT+ESN. And your data validations are pasted in the new cells. That is all. Quick and Easy.

Continue »

Prevent Duplicate Data Entry using Cell Validations

Published on Oct 26, 2009 in Excel Howtos
Prevent Duplicate Data Entry using Cell Validations

We all know that data validation is a very useful feature in Excel. You can use data validation to create a drop-down list in a cell and limit the values user can enter. But, do you know that you can use data validation in a multitude of ways to prevent users from entering wrong data?

Here is a practical application: Prevent users from entering duplicate values in a range of cells.

For eg. you are making an invoice. Wouldn’t it be cool if Excel prompted you when you enter a duplicate line item so that increase the item quantity instead of repeating it.

Continue »

How to get Excel 2003 Toolbars in Excel 2007 [productivity hack]

Published on Oct 19, 2009 in Excel Howtos
How to get Excel 2003 Toolbars in Excel 2007 [productivity hack]

In our recent poll, I have asked you to tell me which buttons you have on the quick access toolbar?

Finnur, one of our readers has configured the Excel 2007 quick access toolbar to make it look like Excel 2003 toolbar.

I think this is a very cool way to max up your productivity, it takes 5 minutes to make your quick access bar look like Excel 2003 toolbar and you could save countless hours of “searching-for-that-command-in-ribbon” time.

Continue »

Another Reason why Tables are so awesome [quick tip]

Published on Oct 8, 2009 in Excel Howtos
Another Reason why Tables are so awesome [quick tip]

Ever since I have learned the tables feature in Excel 2007, I have fallen in love with that. They are so awesome and so user friendly.

Here is a simple, yet very effective feature of Excel Tables that will show table header row even when you scroll down. The header row is shown in the place of column headings (the place where you see A,B,C,D etc.).

Continue »

Make a Bubble Chart in Excel [15 second tutorial]

Published on Oct 5, 2009 in Charts and Graphs, Excel Howtos
Make a Bubble Chart in Excel [15 second tutorial]

A Bubble chart displays circles (or bubbles) at given X and Y co-ordinates. Bubble chart is a very good way to show 3 dimensional data (for eg. Region-wise product sales) without confusing users. In this tutorial, we will learn how to create a bubble chart using excel.

Continue »

Calculate Elapsed Time in Excel [Quick Tips]

Published on Sep 22, 2009 in Excel Howtos, Learn Excel
Calculate Elapsed Time in Excel [Quick Tips]

Calculating elapsed time is very common whether you are managing a project or raising a baby. Elapsed time is nothing but interval between a starting point and the current point in time. We can use excel formulas to calculate elapsed time very easily. In this post, learn how to calculate elapsed time in days, working days, hours, weeks, months, years, minutes and seconds.

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 »

Time to showoff your VBA skills – Help me fix ActiveSheet.Pictures.Insert snafu

Published on Jul 30, 2009 in VBA Macros
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 »