CXO

Tech Tip: Change Word line spacing/Excel natural language formulas/Add an Access report total

Word: Change existing text's line spacing

You can use the Format | Paragraph menu in Word to change the line spacing of existing text in a Word document, but for simple changes, using the line-spacing shortcut keys is much quicker. You can quickly change the line spacing of existing text in a Word document using shortcut keys.

For example, to change from single to double spacing, select the text you wish to change and press [Ctrl]2. To revert to single spacing, press [Ctrl]1. Pressing [Ctrl]5 changes the selected text to 1.5 line spacing.

The Formatting toolbar in Word 2002 now has a Line Spacing button. To double-space selected text, click the Line Spacing button's drop-down arrow, and click 2 for double spacing.

In addition to double spacing, you can also use the Line Spacing button for setting single (default), 1.5, 2.5, or triple spacing. If none of these are sufficient, you can always go to the Paragraph dialog box by selecting More. This takes you directly to the Paragraph dialog box's Indents And Spacing tab, where you can choose from the At Least, Exactly, or Multiple line-spacing options.

Excel: Use natural language formulas

You don't need to use cell names to build formulas. You can take advantage of Excel's support for natural language formulas to use label names instead. For example, suppose you had three columns of data labeled Region, Sales, and Percent of Sales. Below that, you have four rows, one row for each region, labeled East, West, North, and South. The next row is labeled Total Sales.

To find the total sales for all four regions using labels, you would enter =SUM(Sales). To find the Percent of Sales for the East region, you would use the column and row names as follows, inserting a space between the individual label names:

=Sales East / Sales Total Sales

Likewise, the entries for the remaining regions would be:

=Sales West / Sales Total Sales
=Sales North / Sales Total Sales
=Sales South / Sales Total Sales

As with any Excel formula, you can use the autofill feature to copy natural language formulas. In this case, to avoid a Division By Zero error, before copying, make the divisor absolute by selecting the divisor Total Sales and pressing [F4].

If you're using Excel 2002, the natural language formula feature is turned off by default. To turn it on, go to Tools | Options, click the Calculation tab, and select the Accept Labels In Formulas check box.

Access: Use the expression builder to add a report total

Errors in report totals are most often due to mistyped expressions or incorrect syntax. To avoid these errors, use the expression builder to add a report total. Follow these steps:

  1. In Design view, click the Text Box tool and click inside the Report Footer section to insert a text box.
  2. Type your caption (e.g., 2003 Total Sales), right-click the unbound object, select Properties, and select the Data tab.
  3. Click the Expression Builder button [...] at the end of the Control Source box.
  4. Click the equals [=] button in the Expression Builder dialog box to begin building the report total formula.
  5. Double-click the Functions folder in the left-hand list box, double-click Built-In Functions, and select <All> in the middle list box.
  6. In the right-hand list box, scroll down and double-click the Sum function.
  7. Select <<expr>> in the box at the top, double-click the Reports folder in the first list box, and select the report title from the drop-down list.
  8. Click <Field List> in the middle list box. This displays the fields used in your report in the right-hand list box.
  9. Double-click the name of the field whose values you want to total (e.g., if Sales is the field name, the resulting expression is =SUM([Sales]), and click OK.
  10. Press [Enter]. The formula is now your control source.

When you run the report, the total for the selected field will be displayed at the end of the report.

Editor's Picks