I was recently asked if my Gantt Chart Template could be used to compare actual dates to original project dates. The simple answer is "yes, depending on how you want to show the difference." In this post, I'll explain how you can currently do this and also propose a new version of the Gantt Chart Template Pro that makes it easy to see visually.
1. Keep a Backup Copy of the Original Project Plan
Keeping a backup is always a good idea, and this is the most basic approach for keeping a record of the original plan. You can view files side-by-side or compare printouts.
2. Make a Copy of the GanttChart Worksheet and Rename It
You can make a copy of the Gantt Chart worksheet within the same workbook and rename the worksheets GanttChart-Actual and GanttChart-Plan (see the image below). Of course, these worksheets will be entirely independent, but it's an easy solution for keeping a copy of the original project plan. This still doesn't provide a good solution for easily comparing planned and actual dates, though.
3. Add New Columns for Storing the Original Dates
It's easy to insert new columns in the XLSX version of the gantt chart. Many people do that to add a budget column, and in this case you could add columns to keep track of original start and end dates.
With the new columns, you can compare dates side-by-side, but that still may not be as visual as you'd like.
4. Showing the Original Dates in the Gantt Chart
The first two approaches don't make it easy to visually compare differences between the planned and actual dates. The third method gets us part way there, but many people have wanted to see the differences in the Gantt Chart itself.
Thanks to Excel 2007+, which provides a more powerful set of conditional formatting rules than Excel 2003, it's possible to show the planned and actual project dates within the same chart. The screenshot below shows a proposed new feature of Gantt Chart Template Pro that provides an option for displaying original dates as a diagonal fill pattern.
You'll see that this new version includes two new data entry columns: Original Start and Original End. After you create your plan using the normal approach, you can copy the dates from the Start and End columns into the two new columns using Copy and Paste Special > Values.
You can hide the Original Start and Original End columns if you do not want to use them or display them. You can also turn on/off the display of the original dates in the Gantt Chart by unchecking a box.
Current customers can return to the download page and download a copy of the gantt-chart_v4-0_pva.xlsx file. The older gantt-chart.xlsx file has the planned vs. actual method included as a separate worksheet (GanttChart-BETA). Note that this is only an update for the XLSX version of the spreadsheet. This new feature will not be added to the old Excel 2003 (XLS) version.
UPDATE 3/19/2019 :: This feature isn't available in all versions of Gantt Chart Pro, but I recently figured out a way to make this work even in Excel Online and mobile Excel, using the same technique as I've described below for Google Sheets. Please contact me if you want this feature to be available in Excel for Office 365.
Planned vs. Actual in Google Sheets
Update 3/19/2019: Although cross-hatching isn't currently possible as a conditional formatting option in Google Sheets, I figured out how to implement it using text ("////"). So, the Pro version in Google Sheets now includes the option to display the original planned schedule, as demonstrated in the image below.
Please Let Me Know What You Think
I would love some feedback on this new feature, especially regarding the following questions:
A) You'll see from the screenshot that the two new columns use conditional formatting to highlight dates green or red if the start and end dates are ahead or behind schedule, respectively. Is this conditional formatting useful or just redundant?
B) I could either include the new version as an optional worksheet (like I'm doing now) or I could make this a standard feature and hide the two new columns by default - letting the user decide to unhide them if they want to. What would you prefer?
C) There are many fill patterns that could be used to show the original dates in the gantt chart. I picked the diagonal lines because they show up well on different color bars, but if you find something that you think works better, please let me know.
D) At the moment, the diagonal lines are only shown if the original dates are different than the current dates. See Row 23 in the screenshot above to see what it looks like when there is no change in the dates. Would it be better to show the diagonal lines regardless (noting that you can turn them all off if you want to)?