There are things you might find useful in a spreadsheet I made for a "real world" need in my life. (You can download a copy of it... link near bottom of this page.)
This essay discusses some use of Conditional Formatting. (There are other goodies in the worksheet, too, if you look around it!)
The job I was working on involved analysis of some EXiF data extracted from a batch of photographs. I've also written a tutorial on using Phil Harvey's ExifTool to extract data from a batch of digital photos.
Most of this was based on the following. If you want a copy, the link will fetch a .pdf...
From that...
"Make sure, that item Data/ Calculate/ AutoCalculate is active. It is need for conditional formatting to work correctly."
===
We start by putting a condition on a cell.
Select a cell or set of cells that you will put the condition on. Just one is fine for now.
Click: Format/ Conditional/ Condition...
That should open the "Conditional formatting for (the cell(s) you selected earlier)" dialog.
For taste of how this works, set up the following...
Cell value is > 10
Apply style: Good (A standard style that "comes with" LibreOffice)
Still in the "Conditional formatting..." dialog,
Click the "Add" button...
And set up a second condition as follows...
Cell value is <= 10
Apply style: Bad (Another standard style)
Click "OK", to exit the dialog.
Put 10 in the cell... the background should go green
Put 9 in the cell... the background should go red.
---
To give other cells the conditional formatting you have set up...
RIGHT-click on the cell (or one of the cells) you have already given the condtional formatting.
Invoke "clone formatting".
Put the mouse pointer on the first the cells you want formatted thus
Press the (left) mouse button on the mouse, and keep it pressed.
Drag the pointer to the other end of the block of cells you want to have the conditional formatting.
Release mouse pointer.
Presto! Those cells are re-formatted! (And the "clone" state is ended.)
---
Another useful tool...
If you use Format/ Conditional/ Manage, you can see where you've applied conditional formatting. And you can change the conditions if you click "edit", or remove the conditional formatting from whole blocks.
---
Doing It Your Way...
What we have is all well and good, but what if you want more than green and red?
In moment we're going to create a CELL STYLE, and then use that in a condition.
We'll create an alternative to the "Bad" style.
Go to an empty cell, put 9 in it, and then format the cell for...
Font/ Font-style: Bold-Italic
Font Effect/ Font-color: red
Background/ Color: right yellow
(Exactly what you do doesn't matter.)
Close the "Format Cells" dialog.
With the cell selected, use the main menu to invoke "Styles/ New style From Selection"
Give it a name. (Hint: Start the name with a zero, and it will be at the top of lists. Then use something special to you. (I use "tkb", my initials, which, happily don't seem to arise in other contexts. If you are Simon Andrew Davidson, you may need to choose a different "special ngram".)
I've called the new style we just created 0tkbBad
---
Let's use our custom style!
Use the main menu "Format/ Conditional/ Manage.
If by now you've assigned a conditional format to other ranges of cells, select the one we did at the start of this. (If you haven't, it will already be selected in the "Manage Conditional Formatting" dialog.)
Click Edit.
Click on the condition you want to apply the new style to. (We set up two conditions, remember.)
Click on the drop down next to Apply Style. Your made-it-yourself style should now be in the list of available cell styles! Click that; click OK to leave the Conditional Formatting Manager, and you should be returned to your spreadsheet with different formatting in the cells with numbers below 10 in them.
Ta Da!
---
Finally... let's get FANCY!
In the demo spreadsheet, column K changes color if the day changes between rows if there is at least one row for each day in the range in the spreadsheet. (In the job this spreadsheet was set up for, the rows will be in chronological order.)
(I wanted to be able to see at a glance when the day changes as you go down the rows.
I did this with conditional formatting.
Instead of setting the condition based on a condition in the cell, I used a FORMULA based condtion.
And these are the formulae for cell K22...
Condition 1: Formula is MOD(H22,2)=0 (apply one style)
Condition 2: Formula is MOD(H22,2)=1 (apply alternate style)
Don't worry about "what about when we are on a different row?"... the "22" in the H22 *will* be adjusted as necessary.
The MOD function is a useful little tyke if you haven't come across it before. It give you the "remainder" from the simple sort of dividing that you did in your early school days...
ANY (whole) number DIV'd by 2 will either give 0 or 1.
An even number DIV'd by 2 will give 0.
An odd number DIV'D by 2 will give 1.
You can download a zip file containing the spreadsheet discussed here. (Please let me know if your computer doesn't like downloading a .zip file? Contact details below.)
If you found this of interest, please mention in forums, give it a Facebook "like", or whatever. If you want more of this stuff: help!? There's not much point in me writing these things, if no one hears about them. Does anyone feel they are of any use? If YOU do- please spread the word!
Search across several of my sites at once with a Google search button.
Or...
|
Please also note that I have three other sites, and that this search will not include them. They have their own search buttons.
My site at Arunet. (Alas, this only allows http access, no httpS... but as you won't be entering any personal data, how much does that matter to you?)
It's many things! I've been creating pages for over 20 years. I sometimes link to an old page from a new page.
For some of my sites, e.g. Arunet, I can't offer you httpS:// access. (As you are not asked to input any information, that's moot, but it "worries" search engines and browsers.) So I'm moving to my new, all singing, and "will do the httpS:// dance site", WYWTK.com. Sometimes you'll be there, sometimes at one of the old site pages... if you are sufficiently brave.
Why "WYWTK"? It comes from "What You Want To Know".
Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. It passes in some important ways, but still needs work to fully meet HTML 5 expectations. (If your browser hides your history, you may have to put the page's URL into the validator by hand. Check what page the validator looked at before becoming alarmed by a "not found" or "wrong doctype".)
AND tested for
. . . . . P a g e . . . E n d s . . . . .