+ Reply to Thread
Results 1 to 4 of 4

Chart with rows shifted to the left when leading values are zeros/empty etc.

  1. #1
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Chart with rows shifted to the left when leading values are zeros/empty etc.

    Hello, I have a table and I need to plot it.

    However rows are something like:
    row title: 2012 values: X1 X2 X3 X4 etc.
    row title: 2013 values: EMPTY Y1 Y2 Y3 etc.
    row title: 2014 values: EMPTY EMPTY Z1 Z2 etc.

    The chart shroud show no empty values: the first non empty (or no zero) value is the the first to be displayed.
    In practice the chart should show:
    row title: 2012 values: X1 X2 X3 X4 etc.
    row title: 2013 values: Y1 Y2 Y3 etc.
    row title: 2014 values: Z1 Z2 etc.
    Basically the user wants to compare different slopes.

    I have many of these charts, so I don't want to adjust rows manually.
    I have checked the sheet options but it seems to me that it is possible to adjust a single empty value in a sequence, but not to delete all empty/zero in the left side and then shift the full row.
    Also I am trying now to filter the data before plotting it, but with no success so far.

    Any other ideas? Any link to suggest?
    Thank you
    F

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Chart with rows shifted to the left when leading values are zeros/empty etc.

    You could use the techniques of dynamic charting, i.e. create a range names for each series. The formula in the range name definition finds the cells with data in the the range. Then use the range names in the chart series source dialog instead of the worksheet range.

    In order to give you a more detailed solution, please upload a workbook with some sample data and existing charts.

  3. #3
    Registered User
    Join Date
    02-10-2020
    Location
    Italy
    MS-Off Ver
    365 ProPlus
    Posts
    7

    Re: Chart with rows shifted to the left when leading values are zeros/empty etc.

    Quote Originally Posted by teylyn View Post
    In order to give you a more detailed solution, please upload a workbook with some sample data and existing charts.
    I attach some data.
    I want to shift the lines in the chart to the left hand side, so that all lines share the same origin and zeros are not shown
    So users can compare the different slopes.
    My attempt is to filter the data in some way before plotting, so that the chart automatically adjusts.
    Anyway any suggestion is welcome.
    Thank you.
    F
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Chart with rows shifted to the left when leading values are zeros/empty etc.

    Doing a bit of guessing that the output should display the value of some fund in the first month of existence, the second month of existence etc. as opposed to the value on a particular date.
    Using Get & Transform, which should be standard in the 365 version, transform the data using the following in the Power Query advanced editor:
    Please Login or Register  to view this content.
    Add a column to the PQ table, with the header of Month and which is populated using: =IF(A2<>A1,1,SUM(D1,1))
    For the Pivot Table put Month in the Rows field, FY in the Columns field and Value (Sum of) in the Values field.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Using IF and VLOOKUP to compare two values that are not similar (leading zeros for id)
    By usurrao in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-07-2019, 02:28 PM
  2. [SOLVED] Removing leading zeros then sort, then place back original values
    By Eduards in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2019, 11:13 AM
  3. [SOLVED] Macro that deletes empty columns but also removes leading zeros
    By BigTP in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2018, 07:31 AM
  4. Replies: 5
    Last Post: 01-28-2013, 07:29 PM
  5. Replies: 1
    Last Post: 10-25-2012, 05:46 AM
  6. Formatting query, incremental values with leading zeros
    By shockeroo in forum Excel General
    Replies: 5
    Last Post: 04-08-2009, 08:28 AM
  7. Leading Zeros in Numeric Values
    By DBavirsha in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-04-2005, 02:06 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