Hands on: Share Excel files without embarrassment

Welcome wizards
The irritating wizard on the Office Assistant has been removed from Excel 2007, but a number of more useful macros with the name remain.

The Text Import Wizard appears automatically when you open a text file with Excel, or when you import the data from a text file onto an existing worksheet. It’s far more sophisticated than in previous versions. Many databases hold dates in a yymmdd format. The Excel 2007 Text Import Wizard can convert them to ddmmyy.

Open a worksheet. Under the Data tab click ‘From Text’. Select the .txt file and click Import. Step 1 of the wizard gives you a preview of the file contents and offers the option to choose between data separated by spaces or delimiters such as commas or tabs. Step 2 lets you set the column widths.

Step 3, among other options, lets you select from half a dozen ways a date might be recorded in the text file. If the dates look like 080915, select the option YMD and the wizard will change them to the format 15/09/08. Excel will recognise the entries as dates, so you can reformat to something like Sept. 15th, 2008.

People frequently like to swap data between Excel and Outlook. You might want to transfer a phone list from Excel to the Outlook Contacts module or export a list of tasks in Outlook to an Excel worksheet. The Import and Export Wizard can handle this. It’s designed to handshake with Excel, but it starts from Outlook.

Choose Import and Export on the Outlook file menu. To transfer data from Excel to Outlook, choose ‘Import from another program or file’ in the first step of the wizard. Click Next. In the Import a File dialogue box, oddly, if you’re using Outlook 2007 and Excel 2007 in Office 2007 among all the programs offered, the only mention of Excel is for ‘Microsoft Excel97 ­ – 2003’. Not to worry ­ – it will import data from Excel 2007. In the final step of the wizard, you pick the file to import and choose between replacing or ignoring duplicate items.

Looking up
There can be times when a formula combining the INDEX function and the MATCH function can be a much better choice than VLOOKUP or HLOOKUP. But if that’s too complicated, turn to Excel’s Lookup Wizard which will create the formula for you. Click the Office button, Excel Options, Add-Ins, Lookup Wizard. If it’s not installed, follow the offered steps to install it. The wizard will then be shown on the far right of the Ribbon under the Formulas tab. Click Lookup and follow the four steps of the wizard to create the formula.