Microsoft Excel - How to show pics

[Accumulator 52]

I've looked at proprietary software packages but for the moment have decided to build a simple Excel spreadsheet as I sort through my coins. I read previous threads about Access (which I do have some experience of) and one of the main reasons for going that route seems to be the difficulty in adding images to Excel.

Checking around various techy forums, though, I've come across a really neat way to add images to excel using the 'comment' feature. It's not that obvious how to go about it, but the result is that when you mouse-over the relevant cell, up pops your image and any text you want to add too! As soon as you move the mouse pointer away the image disappears.

If anyone wants precise details, I'd be happy to post them here.

The effect is like this (excuse the rather basic dummy spreadsheet in the background):

[azda 981]

If you can post the (for dummies) version accumulator

[Accumulator 52]

If you can post the (for dummies) version accumulator

Actually it's really easy, just very well hidden for some reason.

This is what you do:

1. Choose the cell that you want to activate the coin pic. when you mouse-over (if you want separate pics for OBV & REV just use two cells, one for each)

2. Right click on that cell then choose 'insert comment' from the drop down menu

3.The text box which pops up may contain some dummy text which you can just delete.

4. Place your mouse pointer over the BORDER of the box, right click and choose 'Format Comment' from the drop down menu. This is the important bit, because if you right click over the centre of the box you won't get the option to add a pic later!

5. In the Format Comment window that pops up, click on the 'Colours and Lines' tab then click on the down arrow next to Fill Colour to bring up the colours and fill effects. Click on the 'Fill Effects' button at the bottom.

6. In the fill effects window which pops up, you will have a 'Picture' tab which allows you to browse and select a picture from your computer.

7. Finally, you may need to close the Fill Effects window and go back to the Format Comment window to set the size and aspect ratio of the pop-up pic. You can also add some text if you like too.

Once you have done the above, a small red triangle will appear in the corner of the cell to show that a pic is available. Very neat!

And that's it. Sounds complicated but it's really not.

[declanwmagee 122]

If you can post the (for dummies) version accumulator

Actually it's really easy, just very well hidden for some reason.

This is what you do:

1. Choose the cell that you want to activate the coin pic. when you mouse-over (if you want separate pics for OBV & REV just use two cells, one for each)

2. Right click on that cell then choose 'insert comment' from the drop down menu

3.The text box which pops up may contain some dummy text which you can just delete.

4. Place your mouse pointer over the BORDER of the box, right click and choose 'Format Comment' from the drop down menu. This is the important bit, because if you right click over the centre of the box you won't get the option to add a pic later!

5. In the Format Comment window that pops up, click on the 'Colours and Lines' tab then click on the down arrow next to Fill Colour to bring up the colours and fill effects. Click on the 'Fill Effects' button at the bottom.

6. In the fill effects window which pops up, you will have a 'Picture' tab which allows you to browse and select a picture from your computer.

7. Finally, you may need to close the Fill Effects window and go back to the Format Comment window to set the size and aspect ratio of the pop-up pic. You can also add some text if you like too.

Once you have done the above, a small red triangle will appear in the corner of the cell to show that a pic is available. Very neat!

And that's it. Sounds complicated but it's really not.

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

[declanwmagee 122]

Here's how I think I'll use it...

explanatory pic to show what a 1953 obverse 1 Shilling looks like, for instance:

[Rob 3,185]

Before the files got corrupted a month ago, I just converted the text in the box to a link to an image of each coin which I could call up when I wanted. That seemed to be simple enough to operate as it was one box, one coin. The above seems a tad complicated.

[SionGilbey 1]

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

If you have a modern computer a 2mb pic on each of the 2000 makes only 2GB in files.

Which is only the size of the smallest flash drive you can get these days.

Computer hard drives are usually more than 100GB, mine being 240.

Before the files got corrupted a month ago, I just converted the text in the box to a link to an image of each coin which I could call up when I wanted. That seemed to be simple enough to operate as it was one box, one coin. The above seems a tad complicated.

I think this is supposed to be quicker and more aesthetically pleasing.

Also it's more convenient if you are checking all the images of the same year coin for an error you've just read about you can just gently move the mouse down as opposed to opening an unholy amount of windows or tabs.

[Rob 3,185]

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

If you have a modern computer a 2mb pic on each of the 2000 makes only 2GB in files.

Which is only the size of the smallest flash drive you can get these days.

Computer hard drives are usually more than 100GB, mine being 240.

Before the files got corrupted a month ago, I just converted the text in the box to a link to an image of each coin which I could call up when I wanted. That seemed to be simple enough to operate as it was one box, one coin. The above seems a tad complicated.

I think this is supposed to be quicker and more aesthetically pleasing.

