Tech Tip: Select Word cells in large tables/Find a value in Excel/Make Access reports easier to read

Learn how to select cells in large tables using the keyboard, find a value in a data range with an array formula, and make Access reports easier to read with alternate line shading.

Select cells in large tables using the keyboard

As the size of a table increases, the harder it becomes to use the mouse as your sole means of navigation in a table. For example, to select a column with the mouse, you need to move the pointer along the top gridline of the first cell in the column until it changes to a down arrow and then click.

However, using the keyboard simplifies this process. Position the pointer anywhere in the column, press [Alt], and select any cell. To use the keyboard to select an entire table, click anywhere in the table and, with Num Lock off, press [Alt]5 on the numeric keyboard.

Like Excel, Word XP and Word 2003 also let you press [Ctrl] to select nonadjacent cells. For example, to select columns 1 and 3 using the keyboard, press [Alt], click somewhere in column 1, press [Ctrl][Alt], and click somewhere in column 3.

Find a value in a data range with an array formula

You can use Excel's built-in Find feature to determine whether a specific value appears in a range of cells. But this isn't your only option; you can also use an array formula.

For example, let's say you have a spreadsheet that lists names, addresses, and phone numbers in the range A3:H110. You've named this range Customers, and you use cell B1 (named NamedEntry) to enter the name you wish to verify.

Follow these steps:

  1. In C1, enter this formula: =IF(OR(NamedEntry=Customers),"Found","Not Found")
  2. Press [Ctrl][Shift][Enter].

If you enter "Mary" in B1, the formula compares the value Mary to each cell in the Customers range. If there is a match, the formula displays Found in C1; if there is no match, it displays Not Found.

Make reports easier to read with alternate line shading

Financial reports that list scores of records with many columns of numbers can be difficult to follow. However, you can make these reports easier to read by adding alternate line shading.

Follow these steps:

  1. Open the report in Design View.
  2. Double-click the Detail Section Selector. (This is the box on the ruler to the left of the Detail section divider.)
  3. On the Event tab, select [Event Procedure] from the On Format drop-down list.
  4. Click the button to the right of the drop-down arrow.
  5. Enter the following, and close the VBA window:

Const vbLightGrey = 12632256

If Me.CurrentRecord Mod 2 = 0 Then
       Me.Section(acDetail).BackColor = vbLightGrey
       Me.Section(acDetail).BackColor = vbWhite
End If

When you run the form, Access will use a white background color for odd records and a light gray background color for even records. Please note that the background color of any text boxes in the record will remain the same.

Editor's Picks