Excel has different ways on how you can be able to show the year alone without having to go through so much hassle. All you have to do is to format the date in excel or use a formula. With that, you will be able to display the year, which is the most important part of the date in your data. Take a look at how it is done.
Showing the year in Excel with Format Cell Function
This is done by formatting the cell that has a full date to yyy. After formatting, you will be able to only show the year in excel. Here are the steps
1. Launch excel and open the worksheet that you want to show the year in excel
2. Select the cell with dates that you want to show the year only.
4. It will open a Format Cells Dialogue box
5. In the Format Cells dialogue box, Click Custom in the Number tab in the Category box. Then type yyyy into the type box.
6. There you go. You have only shown the year in excel.
How to show year only using the YEAR Function
You can also use the YEAR function and be able to display the year. The function extracts the year from the date. Here is the formula
Note: The generic form of this formula requires that the date that you want to change to a year must be in the form that Excel recognizes to be a valid date. The YEAR function also takes just one argument, which is the date that you want t extract the year.
Therefore, if you want to show the year only, you need to follow the following steps.
1. First, Highlight the Date cell and right-click the selection. From the right-clicking menu, select Insert…
2. A new cell is inserted to the left of the Date cell. Name it year.
3. Click Cell B2 and type =YEAR(C2).
4. You can now drag the formula along the cell. Using the filler handle.
5. Once you have dragged the formula to the end of the table. It is now time to get rid of the date cells and remain with the Year only cell. Click the Year cell.
Note: Paste Values means pasting Formula results without formatting or adding comments.
8. You can now delete the Date cell by selecting the Date cell. Right-clicking the selection. From the right-clicking menu, select Delete.
9. There you go, you have only shown the year in Excel.
Note: You can also extract the year from the day entered as text using the formula =YEAR (“1/11/2022”). But, for many computers, the process comes with a lot of challenges causing unpredictable results since computers have different date settings. It is advisable to use the format of supplying addresses to a cell that has already a valid date. You can also format a cell to a custom number format like “yy” or “yyyy” which will show the year only after you enter a date.