Archive for February, 2010

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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. […]

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 […]

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 […]

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 […]

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.