How can I use Excel data bars to format cells with text values?












5















I'm creating a spreadsheet for project planning and have a column that lists estimated effort as follows:




  • Unknown

  • Very High

  • High

  • Medium

  • Low

  • Very Low

  • None


I like the visual cues provided by data bars in Excel, and would like to use them to show relative effort for the "Very High" to "None" values. However I can't figure out how to do this. I thought about adding a numeric value to each value - e.g. "5 - Very High" - but I'm now stuck trying to apply the data bar criteria to a substring of the cell contents.



Is there a way to do this, or an alternate approach that would achieve the same results?










share|improve this question























  • Does it have to be data bars or can you use color scale?

    – Raystafarian
    Oct 21 '14 at 14:25













  • I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

    – Roger
    Oct 21 '14 at 14:27
















5















I'm creating a spreadsheet for project planning and have a column that lists estimated effort as follows:




  • Unknown

  • Very High

  • High

  • Medium

  • Low

  • Very Low

  • None


I like the visual cues provided by data bars in Excel, and would like to use them to show relative effort for the "Very High" to "None" values. However I can't figure out how to do this. I thought about adding a numeric value to each value - e.g. "5 - Very High" - but I'm now stuck trying to apply the data bar criteria to a substring of the cell contents.



Is there a way to do this, or an alternate approach that would achieve the same results?










share|improve this question























  • Does it have to be data bars or can you use color scale?

    – Raystafarian
    Oct 21 '14 at 14:25













  • I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

    – Roger
    Oct 21 '14 at 14:27














5












5








5


0






I'm creating a spreadsheet for project planning and have a column that lists estimated effort as follows:




  • Unknown

  • Very High

  • High

  • Medium

  • Low

  • Very Low

  • None


I like the visual cues provided by data bars in Excel, and would like to use them to show relative effort for the "Very High" to "None" values. However I can't figure out how to do this. I thought about adding a numeric value to each value - e.g. "5 - Very High" - but I'm now stuck trying to apply the data bar criteria to a substring of the cell contents.



Is there a way to do this, or an alternate approach that would achieve the same results?










share|improve this question














I'm creating a spreadsheet for project planning and have a column that lists estimated effort as follows:




  • Unknown

  • Very High

  • High

  • Medium

  • Low

  • Very Low

  • None


I like the visual cues provided by data bars in Excel, and would like to use them to show relative effort for the "Very High" to "None" values. However I can't figure out how to do this. I thought about adding a numeric value to each value - e.g. "5 - Very High" - but I'm now stuck trying to apply the data bar criteria to a substring of the cell contents.



Is there a way to do this, or an alternate approach that would achieve the same results?







microsoft-excel microsoft-excel-2007 conditional-formatting






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Oct 21 '14 at 14:14









RogerRoger

1,17731129




1,17731129













  • Does it have to be data bars or can you use color scale?

    – Raystafarian
    Oct 21 '14 at 14:25













  • I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

    – Roger
    Oct 21 '14 at 14:27



















  • Does it have to be data bars or can you use color scale?

    – Raystafarian
    Oct 21 '14 at 14:25













  • I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

    – Roger
    Oct 21 '14 at 14:27

















Does it have to be data bars or can you use color scale?

– Raystafarian
Oct 21 '14 at 14:25







Does it have to be data bars or can you use color scale?

– Raystafarian
Oct 21 '14 at 14:25















I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

– Roger
Oct 21 '14 at 14:27





I know I can change color, but prefer not to have a wide range of colors on the spreadsheet - I think it would look distracting as I have multiple effort columns.

– Roger
Oct 21 '14 at 14:27










3 Answers
3






active

oldest

votes


















4














I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.



The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.



enter image description here






share|improve this answer
























  • I was afraid of this. Thanks for confirming.

    – Roger
    Oct 22 '14 at 0:44



















2














I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.



Steps:

1. Type values for databars where the inputs will be i.e.(C2:C8).

2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere

3. Highlight databar cells and apply databar conditional formatting

4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:

5. Replace General with "Unknown" and click OK

6. Cell B2 will now say Unknown. Left Align the text.

7. Repeat steps 4 though 6 for each databar cell



You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
End Sub


Steps 2 through 6:



Steps 2 through 6






share|improve this answer

































    -1














    you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting






    share|improve this answer
























    • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

      – fixer1234
      Dec 18 '18 at 5:17











    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f829513%2fhow-can-i-use-excel-data-bars-to-format-cells-with-text-values%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    4














    I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.



    The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.



    enter image description here






    share|improve this answer
























    • I was afraid of this. Thanks for confirming.

      – Roger
      Oct 22 '14 at 0:44
















    4














    I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.



    The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.



    enter image description here






    share|improve this answer
























    • I was afraid of this. Thanks for confirming.

      – Roger
      Oct 22 '14 at 0:44














    4












    4








    4







    I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.



    The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.



    enter image description here






    share|improve this answer













    I tried to do this yesterday - I don't believe you can achieve this with conditional formatting data bars, which can only apply to cells containing numerical values.



    The way I got round it was to overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent.



    enter image description here







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Oct 21 '14 at 15:28









    Andi MohrAndi Mohr

    3,39042044




    3,39042044













    • I was afraid of this. Thanks for confirming.

      – Roger
      Oct 22 '14 at 0:44



















    • I was afraid of this. Thanks for confirming.

      – Roger
      Oct 22 '14 at 0:44

















    I was afraid of this. Thanks for confirming.

    – Roger
    Oct 22 '14 at 0:44





    I was afraid of this. Thanks for confirming.

    – Roger
    Oct 22 '14 at 0:44













    2














    I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.



    Steps:

    1. Type values for databars where the inputs will be i.e.(C2:C8).

    2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere

    3. Highlight databar cells and apply databar conditional formatting

    4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:

    5. Replace General with "Unknown" and click OK

    6. Cell B2 will now say Unknown. Left Align the text.

    7. Repeat steps 4 though 6 for each databar cell



    You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:



    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
    End Sub


    Steps 2 through 6:



    Steps 2 through 6






    share|improve this answer






























      2














      I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.



      Steps:

      1. Type values for databars where the inputs will be i.e.(C2:C8).

      2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere

      3. Highlight databar cells and apply databar conditional formatting

      4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:

      5. Replace General with "Unknown" and click OK

      6. Cell B2 will now say Unknown. Left Align the text.

      7. Repeat steps 4 though 6 for each databar cell



      You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:



      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
      End Sub


      Steps 2 through 6:



      Steps 2 through 6






      share|improve this answer




























        2












        2








        2







        I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.



        Steps:

        1. Type values for databars where the inputs will be i.e.(C2:C8).

        2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere

        3. Highlight databar cells and apply databar conditional formatting

        4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:

        5. Replace General with "Unknown" and click OK

        6. Cell B2 will now say Unknown. Left Align the text.

        7. Repeat steps 4 though 6 for each databar cell



        You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:



        Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
        End Sub


        Steps 2 through 6:



        Steps 2 through 6






        share|improve this answer















        I was able to use Brynjar Hallmannsson's tip to recreate what Andi did.



        Steps:

        1. Type values for databars where the inputs will be i.e.(C2:C8).

        2. Link databar cells to inputs (i.e. B2 Formula: =C2) - you can just use the data bar cells as inputs but your numbers wont be visible anywhere

        3. Highlight databar cells and apply databar conditional formatting

        4. Right click on databar cell B2 and go to Format Cells -> Number -> Custom -> Type:

        5. Replace General with "Unknown" and click OK

        6. Cell B2 will now say Unknown. Left Align the text.

        7. Repeat steps 4 though 6 for each databar cell



        You can use VBA if you want to control the text of the databars via another group of cells. Lets say D2:D8. Use the OnChange Event to format the Custom Type of the databar cell whenever the associated cell in column D is changed. Like so:



        Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Target.Offset(0, -2).NumberFormat = Chr(34) & Target.Value & Chr(34)
        End Sub


        Steps 2 through 6:



        Steps 2 through 6







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Feb 8 at 5:05









        fixer1234

        19k144982




        19k144982










        answered Feb 8 at 4:26









        EzAnalystEzAnalyst

        212




        212























            -1














            you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting






            share|improve this answer
























            • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

              – fixer1234
              Dec 18 '18 at 5:17
















            -1














            you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting






            share|improve this answer
























            • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

              – fixer1234
              Dec 18 '18 at 5:17














            -1












            -1








            -1







            you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting






            share|improve this answer













            you can paste the text in double quotation marks into the type section in custom format for the cell, when you then enter a numeric value in the cell the text will appear. to create the bar use conditional formatting







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Dec 18 '18 at 3:47









            Brynjar HallmannssonBrynjar Hallmannsson

            1




            1













            • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

              – fixer1234
              Dec 18 '18 at 5:17



















            • Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

              – fixer1234
              Dec 18 '18 at 5:17

















            Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

            – fixer1234
            Dec 18 '18 at 5:17





            Welcome to Super User. Any chance you could show a more explicit example with some screenshots to better explain the how-to? Thanks.

            – fixer1234
            Dec 18 '18 at 5:17


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f829513%2fhow-can-i-use-excel-data-bars-to-format-cells-with-text-values%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            How to make a Squid Proxy server?

            Is this a new Fibonacci Identity?

            19世紀