TechBits

Freezing panes in Excel

I made peace with a monster spreadsheet recently with this tip to freeze the header row and column at the same time to keep them both visible while scrolling.

Place the cursor in a cell below and to the right of the row and column you want to freeze, then select View > Freeze Panes > Freeze Panes.

The header row and column stay frozen on screen alongside those far out cells after scrolling.

Thanks to Kerri for teaching me this tip just when I needed it!

Zoom, zoom, zoooooooooom

Zoom in MS Office
Did you know you can quickly zoom in or out of a document (Word), presentation (PowerPoint), or worksheet (Excel)? 

 

• On the status bar in the lower right, click the Zoom slider
• Slide to the percentage zoom setting that you want

 
Zoom in IE 

Want to quickly zoom in IE?
<--- There's a zoom menu on the status bar...

And if you prefer keyboard commands, don't forget

Ctrl +  (zoom in)

Ctrl -  (zoom out)

They work in most browsers!

How to use autocomplete in Word 2007

Do you get tired of typing the same string of characters over and over in a Word document?  For instance, since I work for the South Central Library System I may have to type that several times in a document or several documents. Autocomplete is turned off by default in Office 2007. Here are the steps I have used to get around that.

 In Word highlight the string of text you wish to have autocomplete. Press Alt F3, that will bring up the "Create New Building Block" dialog box.

Fill out the information in the Create New Building Block dialog box:

Name: This is filled out automatically with the text you highlighted.

Gallery: Select the gallery that you want the building block to show up in.

Category: Select a category, or create a new category.

Description: Type a description of the building block.

Save in: Click the name of the template in the drop-down list. A template must be open to be displayed in the drop-down list of template names. I use the default "Building Blocks.dotx".

Options: Choose one of the following:

    Select Insert content only. This is selected by default and is the one I use.

    Select Insert content in its own page.

    Select Insert content in its own paragraph.   

 That's how you create an Autocomplete template. To use it start typing your string of characters, usually you only need the first two to four to start and hit the F3 key and the rest will autocomplete. This will work only if there are no other entries that begin with the same four letters, so be aware of that.



Learn Free

GCFLearnFree.org is supported by the Goodwill Community Foundation.  According to their website, they "create and provide quality, innovative online learning opportunities for anyone who wants to improve the technology, literacy, and math skills necessary for them to be successful in both work and life."

What will you find there?  Tutorials.  Lots and lots of tutorials, some videos, and even online classes!

Here's a peek at the main technology topics (click on the image to enlarge it)...

There are tutorials for multiple versions of the Office products -- 2000 all the way up through 2010.  Social Media offerings include Facebook, Skype, Twitter, and Google.

I haven't run through any of the tutorials yet, but I thought they might be a nice possibility for patron training.  Each of the many lessons also offers a printable version.  As an example, here are links to the lesson and the printable version of the lesson on Mail Merge for Word 2007.

Opening Two Excel Files Side-by-Side

Did you ever need to look at two Excel documents at the same time? By default Excel opens both documents in the same window. This makes it impossible to see both documents at the same time.

The secret to getting this to work is to open another instance of Excel before you open the second Excel document.  In order to do this you need to:

1. Open first Excel document by double-clicking on it
2. Open another instance of Excel:
   1. Use the mouse to
      1. Click Start -> Programs -> Microsoft Office -> Excel
   2. Use the keyboard shortcut of
      1. Hit WIN+R
      2. Type Excel
      3. Hit Enter
3. Then double-click on your second Excel document

You will now have two instances of Excel open and each will contain a different Excel document.  You can now resize each Excel window in order to have them side-by-side for comparison.

Thanks Cindy for showing me this trick!

 

Excel Tips

Here are some quick tips for working with Excel:

Clearing the Print Area:

In Excel 2007

1. Click anywhere on the worksheet for which you want to clear the print area.
2. On the Page Layout tab, in the Page Setup group, click Clear Print Area.

In Excel 2003

1. Choose File > Print Area > Clear Print Area from the menu.

How to Hide a Column:

In Excel 2007

1. Select the column(s) you want to hide. 
2. On the Home command tab, in the Cells group click Format.
3. From the Format menu, in the Visibility section, select Hide & Unhide.
4. Select Hide Columns.

In Excel 2003

1. Select the column(s) you want to hide.
2. On the Format menu, point to Column, and then click Hide.

To hide a row in either Excel 2007 or Excel 2003, use the same steps except substitute row for column.

How to Unhide a Column:

