Post navigation

Unique IDs in Excel

Continuing our series on unique IDs, this week’s post will cover how to correctly establish and maintain a unique ID in an Excel spreadsheet that you are planning to use with OnePager Express. The focus of this article is for project plans that you are building from the ground up in Excel.

If you are using Excel to transfer data from another PPM tool like Planisware, Primavera, or Smartsheet, you’ll want to wait for our next article, which will provide specific instructions for each of those platforms.

When creating a unique ID in Excel, you want to use a field where all the values can be (1) unique and (2) consistent. It doesn’t really matter what you name the field as long as you adhere to these two principles. The unique IDs themselves can be any format that you like. Most users prefer a simple numeric ID, but if you want to create something different, you certainly can.

In this example, our simple Excel spreadsheet has a field called ID which is what we’ll use for the unique ID. Notice that all four IDs have a distinct value (1, 2, 3, 4). The same value never repeats in the unique ID field:

Once you’ve established a unique ID in Excel, you want to make sure that OnePager Express knows it’s there. If you call your Excel field Unique ID or even just ID, there’s a pretty good chance that OnePager will pick it up automatically. It never hurts to double-check, though. When you first build your chart, click on the Next > button to show your Excel field mappings. As long as your unique ID field is selected as the unique ID, you’re all set.

It’s important to make sure that your unique ID is set up before you build your OnePager chart. Once you’ve built a OnePager chart, there is not a way to add a UID where there wasn’t one, or to switch from a poor field choice to a good one, so make sure you get it right early on.

Making Changes to your Excel Spreadsheet

We get it. Project plans change. The spreadsheet that you used to build your original OnePager Express chart isn’t going to stay the same. In fact, it’s almost guaranteed to change as your project evolves. When making changes to your Excel project plan, keep an eye on your unique ID, and it’ll make updating your OnePager chart a breeze.

In this example, we’ve added a row to the Excel file right in the middle of the existing tasks. We now have a break in the unique IDs where there isn’t a UID assigned to the new task:

It can be very tempting to re-number unique IDs so that they stay “in order”, like this:

Whatever you do, DO NOT DO THIS! This is the OnePager equivalent of cutting the brake lines in your car and taking a leisurely cruise down a winding mountain road. It is guaranteed not to end well.

As innocent as this may seem, this kind of renumbering is going to wreck your OnePager chart. OnePager is going to always treat the task with ID #3 as “Task 3”, even though that’s been reassigned to a completely new task. The task called “New Task” will never appear in your chart, even though you added it to Excel. At the same time, there was never a task with ID #5 originally, so the old “Task 4” which actually did exist previously will be treated as a brand-new task, even though has been there all along. Meanwhile, what you think of as “Task 3” will actually be “Task 4” in OnePager.

In short, it’s a mess.

When adding tasks to your Excel file, it’s ok if your IDs aren’t in order. In fact, you can really give “New Task” any UID that you want as long as it’s not being used elsewhere.

Here’s an example of a good solution where the UID for “New Task” is a decimal number halfway between its two neighboring tasks:

It’s also OK to assign a unique ID to the new task that’s completely out of order. Prior to adding the new task, the highest UID was #4, so the new task can be #5, even though it’s not in sequence.

In fact, the newest unique ID doesn’t even need to be a number, even if all of the others are:

So remember, it’s normal to add rows to your spreadsheet and it’s normal for your unique IDs not to be ordered perfectly. Just keep them unique and consistent. You’ll have perfectly-updated OnePager charts every time.

Unique IDs from Multiple Excel Files

If you read our earlier post on multi-project reporting in Microsoft Project, you know that unique IDs aren’t always unique between one project and another, which can cause issues if you’re trying to build a multi-project report. The same principle applies in Excel.

If I have two Excel files named Project A and Project B, and both of those files have tasks with UIDs numbered 1-5, those UIDs may work within each file individually, but they won’t work at a portfolio level.

To address this, we recommend setting up your UIDs to reflect both the project name and the UID itself. Here’s Project A:

And here’s Project B:

In both cases, we appended a letter “A” or “B” in front of the numeric unique ID, ensuring that the two Excel files won’t collide if combined into the same report.

About Safford

Safford is a versatile technology professional with a solid history of empowering emerging growth companies in a broad array of industries. His employment history includes energy industry consulting at Quorum Business Solutions, Senior Manager of Business Development and Technical Sales at telecom service aggregator GetConnected, and Vice President of Strategic Partner Management at electronic payment processor IP Commerce. Prior to his tenure as OnePager's COO, Safford was the company's Vice President of Marketing and Alliances. Safford holds a BA in Psychology and management from Rice University.