Yearly Slicer in Excel: What It Means and How to Set One Up

How-to basics · Updated November 21, 2026

A yearly slicer in Excel is a slicer that lets people filter a PivotTable or connected report by year. It is not a separate, formally named Excel feature: the phrase informally describes a slicer connected to a Year field. For a reliable setup in Excel for Microsoft 365 on Windows, add a Year column to your source data, include it in a PivotTable, then select the PivotTable and choose PivotTable Analyze > Insert Slicer.

Is “yearly slicer” an official Excel term?

No. Excel’s interface calls the control a slicer; “yearly slicer” is a descriptive, informal phrase for one that filters records by year. It is not a distinct Excel object, command, formula, or standard technical term. The wording makes sense because the slicer’s buttons might show years such as 2022, 2023, and 2024, but the same slicer feature can filter by other fields, including product, region, or salesperson.

The phrase has no established, documented origin as a named Excel feature. “Slicer” is Microsoft’s name for the on-screen filtering control, introduced for PivotTables in Excel 2010. A year slicer is simply that control applied to a field containing years. In ordinary writing, someone might say, “Add a yearly slicer to the PivotTable so the team can filter sales by year.” That sentence uses the phrase descriptively, not as the name of a special Excel command.

What a yearly slicer filters

A slicer displays selectable buttons for the distinct values in a field. If its field is Year, selecting 2024 filters the connected PivotTable to records assigned to 2024. Selecting another year changes the filter; clearing the selection restores all years. It offers a visible alternative to opening a field’s drop-down filter menu.

The year value usually comes from a helper column in the source data or from grouping a date field in a PivotTable. The slicer itself does not calculate a year from every date, and it does not change the source records. It filters a report based on the values in the field to which it is connected. This distinction matters: a slicer attached to a month or transaction-date field will show those values instead of automatically becoming a year selector.

Slicers are commonly used with PivotTables and Excel Tables. A slicer on a PivotTable can filter summarized results; a slicer on a Table filters the table’s rows. If you need several reports to respond to one selection, the reports must be compatible PivotTables and connected to the same slicer through Report Connections.

Prepare the source dates and a Year field

Start with a column containing real Excel dates, not text that merely looks like a date. A practical source table might have columns named Date, Region, Product, and Sales, with one transaction per row. Convert the range to an Excel Table using Insert > Table, or Ctrl+T on Windows, so added records can be included more easily when the data is refreshed.

For a helper-column approach, add a column named Year. If source dates are in A2:A100, put the Year heading in B1 and enter this in B2, then fill it down:

=YEAR(A2)

For a row where A2 contains June 15, 2024, the formula returns 2024. The formula extracts the calendar year from the date; it does not create a fiscal year. If your financial year begins in a month other than January, a simple YEAR formula may not match your reporting calendar, so define the fiscal-year rule you need before building the slicer.

When the source is an Excel Table, a formula entered in the Year column generally fills down as a calculated column. Confirm that new source rows have a Year value too. A missing year, a blank date, or a date stored as text can cause records to be absent from the expected year selection or can interfere with date grouping.

Create a PivotTable and insert the slicer

  1. Click a cell in the source Table or data range. Choose Insert > PivotTable and confirm the source and destination in the dialog. Put the PivotTable on a new worksheet if you want to keep the summary separate from the records.
  2. In the PivotTable Fields pane, drag Year to Rows, and drag a measure such as Sales to Values. You can add Product or Region to Rows or Columns if the report needs a breakdown. Check that the values in the PivotTable make sense before adding the control.
  3. Click inside the PivotTable. On the ribbon, choose PivotTable Analyze > Insert Slicer. In the field list, select Year and choose OK. Excel adds a slicer with a button for each year represented in the PivotTable’s data.
  4. Move and resize the slicer so it does not cover the report. Select a year button to filter the PivotTable. To clear the selection, use the clear-filter icon in the slicer header; its appearance can vary slightly by Excel build.

On Mac, the PivotTable and slicer workflow is similar, though ribbon labels or their placement can vary by Excel version. If Insert Slicer is unavailable, check that a PivotTable is selected and that the desktop version is up to date. In Excel for the web, available creation and editing controls can depend on the current feature rollout; use desktop Excel if the required command is missing.

