If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charting in a single cell question...
I think your SOL on this one, unless someone is wiser on
charting. You could leave an empty column next to each question, make a vertical X-axis bar chart, remove all formatting entirely and squeeze and stretch it and the row widths and hope it fits. If there is a limited number of questions, you could squeeze about 50 columns together and use the conditional formatting function which will refernce the score and shade appropriately. -----Original Message----- Hi all, not sure if this is the correct section, but here goes: i've got a printed example of a report that i need to approximate in excel if possible. it's a 5 column list : name | city | question 1 | q2| q3| basically a report of question scores by name and city. what i need to do is to show the numeric values in the question score columns as it would appear if i had done a bar chart for only that number - if that makes sense. ex. for suzy, in phoenix, who scored 87% on question 1, i want there to be in the col q1/row suzy cell, instead of just "87%", a colored bar (a la a bar chart) with "87%" at the end of the bar - the bar needs to take up 87% of the width of the cell so that it looks proportionally larger than bob from wisconsin beneath suzy who only scored 57% on Q1. i can't see how to make this tabular representation going the standar charting wizard route and all that, but neither can i find any kind of custom bar-graph like formatting to perform on a single cell. any ideas? thanks! --- Message posted from http://www.ExcelForum.com/ . |
#2
|
|||
|
|||
Charting in a single cell question...
This isn't possible using Excel's charting feature. But you could have
VBA draw a bar covering a percentage of a cell. This macro draws a blue rectangle covering part of cell F2, based on the percentage in cell E2. You could expand it to draw a rectangle next to any cell with a qualifying value (0x1). Sub Macro1() Dim myBar As Shape Dim myRange As Range Dim myPct As Double Set myRange = ActiveSheet.Range("F2") myPct = myRange.Offset(0, -1).Value With myRange Set myBar = ActiveSheet.Shapes.AddShape _ (msoShapeRectangle, .Left, .Top, .Width * myPct, .Height) End With With myBar .Fill.ForeColor.SchemeColor = 12 ' Blue End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ lunchblaze wrote: Hi all, not sure if this is the correct section, but here goes: i've got a printed example of a report that i need to approximate in excel if possible. it's a 5 column list : name | city | question 1 | q2| q3| basically a report of question scores by name and city. what i need to do is to show the numeric values in the question score columns as it would appear if i had done a bar chart for only that number - if that makes sense. ex. for suzy, in phoenix, who scored 87% on question 1, i want there to be in the col q1/row suzy cell, instead of just "87%", a colored bar (a la a bar chart) with "87%" at the end of the bar - the bar needs to take up 87% of the width of the cell so that it looks proportionally larger than bob from wisconsin beneath suzy who only scored 57% on Q1. i can't see how to make this tabular representation going the standar charting wizard route and all that, but neither can i find any kind of custom bar-graph like formatting to perform on a single cell. any ideas? thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Charting in a single cell question...
Maybe you could use the REPT function to mimic a bar.
But this formula in cell A1. B1 contains the percentage. A1: =REPT("|",INT(B1/5)) B1: 50 The division by 5 is so the cell contains a maximum of 20 characters. Also you may want to use the CharacterMap program to choose an alternative character to create a bar. Try U+2588: Full Block. from the arial font. lunchblaze wrote: thanks alot for the responses. yeah, i have a good idea that this was originally done in a more 'robust' illustrator type prog. i'll give the vb route a try if they still want to pursue it! thanks again. --- Message posted from http://www.ExcelForum.com/ -- Cheers Andy http://www.andypope.info |
#4
|
|||
|
|||
Charting in a single cell question...
Treat multiple columns as though they constitute a single bar and you
can do what you want with simple conditional formatting. Suppose your data are in column A:E. Suppose suzy is in row 2 and bob in row 3. Since you have 3 questions, you cannot create one bar from 100 columns. It also doesn't make sense for aesthetic reasons. I decided to create one bar from 25 columns. So, select 25 cells from F6:AD2. Set the column width to 0.5. Set the conditional formatting (Format | Conditional Formatting...) to 'Formula Is' and specify the formula as =$C2/4(COLUMN()-6) Click the Format... button and select an appropriate color from the Patterns tab (I used green). Do the same for 25 more cells from AE2. This time, the conditional formatting formula should be =$D2/4(COLUMN()-31). Also, you might want to use a different color. Repeat for the 3rd question. Experiment with the number of columns per bar (i.e., number of columns per question) and the column width to achieve the desired aesthetic effect. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , lunchblaze says... Hi all, not sure if this is the correct section, but here goes: i've got a printed example of a report that i need to approximate in excel if possible. it's a 5 column list : name | city | question 1 | q2| q3| basically a report of question scores by name and city. what i need to do is to show the numeric values in the question score columns as it would appear if i had done a bar chart for only that number - if that makes sense. ex. for suzy, in phoenix, who scored 87% on question 1, i want there to be in the col q1/row suzy cell, instead of just "87%", a colored bar (a la a bar chart) with "87%" at the end of the bar - the bar needs to take up 87% of the width of the cell so that it looks proportionally larger than bob from wisconsin beneath suzy who only scored 57% on Q1. i can't see how to make this tabular representation going the standar charting wizard route and all that, but neither can i find any kind of custom bar-graph like formatting to perform on a single cell. any ideas? thanks! --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|