Trying to use data bar formatting

  • I am truly a beginner in Excel and need all the help I can get!
    Okay so I have a table that is $A$2:$N$333 and I would like column A to show a data bar to reflect the data that each row has completed. Example: Row 3 has data in every column up to G now I'd like Cell A3 to show a 50% data bar in that cell to show that only 50% of the data needed for that row has been entered. Is that possible and how would I go about doing so?


    (this is the reply I revieved from tech support)
    If you add another column for the data bar, you can do it with conditional formatting.
    If you insert a column before column A and inserted this formula:
    =COUNTA(B5:O5)/14
    And then use conditional formatting in the cells in column A
    (open) conditional formatting
    Use: format all cells based on their values
    Choose data bar from drop down check box to show data bar only
    (in type) choose number from drop down
    Enter 0 on minimum and 1 on max
    Use solid fill and no border
    and hit okay


    I COULDN'T COPY AND PASTE THE IMAGE SO I DESCRIBED IT AS BEST AS POSSIBLE



    (This is my reply )
    So after I've entered the formula into each cell down column A do I high light all of column A and then select the conditional formatting with Data bar?
    So I did Just that I copied the formula into each cell in column a and then I highlighted the column and did the conditional formatting using Data bars and I got nothing. In the picture it says edit formatting rule. I made a new rule with all of column A highlighted was I supposed to go through the manage rules window instead of make a new and also in the picture it says minimum (Type) Number 0 and max Number 1 was I supposed to put that in as well?
    Thanks for all your help I'm sorry I am so new at this excel stuff


    AND THEN I RECEIVED THIS REPLY


    Yes, you need to have it look exactly like the image I posted for it to work, so you'll need the types to be numbers and values to be 0 and 1.


    OKAY SO THIS ONLY ANSWERED ONE OF MY QUESTIONS AND IT STILL ISN'T WORKING CAN YOU HELP ME? PLEASE


    THANKS FOR ALL YOUR HELP VINTA

  • I created a small sample and attached it here.


    As they said, in A2, use this formula: =COUNTA(B2:O2)/14


    Copy that formula down column A.


    Select A2:A333 > Conditional Formatting > Data Bars > Pick which one you want


    Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Bar Only" box > OK > OK

  • You are amazing, thank you! also is there an easier way to enter this formula while making it recognize the rows changing numbers or is it restricted to only manually entering in this code changing the the row as you go?


    Again thanks so much

  • I don't think that there is much versatility with the data bars (changing color conditionally or disappearing completely).


    One option, if you want to have a green check mark show up in the cell when the value reaches 100%, you can add another Conditional Formatting rule using icon sets.


    Highlight A2:A333 > Conditional Formatting > Icon Sets > 3 Symbols


    Now, with any of the cells in A2:A333 selected, Conditional Formatting > Manage Rules > Edit Rule > Check the "Show Icon Only" box > change the bottom two icons to "No Cell Icon" > change the first Value to 1 and the Type to Number > OK > OK


    This will show the bars and a green check mark when the bar hits 100%.

  • Thank you! Falcon Dude you've gone above and beyond... What is the ("█", mean? Or how do I replicate the black square? Lol. I am sure these are basics for you and I appreciate you taking the time. I do need laymen terms. LOL! One more question say I have data in D4 and D6 and I want them to automatically generate combine in to cell B6. What is the formula I'd use and why? I ask why because if I am able to understand the (< : " =) Symbols then maybe I'll figure out how to create these formulas on my own, and stop wasting your time. Or if you know of a online free training that helps break it down, I'd greatly appreciate it...
    Example: Cell D4 has first and last name
    Cell D6 has address
    I'd like Excel to automatically Combine First,& last name, and address in to cell B6.?. Is this even possible?


    Thanks so much!

  • Welcome back. The formula repeats these --> █ n times where n is the number of cells in the row that are not empty. The repetition of these black rectangles are what creates the bar.


    Just highlight the formula in post #9 and paste it into cell A2.




    If you want to combine data in D4 and D6 into cell B6, you can use CONCATENATE like this: =CONCATENATE(D4,D6) or even easier, =D4&D6


    Note that if you want to put a space in between the last name and address, you can do that like this:


    =CONCATENATE(D4," ",D6)


    or my preferred method, like this:


    =D4&" "&D6


    Put either of those formulas in cell B6.