May 31, 2016
Hi fellow Excel Addict,
Do you know that there are dozens of time-saving Excel features 'right under your nose' that you may not even be aware of?
Today's 'Excel in Seconds' tip is a great one that Heidi only recently discovered even though it has been a part of Excel since 2010.
Has this happened to you too? What are some of the best hidden tips you have uncovered in Excel?
Excel in Minutes
Sorry, last week was a little hectic for me after spending the long weekend in Halifax, so I didn't publish my 'Excel in Minutes' tip last Thursday. The last tip I published was 'Open the Last-Saved Version of an Open Workbook'. If you missed it, you can find it here.
If you have a suggestion for a tip you would like me to include in a future newsletter or have a favourite tip that you'd like to share, please send it along to me.
Wishing you an AWESOME week,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
Filter Your Data for Unrelated Items
I'm sure all Excel users have experienced this at one time or another. After working with Excel day in and day out, you suddenly notice a feature that has been 'right under your nose' for years.
I received an email from Heidi last week about a recent revelation to her. Maybe it will be a revelation to you as well.
Beginning in Excel 2010, there were a couple of great new options added to Excel's autofilters, tables, Pivot Tables and Pivot Charts. Two of these options, 'Search' and 'Add current selection to filter" have transformed the way we filter our data. These features give you the ability to quickly and easily filter multiple unrelated items into a single listing even if you are working with large data sets.
For example, if your data contains a list of cities that you want to filter down to 5 specific cities, you can't do that with regular autofilter or even a Custom Filter. You could use Advanced Filter, but using the 'Search' and 'Add current selection to filter' options is often easier.
Checking off individual items in autofilter is pretty simple but for larger lists it is often faster to use the Search box rather than scrolling and looking for items in a long list. Also, for partial text matching, combining 'Search' with 'Add current selection to filter' makes this process so much faster and more versatile.
For partial matching you just begin typing in the Search box and the list is instantly narrowed down to only the items containing your search characters. If there are items in the list that you don't want to include in your filter, you can simply uncheck those items. Then click OK to apply the filter to your data.
If you want to include additional items in your existing filtered list, start typing again in the Search box and when you have narrowed down the list, before clicking the OK button, click the 'Add current selection to filter' option. Now, when you click OK, the items matching your second search will be added to the previously filtered list. You can repeat this process over and over until you have all the items you want.
Don't forget to select the 'Add current selection to filter' option each time or you will lose your previous selections. If you accidentally forget, you can simply click Undo (CTRL+Z) to restore your previously filtered data. It would have been nice if Microsoft had made this option 'stick' until you unchecked it.
Note that the 'Add current selection to filter' cannot be used with the 'Filter by Color' and 'Text Filters' options.
| 'Spreadsheets Tips
Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630