It is so easy to capitalise all the letters in a sentence or paragraph in Word (and vice versa), but it isn’t exactly straightforward to do the same in Excel. Yes folks, you can actually change the capitalisation of the words in Excel too! It just requires extra steps to achieve it as compared to Word.
Picture this data entry scenario. You are compiling a customer contact list with a few columns and over 1000 rows of entries. Something like this:
As you can see, the entries do not have consistent capitalisation, and you want to capitalise all the letters as a form of standardisation. Let’s start with the column “CUSTOMER NAME”.
1. Insert a new temporary column next to column C “CUSTOMER NAME”. Note that the new column D has a default table header name “Column1”.
2. Click the empty cell of the first data entry line (i.e. one line below the table header). In this example, it will be cell D3.
3. In cell D3, type the formula =upper([@[CUSTOMER NAME]]) and then hit the Enter button.
Alternatively, you can begin typing =upper( in cell D3 and then click cell C3. This will automatically insert the table header name “CUSTOMER NAME” into the formula. Insert the close bracket ) to complete the formula.
4. Immediately after you have pressed the Enter button, all the empty cells in column D will be automatically filled with text in uppercase.
5. Press the Ctrl and C buttons to copy all values in column D (i.e. starting from cell D3). Alternatively, select all cells with values in column D, right-click any of the selected cell and select Copy from the pop-up menu.
6. Right-click cell C3 and select Paste Special from the pop-up menu. Then, select Paste Values (first icon) from the submenu.
7. Now that all the text in column C are capitalised, you no longer need the temporary column D. Delete the entire column D.
8. Repeat Step 1 to 7 to capitalise the text in other columns.
But Why Can’t I Just Delete Column C Instead of D? That Way, I Don’t Have to Copy the Values from D and Paste It Into C.
Well, let’s go back to Step 3 and 4. When you type the formula in cell D3 and hit the Enter button, two things happened. First (obviously), all the text in column D are capitalised. Second (that isn’t obvious), the cells in column D have actually turned into formula! If you select any of the cells (with values of course) in column D, for example, D6, the formula bar shows you =upper([@[CUSTOMER NAME]]) instead of “MUTUSAMY INDUSTRY LTD”.
The formula that I’ve used here is called structured reference. You can read further to understand how it works and why you should be using this type of reference when handling data in a table. So, when you delete the original column C, what happens next is, the formula in column D (which is now, the new column C) will throw you an error message like this:
This #REF! error occurs when a formula refers to cells that have been deleted (or replaced with other data). So, in order to avoid such error, you need to copy and paste as values to get rid of the formulas (hence, Step 5 and 6). Savvy?
You can also turn all the text into lowercase or proper case! Just repeat the same steps but replace the =UPPER() formula with:
=LOWER() : if you want to convert all text into lowercase
=PROPER() : if you want to convert all text into proper case
This is why i hate microsoft. Such a ridiculously cumbersome process just to change case of text.
Well, let’s hope they can ‘fix’ this in their next release! :)