Use Python Xlsxwriter To Create Excel Conditional Formatting(Part 4)

hi friends welcome back in this video we'll talk about conditional formatting with the excel writer python library this video is a response to one of our youtube friends request so you guys can feel free to ask me anything and i'll try my best to answer your questions okay let's dive into today's topic conditional formatting as excel users we're probably familiar with the conditional formatting feature and most of the time we use it to highlight certain values or make simple data visualizations so we can find the conditional formatting button here in excel and as you can see there are many options to choose from i always like to go to the bottom where the new rule and because this window summarizes all the available options and i can just pick what i need from here as you know there are lots of possibilities with conditional formatting so we cannot talk about all of them we'll walk through a few common ones in the video but if you have any questions on any specific scenarios leave a comment down below and i will get back to you soon we're going to rely on some of the things we talked about in the previous video and you can find the link to that video on the screen or in the description down below okay let's open up a python environment import writer library and create an excel workbook then we'll create three excel formats one for red one for yellow and one for green i'm going to generate some values to use for the conditional formatting examples i'm actually going to show this excel sheet in the background so you have a better understanding on what we're trying to achieve and this excel file was actually created from our python code we also need to talk about a few new concepts here so first a conditional formatting in excel is superimposed over the existing cell format meaning that the conditional formatting will override some of the existing formats let me show you an example in excel so here is a list of numbers from 1 to 10. let's add some format by changing the background color to yellow and the text color to red and we also apply a strike through to the text then let's apply conditional formatting we'll change the format for any values between 4 and eight and in the format window you can see that not all options are available to us for example the fund or the size are grayed out you can still change the style whether you want to bold or regular funds and we can still change the border but still some options are grayed out here there seem to be no restrictions on the color selection so let's change the format to using a black background color and white text without the strikethrough okay so now we see that the conditional formatting basically overrides existing format that's the first new concept the second one is that in the excel writer library we use the method worksheet dot conditional format to create a conditional format of course there are a few arguments for this method and the first one is the cell address it can be a single cell or a range and we can use either the a1 style or the row column style notation to represent cell or the range address the second argument must be a dictionary which contains the properties that describe the format some of the main properties are type criteria value and format so type is the type of the format whether we want to apply the cell or blue text or do we want to find the largest or the smallest numbers from a list criteria is the part of the condition we want to specify for example do we want to select numbers that are larger than something or smaller than something and value is the second part of that condition so for example we can select values between let's say five to seven and format is of course the format we want to apply for the cell but remember that not all format properties can be modified during a conditional formatting let's get started with the code and you can find the link to the code in the description below alright so for the first example we're going to do some color coding for our numbers for the numbers larger than 7 we're going to use green and for the numbers smaller than 5 we're going to use red and for any numbers in between between 5 and 7 we're going to use yellow so i'm just going to copy over the code and i'll walk through the code what each line does so this very first line this is basically just writing kind of the table header and we write the first list to cell b2 and we write the second list to cell c2 so list one is a list of integers from zero to nine and the list two is also a list of integers from zero to nine but not in any specific order and we're going to apply the first dimensional formatting so the range we want to apply the format for is from b2 to c11 which is this range over here and the type we want to apply is on the cell so in general if you're trying to apply a conditional formatting for number values then you want to use cell for the type the criteria we say is greater than 7 and the format we're going to use is the green color then the second conditional format does almost the same thing except that for the condition we are saying okay for numbers smaller than five we use the red color and last but not least for the numbers between five and seven we use the format yellow and if you run this you will get this kind of format over here let's move on to the next example so in this example essentially this is similar to what we had over here except that we're not hard coding the threshold five and seven instead we are inputting the threshold in the cell so we can change these numbers later on let me just do a test if i change that to three then you see that there are sales highlights changing and let me try that again change it to five and you see some other cells are changing so let's take a look at how this is achieved so again similar things we write the list of numbers to excel and for the conditional format section we want to format cell and then greater than so instead of hard coding the threshold numbers now we want to input the cell address into the value property and in general whenever you want to use the sale address for the value property you almost always want to use the absolute references meaning that your sale address should contain these dollar signs otherwise could mess up your conditional format and the second conditional format method does the same thing except that for the criteria that we want to use is less than number in cell c19 so for any number less than the number that's input in the cell c19 it will show the red color okay moving on to the next example conditional formatting based on text in column e and f we write two list of strings and in column e the condition is whenever the string contained the word python so as you can see all the items that contain the word python got highlighted in green and the second conditional formatting is whenever the text begins with python so you see that although this one excel automate python although this one contains the term python it's not selected because it doesn't begin with python and for the conditional format in column e all you need to do is you set the type to text and the criteria is containing and the value is the term that you want to check whereas for the column f all we need to do is change the criteria from containing to begin with so the next example we're going to look at is highlight the top end numbers from a list i'm going to make this window smaller a little bit so you guys can see so here this is our unordered list from zero to nine the conditional format the type we want to use is up and the value is 5 meaning that we're going to highlight the largest five numbers there's also criteria property and right now it's commented out but if we uncomment this and basically just say criteria is the percentage sign then it means this conditional formatting will look for the top five percent numbers instead of the largest five numbers so the next example is average essentially excel will calculate the average of this list now if i select this you see that the average is 4.5 so that's specified here the type is average the criteria is above so essentially we'll format any number that is larger than the average of 4.5 that means from 5 and above will be highlighted in green and the next example is check for duplicate you can use this to check for either duplicate or you can also check for unique values let me move this window over here so you can see in this list here there are only two duplicate entries so the first python and the second python so this is how we specify the type we want to find to the duplicates and if we change the duplicate to unique as the type you will see that these four values will be edited in green so moving on to the next example we can also do something called a three color scale the conditional formatting in excel and that is this rainbow looking i guess table here all you need to define is for the type you just want to define three color scale and you want to define three different colors so minimum color mid color and max color and you can kind of see that the minimum color is set to red the middle color somewhere between four to five is set to yellow and the next color nine is set to green there is also a two color scale instead of three color so if you want to use the two color scale all you need to do is change the type from three color to two color and at the same time comment down this middle color and that will give you a two color scale instead of three all right to the next example we'll have a data bar this is a data bar basically if i click on this then you will see these are actually values so from minus 10 minus 9 all the way to plus 9 and these are the actual values but i suppose to hide the numbers and only show the bars that's why you only see the bars not any numbers and if you want to create a data bar then you got to use the type as data bar and there are a lot of things that you can play around with for example you can play around with the bar color or the bar negative color so the positive numbers will be set to green and then the negative numbers will be red you can find the code from the link in the description below so feel free to download the code and play around with it so the last example we're going to talk about is conditional formatting based on formatters this kind of formatting will make our excel sheet very dynamic fiber formula-based diplomatic can be a little tricky to work with because in some situations we need to use absolute references while in other situations we have to use non-absolute references my go-to strategy is that try the formula that you want to apply in excel for example um you want to go to here and type the formula that you want to use you can try the formula twice one with the dollar sign and the other is without the dollar sign for the cell references if it works in your excel and you can take that same formula over to python and that will also work so for this particular example here we have two columns column r and column s we want to compare the two columns and we want to highlight the larger number between the two columns so for example here we're highlighting 5 4 3 because they are greater than 0 1 2 and on next two colors we're highlighting three four because they're greater than two and one and so on and so forth and how to achieve that in the type property we specify formula and in the criteria so here the range that we to format is from r2 to r11 and the first cell is r2 that's why we type r2 greater than s2 here if we were to format r3 to r11 then we have to change this criteria from 2 to r3 larger than s3 alright let's change that back because we do want to use r2 instead of r3 in our case and the second conditional format basically does the same thing it's applied to the column s so the first one the first conditional format is applied to column r only r2 to r11 and the condition is if the column are greater than column s then we highlight the cells and the second conditional format is applied to column s and the condition is if column s is greater than column r then we highlight the cell if you have any questions leave a comment down below alright so that's it for today and i hope you guys learned a lot about the conditional formatting with xox rider library i'll see you in the next one

Channel: Python In Office

Views: 1,769

Keywords: python xlsxwriter, python automate excel, python excel automation, python excel conditional formatting, xlsxwriter conditional formatting, excel conditional formatting, automate excel conditional formatting, python, xlsxwriter, conditional formatting, python tutorial, excel tutorial

Id: kpLyL-O8CFw

Length: 12min 43sec (763 seconds)

Published: Mon Oct 11 2021