Archive for March, 2010

101 Excel Secrets – Recommended E-Book

Published on Mar 31, 2010 in products
101 Excel Secrets – Recommended E-Book

Francis J Hayes runs probably one of the most popular excel news letters. His email newsletter, called as “The Excel Addict” has an interesting nugget, tip or trick every week. I have been reading his emails for the last few months and I really enjoy them. So naturally I got curious when I heard about Francis’ e-book titled “101 Secrets of a Microsoft Excel Addict“. Yesterday I purchased a copy of the book and there are so many hidden gems in the book that I had to tell you about it.

Continue »

Making a Dynamic Dashboard in Excel [Part 2 of 4]

Published on Mar 30, 2010 in Charts and Graphs, Learn Excel
Making a Dynamic Dashboard in Excel [Part 2 of 4]

In part 2 of Excel Dynamic Dashboard Tutorial, we will learn how to set up various dynamic charts that are part of the dashboard. We start with a simple dynamic pie chart that shows the sales distributions and then move on to sales trend line charts. These charts use various excel formulas to pull in the information based on user selection.

Continue »

How to Check whether a Table is Filtered or not using Formulas

Published on Mar 29, 2010 in Excel Howtos, Learn Excel
How to Check whether a Table is Filtered or not using Formulas

Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).

Often when we have large data sets, we apply data filters to select and display only information we want to see.

Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet.

But, what if you need a way to show “filtering” status thru formulas? Like this…,

Continue »

What do you use Excel for? [Weekend Poll]

Published on Mar 26, 2010 in Learn Excel
What do you use Excel for? [Weekend Poll]

It is Friday again, time for another poll on PHD. This time, we will keep it very simple. I want to know what you use excel for? I use excel for, Preparing dashboards, reports, automation solutions for my clients Making invoices, estimates, plans for my projects Tracking various project activities (bugs, issues, changes, requirements etc.) […]

Continue »

My First Podcast!!! – Listen to know some cool charting and dashboard stuff

Published on Mar 25, 2010 in interviews
My First Podcast!!! – Listen to know some cool charting and dashboard stuff

Just weeks before Christmas, Ross McLean, who runs Methods in Excel (a very cool and useful Excel blog) asked me if I can join him for a small podcast interview on things we both love – excel, charts and talking. I couldn’t be more happier to accept his offer. We did the podcast and life kept busy both of us for a while. That is until yesterday, when Ross made the podcast available online for all of us.

Please go here to listen to my first podcast interview.

Continue »

Quarterly & Half-Yearly Profit Loss Reports [Part 5 of 6]

Published on Mar 24, 2010 in Learn Excel, Pivot Tables & Charts
Quarterly & Half-Yearly Profit Loss Reports [Part 5 of 6]

This is continuation of our earlier post Exploring Pivot Table P&L Reports.

We have learned how to change our P&L report on various data elements. We have seen how the P&L report can be changed with just few clicks.

In this post we will be learning some grouping tricks in PivotTables. We will cover grouping of dates, text fields and numeric fields. You will need to start with Monthly P&L report prepared in previous post. We will also learn some really clever tricks and hacks on how to group data in Pivot Tables. So read on…,

Continue »

How to Convert Text to Dates [Data Cleanup]

Published on Mar 23, 2010 in Excel Howtos, Learn Excel
How to Convert Text to Dates [Data Cleanup]

Sometimes when we import data from another source in to excel, the dates are not imported properly. This can be due to any number of reasons. In this post, we will learn some tricks and ideas you can use to quickly convert text to dates.

Continue »

Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines

Published on Mar 22, 2010 in Charts and Graphs
Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines

Sparkline or Microchart is a tiny little chart that you can place on dashboards, reports or presentations to provide rich visualization without loosing much space. In excel 2010, MS introduced a beautiful feature for creating sparklines from data in spreadsheets. For earlier versions of Excel (that is 2007 and before) there is no native support […]

Continue »

Super Timesaver Tip – Add “Select Objects” to Quick Access Toolbar

Published on Mar 18, 2010 in Learn Excel
Super Timesaver Tip – Add “Select Objects” to Quick Access Toolbar

Here is a very quick, very short excel tip. Add the “select objects” tool to Quick Access Toolbar. In excel 2003 and earlier, this button is available in the bottom left corner (on the drawing toolbar) so that whenever you are working with charts or drawing shapes, you could easily select multiple objects and drag, […]

Continue »

Making a Dynamic Dashboard in Excel [Part 1 of 4]

Published on Mar 16, 2010 in Charts and Graphs, Featured, Learn Excel
Making a Dynamic Dashboard in Excel [Part 1 of 4]

In this and next 3 posts, we will learn how to make a Dynamic Dashboard using Microsoft Excel.

At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using excel formulas and simple VBA macros.

Continue »

Excel Links – Back to India Edition

Published on Mar 15, 2010 in excel links

As some of you know almost 10 days back I left Denmark and came back to India. I had to come back because of visa and personal issues. For the next 6 months PHDs will be based out of India and working from home. While this gives me a lot more time, I will be […]

Continue »

31 Excel Tutorials – Learn and Be Awesome in Excel

Published on Mar 11, 2010 in Charts and Graphs, Featured, Learn Excel
31 Excel Tutorials – Learn and Be Awesome in Excel

In October 2008, I have started an ambitious series of posts on this blog called – Spreadcheats. These are little tricks, nuggets, tutorials on using Excel that would make anyone a spreadsheet guru.

The spreadcheats series has been wildly successful. I am compiling all this useful information and articles in to one big post so that anyone can follow the links and become good in Excel. Read on,

[Note: This is not for beginners. If you know what a formula is, you would enjoy this 31 articles]

Continue »

Introducing Flag Project – A fun way to Learn Excel Charting

Published on Mar 8, 2010 in Charts and Graphs, Featured, India
Introducing Flag Project  – A fun way to Learn Excel Charting

I have a new community project for all the members of PHD. It is a simple yet intuitive challenge. Make your own country’s flag using Excel Charts. To start the project, I have made the Indian flag using a bar and pie chart. Go ahead and see the rest of this post to findout how this chart is made and then participate in the “flag project” by making your own country’s flag. All the best.

Continue »

Get a Copy of Excel Project Management Templates Today!

Published on Mar 4, 2010 in products

Some of you know that I sell a bundle of Project Management Excel Templates. The bundle includes 24 ready to use templates for Gantt Charts, Timesheets, Project Dashboards, Status Reports, Time Line Charts, Issue Logs, To do Lists and more. Launching these templates is one of the best moves in my life. They have been […]

Continue »

Exploring Profit & Loss Reports [Part 4 of 6]

Published on Mar 3, 2010 in Learn Excel, Pivot Tables & Charts
Exploring Profit & Loss Reports [Part 4 of 6]

This is part 4 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 »