Categories
Self-organization

How to quickly and easily change case in excel 2013 using a function

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

You may find you need to change multiple cells from one case to another in Excel. Maybe you imported data that came in all upper case, or maybe you convert headings on columns to upper case. Changing case is easy using some special functions.

There are three functions that allow you to change the case of text in multiple columns easily:

  • = Upper(B1) – converts text to all upper case
  • = Lower(B1) – converts text to all lower case
  • = Proper(B1) – converts text to proper case, or title case (the first letter of each word is capitalized)

For our example, we will change two columns of first and last names in a sample address list. First, we want to insert a new column after the Last name column. To do this, highlight the column after which you want to insert the blank column by clicking on the lettered header, right-click on the header, and select Insert from the popup menu.

The new column is formatted the same way as the original column. In our example, we entered the title of the column in the gray highlighted cell at the top of the column. We will be deleting the original column once we’ve converted the case.

In the first cell of the new column, enter the desired case function, with the cell reference in the parentheses, for the text you want to convert. In our example, we want to convert each name to title case, so we entered the Proper() function in the first cell in the column (below the heading row) with A2 as the cell reference.

NOTE: Remember to preface your function with the equals sign.

Now, we need to propagate the function to the rest of the cells in the column. To do this, select the cell containing the case function and click Copy in the Clipboard section of the Home tab or press Ctrl + C.

Highlight the remaining cells in the column and click Paste or press Ctrl + V.

TIP: You can also quickly copy the contents of a cell into the remaining cells in the column by double-clicking the box on the lower, right corner of the cell.

Each of the cells in the new column look like they contain the names in a different case. However, each cell still contains the Proper() function referring to another cell. Because we are planning to delete the original column, we need to replace the function with the actual name to which it evaluates. To do this, highlight all the cells in the column containing the function and copy them.

To paste the values back into the same cells, click the down arrow on the Paste button in the Clipboard section of the Home tab. Then, click Values in the Paste Values section.

NOTE: This is the same procedure we discussed in a previous article about converting a numerical formula to a static value.

Now, all the cells in the column should contain text, not functions.

To delete the original column, select the entire column by clicking the lettered header, right-click on the header, and select Delete from the popup menu.

To convert the first names to title case, we followed the same procedure.

Lori Kaufman is a technology expert with 25 years of experience. She’s been a senior technical writer, worked as a programmer, and has even run her own multi-location business. Read more.

You may find you need to change multiple cells from one case to another in Excel. Maybe you imported data that came in all upper case, or maybe you convert headings on columns to upper case. Changing case is easy using some special functions.

There are three functions that allow you to change the case of text in multiple columns easily:

  • = Upper(B1) – converts text to all upper case
  • = Lower(B1) – converts text to all lower case
  • = Proper(B1) – converts text to proper case, or title case (the first letter of each word is capitalized)

For our example, we will change two columns of first and last names in a sample address list. First, we want to insert a new column after the Last name column. To do this, highlight the column after which you want to insert the blank column by clicking on the lettered header, right-click on the header, and select Insert from the popup menu.

The new column is formatted the same way as the original column. In our example, we entered the title of the column in the gray highlighted cell at the top of the column. We will be deleting the original column once we’ve converted the case.

In the first cell of the new column, enter the desired case function, with the cell reference in the parentheses, for the text you want to convert. In our example, we want to convert each name to title case, so we entered the Proper() function in the first cell in the column (below the heading row) with A2 as the cell reference.

NOTE: Remember to preface your function with the equals sign.

Now, we need to propagate the function to the rest of the cells in the column. To do this, select the cell containing the case function and click Copy in the Clipboard section of the Home tab or press Ctrl + C.

Highlight the remaining cells in the column and click Paste or press Ctrl + V.

TIP: You can also quickly copy the contents of a cell into the remaining cells in the column by double-clicking the box on the lower, right corner of the cell.

Each of the cells in the new column look like they contain the names in a different case. However, each cell still contains the Proper() function referring to another cell. Because we are planning to delete the original column, we need to replace the function with the actual name to which it evaluates. To do this, highlight all the cells in the column containing the function and copy them.

To paste the values back into the same cells, click the down arrow on the Paste button in the Clipboard section of the Home tab. Then, click Values in the Paste Values section.

NOTE: This is the same procedure we discussed in a previous article about converting a numerical formula to a static value.

Now, all the cells in the column should contain text, not functions.

To delete the original column, select the entire column by clicking the lettered header, right-click on the header, and select Delete from the popup menu.

To convert the first names to title case, we followed the same procedure.

Changing Case of Text in Excel

Excel provided a set of options to deal with text. But, there is a problem with Excel. It does not have the option to change the case of the text in Excel worksheets. Instead, it provides three major functions to convert the text to lower, upper, and proper cases. It helps to minimize the problems in changing the cases. This article will teach how to change text cases in Excel.

