Select the picture. On the Picture Tools Format tab, there is a new Transparency slider. Choose one of the thumbnails to adjust the photo transparency. At 50% transparency, you can start to make out the words behind the photo. I'm using Windows 7 with Excel 2010. We have an Excel form to all users to fill in the data but don't allow to make any change (edit/delete/resize) to the attached image in the header's cell. I heard that I need to use VBA to control it but I don't know how to use it. Please kindly advise.
How can you lookup different pictures based on the value of a cell?
Just to give you a heads up. This is a VBA-free tutorial!
We saw the dynamic picture lookup in action towards the end ofthis video, right here.
You can apply this concept to anything where you need dynamic pictures.
For example:
- Company logos
- Employee pictures
- Flags
- Houses
- Report headers…
Basically anywhere where you'd like the picture to change once the value of a cell changes.
Here's our Picture Lookup Challenge
We'd like to select the country from our drop-down list,
and we'd like the image – i.e. the flag for the country to reflect our selection:
Let's get started…
Step 1: Organize the pictures in separate cells
Make sure each single image is 'inside' its own cell. It should be surrounded by the cell borders. You'll see why in a bit…
Adjust the row height so your images have enough space. To make sure each image is inside its own cell, you can select the images – Shortcut key Control + A selects all images (make sure you click on an image first before you use the shortcut key, otherwise you select all the cells instead).
Now use the options inside picture tools to align the images properly. You can for example, align them to the left side and distribute them vertically. For proper vertical distribution, make sure the first and the last image sit correctly in their cells. The rest will be proportionally distributed.
Now for the data validation:
Go the cell where you'd like your drop-down to be. Then go to:
Data / Data Tools / Data Validation
Select List from the Settings tab. For data source, select the range where you have your list. In my example that's:
Step 2: Assign Names to cells
Go back to your images. Each cell with an image needs a name. It's easiest to assign the text you use for your list as the cell name.
For example cell A3 should get the name 'Germany', A4: 'France' and so on.
You can do this the slow way, by selecting each cell and then typing the name inside the name box. Or you can do it the fast way:
Highlight the image cells and your names – in this example A3:B8. Go to:
Formulas / Defined Names / Create from Selection – select right column.
The names are created automatically – so now the text inside B3 is the name of the cell for A3.
Think of names as bookmarks. You have now book marked cell A3 to be 'Germany'. Every time you type in Germany inside the name box, you jump to cell A3 on the Master tab.
Step 3: Copy one of the pictures and make it the placeholder image
Now click on one of the flags (doesn't matter which one) and copy it.
Go to the place you'd like to have your dynamic image and paste it there. This is now your placeholder image. It's not dynamic yet, but it'll be soon.
Step 4: Assign a name to INDIRECT formula
The INDIRECT formula is the perfect formula to handle this step.
Why?
Because INDIRECT, indirectly gives you the right cell address.
What does that mean?
Whichever cell reference you give to INDIRECT, it tries to translate the text inside the cell to an address. If you type A6 inside cell B2 and write a formula like this: Bluestacks latest version 2019.
=INDIRECT(B2)
You get whatever is inside cell A6.
Why?
Because INDIRECT uses the text it sees inside cell B2 as an address – i.e. as the new cell reference. Go to cell A6 and type in any text. Now your INDIRECT formula which is referencing B2, is returning what is inside A6. This can be a bit confusing…
Find out more about INDIRECT in this post. You need to think a little outside the box here. Just make sure you're in the right mood.
Now let's finalize Step 4. Go to Name Manager, click on new and type in this formula:
It should reference the cell where you have your data validation list. Give it a name. In this example I called my formula 'Flag'.
Step 5: Link this name to the placeholder image
Edit Picture In Excel
Now comes the last step:
Call of duty 2003 online. Use this new name as a link for your placeholder image:
Click on the image first – then immediately go to the formula box and type in
(The name you used in name manager for your INDIRECT formula)
That's it!
Test it out now. Select another category from your drop-down list and watch your image change!
That's magic done in Excel.
Remember: You can use your pictures tools options to optimize the image. Windows video clip.
You can resize it, crop it, add a frame to it;
Your options are limited to your imagination (and to picture edit tools)….
In the example shown, I've resized it and then cropped out the cell borders from the image.
Feel free to Download the Workbook HERE.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW
Generally, most of us have seen Excel as a plain and monotonous piece of software.
But have you ever thought about how you can use your creativity to create amazing and attractive spreadsheets?
In this post, I am going to share one such technique, here we are going to learn how to add background images in your Excel worksheets.
So grab a cup of coffee, get relaxed, and read on:
Option 1: Adding a Background Image to your worksheet
For Excel 2003
- First of all, open the worksheet where you wish to add the background.
- After this navigate to ‘Format' > ‘Sheet' > ‘Background'.
- Now, browse through all the available images, select the image that you wish to add and click the insert button as shown in the below image.
- This will add the picture that you just selected as the sheet background.
For Excel 2007, 2010 and 2013
- First, open the worksheet where you have to add the background.
- Next, navigate to the ‘Page Layout' tab in the ribbon and click the ‘Background' option.
- This will open a sheet background window, select the image that you wish to use as a background and then click the insert button.
- Now the image that you just selected will be used in the background of your worksheet.
- The resultant will look somewhat like the above image.
Few Important tips about spreadsheet backgrounds
- Try to use contrast colors to make your spreadsheets more readable. For example- if you use a dark color font on a light color background image, it will be easier to read.
- You can also use the shortcut Alt + P G to add an image background to your worksheets.
- The image in your spreadsheet background can increase the overall size of the spreadsheet. So, only select those images that have small sizes.
- You cannot add a background image to multiple worksheets at once. You can only add the background to one worksheet at a time.
- Remove gridlines on your spreadsheets when you are using a background image, this makes them look smooth.
- It should be noted that when you add an image background to your spreadsheet using the above method then it won't show up when you print the sheet. To add printable backgrounds to your spreadsheet use the second option.
How to Remove the Background Image in Excel
To remove the background in the image follows the below steps:
For Excel 2003:
- First open the spreadsheet, where you have already added an image as background.
- Next, navigate to ‘Format' > ‘Sheet' > ‘Delete Background'.
- This will delete the image from the background.
For Excel 2007, 2010 and 2013
- First of all open the spreadsheet, where you have already added an image as background.
- After this, navigate to the ‘Page Layout' tab in the ribbon and click the ‘Delete Background' option.
- Now, the image in the background of your worksheet will be deleted.
Option 2: Adding a Printable image to your worksheet
In this method, we will are not going to add the image as a background but instead, we will add the image on top of another object. And then we will adjust the object so that it fits in the whole printable area. To use the method follow the below steps:
- Open the worksheet where you wish to add your image background. Before proceeding further make sure that you have filled the required data to your spreadsheet.
- Next, navigate to ‘Insert' Tab > ‘Shapes' > ‘Rectangle', and draw a rectangle on your sheet.
- After this, right-click on the rectangle and select the option ‘Format Shape' as shown in the image.
- This will open the ‘Format Shape' options, click the ‘Fill' option, in the ‘Fill' type select the ‘Gradient or Texture' radio button. Then using the ‘File' button select the picture of your choice as the background and set its transparency according to your needs.
- Try to use contrast colors to make your spreadsheets more readable. For example- if you use a dark color font on a light color background image, it will be easier to read.
- You can also use the shortcut Alt + P G to add an image background to your worksheets.
- The image in your spreadsheet background can increase the overall size of the spreadsheet. So, only select those images that have small sizes.
- You cannot add a background image to multiple worksheets at once. You can only add the background to one worksheet at a time.
- Remove gridlines on your spreadsheets when you are using a background image, this makes them look smooth.
- It should be noted that when you add an image background to your spreadsheet using the above method then it won't show up when you print the sheet. To add printable backgrounds to your spreadsheet use the second option.
How to Remove the Background Image in Excel
To remove the background in the image follows the below steps:
For Excel 2003:
- First open the spreadsheet, where you have already added an image as background.
- Next, navigate to ‘Format' > ‘Sheet' > ‘Delete Background'.
- This will delete the image from the background.
For Excel 2007, 2010 and 2013
- First of all open the spreadsheet, where you have already added an image as background.
- After this, navigate to the ‘Page Layout' tab in the ribbon and click the ‘Delete Background' option.
- Now, the image in the background of your worksheet will be deleted.
Option 2: Adding a Printable image to your worksheet
In this method, we will are not going to add the image as a background but instead, we will add the image on top of another object. And then we will adjust the object so that it fits in the whole printable area. To use the method follow the below steps:
- Open the worksheet where you wish to add your image background. Before proceeding further make sure that you have filled the required data to your spreadsheet.
- Next, navigate to ‘Insert' Tab > ‘Shapes' > ‘Rectangle', and draw a rectangle on your sheet.
- After this, right-click on the rectangle and select the option ‘Format Shape' as shown in the image.
- This will open the ‘Format Shape' options, click the ‘Fill' option, in the ‘Fill' type select the ‘Gradient or Texture' radio button. Then using the ‘File' button select the picture of your choice as the background and set its transparency according to your needs.
- Now adjust the image such that it shows in the printable area and then print the sheet.
Cannot Edit In Excel
So, this was all from my side. Hope you would have liked this post. 🙂
Edit Picture In Excel
You May Also Like
