how to change from all caps to lowercase in excel

Often when working with text data such equally names or address, yous will go the information in uppercase, and you may demand to catechumen it into lowercase or propercase.

With my piece of work, I often get all capital letter data from databases or website downloads.

Thank you to Excel, converting upper-case letter to lowercase (or vice versa) is really easy. All yous need to know is the right formula or functionality.

In this tutorial, I will testify you three dissimilar ways how to alter uppercase to lowercase in Excel.

Change Upper-case letter to Lowercase Using the LOWER function

Let'due south say that y'all accept a list of names in column A in upper-case letter, and want to convert them to lowercase in column B.

In cell B2, enter the below formula. The LOWER function takes a text (or a prison cell reference) and converts all characters to lowercase.

=LOWER(A2)

Position a cursor in the correct lower corner of prison cell B2, until the black cross appears (it's called the Make full Handle). Drag information technology until the concluding populated row (B8).

Apply lower function to the entire column

Equally a issue, you become all the values from column A, changed to lowercase in column B.

Names converted from Upper case to lower case

Note that every bit of at present, the lowercase result is linked to the majuscule names in column A. So if you brand whatsoever changes in column A, it would instantly be updated and shown in lowercase in cavalcade B.

This too means that you can not delete Cavalcade A.

In case you lot but desire to only go on the lowercase value and delete the uppercase data, first convert the formula result in cavalcade B into static values.

Just like we converted upper example text to lowercase, y'all tin also practice the reverse. You can use the UPPER formula to do that.

Change Upper-case letter to Proper Case Using the PROPER Function

Like to changing text to lowercase, yous can also change a text to a proper instance. This ways that the changed text volition accept every starting time letter of the text capitalized.

So for names, the outset character of the get-go and last proper noun would be in upper instance and rest would exist in lower example.

Let's say that you take the same list of names in column A.

When you changed information technology to lowercase, you probably wanted to have every kickoff letter capitalized, as you lot're dealing with names.

In cell B2, enter the formula:

=PROPER(A2)

The PROPER office takes a text and converts the first letter of every word in a text to capital letter letter of the alphabet.

Position a cursor in the right lower corner of cell B2, until the black cantankerous appears, and drag it until the last populated row (B8).

Apply the Proper formula to the entire column

Now, you have the listing of names converted from capital to proper instance.

Again, if you want to delete the names in majuscule and simply keep the ones in lowercase, you first need to convert the formulas to values in column B.

Catechumen Uppercase to Lowercase Using the Flash Fill up

While the formula method is quite easy and straightforward, there is another equally piece of cake and user-friendly way to change the instance of text in Excel.

And that is by using the Flash Fill

Wink Fill is relatively new functionality in Excel that recognizes the pattern and fills cells automatically for yous.

Suppose you take the following list of names in column A, and you want to catechumen them to lowercase.

Let's see how to do this using Wink Fill:

In cell B2, enter the value from A2, but in lowercase ("michael james"). The idea here is to enter the expected result so that Excel tin deduce what we are trying to practise

enter the expected result in the first cell

Hit the Enter cardinal to go to prison cell B3
In cell B3, start entering the expected result value from A3, also in lowercase. While you're typing, Excel recognizes the pattern based on these two cells and recommends populating the rest of the cells (until B8) automatically.

If you call back Excel has correctly identified the pattern, just hit the Enter primal and Excel will automatically fill the cells with the lowercase name. Later on that, yous can encounter that the Flash Fill up icon appears on the right, and you need to click 'Have suggestions' to confirm the values.

Equally a final outcome, you lot get all names from column B converted to lowercase.

I absolutely love this method every bit information technology's fast, and the resulting values are static. This ways that I don't have to convert these to values (as I had to when I was using the formula).

Note that Flash Fill up can work with many different types of data every bit long equally information technology can identify the blueprint. In simple stuff such equally changing the example of the text, Wink Fill works flawlessly.

I recommend y'all nonetheless double-check the result of Flash Fill. In some rare cases, it may give a incorrect result in instance the pattern it identifies is wrong or simply partially correct.

And just the way we used Wink Fill to catechumen Capital to Lowercase, yous can too practice other text transformations equally well, such equally converting data to capital or proper case.

Using the Flash Fill up in the Ribbon

I some cases, it may happen that yous don't encounter the Flash Fill suggestions while you are typing (where it fill the entire range with expected issue in light gray colour). If that happens, yous can use the beneath method.

Suppose you have the below dataset in column A that you lot want to convert to lowercase

In cell B2, enter the value from A2, but in lowercase ("michael james")

In the Ribbon, go to the Data tab, and choose Flash Fill in the Information Tools grouping.

Now you get all values suggestions, and you need to click on the Wink Make full icon to take values and populate cells.

The final output is the same as in the previous section – you converted all names from column A to lowercase in column B.

In case Excel is not able to identify the pattern with a single input in cell B2, enter the expected result in another cell (B3) and and then do the to a higher place steps.

Convert Uppercase to Lowercase Using the Ability Query

Another option for converting to lowercase is to utilize the Ability Query. This tool is very useful for yous if you demand to transform your information.

Annotation: To use Power Query, you lot should convert your data into an Excel Table. You lot can practise that past selecting whatever jail cell in the data and using the keyboard shortcut Control + T (or Control + T if using Mac)

Let's get-go from the following data list in column A.

Select a range that you want to convert to lowercase (A2:A8).

In the Ribbon, become to the Data tab, and choose From Table/Range in the Get & Transform Data group. This option opens the Power Query editor.

In the Create Tabular array dialog box, uncheck the 'My tabular array has headers' option, as you selected the data without header and click OK.

Now the Power Query editor is open. As you tin can meet, our information range is imported as column 1. Hither, you lot can come across different tabs and options for transforming data.

In the Ribbon, go to the Add Column tab, click on the Format icon, and choose lowercase.

This choice creates a new column, and populates all rows with values from the original column in lowercase.

Finally, in the Ribbon, get to the Domicile tab, and cull Shut & Load in the Close group. This will close the power query editor and load the table in the new sail in your Excel file.

Equally you can see in the flick beneath, now yous have the new table in the new sheet, with values converted in lowercase.

Resulting table with lowercase transformed data

While I accept shown yous how to utilise Ability Query to alter the names to lower instance and keep both the original data and the lowercase information, you tin can also choose the delete the upper instance data and only keep the lowercase i.

1 astonishing thing about Ability Query is that in case your original data changes (more names are added or modified), y'all don't demand to follow the entire process again.

All y'all demand to do is go to the resulting tabular array (the 1 with lowercase data), right-click and then click on Refresh. This will automatically check the original data and update the resulting data.

And again, the way we accept converted capital information to lowercase, you can also do the aforementioned to convert information to uppercase or proper case.

So these are 3 simple means you can employ to convert Upper-case letter to Lowercase or Proper Instance.

I hope you found this tutorial useful.

Other Excel tutorials you may also find useful:

How to Copy Formatting In Excel
How to Add Text to the Start or End of all Cells in Excel
How to Remove Text later on a Specific Character in Excel
How to Count How Many Times a Word Appears in Excel (Easy Formulas)
How to Reverse a Text Cord in Excel (Using Formula & VBA)
How to Merge First and Final Proper name in Excel
How to Modify All Caps to Lowercase Except the First Letter of the alphabet in Excel?