+ Reply to Thread
Results 1 to 6 of 6

Using Data Bars on Cells but not the cell's value

  1. #1
    Registered User
    Join Date
    07-31-2020
    Location
    Calgary
    MS-Off Ver
    2016
    Posts
    9

    Using Data Bars on Cells but not the cell's value

    I have a set of data that shows a total budget of the project (column A). Let's assume in $1000. I have another source data that shows me actual spend to date, e.g. $600 (column B). Is there a way to show the data bar on column A and reflect the 60% spend. In other words, I would like to cell with $1000 to show a bar across it at 60%.

    I hope that's clear.

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Using Data Bars on Cells but not the cell's value

    c1 = b1/a1 and drag down. Format column C as %.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    07-31-2020
    Location
    Calgary
    MS-Off Ver
    2016
    Posts
    9

    Re: Using Data Bars on Cells but not the cell's value

    thanks Oeldere.

    This doesn't exactly answer my question. I do not want to add a new column C. I want the data bar to show on col A if possible. I have many columns on this sheet and would prefer to min. the use of any helper columns.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Using Data Bars on Cells but not the cell's value

    A1 = b1/1.000

    But probably you don't want that.

    You can't use the input of a cell to get the result (formula) in the same cell (in Excel).

  5. #5
    Registered User
    Join Date
    07-31-2020
    Location
    Calgary
    MS-Off Ver
    2016
    Posts
    9

    Re: Using Data Bars on Cells but not the cell's value

    I was hoping to use the "formula feature" of the data bar somehow to do that but it doesn't seem like it's possible because I can't use relative references in the formula within a data bar

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Using Data Bars on Cells but not the cell's value

    You would need to set up a data bar for each cell as relative references are not supported.

    assuming budget in A1, actual in B1
    then CF Data bar in A1, Minimum set to zero.
    maximum is a Formula, =$A$1/($B$1/$A$1)
    Cheers
    Andy
    www.andypope.info

+ 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. Conditional Formatting of Data Bars based on value from another cell
    By Sanjibghosh in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-27-2017, 07:31 AM
  2. [SOLVED] Excel create “bars” or fill series of cells based on other cell value
    By davva23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-08-2015, 09:16 AM
  3. Replies: 2
    Last Post: 01-08-2014, 08:57 AM
  4. [SOLVED] Data Bars in Conditional Format are not displaying % from 0-100 correctly in the cell
    By maldoncn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 02:17 AM
  5. Cell conditional format - Data bars
    By gjrr4x1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-19-2013, 04:23 AM
  6. Conditional formatting on a Cell with DATA BARS, based on value of another cell
    By nikeshghimire in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-15-2012, 08:13 AM
  7. Tying Chart Bars to specific cells in source data
    By LMclaughlin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-06-2007, 11:29 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