Table of contents
  • Changing Case of Text in Excel
    • Functions used to Change Case in Excel
      • #1 – Lowercase in Excel
      • #2 – Uppercase in Excel
      • #3 – Proper Case in Excel
    • How to Change Case in Excel? (with Examples)
      • Example#1: Change Text to Lowercase using the LOWER function
      • Example#2: Converting Text to Uppercase Using UPPER function
      • Example#3: Converting Text to Title case Using the PROPER function
      • Example#4: Changing the Text Using the Flash Fill Method
    • Things to Remember
    • Recommended Articles

Functions used to Change Case in Excel

The following are the functions used to change text case in Excel:

#1 – Lowercase in Excel

Use the LOWER function to convert all the text presented in a cell to lowercase. Use the following formula:

#2 – Uppercase in Excel

Use the UPPER function to convert all the text presented in a cell to uppercase. Use the following formula:

#3 – Proper Case in Excel

Use the PROPER function to convert all the text presented in a cell to the title case. Use the following formula:

WE should select only one cell to apply the change case functions. When a formula is entered, the hover is displayed by mentioning the purpose of the function. After entering a formula, we should press the “ENTER” key on the keyboard to have the desired result.

How to Change Case in Excel? (with Examples)

The change case functions are used in two ways in Excel:

  • First method: Directly enter the function name in a cell.
  • Second method: Insert the functions from the “Formula” tab.

The following are some examples to understand how to change the case.

Example#1: Change Text to Lowercase using the LOWER function

The following example is considered to explain the use of the LOWER function. Several steps are required to display the text in the required format.

The above figure shows the sample data converted into a lower case. We should enter the formula of the lower function into the highlighted cell.

  • Step 2: First, we must go to the top of the ribbon and click on the “Formula” tab to select the function, as shown in the image. The description for the LOWER (text) is displayed by hovering on the function.
  • Step 3: Once we click on a LOWER function, the function argument window will open.
  • Step 4: The cell address should be entered into the function arguments box, and the results are displayed in the figure.

Now, press the “Enter” key to see the change in the text case.

  • Step 5: Now, we will drag the formula to apply to the remaining cells to convert the data presented in the next rows.

Example#2: Converting Text to Uppercase Using UPPER function

The following example is considered to explain the use of the UPPER function. Several steps are required to display the text in the capitalized format.

  • Step 1: We must select the cell we want to convert into the upper case.
  • Step 2: Then, we must go to the top of the ribbon and click on the “Formula” tab to select the function, as shown in the image. The description for the UPPER (text) is displayed by hovering on the function.
  • Step 3: Once we click on an UPPER function, the “Function Arguments” window will open. The UPPER function is found among the several functions available.
  • Step 4: The cell address should be entered into the “Function Arguments” box, and the result is displayed in the figure.

Now, press the “Enter” key to see the change in the text case.

  • Step 5: We will drag the formula to apply to the remaining cells to convert the data presented in the next rows.

Example#3: Converting Text to Title case Using the PROPER function

  • Step 1: We must select the cell we want to convert into a title case.

The above figure shows the sample data to convert into a title case. We should enter the formula of the title function into the highlighted cell.

  • Step 2: Then, we must go to the top of the ribbon and click on the “Formula” tab to select the function, as shown in the image. The description for the PROPER (text) is displayed by hovering on the function.
  • Step 3: Once we click on a PROPER function, the “Function Arguments” window will open. The PROPER function is found among the several functions available.
  • Step 4: We must insert the cell address into the “Function Arguments” box, and the result is displayed in the figure.

Now, press the “Enter” key to see the change in the text case.

  • Step 5: Next, we will drag the formula to apply to the remaining cells to convert the data presented in the next rows.

Example#4: Changing the Text Using the Flash Fill Method

  • Step 1: We must type the data in lower case to convert it into upper case.
  • Step 2: Now, type the same data in a highlighted cell in the UPPER case and press the “ENTER” key to move the cursor to the below cell.
  • Step 3:
  • Go to the “Data” tab and click on the ‘Flash Fill’ option in Excel under the “Data Tools” group. The remaining data is automatically typed by converting it into the UPPER case, as shown in the below figure.

Things to Remember

  • The change case functions such as LOWER, UPPER, and PROPER do not work on the numerical data, special characters, and punctuations.
  • While using a PROPER function, the “S” letter after the apostrophe is converted into the uppercase. So here, we should convert letters manually into lower case.
  • The change case functions accept only one input at a time. Therefore, it is hard to change the case of text presented in all cells.
  • There is no option to select only the particular text to change the case.

Recommended Articles

