Software

Office Q&A: After-the-fact captions and easy counts using a PivotTable

This month, Susan Harkins helps readers learn how to add captions to multiple images and how to count items in a group.

Sometimes you get lucky; sometimes you don't. Iain is hoping for an easy way to add captions to pictures in an existing Word document—there really isn't one. Word makes it easy to find pictures, but there's no easy way to automate adding a caption to all of them. José wants to count items in a group, and that's easy thanks to Excel's PivotTable.

I'm using Office 2016 (desktop) on a Windows 10 64-bit system, but both solutions will work in earlier versions. You can't insert captions in a Word document in the browser edition, but you can create PivotTable's in the browser edition. You can work with your own data or download the demonstration files.

Adding captions after the fact

Iain has a document with several picture files dispersed throughout the text. Now the trick is to add a caption for each. The best time to add a caption is when you insert the picture. The process is the same, but you won't displace existing content by adding a new element after-the-fact. To add a caption to a picture, right-click the picture and choose Insert Caption.

SEE: 13 handy Excel data entry shortcuts (free PDF) (TechRepublic)

Using the dialog shown in Figure A, you can customize your captions. The default label is Figure 1 and as you insert new figures, Word updates the sequential numbers automatically. The default position is below the picture, but you can change it using the Position dropdown. The default settings are adequate for most documents. You can also change the default label by clicking the New Label button. By doing so, I was able to overwrite the Figure default with Bloom.

Figure A

2018marchqaa.jpg
Use the Caption dialog to automate captions for your figures.

The problem with adding captions after you're done with the document is apparent in Figure B. As you can see, adding the caption displaces existing content. For this reason, I recommend adding captions as you add the pictures. Unfortunately, that's not always possible.

Figure B

2018marchqab.jpg
Inserting the caption pushes the following content down.

Adding a caption to each picture isn't that difficult—simply select the picture and add the caption. If there are only a few pictures to caption, this is simple enough. If you're working with a long document with several pictures dispersed over many pages, your job is a bit more tedious. However, you can help yourself, a bit.

If the pictures are close together, you can save yourself a few clicks by using F4. First, caption the first picture. Then, select the next picture and press F4. Doing so will repeat your last task—which was adding a caption to a figure. Word inserts Bloom x and adjusts the insertion point so you can immediately add the rest of the label. If you add additional content, such as Crocus in the first picture, you lose your edge and this technique won't work. If you can live without the additional content, keep selecting pictures and pressing F4. In addition, you can use the scroll bar or your mouse wheel to move through your document; if you move the insertion point, this technique won't work

As simple as that is, this process will prove tedious if you're browsing through lots of pages. You can use the Find feature to navigate the pictures. To do so, click Find in the Editing group (on the Home tab). In the Navigation panel, choose Graphics from the Find dropdown and then use the arrows to move from one picture to the next, avoiding all the text in between. Unfortunately, you can't use F4, because your last task is a find.

VBA isn't quite the magic bullet, in this instance, that it usually is because it's impossible (I rarely use an absolute) to fix the displaced content. If you have a VBA procedure that adds captions and fixes content displacement along the way, please share it!

Counting related items

José wants to count items in one column based on another, and a PivotTable is the quickest way to get there. Figure C shows a simple data set. Now, let's suppose you want to know how many times each person sold each item. In such a simple data set, you can easily discern that John sold apples three times, but that's not a solution. Instead, considering using a PivotTable to group the data by personnel and count different items (values) in each personnel group. In grouping terms, each person is a group.

Figure C

2018marchqac.jpg
We'll use a PivotTable to count items.

To generate a PivotTable based on this data set, do the following:

  1. Click anywhere inside the data set. (The data set is a Table object.)
  2. Click the Insert tab and then click PivotTable in the Tables group.
  3. In the resulting dialog, you can choose to insert the PivotTable in a new worksheet or the existing worksheet. For this example, retain the default, which is New Worksheet. Click OK to continue.
  4. In the PivotTable Fields pane, drag Personnel and Item to the Rows section and Item to the Values section (Figure D).

Figure D

2018marchqad.jpg
Create a simple PivotTable to count items by personnel.

Excel defaults to a count of the Item field. To show the count by personnel each month, drag Month to the Columns section. Figure E shows the results.

Figure E

2018marchqae.jpg
Add Month to the PivotTable.

This is a simple example; you can do much more with PivotTables. To learn more about this feature, read the following articles:

Make summarizing and reporting easy with Excel PivotTables

Get the most out of your Excel PivotTables with these handy tips

Stay tuned

In a future article, I'll show you how to use Excel's data model feature to create a relationship between two Table objects to display description text in a lookup table instead. Using the above data set, we'll display the more meaningful descriptions in column H instead of the shelf codes in column C.

The pictures in the Word document are mine. You are free to use them for instructional purposes (only).

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-664213562.jpg
Image: iStock/shironosov

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