Articles Blog

23- Libre Office – Calc, Open Office — Calc, Excel Tutorial — Conditional Formatting

23- Libre Office – Calc, Open Office — Calc, Excel Tutorial — Conditional Formatting

for those who want to download the worksheet for the next examples go to
video tutorials and then office – spreadsheet and down at the
bottom that page I will add the download link for the .ods spreadsheet
now we’re going to mix the conditional and the formatting and bring it all together
so what I’m going to start with here is I”m going to click on this accounting and I want to go into format conditional formatting I want to say – If the cell value is equal to and I’m going to use a double quote Accounting – typed out
and then another double quote the cell style it’s set at default
we want to change that to… We’re going to create a new style
a lot of people from Excel are going to see this too because you have to
create a new style – but I could choose from the blue default or the blue heading
that I have but, I’m going to select new style and I’m
going to call this new style… Accounting to make them stand out
I’m going to give a yellow background and i want my font to be bold I’ll click okay and we see bold yellow accounting and I want this format to be for the column
so I will click on the paintbrush there and highlight all of this column… all the way down anywhere it has accounting stands out – so that’s kinda handy we can also use this to make people’s age… let’s say to format conditional formatting we want everyone under 40 – we need to select less than 40 – We want to create a new style
and if I don’t give it a title it will remain untitled and I”m going to
change the background to green
we’ll make it a brighter green than than and a second condition is if it is greater than 40 We want to say greater than or equal to
40 we will make our new style untitled 2 and it’ll be a background gray 20 percent clicking okay this one shows a green but we still want to apply this to
the whole column instead of just this one
so I will click on the format paintbrush highlight the whole column – now things standout
Why didn’t our people turn gray? Let’s go back into our format
We’ll check that format conditional formatting if it is less than or equal We had less than for everything,
we need to say greater than or equal to
now we should be good and again I need to copy this
all the way down now we should be all set. and there we have the gray or 40 and green under 40 another thing we want to check for
would-be blanks these are nothing so I will… I’ll start with it here
because it is easier to copy from the top clicking on format…
conditional formatting I see if cell value is equal to nothing – I will use the account style
There’s our accounting style and we don’t see anything
because this has a value in it, when I do the paintbrush see that the the blank ones standout as yellow
we can also do this with… refer to a different cell see here we have a last name Taylor and i
want to format this as conditional formatting to change things up we are going to give it
a new style untitled 3 we are just going to
change font – let’s make it bold and italic we want to say IF instead of equal to the cell value we want to say formula now we want to say if we are in B5 so I want this to have L5 – I need to have equals=L5=”Group D” and we click okay so we can see that he is italicized
but he is not bold let’s fix that format conditional formatting actually, I’m just going to go in through here
show that we have our untitled 3 and I want to modify that
so I wanna make it bold and Italic Let’s also put some borders around it
just regular borders – now we can see it and we want this one as well so if I copy this format over
and we’re gonna have a quick change with that because we need to say format – conditional formatting formula m5 – we want to change this to L now we got them all and we use our little paintbrush and we can see everyong in Group D
is now bold italicized we’ve done our ages so we’ve got a
little crazy with our formatting here but you get the idea you
might want to do a lot less but I just enough to point out the things
really matter but that this was just to demonstrate how the different was of using conditional formatting all all cool all

