Software

Office Q&A: Excel referencing, Word field codes, and a table trick

This month, Susan Harkins presents some easy solutions for a few problems that only seem big.

It's been a month of easy answers for the most part. The problems seem big, but as usual, there's an easy answer for each one. This month, I showed Gerald how to use INDIRECT(), showed Ray how to format a field code, and showed Jeanie how to reuse her favorite table formatting.

I'm using Office 365 on a Windows 10 64-bit system. All three solutions will work in all ribbon versions of Office. There are no demonstration files—you can easily recreate the situation yourself.

Workaround for updating references

Gerald wants to add a column, but he doesn't want an expression that references an adjacent column to update. Excel doesn't work that way. When you insert a column or row, references in expressions and functions update accordingly. Most of the time, that's what you'll want. Fortunately, there's help for Gerald, but first, let's review how Excel handles inserted (and deleted) rows and columns.

SEE: Windows spotlight: 30 tips and tricks for power users (Tech Pro Research)

Figure A shows a simple expression that adds the values in B3 and C3. If you enter a column, moving the value in C3 to the right, the expression updates accordingly to =B3+D3.

Figure A

oct2018qaa.jpg
This simple expression adds two values.

Gerald doesn't want the expression to update. He wants to insert a column without updating the reference. By wrapping each reference in Excel's INDIRECT() function, Gerald can bypass Excel's auto-updating referencing. This function is powerful, but all you need to know for now is that it returns a reference from a string—a string that Excel won't update.

The syntax is simple:

INDIRECT(ref_as_text, [a1])


where ref_as_text is a string that represents a cell reference using A1-style referencing, R1C1-style referencing, or a named range. A1 is an optional logical value that specifies the type of referencing, where A1-style referencing is the default. To specify R1C1-style reference, the optional a1 argument should be FALSE.

You can see how it works in Figure B. The new expression

=INDIRECT("B3")+INDIRECT("C3")

accommodates a new column. You can wrap all references, some, or only one in INDIRECT().

Figure B

oct2018qab.jpg
Use INDIRECT() to create a non-updating cell reference.

Formatting {StyleRef}

Ray uses a {StyleRef} field code to repeat the first heading on each page in the header. It's a great technique that you can learn about by reading Use Word's StyleRef field to efficiently reuse text. The heading is proper case, and Ray wants it to appear in upper case in the header.

The good news is that you can drop in a simple switch to format the {StyleRef} field code in the form

{STYLEREF style \* upper}

Figure C shows this simple solution at work. You can use Quick Parts to enter a field code or press Ctrl+F9 to start the process manually. Use Alt+F9 to toggle between the field code and its results.

Figure C

oct2018qac.png
Use the \* upper switch to display the results of the field code in upper case letters.

Reuse Word tables

Jeanie uses the same formatting for all Word tables, so she'd like a way to reuse it, or set it as the default. This is one of my favorite Word tips—save a formatted table to Word's Quick Tables gallery. (This won't work in earlier menu versions of Word.)

Saving a formatted table to the Quick Tables gallery is simple:

  1. Select the table.
  2. Click the Insert tab. In the Tables group, click Table and choose Quick Tables from the dropdown list.
  3. In the resulting dialog, choose Save Selection to Quick Tables Gallery (at the bottom).
  4. Enter a name for the table (Figure D) and click OK.

Figure D

oct2018qad.png
Name the table.

To add the formatted table to a document, click the Insert tab. From the Table drop-down menu, select Quick Tables, and select the table, as shown in Figure E. You can save as many formatted tables as you like.

Figure E

oct2018qae.jpg
Choose the formatted table from the gallery.

You can add several elements to their respective galleries, but they will appear below the built-ins. If you want to move your custom elements to the top of the list, read How to create custom galleries and categories to control organization in Word docs.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

See also

istock-874804344womanatcomputer.jpg
Image: bernardbodo, Getty Images/iStockphoto

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox