+ Reply to Thread
Results 1 to 5 of 5

Working with cell data in Charts

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    Scottsdale
    MS-Off Ver
    Office 365
    Posts
    5

    Working with cell data in Charts

    Hi,

    I'm sure this has been asked and answered many times here, but a quick scan didn't turn up the answer I'm looking for..

    I want to create a simple 3D bar graph where one bar is "Sales Goal" and the bar next to it is Actual sales that shows as percentage attained. Conversely, I'd settle for something like those fundraising thermometers where the top line is $100,000 and it gets colored in and fills up as funds come in. I'm basically just looking for a visual aid to represent how close we are to hitting our sales goal

    Simply put, if cell B6 is sales goal and cell B7 is sales to date, I know how to create a bar graph showing those two data points with their accompanying legends. But if I use the 3D chart in percentages, both columns in the graph will show 100%. I need to take B7 and divide it by B6 to get a percentage. I can do that in a separate cell (by formatting to percentage), but ideally, I'd like to perform this calculation in the chart so that's not necessary to have this extra cell cluttering up the spreadsheet and the bar with the legend "actual sales" just fills up next to "Sales Goal" with the corresponding percentage reached to date

    Can anyone help me with this?

  2. #2
    Registered User
    Join Date
    11-05-2019
    Location
    Kaysville, UT
    MS-Off Ver
    Office 365
    Posts
    64

    Re: Working with cell data in Charts

    Is the attached example what you had in mind?
    I do have another column with the calculation but it is 1 character wide and the font is white so nobody sees it.
    I accomplished this using the included 3d chart styles and formatting the data label to pick up a value from the calculation field. Look at the properties for the data label to see how it is done.
    Attached Files Attached Files

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,906

    Re: Working with cell data in Charts

    I'd strongly advise against using 3D chart of any type. It adds nothing to the data and only makes it harder to read.
    Appropriate 3D visualizations are rare, and certainly not for bar charts.

    Have a read of...
    https://serialmentor.com/dataviz/no-3d.html
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    07-18-2016
    Location
    Scottsdale
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Working with cell data in Charts

    Wow yes, this could definitely work! Thanks! But I can't figure out how to copy it?

  5. #5
    Registered User
    Join Date
    11-05-2019
    Location
    Kaysville, UT
    MS-Off Ver
    Office 365
    Posts
    64

    Re: Working with cell data in Charts

    First add the column to get the percentage to your spreadsheet.
    Right click on the chart on my spreadsheet and choose copy.
    Go to your spreadsheet and paste.
    Click on the pasted spreadsheet.
    Click the menu item Chart Design and then the icon Select Data
    Edit where the data comes from for each Legend Entry and Axis Labels to match the data on your spreadsheet and click OK.
    Click on the data labels for the Actual Sales and then right click on them choosing Format Data Labels
    Next to where it says Value from Cells there is a button to Select Range - click on that and change it to where your percentage data is.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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