Transpose-->switch columns and rows Excel 2007

The data is in rows or in columns and you want to rearrange or switch the data from one to the other.  Microsoft Excel 2007 provides a command to complete the process simply with just a few steps. 

To rearrange data from rows to columns, select the cells that contain the data. 

1.  From the Home tab, within the Clipboard group, click on Copy. (Ctrl +C)

2.  On the worksheet, select a cell  destination for the column into which you want to transpose the copied data. 

3.  From the Home tab, within the Clipboard group, click on the down arrow below Paste, and select Transpose. 

4.  Success!  Delete the data from the copy area. 

Additional Resource:
http://office.microsoft.com/en-us/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx

Copy single column of values to an adjacent column- Excel 2007

We all know how to copy data in Excel, in fact there are many different ways to do it. In an effort to work smarter, not harder- copy data and eliminate a few clicks of the mouse by using the keyboard instead. 

1.  Select the column of data to be copied and the adjacent column. 

2.  Press Ctrl + Shift + . (period character), insuring both columns are highlighted prior.

Note: this trick also works with rows--> Press Ctrl + Shift + , (comma character)

Outlook 2007 - Change font or font size within your message list

Whether your eyes are young or old having the ability to customize the font size within the message list of Outlook can be beneficial.  Between the Navigation Pane and Reading appears the message list.  The default setting is Seogoe UI regular 8-point.  Follow the 4 steps below to customize...

1.  Within Outlook mail, from the View menu, select Current View and click on Customize Current View. 

2.  From the Customize View: Messages dialog box, click on Other Settings.

3.   From the Other Settings dialog box, within the Column Headers and Rows, and click on Row Font.

4.  Select a font, font style, and size that you want to use. Click OK on the Font, Other Settings, and Customize View: Messages dialog boxes.

http://office.microsoft.com/en-us/outlook-help/change-the-font-or-font-size-of-the-message-list-HA010274437.aspx

SmartBoard - Changing the Pen Color

On Saturday, I worked a a wonderful group of Education students, it was early and I have to admit I wasn't quite awake yet.  But their energy drew me in, the questions flew out of each of them and before I knew it the hour had come and gone.  If this is a glimpse of what they are like as a student just imagine what it must be like to be a student in their classrooms.  The question was asked, " How do you change the Pen Color permanently"?  I was sure there was a way but never having done it before I looked up the answer.

From the Smart Board control panel, you can change the color of a pen, or change it permanently into a highlighter.

1.  From the System Tray in the lower right corner of the computer, right-click on the SMART Board icon and select Control Panel or from the Welcome Center, click on Control Panel icon.

2.  From the Smart Board Control Panel dialog box, select the Pen Tray tab and select a pen.

• Change the width
• Change the color
• Change the pen to a highlighter (transparent)

* Note you have to be connected to a SMART Board to change the colors of the pen tools and you will have to save the settings under your own User Profile, the Default Profile is (Read-only). 

Additional Resources:

• http://downloads01.smarttech.com/media/services/quickreferences/pdf/english/qrnb10welcomecenter.pdf
• http://classrooms.uiowa.edu/multimediaforms/SmartboardTipsAndWhy.pdf

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.  

Using the Levels Command - Photoshop

Need to lighten or darken a photo/image? Using the the Levels command is a simple way to adjust the image Input Levels.  Not a expert, no worries you don't have to fully understand how the Levels command works to obtain a good looking results.

1.  Open an image in Photoshop, press Command + L (Mac), Ctrl + L (Windows).

2.  From the Levels dialog box, drag the white Input Levels slider toward the left to lighten the image, drag the black Input Levels slider toward the left and click OK.

With slightly different results, use the Output Levels.  Drag the black Output Levels slider toward the right to lighten the image.  Drag the white Output Levels slider toward the left to darken the image. 

Try it out, have fun and see how easy it is to use the Levels command in Photoshop. I am sure that you will be pleased with the outcome.

A Time Saver, Quick Parts - Outlook 2007

How regularly do you use the same information to answer questions or reply  in your personal or professional email correspondences?
Quick Parts is available in both Outlook and Word 2007, re-use content easily, as many times as you like.  Like it's name, using Quick Parts can save you a lot of time when sending/replying email messages. In an attempt to keep control of your inbox, this tip may just be the one for you.

To Create/Save a Quick Part:

1.  From a new message, within the body of the message, select the text to be reused.

2.  From the Insert tab, within the Text group, from the Quick Parts drop down, select Save Selection to Quick Part Gallery.

3.  From the Create New Building Block dialog box, enter a name for the quick part and click on OK.

To use a Quick Part:

1.  Starting with a new message and within the body of the message. From the Insert tab, click on Quick Parts, a gallery/listing with all of your saved Quick Parts is displayed.  Click on the Quick Part and the content will be inserted at the existing cursor position.

• Right-clicking on the desired Quick Part will display more advanced options for insertion.
• Keyboard shortcut - enter/type the first three letters of the name of the quick part and press F3.

Give this a try, how many emails can you respond to in 2 minutes or less using Quick Parts? You will be surprised at how much time you save.

Text to Columns - Excel 2007

A worksheet containing data in one column; you may find it necessary to separate the cell contents into two columns. In the image below, you maybe required to separate the first and last names into two columns, when using the data for a mail merge.

1.  Select your data, in this example A2:A10

2.  From the Data tab, within the Data Tools group, click on Text to Columns.

Text to Columns button

3.   From the Convert Text to Columns Wizard dialog box, select Delimited and click on Next.

4.  From the Convert Text to Columns Wizard dialog box, select Space, clear tab check mark and click on Next.

5.  From the Convert Text to Columns Wizard dialog box, select Text, click on the second column of data, select Text and click on Finish.

In the image below, finished results show data moved last names into it's own column.

Excel is awesome- it helps you work smarter, not harder.  Can this tip help you?

Hello,

I post to a website, on Twitter, correspond via email and now I am blogging.  To begin my blogging journey, I will post a Tech Tip once a week, hoping end users will find the information relevant and beneficial.  I look forward to your comments and feedback.  Ask questions, learn "Tricks" that will help you become a tech savvy, confident employee and/or student at Fisher and at home.