Excel 2013 relies on functions to change capitalization. The LOWER, UPPER and PROPER functions change text in referenced cells to lowercase, uppercase or proper case. This method requires a formula in another cell, but you can copy the results to the original location to avoid with duplicate data.
1. Right-click the column letter and click Insert to create a new column in which to change capitalization. Alternative, simply choose a blank column to enter the formulas. You can also click a row number to add a new row, if the existing values are listed horizontally as headers might be.
2. Select the cell corresponding to the first entry you want to change and enter the PROPER, LOWER or UPPER function, as follows:
=PROPER(reference) to capitalize just the first letter of each word;
=LOWER(reference) to make all text lowercase; or
=UPPER(reference) to make all text uppercase.
In this example, the formula changes the text in cell B2 to proper case, such that “david smith” appears in cell A2 as “David Smith.” Don’t worry that you can’t see the entire text in cell A2; you’ll remove it soon enough anyway.
3. Click the lower right Fill Handle and drag down to the cell corresponding to the last entry you want changed. When you release your mouse button, the adjusted formula is copied to each cell in the selected range.
The mouse button has not yet been released in this screenshot so you can see the Fill Handle. However, when the button is released, the proper case names appear.
4. Select the cells containing the new values, right-click anywhere in the selection and click Copy to copy the cells.
In this example, cells A2 through A6 are selected for copying.
5. Right-click the first value of the original series (the range in need of changing) and click the Paste Values option, indicated by a clipboard with “123” at the bottom.
In this example, cell B2 was selected. After choosing Paste Values, all the text values in B2 through B6 are replaced with the corrected values from cells A2 through A6.
6. Right-click the column letter of the newly created column and click Delete to remove the column containing the capitalization formulas. Doing so will not change the copied data.
Right-clicking column A and choosing Delete removes the added column, leaving you with the original data with capitalization changed:
