February 19, 2009

Quickly move data to another workbook without copy and paste

  • Date: November 4th, 2008
  • Author: Mary Ann Richardson

Excel offers a fast, convenient way to transport data from one workbook to another. Mary Ann Richardson explains the technique.

Let’s say you keep all records for your clients for the Philadelphia region in one Excel workbook. The first worksheet lists the company name, address, and contact name and title. The second worksheet keeps a record of the clients’ sales transactions. You would like to use the information in the Excel workbook to send weekly reminders of weekly promotions to your clients. However, you don’t want the marketing staff to work with the same workbook as the sales staff. Using Excel’s worksheet copy/move feature, you can quickly create a workbook that contains only the information needed to address your weekly promotional mailings. Follow these steps:

  1. Open the workbook containing the customer data.
  2. Right-click the first worksheet tab.
  3. Select Move Or Copy from the shortcut menu (Figure A).

Figure A

move or copy

  1. Click the To Book drop-down list and select (new book).
  2. Select the Create A Copy check box (Figure B) and click OK.

Figure B

create a copy

Excel creates a new workbook (Book1) and copies the data from the first worksheet to Sheet 1 of the new book. Save the new workbook as Mailing Lists.

To add another list of clients from another workbook — say, one containing records on customers from Cherry Hill, NJ — follow these steps:

  1. Open the Mailing Lists workbook.
  2. Open the second workbook containing the cialis trial pack customer address listing for Cherry Hill on Sheet 1.
  3. Right-click the Sheet 1 tab.
  4. Click Move Or Copy from the shortcut menu.
  5. Click the To Book drop-down list and select Mailing Lists.
  6. From the Before Sheet list box, select (move to end).
  7. Select the Create A Copy check box (Figure C) and click OK.

Figure C

move to existing sheet

Permalink • Print • Comment

Easy lookup formulas in Excel

  • Date: October 4th, 2008
  • Author: Susan Harkins

Creating the right formula often seems like rocket science, but Excel offers many tools to help. One of those tools is the Lookup Wizard. You’ll need a lookup formula to return a value from a table (the intersection of two values). Lookups cialis tadalafil side effects can be difficult to write even if you know what you’re doing. Don’t struggle with the problem; launch Excel’s Lookup Wizard.

If you don’t find this helpful wizard on the Tools menu, install it as follows:

  1. Choose Add-Ins from the Tools menu.
  2. In the resulting dialog box, check Lookup Wizard and click OK.


In the worksheet, label two cells for entering two parameters, which represent the column and row headings. (You don’t have to do this, but the labels make the process easier.) Then, you’re ready to launch the wizard and create a lookup formula as follows:

  1. From the Tools menu, choose Lookup.
  2. Identify the table that contains the lookup values and then click Next.


  1. The wizard does a good job of identifying the appropriate column and row headings (the parameters). Most likely, you won’t have to do a thing other than accept the wizard’s choices and click Next.


  1. In the next window, decide whether you want to display parameters and the formula’s results or just the results. If this is a one-time calculation, you don’t need the parameters. If you want a more dynamic solution, include the parameters as shown. Then, click Next to continue.


  1. Specify a cell for the first parameter and click Next.


  1. Specify a cell for the second parameter and click Next.


  1. In the final window, select a cell for the formula and click Finish.


Everything’s in place now. Simply update the parameters to find the value you need.


Pay close attention to the formula in the Formula Bar. How long would you spend writing and testing such a complex formula? I don’t even bother. Excel is more efficient than I’ll ever be!

Permalink • Print • Comment

Use Excel’s spinners to analyze input value assumptions

  • Date: October 28th, 2008
  • Author: Mary Ann Richardson

The spinner offers an easy way to test different input variables to see the effect on a financial model — without altering your underlying formulas. This example shows how to put this useful feature to work.

Sensitivity analysis determines how a spreadsheet’s outputs will vary in response to changes made to the spreadsheet’s inputs. Excel gives us a number of tools for sensitivity analysis. One of these is the spinner. A spinner is linked to a cell containing one of the variable inputs. As the user clicks on the spinner, the value of the linked cell changes.

For example, the following worksheet can be used to examine how changes in three variable cialis soft tabs online inputs (price, unit cost, and fixed cost) affect profit. To set up the formulas for the worksheet, follow these steps:

  1. Select C1:D7, as shown in Figure A.

Figure A

worksheet setup

  1. Go to Tools | Options, click the Calculation tab, and then select the Accept Labels In Formulas check box. (In Excel 2007, click the Formulas tab, click Create From Selection in the Defined Names group, and then click OK.)
  2. Enter 2 in cell D1, which is the current price.
  3. Enter the rest of the formulas as shown in Figure B.

Figure B

entering formulas

  1. Type Enter Unit Cost (in hundredths) in cell H1.
  2. Type Enter Fixed Cost (in thousands) in cell H2.
  3. Enter 44 in cell I1 (the current Unit Cost).
  4. Enter 20 in cell I1 (the current Fixed Cost), as shown in Figure C.

