All articles with 'howtos' Tag

Grouping Dates in Pivot Tables

Published on Nov 17, 2009 in Learn Excel, Pivot Tables & Charts

Do you know you can group dates in pivot tables to show the report by week, month or quarter? I have learned this trick while doing analysis on a pivot table today. In this online lesson on pivot tables, I will teach you how to group dates in pivot tables to analyze the data by month, week, quarter or hour of day.

Continue »

10 Tips to Make Better and Boss-proof Excel Spreadsheets

Published on Nov 3, 2009 in Excel Howtos, Featured, Learn Excel

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 »

Making Interaction Plots using Excel

Published on Oct 29, 2009 in Charts and Graphs

Yesterday I read about interaction plots on junk charts where he points out the merits of an interaction plot. Interaction plots show interaction effects between 2 factors. For eg. you can show how your product sales have changed between year 1 and year 2 using an interaction plot like the one shown aside.

Continue »

Making a chart with dynamic range of values

Published on Oct 15, 2009 in Charts and Graphs, Excel Howtos

We all know that to make a chart we must specify a range of values as input.

But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?

Well, you can do it very easily using excel formulas and named ranges. It costs just \$1 per each change. ðŸ˜‰

Ofcourse not, there are 2 ways to do this. One is to use Excel Tables and another is to use OFFSET formula.

Continue »

Excel Check Boxes, Even Simpler Way

Published on Sep 15, 2009 in Excel Howtos, Learn Excel

In how to get tickmarks in excel, Jon commented,
“[…] Better yet, use real checkboxes, so the user can change them with the mouse.”

That got me thinking,

In excel, you can add a checkbox to spreadsheet using developer tools. But what if you need a whole bunch of checkboxes, one in each cell?

Continue »

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

Published on Jul 30, 2009 in VBA Macros

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 »

Make an Impressive Product Catalog [spreadsheets for small business]

Published on Jul 13, 2009 in excel apps, Learn Excel

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 »

Create a number sequence for each change in a column in excel [Quick Tip]

Published on Jun 29, 2009 in Excel Howtos, Learn Excel

Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]

Continue »

Remove Duplicates [Quick Video Tip]

Published on Jun 22, 2009 in Excel Howtos

It is very surprising that so many of us are still not aware of this extremely powerful tool in Excel 2007. Just with a click of the button, you can remove all the duplicate values from a list / table. To remove duplicates, (1) Just select the list of values from which you want to remove duplicates (2) Go to Data ribbon (3) Hit the Remove Duplicates button (4) Bingo…

Continue »

Converting Excel to PDF – Everything you need to know

Published on Jun 4, 2009 in Excel Howtos, Featured

Ok, so you have that neat little excel report ready and now you want to share it with your boss or client in a PDF format. So how would you do that? Your best option is …

Continue »

Intraday Candlestick Charting [Stock Charts]

Published on May 28, 2009 in Charts and Graphs

Gene asks me in an email, “I’m trying to graph candlestick charts in Excel for 10 minute candles. Excel seems to allow daily only with its stock templates. Can you point me to any resources for creating intraday candle charts?”. Of course, you can create intraday candlestick charts just the way you would create normal candlesticks, just change the axis options once you are done. Read the post to find more.

Continue »

Excel Tips Submitted by You [Part 3]

Published on May 13, 2009 in Excel Howtos, Learn Excel

In the third installment of your week at PHD, we have a fantastic tutorial on R1C1 style of formula referencing, a nifty vlookup hack and adding web toolbars to excel UI. We have one more day to go, so rush!

Continue »

Excel Colors Somehow Getting Changed ?

Published on Feb 13, 2009 in Excel Howtos, Learn Excel

Reader Raja asks about “how he can reset the colors in his workbook”. Read more to find out how you can fix this problem.

Continue »

Review of PTS Clustered Stack Chart

Published on Feb 9, 2009 in Charts and Graphs, Learn Excel

My Friend and long time Microsoft Excel MVP, Jon Peltier has released a wonderful little excel charting utility called Cluster Stack Chart Utility. Out of curiosity I have mailed Jon and asked him if he could send me a copy of it so that I can review the product and recommend it to PHD reader community. He is kind enough to mail the add-in to me and here goes my review of the tool.

Continue »

Write Your Own Twitter Client using Microsoft Excel

Published on Feb 5, 2009 in excel apps, Featured, Learn Excel

Learn how you can write your own twitter client using Excel. All it takes is excel, internet connection and 5 minutes of spare time.

Continue »