Do you ever put a picture or clip art on a spreadsheet? I don’t use them very often, but occasionally I’ll add a small picture on an instruction worksheet, or insert a company logo on a printable form.
If you do add pictures, do you fuss with them, or just slap them on the sheet, and leave them as is? I usually adjust the picture’s size, so it fits in the spot where I want it, and maybe crop a little off the sides.
Remove Picture Background
A couple of weeks ago, I saw John Walkenbach on Google Plus, showing how to remove the background from a picture in Excel. It looked interesting, so I decided to test that feature on one of my own pictures.
NOTE: The Remove Background feature was introduced in Excel 2010, so you can use it in that version too.
- First, I inserted a picture onto the worksheet, and selected the picture.
- On the Ribbon’s Format tab, I clicked the Remove Background command
- The background changes to purple, and those are the sections that will be removed.
- There is a rectangular outline around the “Keep” section, and you can adjust that, to include or exclude more of the picture
Adjust the Sections
To adjust the sections that will be removed, you can use the tools on the Background Removal tab.
- For example, if a section is coloured pink, and you want to keep it, click the Mark Areas to Keep tool, then click on that part of the picture.
- If you change your mind, use the Delete Mark tool to remove the marks that you added.
Keep the Changes
When you’re satisfied with the background markings, click the Keep Changes command, to see the result.
And remember, you can put pictures in cell comments too, where they only appear if you point to the cell.
Excel Book Giveaway
That’s just one of the 101 tips in John Walkenbach’s new book – 101 Excel 2013 Tips, Tricks & Timesavers.
You might already know a few of the tips, but there must be a few things that you don’t know – or you used to know, and forgot!
For example:
- Display a live calendar in a range
- Count non-duplicated entries in a range
- Use Flash Fill to extract data
- Perform inexact searches
Katie Mohr, at Wiley, has kindly provided a ebook copy John’s book for a giveaway. If you’d like to win a copy of the book, add a comment below.
- In your comment, share one Excel tip that you would include, if you were writing a book of 101 Excel tips.
- Include your email address, so Katie can contact you if you win. Your contact information won’t be publicly visible, and it won’t be used for any other mailings.
- The deadline is Wednesday, July 24, 2013, at 12 noon Eastern Daylight Time.
- One entry per person.
- The winner will be selected in a random draw, and announced here on Thursday, July 25th.
_______________
A couple tips for you:
1) Data validation doesn’t have to point to cells in the current sheet. You can just hard-code a comma-separated list right in the dialog box when you set up validation (Ex. Choice 1, Choice 2, Choice 3). For many drop down uses, it is better to just type in the list than to put it in cells.
2) Use Ctrl+ArrowKeys to navigate a large table. Use Ctrl+Shift+Arrow to select a large table. Ctrl+Arrow will start in the current cell and go to the next blank cell in the current region. There are few ways faster to select an entire table than Ctrl+Shift+DownArrow followed by Ctrl+Shift+RightArrow (starting from the upper left cell). I cringe every time I watch a co-worker scroll with the mouse… you know when you scroll down and it takes forever to get to the end, and then Excel keeps on going and you pass the end, so you have to scroll back up again… It is so much slower and frustrating than these simple keystrokes!
3) If you select a certain range of cells frequently and paste into other places, help yourself by grouping cells around it. If my table is in cells B2:H25, I will merge cells A1:A25 and merge cells B1:H1. Then selecting the table is as simple as clicking in cell A1 and pressing Shift+RightArrow! This is great for pasting quickly into email or PowerPoint.
The problem with sharing your favorite tip is you do it so often you assume everyone already knows it. Time saver for me is to save my Excel workbook as binary when the size gets over 10,000 KB. Speeds up opening and saving and I have not found any drawbacks.
Shift space bar to select a row
Control. space bar to select a column
Control minus (on keypad) to delete the selected row or column
Control plus (on keypad) to insert a row or column at the selection point
One of my favorite tips is to really hide a tab on a spreadsheet. You may know about how to hide a spreadsheet with the right click > Hide option. But if someone know about this they can just right click and Unhide the tab. The really, really hide a tab use the Developer tab and click the Visual Basic icon to bring up the Visual Basic Editor. Select View > Properties Window. Select the tab (sheet) you really want to hide. In the Properties Window select the dropdown for Visible and select xlSheetVeryHidden.
This will really hide the tab (sheet). Video example here http://youtu.be/ixLWQAfqpiY
Well my favourites tend to vary but one I really like (excellent for entering the same information multiple times). Press down the Ctrl key, click on the cells you want to add the entries to, when you come to the last cell, type in the entry you want to duplicate, press Ctrl and Enter and it adds it to all of them. 🙂
Great tips! One I love for people who work with a lot of data is using the Text to Columns feature. For example, if I want to break up a column of names formatted as First Name,Last Name, I first make sure the column is all the way to the right (or has several blank columns to the right so there is a place for the data to go). I then highlight the column, select Data – Text to Columns, and then follow the prompts to pick a delimiter (in this case a comma). The preview shows you that it will put the first names in one column and the last names in the next column, using the comma as the place to break out the text. there might be a little data cleanup to do but it is certainly better than breaking them all out manually!