Customize Toolbars

If you find yourself frequently going to a menu, submenu, and then a dropdown to make a selection for a feature; why not customize your toolbar to place that feature on it. You will then be able to select the feature with one click. Let's take a double underline as an example. To get to this feature in Word or Excel, you need to Go to the Format Menu and then select Cell or Font, then drop down the underline list and select the double underline style. To create a shortcut, place a button for double underline on your toolbar.

To customize your toolbar go to the Tools menu and select Customize. Go to the Commands tab, and in the Categories list on the left, select the category or menu which contains the command you want (for underlining, it would be the Format category). In the Commands list on the right, scroll until you see the command you want (double underline) and drag that command, with your mouse up to the toolbar you want to place it on, and in the spot you desire (next to the underline button on the Formatting toolbar makes sense, for this example). Close the Customize dialog box, and you will now have a button for double underlining on your toolbar.
return to top

Desktop Shortcuts

Do you need to go to the Start Menu, and select All Programs and then drag up and down your list of programs to find and open up the application you want to use? You can easily create a Shortcut on your Desktop to any program, folder, or file that you need quick access to. For a Shortcut to a program, locate the program in your All Programs list and use your LEFT mouse button to drag it to your Desktop. The Icon on your desktop will be a shortcut to that program, with a small black arrow in a white box on its lower left. You can move the shortcut and rename it, if you desire. Deleting the shortcut WILL NOT delete the program.

To create a Shortcut to a file or folder open your Windows Explorer (right click on the Start Menu and click on Explore) and make sure the window does not cover the entire Desktop (use restore button on upper right of window, if it does). Locate the folder or file you want quick access to and use your RIGHT mouse button to drag it to your Desktop. When you release the mouse you will see a short menu - make the choice "create shortcut here". Deleting the shortcut DOES NOT delete the file or folder.
return to top

Format Painter

How often do you need to format headings or subheadings throughout your Excel, Word or Powerpoint document? Instead of selecting and formatting each cell or piece of text with the attributes you need for your heading, use the format painter button on your standard toolbar. Select the word or text or cell that is formatted in the way you want and then click on the format painter button (looks like a paint brush). Next, select the text or click in the cell you want to apply the exact same format to. The character, number and paragraph formatting will be copied, but not the text itself. The format painting function will automatically turn off. If you need to copy formatting to more than one piece of text or cell, double click the format painter button, and then you can paint the format to as many other pieces of text as you like. To turn off the format painter, and return your cursor to normal function, click once more on the format painter button, or hit the ESC key.
return to top

Text Box

Sometimes we need to place text on a page that does not quite fit within a cell in Excel, or within the paragraph format of Word, or within the bulleted paragraphs of a PowerPoint slide. At times like this - you need the Text Box Tool.

In each of these programs you can display the Drawing Toolbar. If it is not already visible, just go to View Menu, Toolbars, and click on Drawing. On that Drawing toolbar is a tool which looks like a page of text wrapped around an A. It says Text Box in the popup tool tip when you point to it. Click on that tool, place your cursor on your document and drag to draw a box where you would like to place your text. There will be a cursor within the box - type your text and edit and format as usual. The Text Box can be moved anywhere on your page by pointing to its shadowed edge, and dragging. It can be resized by pointing to any corner or side handle, and dragging. You can turn off of the border around the text box by double clicking on its edge and getting a Format Text Box dialog box. In this box, in the Colors and Lines tab, select Line Color - No Line. You now have what I call "Free Floating Text" which you can move and size to any position on your document.
return to top

Excel Tips

Autofilter a List

Have you ever had a list of items in Excel (say a list of 200 employees) and you wished you could quickly see just the ones in the Accounting Department? Well, you can if the list is set up as a contiguous list with the first row containing headings for each column (field), and each row containing the information for one employee (record). Simply click your cursor anywhere in the list, go to the Data menu, select Filter, Autofilter. Next to each field heading, there will be a drop down arrow. If you click on the dropdown arrow next to Department, it will drop down a list of all the departments. If you click on Accounting, the list will be filtered, and will only show the records for the employees in the Accounting Department. Once you have finished with this department, you can filter for another department, or select All from the drop down, to show all your employees again. You can filter by as many fields as you need to, in order to narrow down the list to the group you need. Simply select Data, Filter, Autofilter again, to remove the filtering feature.
return to top

Autofill Automatically Down to the Right Cell

How many times have you wanted to copy a formula down which adds or multiplies across a row? Often we need to copy, or auto fill the formula down several hundred rows. Dragging the auto fill handle sometimes causes you to zoom down to row 10,000. If you point your cursor on the auto fill handle until you see the thin black cross, and double click, it will fill the formula down to where you have data to its left. Usually, this is exactly where you want to fill it!

Conditional Formatting

Have you ever wanted a quick, automatic way to have all the expenses over $10,000 stand out on your budget spreadsheet? Conditional Formatting allows you to do this with ease.

Select the range (or ranges) you would like to format based on one or more criteria. Then go to the Format Menu, Conditional Formatting. The dialog box asks you to enter the condition(s) you wish the cells to meet. These include: greater than, less than, equal to, not equal to, between, not between, etc. Let's say your condition is >10,000. Then select the Format button, and indicate if you would like those cells to have: bold, italic, color, border, fill. When you OK your selections, all cells in the range you indicated that contain a value greater than 10,000 will have the formatting you selected. You can set up to three conditions for the same range, by selecting Add , in the Conditional Formatting dialog box. For example, you might want all values in the range › 10,000 to be bold, all those ‹ 500 to be italic, and all those equal to 1,000 to display in dark blue.
return to top

Copy an Entire Sheet

Do you sometimes need to copy all the data on one sheet to a new sheet in an Excel file? Perhaps you are creating a sheet for a new product, month or sales representative, which is very similar to an existing sheet. When you copy by selecting the range of cells you want and doing a copy and then pasting to a new sheet, the data in the range is copied, but not the printing settings, not the column widths, etc. To copy everything from one sheet to a new sheet, in one easy step, select the sheet tab at the bottom of the screen, hold the Control key down, and drag the sheet tab to a new position. This will copy the sheet, and Excel will simply put add number to the sheet name. Rename the sheet, as you wish, and make whatever changed you need to on the copied sheet. ALL SETTINGS will be copied.
return to top

Create a Custom List

Do you have a list of item (ex: products, salesreps) that you need to repeat on many occasions in various Excel spreadsheets? You can easily create a custom list that would allow you to type in one item on your list and then drag down the autofill handle to fill in the rest of this list automatically. Just type out the list of items in a column in Excel, and select your list. Then go to the Tools menu, select Options, Custom Lists tab, and click the Import button on that dialog box. You will see your list in the list entries window. Click OK, and you are ready to roll. Type the first name on your list into any cell (in any Excel spreadsheet), grab the autofill handle (black box in lower right corner of selected cell) and drag down (or up, or right, or left). The items in your list will be arrayed in the order you listed them. This will work in any Excel spreadsheet you create on that PC, unless you delete the list you imported!
return to top

Create Charts with One Keystroke

Have you ever wanted to present data in a chart form and thought it was complicated to do so? Just select the range containing your row and column heading and leave out the totals. Press F11 (function key 11) and you will have a basic chart! The chart will be placed on a new sheet in your Excel workbook. You will see a Chart Toolbar which will allow you to make many adjustments to the chart. You will be in Microsoft's Graph program (same as in Powerpoint charting) the Menu will be different, and will have a Chart choice on it, which will also give you many charting options.
return to top

Formatting for Zip Codes

Many of us use Excel to keep lists with names and addresses. Usually, there will be a column for zip codes. When entering zip codes beginning with zero, as in CT and NJ, problems can arise. Excel sees the numbers as values and drops the leading zero. If you use Excel's "Special" number format for zip codes, the zeros will appear in your spreadsheet cells, but not in the formula bar, when you select one of those cells. Should you use this list for a mail merge, the zeros will not appear, and the post office has no sense of humor for 4 digit zip codes. What will work to maintain the leading zeros, in using Format, Cells, Text, on the column or cells you will be typing zip codes into. You must format for text before entering the zips. The zeros will then be maintained, and any mail merge you use the list for will have the correct zip codes. If you need to format for zeros after the leading zeros have been dropped, go into Format, Cells, Custom and type in a custom format of 5 zeros: 00000.
return to top

Group Sheets

Have you ever had a workbook with multiple similar sheets, and you decide you need to make a consistent change to all of them. For example: create a formula in cell G2 on all sheets, bold the title in A3 on all sheets, or make the font larger on the labels in row A. Did you know there is an easy way to apply the change to all sheets at once? All you need to do is group them first and make your change!

To group sheets, click on the sheet tab of the first sheet, hold Shift down and click on the tab of the last sheet. All sheets will be selected, and any change you make will apply to all selected sheets. You will see the word [Group] in the title bar. Once you are done making changes to all your sheets, just click on any sheet's tab and they will ungroup, or right click and select ungroup sheets.
return to top

Grand Totals - Automatically

Do you often have a long list of items in Excel with subtotals at various points in your list? Undoubtedly, you need a grand total at the very end of the list. If your data is set up in contiguous rows, you can select the row below your last subtotal, in which you would like the grand total to appear, and just click the AutoSum button. Excel will automatically add up just the subtotals! You can select one cell, if you only need a grand total for one column, or 10 cells in the row, if you need grand totals for 10 columns in your list.
return to top

Navigating to Sheets

Do you sometimes have a workbook with 12, 15 or 20 sheets? When moving from sheet to sheet does it take too much time to click on the navigation arrows on the bottom left of the screen to pull the name of the sheet into view? Then you need to click on the sheet you want to open it.

Try this instead: right click on one of the navigation arrows and a list of all the sheet names pops up. Click on the name of the sheet you want and it opens on your screen.
return to top

Selecting Contiguous Area

How many times have you wanted to select an area of 300 rows X 12 columns in your Excel spreadsheet, and when you started dragging down to select , you wound up in row 3,000? To prevent this aggravating and time consuming method of selecting, just click into your data area, and hold Control key while tapping the asterisk (on the numeric keypad). This will automatically select all the contiguous rows and columns of date, without any dragging.
return to top

Subtotals - Automatically

Do you often have a long list of items in Excel which you need to create subtotals for according to a specific grouping? For example, if this is a list of products with columns (fields) for product ID number (5421), manufacturer (HP), product category(printer), specific product (HP 5610), cost ($175), and number in inventory (10), Value of Inventory ($175 * 10), and you want to obtain a subtotal for the value of products by each manufacturer. This list must be laid out in contiguous rows and columns, with no blank rows or columns.

First sort the list by manufacturer by clicking in the manufacturer field and using the A-A button on your toolbar. Then go to the Data Menu and select Subtotals. At each change in - check manufacturer - since this is the category you want subtotals for. You want to use the Sum function, and you want to add subtotals to your Value of Inventory field. Just click OK, and your list will be subtotaled and labeled by each manufacturer, with a grand total at the bottom.

Best of all, you will have an Outline panel on the left of the screen, with 3 levels of information indicated. If you click the 2 at the top of the panel, you will see only the subtotals and grand totals, which you can print, copy, sort, or format.
return to top

Watch Window

How many times have you made changes to a Spreadsheet, and wished you could see how those changes effect numbers in a different sheet or another location in the same sheet. Well, starting with the XP (2002) version of excel, there is a feature called "Watch Window" which lets you do just that. In the tools menu, select Formula Auditing, and Show Watch Window. If you open the Formula Auditing Toolbar, there is a button for Watch Window. After the window is open, simply click on the cell containing the date you want to "watch" as you make changes, and click on the Add Watch button at the top of the window. You can continue to select cells or ranges and click the add Watch button to add them to the window. You can then move to anywhere in the workbook, make changes to your data, and see them reflected in the Watch Window. Simply close the window when you are done "watching".
return to top

Outlook Tips

Categories in Outlook

Outlook is an email and contact management program that has a huge following. If you are using Microsoft Outlook, are you aware that you can categorize your contacts, for example: prospects, clients, personal, hot leads, etc. In order to do this, in each contact's card, there is a category button on the lower right of the form. Outlook has many categories already set up that you can choose from, but you can go to "master category list" and add your own categories, and delete the ones that are not relevant. You can create as many categories as you like. A contact can belong to more than one category.

You can then go to the View Menu, and select Arrange by, Category, and your contacts will be organized by category, and you can expand or collapse each category as you choose. Displaying the Advanced Toolbar (with many useful shortcuts on it) will enable you to select the Categories View from a dropdown for current views. You may also want to customize views in order to show categories on the address card view, phone list view, or any custom view you create. Categories can also be assigned to tasks and calendar items. Categories are useful to keep track of items all relating to a specific project or topic. When customizing a view, use the Filter button and More Choices tab to set up a filter by category. Categories can also be used with a Word mail merge to send a mailing to a subgroup of your contacts.
return to top

Forward Contact(s)

Have you ever needed to send a colleague or friend one or more of your contacts? To quickly send one contact card (vcard) simple right click on it, and select Forward. This will create an email with that contact card attached. To select multiple vcards, in either address card view or Phone List view, select one, hold Control key and select the other contacts you want to send. Once the contacts you want are selected, right click on any of them and choose Forward - this will create an email with the selected vcards attached.

The recipient can simple click on the vcard attachment(s), and that contact will be added to their contact list.
return to top

Phone List View

Outlook is an email and contact management program that has a huge following. If you are using Microsoft Outlook, are you aware that you can change the contact view from address cards to phone list? This provides you with a spreadsheet like view with each contact in one row, and each column containing one type of information.

To get this view, go to the View Menu, point to Current View, and a menu of choices with appear. Select Phone List. One of the best things about this view is the ease with which you can then sort your list by any of the columns of information. For instance, the default sort is by "file as" which is the last name of the contact. What if don't remember the last name, but you know the company or first name. Simple click on the heading that says "company" or "full name" and the list will immediately sort by that field.
return to top

Search Folders Outlook 2003

Have you ever searched through many folders, including sent and deleted, as well as inbox, to find all the email correspondence with a particular contact? Instead of doing this manually, Outlook provides an easy way to look at all items to, from or both, regarding a particular correspondent. Simply create a Search Folder, by clicking on the drop down arrow next to NEW on your toolbar, and selecting Search Folder. You then select the category of mail you want in the folder, such as "mail from and to specific people". Then you click on the Choose button, and select the person from your address list. Once you select the person and OK, a search folder appears at the bottom of your folder list with that person's name. A virtual copy of all email from any folder, to or from that person, appears in the search folder. None of the emails are moved to the folder, but you can select and open them from that location. You can delete the search folder at any time, and the emails are not deleted, but remain in their original folders.

Another method to quickly list all related emails is to right click on a closed message and Find All, Related Messages (or Messages from Sender).
return to top

Word Tips

Compare and Merge Documents

Do you sometimes receive edited documents? If these documents have been marked up by different editors, how do you merge them together so you can see all suggested edits in one place?

Word allows you to do this effortlessly with their Compare and Merge feature. Open your original version of the document. Then go to the Tools Menu and select Compare and Merge Documents. Locate the first file you want merged into your document (which has been edited with Track Changes). Select that file, and use the drip-down list to verify that Find Formatting is checked. Uncheck Legal Blackline. Then, from the Merge button drop-down list, select Merge into Current Document. You will see the editing changes added to your document. You can now use Tools, Compare and Merge Documents to add another document, with it's edits to your original.

You can add many documents, which will mean editing comments from many editors. To change the comment colors so they are different for each editor, use the Track Changes tab in the Options dialog box. Under Markup, from the Comments Color drop-down list, select By Author.
return to top

Thesaurus

Did you know that Word has a very powerful built in Thesaurus? Just click into (or select) a word that you would like a synonym for, and go to the Tools menu, select language, and then Thesaurus from the fly out menu. You can also use Shift-F7 as a shortcut to the thesaurus. The task pane will open on the right side of the screen with the word you selected will be in the "Search for" box, and alternate words listed in the thesaurus box. There may be no words or many listed under different parts of speech (adv. Adj. verb, etc.) depending upon the word you are looking up. If you locate a word that is closer to the meaning you want, but not quite there, you can click next to it (drop down arrow) and ask Word to look up that term. In older versions of Word, instead of a task pane, you will get a dialog box to look up synonyms.
return to top

Track Changes

Do you sometimes need to edit a document that is sent to you? How about marking up a document to send back to the writer? You can easily accept or reject changes made in a document.

Word allows you to do this effortlessly with their Track Changes feature. To edit a document and have your edits, deletions and insertions clearly marked, without making permanent changes to the document, go to the Tools menu and select Track Changes. Whenever you delete text, it shows with a line through it in color. Whenever you insert text, it shows in a different color. When you replace text, it shows the inserted and replaced text in the appropriate colors for deletion and insertion. You can control the colors used by going to Tools, Options and selecting the Track Changes tab.

A black line also shows in the margin next to text that has been changed. Once you engage track changes, you will get a Reviewing Toolbar with buttons that will allow you to add comments (which appear in a bubble in the margin), A drop-down box in which you can choose to see the original document, the original with markup, or the final. You also have buttons allowing you to go through the document accepting or rejecting each change, or accepting/rejecting all changes at once.
return to top

Translate Word or Document

Do you ever need to translate a word, paragraph or document into a foreign language? Did you know that MS Word has an easy tool to help you do this? Select the word you need to translate, open the Tools Menu, select Language, then Translate. The Research task pane will open up. You can select which language you are translating from and to, and if the word does not automatically appear in the Search for box, simply copy and paste it there. Click the green arrow next to the search for box and you will get a bilingual dictionary listing, with definitions, parts of speech, and the translated word. If the word you are trying to translate is not found, other research sites will usually be suggested.

If you need to translate an entire document, do not select a word. Go into the Research task pane and in the Translation box, there is a green arrow and directions to "Send document for translation over the Internet in unencrypted HTML format". Click the Green arrow and the entire document is translated.
return to top