The far side of Excel - Fireside treats for long winter nights
As you sit by your roaring log fire, idly fondling the shiny new notebook computer that Santa bought you, here are some suggestions for trying out a few lesser known features that lurk in the depths of the Microsoft Office applications, courtesy of our sister site, AccountingWEB.co.uk.
Microsoft Word - Excel in disguise
For most accountants the real problem with Word is that it isn't Excel. If you are in the middle of a Word document and find yourself stricken by Excel withdrawal symptoms, then Word's secret calculation option might offer some temporary relief.
Type in a mathematical calculation and select it – for example: 12*(3+6)
Now Click the Tools Calculate button (Excel 2007: Calculate). The Word status bar displays the result of your calculation:
In addition (sorry!) you can use Edit-Paste to insert the result of the calculation into your document.
The difficult bit is finding the calculate option in the first place. Prior to Word 2007 you can use the View-Toolbars-Customize option to add the Tools Calculate button to an existing toolbar. You can find it towards the bottom of the Tools category. In Word 2007 you can customize the Quick Access Toolbar to add the 'Calculate' button from the 'Commands not in the ribbon' or the 'All commands' categories.
Calculate is not the only command that will make Excel users feel at home in Word. There is a Formula command on the table menu that will insert a formula into a table cell, or even into the middle of a block of text (Word 2007: Customize Quick Access Toolbar, All Commands, Formula or Table Tools, Layout ribbon, Data group, Formula) . The formula can be a simple arithmetic calculation or can use one of nearly 20 built-in functions. There is also an AutoSum button on the Table toolbar (Word 2007: Customize Quick Access Toolbar, All Commands, Sum). AutoSum can be used within a table to automatically create a SUM() formula such as:
=SUM(LEFT) or =SUM(ABOVE)
Microsoft Outlook formulae
Word is not the only Office application that has a slightly obscure calculation capability. You may already be aware that you can add fields to list type views in Outlook by right-clicking in the column heading area and using the Field Chooser. However, the Field Chooser also includes a New button that can be used to create new fields to be displayed. These fields can be straightforward text, number or date fields for example, but you can also choose a Formula type:
The formula can be typed directly into the Formula box or, for more complicated formulae you can click on the Edit button and use the Field and Function buttons to help create the formula required. In this example we have created a new field to show the length of time between when an email was sent and received. We have formatted the result to display in hours and minutes:
If you need to edit a formula that you have set up, display the Field Chooser and drag the field out of the column heading area. It should appear in the 'User defined fields in…' section. You can then double-click on the field which will display the Field Properties dialogue box allowing you to edit the formula directly or via the Edit button. Here we have changed the format to show seconds as well: