Software

Tech Tip: Keep Word table rows from breaking/Check Excel formula values/Change a field’s name in Access

Learn how to prevent table rows from breaking between pages, check formula values with Trace Precedents, and change a field's name in a query.

Prevent table rows from breaking between pages

Word automatically inserts page breaks when the last row of a table reaches the bottom page margin. Depending on the margin setting, Word might add these page breaks in the middle of a row, causing a part of the row to drop to the next page.

You can go back and force a hard page break above the row, but it's easier to prevent Word from inserting page breaks in the middle of a row in the first place.

Follow these steps:

  1. Right-click the table and select Table Properties.
  2. On the Row tab, deselect the Allow Row To Break Across Pages check box, and click OK.

Word will now insert all page breaks at the beginning of the row.

Check formula values with Trace Precedents

It's not uncommon for a correct formula to return invalid results. This is most often because of incorrect data references. However, manually checking each reference can quickly become time-consuming with a worksheet filled with formulas.

But you don't have to check a formula's references manually. Excel's Trace Precedents feature can check them for you by showing exactly which cells provide data to the formula.

For example, let's say D6 contains a correct formula for totaling October's sales for all four stores in a retail chain as entered in D2:D5. However, the formula result seems to be less than expected.

Using Excel's Trace Precedents, you can quickly determine whether the formula references the appropriate range of cells.

Follow these steps:

  1. Go to Tools | Formula Auditing | Show Formula Auditing Toolbar.
  2. Select the formula cell you want to check.
  3. Click the Trace Precedents button on the Formula Auditing toolbar.

Excel will draw a blue tracer arrow from the selected formula cell to the cell or cells that provide data to the formula. If you find that the blue arrow doesn't extend to cell D2, the unexpected low revenue resulted from one store's sales not being included in the data range.

If the cell providing data to the formula is itself a formula, clicking the Trace Precedents button again will illustrate the data range for that formula. To remove the trace arrows, click the Remove Precedent Arrows button on the Formula Auditing toolbar until Excel removes all arrows.

Change a field's name in a query

Any object based on a query displays the field names used in that query. For example, all reports and forms based on a query with a field named ADDR1 will also show ADDR1 for that field's label or header.

Rather than changing the field's label as it appears in each object derived from the query, you can use the field's Caption property to change it once in the query design grid.

Follow these steps:

  1. Right-click the field name in the query design grid, and select Properties.
  2. On the General tab, enter a name in the Caption text box.

The name in the Caption text box now appears as the field's heading in the query's Datasheet view. All objects based on that query will also display the Caption property as the field's label or heading.

However, changing the Caption property in the query design grid has no effect on the field's Caption property for the underlying table. All other objects based on that table will display the original field name.

Editor's Picks

Free Newsletters, In your Inbox