jeffreyellis.org

the homepage of Jeffrey Ellis

Excel Templates

Overview

Some problems can be solved more readily by a Microsoft Excel spreadsheet than by writing a C++ app (even though C++ would be more fun). This page is for a couple of the more useful Excel templates I’ve created.

Poor Man’s Gantt Chart

The Poor Man’s Gantt Chart is an example of how to emulate very simple Gantt charts in Microsoft Excel. The Gantt chart is drawn with rows representing tasks and columns representing dates — i.e., it is NOT an inserted “chart” object within Excel. The timeline for each task is created automatically via Excel’s conditional formatting, based on start and end dates. A different color shows the percent complete portion of each task. This “poor man’s Gantt chart” is a quick and dirty alternative to more complicated (and expensive) tools such as Microsoft Project.

(Click to view larger)

Click here to download the template. It is an .xlsx file, and should work in Excel 2007 and later.

Trade Study Template

The Trade Study Template for MS Excel is a spreadsheet template that implements a simple weighted sum method for multiple criteria decision making. The template includes tools for sensitivity analysis, including a stacked bar chart of results that dynamically updates as evaluation criteria weighting factors are adjusted.

(Click to view larger)

Version 2.0 now includes the ability to specify and customize the utility function for each evaluation criteria.

Click here to download version 2.0. It is an .xlsm file, and should work in Excel 2007 and later Excel 2010 and later. (In Excel 2007 the stacked bar chart will not appear properly, as it depends on the Excel 2010 feature that allows conditional formatting to refer to cells in other tabs.)

Click here to download a version 2.0 that works in Excel 2007. Fix provided by Marc Christensen. (Thanks Marc!)

Click here to download version 1.0.

Copyright © 2016 by Jeffrey Ellis. All rights reserved. Frontier Theme