If you find any post useful then, please do share with others. Thanks!

Popular Posts


Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Tuesday, September 2, 2014

Interactive Image in Excel

We can make an interactive image in MS Excel. This is explained below step by step.
Interactive Image means, changing of image by changing data of a particular cell.
Suppose If I select or write person A’s name in cell then his/her picture should show up in the corresponding cell and if type the name of Person B then picture should change to of that person.

We will use the options of Camera, Data Validation & Define Name.
We will use the formulas of Vlookup, Indirect & Concatenate.

Add Camera Option to the Quick Access Toolbar
Go to File Menu bas (Excel 2010)
Click Options
Click/Select Quick Access Toolbar from left bar
Choose All Commands from the drop down for Choose Commands form
Select Camera option from the list under All Commands
Now Add the Camera option to the Quick Access Toolbar
Click Ok
Now, we can find the Camera option in the Quick Access Toolbar

Insert/Export the required Images to Excel
We should have the images with us on our system that we want to use in our Excel file. Please follow the below steps to insert images.
Click on Insert Button and then Picture
Locate the Picture one by one and insert in Excel
Re-size the width and height of all the pictures by dragging the edge of image.
The first picture should be in Cell A1 and the second in Cell A2
We need to ensure that image fit in one cell, by setting the column width & row height of the cell.
Rename the sheet as Image.
Now making the Interactive Images
Go to second/next sheet and name it as Interactive Image.
Go to Cell A1
Click on Data tab & then Data Validation.
Select List from the Allow drop down
Type Pizza & Bread in source box & Click OK
Now we have drop won option in Cell A1, select there the first one ‘Pizza’.

In the cell Y1 type Pizza & in Z1 1 and in Cell Y2 Bread & in Z2 2. We will use this data in our formula.

Use shortcut Ctrl+F3 (then name manager box appears)
Click New (then New Name window appears)
Type Name as "Image"
Enter the below formula in the Refers to box 

=INDIRECT("Image!A"&VLOOKUP('Interactive Image'!$A$1,'Interactive Image'!$Y$1:$Z$4,2,0))

Go to Cell C1
Click Camera option from the Quick Access Toolbar (then you get a cross symbol as cursor)
Drop that cross symbol somewhere in the visible Cell (then excel displays image showing G1 Cell content)
Change the formula to =image from =$C$1 (once you select the image you can find the formula in the formula bar)

Now check that changing the Input in Cell A1 (Where Data Validation was done) will change the image that we have placed in/near cell C1.

No comments:

Post a Comment