Effectively Relabeling Data

Find & Replace seems like a great option at the time when you need to update certain labels or categories in your data. However, it is very difficult to ‘undo’ down the road. When you use find & replace, you have lost your original values that you replaced, and if you ever need to reference the original values, you will need to start over with your original dataset.  Additionally, there is no record of which data you updated to which new labels, and it may get hard to remember how you relabeled your data in the future.

One alternative to Find & Replace is creating a key to relabel data that includes a unique list of our existing values and the corresponding list of relevant relabeled values.  Relabeling data using a key is a systematic approach that avoids any manual re-labeling and is easy to update – ensuring we only have to do the work once.

Creating a key:

• Maintains the original integrity of our data: By keeping our existing values in our dataset, we can easily reference our original labels and change back to these original values if needed
• Enables easy, universal updates and changes to our new labels: The easy-to-read master list of our new and old labels side by side provides us one central place to make changes that will flow through the rest of our analysis

No more manual rework that takes a long time to complete and is arduous to update.  A fast, easy-to-follow approach so you can move on to the more important task of analyzing your new data.

In the following guide, we have laid out a clear, step-by-step process to effectively and efficiently create a key and use it in your analysis.

For a quick snapshot of the process, see the below video clip that walks through the process in a summarized manner.

Set up a flexible, easy to use key in 6 quick steps:

• Setup the key by creating an “old name” and “new name” column
• In the “old name” column, insert a unique list of your existing data using the Unique() formula
• Sort this unique list alphabetically, using the Sort() formula
• Copy and paste the list as values so that the list is no longer dynamic
• If relevant (if you are using similar naming convention for your new name column), copy and paste the completed list in the “old name” column into the “new name” column
• Relabel the relevant line items in the “new name” column, copying and pasting existing values as much as possible to avoid the risk of typos

Once we have created the key, we now need to incorporate the new, relabeled values into our existing dataset. In order to do so, we leverage the Xlookup( ) formula.

The Xlookup( ) formula uses an existing piece of information to lookup a related piece of information in a separate dataset.  In this case, we want to use our existing value in our base dataset to lookup our new, relabeled value in our key, and incorporate that into our base dataset.  Watch the video for a full walk-through completing the Xlookup formula and incorporating our new, relabeled values into our original data.

Once we have incorporated our new, relabeled values into our original dataset, we can now run analysis on these new values by referencing this new column in any summary formulas.  If we need to change any labels in the future, we can simply update our key and the changes will flow through our final analysis automatically.

Watch the video below as we demonstrate how to complete a summary table using our newly labeled teams in our example:

Now that we have incorporated our key into our base data and our analysis, updating the key with new categories/labels is as simple as just updated the “New” column in the key. Once you update the “New” column in the key, the updated labels will flow through your original data (since you used XLOOKUP to incorporate the key into your data) as well as your summary analysis (since you used the UNIQUE formula). In the update, if you add an additional new label to the list, you will need to ensure you have enough space in your summary table to allow for the additional values. See the below video clip for an overview of how to update the key and the related considerations.