Pop-up Pictures of Coins in Excel

Lots of folks, like me, keep their coin inventory data in Excel spreadsheets. And like me, they would like to have pictures of their coins in the spreadsheet. There are a several ways to do it. One is to insert pictures directly into the spreadsheet. Problem is the pictures are visible all the time. If you have very many coins, the area covered with text and pictures becomes enormous. It can be a problem for reformatting or printing the spreadsheet.

You can put the pictures on designated sheets in a workbook, and particular coins will be visible only when you click on their sheet. But that makes scrolling through your collection inconvenient. And the problems of reformatting and printing are even more difficult.

You can use macros to have pictures pop-up and do lots of neat things. But how are your VBA programming skills?

I prefer to have the pictures pop-up when the cursor is hovered over certain cells and otherwise be invisible.

The way to do it is with notes/comments. Some newer versions of Excel, like mine, use the term, “notes”, for comments. If you have a version of Excel that uses “comments”, just substitute “comment” for “note” below. I organize my spreadsheet so that each coin has a row. If you organize yours differently, make appropriate adjustments to the directions below.

In the row for each coin, I have a cell with the contents of “ob pic” for the obverse picture and another with “rv pic” for the reverse picture. Other cells in the row have info like mint, date, description, cost, etc. Do yours however you want, but there can be only one picture per cell. However, you could join multiple pictures into one with image-editing software and put the composite in one cell. The cell doesn’t have to have contents describing the picture; it could be cost, mint, whatever. However, I find it simpler and more flexible for pictures to have their dedicated cells.

The basic concept is that the picture will become the background for the note. Left click on the cell; then right click on it. In the pop-up menu, click on “New Note” (“Insert Comment” in some versions). If there is any text in the note, delete it. Now place the cursor on an edge or corner of the note box so that you see crossing arrows. It is very important that the crossing arrows be visible for the next steps to work. Right-click on the crossing arrows and from the pop-up menu select “Format Comment”. It will show “Format Comment”, not “Format Note”, in most versions of Excel. A pop-up menu should appear with multiple tabs. If the only tab is “Font”, the cursor was inside the box rather than on the edge or corner when you right clicked; so, try again.

Select the “Colors and Lines” tab. Under “Fill” and “Color:”, left click the down arrow. At the bottom of the menu with the colored squares, left click “Fill Effects”. Select “Picture” from the pop-up menu. Left click “Select Picture”. A menu may appear with choices of “Work Offline” or “Cancel”; choose “Work Offline”. You will now be given the opportunity to select a picture. Do so. Excel understands most image formats. Once you have the picture file name in the “File Name:” box, click on “Insert”. This action brings you back to the “Fill Effects” menu, and you should see the picture in the box. Check “Lock picture aspect ratio”; click OK. The “Format Comment” menu should appear; click OK.

The picture or part of it should appear in the note box. Use the grab handles on the box to match the aspect ratio of the box to the picture. Click on some other cell, and the note box should disappear. Hover the cursor over the picture cell, and the picture should pop-up.

If you want to change the picture, left click on the cell and then select “Delete Note” (or “Delete Comment”). Then use the procedure described above. If you want to change the aspect ratio or size of an existing note box, left click on the cell and choose “Edit Note” (or “Edit Comment”). The note box may now appear as an empty box. Simply left click on the border of the box, and the picture will appear. Now use the grab handles to change the size or aspect ratio of the box. You can also type text in the note/comment, but it will be in front of the picture (the picture is the background). Save and backup the spreadsheet!

You should be aware that by incorporating pictures into the spreadsheet, its file size can become enormous depending on how many pictures are incorporated and their resolution. For many of my coin pictures, I create lower resolution versions for incorporation into the spreadsheet while keeping the high res versions in photo folders.

Cal

 

Log in or Sign up to hide this ad.

Worked as you described. Thanks.

BTW, in my version of Excel, when you right click on the cell, I get both "New Comment" and "New Note". I used "New Note" and it worked like you described. ("New Comment" is something completely different)

 

Good info for those not aware. I have been doing this for about 2 years now for images. Also, so the database page isn't too "busy", you can use the Insert comment function to hold such data as your storage reference ID, attributions of errors or varieties and provenances for ancients etc. and just have it pop up when the mouse is hovered over the cell.

 

There is one other way to have pictures quickly available from the spreadsheet simply by clicking on a cell (not a true pop-up). The method is to put a link to a picture in a cell. Right click on an empty cell. Then from the menu, select “Link”. In the small and big boxes, go through the disk drive and folder structure to your picture. Click on it, and the complete path to it should be in the address box at the bottom of the window. Click on OK. The window will close, and the link will be in the cell. Double click on the cell, and the picture will now be displayed in your default image-viewing program.

The advantage of the above method is that accessing pictures from the spreadsheet adds almost nothing to the file size of the spreadsheet because the pictures aren’t really in the spreadsheet. But there are two disadvantages. (1) As mentioned above, when you double click on the link cell, you are taken out of Excel and into an image-viewing program. This may not be bothersome to some folks. (2) Far worse; if you change the disk drive letter, rename any folders in the path, insert or remove folders in the path or change the picture file name, the link will no longer work. Or even worse, the link may bring up the wrong picture. And the spreadsheet will work on a different computer only if the images are there and have the same path as the original computer.

Cal

 

Good point. I use notes/comments for financial data (cost, source of funds, current value, etc.). That way they are kept confidential but quickly accessible.

Cal

 

@calcol thanks for that writeup. Very easy to follow.

 

Cal, thanks for your excellent writeup. I will try this on my collection.

For years I have used the other hyperlink method you describe in your second post, linking the image of usually the obverse & cert #. While the method is quick, you can have problems as you describe if this folder is moved/renamed, or if the base image filename is changed.