This article is a guide to Change Case in Excel. Here, we discuss how to change cases in Excel using 1) Lower Function, 2) UPPER Function, 3) PROPER Function, and 4) FLASH FILL with examples. You may also look at these useful functions in Excel: –

Supposing you have a worksheet which contains some texts mixed with upper case and lower case. In this case, how could you change upper case to lower case and vise verse? In Excel, you can use Upper or Lower function to deal with it. But with the Kutools for Excel’s Change Case utility, you can quickly do the following options:

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

If you have some mixed text contain upper case and lower case, and now you need to change case of text to all caps or upper case, you can quickly get it done as follows:

1. Select the range that you want to change case of text into upper case.

2. Apply Change Case tool by clicking Kutools > Text > Change Case.

3. In the Change Case dialog box, check UPPER CASE from Change type. And you can preview the results from the Preview box. See screenshot:

4. Click Ok or Apply. The text case has been converted to upper case. Note: click Ok button will close the dialog box and apply the operation; but click Apply button will only apply the operation without closing the dialog box. See screenshots:

Also you can easily convert text case to lower case as follows:

1. Highlight a range that you need to convert the text case to lower case.

2. Go to the Change Case dialog box, specify lower case from Change type. And you can preview the results from the Preview box. See screenshot:

3. Click Ok or Apply. All of the text case has been changed to lower case in the selected range. see screenshots:

Proper case: upper case on the first letter in each word and lower case the rest.

You can change the text case to proper case as follows:

1. Highlight the range that you need to convert the text case to proper case.

2. Go to the Change Case dialog box, specify Proper Case from Change type. And you can preview the results from the Preview box. See screenshot:

3. Click Ok or Apply. All of the text case has been changed to proper case. See screenshots:

You can change case of text to sentence case as follows:

1. Highlight the range that you need to convert case of the text to sentence case.

2. Go to the Change Case dialog box, specify Sentence Case from Change type. And you can preview the results from the Preview box. See screenshot:

3. Click Ok or Apply. All of the text case has been changed to sentence case. See screenshots:

Toggle Case: it will shift between two case views (for example, to shift between Apple and the opposite, aPPLE ).

You can change case of text to toggle case as follows:

1. Highlight the range that you need to convert case of the text to toggle case.

2. Go to the Change Case dialog box, specify Toggle Case from Change type. And you can preview the results from the Preview box. See screenshot:

3. Click Ok or Apply. All of the text case has been changed to toggle case. See screenshots:

1. This utility supports Undo (Ctrl+Z), and the undo is only one level.

2. Share this feature to your friend or social media button: If you like this feature and want to share it to your friends or other social media, please click this button in the Change Case dialog box. See screenshot:

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Top 6 Methods to Change Capital Letters to Lower Case

There are many methods to change capital letter text to lowercase in Excel. This guide will look at the top 6 ways to make capital letters into lower cases.

Table of contents
  • Top 6 Methods to Change Capital Letters to Lower Case
    • #1 Using Lower Function to change case in Excel
      • Example
    • #2 Using VBA Command Button
      • Example
    • #3 Using VBA Shortcut key
      • Example
    • #4 Using Flash Fill
      • Example
    • #5 Enter Text in Lower Case Only
      • Example
    • #6 Using Microsoft Word
      • Example
    • Things to Remember
    • Recommended Articles

#1 Using Lower Function to change case in Excel

MS Excel has a built-in function for decapitalizing each character in a word, a LOWER function.

Example

Suppose we have a list of some verbs in Excel. We want to change the case of the text to lowercase.

To change the case to lower, we need to write the function in cell C2 as ‘=LOWER(A2)’. The”=’ or ‘+’ sign is used to write the function, the “LOWER” is the function name, and A2 is the cell reference Cell Reference Cell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. read more for the text we want to change the case.

Press the “Enter” key. This function will convert all letters in a text string to lowercase.

One value is converted now. For other values, we can either press the “Ctrl+D” key after selecting all the cells with the top cell or press the “Ctrl+C” and “Ctrl+V” for copying and pasting the function. Else, we can drag the formula to other cells to get the answer.

#2 Using VBA Command Button

We can create a VBA command button and assign the code to change the following text to lowercase using the command button.

Example

Step 2: Click the worksheet location where we want the command button to appear. We can resize the command button using the “ALT” button.

Step 3: Using the “Properties” command, change the properties of the command button like caption, name, AutoSize, WordWrap, etc.

Step 4: To assign the code to the command button, we must click on the “View Code” command in the “Controls” group in the “Developer” tab. Ensure “Design Mode” is activated.

Step 5: Please select “ConvertToLowerCase” from the drop-down list in the opened window.

Step 6: Paste the following code in between the lines.

Code:

Step 7: Exit the Visual Basic Editor. Ensure the file is saved with the .xlsm extension as we have a macro in the workbook.

