hello everyone welcome to python life YouTube channel and exploratory data analysis exploratory data analysis complex analysis [Music] only exploratory data analysis volume computers foreign foreign foreign next font e Stylo cavali next fontaneity synthesizer fontaneity next [Music] to you put it alone is manamu foreign next page layout page layout page is foreign but formulas formula Center normally excellent formulas next data tab data Tableau basically sort and filtering comments foreign so View View and page layout similarities so foreign so next to developer tab developer low extended records blue um second row for example first row First Column A1 and first row second column B1 foreign uh next thing is this is a number to us this is a number to us so and foreign so this is all about cheat and is sheets foreign um separated files older version of excel uh newer versions 45 89 and 27 Excel it's a low uh and this is all about introduction of excel so uh basic uh things zoom in zoom out tonight and thank you everyone foreign table okay so here table um and foreign foreign foreign foreign so by that we can see highest marks so uh table news so this is about table and Department of references one plus two enter so already my name is foreign so e-cell address Plus e-cell address hit enter equals to e-cell address plus e-cell address hit enter so okay my name is foreign foreign Plus two plus two three plus two four plus two ravali so reference foreign so enter foreign this cell reference multiplied by this one enter equals to this one multiplied by this one enter so equals to this one multiplied by this one enter B3 into C1 B4 into C1 B Phi into C1 B6 into C1 or sunny so equals to B6 into C1 so value changes B C Inca um this one multiplied by this one enter equals to this one multiplied by this one enter and it's already blue it is foreign this one into sorry this one into this one so enter Because so correct answer of questions um drag and drop seriously okay next you drag and drop um so the two foreign zoom in um so B column uh first Rooney multiplication is so this is all about uh cell references I think let me know in the comment section and I'll I'll let you know and thank you for watching bye this one welcome to python life YouTube channel formatting and conditional formatting so first Tuesday Excel text Honey is formatting options next currency currency form at low next to increase accounting short date long date time percentage fraction text so you put the text is the formatting Next Step types of the first one is e greater than 7A 80 greater than 7 and is greater than seven evenly greater than 79 rows score foreign equals to text that contains Allah so greater than so greater than three highlight Covenant okay okay then greater than tune so basic Governor first name is greater than greater than 3 okay greater than three unit values based on conditions [Music] conditional formatting okay highlights greater than so we top 10 items only top uh four items your top three items items so top three items so top three items Next Step is formatting manage rule slow current selection this worksheet e worksheet selections um close on sorry manage truth ebook sheet this worksheet delete roll delete roll delete rule apply okay so uh formatting is foreign foreign income conditional formatting local scales okay foreign 4 6 and 6 and maybe medium middle values so in eight nine ten and maybe large numbers he said low E 2 3 and 1 and AV small numbers Etc so Allah rules [Music] additional formatting manage rules equals to equals to 5 so 5 equals to ten okay when we managers so format only cells or format all cells for example Sunday [Music] Thursday foreign duplicate values okay so you could choose the Sunday Monday and AP duplicate values duplicate values [Music] and thank you for watching welcome to python life YouTube channel my name conditional formatting formatting entity sorting filtering filtering energy foreign [Music] foreign you know foreign [Music] foreign um segment customer namely use cases [Music] foreign [Music] is based on city based on city Jason South Dakota state law um [Music] foreign foreign foreign [Music] foreign foreign filter custom sort so ad status short term cell values values so okay foreign smallest atom so filtering containers foreign eight zero so uh number one one double zero six zero one zero one zero five a below um [Music] okay filtering so foreign hello [Music] results so and he only um okay data is um foreign [Music] so these are the customers foreign select all and state negative filter Json select all okay the basic uh sorting and filtering uh so I think uh um thank you thank you for watching foreign first term cell function entertainment crushers important logical functions mathematical functions text functions data and time functions lookup functions summary functions errors functions so first term so for example sessions two plus two answers calculation is an operations foreign foreign uh 2 not equal 3 S 2 not equal three two I'll again greater than or equal less than or equal to so a operation performs H to 2 into 10. is logical logical uh logical functions if and or not foreign if if family is continent the first argument is logical test Indian day two equal to one is two equal to logical test next square brackets so logical tests is to equals to enter 0 in the questions equal to yes 2 equal to 2 equal to and a true I say yes [Music] logical logical test enter yes easy um if but if if I could use a summary for example for example because equal if okay sales sales logical testing the greater than 500 items High High sales low sale foreign [Music] [Music] I put them in the next week is foreign [Music] sales greater than 500 and the discount greater than zero on darling and logical logical and logical testing TNT is sales greater than 500 comma ecological Destiny it discounted greater than 1. greater than zero yeah foreign foreign the sale greater than 500 only discount greater than zero list is if a monological test if you say monological testing and greater than 500 comma discount greater than zero close value true items High low is it greater than 0 is our conditions um foreign thank you welcome to python life YouTube channel mathematical functions summary functions so first uh mathematical functions some count the average minimum maximum even a mathematical functions so if you simple sum in the current addition of numbers go some some use yes demo sum and number one number value CO2 range next up is equals to sum of okay range specifications total sales some of sales sales Sunday foreign [Music] point something is 90. minimum value name so basically some total sales um foreign next zero is parameters foreign summary functions summary functions for example situations foreign criteria one two three four next to close bracket nineteen five one study one two three uh some if you could do for example the optional parameters for example Channel greater than three universities so sum of five foreign foreign [Music] foreign [Music] Ranger range of chassis E3 next criteria which is office supplies furniture next average engineering and quantities enter so 2.5 alarm matter it'd be countif sum if average is sumifs average foreign foreign uh criteria range one okay criteria range one name is first of all so criteria range on any quantitation of over comma extra criteria 180 is the greater than um Dali okay greater than two hundali comma next to criteria range is foreign [Music] not available a to um equals to Edie Plus 2. this is foreign [Music] foreign [Music] next to number error for example equals to sqrt of minus 3. it is um deaf criteria range clothes foreign summary functions and errors India thank you for watching welcome to pythonlex YouTube channel function slow date functions and text functions so date functions um for example date and time is yes foreign foreign foreign foreign [Music] next text function text function for example Monaco 8601 2023 manamu premium e text functions [Music] um system ampersand I am percent intended two words concatenated and he uses some concatenate functions for example um equals to this one ampersand Eddie items um like enter so my name is is it double coat space double quotes ampers and sign steps enter so space double quotes spacer double quotes comma [Music] okay comma enter so hello people come home and then one day so welcome homeless texture comma number of characters one two three four five six seven seven characters kabali so um foreign [Music] next down finder finder finder starting position of one text okay o position o position opposition within this text starting number one listen and you know next step so oh fifth position YouTube [Music] Next Step the finder next trimmer treatment and The Unwanted spaces um texture unwanted species functions foreign foreign [Music] foreign for example City New York City sales filter applications soil Anton for example 2019 for example 2019 laws 67 000 so foreign so look up value lookup value in t Madam first question observation what is the sale in Assam for the year 2019 until first ending Assam sales 2019 sales is foreign foreign foreign let's look up first year 2019 lookup value foreign B [Music] 12 G6 so uh column entity column index and t one two three guys are not available foreign I know uh index matches so okay first match ES index number position is equals to um match lookup value in the assume so look up identity the single dimensional array so enter so 4. Asama 24 1 2 3 4. um foreign equals to match match of Entry lookup value until 2019 lookup array single Dimension array close column entity 2019 1 2 3. index positions foreign and 67 000. so indexes for example is equals to index of Monica index kavali e r um match lookup value in t sum lookup identity next to close next column number column number match a value e value comma took a parenting close close enter so we look up hlookup match and index index news lookup match index and thank you for watching foreign foreign foreign [Music] foreign together foreign foreign foreign [Music] foreign foreign [Music] foreign [Music] category region um foreign foreign uh for nature uh categories for example foreign foreign foreign foreign foreign [Music] column slope YouTube until [Music] value field settings foreign total okay so Grand totals 22 percent 28 50 in percent 34 percentage Next Step for example put it down together oh for example sales and I'm gonna ship data ship date yearly sales uh 2014 year sale so in the 15 sales 16 years foreign [Music] foreign oh okay so fast group and group and options foreign and next up for example some of sales City sales tonight so um foreign [Music] foreign soil uh data foreign so [Music] foreign [Music] oh I couldn't region next category so you could use them city city foreign quality next right click group and options group s it is so is the grouping um zero two thousand and thousand to two thousand next 2000 to 3000. foreign [Music] foreign equals to JC countifs criteria range entity is criteria range one um criteria on Indian tip it should be greater than or equals to zero comma less than or equals less than or equals um criteria 20. less than or equals to 1000. 1000. sales uh greater than or equals to 0 only um greater than or equals to zero and greater than zero nine thousand key um sales news starting zero and only started so okay zero to thousand thousand to two thousand two thousand to three thousands categories foreign left click system field settings show items with no data on them um pivot table options locally for empty cells show zero okay first time zero two thousand thousand to two thousand groups zero to um 0 and specified okay [Music] so this is all about pivot tablets [Music] and yeah thank you for watching welcome to python life YouTube channel my name is foreign first item s foreign [Music] [Music] area um honey foreign next data labels data labels are addresses [Music] foreign foreign title I know student marks and with the Nano title name [Music] foreign foreign foreign negative 100 maximum marks um foreign foreign foreign foreign foreign foreign next foreign [Music] [Music] [Music] [Music] pie chart you know sales I'm not good enough country wise sales is 24 countries um [Music] foreign [Music] foreign foreign sorry the pie chart next line chart lines are 10 times situations color charts insert line chart only so the line chart foreign foreign foreign three mapper painting for example manaku sales yeah so tree mapper so so contributions storage and organizations foreign [Music] foreign foreign foreign foreign [Music] histogram data select JC insert foreign foreign [Music] [Music] histograms next to Waterfall chart waterfalls [Music] is foreign blue only select this fill below next to the film blue so yeah um so faster insert hello so next time fill it purple color next to the Buddha fill the purple this is next final chart final chance for example um [Music] so next resume screening low 60 members telephonic interval of 40 members interview 20 members connected stages situations applied okay 100 resume screening 60 telephonic interview 40 interview 20 offer 10. so my name is foreign foreign [Music] foreign visual basic for applications until foreign [Music] G Q r soda is Ctrl C foreign foreign [Music] you know foreign foreign uh macros macro one Tuesday s absolute references [Music] foreign foreign X next y z n g equal to 1 1 sheet file um Next Step next a first of all use relative references home locally um so heading yellow color so in the stop recording stop searching is macros s run foreign [Music] [Music] a micro one or something micro to run Allah button options for um foreign okay so we put on Tuesday my name is [Music] Visual Basic um so first of all malamo you could use them visual basically developer tables the cold decoder so either macro and code so choose range A1 to C4 select J select Chase in the next font selection line extra font selection selection font a font select some size and the next color borders next line of foreign foreign foreign thank you for watching welcome to python life YouTube channel Heroes videos validation so first uh first of all data validation Channel range selectors [Music] data validation data violation [Music] it is used in a whole number item s between not between equals to not equal until either foreign minimum value either chase a maximum value 10. hello um one two five eight watches this value doesn't match the data validation restrictions defined for this cell okay this value doesn't match the data violation restrictions defined for this list is data validation data validation foreign [Music] um W go next r so you could use this value doesn't match the data validation restrictions defined for this cell the retry the edit mode data validation date between 1 1 20 20. 33 next one two twenty twenty three okay okay one one twenty twenty three next or to to 2023 this value doesn't match the data violation restrictions defined for this cell ular date next drop down list data validation data validation next foreign India USA UK next to Japan okay um foreign [Music] so next okay foreign put on any values selection is data validation Law data validation looking list so economic source evaluously next okay and yeah okay for example foreign foreign this value doesn't match the data violation restriction defined for this cell what if okay because maybe please enter the values between 1 to 100 foreign values between one two 10. okay one two ten thirty four please enter values between 1 to 10. next to uh next input messages enter values between one two ten so next step okay information enter values between 1 to 10 and I see so element of information so fast [Music] foreign foreign [Music] [Music] foreign [Music] foreign enter values 1 to 100 okay no one not one second one not one one two hundred and the support S no cancel only no um [Music] [Music] validation information from uh one to two okay 122. so enter value is one to ten okay can evaluate it is thank you for watching foreign my name is values juicy so foreign Mumbai next next Delhi Boston Florida Tokyo so pretty loud um [Music] [Music] and tomorrow so dependent drop down list dependent drop down list important so as well [Music] for example foreign foreign [Music] together [Music] thank you so you can name one another then name two okay close so you produce them and I kind of name range the same name to name changes name ranges new systems create from selection okay so you could use them uh name designation and salary name is data data validation data validation any value um foreign foreign [Music] foreign [Music] foreign Hyderabad Mumbai Delhi um Boston Florida Japanese dependent drop down list our country name is foreign and thank you thank you any data center footballing so um foreign uh corresponding country flag flag images so next to overall um potential potential rating out of 100 defines so potentially uh uh the player uh it is a potential to become an amateur potential of the player to become until in foreign foreign um Allah is next value foreign Mata next to International reputation reputation Indian um highest value five year next week um foreign um sub position s foreign foreign foreign striking uh position is striking positions [Music] finishing dribbling out of 100K rating Crossing finishing incredible aggression vision so next release so yeah in the data set so basically um foreign so um and foreign so my name is [Music] [Music] insert pivot table and existing location in JC na so um okay in English previous videos and so yeah nationality next time unique countries um count of players in each country players my name is so count of name is Afghanistan for Albania 40. so next term um foreign group by five okay um item is so ignore this one so next is foreign average okay so I produce the average home changes so uh age group average of overall rating 69.5 31 to 35 miles average age groups foreign foreign um you got a value field settings show us value calculation the percentage of grand total okay so to take a 23 percent left for uh write them or 76 percent along narrow sorry then Mata um um okay uh count of persons in foreign foreign position okay position rows next um counter countenance okay choose them uh count of values such as um foreign you know chart title chart title average rating per age group chocolate friends average rating per age group next um okay foreign um insert pie chart you could use them easy guys so I produced the 23 percent value 76 percent uh right uh food pre-purchase of all okay next step row labels um no problem foreign [Music] so let's say I don't think a pivot table rather than put up yes inside please select okay so anyway okay um dashboard cool ant um for example National devices foreign together that is the stats um ikkada insert a developer okay insert slicer yeah okay foreign Kelly so anyway so my name is foreign foreign foreign but okay um foreign but look um foreign filter Supply system okay customers um [Music] foreign [Music] mm-hmm okay hello players so kind of place um equals to countif y my name is Argentina foreign [Music] um foreign [Music] [Music] shifter next is sort I do continue the current selection oh my God okay now whatever I have no idea come okay okay okay okay okay okay so first of all my name is foreign yes foreign [Music] [Music] [Music] what's up next time separate count of players count of count almost foreign foreign nationality range Next Step English Afghanistan close enter my name is foreign foreign 25 26 2 30 1 to 35 36 to 40 1 to 45 okay average rating um average rating average so average foreign comma so she take the table one Edge comma um criteria in t because average range sorry average range average so overall either uh criteria agency criteria range age group age criteria range put a h value age stickers greater than R equals to 16. um greater than or equals to 16. and inco next NT criteria range to criteria range to good then indirectly table one of age next criteria two product criteria Two and A greater than or equals to 16 or less than or equals to 20. all right [Music] okay um [Music] foreign yes so 58.9 I'm gonna name this is Ctrl C you press enter so yeah uh average is greater than or equals to 21 or less than or equals to 25 enter next Ctrl V integrated then or equals to 26 and less than or equals to 30 30. Next Step accordingly greater than or equals to 31. greater than or equal to 31 and less than or equals to 35. 35 and um greater than or equals to 36 36 less than or equals to 40. yes same Ctrl V greater than or equals to 41. uh enter so um next um sorry um next okay 26 Elise so yeah rating on his average groups foreign okay okay so yeah preferred food enter left right um preferred foot range so the preferred foot in there foreign uh value n t e value preferred food value entity in a value enter value in touch next right value in touch so either next thing came in the preferred photo count in good position okay number number of players okay position number of pairs preposition is okay position position foreign foreign [Music] account one low position okay position table one look position enter we don't matter foreign faster foreign foreign Ty comma row number values foreign foreign sorry input range next is that one name Armenia right soila so first is nationality condition comma is dollar enter my name is [Music] let's see so come on foreign [Music] okay enter so you could error is um if actually is comma zero enter okay next foreign [Music] um next zero enter chapter than or equals to 40 next comma zero enter minutes if error if error tab next control switch extra comma zero under so just wanna Afghanistan um or Algeria Armenia okay um okay mattress Afghanistan foreign so IL and Mata [Music] 35 table Runnings down okay what's up next countifs count difference any function use you see criteria range one and the preferred food value next to criteria range 20. table one nationality [Music] nationality comma next is enter so same because again table one nationality value is not account if it's usually function countifs enter soila because value table one low nationality next um criteria entry okay so 19 key and dollar signs money so three dollar three yes enter so any transition one two three four total four members um average rating juicy um insert I'm gonna call them column set Next Step um easy select JC um pie chart okay first time okay next next um control me [Music] Next Step okay next insert an entry map Japan next next step Ctrl V um page layout that length of yes now next step foreign um insert and developer low insert the combo box ing suggestion so format control input range intent easy input Ranger foreign foreign over this e dashboard dashboard contact dashboard um foreign so first of all uh first first question what is Microsoft Excel in brief so in the first Microsoft Excel First videos so Microsoft Excel Energy okay tool storage so only exploratory data analysis sorry and also the the employee only data analysis until only exploratory data analysis on storage of data gapunda uh people paintings logos created and can games purpose AMS Excel used yesterday so different domain slow EMS Excel use Chester row next um it is developed by Microsoft team Excel next term can be formatted cell in Excel yes you can so first formatting and tendent uh either in a form uh formatting first of all uh foreign Justin hey Karma red flag meaning next what is conditional formatting foreign 56 3 1 2 um greater than 50 values greater than um 50 foreign [Music] foreign name roll number name row number like Inca attributes foreign foreign how to apply same format to all sheets okay um foreign foreign okay Next Step what is your relative cell reference okay my relative cell reference absolute cell reference next to next cell reference so relative cell reference for example one two three and four five six okay equals to four Plus actually enter so my name is foreign um this one plus absolute cell references foreign and um um okay for example he addressed one in English equals to initial address Plus enter 5. okay oh four dollar five dollar enter particular valuation um only roast me free and I mean absolute difference also okay next step Next Step Next Step okay how do you create name ranges okay name changes for example for name roll number box all right yes rule number one two something three 78 98 Okay so um data sorry create from selection create from selection top row Okay so um foreign I'm sorry next explain pivot tables pivot tables until we uh pivot tables are the tables that allows you to displace the exact data that you want to analyze display um again okay I'll create just some first of all and then insert tab insert inside table saw equal selections okay um next Next Step how do you create indicator next can we see the results displayed in pivot table okay um slow country and values foreign um yes we can see the result displayed in pivot tables next how are pivot tables used to filter the data so data filtering for example 2017 values filtering up 2017 only Canada so only only Australia okay the sum of 2017 in Australia next um Next Step water functions functions complex uh operators so water functions functions Define complex calculations that are difficult to do manually logical functions text functions and summary functions mathematical functions date and time functions uh so category so what is the order of Precedence in um Excel and what are the types order of presidency in excellent um equals to negative one two three equals to this one into this one Plus this one so one into one one into two two plus three five solar so um Excel next to what are the types of count functions and okay types of count functions for example one two three anymore and something like 10 divided by 0 enter so alone just under counter count functions to use yes then close and enter so count function name is foreign foreign range criteria greater than or equals to enter so any value under e range law or greater than or equals to 2 sales and units for example one foreign oh Okay so 50 out of 100 out of 100. percentage is equals to this one divided by this one and two is equal the same you were the same foreign concatenate function on the Monaco equals to concatenate this one um foreign kind of issue so close so yeah count of Kitty so 27. you could um provide that count of total orders from the list count functions so next question so is these are important questions so text functional left function and find function cell so right functions so our functions equals to so equals to left off text term so next term any number of characters function find function use JC find it extending um starting number is so he produces um space foreign a text actually one two three four five six seven seven letters find space space foreign foreign so write function a text next number of characters um foreign length of this texture minus um name just you know find extending space comma Within text start in number one so close close enter label second value left function low any number of characters any number of characters space foreign my length length of the text minus finder space system next to provide the total profit end in 2018 okay same a profit total profit earned in 2018 first of all update sales product subcategory product ID region customer ID segment City postal code region first of all your um wait a minute postal code in the so 2018 low um foreign country city state postal code region product ID categories sub category sales quantity discount s equals to foreign foreign and you operate the scientific date function here in t 2018 first month first day greater than or equals to first day first month uh 2018 year next um next to date range next less than or equals to Ichi and date of year 2018 month 12 day 31. so much uh next criteria I mean greater than or equal to date functions First Day 2018 greater than or equals to only uh less than or equals to um 2018. 2018 foreign foreign next to date okay highlight in column G list of the employees who have joined post one January 2009. so many higher date law oh 2009 January conditional formatting conditional formatting greater than an Academy student greater than [Music] um greater than 0 1 um January 0 1 2009 okay um so next is um equals to sumifs so data um criteria criteria together next time greater than or equals to and um entry date of 2009 comma one in the first criteria Penny next criteria range to good so criteria range to criteria 20 less than or equals to and criteria to date of 2009 month 12 data 31 sorry 31. less than or equals to okay [Music] first some range criteria range greater than or equals to end it is so the next step next Department ID Department is corresponding Department names you know if you look up look up value uh lookup value in D Department ID table array sheet 6 cylinder table array next comma um column index 2 comma zero enter so I'm gonna drag this type again so yeah important questions Next Step now student name is fetch the students marks in in beard in such a way that when a student name changes in A8 corresponding marks would be reflected in the B8 student name changes so next to equals to um equals to H lookup the table array uh next row index number is one two three four range is zero extend 67. equals to hlookup look up value in team again table array is this is the table next to row index number into science logo two one two exact match sorry exact match enter so 81 Krishna or signs 81 and English 67 you put a name yes you know foreign look up so next time create a drop down list first drop down lists dependent drop down list is Samantha foreign control this formulas create from selection top row okay data data validation locally any value the currently chasing Source law indirect of this cell okay only us alone foreign calculate the discount amount which is eight percent provided the person is working on the Caravan project or on the Paris project but the risk of the job must be low so you could use a discount amount eight percent discount amount first discount no eight eight percent and eight this one into eight divided by hundred eight percent so either eight percent touching on water uh eight percent calories but the risk of the job must be low job risk of the job much below until is it definitely foreign um [Music] or c e r r o b a l comma ecological testing random next to this one equals to p a r i n a s p a r i n s and c e double r o okay equals to low okay um and what happened so next yes into 8 divided by 100 or else zero so yeah um question so yeah um and you see Ctrl V so yeah hey pretty loud lookup functions look up functions index and match functions Canada next Canada next year 2000 2018 first of all match use Jason value index number Jesse I'm sorry look up value lookup array next match type zero okay wait match lookup value and look up array and this is okay one minute delete foreign we are looking for okay Canada is this mass type is 0 enter oh my God okay sorry equals so first questions single dimensional RNA YouTube multi-dimensional like a two-dimensional array here firstly next equals to mass of lookup value into 2018 Next Step look up array The Next Step 0 enter index next to comma row number ready column number is a enter 55 000. Canada extra okay 55 000. I mean limitations so thank you for watching