Use a grouped date field instead

You can often create a year field inside a PivotTable without adding a helper column. Put the date field in the PivotTable, right-click one of its date entries, choose Group, and select Years in the grouping options. Excel groups the dates by calendar year and adds a Years field to the PivotTable field list. You can then select the PivotTable and use PivotTable Analyze > Insert Slicer to add a slicer for Years.

This method is convenient when the source dates are clean and calendar years are appropriate. Grouping may fail or be unavailable if the date field contains blanks, text values, or other inconsistent entries. Some PivotTable sources, including certain external or OLAP data sources, also restrict grouping. In those cases, a Year field prepared in the source data or data model may be the better route. Refresh the PivotTable after source changes so its available years and results are current.

Year slicer, Timeline, or ordinary filter?

These options all narrow results, but they suit different tasks:

  • Year slicer: A set of year buttons is straightforward when users usually choose one calendar year at a time, or when you want year choices visible beside a PivotTable.
  • Timeline: A PivotTable Timeline is a date-specific visual control. Use PivotTable Analyze > Insert Timeline and select the date field. Its time-scale menu can show years, quarters, months, or days, making it useful for selecting date ranges as well as whole years.
  • Field filter: A PivotTable field drop-down can filter Year without taking up space for a separate control. It is less prominent but may suit a compact report.
  • Table slicer: A slicer connected to an Excel Table filters its rows, rather than summarizing them as a PivotTable does. It can work for a simple list, but does not by itself provide PivotTable-style totals.

Choose based on how people need to explore the data. A slicer is useful when the year choice should be obvious and easy to click. A Timeline is often more natural when people need to move across date ranges. Neither changes the underlying date values or determines which year your organization considers its fiscal year.

Connect and format the slicer

A slicer initially filters the PivotTable from which it was created. To have one slicer control more than one PivotTable, select the slicer and look for Slicer > Report Connections or PivotTable Connections, depending on the ribbon and Excel build. Select the PivotTables that should respond. They generally need to share the same source or PivotTable cache; unrelated PivotTables may not be connectable this way.

Use the slicer’s buttons to select a single year. To select multiple years, use the multi-select control in the slicer header if available, or hold Ctrl while selecting buttons on Windows. On Mac, use Command when the interface calls for the Command key. You can change the slicer’s visual style and button layout from its contextual Slicer tab, but formatting affects appearance, not which records the filter includes.

Common problems and checks

  • Years are missing: Check the source rows, the Year column, and whether the PivotTable has been refreshed. Use PivotTable Analyze > Refresh, or Refresh All when multiple connections need updating.
  • Dates will not group: Inspect the entire date column for blanks, text dates, or mixed data types. Correct the source values, refresh, and try grouping again. A helper Year field can avoid date-grouping problems.
  • The slicer shows dates rather than years: It is connected to a date field, not a Year field. Add a Year field or group the dates by Years, then insert a slicer using that field.
  • Clicking a year does not change another report: Check Report Connections and confirm that the other PivotTable is compatible with the slicer’s source. A slicer does not automatically control every PivotTable on a workbook.
  • New records do not appear: Confirm the data source includes them, especially if it is a fixed range rather than an Excel Table. Refresh the PivotTable after extending or updating the source.

Frequently Asked Questions

Is a yearly slicer a built-in Excel feature?
No. Slicer is a built-in Excel feature, but “yearly slicer” is informal shorthand for a slicer connected to a Year field. There is no separate command with that name.

Can I make a slicer from a date column?
Yes, but a slicer made directly from an ungrouped date field may list individual dates. To get year buttons, create a Year field or group the PivotTable date field by Years, then insert a slicer for that field.

Can one year slicer control multiple PivotTables?
Often, if the PivotTables share a compatible source or cache. Select the slicer and use Report Connections or PivotTable Connections to choose the reports it should filter.

Why does my year slicer not show the latest year?
Check that the latest records are in the PivotTable’s source, that their Year values are populated, and that the PivotTable has been refreshed. A slicer cannot display values absent from its connected data.

Is a Timeline the same as a yearly slicer?
No. A Timeline is a separate date-filter control for PivotTables, with selectable time scales such as years and months. A yearly slicer is an ordinary slicer connected to a field of year values.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.