Share via

After adding a column, heading doesn't show up in horizontal axis of stacked bar chart for that column. That spot on axis is blank.

Anonymous
2014-05-13T19:06:06+00:00

Hi folks, I hope you can help. I have a summary sheet of data that we add to daily. I have a stacked bar chart to represent the data. I have inserted new columns for additional days. The column header is the date, which becomes the value that should display on the horizontal axis of the chart, and in the data table. Some of my newly inserted columns are coming up in the chart with nothing on the axis or as the header in the data table.

As you can see, the header/axis lable for columns for 5/7, 5/8, and 5/12 are not there. Below is the area of the spreadsheet that the chart is generated from.

There are hidden columns to prevent the chart from being so large, we only wanted to show the previous 7 days. Also, column AA is a column labeld END. I do this so that I can create the chart to include that column and then hide it so that it will not show up in the chart. Any columns inserted infront of column AA should automatically be added to the chart. The data select for the Axis is $C$66:$AA$66.

I don't understand why the data is showing up, but the axis and data table headers are not when the data select for the Horizontal Axis is $C$66:$AA:$66.

My horizontal axis is formatted as text also.

Thanks in advance for any insights here!!!

Darrell

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-05-13T20:53:37+00:00

    Hello,

    chances are the X Axis label source is not defined correctly.

    Open the "Select Data" dialog for the chart and check the range selected in the Horizontal (Category ) Axis Labels setting.  Make sure all desired columns are included.

    Or select a data series in the chart and check the formula bar for the series formula

    =SERIES(Sheet1!$A$4,Sheet1!$P$1:$S$1,Sheet1!$P$4:$AA$4,3)

    The highlighted second parameter contains the range for the X axis labels. See how this one goes from column P to S, whereas the data goes from column P to column AA. Adjust the column S to read AA and the missing labels should appear.

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-05-13T23:28:38+00:00

    Hi,

    Even though you have mentioned that the X axis is formatted to Dates, try this once again

    Right click on the Dates in the X axis > Format Axis > Text

    Does it make any difference?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-05-14T21:07:10+00:00

    >>It would also help if you could use the arrow keys to scroll through the formula bar.

    You can. In any situation where you are editing a formula and the arrow keys will default to navigating worksheet cells, hit the F2 key on your keyboard. It toggles between the cell selection and the edit mode. It works in the formula bar and all dialogs like defining chart source data, named ranges, conditional formatting formulas etc.

    0 comments
  3. Anonymous
    2014-05-14T21:00:36+00:00

    Ashish, thank you for the effort. Yes, I already had the X Axis set to text. I had actually double checked that before I posted my question. I believe Teylyn was on the right track.

    0 comments
  4. Anonymous
    2014-05-14T20:58:15+00:00

    Teylyn, thank you. I believe that you were correct and that was the problem I was having. My rows in my chart were not necessarily all adjacent to each other. When I went to Select Data, my Chart Data Range was blank. I tried typing in the correct information, even going so far as to copy the text from another chart with a similar range and trying to modify it. The problem was that it changed the formatting of my data series, and I didn't want that to happen. I like the default colors that it was using and didn't want to have to try and recreate them manually. The workbook has 37 different charts that all contain comparable data and I was afraid a lot of them had this problem. Today they're all fine except the one, so yay.

    Everything I tried typing into the Chart Data Range changed the alignment of the rows in the data tables and changed my data series formatting, it was quite frustrating. In the end, I just ended up recreating the chart from scratch and it worked and I managed to get the color scheme the way I wanted it. I still believe that your info was correct though, if I could just figure out the relationship to the way the information it typed into the Chart Data Range and the order that it displays on the chart. It didn't seem to matter that under the Legend Entry (Series) section of Select Data that I already had them in the order that I wanted, it changed.

    It would also help if you could use the arrow keys to scroll through the formula bar.

    0 comments