Date: April 12th, 2007

Author: Susan Harkins

Excel is so powerful that nobody knows everything about it. If you use Excel frequently, you probably run into situations, from entering data to calculating complex workbooks, that slow you down. The following tips won’t solve unique problems, but they will help you complete common tasks more efficiently.

This entry originally appeared as an article and as a PDF download. We’re presenting it here as well so that we can build a “10 things” archive.

#1: Exploit defined names

Defined names aren’t just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

#2: Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell’s fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn’t always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that’s the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you’ve highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you’ve completed the copy task. Press Esc to clear the selection of the source cell.

#3: Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don’t change the file’s name; you’re just updating it. (It’s a good idea to create a copy of the original Book.xlt so you can revert to Excel’s original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet’s default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don’t have one or both files, simply create your own. Just be sure to save them in Excel’s XLStart folder (\Program Files\Microsoft Office\XLStart).

#4: Enter repetitive data quickly

Tip #2 shows you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.

#5: Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you’ve completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don’t have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

#6: Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won’t always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they’re far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer’s default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After viagra low cost Enter check box (if necessary) and then choose a direction from the option’s drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

#7: Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet’s last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don’t inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet’s used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

#8: View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there’s a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you’re finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

#9: Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control’s drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file’s pathname:

where sheetname is the sheet’s name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

#10: Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don’t usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

by Scott Lowe MCSE | Apr 05, 2006 7:08:00 PM

Takeaway: For a few simple form letters, Microsoft Word's internal address book may be all you need when using the built-in mail merge features. However, the true power of Word's mail merge is only revealed after you integrate it with the more advanced database features of Microsoft Excel. In this How do I… article Scott Lowe shows you how to integrate Excel data when using Word's mail merge feature.

But, how many of you actually keep all of your contact information in Word? If you keep it in any Office program, you probably keep it in Excel, or in Access. Or, if you keep it in some other system, it's generally an easy process to get, for example, customer address information into Excel. In this article, I will show you how to make use of Excel data when using Word's mail merge feature.

I'm assuming that you know how to enter data into Excel and understand the purpose of Word's mail merge feature. If you need a refresher on either of these topics, take a look at previous articles in this series. I will be repeating some of the mail merge information from my previous article in this article in the interest of completeness. However, if you're interested in Word mail merge, you should read that article before you read this one.

Inventory sheets

For this article, rather than the typical form letter, we'll use an existing Excel used vehicle inventory to create inventory sheets that can be posted on each vehicle on the lot. Here's a snapshot of the Excel inventory table.

Figure A

We'll use this inventory table for the examples in this article.

Note the column headings in Figure A. Every column has some kind of heading. While this isn't absolutely required, I do highly recommend that you assign a column heading to every column in your spreadsheet. Word will use these column headings as field names. If you fail to provide a column heading, you'll get things like "Column A", "Column B", which can be rather confusing. "Leather seats" and "Sunroof" are much more descriptive.

The Word mail merge process

To get started, open a new document in Word and choose Tools | Letters and Mailings | Mail viagra brand cheap Merge from the menu bar. In the sidebar area, Word opens a Mail Merge helper that provides you with a wizard-like interface that walks you through the process.

Step 1: Select document type

Word's mail merge can be used to create form letters, e-mail messages, envelopes, labels, or directories. With a little imagination, these choices let you create just about any kind of document or communication you would need.

Figure B

Mail merge step 1: Determine your document type

Feel free to experiment with the different document types. For this example, we'll be creating an inventory sheet using Excel data. The best option to choose for this kind of mail merge is Letters, so I'll choose that option from the list of selections. When you've made your selection, click "Next: Starting Document".

Step 2: Select starting document

Any document you've created can be converted into a form letter. Or, if you want to start from scratch, you can use the current document (which, for me, is a blank document) and type your letter. You can also use any document template on your computer, or on Office Online, Microsoft's Web repository that contains hundreds of templates available for download.

This starting document is often referred to as the "master document" as well. The terms are interchangeable, although other Word features also use the term "master document", so be sure to keep things straight.

The starting document is the document that will ultimately contain the base form letter.

I'm going to use the blank document I have on the screen as the starting document.

Figure C

Mail merge step 2: Choose the starting document.

Once you've decided on the document that gets the honor of being the starting document, click the "Next: Select recipients" option. Notice that the wizard also allows you to go back to the previous step if you need to.

In step 4 of the wizard, you will be able to modify your letter.

Step 3: Select recipients

In the previous step, you chose what document to use as a form letter. In this step, you need to decide to which recipients you want to send the form letter. Word's mail merge feature lets you use a wide variety of data sources. In this case, we'll use an existing list. Click the Browse button to look for the Excel file you want to use for your mail merge.

Figure D

Mail merge step 3: Tell Word from where you intend to get your recipients.

The Browse button opens a window called "Select Data Source" from which you can connect Word to just about any database you like. From this window, choose your Excel inventory file and click the Open button.

Figure E

Select your Excel data file and click Open.

Most people are not familiar with "Named Ranges" in Excel (I'll cover them in a future article), so the resulting window might be a little confusing at first since it looks like gibberish. Notice that three of the four choices are the names of the individual sheets (Sheet1, Sheet2 and Sheet3). In my Excel file, the inventory information is on "Sheet1", so I will choose the corresponding option from the Select Table window. Note also the "First row of data contains column headers" option at the bottom of this window. As I mentioned before, it's really a good idea if you include headers in your spreadsheet. However, if you don't want to do so, make sure you uncheck this box. If you don't, Word will assume that the first row of your data contains headings.

Figure F

Select the sheet on which your information resides.

On the resulting window—the Mail Merge Recipients window—are a number of options that let you control what records you want to include in your merge. Note the checkbox to the left of each record. The check in the box indicates that this record will be included in your merge. If you don't want to include a particular record, click the box to remove the checkmark. You can also filter the records that you want to include in the merge by using the down arrows next to each heading. The buttons at the bottom of the window let you find specific records (Find), quickly select and deselect all of the records (Select All, Clear All), make sure that addresses are valid (Validate) and more. Click OK when you're done. Figure G below shows you the Mail Merge Recipients window while Figure H shows you your Word sidebar after you're done deciding which records to import.

Figure G

Which records do you want to include in your mail merge?

Figure H

If you want to make a change to your list, click the "Edit recipient list" option.

Step 4: Write your letter

Ok – this one isn't a letter (it's a form to slap onto the cars in our inventory), but Word doesn't know that. To Word, everything is a letter! On this step, you have the Word sidebar shown below in Figure I.

Figure I

The "More items" link is most appropriate for this example.

On this step, create your inventory sheet/flyer as your normally would. However, instead of typing in the individual inventory information, use fields instead. In this way, you're keeping your letter generic and letting Word do the work. You should also see a new toolbar added to your tool arsenal.

I'm not going to go over every option on the toolbar in this article, but will provide details on which buttons you need for most mail merge operations.

The first three buttons are pretty important. In order from left to right:

• Main document setup: This is the same as step one of the wizard in that you select the type of document you intend to create.
• Open data source: Again, this button loosely matches a step in the wizard; in this case, Step 2. When you click this button, Word opens up a dialog window asking you to choose the data file you want to use. If you want to connect to a different kind of source, such as an Access database, click the New Source button.
• Mail merge recipients: This button opens the window you saw in the previous step where you can decide which recipients should be included in the final output.

Now, we'll on to making the appropriate changes to your letter to make it generic. Notice in the sidebar, now entitled "Write your letter", there are a number of options, including "Greeting line", "Postal bar code", and "More Items…"

Figure J

The mail merge toolbar makes using mail merge a little easier.

Take particular note of the sixth button from the left. I've highlighted it for you in Figure J. Click this button to open the Insert Merge Field window. Alternatively, you can click the "More Items…" option in the sidebar. Either way you do it, Word opens up a window from which you can select fields from Excel to add to your flyer. As you're typing your flyer, use this window to add a field where you would normally put in specific information about a vehicle. For example, rather than typing in the price of a vehicle, insert the "Price" field instead. Figure K shows you what my sample flyer looks like.

Figure K

My sample letter, with merge codes.

Bear in mind that you need to put spaces, commas and tabs where they belong. In the previous article on this topic, we used address fields, but still had to manually put the comma between the city and the state fields.

Click "Next: Preview your letters" when you're done with your flyer.

Step 5: Preview your letters

With your flyer crated, it's time to see the results. From the sidebar, click the "Next: Preview your letters" option. The result should look similar to what you see below in Figure L.

Figure L

Here's a preview of the flyer.

One thing you may notice right away is the formatting of the price is wrong. There is no dollar sign or comma where it belongs. To fix this, click the "Previous: Write your letter" option in the task pane. This will take you back to the "write your letter" step of the wizard.

Figure M

Use the "Previous" option to go back to the previous step.

Now, click somewhere in the Price field and press Alt + F9 on your keyboard, or, right-click the field and choose "Toggle Field Codes". Either way, you will get the result shown below in Figure N.

Figure N

The fields look a little different here. This is what the full field code looks like behind the scenes.

Note, if you use the Alt+F9 option, you will always get the field codes for all of the fields. If you right-click a field and choose Toggle Field Codes, you will get the field codes for just that field. It doesn't hurt to show the field codes, but, as you can probably see, it can muddy up your document.

In these field codes, we want to change the format so that it comes out as currency rather than as a plain number. To do so, we need to type in a little code, such as "{ MERGEFIELD "Price" \# $#,### }" The "\# $#,###" portion of this stuff tells Word that we want to format this field as a number with a dollar sign followed by # signs indicating where numbers from the price will go. Also note the inclusion of the comma. Don't worry that there is only a single # sign between the dollar sign and the comma. Word knows enough to out multiple digits in that space for amounts larger than or equal to $10,000. Look at Figure O. When you're done, click the "Next" option to go to the "Preview Your Letters" step again.

Figure O

The field codes are used to make granular changes to the field.

Now, take a look at Figure P below. This time, the price of the car is properly formatted and will look a lot more professional. Choose the "Next: Complete the merge" option.

Figure P

The result: A properly formatted dollar amount.

Step 6: Complete the merge

This part is easy! Your sidebar again changes to match the step you're on and looks a whole lot like what you see below (Figure Q).

Figure Q

Step 6: Finish your mail merge.

At this point, Word has not officially performed the merge. That is, your letter and data file are still mostly separate. When you click one of the two options—(1) Print; or (2) Edit individual letters—Word asks you what you want to merge.

Figure R

What records do you want to have printed?

Your options here are to merge all of your records, in which case you will get one page per recipient, or choose which records you want to include. When you choose the print option, Word sends your flyer right to your printer. If you made a mistake, you have to reprint the flyers. By choosing the "Edit individual letters" option, you make Word create a huge document that includes every letter just as if you'd typed the letter manually for each recipient. I usually prefer to use the latter option so I don't have to reprint things.

Harness the power

Now, you've seen Word's mail merge in action using its own limited database and with its ability to harness Excel's power. You've also seen two examples of what mail merge can do: create simple letters, or create non-standard documents such as flyers.

Date: January 19th, 2008

Author: Susan Harkins

We tend to view spreadsheet data as a whole, and that’s as it should be. Seldom does a single column of values mean much to us without some related data to define it. Together, seemingly useless values become information. In fact, that’s the definition of data and information. Information is an aggregate — a collection of data that’s greater than the sum of its parts.