Step 8: Now, deactivate “Design Mode.” After selecting the required cells, the values are converted to lowercase whenever we click on the command button.

Select all the values from A2:A10 and click on the command button. The text will get changed to lowercase.

#3 Using the VBA Shortcut key

This way is similar to the above, except we do not need to create the command button here.

Example

Step 1: Open the Visual Basic Editor from the “Developer” tab or use using the excel shortcut key Using The Excel Shortcut Key An Excel shortcut is a technique of performing a manual task in a quicker way. read more (Alt+ F11).

Step 2: Insert the module using the Insert menu -> Module command.

Step 3: Paste the following code.

Step 5: Now, choose the “Macros” in the “Code” group in the “Developer” tab.

Step 6: Then click on “Options” and assign the shortcut key to the macro. We can write a description as well.

In our case, we have been assigned “Ctrl+Shift+L.”

Step 7: Macro is ready to use. Select the required cells to change the values into lowercase and press the “Ctrl+Shift+L” keys.

#4 Using Flash Fill

Example

Suppose we have the following data, which we want to get in lowercase.

We need to manually write the first list value in the lower case in the adjacent cell to do the same.

Come to the next cell in the same column and press the “Ctrl+E” keys.

Choose “Accept Suggestions” from the box menu that appeared.

That is it. We have all the values in the lower case now. So, we can copy the values, paste the same onto the original list, and delete the extra value from the right.

#5 Enter Text in Lower Case Only

We can make a restriction so that the user can enter text values in lowercase only.

Example

To do this, the steps are:

  • We must select the cells which we want to restrict.
  • Then, choose “Data Validation” from the “Data Tools” group from the “Data” tab.
  • Apply the settings explained in the figure below.
  • Whenever the user enters the value in capital letters, MS Excel will stop and show the following message.

#6 Using Microsoft Word

In Microsoft Word, unlike Excel, we have a command named “Change Case” in the “Font” group in the “Home” tab.

Example

Suppose we have the following data table for which we want to change the text case to the “lower” case.

First, we will copy the data from MS Excel and paste it into MS Word to change the case. To do the same, the steps are:

Select the data from MS Excel. And press the “Ctrl+C” key to copy data from MS Excel.

Open the MS Word application and paste the table using the “Ctrl+V” shortcut key.

Select the table using the “Plus” sign on the left-top side of the table.

Choose the “Change Case” command from the “Font” group and select “lowercase” from the list.

Now, the data table is converted to “Lower.” After selecting the “Plus” sign from the left top corner, we can copy the table and paste it into Excel.

We can delete the old table using the “Contextual” menu, which we can get by right-clicking on the table.

Things to Remember

Using the VBA code (command button or shortcut key) to convert the values into lowercase, we must save the file with the .xlsm extension as we have macros in the workbook.

Recommended Articles

This article is a guide to Lowercase in Excel. We discuss the top 6 ways to change capital letters to the lower cases, including – the LOWER function, VBA Code, Flash Fill, VBA shortcut keys, etc., along with examples. You can learn more about Excel functions from the following articles: –

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

There are times when letter case in Excel needs to be changed or corrected.

Doing the changes by hand is extremely boring. Not only that, it’s also inefficient.

In some cases, it’s also close to impossible…

…Imagine changing letter case in large data sets or long data strings!

Well, I’m here to share the good news: You don’t have to do it manually at all! Excel happens to offer a few functions that allow us to address letter case in a much more effective manner.

There are two functions that are most notable: ‘UPPER’ and ‘LOWER’. They do exactly what their names imply.

There is also a function that capitalizes the first letter of proper names and places. It is called, ‘PROPER’.

All three have their uses and make life a lot more convenient for us Excel users.

There might be some (rare) cases, where these functions aren’t going to provide a solution. Don’t worry, I’ll show you exactly what to do in those cases.

For now, let’s set the foundation by learning how to use the ‘UPPER’, ‘LOWER’, and ‘PROPER’ functions on their own.

Table of Content

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Capitalize the first letter using
the function ‘PROPER’

The ‘PROPER’ function works the same way its cousins ‘LOWER’ and ‘UPPER’ do.

The difference is, that it only capitalizes the first letter of each substring of text. This could be a single word. It could also be multiple words such as first and last names, cities and states, abbreviations, suffixes, and honorifics/titles.

The syntax for all three of the functions that change case is the same.

It’s simply the function and the text (or cell reference) within parenthesis.

Here’s the syntax for ’PROPER’:

Now let’s look at some examples of text strings and the results of applying the ‘PROPER’ function to them.

Notice that the function takes string elements whether they are capitalized or not. Then it converts the entire string to proper form. This is quite straightforward.

But there are a couple of issues to be aware of when using the ‘PROPER’ function…

