How To Capitalize in Excel
Sep 28, 2024
Do you need to quickly capitalize text in Excel?
Unlike Microsoft Word, Excel doesn’t offer a direct button for capitalization. However, there are simple ways to get it done. You can capitalize names, titles, or any other data. You will know how to capitalize in Excel like a pro at the end of this article.
Capitalizing in Excel Using the UPPER Function
You can use the UPPER function to quickly capitalize all text in a column. It won't affect the original data. Here's how to convert all text to upper case letters in Microsoft Excel:
1. Select a new column: Choose an empty column next to the data. For example, select column D to hold the capitalized version of the names.
2. Enter the UPPER function: In cell D2, type the following formula: =UPPER(A2) and click Enter. Ensure the appropriate cell reference is in the brackets. This converts the name "john doe" from cell A2 to "JOHN DOE."
3. Apply the formula to the entire column: Drag the small square at the bottom-right corner of cell D2 downwards to apply the formula to the other rows. This will capitalize the entire list of names in column A.
4. Copy and paste as values: Copy the cells with capitalized text. Right-click on the original column (in this case, column A, where the names are). Select Paste Special. Choose Values from the options and press OK. This replaces the original text with the capitalized version without keeping the formula.
5. Delete the extra column: After pasting, you can safely delete the column with the formulas (e.g., column D) if it's no longer needed. You can easily undo any mistake you make.
Capitalizing in Excel Using the PROPER Function
This function capitalizes only the first letter of each word in a cell, i.e., proper case. Hence, it is great for names and titles.
1. Select the target cell: Click on an empty cell next to the first name (e.g., cell D2).
2. Enter the PROPER formula: Enter this formula into the target cell: =PROPER(A2) and press Enter. Excel will change "john doe" to "John Doe," where the first letter of each word is capitalized.
3. Apply to the rest of the column: Click on the bottom-right corner of D2 (the small square) and drag it down to apply the formula to the rest of the rows. Each name in column A will now be capitalized in column D.
4. Copy the results: After applying the PROPER function, select the cells containing the newly capitalized text (e.g., column D or E). Right-click on the selected cells and choose "Copy," or press Ctrl + C on your keyboard.
5. Paste values into the original column: Select the original column you want to replace (e.g., column A for Names). Right-click and choose "Paste Special." In the Paste Special dialog box, choose "Values" and click "OK."
6. Delete the extra column: After replacing the data, you can delete the temporary column you used for the capitalized text.
Capitalizing in Excel Using VBA Macro
When working on large data sets, you can create a macro using VBA to convert entire columns to uppercase. We have explained the steps below:
1. Open the VBA editor: Press Alt + F11 to open the VBA editor in Excel.
2. Insert a new module: In the editor, go to Insert > Module. This will create a new module where you can write your macro.
3. Write the macro: Copy and paste the following code into the module:
This macro will convert text in any selected cells to only upper case letters.
4. Run the macro: Close the VBA editor and return to your Excel sheet. Select the range of cells you want to capitalize. For example, select the cells in column A that contain the names (A2). Press Alt + F8 to open the macro window, select CapitalizeText, and click Run.
Convert Text Using an All Caps Font
Some fonts in Excel automatically convert texts to upper case letters. While the underlying text remains unchanged, these fonts give the visual effect of uppercase letters.
1. Select the range: Highlight the cells containing the text you want to capitalize in your Excel table (e.g., column A for names).
2. Open the Format Cells dialog box: Right-click the selected range, choose Format Cells, or press Ctrl + 1 on your keyboard.
2. Go to the Font tab: In the dialog box, go to the Font tab.
3. Choose an all-caps font style: Scroll through the list of fonts, select your desired all-caps font, and click OK. Examples include COPPERPLATE GOTHIC BOLD, CASTELLAR, and CHARLEMAGNE STD. You can download more free fonts here free.
4. Apply the Formatting: Click OK to apply the changes.
Final Thoughts on "How To Capitalize in Excel"
The best way to capitalize in Excel depends on your needs. For a quick solution, using the UPPER function is straightforward and efficient for converting text to all caps. However, for more control or bulk tasks, a VBA macro is more versatile and automates the process across large datasets.
You can visit our homepage for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets Templates!
FAQ on "How To Capitalize in Excel"
1. Can I capitalize only the first letter of each word in Excel?
Yes, use the PROPER function with =PROPER(A1). This will capitalize the first letter of each word in the selected cell.
2. Can I convert text to uppercase without formulas?
Yes, you can use Excel's Flash Fill by typing the uppercase version in the next column and pressing Enter. Excel will suggest filling the rest automatically.
3. Is there a shortcut to capitalize all selected text in Excel?
Yes, by using a VBA Macro, you can automate capitalization across large ranges quickly. This method is ideal for repetitive tasks.
Related Articles
How to Print Gridlines in Excel
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
