In this month’s Office Q&A, Jean asks how to filter records by multiple values, and I introduce her to Excel’s advanced filter feature. In PowerPoint, Andrew is working harder than necessary because his co-workers don’t know how to use placeholders. I can’t help Andrew train his coworkers, but I can help him work more efficiently.
I’m using Office 2016 on a Windows 64-bit system. Excel’s advanced filter feature is available in the .xls format, but this article doesn’t supply step-by-step instructions for that version. For your convenience, you can download the demonstration .xls or .xlsx file.
Excel’s advanced filter
Jean wants to filter a simple data set by two columns. She learned quickly that the built-in Filter feature can’t do that, so I explained how to use an advanced filter. Figure A shows a sample data set that’s similar to Jean’s. I’m working with a Table object, but you don’t have to convert an existing data range to a Table to use an advanced filter.
Figure A
TeamA and TeamB share many of the same teams.
Jean wants to filter by the TeamA and TeamB for specific teams. An advanced filter requires a bit of setup:
- A data range.
- A criteria range.
- A copy to location if you want to work with the filtered data set independent of the original data. You can also filter in-place.
We have the data range (the Table in Figure A) already. You can position the criteria range anywhere you like, but above the data set is a convenient spot. Figure B shows the new criteria range, C1:E3. A criteria range can comprise one column or all of the columns in the data range. The heading is required and the labels must match those in the data range.
Figure B
Add a criteria range.
Your needs will define this range. Jean wants to include both team columns and she wants any record that includes the specified team (criteria value) in TeamA or TeamB. That means the criteria range needs a heading row and two value rows to express an OR evaluation. (The formatting and the AND and OR labels to the left are unnecessary but they visually support what we’re doing.)
SEE: How to remove unwanted apps from Windows 10 (even though Microsoft doesn’t want you to)
When you enter filtering values in the same row, Excel performs an internal AND evaluation. We can best illustrate this result by applying the appropriate filter as follows:
- Enter a filter value in the appropriate cells. In this case, enter Franklin in both C2 and E2.
- Click anywhere inside the data set. Excel will assume the range using the first adjacent blank rows and columns.
- Click the Data tab and then click Advanced in the Sort & Filter group. In Excel 2003, choose Filter from the Data menu and then select Advanced Filter. The feature defaults to filtering in-place; don’t change that option for this example. The default data range (List range) is correct, so you don’t need to change it.
- You do need to specify the criteria range, which you can do by clicking inside the control and then selecting C1:E2 manually or entering the range address (Figure C). Notice that I did not include row 3. This is an AND filter–we want to consider only the filtering values in row 2.
- Click OK to see the filtered results shown in Figure D. Excel hides any record that doesn’t meet the criteria when you filter in-place. Only one record contains Franklin in both TeamA and TeamB.
- To clear the filter, click Clear in the Sort & Filter group.
Figure C
Specify the filter options.
Figure D
Filtering in-place hides records in the original data set.
Knowing how to express an AND filter is great, but Jean wants to see all records that have the specified team in either column, not only both. For this, she needs an OR filter. To accomplish an OR filter, we need to move one of the filtering values to row 3 and update the criteria range accordingly. Delete Franklin in E2 and enter it in E3. Then, reset the options as follows:
- Click anywhere inside the data range.
- Click the Data tab and then click Advanced in the Sort & Filter group.
- In the resulting dialog, update the criteria range to include row 3 (Figure E).
- Click OK to see the results shown in Figure F.
- Click Clear when you’re ready to remove the filter.
Figure E
Modify the criteria range to accommodate the OR filter.
Figure F
The OR filter displays several records.
If you don’t want to work with the original data range, use the in-place option. Simply specify a new location (including another sheet) when specifying the options–something we didn’t do in Jean’s example.
Excel’s advanced filter is flexible. You can include multiple columns and rows in the filter. Keep in mind that values in the same row find records where both criteria values are found; criteria values in different rows displays records where either value is found.
SEE: How to add real-time audience responses to a PowerPoint presentation
PowerPoint placeholders
After reading How to use picture placeholders for easy placement and cropping in PowerPoint, Andrew asked about pasting pictures into placeholders. Some of his coworkers don’t use the placeholders and simply insert them and then resize them. He spends too much time hunting for the original pictures files and inserting them himself after the fact.
I wish I could help Andrew with his coworker problem–it’s easy to insert a picture into a placeholder and there’s no resizing afterward! As you can see in Figure G, the instructions are on the placeholder–simply click and the interface walks you through the task. And if you’re using the Insert option instead, PowerPoint still uses the placeholder (in the latest versions). Perhaps some simple training with his coworkers might help Andrew avoid the extra work, but that’s easy for me to suggest–harder for Andrew to implement.
Figure G
The placeholder displays instructions.
I was able to help Andrew a little though. Once the picture’s in PowerPoint, you can right-click the picture, choose Save As Picture to save the file locally, and then insert it into the placeholder as originally intended. If you don’t need to save the file locally, you can right-click the picture and copy it to the Clipboard by pressing [Ctrl] + C. Then, select the placeholder and press [Ctrl] + V to paste it into the placeholder. (I was unable to get this to work in PowerPoint 2007.)
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Also read…
- How to use PowerPoint 2016’s stunning new Morph transition
- How to link a single slicer to two or more Excel PivotTables
- How to use built-in Excel features to find duplicates
- Office Q&A: How to create Outlook reminders and build a new profile
