This post will guide you how to filter out weekend or weekday from a list of data in Excel. How do I filter out the data in all Weekends in Excel. How to filter out all data in all weekdays in Excel.
Filter Out data in Weekdays and Weekends
If you want to filter out the data that belong to all the weekends or weekdays in Excel, how to achieve it. You can use a formula based on the WEEKDAY function to return a number 17 for each day of the week. The number 1 indicates that it is Sunday, and the number 7 indicates that it is Saturday. Then you can use the Filter feature to filter out the data based on the week number. Just do the following steps:
#1 Type the following formula in a cell adjacent to the date column.
=WEEKDAY(B2)
#2 press Enter key in your keyboard, and then drag the AutoFill Handle to other cells to apply this formula.
#3 keeping the week numbers are selected, and go to DATA tab, click Filter command under Sort & Filter group.
#4 click Arrow button in week number filed, checked 1 and 7 to filter out all weekends. Or checked 26 to filter out all weekdays. Click OK button.
#5 let’s see the result.
Related Functions

Excel WEEKDAY function
The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
Related Posts
Calculate Cumulative Loan Principal Payments in Excel
How do you Calculate Cumulative Loan Principal Payments in Excel? There are a few different ways to calculate this information, but one way has been proven to be accurate more often than others. One option is to use the CUMPRINC ...
Calculate Compound Interest in Excel
This article will show you how to calculate compound interest in Excel. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest. One of the simplest ...
Count Attendance and Absence with COUNTIF function
In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...
Average Of Numbers With Multiple Criteria In Excel
Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter ...
Calculating Average Of The Numbers
Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...
How to Count Dates by Day of Week in Excel
This post will guide you how to count days of week in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by day of week in Excel. Count Dates by ...