Excel Training - Easy - Intermediate & Advanced Excel Tips & Tricks - Vol #1

October 29, 2022

Easy

How to format text within a cell:

#1

Cell A1 shows text that we formatted. We will show you

how to format the text in cell A3.

Formatting the contents of an Excel cell.

#2

Here we selected cell A3 to make it the active cell.

Step One to formatting individual text in a cell in Excel...select the cell

#3

We then double clicked on the word SELECT.

Step Two to formatting individual text in a cell in Excel...select the specific text to format

#4

Once the word was highlighted, we changed the color from black to red.

Step Three of formatting individual text in a cell in Excel...choose your desired format (here...red)

#5

We then selected the words INSIDE THE CELL.

Continue formatting additional individual text in a cell in Excel...highlight the text

#6

Then we chose Bold in the Font section.

Additional formatting of individual text in a cell in Excel (here, BOLD)

#7

Finally, we underlined the text INSIDE THE CELL. We also formatted the words

SPECIAL TOUCH with Bold and Italic.

Additional formatting of individual text in a cell in Excel (here, bold AND italic)

And that's how you format the text within a single cell in Microsoft Excel.

---

Intermediate and Advanced Tips and Tricks (below) courtesy of the Excel Legend (AKA Bob Umlas)

Intermediate

Range Names

Do you use Range Names? They can be very handy!

Let's unpack what RANGE NAMES refers to.

#1 Let's say you have data in columns A through F and in rows 1-7

That range is defined as A1:F7 The range refers to that bunch of data.

Range Names in Excel...first, highlight the range of cells and then name that Range

#2 Now, we want to give that range a name, so we can return to it later (and a whole lot more) So we give it the name RWS.

In addition, if you change the Zoom view to 39% or less:

Zoom feature @ 39% in Range Names in Excel

Then the range name (all range names over 1 cell in definition) show up like this:

Result of Named Range in Excel

In addition, the name RWS will exist in the NAME BOX, where you can select it and Excel will go to that data.

Range Names get added to the Name Box in Excel

So, the next time you're searching through a large workbook with multiple sheets, if you remember the RANGE NAME you will save yourself a ton of time!

---

Advanced

"Group Sort"

Suppose you have data like this, and you want to sort each group by last name:

Raw Data for Group Sort in Excel

Clearly, this is not going to sort as you would like without using some data manipulation. Think about it a bit first, then read on.

Insert a row at row 1, then enter this formula in cell B2:

Step One...Group Sort from Raw Data in Excel

This is saying that if the cell above and to the left is empty, use the cell to the left. Otherwise, use the cell above. Not clear how this solves the issue? Fill cell B2 down:

This formula puts the name in each cell of the "group"! It changes to "Adams, Jane" in row 6 because row 5 is blank. Now all you need do is select cell B2 and click the Sort Ascending button in the Data tab of the ribbon, and you're done (well, clear column B after the sort!)

Step Two...Group Sort Using Sort Ascending in Excel

Result after using the Group Sort & Sort Ascending in Excel

Want to see why Bob Umlas is the Excel Legend? Scan the QR Code below:

Bob Umlas is inviting you to a monthly Zoom meeting. Second Tuesday of the month. 6PM Eastern. This is a free meeting!

Please email your questions to Excel.Legend@rwsinfo.com