• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Slicers for Fiscal Years

You are here: Home / Excel PivotTables / Excel Slicers for Fiscal Years
Excel Slicers for Fiscal Years
May 6, 2015 by Mynda Treacy

There’s no built-in way to create Excel Slicers for fiscal years, however the solution is easily achieved by adding an extra column to your source data to classify each date into its relevant fiscal year.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Be sure to watch to the end for the bloopers!

Creating Excel Slicers for Fiscal Years

In Australia our financial year runs from July 1 to June 30 so I’ll use that as my example.

In columns A and B below is the data I want to analyse in a PivotTable and Pivot Chart; it’s the trading volume of some stocks by date.

And in columns E and F is my lookup table (with the named range tbl_fiscal_yr), that I’ll use to map the dates in column A into their Fiscal Period:

Slicers for Fiscal Year raw data

Note: Both tables are formatted as an Excel Table and my dates are dd-mm-yy.

I'll add a column (C) to classify the dates in column A into their fiscal year. For this we can use a VLOOKUP formula with a Sorted List:

classify dates into fiscal years for slicers

Note: If the formula arguments like [@Date] look odd to you it’s because they’re using the Excel Table’s Structured References.

Now that we have the new column (C) for the fiscal year we can use that field for the Slicer and then choose the fiscal year we want to display in the PivotTable and Pivot Chart:

PivotTable and Pivot Chart with Slicers for Fiscal Years

Excel Slicers for Fiscal Years

More Slicers Posts

Symbols in Excel Slicers

Symbols in Excel Slicers is a fun way to make your reports more interesting. Here are some tricks to getting wingdings and symbols to display in Slicers.
Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts can open up a huge range of possibilities and the best part is you can use Slicers to control regular charts too.
slicer selection in formula

Use Excel Slicer Selection in Formulas

How to use Excel Slicer selection in formulas to control charts and tables. Download the workbook and follow along.
Excel Slicer Trick

Excel Slicer Trick

In this Excel Slicer Trick I show you how to use a Slicer to display a drilled down view of your data in a PivotChart. Download the file and follow along.
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

Creating Excel Slicers for rolling periods is easy with this IF formula. Download the workbook and watch the video.
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

How to create a single Excel Slicer for Year and Month fields. Includes written instructions, video and workbook download.
Excel Slicers

Excel Slicers – Everything You Need to Know

How to insert and use Excel Slicers to create interactive charts and PivotTables. Includes video and step by step written instructions.
Sorting Excel Date Slicers

Sorting Excel Date Slicers

Slicers have some shortcomings when it comes to dates and sort order. This post explains a couple of ways to sort dates correctly in Excel slicers.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.


Category: Excel PivotTablesTag: slicers
Previous Post:Excel Slicer rolling periodsExcel Slicers for Rolling Periods
Next Post:Excel PivotTable Calculated ItemsExcel PivotTable Calculated Item

Reader Interactions

