How Do I Place Years in an Excel Chart?

Microsoft Excel is a spreadsheet program that allows users to store different types of data.

Microsoft Excel is a spreadsheet program that allows users to input and store large amounts of data. Data of several different types can be stored, including text, integers and dates. Those wishing to display a list of dates while still keeping the full date data within the spreadsheet can do so by using one of two techniques. One method uses the "Format Cells" option to hide additional date data and the other uses a cell formula to create a new set of cells.

Instructions

Formatting an Entire Column to Show Years

1. Right-click on the top heading of the column of cells that you wish to display years in and select "Format Cells" from the contextual menu. The "Format Cells" dialog box will appear.

2. Select "Custom" from the "Category" panel on the left side of the dialog window to bring up the custom formatting tab.

3. Type "yyyy;@" without the quotation marks into the "Type" box in the custom formatting tab.

4. Click the "Close" button. Now whenever you enter a full date into a cell in this column, only the year will be displayed.

Creating Cells to Display the Years from a Column of Dates

1. Click on a cell to select it. If you are making an entire set of cells display years, ensure that there are enough blank cells below to store the entire set.

2. Type "=YEAR(CELL)" without the quotation marks and replace "CELL" with the cell that you wish to display the year from. For example, "=YEAR(A1)" will display the year from the date stored in cell A1 of the spreadsheet.

3. Hover the mouse pointer over the bottom right corner of the new cell until it becomes a crosshair, then click and drag the box downward to create a set of cells. The formulas in the new cells will update automatically to include the correct locations of cells that the years are being extracted from. Make the set of cells as big or small as required.

Tips & Warnings

• If you do not wish to utilize an entire column for dates, you can format a specific set of cells to display the year only by clicking and dragging the mouse cursor to select all the date cells, right-clicking and then selecting "Format Cells" from the contextual menu. Follow steps 2 through 4 of the first section to complete the process.

References

• Microsoft Support: How to Control and Understand Settings in the Format Cells Dialog Box in Excel
• Microsoft Office: Function Reference: YEAR

Resources

• Microsoft: Excel Help and How-To