Also it's more convenient if you are checking all the images of the same year coin for an error you've just read about you can just gently move the mouse down as opposed to opening an unholy amount of windows or tabs.

I guess it's horses for courses. All I needed was the ability to compare an image copied off the web or in a book, with those in the database. Having compared images, I either extend an existing provenance and redo the link or create a new entry. Whatever, it would be too much work to modify each entry to a different format.

[declanwmagee 122]

If you have a modern computer a 2mb pic on each of the 2000 makes only 2GB in files.

Which is only the size of the smallest flash drive you can get these days.

Computer hard drives are usually more than 100GB, mine being 240.

Modern computer or no, the very last thing you want is a 2Gb Excel spreadsheet; trust me...

Excellent tip though - I will be using it!

[Accumulator 52]

Modern computer or no, the very last thing you want is a 2Gb Excel spreadsheet; trust me...

Excellent tip though - I will be using it!

Glad it helped. I've been working on my penny sheet today. I think the easiest way around any size problems, should they become an issue, is to simply put different denominations (or monarchs if you prefer it that way!) into separate sheets.

[Peckris 579]

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

Not only that, the program will slow down exponentially once the file size goes over a certain size.

This is the great advantage of databases. You can have a related table of pictures and just set up a relationship between the coins table and the pictures table, and when you open the coins file, you can see the pictures without even having to open that file.

[Accumulator 52]

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

Not only that, the program will slow down exponentially once the file size goes over a certain size.

This is the great advantage of databases. You can have a related table of pictures and just set up a relationship between the coins table and the pictures table, and when you open the coins file, you can see the pictures without even having to open that file.

I know you're right and I appreciate there are technically better solutions. One day I might move over to a database with tables as you suggest. However, yesterday I spent time putting my early Victorian copper pennies into the new spreadsheet, with obv & rev pics for each, and I have to say I'm really pleased with the result. I set the size of each 'comment' image at 12cm x 12cm and, with 1200dpi resolution, each image file is around 300k. This is larger than needed but means I have a hi-res version of the image available too. I haven't experienced any slowing down but, if in future I do, I'll simply split the spreadsheet into sections.

It might sound complicated Rob, but once you've done a few it takes literally seconds to add each image. The much longer task is the scanning!

[Peckris 579]

Very cool!

Watch out for the size of your file though, every 2Mb pic you stick in will increase the file size by 2Mb, and if you've got 2000 coins...

Not only that, the program will slow down exponentially once the file size goes over a certain size.

This is the great advantage of databases. You can have a related table of pictures and just set up a relationship between the coins table and the pictures table, and when you open the coins file, you can see the pictures without even having to open that file.

I know you're right and I appreciate there are technically better solutions. One day I might move over to a database with tables as you suggest. However, yesterday I spent time putting my early Victorian copper pennies into the new spreadsheet, with obv & rev pics for each, and I have to say I'm really pleased with the result. I set the size of each 'comment' image at 12cm x 12cm and, with 1200dpi resolution, each image file is around 300k. This is larger than needed but means I have a hi-res version of the image available too. I haven't experienced any slowing down but, if in future I do, I'll simply split the spreadsheet into sections.

It might sound complicated Rob, but once you've done a few it takes literally seconds to add each image. The much longer task is the scanning!

The good news is that database managers such as Access or FileMaker will both import Excel spreadsheets into a table once you've defined the fields. So none of your XL work will be wasted.

[azda 981]

If you can post the (for dummies) version accumulator

Actually it's really easy, just very well hidden for some reason.

This is what you do:

1. Choose the cell that you want to activate the coin pic. when you mouse-over (if you want separate pics for OBV & REV just use two cells, one for each)

2. Right click on that cell then choose 'insert comment' from the drop down menu

3.The text box which pops up may contain some dummy text which you can just delete.

4. Place your mouse pointer over the BORDER of the box, right click and choose 'Format Comment' from the drop down menu. This is the important bit, because if you right click over the centre of the box you won't get the option to add a pic later!

5. In the Format Comment window that pops up, click on the 'Colours and Lines' tab then click on the down arrow next to Fill Colour to bring up the colours and fill effects. Click on the 'Fill Effects' button at the bottom.

6. In the fill effects window which pops up, you will have a 'Picture' tab which allows you to browse and select a picture from your computer.

7. Finally, you may need to close the Fill Effects window and go back to the Format Comment window to set the size and aspect ratio of the pop-up pic. You can also add some text if you like too.

Once you have done the above, a small red triangle will appear in the corner of the cell to show that a pic is available. Very neat!

And that's it. Sounds complicated but it's really not.

I shall probe this over the weekend

[SionGilbey 1]

Hmm... Is there any similar way for Access?

I can only attach pictures as objects that you have to click on and open in the default program where you use it.

I'd rather have a mouseover or immidiate view.

Anyone know how?

