Excel "Tips & Tricks" v1 - Easy - Intermediate & Advanced

Updated: Nov 29


Every entry will consist of 3 "tips & tricks" - Easy - Intermediate - Advanced



October 29, 2022


Easy Trick


This is 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.

How to format the individual contents of an Excel cell.
Formatting the contents of an Excel cell.


#2


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

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

#3

We then double clicked on the word SELECT.

Step 2 of formatting text in a cell in Excel
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.

Results of formatting individual text in a cell in Excel
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.

You can keep formatting individual text in a cell in Excel
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
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.

Formatting text within a cell in Excel
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 Tip


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.




Step One - Range Names in Excel
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:



Range Names in Excel
Zoom feature @ 39% in Range Names in Excel

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



Onscreen result of Named Range in Excel
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.



Easy access to your Range Names in Excel
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 Trick


"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. A regular sort will not do the job.
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:

Starting data to begin Group Sort in Excel
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 in Group Sort in Excel
Step Two...Group Sort Using Sort Ascending in Excel

Result of using Group Sort and 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:



QR code with link to Bob Umlas Excel Legend bio
QR Code displays books written by Excel Legend with link to Amazon



Bob Umlas is inviting you to a scheduled Zoom meeting. Every Tuesday 6PM Eastern. This is a free meeting.

Topic: Bob Umlas' Excel SIG Zoom Meeting

Time: This is a recurring meeting

Join Zoom Meeting

https://us02web.zoom.us/j/85438615291

Meeting ID: 854 3861 5291

One tap mobile

+19292056099,,85438615291# US (New York)

+13017158592,,85438615291# US (Washington DC)

Dial by your location

+1 929 205 6099 US (New York)

+1 301 715 8592 US (Washington DC)

+1 309 205 3325 US

+1 312 626 6799 US (Chicago)

+1 646 931 3860 US

+1 719 359 4580 US

+1 253 215 8782 US (Tacoma)

+1 346 248 7799 US (Houston)

+1 360 209 5623 US

+1 386 347 5053 US

+1 507 473 4847 US

+1 564 217 2000 US

+1 669 444 9171 US

+1 669 900 6833 US (San Jose)

+1 689 278 1000 US

Meeting ID: 854 3861 5291

Find your local number: https://us02web.zoom.us/u/kjML7LdwX