1

I have a table in Excel 2016:

     YEAR_     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

I'm using the table in a PivotTable and a PivotChart (bar chart):

enter image description here

That works, but I need bars for each year within this range: system year + 9. In other words, I want bars for 10 years, starting with the current year (currently 2023).

So I need to force Excel to show empty bars for 2023, 2030, and 2032.

It would look like this:

enter image description here

How can I force the bar chart to show the missing years? I'm looking for a dynamic solution; I don't want to manually enter filler rows into the table.

Related:

If rows are missing for certain years within sysdate-based year range, generate filler rows using Power Query

User1974
  • 137
  • 1
  • 18

1 Answers1

1

  1. Select the chart

  2. Select the x-axis

  3. Right-click on the axis and select "format Axis" from the dropdown menu

  4. Choose "Date Axis" from the Axis Options

cybernetic.nomad
  • 5,415
  • 12
  • 25
  • Thanks. I tried that, but it doesn't seem to have an effect. Even if it did do something, I don't think I understand how Excel would know that I want the *"ten year range, starting this year, even if data is missing for certain years"*. I haven't told Excel I want that anywhere. – User1974 Jan 24 '23 at 16:20
  • Change the bounds of the axis – cybernetic.nomad Jan 24 '23 at 16:34
  • Yeah, I don't see anywhere to change the bounds of the axis: https://i.stack.imgur.com/tfDSr.png. Maybe because the values aren't true dates, but years? Even still, I don't want to hardcode to specific years. I want it to be dynamic. So on January 1, 2024, I want it to automatically switch from 2023-2032 to 2024-2033. – User1974 Jan 24 '23 at 16:48
  • Consider creating a separate table with the desired dynamic year range in that case – cybernetic.nomad Jan 24 '23 at 17:44