Comments

  1. Daniel

    August 12, 2019 at 6:51 am

    Hi Mynda,
    Our fiscal year starts in November. I found a solution by having a “month” column with the first day of the corresponding month, then setting the column to a custom format “mmm” in the table. That format carries forwards to the slicer, keeping the months in the order I want 🙂 Unfortunately, this doesn’t work for power pivot, so I’m trying to find a way to do there too.

    Reply
    • Mynda Treacy

      August 12, 2019 at 9:40 am

      Hi Daniel,

      In Power Pivot you add a numeric column to your table with the sort order e.g. November will be 1. Then set the ‘Sort By’ for the Month column name to be sorted by the numeric column.

      Mynda

      Reply
  2. Peter Ryan

    August 15, 2017 at 3:35 pm

    Hi Mynda. The difficulty I’m having when I do a pivot table is that the months still show in a calendar year order despite my classifying them in the way you suggested. Could it be because the figures I’m using are monthly figures only – ie there is only one date for each month. Anyway I’m trying to get the figures into an April/March fiscal year but I get them sorted January/December with the last 3 months of the fiscal year showing as the 1st 3 months of the calendar year.

    Maybe I’m missing something.

    Reply
    • Mynda Treacy

      August 15, 2017 at 5:37 pm

      Hi Peter,

      Having just one entry for each month won’t be the problem. In your PivotTable do you have the calendar year and calendar month in the rows area and the Fiscal year in the Slicer or Filter area like my example at the bottom of the post?

      If you’re still stuck please post your question and file on our Excel forum where we can help you further.

      Mynda

      Reply
      • Peter Ryan

        August 16, 2017 at 12:26 pm

        Thank you for your help Mynda. That worked!
        I should have looked a bit more closely.

        Regards

        Peter

        Reply
        • Mynda Treacy

          August 16, 2017 at 12:30 pm

          No worries, glad that fixed it.

          Reply
  3. Adam

    September 19, 2016 at 5:22 pm

    Thanks for this Mynda.
    I work at a sugar refinery and our working day starts at 8AM therefore anything produced from midnight to 8AM gets booked to the previous day’s production.

    Also our production week starts at 8AM on Monday morning.

    I can adapt this idea to accommodate this.

    Thanks for sharing your hard work and experience. Your video’s and examples are great.

    Reply
    • Mynda Treacy

      September 19, 2016 at 8:38 pm

      glad I could help 🙂

      Reply
  4. maggie

    June 17, 2015 at 3:13 pm

    Very interesting

    Reply
  5. Bob Thorp

    May 12, 2015 at 3:06 am

    Hi Mynda, The videos are very helpful and easier to follow than reading through the text. the headshots are good at the beginning and end of the video, but distracting during the training portion. As always, your training material is very useable. Please keep including the videos!

    Thanks for all your hard work on the training material!
    Bob T

    Reply
    • Mynda Treacy

      May 12, 2015 at 8:00 am

      Thanks, Bob. Glad you’re finding the videos helpful.

      Mynda

      Reply
  6. Dennis

    May 8, 2015 at 4:37 am

    Mynda
    This is just fantastic.
    Your Newsletters are marvelous.

    Keep it up.

    Reply
    • Mynda Treacy

      May 8, 2015 at 12:03 pm

      Thanks, Dennis! Glad you like it 🙂

      Mynda

      Reply
  7. Pablo

    May 8, 2015 at 3:33 am

    Hi Mynda,
    Following the videos is way easier than reading the blog, so please keep the videos. Also it does help to see your facial expressions as you explain things, it makes it more real.
    I love that you added a blooper at the end, great sense of humor.
    Thanks for sharing so much knowledge, and for your hard work, your material and explanations are the best.
    Keep up the good work.
    Pablo

    Reply
    • Mynda Treacy

      May 8, 2015 at 12:04 pm

      Thank you, Pablo! I’m glad you found the video easy to follow and liked the personal touch 🙂

      Reply
  8. Mike Bailey

    May 7, 2015 at 9:29 am

    Very good and appreciated! Thanks!

    Reply
    • Mynda Treacy

      May 7, 2015 at 9:30 am

      Cheers, Mike.

      Reply
  9. Dennis

    May 7, 2015 at 3:04 am

    I do find the headshot a little distracting but it is nice to see your face at the beginning and at the end of the video. The bloopers are a nice touch.

    Reply
  10. BRUCE RAWLINSON

    May 7, 2015 at 1:19 am

    Including the head shot is fine, in my opinion. Creates a good connection with your students.
    BR

    Reply
  11. Alex

    May 6, 2015 at 11:58 pm

    great tutorial again Mynda thank you.
    Yes your head-shot is a bit distracting after a little while, maybe just have it run for a minute or so by way of introduction, i would add that the background fades in and out colour wise which is not flattering to you, if i may be so bold.

    And as my dear old Pop used to say

    awrabest

    Reply
  12. Col Delane

    May 6, 2015 at 11:46 pm

    Hi Mynda

    Ascertaining and using a fiscal year value (as opposed to a calendar year) for a given date is a frequent task for us bean counters, and for me even more so now as a financial analyst when building models with time-series data for valuations, DCFs, etc.

    However, rather than build a separate table and lookup that table for the Fiscal Year as you have demonstrated, I usually just add a formula where the value is required to directly return the calendar year of the end of fiscal year date (e.g. return 2015 for any date between 01/07/14 and 30/06/15). A single number is much easier to work with mathematically than the two year format like 2014/15.

    So in your tbl_stocks, I’d use the following formula in column C instead of the VLOOKUP:
    =YEAR([Date])+IF(MONTH([Date])>=7,1,0)

    Re the headshot in the video: I don’t believe it adds any value whilst the focus of attention needs to be on the content, but does make for a greater connection at the end during the wind up. It is a little distracting to me during the guts of the presentation, and means you have to be very careful with your facial expressions and other things you might do unconsciously whilst talking!!) You could consider doing a fade-out after the intro and then zoom back in for the wind up.

    Cheers
    Col

    Reply
    • Mynda Treacy

      May 7, 2015 at 8:59 am

      Hi Col,

      Thanks for sharing your formula. There are many ways to skin the Excel cat, so to speak.

      I think I will try the fade in/out for the headshot in the video as you and others have suggested. It sure will take the pressure off while recording, too 🙂

      Cheers,

      Mynda

      Reply
  13. Abbott Katz

    May 6, 2015 at 10:53 pm

    Hi. I think the vlookup array for the fiscal year exercise could be trimmed to two rows:

    Months Fiscal Period
    1/3/2014 2013/14
    1/1/2015 2014/15

    Since a looked-up date that falls between two lookup array values in the first column reverts to the smaller value, I think the above array would suffice.

    Thanks,
    Abbott Katz

    Reply
    • Mynda Treacy

      May 7, 2015 at 8:56 am

      Hi Abbott,

      Good point. I could certainly trim it to:

      1/3/2014 2013/14
      1/7/2014 2014/15

      Since our fiscal year runs Jul 1 through Jun 30.

      Cheers,

      Mynda

      Reply
  14. Dan Laemont

    May 6, 2015 at 10:46 pm

    I vote to keep your face on the videos … not at all distracting and it tends to create a more “classroom/Personal touch” feeling. The videos are simply terrific, btw.

    Reply
    • Mynda Treacy

      May 7, 2015 at 9:01 am

      Thanks for the video feedback, Dan, Dennis, Bruce and Alex.

      I think next time I’ll try the fade in/out so I’m not there the whole time. I did find I got in the way of the PivotTable field list so finding somewhere to place the camera shot was tricky.

      Cheers,

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.