If you work with a lot of text-based data in your Excel spreadsheets, formatting it can easily become a tedious chore. Unlike Microsoft Word, Excel does not include a Change Case tool for editing text with special upper, lower or other types of capitalizations. So, what’s the solution to a problem like this? You could copy the column into Microsoft Word, use the Change case tools then copy and paste into Excel again.
That’s a bit cumbersome, though, and prone to copy-paste errors for large sets of data. A more Excel-centric option is to use the UPPER, LOWER, and PROPER functions. To do this, you can create a temporary column to format your text and then replace your old values with your properly capitalized text. Once you get the hang of it, the technique is quite simple and easy. Let’s take a look.
How to Use Change Case in Excel 2016
As noted above, there is no Change Case option in Excel 2016, like there is in Word 2016. But you can essentially roll your own with the steps below.
In this example, the employee name column below contains names using ALL CAPS, but I would prefer if they used PROPER capitalization.
First, you will need to make a temporary modification to your spreadsheet by inserting a new column to the right of the column containing the text you wish to modify. After inserting the column, activate the first blank cell next to the column containing text. Proceed to enter the formula to change the case; specifically, for proper, this would be =PROPER(A3). If you want to use a different type of capitalization, =UPPER would capitalize all letters, while =LOWER would convert to all lowercase letters. After entering the formula, hit Enter.
You will notice, the text from the first cell is copied into the column we created earlier using proper casing.
All you need to do now is use the autofill function to repeat the process for the other names.
Now that the names are using the proper casing, what will you do with the duplicates? Easy: Select the names in the column you just auto-filled, then click Copy or press CTRL + C. Right-click in the beginning column; in my case, it’s A3. Click the Paste Special menu for Values.
Proceed to delete the temporary column used to facilitate the formatting. There you have it—your names are now using the proper casing.
With this simple tip, you’ve just saved yourself minutes, maybe hours of menial data re-entry labor. Interested in more Excel tips? If you are new to Microsoft Excel, be sure to check out our tutorial for getting started with this popular spreadsheet app.