Monthly Archives: March 2013

3 Guidelines to Simplify Your Excel PivotTables

PivotTables are one of the most useful of all tolls in Excel; however their use causes some concern with many users. This is due sin part because there appears to be no definite rules when building PivotTables. If you have let’s say 8 columns of data this will produce 8 PivotTable ‘fields’, but there are only 4 ‘field areas’ – so where do they all go?! The answer is that whilst there may be no definite rules for the construction of PivotTables, there are three very useful guidelines which help greatly when building them.

The first guideline is to initially isolate all of your ‘value fields’. These fields will almost always need to be placed into the value field area which is situated in the bottom right hand corner of the values pane. It doesn’t matter how many values there are as you can simply stack them on top of each other by clicking and dragging into the particular area. The order in which you stack these values will determine the sequence in which they appear from right to left in the actual PivotTable. The first value field will appear in column A with the next one down in the list appearing in column B and so on. In this way you can take care of many of your columns of data in one go.

The second guideline concerns the row fields. As with value fields, the order in which you stack these will determine the sequence in which they appear within the PivotTable itself. One handy piece of advice is to consider the fields which are to be placed in rows and determine how many separate areas of data relate to each one. For instance if you have one field for months and another for quarters, there will of course only be 4 quarters as compared with 12 months. If you therefore click and drag the quarters onto the row area first, followed by the months, you will create a form of data hierarchy which will make filtering and analysis somewhat easier.

The third guideline is in respect of the ‘Report filter’. Bringing columns into this field creates a filter which sits outside of the PivotTable itself. This enables us to filter all of the date within the PivotTable in one action. What can be useful about the Report filter is that because it is located outside of the main table, we can bring multiple fields into this area that we might otherwise struggle to find a logical place for. Therefore, any fields that you have left over after laying out the basic PivotTable can be brought into the report field area, providing you with enhanced filtering capability.
PivotTables are an immensely useful tool in Excel, but many are put of using them due to the amount of data in their spreadsheet.

The hope is that this article might encourage more people to experiment with them and eventually include them in their daily office work .

How to Avoid the Most Common Mistake in Microsoft Project

Microsoft Project is widely used by project managers across the World, but many are unaware of the traps they are falling into when teaching themselves.

The most common of these is the habit of entering start and finish dates for tasks. It is easy to see why PMs fall into this trap as one look at the Task Sheet seems to suggest that this is what should be done. There is a column for both start and finish dates and the assumption is that as tasks must be scheduled, these must be manually entered. In fact, entering dates directly into these fields creates what MS Project refers to as a ‘constraint’. A constraint prevents a task from automatically rescheduling if it is affected by another task and whilst sometimes this is desirable, it takes away much of the software’s flexibility.

The correct way to determine start and finish dates is by creating ‘dependencies’ between tasks. Dependencies allow us to schedule tasks depending upon the relationships w between them within the project. For example, if we have a task which is to lay foundations for a building and the another which is to build the walls, the walls must be erected after the foundations have been completed. The relationship between these two tasks is what MS Project describes as ‘Finish to Start’. In other words, the second task cannot start until the first task has finished. Scheduling tasks in this way ensures that the plan remains flexible and is free to move should we encounter a deviation from our original project.

There are 4 dependency types:

  • Finish to Start
  • Start to Start
  • Finish to Finish
  • Start to Finish

Using these linkages in combination within the project we can schedule tasks to begin and end precisely on time and leave us with the option to reschedule should the need arise. To create task dependencies, double click on a task name to open the information window. Select the Predecessors tab and then type the ID of the task you wish to link back to (note that we always link backwards to an earlier task). The dependency type can now be chosen by clicking in the Type field and selecting from the menu.

Microsoft Project is a remarkably flexible scheduling tool if it is used correctly. By making sure that you link your tasks through the correct choice of dependency, you will ensure that you get the most from what the software has to offer.