How can I filter a roster to show people assigned to any of several shifts?
Quick answer
Treat the selected shifts as alternatives: include a person if their shift is Morning OR Evening OR Overnight. In Excel Advanced Filter, put each alternative on a separate criteria row; conditions on the same row require all listed conditions to be true. Add any required conditions, such as a team, to each alternative row. Advanced Filter does not automatically update when criteria values change, so run it again after changing them. [S1]
The question, the details, the next step.
I have a roster with a Shift column and want to see everyone assigned to either the morning, evening, or overnight shift. What should the filter mean?
The word “either” means an inclusive OR: keep a person if their Shift value matches Morning, Evening, or Overnight. They do not need to match all three. A person assigned to a different shift should be left out.
For example, if the roster has columns Name and Shift, these rows should remain:
- Ari | Morning
- Bo | Evening
- Cam | Overnight
Ari does not also need an Evening or Overnight assignment to qualify. This example assumes each person has one shift in a single row. If people can have multiple rows or shifts, decide whether you want matching assignments or unique people before filtering.
I’m using Excel Advanced Filter. How do I lay out the criteria so it includes any of those shifts?
Make a criteria range with the shift-column heading, then list each accepted value on its own row:
Shift
Morning
Evening
OvernightIn Excel Advanced Filter, criteria on separate rows allow a match to any listed condition. By contrast, criteria on the same row require all listed conditions to be true. For your one Shift column, the three alternatives belong on separate rows, not together as simultaneous requirements. Advanced Filter is documented for the Excel editions listed in Microsoft Support, including Microsoft 365 for Mac and Excel 2016 through 2024. [S1]
Select the roster and criteria range, then use Advanced Filter to filter the roster in place or copy matching rows elsewhere. [S1] This example assumes your roster has a single Shift field containing the values you want to match.
What if I have another condition too, like only showing people from the Support team?
Combine the logic in two steps: a person must be on Support AND must have at least one of the selected shifts. Put Team and Shift conditions together on each criteria row, and put the shift alternatives on separate rows. For example:
Team Shift
Support Morning
Support Evening
Support OvernightEach row is one acceptable combination: Support with Morning, Support with Evening, or Support with Overnight. A row qualifies if it matches any one of those combinations. [S1] If you add more required conditions, include them in each alternative row so every combination states the full requirement.
How can I make sure I haven’t accidentally excluded people or included the wrong ones?
Compare the filtered Shift values with the exact alternatives you intended. Check a few known rows: one for each wanted shift and one for a shift that should be excluded. If you copied results elsewhere, check that the destination contains the records you expected.
One limitation matters: Advanced Filter does not automatically update when criteria values change. [S1] If you change the accepted shifts or roster data, run the filter again and verify the refreshed result. This guidance covers Excel Advanced Filter’s criteria logic and update behavior; it does not establish that every roster layout or other spreadsheet app works the same way.
A practical checklist
Decide whether the rule is “any selected shift” (OR) and note any additional required conditions (AND).
Use a criteria range for the shift alternatives.
Put each acceptable shift on a separate criteria row.
Repeat required conditions, such as Team = Support, on every alternative row.
Check sample included and excluded records, and rerun Advanced Filter after changing criteria or data.
A few more useful answers
What is the difference between OR and AND in this filter?
OR means any one listed shift is enough to include the row. AND means every condition in that criteria combination must be true, such as Team = Support and Shift = Morning. [S1]
Will the filtered results change automatically when I edit the criteria?
Excel Advanced Filter does not automatically update when criteria values change. Run it again after changing the criteria, then check the results. [S1]
Can I use this exact setup in another spreadsheet app?
This example is for Excel Advanced Filter. The guidance here does not establish that the same criteria layout applies in another spreadsheet app.
Sources
References used for this guide.
- [S1] Filter by using advanced criteria — Microsoft Support support.microsoft.com · Checked