[SionGilbey 1]

Oh, and isnt the large file size slowing down the program a RAM issue?

[Colin G. 147]

Hmm... Is there any similar way for Access?

I can only attach pictures as objects that you have to click on and open in the default program where you use it.

I'd rather have a mouseover or immidiate view.

Anyone know how?

If you insert the file into the table you can choose whether it displays the icon or the picture. If you then create a query based on all the fields (including the pics) and sort by date for example. Then base a form on that query, you can get the image to display automatically.

Send me an e-mail and I will send you a screenshot back

[Peckris 579]

Hmm... Is there any similar way for Access?

I can only attach pictures as objects that you have to click on and open in the default program where you use it.

I'd rather have a mouseover or immidiate view.

Anyone know how?

You have an Access table (file) of pictures with an associated unique Coin ID for each picture. You also store that same unique ID against each coin in your main table. Finally, you establish a relationship between the two tables based on Coin ID, and simply set up a picture field that brings in the relevant picture based on the relationship. It sounds more complicated to explain than to set up.

Oh, and isnt the large file size slowing down the program a RAM issue?

Yes - the more RAM you have, the less the problem will manifest, but Excel itself probably has an overhead position where it will start to slow down on very large files, but I have absolutely no idea what that size is.

Edited February 22, 2011 by Peckris

[declanwmagee 122]

Yes - the more RAM you have, the less the problem will manifest, but Excel itself probably has an overhead position where it will start to slow down on very large files, but I have absolutely no idea what that size is.

I try and keep my stupidly complex Excel file under 20Mb, from bitter experience. Oddly, it's things like conditional formatting and large areas of unused space that take up the room, not what you'd expect. I can fill as many cells as I want with lengthy formulae, unwieldy nested IFs and INDEX and MATCH functions, but try and make it look pretty and you can run into trouble quickly!

Oh, and that's with 4Gb of RAM...

Edited February 22, 2011 by declanwmagee

[SionGilbey 1]

Hmm... Is there any similar way for Access?

I can only attach pictures as objects that you have to click on and open in the default program where you use it.

I'd rather have a mouseover or immidiate view.

Anyone know how?

You have an Access table (file) of pictures with an associated unique Coin ID for each picture. You also store that same unique ID against each coin in your main table. Finally, you establish a relationship between the two tables based on Coin ID, and simply set up a picture field that brings in the relevant picture based on the relationship. It sounds more complicated to explain than to set up.

Oh, and isnt the large file size slowing down the program a RAM issue?

Yes - the more RAM you have, the less the problem will manifest, but Excel itself probably has an overhead position where it will start to slow down on very large files, but I have absolutely no idea what that size is.

I've sorted the Access problem out with Colin now so no need to worry.

Your method sounds interesting though and I'll try it out when I have some time.

Also, with Access you can store the images outside the database rather than integrating them therefore there isn't any of that problem.

[Peckris 579]

Also, with Access you can store the images outside the database rather than integrating them therefore there isn't any of that problem.

Yes that's true. But since I was already a dab hand with FileMaker, it made sense - when I made scans of my coins - to put them into a FM table rather than dump 'em into some folder (and the FM table is password protected too).

[Rob 3,185]

I can see that if you are only recording a fixed number of data points per item, then Access has to be easier due to its versatility for searching, but in my instance I found it easier with Excel to add in new data due to a variable number of data points for any single coin.

Take the example of the unique D23 Worcester 2/6d (Brooker 1153 for those in the dark). I have 14 references to this coin in sales or lists over the past 140 years for which said number may well be increased if I can find the lot bought by the owner who sold it in 1872. Those 14 (or potentially more) individual sales would each require a field if I am to be able to search using the sale date. Many individual coins will only have a single sale reference, but I will still have an image for it in just the same way as I do the coin with the long provenance. Add in the concordance for the various (numismatic) references through the ages which may well number 8 or 10, a brief description of the defining points of the variety, any notes of interest for the coin and you are looking at a Access database that is say 30 fields wide. This is unwieldy compared to what I currently have in place which has only 6 fields - Date, Reference, Variety Identifier, Metal/Mintmark/Moneyer (depends on coin type), Provenance and finally notes of interest such as unique/ so many known/ no of die varieties known for the generic design etc. This isn't searchable, but you would never have to look far as any systematic entry will automatically be in the correct denomination file and listed under the correct monarch or any other chronological attribute even if not dated. So for example, an Exeter C6 will always be cunningly interposed somewhere between the last entry for a C5 and the first C7. It is the guaranteed inconsistency in the number of fields required that made me use Excel and condense them into a single box for multiple data points of the same feature.

The only way I can see that this would be improved is if I had some means of sorting the group of coins of an individual die combination into the best known down to the worst, but that isn't a problem until you have a few dozen or more examples imaged.

