+ Reply to Thread
Results 1 to 24 of 24

help with creating a chart from data

  1. #1
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    help with creating a chart from data

    Hello,

    I would like to make a chart of the revenue of different projects. I would like to split the cost over the weeks as in my data. Also I would like a slicer for ''OMSCHRIJVING'' to add or delete several projects so that I can see how the revenue changes with or without several projects and also a slicer for the ''status''
    thank you
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    As required

    Ochimus
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    wow thank you very much. I will try it with all my data and I will come back to you if I cant figger it out which will probablt happen

  4. #4
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    when I for instance look at just week 25 it says total 50618 and when I look at the data in sheet1 it says 58433,91499 I dont understand why that does not match
    Attached Images Attached Images

  5. #5
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    Very good question, no idea why at the moment, but looking into it!
    Though someone else will probably solve this beforehand. This site is packed with real expertise!

    Ochimus.

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    Attached now shows correct values, but I'm none the wiser why it was wrong in the first place.

    The "fix" was actually posted on this Forum back in 2013 by 'The_Gute', who 'expanded and collapsed the totals in the Pivot table - and it recalculated correctly'.

    So I did that - and it worked!

    Never a dull moment with Excel

    Ochimus
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    Hi,

    I see it. you changed the duration of the project through your formula in ''duur'' . In my original file they matched with ''duur2''

    But i dont know how it happenend
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    the first one was actually right it should be the 58434 but you deleted the vacations so it was wrong when I add the vacation days it is right

  9. #9
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    why doesn't a formula work in a table?? I need to type the formula for year and week so I can easily change the data in the future
    Attached Images Attached Images

  10. #10
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    The only thing I changed was replacing your "'Kolom" text in J4 - DI4 to the Week numbers you had in row 2 so the Pivot knew which weeks to refer to. No idea how the formula in Col I altered, as I never even looked at it!

    And you CAN have formulae in Tables and Pivots. This explains how:

    https://www.wallstreetmojo.com/pivot...culated-field/

    Ochimus

  11. #11
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    Well I believe that is only about pivot table I dont see the analyze button because its a normal table in sheet ''€''

  12. #12
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    You can have Formulae in the "Header" row but at a cost.
    Click anywhere in the table, select Table Design" on the ribbon, unclick Header Rows and put this in J4:
    =YEAR(J1)&"-"&WEEKNUM(J1)
    and drag it across. Fill the cells dark blue or whatever colour you want.

    BUT you then lose the "Sort" ability of the Header Row AND - given the "Header" can be used in formulas (e.g. =SUM(Table1[whatever]) ) - you will have real problems if what should be a "static" reference changes during a calculation cycle.

    So it's your choice. "Formula in Header Row" or "Sort ability via Header Row".

    Ochimus
    Ochimus

  13. #13
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    oke thank you I will proceed with my all my data and if I have a question in the near future I will come back.

    thank you very much

  14. #14
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    Hi,

    did you manually change all the weeks so that it just displays 2021-31 instead of Som van 2021-31?

    If I want to give allmost al projects the same color instead of 2 or 3 do I have to do that manually?

    and is it possible to show the sum of each weak instead of the sum of each project/colour? as in my picture
    Attached Images Attached Images
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    1. Yes. I highlighted all the week number cells (select first one, press 'End + Right arrow' on keyboard), then pressed Ctrl+F (Find and Replace), and replaced "Sum of " with " " (one space). Left just the week numbers.

    2. Yes. https://www.pivot-table.com/2016/01/...mns-one-color/

    3. Yes. https://www.excelcampus.com/pivot-ta...Rows%20Only%29.

    Ochimus

  16. #16
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    thank you. is that really the fastest way for to add totals for pivot? I have 3 graphs wit each spanning over 1 year so more than 150 times but if its the fastest way oke

  17. #17
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    If i knew a faster way i would have told you. Someone else on here may do, of course.

    Ochimus

  18. #18
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    oke. my last problem is I would like to choose the order of how the projects are displayed meaning which one at the bottom which one at the top. Now If I try that its not possible but when I copy my graph to a new excel file it is possible as you can see in the screenshot. But I dont want that because than my options for changing the pivot table are gone.

    is there any way I can change how different project are displayed in order from bottom to top

    thank you
    Attached Images Attached Images

  19. #19
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    This site is invaluable, and we are all happy to help, but in many cases you can find the answer easily for yourself by just typing the question into your Browser?

    https://www.sagecity.com/blog/b/exce...-sort-a-slicer

    Ochimus

  20. #20
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    I tried that but I was not able to find it in dutch.

    relating to the method for displaying the grand total. in the video the item totals are displayed in the graph in the normal way. I actually dont neet the grand total but just the totals like in the screenshot. how do I display them? because they are just visible in the pivot table
    Attached Images Attached Images

  21. #21
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    because now when I change my data. and I renew the file its not working anymore
    Attached Images Attached Images

  22. #22
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    Hello, I have my graph ready now. Only problem I have is that when I use my slicer to filter or add several projects it completely messes up all the colours. How do I save something like that from preventing it happening
    Attached Files Attached Files

  23. #23
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help with creating a chart from data

    Neele,

    You attached the graph but not the Data or Pivot table that created it?

    Ochimus

  24. #24
    Registered User
    Join Date
    02-18-2021
    Location
    Linne
    MS-Off Ver
    Microsoft 365 Business
    Posts
    55

    Re: help with creating a chart from data

    I neeed everything with ''opdracht'' the same colour and ''kansrijke offerte'' should all have other colours. I do it manually everytime because when I load new data the colours are completely random.

    How can I save this or something
    Attached Files Attached Files

+ 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. Creating one chart for 4 weeks of data
    By melissaoi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-10-2017, 08:14 AM
  2. Creating Chart and Selecting Data
    By wz4np1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2012, 01:50 PM
  3. [SOLVED] Creating Graph/Chart for some Data
    By ur2l8 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 04-22-2012, 03:17 PM
  4. Excel 2007 : Chart Creating Data of it's Own
    By LaLaLand in forum Excel General
    Replies: 1
    Last Post: 01-16-2012, 01:13 PM
  5. Creating Line Chart From Data
    By sun815613 in forum Excel General
    Replies: 2
    Last Post: 11-30-2010, 04:35 PM
  6. Creating Chart from Row of data
    By remo in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-26-2008, 03:42 PM

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