I’m a big fan of TextExpander ($34.95) and use it every day to type often-used phrases, addresses, etc.  Recently I ran into a problem using TextExpander to fill in multiple fields of a Waiver form I made in Excel.  If you want to fill out a form in Excel by using TextExpander, but you are using merged cells, you can run into issues because of the way TextExpander works and because Excel will not allow you to paste text on a merged cell.

How to use TextExpander Tabs With Merged Cells in Excel

The snippet I’m expanding looks like this:

1829 Home Ave%key:tab%Berwyn, IL

Where %key:tab% causes TextExpander to insert the tab character, which causes the cursor in Excel to jump to the next cell.  When inserting that snippet, if the second cell consists of merged cells you’ll get the following error:

Cannot change part of a merged cell.

Error in Excel: Cannot change part of a merged cell.

If the spreadsheet is protected then you’ll also get this error:

The cell or chart you are trying to change is protected and therefore read-only.

There is a way to get around this issue in Excel and it involves using a horizontal alignment setting in Excel called “Center Across Selection.”  Instead of merging the cells I select all of the cells and set the horizontal alignment to be “Center Across Selection” as seen below (select the cells and then use the keyboard shortcut Command-1 to bring up this window on a Mac):

Center Across Selection Horizontal Text Alignment Setting

Now lock all but one of the cells in the range you previously selected and you can tab to this cell and the text will “center across selection,” looking just the way it should, and you can use TextExpander to fill it in:

Now we achieve the same results, but without using merged cells

Note that there are two distinct cells that “Berwyn, IL” is centered across but when it prints you’ll never see the difference.