Figure C

entering text

  1. Go to View | Toolbars | Forms to display the Forms toolbar. (In Excel 2007, click the Developer tab.)
  2. Click the Spinner button on the Forms toolbar. (In Excel 2007, click Insert in the Forms Group and then click the Spin button under Form Controls.)
  3. Click and drag in cell F1 to create the Price spinner.
  4. Repeat step 9 and then click and drag in cell F3 to create the Unit Cost spinner.
  5. Repeat step 9 and then click and drag in cell F4 to create the Fixed Cost spinner.
  6. Right-click the spinner in F1, click Format Control, and enter the values shown in Figure D.

Figure D

formatting control in F1

  1. Right-click the spinner in F3, click Format Control, and enter the values shown in Figure E.

Figure E

formatting control in F3

  1. Right-click the spinner in F4, click Format Control, and enter the values shown in Figure F.

Figure F

formatting control in F4

Your manager just learned that increases in material costs have raised the unit cost to .60. Using the spinner to adjust for this higher cost, the profit decreases to $45,800. Clicking the Price spinner up arrow once to raise the price to $3 immediately shows a drop in demand (assuming the demand formula is correct). However, even with this drop in demand, profit goes up significantly, which may warrant the price increase.

Permalink • Print • Comment

February 5, 2009

Transfer data from Word forms to an Excel worksheet

  • Date: September 30th, 2008
  • Author: Mary Ann Richardson

Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.

Word forms provide a convenient way to gather information from users or clients so that you can analyze it later. But bringing the data into Excel can be a big chore. To avoid having to manually enter the data into a worksheet, let Excel’s Text Import wizard bring it in for you. To demonstrate, let’s say you’ve asked employees to complete the form shown in Figure A.

Figure A

Follow these steps to save the data in each completed form to a text file that can be imported into Excel:

  1. Open one of the completed forms.
  2. Go to Tools | Options, click the Save tab, and select the Save Data Only For Forms check box. (In Word 2007, click the Office button, click Advanced, scroll to Preserve Fidelity When Sharing This Document, and select the Save Data As Delimited Text File check box.)
  3. Click OK.
  4. Save the file as a .txt file.
  5. When the File Conversion dialog box appears (Figure B), click OK.

Figure B

You can now import the data in the text files into a spreadsheet by following these steps:

  1. cialis canadian pharmacy
    Open a blank worksheet in Excel.
  2. Go to Data | Import External Data | Import Data. (In Excel 2007, click the Data tab, click Get External Data, and then select From Text.)
  3. Click the text file you want to import, then click Import.
  4. Select the Delimited option (Figure C) and then click Next.

Figure C

  1. For this example, clear the Tab check box and then select the Comma check box (Figure D).

Figure D

  1. Click Next and then click Finish.
  2. Click in cell A1 and then click OK.

Figure E shows the resulting data. Note that headers have been inserted above the data in row one.

Figure E

To import the second text file, you just open the same Excel worksheet and click in the second row below the last row of data; in this case, A4. (The wizard forces you to skip a row each time you add a new row of data. You can delete these blank rows later.)

Important note: If you need to enter more than a handful of forms a day, you should consider using ADO (ActiveX Data Object) to create a code connection to the worksheet that will automate the data transfer process.

Permalink • Print • Comment

Print a graphic in an Excel header — sort of

  • Date: September 26th, 2008
  • Author: Susan Harkins

Word lets you insert a graphic into a document’s header or footer. Excel’s not so flexible, but you can cheat. Insert the graphic and specify it using the Print Titles feature. Excel will print the graphic at the top of each printed page, which is really what you want — header or not.

The first step is to get the graphic into the workbook. Try to insert it into cell A1 and then adjust the cell’s height to accommodate it, but that might not work. Depending on the size of the graphic, Excel might insert cialis c10 it as is, usurping several rows and columns. To insert a graphic, choose Picture from the Insert menu, select From File, locate the file, and then click Insert. In Excel 2007, click Picture in the Illustrations group on the Insert tab.


The next step is to identify the graphic using the Titles feature as follows:

  1. Choose Page Setup from the File menu and click the Sheet tab. In Excel 2007, the Page Setup group is on the Page Layout tab.
  2. Click the row or drag across the rows that contain the graphic. Or enter the rows in the Rows To Repeat At Top control. For instance, the RabbitTracks graphic (above) covers rows 1, 2, and 3. Therefore, you’d enter $1:$3. When you finish, click OK.



Click Print Preview to view the graphic on all pages of the worksheet. You can’t really tell, but the following picture shows the second page of a long sheet, and each page displays the same graphic at the top.


Permalink • Print • Comment
Next Page »
Made with WordPress and a healthy dose of Semiologic • Sky Gold skin by Denis de Bernardy