UPPER lower Proper Case (3 ways)
The objective of this tutorial is to demonstrate three different methods to change the case of text in Microsoft Excel: Flash Fill, formulas, and Power Query. By the end of this tutorial, you will be confident in using these techniques to convert text to upper case, lower case, and proper case.
Let’s walk through the concepts one exercise at a time.
Exercise 1: Using Flash Fill
Flash Fill is a convenient tool in Excel that detects a pattern and fills it down. You give it a sample of what you want, and Excel attempts to extend the pattern all the way down the list. While we can use it to accomplish many different things, in this example, we’ll use it to quickly change the case of text.
Consider the following:
The list contains cities, but they are all in a different format: some are all lower case, some all upper case, and some are proper case (first letter of each word is capitalized). If we wanted to make them consistent, like all proper case, we can give Excel and example by typing in the desired format for the first one in the list:
Next, we just click the Data > Flash Fill command (or use the Ctrl E shortcut):
Excel attempts to detect the pattern based on your sample, and fill the pattern down:
We can do the same thing to convert a state abbreviation column to upper case. Enter the sample:
And Flash Fill it down:
Now, what happens if the original is the same as the sample, such as in this case:
Well, when we Flash Fill it down, it doesn’t understand the pattern and simply copies the original format:
But, the good news is that we can provide additional samples as needed. So, we add another sample:
And then Flash Fill it down, and bam:
Ok, so that was one option. Flash Fill provides static values, and doesn’t automatically update the results if the data changes. If we want a more dynamic solution, we can use formulas instead. Let’s try that next.
Exercise 2: Using Formulas
Formulas in Excel provide a more dynamic solution for changing the case of text. Let’s explore this method. There are three functions that will help here. To convert the text in A1 to proper case, we would use the PROPER function like this:
We fill the formula down and bam:
As you may have guessed, we can use the UPPER function to convert the text in A1 to upper case:
And we have a LOWER function to convert text to lower case:
Formulas operate well when the data is already in Excel … but … what if we are importing the data from an external source and want it cleaned up on its way into Excel? Well, for that we can use Power Query.
Exercise 3: Using Power Query
Power Query is a powerful tool that provides extensive data transformation capabilities. Here’s how you can use it to change the case.
First, use the Data > Get Data command to get data from the source. After you have identified the data, you’ll see a preview in the Power Query editor:
Right click the City column and select Transform > Capitalize Each Word. Bam:
Right click the State column and select Transform > UPPERCASE:
Right-click the Email column and select Transform > lowercase:
Next, Home > Close & Load To and then opt to save as a Table in a new or existing worksheet. Bam:
Now, the great news is that anytime your original data changes, right-click on the results table and click “Refresh” to update the transformed data.
I hope the three methods presented to change the case of text in Microsoft Excel (Flash Fill, formulas, and Power Query) will be helpful. Use Flash Fill when dealing with simple patterns, formulas for dynamic changes, and Power Query for more complex transformations and external data. By mastering these techniques, you can efficiently convert text to upper case, lower case, or proper case, saving time and ensuring accuracy in your Excel projects.
If you have any improvements, alternatives, questions, or suggestions … please post a comment!
Q: Can Flash Fill work with complex patterns?
A: Flash Fill works best with consistent patterns.
Q: What other data transformations can Power Query perform?
A: Power Query offers a wide range of transformations, including but not limited to data filtering, merging, splitting, and cleaning. It’s worth exploring the capabilities of Power Query to simplify your data manipulation tasks.
Q: Is Flash Fill capable of handling irregular patterns in data?
A: Flash Fill is designed to work best with consistent patterns. If the data patterns are irregular or inconsistent, Flash Fill may not produce the desired results.
Q: Can I use formulas to change the case of text in a specific range of cells instead of the entire column?
A: Yes, formulas can be applied to a specific range of cells by adjusting the cell references in the formula accordingly.
Q: How can I apply Power Query transformations to multiple columns simultaneously?
A: In the Power Query editor, hold down the “Ctrl” key while selecting multiple columns. Then choose the desired transformation option to apply the changes to all selected columns.
Q: Can Power Query automatically update the transformed data when the original data changes?
A: Yes, Power Query provides a “Refresh” option that allows you to update the transformed data whenever changes are made to the original data source.
Q: Are there any limitations to the amount of data that can be processed using Power Query?
A: The processing ability of Power Query depends on the hardware resources available on your computer. However, it is generally capable of handling large datasets efficiently.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.