Ask Your Question

Putting Text over cell

asked 2017-11-19 01:54:57 +0100

LON gravatar image

Hello, I created a table that changes the colour of a cell depending of the result of a RNG.

For example if value of RNG lower than 50 the colour is purple if the value of RNG is higher than 50 colour is orange.

I want to put text over the cells in the table so it is easier for me to view the table but I dont want to overwrite the formula in each cell.

(I have the number of the RNG in Cell b2 and the colour of the cell in the table changing according to that cell value (=b2))

Thank you in advance

edit retag flag offensive close merge delete


"RNG" means Random Number Generator perhaps? It would help to provide an example formula in a cell and the text over the cell. Also describe the method you used for coloring the table. See

Jim K gravatar imageJim K ( 2017-11-19 05:15:52 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-11-19 05:04:40 +0100

Jim K gravatar image

updated 2017-11-20 19:13:29 +0100

Use Conditional Formatting.

  1. Enter =RANDBETWEEN(1;100) in B2.
  2. C2 through D4 will be our table. Enter =SUM(4;5;6) in C2 as an example.
  3. Create two cell styles, one for each color. A convenient way to do this is to set a cell background color to purple, then in the Styles and Formatting sidebar, click New Style from Selection and name it "Purple".
  4. Select C2 through D4, go to Format -> Conditional Formatting -> Manage and press Add.
  5. Condition 1 Formula is $Sheet1.$B$2<50, Apply Style "Purple".
  6. Condition 2 Formula is $Sheet1.$B$2>=50, Apply Style "Orange". OK. conditional formatting


Why does the formula for D7 need to be =B3? If you use conditional formatting as explained above (with $Sheet1.$B$3<20 for D7:E8), then set the formula for D7 to =LEFT(D6,1) & $C7 and it produces the results in your second image.

image description

edit flag offensive delete link more

answered 2017-11-19 05:46:54 +0100

LON gravatar image

updated 2017-11-19 05:57:45 +0100

Thank you for the answer, unfortunately it isn't quite what I was looking for

Perhaps I didn't expose my case explicitly i will try to again with some images

This is what i have:

AS you can see the cells change colour depending of the result of the RNG, (Cell D7 is connected to =B3) for example if RNG number is greater than 20 D7 will change to orange.

What i would like to be able to do is to add text over the cell so I don't overwrite the formula at D7 and i can easily identify which cell I am looking in big tables

Something like this :

As for the method i changed the background colour for the cell and i put the Font colour the same as the cell:

Thank you very much once again for taking your time to answer my question

edit flag offensive delete link more


This information does not belong in an answer. Can you edit the question and move it there? Anyway, I edited my answer in response.

Jim K gravatar imageJim K ( 2017-11-20 19:02:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-19 01:54:57 +0100

Seen: 42 times

Last updated: Nov 20 '17