How to Uncapitalize a Large Data Field in Excel

Fixing the capitalization of a large spreadsheet probably ranks absolutely nowhere on the list of things you like doing, but sometimes tasks like this can't be avoided. While you may be unable to saddle some other unfortunate soul with the job, though, that doesn't mean you can't make things easier for yourself. As long as the data in the spreadsheet needs to be all in lowercase or in proper case, you can use one of Excel's built-in formulas to do the work in five minutes rather than five hours.

Find a block of empty cells in your spreadsheet that's as big as the block of data you need to fix. You'll only need the space temporarily, so you don't have to worry about design or positioning.

Type the following formula in the first cell in the free block, replacing "A1" with the coordinates of the first cell in the block of data you need to fix:

\=PROPER(A1)

This formula changes text to proper case, with each word capitalized. If you want the text to be completely lowercase, use the following formula instead:

\=LOWER(A1)

Press "Enter" and then select the cell again. Click the small square in the bottom right corner of the cell and drag down to copy the formula to as many rows as you need, converting all the text in that column. If the block of text you need to fix takes up more than one column, click the small square again and drag it to the right until all your text is converted.

Press "Ctrl-C" to copy the converted text. Scroll back to the block of text you needed to fix, right-click the first cell in it, hover over "Paste Special" and select "Values." This pastes the converted text over the original, replacing it.

Delete the block of cells you used for the conversion.