Tip of the week # 636
8 January 2021

Create and insert reusable pieces of content in a workbook (Microsoft Excel) *

This week's tip has been inspired by one of my delegates this Wednesday, who liked the idea of Quick Parts in Word and Outlook and asked whether it was also available in Excel. (Thanks for the inspiration, Sonal.) Well, I'm afraid not, although you can vote for the feature to be added in future releases using the official Office 365 feedback and suggestions site.

In the meantime, there are two alternatives you might want to check out ...

Set up automatic text replacement using AutoCorrect - ideal for short pieces of text

Here's how:

  1. On the File tab, click on Options.
  2. Click on Proofing or type the letter p.
  3. Click on the AutoCorrect Options button.
  4. In the Replace box, type a word or abbreviation you want to use as the "shortcut". For example, KR.
  5. In the With box, type or paste your preferred text. For example, Karen Roem.
  6. Click Add.
  7. Click OK twice.

In Microsoft 365 the entries in the AutoCorrect list are available across all Office programs that support the AutoCorrect feature. So next time you type KR followed by a space or tab or carriage return, it will change KR to Karen Roem in Excel, Word, Outlook and PowerPoint.

Record a macro - ideal for pictures and longer text (It's easier done than said and you only have to do this once!)

Here's how:

  1. If necessary, add the Developer tab to the ribbon by right-clicking anywhere on the Quick Access Toolbar and selecting Customize the Ribbon.
  2. On the Developer tab, in the Code group, click Record Macro.
  3. Type a name for the macro, such as autocorrect.
  4. Press TAB and hold down, say, the SHIFT key and type the letter A to assign CTRL + SHIFT + A as a keyboard shortcut to run the macro.
  5. Press TAB and select Personal Macro Workbook to make the macro available whenever you use Excel. (Make sure to click Save when you close down Excel and see a prompt to save the changes you made to the Personal Macro Workbook.)
  6. Press ENTER or click OK.
  7. Type the text and/or insert the picture you want to be able to insert as a reusable piece of content.
  8. On the Developer tab, in the Code group, click Stop Recording.

In future, you can use the shortcut key you assigned in step 4 to insert the text and/or picture. But why not check out tip 619 and add the macro as a button on your Quick Access Toolbar.

Related tips
1. Create and modify Quick Parts to insert frequently used text, tables or pictures - tip_605.php
2. Save and insert frequently used tables - tip_597.php
3. Create, store and insert frequently used text and graphics - tip_486.php
4. Create and insert frequently used text and graphics in Outlook - tip_335.php
5. Adding AutoCorrect Entries - tip_127.html 6. Open several workbooks with a single click - tip_619.php

* Unless stated otherwise, these tips were written for Microsoft Office 2010.