Microsoft Word
Microsoft Excel
Microsoft Access

1. How do I correctly align numbers when bracketing negative figures?

   Right alignment or the use of the right tab will correctly align numbers where brackets are not used and where the same numbers of decimal places are used throughout the list. However if you do use brackets or different numbers of decimal places, right-alignment will not be appropriate.

   Word includes a special type of tab for aligning numbers - the decimal tab. In spite of its name it is not necessary to have a decimal point in the number for it to be aligned correctly - whole numbers will simply be aligned to where the decimal point would have been. This enables numbers to be aligned correctly even when brackets are used.

   The decimal tab can be used in a normal paragraph or in a table.

2. How do I easily enter fractions apart from ½, ½ and ¾?

   Enter the fraction as number / number, e.g. 7/8. Select the '7' and change it to superscript (Ctrl Shift =), then select the 8 and change it to subscript (Ctrl =). The resulting fraction can then be selected and entered into AutoText. The easiest way to set up additional fractions is to call up the 'one you made earlier', and change the numbers, which will already be superscript and subscript.

3. How do I neatly underline figures in table columns?

   There are several ways. Using cell borders is OK but results in underlines that are as wide as the column rather than the figures within it. Using the dash or the equals results in an underline of the right width and in the right position, but it will generally not be a solid underline.

   One method is to use the character spacing option within the font dialog box. Enter about a dozen dashes, then double click in the middle of them to select them all. Right click in the selected dashes and choose font from the short cut menu. Click the character spacing tab and, on the spacing line, in the 'by' box use the down arrow to 'condense' the spacing by about 1.2 points. This should give you a solid underline. Inserting or deleting dashes in the middle of the solid line will make it longer or shorter as required.

   A similar approach will work with the = for a double underline, although often the value of 1.2 points will be different. Once the solid underlines have been created, they can be saved as AutoText for use when required.

4. Whenever I do lists of numbers using tabs it all goes pear shaped.

   If you are creating a list of items using tabs, try ending each line with a shift-return rather than a normal return. This creates a new line without ending the paragraph. The advantage of this is that, should you subsequently change the tab position on any one line, all the lines will be automatically adjusted. This approach is less prone to error than treating each line as a separate paragraph, when it is all too easy to change one line and leave the others unchanged.

5. How do I connect word to a database to do a mail merge?

   Use the 'Tools', 'Mail merge' option. Step two of the mail merge helper lets you open an existing data source. Under 'Files of type' you can specify a Microsoft Access database, or use Microsoft Query to access other types of database. In recent versions of Word you will also see a 'Select method' check box. Ticking this before you find your database will enable you to choose the most appropriate way to access the data. For example accessing an Access database using the ODBC rather than the DDE method will enable you to access the data directly, without having to open the Access program itself.

1. How do I set a special number format as the default?

   Set the number up as a custom number format, in the 'format','cells' dialog box. For a typical accounting format with brackets for the negatives the format could be:

   #,##0.00_);[red](#,##0.00);-?

   Note the underline and closing bracket before the first semi-colon. This makes Excel leave an amount of space equal to the width of a closing bracket to ensure that bracketed figures line up correctly with non-bracketed figures. Also the third section of the format, which is used to show how zero figures are shown, includes a dash followed by two spaces.

   In Excel this can be saved as the number format for the style named 'normal'. If this workbook is then saved as a template file named book.xlt in the 'xlstart' folder, the format will be available to all new workbooks set up using the 'new' icon.

2. What's the easiest way to copy a range of cells?

   Well this depends on whether your hand is on the keyboard or the mouse, and where you want to copy the cells to. Assuming you are using the mouse, once you have selected the area to be copied, you can click the area with the right mouse button and choose 'copy', and then right click the top left cell of the destination area and choose 'paste'.

   If you are copying a cell, or cells in a single column, across the page to one or more columns, it is easiest to 'drag' the small black blob in the bottom right hand corner of the cell or range of cells as far left or right as you require.

