Effectively Relabeling Data
We frequently encounter data that requires relabeling. Creating a key is a systematic method allowing for fast, accurate relabeling.
We frequently encounter data that requires relabeling for various reasons -- including cleaning up messy data (ex. customer names) and recategorizing (ex. states into regions, teams into divisions, etc). To avoid the tedious and time-consuming task of manual rework, we have outlined a systematic and efficient method that is easy to update and maintain -- allowing for fast, effective relabeling that saves valuable time.
Can't I just relabel using find & replace?
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.
Overview of how to create & use a key to relabel 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.
Relabeling data quick version (YouTube)
Part 1: Creating the key
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
Part 2: Incorporating the new relabeled values into your data
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.
Incorporating new labels into base data (YouTube)
Part 3: Using our new relabeled values in analysis
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.
Using the relabeled data in analysis (YouTube)
Part 4: Updating our key with new labels
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.