+ Reply to Thread
Results 1 to 5 of 5

Difficulty in creating a complex bar chart including dates and comments

  1. #1
    Registered User
    Join Date
    01-20-2022
    Location
    Bern, Swiss
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (16.0.13801.20442) 32-Bit
    Posts
    2

    Difficulty in creating a complex bar chart including dates and comments

    Hi Excel pros! I have some difficulties creating a barplot and would greatly appreciate your help.

    Here is the story:
    I produced different batches in my company. After the end of the production (date) I have 12 days to write the corresponding report.
    My boss wants to see every week the current situation status with a barplot (i.e. if I am late with my reports and why (comments).

    In the excel file attached to this post, I draw the perfect barplot using PowerPoint.


    Here are my variables (what need to be included in the barplot):

    - Batch number (Y-axis)
    - Dates (X-Axis)

    Additional variable:

    - Comment related to each batch (format: text)
    - Lign showing the current date (today = 20.01.2022)


    I would be extremely thankful if you could provide me with a solution.
    I do have 0 experience with Excel, so your help will be hugely appreciated.

    Please text me anytime if you have question.

    Thank you Thank you for your help!

    KR

    Greg
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,396

    Re: Difficulty in creating a complex bar chart including dates and comments

    I ran into some difficulties here because of the regional differences in the settings between our systems for dates.

    First of all, I converted the data range into an Excel Table for several reasons: Excel tables know how big they are so as you add and delete data from the table, the chart will change automatically.

    I converted a new column in the table to create the End of Production Date into a date my system understands. Your end of production date might be a string and not a true date. If this is the case, you may have to convert it too. I suggest using this formula:
    =IF(ISNUMBER([@[End of production]]),[@[End of production]],DATE(RIGHT([@[End of production]],4),MID([@[End of production]],4,2),LEFT([@[End of production]],2)))

    Basically, it says that if what is in End of Production is a number (date) then use it otherwise parse it out an make the date. The parsing will work on both our systems. Column E is a real date, and I have it formatted to American style. You can format the cells to dd.mm.yyyy format. Likewise column C is a real date. This one I did choose to format to dd.mm.yyyy format.

    yyyy might be jjjj on your system.

    The chart itself is a clustered bar chart with 100% overlap. Where you have the extension shown as a line, I have a different colored bar. I am not sure I can get a line

    Then I added data labels for the orange series. Initially these showed date values. I right clicked on these and selected format data labels. I clicked OFF the current selections and clicked on Value From Cells. Then I highlighted the values in Column D. Since this is an excel table, the chart "knows" that this range is actually the table column so when you add a new row, it will pick it up automatically.

    It's not exactly what you want, but it is close.

    You can delete the table rows. (Highlight the rows you want to delete, right click and select Delete > Table Rows. Then you can enter data in manually or copy and paste column A and B information in. The table will "remember" the formulas and copy them down. The chart will refresh automatically
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-20-2022
    Location
    Bern, Swiss
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (16.0.13801.20442) 32-Bit
    Posts
    2

    Re: Difficulty in creating a complex bar chart including dates and comments

    Hi Dflak,

    I do not know how to express my gratitude.
    Thank you so much for your detailed comment.

    To be honest, you motivated me to learn more about excel.
    And maybe one day I ll be the one helping other here

    I wish you all the best and again, thank you!

    Greeting from Switzerland!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,396

    Re: Difficulty in creating a complex bar chart including dates and comments

    This forum helped me a lot. At first showing me how to do things. Then I learned a lot more when I started helping people out.

    When I applied for my last job, I told them about the 7,000 posts I have hear. That was part of the reason they hired me.

    I am glad to help and good luck.

  5. #5
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    332

    Re: Difficulty in creating a complex bar chart including dates and comments

    This one was still on my to do list.....
    In my view it comes also close to the requested chart.
    The data table however is a bit more complex.
    Attached Files Attached Files
    Kind regards,
    Piet Bom

+ 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] Difficulty creating formula to complete all conditions
    By 4excel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2020, 12:11 PM
  2. Difficulty Creating Chart Per Request
    By fyrewal in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-25-2019, 12:18 PM
  3. Creating chart w/ different dates
    By kimkmmy in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-17-2013, 07:49 PM
  4. Difficulty creating a basic line chart without breaks in line plots
    By brharrii in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-05-2013, 04:29 PM
  5. Difficulty creating charts from complex data set
    By RosaNZ in forum Excel General
    Replies: 11
    Last Post: 08-23-2010, 04:50 AM
  6. Creating a chart of a list of Dates
    By cakonopka in forum Excel General
    Replies: 4
    Last Post: 04-08-2007, 06:47 AM
  7. Difficulty of creating a macro?
    By TylerLange in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2006, 06:02 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