3. How do I change the width of my columns to fit the figures in them?

   To change the width of a column to fit the widest figure or piece of text in the whole column, simply double click the line at the right hand edge of the column heading for that column - this is the grey box at the top of the worksheet with the column's reference letter in it. To fit only to certain cells in a column, select the cells you require first, then double click as above.

4. How do I link a chart to a pivot table?

   Usually you will want to turn off the row and column grand totals otherwise they will distort the chart. In Excel 97 all you then need to do is to click on a cell anywhere within the pivot table, and then run the chart wizard. With earlier versions of Excel you will need to select the area yourself. To do this, select the bottom right cell and then drag the selection to the top left cell - under, and not including, the 'sum of amount' cell:

   Sum of Amount	Salesperson
   Region	Alan	Bobby	Geoff	Martin
   North	70	120	0	60
   South	230	150	340	110

   Then once again you can use the chart wizard.

   In both cases the chart is linked to the pivot table itself, not just the cells you have selected. This means that the chart will adapt to changes you may make to the pivot table - even moving row and column headings around.

5. Do I have to use the keyboard to enter mathematical operators when I'm creating formula by 'pointing' to cells?

   No.

1. I've got a list of transactions with nominal codes. How do I turn them into a trial balance?

   If you hold both sides of a transaction in a single record, by including the debit and the credit account for each amount, the problem is to create two lines - one debit and one credit from one transaction.

   One way of achieving this is to create a new table with one field, headed DrCr for example. This field should be a number field. Enter two records: 1 and -1.

   If you create a query with two unlinked tables as the input, and include fields from both tables in the output, the number of output lines will be the number of records in one table multiplied by the number of records in the other table. This can lead to some very long and slow queries! In our case we will use our two record table described above in conjunction with the transaction table. This will create two rows for each transaction. The use of 'iif' statements can then include the correct debit or credit account in each record, and multiplying the amount of the transaction by the DrCr value will take care of the positive/negative values:

   Nominal account expression:

   Account: iif([DrCr]=1,[DebitAccount],[CreditAccount])

   Value expression:

   DrCrAmount: [amount]*[DrCr]

    

2. What does referential integrity mean? Is it anything to do with diving in the penalty area?

   Within a relational databases, information is held in separate 'tables' which are linked together using a field which is common to both tables. For example a sales ledger system could include a client table and an invoice table, with the invoice table including a client 'code' field which links it to the client table. This relationship would be a 'one to many' relationship. One client could have many invoices.

   Referential integrity controls whether unlinked records are allowed in the two tables. It would be perfectly legitimate for a client to have no invoices, i.e. a record in the 'one' table with no matching record or records in the 'many' table. However referential integrity would not allow an 'orphan' invoice record - a record in the 'many' table with no matching record in the 'one' table. Without referential integrity this could occur if a client with invoices was deleted without the deletion of the related invoices.

   Access can enforce referential integrity between linked tables. This is achieved using the 'relationships' screen.

3. How do I use data on a spreadsheet within Access?

   Access can import data from spreadsheets using the 'File','Import' option (Access 97 - 'File','Get External Data', 'Import'). In addition Access 97 has the ability to create a dynamic link to a sheet in an Excel workbook, or to a named range in worksheet. Use the 'link tables' option of 'File','Get External Data' to create such a link.

4. How do I link information from two different programs?

   Access can be used to store data in its own right, but it can also be used to enable 'access' to data you already have in existing systems. Many applications for the accounting profession are now written using 'open' data structures or special drivers are provided to allow access to the data.

   If you have data in such a package you can link an Access database to it, in the same way as described above for dynamically linking to a spreadsheet. Once a data table is linked the data can be viewed, and in some cases edited, as though it was held within the Access database.

   If you had a tax program and a practice management program, both with accessible data structures, you could link information from them both to a single Access database, and then design forms and reports to included combined data from the two programs. To achieve this you have to have a field in both linked tables that is common, for example a common client code, in order to be able to link the tables together. If this does not exist, and you cannot create such a field within either package, you would need to create a third table in Access which would 'cross index' the unique client code from each table.