All articles in 'Learn Excel' Category

Custom Views in Excel – Save filter and header settings for quick reference

Published on Dec 17, 2008 in Learn Excel
Custom Views in Excel – Save filter and header settings for quick reference

PHD reader and commenter Vishy contributed this post through e-mail. Thank you so much Vishy for this very useful tip. The Problem: You have created a specific view of your data in Excel (say by filtering, zooming out, changing column width, hiding specific rows, customizing window settings, print settings etc.). This is your reference point […]

Continue »

2009 Calendar Template in Excel – Now with week starting on Monday

Published on Dec 16, 2008 in Learn Excel
2009 Calendar Template in Excel – Now with week starting on Monday

Stružák, one of the commenters on the 2009 Excel Calendar Template post asked me if I can rearrange the cells in the calendar so that the week could start on Monday. Since I didn’t have access to internet for a while, I have asked him to make the necessary changes and mail it to me […]

Continue »

Formula Debugging in Excel – Tip [spreadcheats]

Published on Dec 15, 2008 in Learn Excel
Formula Debugging in Excel – Tip [spreadcheats]

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.

Continue »

Excel Links of the Week [Dec 15]

Published on Dec 15, 2008 in excel links

Finally I have got internet connection at home. The folks at Airtel Broadband are very fast and professional. They set up the connection in very few days. So I will be able to blog regularly from now on.

Moving to this week’s excel links you should browse;

Continue »

Interview with Charley Kyd on Everyday Excel

Published on Dec 11, 2008 in Learn Excel

As mentioned earlier, I have conducted a small interview with Charley Kyd – an Excel MVP, author of four books and 50+ articles for various national media, owner of exceluser.com and creator of popular products like plug-n-play excel dashboard kit. He sent me the answers almost a week back, but I could push the interview only today due to my travel and settling down stuff. As expected the interview is very entertaining and useful. I hope you like this.

Continue »

Brick Charts in Excel – an Alternative to Gridlines

Published on Dec 10, 2008 in Charts and Graphs, Learn Excel

Grid lines provide great help in understanding values in a chart. Here is a handy trick you can use in the next bar chart to spice it up. Here is how you can get this type of chart (we will call it a brick chart) First we will make a regular bar chart Now, let […]

Continue »

Free Excel Calendar Template for year 2009 (and all years up to 9999)

Published on Dec 4, 2008 in Featured, Learn Excel, personal
Free Excel Calendar Template for year 2009 (and all years up to 9999)

Free excel calendar template for year 2009 (well, it works for any year all the way up to 9999)

Go ahead and download it, change the year number in the first sheet to 1981 or something and see the magic.

Continue »

What is camera tool and how to use it? [spreadcheats]

Published on Dec 2, 2008 in Charts and Graphs, Learn Excel
What is camera tool and how to use it? [spreadcheats]

Camera tool is your way of creating visual reference in an excel sheet. It is one of the useful and hidden features of excel. Here is how it works. You specify a rectangular area in your workbook and camera tool creates a mirror image of that area as a drawing object. You can move it or resize it. And whenever the contents of original rectangular area changes (charts, drawings or cell values) the mirror image changes too.

Continue »

Best of Pointy Haired Dilbert – Nov 2008

Published on Dec 2, 2008 in blogging, Learn Excel
Best of Pointy Haired Dilbert – Nov 2008

November 2008 has been the best month since I started this blog and thanks to all the readers and commenters for your tremendous support.

We had 33 posts and 331 comments (highest ever). We also had 150,000 page views, 50% more than last month and highest ever. Our RSS subscriber base is now at 1750 and growing everyday. This is a great feeling and I couldn’t have done any of this without your constant support and motivation. I am thankful to each and everyone of you for helping me learn and share whatever I can.

Continue »

Excel Links of the Week [Dec 1]

Published on Dec 1, 2008 in excel links

Every Monday PHD shares some of the best excel related articles around the web so that you are up to date with discussions and new ways to be productive. Here is a list of links recommended for this week.

Continue »

Say thanks with an excel tip

Published on Nov 26, 2008 in blogging, Featured, Learn Excel

Tomorrow is thanksgiving day here. It is a harvest festival on which people express thanks for what they have. Much like Sankranthi (pongal) in India. At this moment I am very angry and frustrated about the terror strikes in Mumbai. I am not sure how a person can do something like this to others. I […]

Continue »

Prevent users from scrolling away on your dashboards [dirty little trick]

Published on Nov 25, 2008 in Charts and Graphs, Learn Excel

Dashboards let users get all the information they want in a quick glance. Not if users start using scroll bars and scroll down to cell A64000. So here is a quick and dirty trick. Assuming your dashboard ends in row 40, select row 41 and go to menu > window > freeze panes. That way […]

Continue »

Advanced Data Validation Techniques in Excel [spreadcheats]

Published on Nov 25, 2008 in Learn Excel
Advanced Data Validation Techniques in Excel [spreadcheats]

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

Continue »

Excel Links of the Week – PHD’s new tag line [Nov 24]

Published on Nov 24, 2008 in blogging, excel links

This is probably the 100th time I have changed this site’s tag line. So, it may not be a great news to you. But again, I need to say out this loud so that you, my dear reader, will know what this blog is going to stand for: Excel Tips, Charting Ideas and Visualization Oomph. […]

Continue »

Introducing Excel Formula Helper

Published on Nov 24, 2008 in blogging, Learn Excel
Introducing Excel Formula Helper

One of the most common comments we get here is “how does xxx formula works?” Even though there is vast documentation on excel formulas including the built-in help in MS Office, often it is difficult for us to learn a formula quickly and use it in a snap. So to help new users of excel […]

Continue »