Tip of the week # 628
1 November 2020


Change case without using Formulas and Functions (Microsoft Excel 2013 onwards) *

As you probably know, Excel has no Change Case button like Word has. (And no, SHIFT + F3 doesn't work either.) Instead, you might be using Excel's UPPER and LOWER Functions to change text in a cell to UPPERCASE or lowercase.

But if you are using version 2013 or later you can use the Flash Fill functionality from the Auto Fill Options button that pops up when you've used the Auto Fill feature. Or - as described in tip 620 - you can use its keyboard shortcut.

Here's how:

  1. Open FlashFill.zip to understand this example.
  2. Click in cell B2.
  3. Type ANNE BROWN or anne brown.
  4. Press ENTER.
  5. Press CTRL + E.

Excel will recognise the "pattern" you specified in step 3 and converts all text to uppercase or lowercase letters. No need to work with "helper columns" to store the newly formatted text. As a recent webinar attendee wrote "Like watching Professor McGonagall transfigure into a cat"

Related tips

  1. Split first name and last name into separate cells or combine first and last name in one cell - tip_620.php
  2. Quickly increase a list of numbers by one - tip_258.php
  3. Toggle between UPPER CASE, lower case and Sentence Case in Word - tip_19.html

.


* Unless stated otherwise, these tips were written for Microsoft Office 2010.