Occasionally, the big picture gets in the way. That happens when you need to view a subset of the data. Actually, viewing that subset isn’t the problem, but manipulating it can be. For instance, suppose you want to sort a unique list of cities, countries, ZIP codes, and so on. A simple change of perspective is all you really need.

Copy the single column or subset to an out of the way spot so you can sort it separately from the source spreadsheet. To do this, select the column you want to sort by clicking the column’s header cell.

Next, choose Filter from the Data menu and then select Advanced Filter. In the Advanced Filter dialog box, click the Copy To Another Location option. If you want a unique list, be sure to click the Unique Records Only option. Then, enter an out-of-the-way cell in the Copy To option. Or click that option and then click a cell.

Click OK to copy the contents of the column. Now, you can viagra australia no prescription sort the copied list by clicking any cell in it and choosing Sort from the Data menu. By default, Excel selects the Header Row option. Be sure to update this option accordingly.

Then, click OK. The result is a sorted list. Of course, this list isn’t dynamic. As you add records to the spreadsheet, you’ll have to re-create the copied list.

Date: January 25th, 2008

Author: Susan Harkins

Lists are new in Excel 2003 and they’re great, unless you want to subtotal columns; Excel disables the Subtotal feature for lists. Now, you can keep the list and produce subtotals the hard way. Or you can temporarily convert the list into a conventional range and subtotal the easy way. I always choose the easy way unless I’ve got a good reason to take the hard route.

Excel 2007 calls lists Tables. If you’re using Excel 2002 or an earlier version, lists aren’t available, but you can still use the easy Subtotal feature. You’ll know lists are in place by the small drop-down controls to the right of each heading cell.

Before you can subtotal list data, you must convert the list into a conventional range, viagra and premature ejaculation as follows:

Once you have a conventional range, sort the data according to your subtotaling needs. For instance, to subtotal the Total column by Salesperson, you must first sort the data by Salesperson, as follows:

1. Click in any Salesperson cell.
2. In Excel 2003 and earlier, click Sort Ascending or Sort Descending, accordingly. Excel 2007 users much select a specific sort by clicking Sort and Filter in the Editing group on the Home tab. Why they made sorting so difficult in 2007, I haven’t a clue.

Now you’re ready to add subtotals, as follows:

1. Click anywhere inside the spreadsheet.
2. In Excel 2003, choose Subtotals from the Data menu. Excel 2007 users should click Subtotal in the Outline group on the Data tab. In the Subtotal dialog box, you have a number of choices:
   • From the At Each Change In control, choose the column by which you’re subtotaling (not the column that contains the values you’re subtotaling). In this case, that’s the Salesperson column.
   • From the Use Function control, select the appropriate function. Sum is the default, and in this case, the desired function.
   • Check the appropriate columns in the Add Subtotal To section. In this case, check Total.
3. After specifying the right columns and functions, click OK.

This feature inserts a subtotaling row and result for each group as specified in the Subtotals dialog box. In this case, Salesperson is the group, so Excel displays a subtotal for both Susan and Bill.

To reclaim the previous lists, select the spreadsheet, press Ctrl+L, and click OK to close the Create List dialog box. You don’t even have to remove the subtotals first. The first time you sort any list in the spreadsheet, Excel will warn you that it’s going to remove the subtotals. However, you can remove the subtotals by displaying the Subtotal dialog box and clicking Remove All.

Date: April 22nd, 2008

Author: Mary Ann Richardson

When you create a bubble chart in Excel, you do not select the labels, as Excel would not know what to do with them. Instead, you need to add the chart labels after you create the chart. Adding the x-axis and y-axis labels can be done in the usual way. However, Excel has no specific tools for adding individual viagra alternatives over the counter data labels to each bubble. You will need to add each data label separately.

For example, say you have just created the following bubble chart from the range B2:D7.

Follow these steps to add the employee names as data labels to the chart:

The completed data labels are shown below.