This guide will discuss how to filter dates by year in Excel using the filter tool.
Due to the multiple built-in functions and tools, Excel is a popular tool for storing, organizing, and manipulating data. Essentially, we can apply several criteria to our data set in Excel. However, we will focus on filtering the dates in our data set by year.
Furthermore, this is not a difficult task at all. Since there is a built-in filter tool in Excel, we can easily filter dates by year in Excel. And this allows us to easily see certain data values within a large data set.
Let’s take a sample scenario wherein we must filter dates by year in Excel.
Suppose you are an employee at a company tasked with creating a sales report containing data from the last three years. And you want to be able to show data from specific years at a time. Luckily, you easily did this by applying a filter to your data set.
Now you can simply filter the data to only show the rows or values containing the specific year you choose.
Great! Now let’s move on and dive into a real example of filtering dates by year in Excel using the filter tool.
A Real Example of Filtering Dates by Year in Excel
Let’s say we have a data set containing two columns. Firstly, we have the first column showing the date of sales from the years 2020 to 2022. Then, we have the second column, which has the number of sales. So our initial data set would look like this:
We want to be able to filter the data set by year so we can show only data from a specific year. Fortunately, this can easily be done by simply applying a filter to the data set.
So the filter tool in Excel allows us to temporarily hide some rows of data in a table to focus on or only display certain data we want to see. Essentially, the filter tool only displays filtered rows of data that meet the criteria or conditions we specify.
Then, the filter tool hides the rows of data that do not meet the criteria or conditions we set. Basically, it hides the rows of data we do not want to see at the moment.
After we filter the data set, we can now copy, edit, find, chart, print, and format the subset of filtered rows of data without rearranging or moving the data.
Additionally, we can also filter by more than one column. Since filters have an additive nature meaning each additional filter we add is based on the current filter and continues to reduce the subset of data.
Hence, we can easily filter dates by year by applying a filter to our data set. To do this, we simply go to the Data tab and apply the filter tool. So our data set will have a dropdown filter found at the header.
Then, we can filter the data to only show a specific year by clicking the dropdown arrow and checking the box beside the specific year we want to display. If we want to display rows of data from two years, we can simply check two boxes of years to display them.
Furthermore, we can format our range of cells into a table to automatically apply a filter. Then, we can repeat the same process and check the box for which year we want to display.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can explain the process of how to filter dates by year in Excel using the filter tool.
How to Filter Dates by Year in Excel
In this section, we will discuss the step-by-step process of how to filter dates by year in Excel using the filter tool. Furthermore, each step contains detailed instructions and pictures to guide you along the process.
To apply this to your work, simply follow the steps below.
1. Firstly, we will select our entire data set, which consists of two columns. Then, we will go to the Data tab and click Filter, which is found in the Sort & Filter section.
2. Secondly, we now have a filter applied at the first row of each column. And we can confirm this by the appearance of a dropdown menu beside the first rows. Next, we can begin to filter the dates by year. To do this, we can simply click the dropdown arrow beside the Date column.
In the dropdown menu, we will check the box beside the year we want to display. In this case, we want to show only rows of data from 2021. Lastly, we will click Apply to apply the changes.
3. And tada! We have successfully filtered dates by year in Excel using the filter tool.
4. Additionally, we can filter dates by multiple years. To do this, we can simply click the arrow beside the Date column. Then, we will check the boxes of the years we want to display. In this case, we will check 2021 and 2020. Lastly, we will click Apply to apply the new changes.
5. And tada! We have filtered dates by multiple years in Excel using the filter tool.
6. Moreover, we can automatically apply a filter to our data set by converting it to a table. To do this, we will select the entire data set and go to the Insert tab. Next, we will click Table in the Tables section.
7. In the Create Table window, we will check the box My table has headers and click OK to apply the changes.
8. Now, we automatically have filters for our table. Then, we can repeat the same steps. So we will click the dropdown arrow and check the box for the specific year we want to display. In this case, we will check the box for 2020. Finally, we will click Apply to display rows of data from 2020.
9. And tada! We have filtered dates by year in Excel by converting the data set into a table.
And that’s pretty much it! We have discussed how to filter dates by year in Excel. Furthermore, we explained two simple and easy methods to do this. Firstly, you can use the filter tool, or you can convert the data set into a table. Now you can choose any of the methods and apply them to your work.
Are you interested in learning more about what Excel can do? You can now use the FILTER function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