In Excel 2007

1. Select at least one cell from both of the column(s) around the hidden column(s).
   EXAMPLE: If column B is hidden, select a cell from both columns A and C.
2. On the Home command tab, in the Cells group, click Format.
3. From the Format menu, in the Visibility section, select Hide & Unhide.
4. Select Unhide Columns.

In Excel 2003

1. Select at least one cell from both of the column(s) around the hidden column(s).
   EXAMPLE: If column B is hidden, select a cell from both columns A and C.
2. On the Format menu, point to Column, and then click Unhide.

To unhide a row in either Excel 2007 or Excel 2003, use the same steps except substitute row for column.

More Tips for both Excel 2007 and Excel 2003:

Quick Sum of a Column
Hold down the Alt key while you type the equal sign. Excel will automatically write a SUM formula for the cells above or to the left.

Some Keyboard Shortcuts

Ctrl+;(semicolon) - Inserts current date

Ctrl+shift+:(colon) - Inserts current time

Ctrl+Page Down - Move to the next worksheet

Ctrl+Page Up -  Move to the previous worksheet

Ctrl+Home - Go to upper left most cell (usually A1)

Ctrl+End - Go to the bottom right corner of used cells

Ctrl+1 - Opens the format cell window

Ctrl+s - Saves workbook

Ctrl+z - Undo (My personal favorite!)

For even more shortcuts, open up Help in Excel and search for the word "shortcuts".

Format Painter

Let's say you have some beautifully formatted text.  Then you add some text that isn't so beautifully formatted.  How can you easily copy the formatting from the original text to the new text?

Enter "Format Painter".

Available in Microsoft Office programs, Format Painter offers you a quick way to copy formatting from one item to another.

How does it work?

1. Select the text whose formatting you want to copy
2. On the toolbar, click the Format Painter icon
3. Select the text you want to apply the formatting to  ("paint it"!)
4. The text will take on the new formatting.

(click on the image to enlarge)

Want to see this in action?  Microsoft has a handy video!

 

 

 

Excel: Select all with one click!

What is the easiest way to select an entire worksheet?  A picture's worth a thousand words...

 
Source: TechRepublic's "10 Obscure Excel tricks that can expedite common chores"

 

 

Use Alt + F9 to Find and Replace URLs in Word

Last week I was working along merrily in Microsoft Word, using Find and Replace to change URLs in a document, until I realized that I was only changing the link's text... not the URL the link would go to when clicked. In Word, a link's text and its destination URL have to be changed separately if you are using Find and Replace!

I was worried I was going to have to start over and change each URL by hand, so I Googled up a solution: Alt + F9 is the keystroke to show Field Codes, including destination URLs. After hitting Alt + F9, the destination URL for each link was editable through Find and Replace.

Several Find and Replaces later, I hit Alt + F9 to toggle back to having the destination URLs hidden, and moused over my links to double-check that each link's text matched its destination. Success!

Line Spacing in Word 2007

Have you experienced any of the following line spacing issues with Word 2007?

• Increased spacing between paragraphs
• More space than expected between lines in a paragraph
• Unexpected spacing on a page

The reason for the change from what it was in Word 2003 is, as Microsoft puts it is "to introduce more white space in blocks of text, which makes the text easier to read."  The specific changes that they made were to the default line spacing which went from 1 to 1.5 and to spacing after a paragraph which went from 0 pt to 10 pt.

You can change these settings back to the way it was in Word 2003 by performing the following steps:

1. On the Ribbon (which is the bar across the top) select the Home tab.
2. Find the Paragraph section
3. Click on the little arrow that points down and to the right.
4. In the Paragraph Dialog Box that opens find the Spacing section.
5. Change the Line Spacing to Single by clicking on the down arrow.
6. Change the After spacing to 0 by clicking on the down arrow until it gets to 0.
7. Then click on the Default button at the bottom of the Paragraph Dialog Box.
8. You will then see a Window that talks about verifying that you want the changes you made to affect the NORMAL template. Click the Yes button.
9. Then either close out of Word and open it back up again or create a new document in order to start using your changes.

All new Word documents that you create from now on will use these paragraph changes.  You can do the same thing with the font by following the same steps as outlined above, but clicking on the arrow in the Font section.

Some quick line spacing changes that work in Word 2003 and 2007:

• To change some text to single-spaced first highlight the text and then hold down Ctrl and hit 1.
• To change some text to double-spaced first highlight the text and then hold down Ctrl and hit 2.