3 more Excel questions, not just the basics anymore

I was asked during an Excel 2007 Basic training session the following questions.  All good questions, and as I am preparing my response, I thought perfect item for my blog.  And so my blogging adventure after one week is hitting home.  Here you go SJFC community, the answers you requested:  I am starting with the easiest first…

  

How do I bring up or display the “Hot Keys”?

Strictly a keyboard user, you can select commands on the Ribbon by using Keyboard shortcuts.  

Press the Alt key, followed by letter keys displayed on the Ribbon for preferred Excel command without using the mouse.

 

Shortcut letters and numbers appear on the Ribbon.

 

Press the letter to select a tab, press a letter or letters to select a command.

Press ESC to go back one step at a time.

Press a letter or use the arrow keys on the keyboard to select the desired option. 

 

Additional resources, listing keyboard shortcuts for Excel:

• http://www.keyxl.com/aaa33ef/136/Microsoft-Excel-Software-keyboard-shortcuts.htm
• http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx   

How do I convert text to UPPER or lowercase?

 

Enter, =UPPER(“text”) and press Enter.  This function will convert ‘text’ to uppercase.  The text needs to be surrounded by quotes.

Example:

=UPPER(“robin a. schmid”) would be displayed as Robin A. Schmid

Same holds true for =LOWER(“text”)

 

How do I alphabetize all worksheets within the workbook?

In order to accomplish this task you will have to enter code within the VB (Visual Basic) editor.

1.  With Excel open, press Alt + F11.  This will launch Visual Basic

 

2.  From the Insert menu, select Module and copy/paste the code below into the window.

Sub Sortem()

For x = 1 To Worksheets.Count

For y = x To Worksheets.Count

If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then

Sheets(y).Move Before:=Sheets(x)

End If

Next

Next

End Sub

3.  Close the VBA editor and click on the Developer tab.

     

      **If the Developer tab isn’t displayed on the ribbon…

•   Click the Microsoft Office Button, and then click Excel Options
•   Click Popular, select the Show Developer tab in the Ribbon check box and click on OK.

4.  Click on Macros, within the Macro dialog box, select the macro and click on Run.

**Remember to save the workbook as a Macro enabled Workbook to maintain the macro.