 |
|
 |
|
| TheExcelAddict.com | |
| November 28, 2018 | |
| Hi fellow Excel Addict, | |
| Many
people use Excel in ways that others may not
immediately associate with normal spreadsheet use.
But that's why Excel is so awesome. I've always
said, 'With a little imagination you can do ALMOST
ANYTHING with Excel". Something that I use in several of my own workbooks is pictures relating to data in my worksheet. If you have ever tried this, most likely you have run into problems with keeping the pictures in their proper places when sorting and filtering your data. Today, in my 'Excel in Seconds' tip, I want to share with you a few tricks that will help you avoid the most common problems people face when sorting and filtering with pictures in Excel. I always appreciate it when you share my tips so, if you find this tip helpful, please share is with other Excel users that you know. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com  |
|
|
|
|
 |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Secrets For Sorting and Filtering With Pictures | |
Have you ever added pictures to a worksheet and tried to sort or filter them? Did you end up with a big mess? Well, you're not alone.  Today I'm going to show you the secrets you'll need to allow sorting and filtering pictures along with your data. Sorting With Pictures When you insert a picture in Excel, it is anchored by its top left corner to the cell beneath. To ensure that your pictures sort along with the data in the same row, always ensure that the top edge of the picture does not extend above the top edge of the row you want it to sort with. Since pictures are anchored to cells by their top left corner, it is technically not necessary to consider the right and bottom edges, however, it is best practice is to resize your pictures so they fit fully within a cell.  Also, you need to ensure that the cells containing the pictures are included in your sort range. For example, if your pictures are in a column to the right of your data, they may not get included in the cells that get sorted. Putting a column header at the top of the pictures column will tell Excel to include that column in the range of cells to sort. Less common is sorting by columns (i.e. horizontally). If you want to sort your pictures by column, always make sure the left edge of the picture does not extend past the left edge of the column you want it to sort with.  Filtering With Pictures One of Excel's default settings for pictures is 'Move but don't size with cells'. When you filter data in Excel, rows that don't meet your filter criteria are hidden — that is, rows are resized to a height of 0. Therefore, pictures anchored in these hidden rows will not be resized and will still be visible and maybe covering some pictures that shouldn't be hidden.  To ensure that these pictures are resized and hidden along with any filtered rows, select the picture (or hold down the SHIFT key to select multiple pictures), right-click and choose Size and Properties... Click the Properties> category and choose the 'Move and resize with cells' option. Now when you filter your data, any pictures in rows that are hidden rows will also be resized to zero and won't be visible. Easier but not foolproof Using these secrets should solve the biggest issues most Excel users have with sorting and filtering pictures. But caution needs to be exercised whenever using pictures that need to be moved with their related data. It's so easy to accidentally move, resize and delete pictures. If you have important workbooks containing pictures that you sort and/or filter, I recommend that you make frequent backups. |
|
|
|
|
Share this tip. Click on one of the options below.. |
|
| 'Excel in Seconds' with The Excel Addict | |
| Secrets For Sorting and Filtering With Pictures | |
|
|
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |