Archive for February, 2010

Sachin Tendulkar ODI Stats – an Excel Info-graphic Poster

Sachin Tendulkar ODI Stats – an Excel Info-graphic Poster

Sachin Tendulkar is undoubtedly the best cricketers to play One Day International Cricket. He is a source of inspiration and joy for me and many others. So, naturally I was jumping with joy when I heard that he scored the highest ever runs in a single match on 24th of Feb. He scored 200 runs […]

Continue »

Adding Calculated Fields to Pivot Table P&L [part 3 of 6]

Published on Feb 25, 2010 in Learn Excel, Pivot Tables & Charts
Adding Calculated Fields to Pivot Table P&L [part 3 of 6]

This is part 3 of 6 on Profit & Loss Reporting using Excel series, written by Yogesh Data sheet structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget […]

Continue »

Use Paste Special to Speed up Chart Formatting [Quick Tip]

Published on Feb 24, 2010 in Charts and Graphs
Use Paste Special to Speed up Chart Formatting [Quick Tip]

Excel Paste Special is one of my favorite features. So I was naturally thrilled when I discovered that you can use paste special to paste formatting from one chart to another. When we have multiple charts, ensuring consistent color schemes, fonts etc. is very important. Often we individually format the charts because they have different […]

Continue »

Find and Remove Blank Items from a Range of Cells [personal experience]

Published on Feb 23, 2010 in Excel Howtos
Find and Remove Blank Items from a Range of Cells [personal experience]

Most of you know that during day time I work as a business analyst. Today while preparing some test scenarios for our latest insurance application, I came across a weird problem. There are some steps in testing. For each test scenario, a combination of these steps is required. It is my responsibility to identify the […]

Continue »

Comprehensive List of Excel Keyboard Shortcuts

Published on Feb 22, 2010 in Featured, Keyboard Shortcuts, Learn Excel
Comprehensive List of Excel Keyboard Shortcuts

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 »

How do you consolidate data from multiple sheets in to one? [open thread]

Published on Feb 19, 2010 in Excel Howtos
How do you consolidate data from multiple sheets in to one? [open thread]

Long time PHD reader and mother of a lovely kid, Michelle, sent me a question in email that provoked me to write this post, I was wondering how to tabulate large amount of information gathered through surveys. Where I work customers are constantly handed survey sheets in order for us to measure how the service […]

Continue »

Excel School Last Call, Closing in Few Hours – Join Now!

Published on Feb 17, 2010 in Learn Excel, products
Excel School Last Call, Closing in Few Hours – Join Now!

If excel school were to be a bar, this post is your last call. Come one, come all and order the course now. Click here to sign-up for excel school (on a lighter note, if excel school were to be a pie, we wouldn’t be having this conversation :P) How many students have joined the […]

Continue »

Save a ton of time by using Mouse scroll-wheel to Navigate Excel Ribbon

Published on Feb 16, 2010 in Learn Excel
Save a ton of time by using Mouse scroll-wheel to Navigate Excel Ribbon

Today’s quick tip is by far the quickest and most profitable tip of all. To switch between ribbon tabs or to change to a particular tab, you can use mouse scroll-wheel. Just place mouse pointer on a ribbon tab, and scroll-down or up using your mouse’s scroll-wheel and you can switch between tabs. Learn more […]

Continue »

Excel Links – Winner Takes it all Edition

Published on Feb 16, 2010 in excel links

Remember the Sales Visualization Challenge? We got 32 extremely good dashboards submitted and finally you voted Alex Kerin’s entry as the winner. So when I informed Alex that he is the winner, I also asked him to send me a pic of him with the iPod Touch that he won. Yesterday, he sent it to […]

Continue »

Welcome to Pointy Haired Dilbert – Know a Little About YOU

Published on Feb 15, 2010 in blogging

Hello dear reader, Welcome to Pointy Haired Dilbert. Know a little about yourself. Read this, You are awesome. You want to learn excel, become better at charting. You like to experiment with excel and see what more you can do with it. You want to be better at your work. Excel plays a major role […]

Continue »

Only 5 days left, Sign-up for Excel School Today!

Published on Feb 12, 2010 in products

A quick update on Excel School before you start your weekend. So far Excel School – online excel training program has been very well received by our members at PHD. How are the registrations so far? We have 44 students in the classroom. Every day I am hearing from people who are willing to join. […]

Continue »

What is the most useless feature in Excel? [poll]

Published on Feb 12, 2010 in Charts and Graphs
What is the most useless feature in Excel? [poll]

What do you think is the most useless feature in MS Excel? I know there are a ton of cool, extremely useful features in excel. But it also comes with a few really un-necessary features. For me, the most useless feature seems to be a 3D surface chart. I have tried to figure why this […]

Continue »

Group Project Activities to Make Readable Gantt Charts

Published on Feb 11, 2010 in Charts and Graphs, Learn Excel
Group Project Activities to Make Readable Gantt Charts

In Excel Gantt Charts part of our project management series, we have discussed about how using Conditional Formatting and Formulas we can make a gantt chart like this: But when you have large project plans, gantt charts like above can get pretty intense and hard to read. So a better approach is to group various […]

Continue »

Preparing Profit / Loss Pivot Reports [Part 2 of 6]

Published on Feb 10, 2010 in Learn Excel, Pivot Tables & Charts
Preparing Profit / Loss Pivot Reports [Part 2 of 6]

This is part 2 of 6 on Profit & Loss Reporting using Excel, written by Yogesh Data sheet structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget V/s […]

Continue »

What is Excel SUBTOTAL formula and 5 reasons why you should use it

Published on Feb 9, 2010 in Learn Excel
What is Excel SUBTOTAL formula and 5 reasons why you should use it

Today we will learn Excel SUBTOTAL formula and 5 beautiful reasons why you should give it a try.

SUBTOTAL formula is used to find out subtotal of a given range of cells. You give SUBTOTAL two things – (1) a range of data (2) type of subtotal. In return, SUBTOTAL will give you the subtotal for that data. Unlike SUM, AVERAGE, COUNT etc. which do one thing and only one thing, SUBTOTAL is versatile. You can use it to sum up, average, count a bunch of cells.

Continue »