Tips and Tricks

Google has become a part of my everyday computer usage and I am regularly telling people that if you need particular information, just "google" it.

My frequent use of Google prompted me to research how to use it effectively. Because I found so many things that you can do, I created a half-day seminar, Introduction to Internet Search (or How to Google) .

Below are a few of the highlights of the seminar that may be of help to you.

Searching for a Range of Numbers or Dates

If you wish to find items that fall within a range of numbers or dates, Google allows you to express the range within your search by listing the first item, two periods (no spaces), and the last item. For example to find information of the Boston Red sox teams the years 1920 through 1925, type the following in the search box:

"Boston Red Sox" 1920..1925

Definitions

To see a definition for a word or phrase, simply type the word "define," then a space, and then the word(s) you want defined. If Google has seen a definition for the word or phrase on the Web, it will retrieve that information and display it at the top of your search results. For example, to view the definition of "onomatopoeia", type the following in the search box:

define onomatopoeia

Calculator

To use Google's built-in calculator function, simply enter the calculation you'd like done into the search box and hit the Enter key or click on the Google Search button. The calculator can solve math problems involving basic arithmetic, more complicated math, units of measure and conversions, and physical constants. For basic math, use the same operators for arithmetic functions that you use in Excel. For example, to total the numbers 18 and 50, type the following in the search box:

18+50

Return to top of page

One of the great features that exists only in Access is that you can choose to send an object automatically to a specific printer instead of the default printer. In every other Office product, you must first go to the print menu and then choose the printer. After printing the item, you must then remember to go to the print menu and switch it back to your default printer. In Access, however, once you have created an object, you can go to File – Page Setup, click on the Page tab, click on the Use Specific Printer option button and then choose the desired printer. Then save the object. After that, the object will always be sent to the specified printer.

Return to top of page

Have you ever wanted to place instructions on a worksheet to help your users and not have those instructions print? Follow these easy steps to do this:

1. Open the Drawing toolbar. (There is a Drawing Toolbar button on the Standard toolbar.)
   
   
2. Click on the Text Box tool (The box with the small A and lines in it.) and drag a rectangle at the location you wish the instructions to appear on the worksheet.
   
   
3. Type the desired text in the text box.
   
   
4. Right-click on the edge of the text box and choose Format Text Box.
   
   
5. In the Format Text Box dialog box, go to the Properties tab, remove the checkmark from Print object and choose OK.
   
   

That's it. The text box will not print. You can edit the content, resize the box, move the box, etc. Treat it just like any other text box.

Return to top of page

I’m not a big proponent of shortcut keys. I’d rather use tools on the toolbars, menu options, or shortcut menus than memorize key combinations. But there are some shortcut keys I find very helpful.

I know that Top 10 lists are more prevalent than Top 11 lists, but I couldn’t eliminate a particular item.

Return to top of page

Just when you think you know a product pretty well, you stumble across information that shows you how little you really know.

If you work with the database features of Excel, you are probably familiar with using the wild card characters * and ? when setting criteria:

• * means any group of characters
  
• ? means any single character

These characters can also be used within the sheet portion of cell addresses within formulas.

•  To sum the same cell, for example cell B5, on all of the sheets except the current sheet type:
  
          =SUM('*'!B5)
  
  
•  To sum the same cell on all sheets whose name begins with “M”, such as Mary Smith, Mark Jones, Marissa Harris, type:
  
          =SUM('M*'!B5)
  
  
•  To sum the same cell on all sheets whose name begins with the characters “Category”, such as Category 1, Category 2, Category 3, etc., type:
  
          =SUM('Category*'!B5)
  
  
• To sum the same cell on all sheets whose name ends with “Quarter Summary”, such as 1st Quarter Summary, 2nd Quarter Summary, 3rd Quarter Summary, 4th Quarter Summary, type:
  
          =SUM('*Quarter Summary'!B5)
  
  
•  To sum the same cell on all sheets whose name begins with the characters “Category” followed by a space and a single character, such as Category 1, Category 2, Category 3, etc., but not Category 10, Category 11, etc, type:
  
          =SUM('Category ?'!B5)
  

These same techniques can be used in any of the other statistical functions, such as AVERAGE, COUNT, COUNTA, MIN, MAX, etc.

