1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to get rid of unwanted labels which are not part of data

Discussion in 'Discuss Data Visualizations and Charting' started by Tauqeer, Mar 14, 2018.

  1. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi

    I am having this problem, I have setup this chart for dashboard, initially the data was for 11 months, when the data is changed for 3 months, chart has shown now three months, if you notice on bottom of June bars there are these 0 labels are coming from nowhere, could anyone advise how to get rid of them, they are no part of the data selected for three months.


    upload_2018-3-14_18-35-26.png



    upload_2018-3-14_18-36-21.png upload_2018-3-14_18-35-26.png upload_2018-3-14_18-36-21.png

    one of the other chart has this $ value label , from no where, please advise how do I get rid of them

    upload_2018-3-14_18-42-21.png

    upload_2018-3-14_18-43-26.png
  2. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Tauqeer have You checked 'Show data in hidden ...' is as below?
    Screen Shot 2018-03-14 at 10.20.52.png
  3. Nightlytic

    Nightlytic Member

    Messages:
    107
    Hi Tauqeer,

    This is because, I suspect, your chart is still pulling in the N#As with the 0 values.

    Simple fix is to go into your primary vertical axis, and set the minimum over 0. Make it a fraction, 0.1 for instance. It won't be noticeable, but any marks and labels and ticks below 0.1 won't show.
  4. Tauqeer

    Tauqeer Member

    Messages:
    71
  5. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi Nightlytic

    Thanks for the advice, by changing the axis ,
    I have got rid of these labels, really appreciate
    It.

    Just wondering why these null values still
    Picking. 0 values.
  6. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi vletm
    I have checked that, box is still unchecked
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,555
    Hi ,

    Upload your workbook if possible.

    Narayan
  8. Tauqeer

    Tauqeer Member

    Messages:
    71

    Hi Narayan

    I am attaching the file , as you can see in the dashboard I have changed the vertical minor axis details to hide these in wanted labels, but if you look at the individual pivot tables sheets you can see these labels coming up, somehow the chart is picking the null values as zeros, I don't know how to fix it.
    please advise.

    Attached Files:

  9. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Tauqeer
    You could use 'Group' as below
    ... before
    Screen Shot 2018-03-15 at 11.14.13.png
    ... and after
    Screen Shot 2018-03-15 at 11.14.03.png
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,555
    Hi ,

    Can you say whether your problem has been resolved ?

    Narayan

    Attached Files:

  11. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi
    Could you please advise how did you group them, and did you apply any formula to group them. Also noticed in the last 02 months of %age line just drops as compared to the dashboard chart, did you notice that as well. please advise
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,555
    Hi ,

    To whom is this addressed ?

    Narayan
  13. vletm

    vletm Excel Ninja

    Messages:
    3,513
  14. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi Vletm
    Thanks for your message, my question is my data
    Changes all the time, 3 months , 6 months,
    9 months and yearly , so that means I have
    To group and ungroup rows manually
    Evertime it changes.
    You also mentioned some settings to be
    Changed with charts , I am open to any
    Suggestions to make this dashboard more
    Meaningful , if slicers can be replaced with
    Something else like buttons etc or any other
    Way to make it more interactive , please
    Advise.
  15. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Tauqeer
    You can make three months steps groups ready and use those as needed.
    It would be 'quicker' if sheets layouts would match for that.
    Your charts size (width and height) while You un/hide row under those.
    There are setting to avoid that.
    I cannot use slicers ... so no comments about those.
    If You could make file which would open without error
    then it would be possible to check better.
    If there are any ActiveX-objects then I cannot either use those.
    Explain You term 'Meaningful'.
  16. Nightlytic

    Nightlytic Member

    Messages:
    107
    I'll give you some pointers if you're open to it :)

    Primarily, your data visualisation is not the best, you could use reading up about it, looking up good examples of charts and dashboards? Plenty on Chandoo.

    1. You use secondary axis A LOT. I'd almost never show the secondary axis. It is very confusing for people because they don't know which visual matches to which side.
    2. If you use data points, there is little to no point in using the axis as well, you could delete both axis from showing on almost all of your charts.
    3. A lot of bar charts, do you want to consider a line graph, a scatterplot, waterfall? Atleast put a table or some KPI flashcards in there. 6 tiles of bar charts doesn't speak to people. In addition, some bar charts there show composition that doesn't change much month to month so it's hard to see what the trend is, or if there is a trend. You would tell a lot more information by simply listing:
    CCR1 : Volume +2%
    CCR3: Volume +1%
    CCR5: Volume -2.5%
    4. If you have axis, titles or labels that are not the most critical thing but you want the information there, make the font grey. lets the real information stand out.
    5. if you're struggling to see the data points that are within the bars, you can give the text a striking background. Put a gradient on it if you don't like how crude that looks, either way the readability is much better.
    6. Why not fix your own colour schemes (rather than use excel defaults everyone recognises by now) maybe try linking them to data, say shades of blue scheme if the data is about volume, red for profits, yellow forecasts? And make the most recent month stand out! Everyone is looking for it, are they not?
    7. Refer reason volume, very hard to read. I'd make the bars thicker (to easier distinguish the colour) and possibly separate them so each reason has a distinct chart. That or insert a table with sparklines.Also, just on this chart, it's difficult to tell whether the upward trend is due to overall volume being higher, or the reason being more prevalent. Perhaps a composition (% of total) would make more sense?
    8. saying $100m (and correcting the labels/axes accordingly, you can change that in custom text formatting options) is probably better than having $100,000,000 everywhere.
    9. top left chart and the one immediately below it, there is no real reason for the line graph to overlap the bar chart, to me it seems to imply some sort of composition trend like 'August 2017 was right on target'. If you play around with the secondary axis min and max, you can lift the line way above the bar charts and it's distinguishable and tells a separate story.
    10. The axis on some of your charts are odd, but I'd think you noticed that. I think you may have not implemented my first pointer to you as I do it, take security indicator volume, primary axis I'd do:
    upload_2018-3-16_9-43-26.png

    Then in number options of the axis, get rid of the decimals
    upload_2018-3-16_9-45-16.png
    Now it looks like:
    upload_2018-3-16_9-45-32.png

    No '1' down bottom, '1601' up top, or weird 201 intervals. 0.1 axis minimum still suppresses all 0 labels.


    Information:
    (caveat, I've no actual idea what the job/point of the MI is, just found it a bit bizarre in telling a story)
    1. You seem to be focusing a lot on averages, why not have month on month comparisons? Year to date? performance to targets, metrics. Add budgets, forecasts? benchmarks? Other comparative information? If this is really a $300m a month operation, I presume there is a lot of that information around to go by!
    2. Similar to above, but actually good job on using the KPIs on top of the dashboard, it's nice to have some solid facts like that up front and up the top. I would suggest looking into what other things you could add there. Last month's data? Year to date?
    3. Understanding that it's a dashboard, I still think you could add commentary some places, for example next to refer reasons, would you not note down whether any policies/structural changes may have skewed the general trend?
    4. Bonus advanced tip: You can link one slicer to multiple pivots, you can, for instance, create a simple pivot that will act sort of as an 'on/off' switch, so if I pick state 'SA', this pivot says 'SA'.
    With this, you can have a special box up the top that would tell general information, how many staff do we have, how many branches, offices, capital.
    But a formula would then, if pivot says 'SA' look up same information regarding SA specifically.

    It looks like you are presenting a lot of complex information, I think I recognise what it is, an automated business loans approval system? If so, from experience here, I sincerely doubt any of your readers will be able to recite what all the rules are, or even basic information about the sectors/products/geographical areas you operate in. It really helps to understand that, and feed them that information somewhere just so that everyone is on the same page and can have a meaningful conversation. There are egos in the room, chances are 10 people noticed a rejection rule trend up and want to ask questions, but won't because they don't know what that rule is. They'll assume others know. They'll ask. Maybe they have a document on this? No, lost it. Questions will never be asked, impact won't be made.
    Khalid NGO and GraH - Guido like this.
  17. Tauqeer

    Tauqeer Member

    Messages:
    71
    Hi Nighltytic

    Thanks a lot for taking time and to write very good points here, I wish I could apply all the things you highlighted. I am a very basic user of excel and doing these charts for another department to gain some practical experience in data visualisation, so I will try to address these things one by one as I go along.

    Tried to lift fix the axis as you mentioned in point no: 10.
    Tried to lift the line as from the bar charts in point no: 9.

    I will attach the file again so you can see.

    You mentioned about deleting the secondary axis, how do I do that if I want to show percentages or dollar amounts against the volumes , please advise.

    You mentioned about the refer reasons chart, to show data table with sparklines, could you please advise on that.

    I changed the chart type app types to line chart to see some trend.

    Tried to do the same on State volume, but they looks quite odd.

    How do you address the CCR volume as you mentioned:
    CCR1 : Volume +2%
    CCR3: Volume +1%
    CCR5: Volume -2.5%

    You are right I am focussing a lot on volumes and %age approved as this
    task was given to me to capture volumes and % age approved under each
    type and that what the process owner wants to capture at this stage.

    I will try to add some more KPI on the top if necessary to show any comparison with the last year, I will try to extract data for atleast 5 yrs and then may be change the charts accordingly to show comparisons.

    I got the slicers which are linked to multiple pivots to show data for particular state or app type etc.

    Please advise and I will try to improve it, but you have to be patient as I am
    not a advance user of excel.

    thanks

    TK

    Attached Files:

  18. Tauqeer

    Tauqeer Member

    Messages:
    71

    Hi Vletm

    Here's the file without any macro.

    Attached Files:

  19. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Tauqeer
    Your 'Dashboard' looked like this ...
    Screen Shot 2018-03-18 at 11.34.49.png
    ... maybe cause 'Slicers' or/and because there were some challenges while opening file.

    I made 'some' changes to almost ALL Your sheets.
    All 'chart data's headers' start from row 40.
    Screen Shot 2018-03-18 at 11.35.35.png

    The 1st sheet has 'controller' for 'number-of-months-to-show'.
    Screen Shot 2018-03-18 at 11.35.03.png Select from its dropdown.

    ... and this is 'the must'.
    Screen Shot 2018-03-18 at 11.35.16.png
    If any of Your sheets cell AA1 has 'font.bold' then
    'number-of-months-to-show' will work!
    ... means rows from 41 to 52 rowheights will set as needed.

    I modified charts sizes and positions same.

    There would be more things to do ...

    Attached Files:

Share This Page