Excel Tip of the Day - Changing Text to All Caps

Because I love you all, I thought I'd share a wonderful little excel trick with you that I recently shared with some coworkers.

Let's say you have a list of something and you have already typed a bunch of it, but then looking it over, you wish you could go back and make it all in caps.

Well sometimes wishes come true! Here's a step by step guide to fixing this without having to retype everything - because Excel does magic all the time.

Here's my original list:

Pick a column next to your text and type in a cell: =Upper(
Once you start typing it, it will try to auto-populate the formula - it's part of the magic trick. Don't be alarmed. It wants to help you find the right formula. Because it's nice.

Typing an equals sign tells Excel you are going to use a formula. Your formula here is Upper. Then you tell it what you want to be in upper case by putting something in the ( ).  You can just type one of the parenthesis and it will add the ending one in this formula. Once you type the formula: =Upper(  -click on the cell you want to make uppercase. It will look like this:

 Now your formula disappears in that cell and it will create your text in that new cell, in upper case, just like you wanted! Thank you Excel!! 

But you're not done yet!!
What about the rest of your list? You don't want to have to type your newly-learned formula into each cell, right? Of course not.

You can very easily just drag your formula down your entire list.
Click on the cell with the formula. In the bottom right of the selected cell, you'll see a little black square. That square is your friend.

Click and hold, then drag it down to the cells below it, like this:

If you have numbers in your column, listed underneath your text, it's not a problem - it will just copy your number over.

So now you have your data in your new column. Perfect...but it's not in the right column. If you just delete your first row, the formula will break. This is a quick and easy way to fix everything.

Select your new cells or the entire column if you just copied the formula down the whole column.
Select Copy.
Now we're going to paste the values of those cells. You can't just do a normal Paste, so click on the paste option and choose Paste Special and choose Paste Values (or just paste values depending on your version), like this:

You can paste it right over the formulas or you can paste it in the original cells. But paste it you must.

This might sound like a lot of steps, but I assure you it's not.
And if you have a long list of data that you want to be upper case, this is really your easiest way to do it (without learning how to write code and such).

I showed this to my coworkers and one of them immediately said: this is too hard. The other one said: I'll never remember that.

This was discouraging, so it's up to you-my beloved readers, to try this, love this, and report back that you appreciate the magic of Excel. Otherwise, I'll go to sleep sad. You don't want that on your shoulders, do you? Thanks!


  1. Brandy@YDK said...:

    GREAT tutorial. i love Excel. I guess I have to since I use it a million times a day.

  1. Mmmm, I'll never remember this. lol

    Just kidding. :)

  1. Blogger101L said...:

    This is very helpful to know! Many times I wished i could do that! Thanks!


Related Posts with Thumbnails