Round Cells??

[Avatar]

Greetings All,

This may well be the stupidest question I’ll ever ask but:

Does anyone know away to make a cell round? (Shakes head) I need a coloured circle in a cell. The colour is based on the contents (percentage) of the cell without using code? Maybe something like the shaded area in the cell to be a circle instead of the whole cell?

Background:
Currently the company I work for displays KPI results in circles in a spreadsheet. This means that each month someone has to insert lots of coloured circles, enter a value in them, then change the colour manually depending on the score (percentage). This also means that these figures can’t be automatically read using linked cells in other workbooks.

These figures are gathered from other workbooks so I made a workbook that gathers the results from the sheets and based on conditional formatting, colours the cells, but I’ve been told we can't use it because the cells are not circles.

Can anyone help?

 

[grd]

Why not create a graph of the data showing a pie chart?

 

[cfree36]

This may be a stretch... but if the circles were transparent, you could have the data directly in the cell rather than in the text of the circle.
This way you could reference the cells on the sheet.

You could then color the cells based on conditional formats... but then the whole cell would be colored. You would still have a circle... but colors would go 'outside the lines'.

Just an idea... I don't think you can actually make the cells round.

 

[grd]

Another thought is that if you made the cells very small and square and you could create a "pixellated" circle with conditional formatting.

To help do this you could perhaps create a transparent circle and then copy and paste a formatted cell throughout the circle.

 

[Avatar]

Greetings,

grd:
Unfortunately a pie chart wouldn't work because the layout is a company standard . Table format with months across the top, processes down the side, with coloured circles for each month for each process.

cfree36:
I had considered that but as you said the whole cell would be coloured, not just the circle.

grd:
I've tried that before, but it always looks really messy, plus with 20+ rows per process, external cell referencing would be a pain in the ****. (it just seams wrong somehow??)

 

[grd]

Greetings,
grd:
I've tried that before, but it always looks really messy, plus with 20+ rows per process, external cell referencing would be a pain in the ****. (it just seams wrong somehow??)

I suggest another KPI....

Somone to improve the method of creating the reporting of KPIs to be more efficient!! eg squares.

Just because it is "Company Standard" does not mean it can not be changed.

However, I'm still of the opinion that the lots of little squares (say, 5 pixels each but depends on scale) to create a circle will work although it would depend on how many different colours are used. With regard to external cell referencing all you need to do is create a conditionally formatted cell to show the colours you need and then create an absolute reference to it and copy and paste into the area under a circle.

To reduce the edge pixellation use the circle you created and set a white border size of 5 pixels, or whatever to give a chunky border hiding all the white bits.

To get a large number in the centre just merge the central cells and link to the external number.

To get a key to top and side merge relevant cells again.

Hey presto, coloured circles – it works because I’ve tried it but it does depend on numbers of colours required!

PM me with e-mail address if you want a copy.

Graham