One thing you should be aware of is that when you enter the formulas above into a cell, Excel converts the wildcard to the appropriate sheet names.  So if you add another sheet with a similar name in the future, Excel will not automatically include that sheet in the calculation.  For example, if you have 3 sheets named Category 1, Category 2, and Category 3 when you enter the formula =SUM('Category*'!B5), Excel converts the formula to
=SUM('Category 1:Category 3'!B5).  If you later insert a sheet named Category 4 to the right of Category 3, you have to edit the formula to include that new sheet in the calculation.  If you insert any sheets between Category 1, Category 2, and Category 3, those sheets will be included in the calculation, no matter what they are named.

Also, Excel will write the formula based upon the location of the sheets when you enter the formula.  For example, if the sheets Category 1, Category 2, and Category 3 are located next to each other in that specific order, Excel will convert the formula =SUM('Category*'!B5) to =SUM('Category 1:Category 3'!B5).  But if the sheets Category 1, Category 2, and Category 3 are not located next to each other and have other sheets between them, Excel will convert the formula =SUM('Category*'!B5) to =SUM('Category 1'!B5,'Category 2'!B5,'Category 3'!B5).

Return to top of page

Office 2007, the newest version of Microsoft Office, contains major revisions to the way you do things. Gone are the menus that traditionally resided at the top of each screen. The menus are replaced by tabbed Ribbons and the tabs for the ribbons do not correspond to the menu options in the prior versions. The File option is replaced by a circular button on the left side of the ribbons called the Office Button.

Below is a view of Excel’s Home ribbon.

The words listed at the top of the illustration are more ribbons. And those aren’t the only ribbons! As you work in specialized objects, such as charts, additional ribbons will appear.

Another major difference in Office 2007 is that all of the Office products use a new file format, based upon XML. The traditional office file extensions have an "x" added to the end of them (such as .xlsx and .docx) to designate the new format. This new file format promises to make our data more portable. But, if you create a file in any Office 2007 product and save it in the new file format, which is the default option, people using earlier versions of Office will not be able to open the file. You can always do a Save As and save the file in Office 97 – 2000 format. You can also place the products into "compatibility mode," which defaults to using the Office 97 – 2000 format.

When running in compatibility mode, some of the new features are removed. For example, Excel 2007 worksheets contain 1,048,576 rows by 16,384 columns. If you save a workbook in Office 97 – 2000 format or you operate in compatibility mode, the number of available rows and columns are automatically reduced to the previous standard of 65,536 rows by 256 columns. If you save a file in a previous format, Office will warn you that you may lose some features of 2007 and often will list the features that will be stripped out of the file, which is nice.

Office 2007 does incorporate many slick, new improvements that I actually love. But I did find the interface changes to be overwhelming when I first saw them. And the file compatibility issue is something that every Office 2007 user must be made aware of.

Return to top of page

One of the features in Microsoft Office 2002(XP) and 2003 that causes joy and annoyance in equal amounts is AutoCorrect. For example, if you type adn and hit the space bar, the Office products will automatically correct that to and which is great unless you actually want to have the letters adn appear in your file. If you do wish to have it stay as adn , in Access, Word, and PowerPoint, you only need to do undo (Edit - Undo, click on the Undo tool or press CTRL+Z) and it will revert to adn. In Excel, undo blanks out the cell. Also in Access, Word, and PowerPoint, if you hover the mouse pointer over an autocorrection, a Smart Tag appears. If you click on the Smart Tag, you can select options to undo the automatic correction or to stop it from performing that particular autocorrection in the future. By choosing Tools – AutoCorrect Options, you can change the way AutoCorrect works, delete AutoCorrect entries and add your own AutoCorrect entries.

AutoCorrect does exist in some of the Office products in versions of Office prior to Office 2002(XP) and some of the information above will also apply to these earlier versions.

Return to top of page

One of the fun things about working with computers is that you can always learn new things and you never know how you will learn them. I often learn things directly or indirectly from students, as was the case in this instance.

Before today, if you had asked me if there is a way to resize dialog box windows, my answer would have been a definite and confident "no."

In preparing for today's Excel seminar, I was testing all of the classroom machines to make sure that everything was working properly. When I opened the Open dialog box on one of the computers, it was a maximized window, filling the entire screen. I was baffled! How did that happen?

After a few minutes of bewilderment, I discovered that you can right-click on the title bar of many dialog boxes and receive a control menu that contains maximize and restore options. It doesn't work in every dialog box, but does in many. And once you choose the option for a particular dialog box, the program remembers it for the future.

The last student who used that PC knew that! Now I do, too. And so do you!

Return to top of page

I love the date shortcut keys that you can use in any date field within QuickBooks! In fact, I love them so much, I have programmed them into Access forms for some databases I have created.

Return to top of page