All articles in 'Learn Excel' Category

It is Easter Time again. Although I am not in Scandinavia (where Easter is a big thing) any more, we have a rather biggish family gathering here at PHD household during this weekend. We are also having anna prashanam for the little ones. It is the ceremony we do in India when kids eat rice […]

A panel chart (often called as trellis display or small-multiples) shows data for multiple variables in an easy to digest format. It lets users compare in any way and draw conclusions with ease.

Today, I want to discuss how the principles of panel chart can be applied to visualize a complex set of survey results. For this we will use the recent survey conducted by Gartner on how various customers use BI (Business Intelligence) tools.

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.

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…,

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

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…,

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.

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

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.

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

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]

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

We all know the AND, OR & NOT formulas in Excel using which you can perform simple logical operations And, Or & Negate. But what if you are the chief of HR at ACME Company, where they have a strange rule on extra allowance like this: Now, to calculate the dates in a month that […]

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

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