Excel

I use Excel in a project management context for things like capacity planning and project estimation.

Principles

Excel is a tradeoff between ease of data entry and ease of querying. You can do a full normalized table in Excel, but it’ll be a pain to enter data that way—you’ll often want to denormalize to make it easier to put data in. Most of the time you’ll end up with something middle-of-the-road.

Separate data from computation. For example, enter data on one sheet, and query it on another.

Minimize data entry toil. This is a rich source of mistakes.

Try not to query too much over results of other queries. Errors can compound. Try to stay one hop away from the data source to keep some healthy separation between your queries.

Enter data in granular fashion so that you preserve the option to query it at that level later. Eventually it’ll happen. - For example, enter people’s hours of capacity per each day, not per week.

No gaudy formatting. No zebra striping, no colors. Bold and not-bold is enough. Align column headers with their data if you want.

Functions

A couple main functions to know for this kind of work:

• SUMIFS: Sum a range based on one or more conditions.
• VLOOKUP: Index into a lookup table.

Estimation

Excel is my usual tool for project estimation. After doing a work breakdown into tasks, I assign each task a t-shirt size and use a lookup table from another sheet to match them to hours, story points, or what have you. (VLOOKUP does the magic.)

You can make a decent project progress tracker out of this by assigning each task a status (for example, “Not started”, “In progress”, “In code review”, and “Done”), and then using a simple model in another lookup table of what percentage of the original estimate remains at each status.

Pivot tables

Pivot tables are great for answering quick client questions about budget.

Select your data and Alt + N, V. (Mnemonic: I envy your Pivot table skills, LOL)

Miscellanea

Searching for Excel documentation is awful. There seems to be an entire cottage industry of Excel tutorial spam websites, and you’ll find about a thousand of them when you look up any Excel function by name. Sometimes these are decent enough, but their content is fluffy and they seem to mainly exist to run ads.