The main issue is that it does not know the difference between an actual word and an abbreviation – like an acronym for instance. For example, if we apply the ‘PROPER’ function to something like ‘FIFA’, it will return ‘Fifa’. This is not the desired outcome and should be kept in mind.

Another example of this would be using the suffix ‘md’ for a medical doctor. Again, ‘PROPER’ will see this more as a word and will return ‘Md’.

If you want both letters capitalized, place a period after each letter in the abbreviation.

Or apply the ‘UPPER’ function to just that part of the original string.

Capitalizing all letters using
the function UPPER

Now that we know how to capitalize the first letter of a text string, let’s find out how to make all caps.

This requires the ‘UPPER’ function and as we already noted, its syntax is much like that of ‘PROPER’.

Consider a case in where we have a worksheet that contains names of customers. This gets exported out of a database in all lowercase form. No problem, we just bring the ‘UPPER’ function into the game.

Apply ‘UPPER’ to our cell references containing the customer names to make all caps.

Changing uppercase to lowercase
using the function ‘LOWER’

If you have a list that comes as all caps, you can use the ‘LOWER’ function to convert to all lower case.

Like this:

These are three of the simplest functions to use in Excel and they sure come in handy when the need presents itself.

But what about something a bit less straightforward?

Using case functions together
for customization

It’s one thing to learn the basic functions and tools that Excel offers.

But it’s far more fun and fulfilling to piece these together to create our own custom solutions.

That’s exactly what we are going look at now…

Think back to what you learned about abbreviations. That’s right, they tend to create some issues! The three functions you’ve learned about, can’t solve these issues on their own.

Let’s say we want to fix the string ‘fifa world cup’ to read ‘FIFA World Cup’.

This requires us to split the string into separate substrings, namely ‘fifa’, and ‘world cup’.

Here we could use the ‘UPPER’ and ‘PROPER’ functions, respectively.

We could split our original string manually and apply the function we need on each substring.

But then we would need to reunite the results of each separate function to get our final result.

For the sake of demonstration, let’s look at how combining these functions with a couple of others can achieve the same results.

Quick intro to
the functions ‘ LEFT ’ and ‘ RIGHT ’

For anyone who is not familiar with these two functions, they are very simple in concept and easy to pick up.

Pro-tip: I explain LEFT and RIGHT (+other text functions) in much more in-depth right here

They do exactly what they sound like they do:

They return a substring of characters from the left or right side of our original text string.

Both require one argument: ‘text’.

This is the text or cell reference to apply the function to.

There is also an optional argument: ‘num_chars’. This is the number of characters from the left or right that we want the function to return. (If we omit this argument, the functions return the first character from the left or right.)

The formal syntax for each is as follows:

Look at the following simple examples to see how these functions work.

Supposing you receive an Excel report with all text strings in uppercase letters, it will be easier to read if you can change these uppercase letters into lowercase in Excel as following screenshot shown. The following tricks can quickly change all uppercase letters into lowercase in Excel.

Microsoft Excel supports the Lower function to change any kinds of text strings to lowercase, please do as this:

1. In the adjacent blank cell D1, enter or copy the formula

and press Enter key to get the first result. See screenshot:

2. Then, select the cell D1 and drag the fill handle down to the cells where you want to apply this formula, and all the text strings have been converted to lowercase letters, see screenshot:

3. And then, you should copy the converted cells and paste them to the original cells as values format as you need.

To convert the text strings to uppercase, please apply this formula:

To convert the text strings to proper case which means to capitalize the first letter of each word, please apply this formula:

Layoff season is coming, still work slowly?
— Office Tab boosts your pace, saves 50% work time!

    Amazing! The operation of multiple documents is even more relaxing and convenient than single document; Compared with other web browsers, the interface of Office Tab is more powerful and aesthetic; Reduce thousands of tedious mouse clicks, say goodbye to cervical spondylosis and mouse hand; Be chosen by 90,000 elites and 300+ well-known companies!

In Excel 2013 and later version, there is a handy utility – Flash Fill, with this feature, you can quickly solve this task as well.

1. Insert a blank column adjacent to your data that you want to change case, and then, enter the first text string that you want to use. For example, I will type the text string with lowercase in cell B2, see screenshot:

2. After typing the proper formatted text string and then select it, then click Home > Fill > Fill Flash, and the remaining cells in this column will be filled with lowercase text strings, see screenshot:

1. To get all uppercase text, please type the uppercase text into the first cell, and then apply the Flash Fill function;

2. To get all proper case text, please type the text strings with first letter capitalized for each word into the first cell, and then apply the Flash Fill function;

The following VBA codes also can help you to change the text string cases to your need, and the converted results will be replaced the original values directly.

1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Change text strings to lowercase:

3. And then, press F5 key to run this code, in the popped out prompt box, select the range of cell you want to convert case, see screenshot:

4. Then, click OK button, and all text strings in the selected cells have been converted to lowercase, see screenshot:

Change text strings to uppercase, please copy and paste this VBA code into the Module:

Capitalize the first letter of each word, please copy and paste this VBA code into the Module:

300 advanced tools,solve 1500 work scenarios

solve 80% Excel puzzles, increase 80% productivity

Help you Get Promotion,
Increase Salary!

Choice by 110,000 elites, 300+ well-known companies

If you have Kutools for Excel installed, its Change Case tool can help you change all text strings to lowercase, uppercase, proper case, sentence case and toggle case in cells directly. Click to download Kutools for Excel! Please see below demo:

After installing Kutools for Excel, please do as this:

1. Select the cells that you want to change case, and then click Kutools > Text > Change Case, see screenshot:

2. In the Change Case dialog box, select lower case option, and then, click OK or Apply button, all the text strings have been converted to lowercase you desired, see screenshot:

Tips: This Change Case utility also can help you to change the text strings to uppercase, proper case, sentence case and toggle case as you need.

Busy work on weekend, Use Kutools for Excel ,
gives you a relaxing and joyful weekend!

On the weekend, the kids are clamoring to go out to play, but there is too much work surrounds you to have time accompany the family. The sun, the beach and the sea so far away? Kutools for Excel helps you to solve 80% Excel puzzles, save 80% work time.

You can change the capitalization, or case, of selected text in a document by clicking a single button on the Home tab called Change Case.

Change case

To change the case of selected text in a document, do the following:

Select the text for which you want to change the case.

Go to Home > Change case .

Do one of the following:

To capitalize the first letter of a sentence and leave all other letters as lowercase, click Sentence case.

To exclude capital letters from your text, click lowercase.

To capitalize all of the letters, click UPPERCASE.

To capitalize the first letter of each word and leave the other letters lowercase, click Capitalize Each Word.

To shift between two case views (for example, to shift between Capitalize Each Word and the opposite, cAPITALIZE eACH wORD), click tOGGLE cASE.

To apply small capital (Small Caps) to your text, select the text, and then on the Home tab, in the Font group, click the arrow in the lower-right corner. In the Font dialog box, under Effects, select the Small Caps check box.

To undo the case change, press CTRL+ Z.

To use a keyboard shortcut to change between lowercase, UPPERCASE, and Capitalize Each Word, select the text and press SHIFT + F3 until the case you want is applied.

See also

Change case

To change the case of selected text in a document, do the following:

Select the text for which you want to change the case.

Go to Home > Change case .

Do one of the following:

To capitalize the first letter of a sentence and leave all other letters as lowercase, click Sentence case.

To exclude capital letters from your text, click lowercase.

To capitalize all of the letters, click UPPERCASE.

To capitalize the first letter of each word and leave the other letters lowercase, click Capitalize Each Word.

To shift between two case views (for example, to shift between Capitalize Each Word and the opposite, cAPITALIZE eACH wORD), click tOGGLE cASE.

To apply small capital (Small Caps) to your text, select the text, and then on the Format menu, select Font, and in the Font dialog box, under Effects, select the Small Caps box.

Small Caps shortcut key: ⌘ + SHIFT + K

To undo the case change, press ⌘ + Z .

To use a keyboard shortcut to change between lowercase, UPPERCASE, and Capitalize Each Word, select the text and then press fn+ SHIFT + F3 until the style you want is applied.

See also

PowerPoint for the web supports changing case. See the procedure below.

Word for the web doesn’t support changing case. Use the desktop application to open the document and change text case there, or else you can manually change the casing of text in Word for the web.

Select the text you want to change.

Go to Home > More Font Options > Change case.

Quickly and easily monitor tracked changes in Excel

Microsoft’s Track Changes function allows revisions to be made to a document and keeps a complete record of all changes made. While Track Changes is most often used in Microsoft Word, Excel also contains this useful feature, which can be valuable if you have created a business document, such as a business plan, that you would like a co-worker to revise and proofread. All your co-worker needs to do is turn on the Track Changes function and all changes will be recorded. Once the document has been returned to you, you can easily accept or reject each change as you see fit.

The following is a step-by-step guide to tracking changes in Microsoft Excel 2010 and Microsoft Excel 2013. We also have guides for using Track Changes in Microsoft Excel 2007.

