If you have ever needed to re-enter a large amount of data in a spreadsheet then you know how tedious it can be.
Thie re-entry is also prone to error, and it can be frustrating to perform a lot of work simply because you need to switch some formatting.
Fortunately, you can use the =UPPER(XX) formula to convert existing text to uppercase in Excel 2013. You can then copy and paste the values from the formula to replace the existing lowercase or sentence case data.
Uppercase typing may have negative connotations when used conversationally in text messages or emails, but there are certain types of databases or situations where your data needs to be in all capital letters.
So if you have a spreadsheet of data that is all lowercase, or is a mixture of lowercase and uppercase, you may be looking for a way to make all of the text uppercase in Excel 2013.
Fortunately, you can accomplish this with the help of a formula that can automatically make all of your text uppercase.
Our guide below will show you how to use this formula, then replace your original lowercase data with the uppercase data that you need.
How to Make All Text Uppercase in Excel 2013
- Open the spreadsheet.
- Right-click on the column, then choose Insert.
- Type =UPPER(XX) into the first blank cell, but replace “XX” wither the cell location.
- Right-click on the uppercase cell and choose Copy.
- Paste the formula to the rest of the cells.
- Copy all of the uppercase cells.
- Right-click on the desired location, then choose Paste Values.
- Delete any remaining unwanted cells as needed.
Our guide continues below with more information on making all of your letters capital in Microsoft Excel, including pictures of these steps.
How to Convert to All Capital Letters in Excel 2013 (Guide with Pictures)
The steps in this guide are going to assume that you currently have a spreadsheet with some cells that are lowercase, but you would prefer to have them uppercase.
We are going to accomplish this conversion with a simple formula. The results of that formula can then be copied and pasted into the original cells, allowing you to replace the original lowercase text with the new and improved uppercase text.
Step 1: Open your spreadsheet containing the cells for which you would like to make all of the text uppercase.
Step 2: Right-click on the column heading to the right of the column with the lowercase text, then click the Insert option.
This is going to add a new, empty column directly to the right of the column we are modifying.
Step 2: Click inside the cell to the right of the first cell with lowercase text, then type =UPPER(XX), but replace the XX with the cell location containing the lowercase text. In this example, that is cell B2. Press Enter on your keyboard after completing the formula.
Step 3: Right-click on the cell containing the uppercase text, then click the Copy option.
Step 4: Select the cells into which you wish to convert the original lowercase text to uppercase text, then right-click one of the selected cells and select the Paste option.
Step 5: Select all of the cells that now contain uppercase text, then right-click on one of them and select the Copy option.
Step 6: Select the original cells that still contain the lowercase text.
Step 7: Right-click on the selected cells, then click the Paste option with the numbers on it.
This is going to allow you to paste the values from the formulas rather than the formulas themselves.
Step 8: Delete the column that contains the formulas by right-clicking the column heading, then clicking the Delete option.
Now that you know how to capitalize all letters in Excel you will be able to make that conversion whenever you have data in your cell that you need to be uppercase instead of lowercase.
You can continue below for additional discussion on changing the case of your cell data in Microsoft Excel.
More Information on How to Make Text All Caps in Excel 2013
Note that the data that is being displayed via this formula is still formula data before you go through the process of copying and pasting it as values. This means that it interacts with cell adjustments the same way as a SUM or other formula type would. For example, if you were to add or delete rows while the cells still contained formulas, then the displayed results would adjust accordingly.
If you copy and paste the uppercase text from Excel to another program, like Microsoft Word, then it will be pasted as values automatically. In some cases, it can be easier to use the Excel capitalize all formula rather than trying to perform the same task in a Word document.
You can use similar Excel functions that we used in this guide on how to make font all caps in Excel if you want to convert text to something other than upper case, such as proper case. In that situation you would use the =PROPER formula instead. This proper function can come in handy when you are working with a lot of lowercase letters, for example.
If you aren’t only interested in using a cell reference for how to capitalize in Excel for a small amount of Excel data, but would instead like to make the change for an entire column, then you can enter the formula into the formula bar for the first cell in the column, then click on the handle at the bottom-right corner of that cell and drag it down to fill the rest of the column.
Do you have a lot of CSV files that you need to combine into one file, but you are looking for a better way to do it than cutting and pasting? Learn how to merge CSV files with the Windows command prompt and make it much easier to combine your data into one spreadsheet.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office and many other popular applications and devices.