How to Uncapitalize a Large Data Field in Excel

Person holding smartphone.jpg

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.

1 Find a block

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.

2 Replacing A1

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:

3 \=PROPER

\=PROPER(A1)

4 This formula changes text to proper case

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:

5 \=LOWER

\=LOWER(A1)

6 Enter and then select the cell

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.

7 Press

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.

8 Delete the block

Delete the block of cells you used for the conversion.

  • Information in this article applies to Microsoft Excel 2013. It may vary slightly or significantly with other versions.

Laurel Storm has been writing since 2001, and helping people with technology for far longer than that. Some of her articles have been published in "Messaggero dei Ragazzi", an Italian magazine for teenagers. She holds a Master of Arts in writing for television and new media from the University of Turin.

×