How to turn on Track Changes in Excel 2010 and 2013

  • Open an Excel document and click on the Review tab.
  • Click the Track Changes button (located on the far right).
  • A small drop-down menu will appear. Click on Highlight Changes.
  • In the dialogue box that opens, check the box next to Track changes while editing. This also enables file sharing.
  • You now have the following parameters:
    • When: Select which conditions trigger Excel to track changes. Select All to track every change. You also have the option to highlight changes since the last time you saved, since a particular date, or since changes were made that have not yet been reviewed.
    • Who: Configure which user’s changes should be tracked. You have the option of choosing Everyone or Everyone but Me.
    • Where: Specify the range of cells in which changes will be tracked.
    • Highlight changes on screen: Click this if you want others to know you are tracking changes.
    • List changes in a new sheet: You can view all changes made to the document in a printable list on a separate worksheet, making it very simple to quickly survey a large amount of information. Note: This option only becomes available after you start tracking changes and save the workbook at least once.
    • Click OK once you have made your selections and then OK again to save your workbook.

Why is this useful?

Tracking changes in Excel 2010 and 2013 is less about editing and more about keeping track of the work done on a document by one or more users. For example, if you have an Excel document that needs to be modified by a number of people, create the document, follow the instructions above, and uncheck the Highlight changes on screen option.

Next, send the business document to everyone assigned to work on it. All their changes will be recorded, although they won’t see this on the screen (remember, you unchecked the Highlight changes on screen option). When the document is returned to you, it will simply be a matter of turning the Highlight changes on screen option back on and reviewing the work that was done.

How to accept tracked changes in Excel 2010 and 2013

If you hover over or click on a cell that has been changed (indicated by a triangle at the top-left corner of the cell), a pop-up window will show the changes, the author of the changes, and the time the changes were made. To accept or reject the changes, click on the Review tab again and select Track Changes, then Accept/Reject Changes.

After clicking Accept/Reject Changes, click OK. Another pop-up will appear, prompting you to select the changes that should be accepted or rejected (you are then given the option of selecting the changes based on the aforementioned three options: When, Who, and Where). Excel will display every change that was made in the document based on which option you have selected, prompting you to accept or reject each one. After you have gone through each change and accepted or rejected it, click on the Track Changes drop-down menu again and select the Highlight Changes option.

There are several important points you should remember when tracking changes in Excel 2010 and 2013:

  • If you have a document with tracked cells, turning Track Changes off will automatically accept the changes that were made; they will not appear as tracked changes in subsequent versions of the document. This means that if you want to keep a record of the changes made to the document, be sure to save it before turning Track Changesoff. Scribendi’s business document editors recommend the following process:
    • Before turning Track Changes off, click Save As, and save a version of the document with the suffix “TRACKED_REVISED.”
    • Next, without turning off Track Changes or modifying the document, click Save As, and save another copy with the new suffix “CLEAN_REVISED.”
    • Turn off Track Changes and then click Save (as opposed to Save As). At this point, you will have the TRACKED_REVISED version showing the tracked changes and the CLEAN_REVISED version with the changes integrated.
    • When you open a tracked Excel document, be aware that you may not be seeing all the changes made to the document. This is because the default setting in Excel shows only the tracked changes made since the last time the document was saved. To see all changes in the document, simply choose All in the When section of the Track Changes dialogue box.

Tracking changes in Excel 2010 and Excel 2013 is easy once you know the steps. However, should you have any problems revising a document created in Excel, our business document proofreaders are available around the clock for all your business proofreading needs.

Account Information

Share with Your Friends

3 quick and easy ways to summarize Excel data

3 quick and easy ways to summarize Excel data

Summarizing data in Microsoft Excel doesn’t have to be difficult. Put these few techniques to work for almost magical results.

Summarizing data is Microsoft Excel’s main function. The good news is that a lot of number crunching can be done on the fly and without any specialized knowledge. Even if you know advanced summarizing techniques, one or more of these Excel tips might make you look good. I’ll show you three ways to summarize Excel data with little effort—almost like magic.

I’m using Microsoft Excel on Office 365 (desktop) on a Windows 10 64-bit system, but these tips will work in older versions and in the browser edition, with one exception: You can’t add a totaling row to a Table object in the browser. You can work with your own data or download the demonstration .xlsx and .xls files.

How to use the status bar to summarize Excel data

The status bar provides instant gratification when summarizing–all you have to do is select the values. Figure A shows the March values selected. The status bar responds by displaying the average, count, and sum of the selected values–all you did was select a few values. This trick can make you look great during a meeting; it’s an illusion, but the boss doesn’t need to know.

Figure A

That’s not all. You can customize the status bar to display exactly the information you need. Simply right-click and check the appropriate options (Figure B).

Figure B

How to use AutoSum to summarize Excel data

You probably know about Excel’s AutoSum tool: You select a cell below or to the right of a few continuous values, click the AutoSum option, and out pops a rabbit… I mean, the sum of the adjacent values. Let’s work through a quick example.