[SionGilbey 1]

I agree that if I had a more specialist smaller collection I would use excel but I am covering a pretty wide area so Access seems the best choice.

Also I haven't really researched into varieties yet.

[Peckris 579]

I can see that if you are only recording a fixed number of data points per item, then Access has to be easier due to its versatility for searching, but in my instance I found it easier with Excel to add in new data due to a variable number of data points for any single coin.

Take the example of the unique D23 Worcester 2/6d (Brooker 1153 for those in the dark). I have 14 references to this coin in sales or lists over the past 140 years for which said number may well be increased if I can find the lot bought by the owner who sold it in 1872. Those 14 (or potentially more) individual sales would each require a field if I am to be able to search using the sale date. Many individual coins will only have a single sale reference, but I will still have an image for it in just the same way as I do the coin with the long provenance. Add in the concordance for the various (numismatic) references through the ages which may well number 8 or 10, a brief description of the defining points of the variety, any notes of interest for the coin and you are looking at a Access database that is say 30 fields wide. This is unwieldy compared to what I currently have in place which has only 6 fields - Date, Reference, Variety Identifier, Metal/Mintmark/Moneyer (depends on coin type), Provenance and finally notes of interest such as unique/ so many known/ no of die varieties known for the generic design etc. This isn't searchable, but you would never have to look far as any systematic entry will automatically be in the correct denomination file and listed under the correct monarch or any other chronological attribute even if not dated. So for example, an Exeter C6 will always be cunningly interposed somewhere between the last entry for a C5 and the first C7. It is the guaranteed inconsistency in the number of fields required that made me use Excel and condense them into a single box for multiple data points of the same feature.

The only way I can see that this would be improved is if I had some means of sorting the group of coins of an individual die combination into the best known down to the worst, but that isn't a problem until you have a few dozen or more examples imaged.

Forgive me saying so, but you sound like a spreadsheet user who is not really au fait with database subtleties? In the above case, you wouldn't keep your sales reference data in your main table, but in a separate related table with COIN-ID links between the two. In your second table you would have a matrix / array / recurring field (call it what you will) with as many repetitions as you think you may need over time (30 say). You then populate from occurrence 1 onwards, and that's your table.

Then in your main table, you define a portal to the second table in either one of your existing layouts (or create a new layout for it), and there you define exactly how many of the field's occurrences you want to display - i.e. from 1 up to the full 30, or expand as you go. So you don't need those 14 extra columns you'd be stuck with in a spreadsheet, you'd only need how many you decide you want to see, and arranged in whatever pattern suits you, unlike the inflexible grid format of the spreadsheet.

I know how to do this in FileMaker, and believe me, Access will allow much the same.

[Rob 3,185]

Forgive me saying so, but you sound like a spreadsheet user who is not really au fait with database subtleties? In the above case, you wouldn't keep your sales reference data in your main table, but in a separate related table with COIN-ID links between the two. In your second table you would have a matrix / array / recurring field (call it what you will) with as many repetitions as you think you may need over time (30 say). You then populate from occurrence 1 onwards, and that's your table.

Then in your main table, you define a portal to the second table in either one of your existing layouts (or create a new layout for it), and there you define exactly how many of the field's occurrences you want to display - i.e. from 1 up to the full 30, or expand as you go. So you don't need those 14 extra columns you'd be stuck with in a spreadsheet, you'd only need how many you decide you want to see, and arranged in whatever pattern suits you, unlike the inflexible grid format of the spreadsheet.

I know how to do this in FileMaker, and believe me, Access will allow much the same.

You are quite right that I don't know all the ins and outs of databases (or spreadsheets for that matter), in fact I find computers incredibly depressing things to use as they frequently don't give me the answer I want - usually because I don't have enough in depth knowledge of a program.

All I need is something that I can understand and use easily to provide me with the information I am seeking to collate together with an image of the coin which I can compare with an illustration in another catalogue and so record the new coin within an existing provenance, or I can generate another known example of that type. In the case of the example above I know that its provenance is ex E W Wigan (collection bt by Rollin & Feuardent 1872), H Webb 560, J G Murdoch 194, G Hamilton-Smith (1913) 126, K Vaughan-Morgan 336, V J E Ryan 1307, J R Vincent, J G Brooker 1153, 3 x SNC references, A Morris (from Roddy Richardson) and finally me (from Lloyd Bennett). It is no help to have the details neatly tabulated out of view from my perspective as the list of names immediately tells me which coin it is, just as the auction catalogue will have a list of past owners underneath the description. This is why putting all the names into one box works so well for me. Any system that requires a single field entry for each name dismembers the provenance. Having a link to the image allows me to compare a new catalogue reference with an existing provenance.

Edited February 26, 2011 by Rob