29 thoughts on “23- Libre Office – Calc, Open Office — Calc, Excel Tutorial — Conditional Formatting”

  1. @BattleGround16 Sorry, the video always shows the formatting after the data was entered, you can format the cells before data is entered. But you still need to format the cells. One way is by typing in the formatting to be yellow, then click the paintbrush, then click the column K (to highlight the entire column) then place the paintbucket in the highlighted area and click (to format the column). Nothing looks changed, but when you type accounting column K, they will have a yellow background

  2. @philipkthompson I bought a new microphone, but it wasn't until near the end of the LibreOffice Calc series – at the time of this recording I was recording this as loud as I could without distortion. If you have some headphones, please try them for this video. Thanks for watching!

  3. Thanks! After struggling with this feature, in the first minute of your video I learned what I needed: the double quotes. Why doesn't the built-in help explain that?

  4. The people that create and support LibreOffice do a great job with the software, but sometimes their help isn't as clear as it could be, that is one of the reasons that I did this video tutorial series. I'm glad my video was able to help out – thanks for watching!

  5. If when you did the original conditional formatting of cell B5, you put the formula as ""=$L5="Group D"" then applied the formatting from B5 (Taylor) to C5 (Mark) would it do the same thing as changing to C5 conditional formatting? I only vaguely understand anchoring, but I know that if I want to anchor a cell's value in a formula I usually us two "$" ($B$5).

  6. You are correct MrsSinette! The anchoring will work that way. And anchoring will work that way for conditional formatting. Since we are applying this conditional formatting to a column, we only want to anchor the column making this $L5. We can also apply anchoring to just a row L$5 or as we commonly see it anchored to a single cell anchoring both the row and the column $L$5. Thanks for pointing that out!

  7. wow thanks! this video has helped me alot, especially with what i'm doing. I'm working with numbers, i'm populating 5 cell rows(A1-E1)going down columns(A-E).
    i would like to have my spreadsheet set up to where i'm able to simply open it up, enter my numbers and see them color filled as i enter them into the cell range. i would color them based on their number range (1-9=yellow)(10-19=blue) ect. this is fantastic automation! and to think i use to do this all manually >_< you saved me time.

  8. Hi GameCasters,
    Thanks for letting me know how use what you learned in my video!
    I'm glad you don't have to do that all manually; anymore.

  9. I am an engineering student and these videos are helping out a lot. If I had any spare change, I would most certainly donate to your website. Thanks for your contribution in helping others to succeed in life.

  10. I noticed on your website that you are knowlegable at fixing mistake that users make on their computers. So how do I reboot my labtop when I deleted the Authenticated users, system, admin, and users on the C drive?
    this is what i did: right click C drive, clicked properties, clicked security, and deleted. Thanks by the way

  11. That's a tricky one, but your computer might not be a goner yet. You may be able to power up into a linux distribution and restore those files. I'm not proficient enough in linux to know how to do this. Larry Bushey of the Going Linux podcast, may know a way. I know they did an episode about fixing windows problems with linux. You may want to check his website and send him an e-mail.

  12. Hello, man. I have one question: What do you do when you have a condition for example: to be larger and equal to 100 and then smaller than 200 – yellow color? But the first condition is smaller than 100 – red color. The final condition is larger than 200 – green color.

    Thanks in advance. 🙂

  13. Hi, I have a very simple spreadsheet made and one of the problems I am having with it is when my dollar amounts are added. In the columns where there is no value, it shows $0.00. Is there a way, when there is no value, the cell just stays blank?
    Thanks for the great tutorials !!

  14. Thank you for the last Tip.  At: 1:06, the Newest ver. [5] contains NO: set NEW STYLE.  My current setting for 'fixx' reads 'agree' rather than 'fix',  [Color is correct] .  I have s'ch'd everywhere … I, finally, have come to the ultimate source.  Can you Help?

  15. Line 4 (in light blue) is the header. Line 5 to infinity is the data. My question is how can you make the ROW AUTOMATICALLY alphabetize itself AFTER you have made the entry, so you do not have to stop and do a sort every so often. I am making a membership list for a non profit organization. At 1st the: header line will have:: Last Name First Name Address , City Stat, Zip[ (CSZ will come from a drop down box) Cphone LandLine email addy notes. So as I fill out the data underneath those columns, I want them to sort them selves alphabetically as the each entry is made. Is there a way to do this?

  16. I’m just wondering if this works the same as Microsoft excel? Like if I follow an advanced tutorial of excel will it basically be the same as using open office? Excel cost money now. I’d rather use something that’s free.

Leave a Reply

Your email address will not be published. Required fields are marked *