Using the demonstration sheet shown in Figure A, click G3 and then click AutoSum in the Editing group on the Home tab–don’t look away, or you might miss it. Before pressing Enter a second time, you can see that AutoSum inserted a SUM() function that evaluates all of the contiguous values to the left (Figure C). Press Enter a second time to commit the function. If you select a range of cells, AutoSum will insert multiple functions. For instance, if you select G3:G14, AutoSum will enter a SUM() function for each row.You probably know about AutoSum: You select a cell below or to the right of a few continuous values, click the AutoSum option and out pops a rabbit… I mean, the sum of the adjacent values. Let’s work through a quick example.

Using the demonstration sheet shown in Figure A, click G3 and then click AutoSum in the Editing group on the Home tab. Don’t look away or you might miss it. Before pressing Enter a second time, you can see (Figure C) that AutoSum inserted a SUM() function that evaluates all of the contiguous values to the left. Press Enter a second time to commit the function. If you select a range of cells, AutoSum will insert multiple functions. For instance, if you select G3:G14, AutoSum will enter a SUM() function for each row.

Figure C

But wait: There’s more! The AutoSum option has a dropdown that offers several other functions–SUM() is the default, but simply select a different option and continue. How’s that for a nice sleight of hand?

How to filter a table in Excel

EExcel’s Filter feature is probably one of my favorites; I use it a lot, because it’s quick and effective. We won’t explore the entire feature, but I will show one quick behavior that you’ll probably come to respect as much as I do—this feature summarizes.

To apply the filter, click anywhere inside the data range, click the Data tab, and then click Filter in the Sort & Filter group. This displays dropdowns in each header cell. Click those dropdowns to explore filtering options, but that’s not what we’re interested in right now.
So far, so good, but instead of pursuing filtering options, let’s convert this ordinary data range into a Table object. Follow these steps.

Click anywhere inside the data range.

Click the Insert tab.

Click Table in the Tables group.

In the resulting dialog, confirm whether your data range has headers (ours does) and click OK.

The Table object has a neat feature—a totaling row—that combined with the built-in filtering feature is almost magical. What’s great about it is that the function evaluates only the filtered set.
Prepare to be enchanted: Click anywhere inside the Table and then click the contextual Table Design tab. In the Table Style Options group, check the Total Row option. Figure D shows the results of a SUBTOTAL() function, which is a powerful and flexible function that you’ll want to explore further.

Figure D

Similar to AutoSum, you can change the function’s purpose. In addition, you can add a function to each column in the Table.

The setup takes a few easy steps to implement, but once it’s in place, those summaries are only a click away. Talk about pulling a rabbit out of your hat!

Easy summarizing

You can’t use the summarized results in further calculations, but you won’t always need that–sometimes, you only need a quick look at what’s going on, and that’s when these techniques will come in handy and really help you shine. Once you’re familiar with these tricks, you will find yourself using them all the time. It’s not really magic–it just seems like it.

Stay tuned for future articles on summarizing Excel data: I’ll also cover the Subtotal feature, PivotTables, and more.

Send me your Microsoft Office questions

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at [email protected]

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.

Sometime you may need to change uppercase letters to title case or proper case as following screenshot shown. And this article will guide you to change all uppercase letters or text strings to proper case or title case in Microsoft Excel.

With Kutools for Excel‘s Change Case utility, you can quickly change any text strings to uppercase, lowercase, title case or sentence case as you need.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Microsoft Excel’s Proper function can change any text to proper cases. Please do as this:

1. In the adjacent blank cell B2, enter this formula: =PROPER(A2) , see screenshot:

2. Then press Enter key, select cell B2, and drag the fill handle over the range that you want to contain this formula. And all of the upper case text has been converted to the proper case. See screenshot:

Note : As they are formulas, when you copy and paste them, you need paste them as values.

1. Active the worksheet that you want to use.

2. Then click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed (or you can press the shortcut keys Alt + F11 to open the window), then click Insert > Module, and input the following code into the Module:

3. Then click button to run the code, then a dialog is displayed for you to select a range, see screenshot:

4. Click OK, and all of the upper case letters have been changed to proper cases.

The Change Case tool of Kutools for Excel can help you easily change the text or letters to any kinds of cases. So does the proper/title case in selections.

1. Select the range which you will change uppercases to proper / title cases.

2. Click the Kutools > Text > Change Case…. See screenshot:

3. In Change Case dialog box, check the Proper Case option, and you can preview the results at the Preview Pane. See screenshot:

4. Then click OK or Apply. And all uppercase letters in the selection are changed to proper / title case.

Note: This utility of Proper Case can not only change uppercase to title case, but also can change all case in the selection to title case.

Kutools for Excel‘s Change Case tool can quickly change text case to UPPER, LOWER, PROPER, Sentence Case, and tOGGLE cASE. Click to know more