If you work with text data in your Excel Worksheets, formatting can be an odious chore. Unlike Microsoft Word, there is no handy dandy tool or shortcut for changing case in Excel.
So then, are you just stuck with doing it manually? Well, you could copy your column into Word and use the Change Case tool and then paste in back into Excel.
But, of course that is tedious at best, not to mention the fact that it greatly widens the margin for errors when copying and pasting.
A better option, I think, is to use the UPPER, LOWER and PROPER functions in Excel, which will allow you to create a temporary column to format your text and then replay your old values with your properly formatted text. It really is very easy to do.
Follow the steps below to learn how:
- Ok, if you do not have an Excel worksheet with text in it, create one to work with for the purpose of learning how to do this.
- Just key in a couple of names in a column (i.e., class names, student name, employee names) and make sure you key them in as ALL CAPS.
- Now you will need to make a temporary modification to your worksheet by inserting a new column to the right of the column containing the text you would like to modify.
- Once you have created the new column, activate the first blank cell next to your column with text.
- You are now going to enter the formula to change the case:
- Proper: =PROPER(A2)
- Upper: =UPPER(A2)
- Lower: =LOWER(A2)
- Once you have entered your formula, click Enter.
- Note that the text from your first cell is copied into the column you created above.
- Now all that is left to do is use the Autofill function to repeat the process for all the other text.
So now, what do you do with the duplicates?
- Select the names in the column you just AutoFilled.
- Click Copy or click CTRL + C
- Right-click in the beginning column; (A1).
- Click the Paste special menu and select Values.
Now you can delete your temporary column.
You have just learned how to save lots of time and keystrokes!