+ Reply to Thread
Results 1 to 20 of 20

Creating Nice visualition chart for dates and hours within year

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Creating Nice visualition chart for dates and hours within year

    Hi Guys,

    i have table like here:

    Screenshot_13.png

    and have to add nice chart to read it. Maybe % of used time for specific activities (slaes, Image, others?).
    Who can have idea and can help?

    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,854

    Re: Creating Nice visualition chart for dates and hours within year

    I do not know what do you mean as 'nice chart'.
    Chart is nice until is clearly represents data, imho.
    Attached Files Attached Files
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    thank you very much Kokosek, love your approach.

    Chart is nice until is clearly represents data, imho.
    Screenshot_18.png

    yes sorry for not clear clarification of my issue.

    when i will have more columns and every week in whole year chart will be not clearly represent data...
    Can you please help with this example?

    Jacek
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,095

    Re: Creating Nice visualition chart for dates and hours within year

    Avoid 3D charts - contrary to what has been said, they do not represent trends as well as a standard 2D chart will do. It's a case of cartoon style over substance.

    The second set of data looks very different to the first: I'd have thought you'd have realised by now that over-simplification leads to a lot of time-wasting.

    Is this it, or are you going to make further changes?

    What is the KEY MESSAGE you want to convey with a chart? This will determine what the best approach is. 'Nice' really doesn't tell us anything.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    No i do not want to make any other changes.

    What is the KEY MESSAGE you want to convey with a chart?
    good question.

    How much time did you spend on every category from specific range of time (in example 52 weeks within 2020).
    How much time did you invest in working of your image or doing sales activities.

    Thanks!
    Jacek

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Hi,

    anyone?

    Jacek

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Anyone has idea?

    please help,

    Jacek

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Creating Nice visualition chart for dates and hours within year

    What do you think the stacked column chart is doing well? What is it failing to show? Have you tried any of the other built in chart types?

    I think a line chart would do a better job at communicating how much time was spent on each category, but that's just my opinion. Have you tried a line chart? What did you like about it? What did you not like about it?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Hi MrShorty!

    Thank you very much!

    Hmm i think stucked with % could be fine when i will have only 52 weeks.

    But what you mean by line chart ?
    Can you please show example?
    I can not imagine it,

    Best Wishes,
    Jacek

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,095

    Re: Creating Nice visualition chart for dates and hours within year

    To see what a line chart looks like, select your chart and change its type from stacked bar to line ...

    There are many built-in chart styles - just go though them individually and play a bit with the options until you find one that suits your purpose.

  11. #11
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Hi,

    i know how to create line chart:



    but i assume that MrShorty has nice idea in order to look this fine.

    Maybe MrShorty i should add pivot table and from pivot table have 52 weeks / year and from pivot create chart?

    Please help,
    Jacek
    Attached Images Attached Images

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Creating Nice visualition chart for dates and hours within year

    but i assume that MrShorty has nice idea in order to look this fine.
    Ummmmm....Incorrect. My idea was as simple as AliGW suggested -- take the chart in the file in post #3 and do nothing more than change its chart type to 2D line. I am somewhat of a chart minimalist, believing that simple chart types with minimal embellishment work best at communicating whatever I am trying to show.

    Pivot tables/charts are useful tools for summarizing data. In this case, I don't see what you want to get our of a pivot table/chart. Suggesting a pivot table/chart indicates to me that maybe you want to further summarize/aggregate the data before charting? Naturally, if you have not yet summarized/aggregated the data you want in the chart, that needs to be done before charting.

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Thank you Shorty!

    I did simple line chart and result you can see on screen above - it is horrible

    It can not be read...

    Hmm so what you would show on line chart?

    Jacek

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Creating Nice visualition chart for dates and hours within year

    Your copy of Excel must be doing something very different from mine, but I obviously cannot tell what it is doing from a picture. When I change the chart type, I get this (you have random numbers, so it isn't exactly the same each time, but you see a normal line chart). I've attached the file, but I still have no explanation for why your copy of Excel gives you something so different.
    Attached Images Attached Images
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    This is awesome Shorty!! Thank you vry much,

    It is of course only part of my data but it can be very useful approach - simple and nice!

    Best Wishes for you and thansk for tips.

    Best,
    Jacek

  16. #16
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Hi Guys,

    i am still having issues.

    What i did i create pivot table from data to get the sum for each week for specific category:

    Screenshot_15.png

    and having issues:

    1) sum of time is not working properly, why?
    2) i want to get stacked % column and line chart - it is working strange, i do not want to add % helper columns in my table - can i do this somehow via chart creation or in pivot table?

    My result is strange - i can not read anything from it - lines are separated...
    Screenshot_16.png

    Please help how to get clear data from this, how to build stacked % line chart and % column chart from pivot table.

    Best,
    Jacek
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Creating Nice visualition chart for dates and hours within year

    1) sum of time is not working properly, why?
    As near as I can tell, the sum of time is working correctly. I notice that your number formats are all "time of day" formats ("hh:mm") rather than "elapsed time" ("[hh]:mm"), which changes the display. (27 hours means 3 AM, so a time of day format will display 3:00 while an elapsed time format will display 27:00). You might review the guidelines for number formatting time values: https://support.microsoft.com/en-us/...rs=en-us&ad=us

    I don't understand your concerns with the stacked line/column chart. The example in your file looks to me like an accurate stacked line chart for the 9 categories. I don't understand what you want the chart to do differently.

  18. #18
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    As near as I can tell, the sum of time is working correctly. I notice that your number formats are all "time of day" formats ("hh:mm") rather than "elapsed time" ("[hh]:mm"), which changes the display. (27 hours means 3 AM, so a time of day format will display 3:00 while an elapsed time format will display 27:00). You might review the guidelines for number formatting time values: https://support.microsoft.com/en-us/...rs=en-us&ad=us
    thank you very much! I didnt know that!

    I don't understand your concerns with the stacked line/column chart. The example in your file looks to me like an accurate stacked line chart for the 9 categories. I don't understand what you want the chart to do differently.
    i made stacked column chart which i can and it is looking like:

    Screenshot_17.png

    but it is possible to get something similar with line chart?

    Best,
    Jacek

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Creating Nice visualition chart for dates and hours within year

    I'm still not sure I understand. In the most recent post, you are using a 100% stacked column chart, and it is showing the data as appropriately sized columns stacked on top of each other to make 100%. In your previous post, you showed a 100% stacked line chart that shows each line "stacked" on top of the others to make a total of 100%. Each chart type is doing exactly what you are asking it to do.

    As a wild guess, are you thinking of a 100% stacked area chart? Assuming your copy of Excel can handle changing the chart type this time, maybe try changing to a 100% stacked area chart and see if that is what you are looking for.

  20. #20
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,370

    Re: Creating Nice visualition chart for dates and hours within year

    Hi MrShory,

    big love for you for youe help and support. Generally for the purpose i used column stacked chart - i had issues with line stacked chart - i should add % of each time during specific day/week/month and based on this i would create the similar char like you reated above.
    But i do not want to do this so i am sticking with columns.

    Best,
    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating a Top 3 list per year (Pivot Chart)
    By neil40 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-24-2019, 07:05 PM
  2. Year on year pivot chart when dates go horizontal?
    By mra1984 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 04-25-2018, 10:44 AM
  3. Creating 5 year Monthly Budget - need sequential dates formula/function/vba
    By CaseyDD in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2018, 04:20 AM
  4. Creating a chart to track hours of machines
    By andrewode in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-10-2014, 04:19 AM
  5. [SOLVED] Simple way of creating rolling year chart
    By katy1985 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-12-2013, 05:14 AM
  6. Replies: 0
    Last Post: 05-10-2012, 06:56 PM
  7. Creating a chart representing hours used
    By joemc911 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-02-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1