Microsoft Excel Data Analytics by Real World Project | Damtew Engida | Skillshare

Microsoft Excel Data Analytics by Real World Project

Damtew Engida, Data Manager

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
24 Lessons (2h 18m)
    • 1. Introduction

    • 2. How to benefit from the course

    • 3. Download Project Files

    • 4. Exploring Project Data

    • 5. Data Cleaning and Customizations

    • 6. Understanding Date Values

    • 7. Date and Time Categorization

    • 8. Combine Date and Time Fields

    • 9. Categorize Weather Conditions by Logical Operations

    • 10. Percentile Calculation

    • 11. Joining Data From Multiple Sources

    • 12. Joining Data by VLOOKUP and MATCH Function

    • 13. Analyzing Trends by COUNTIF Function

    • 14. Create Real time Data Feed by Free API

    • 15. Extract Realtime Data by FILTER XML Function

    • 16. Categorize Realtime Data by VLOOKUP & Conditional Formatting Part 1

    • 17. Categorize Realtime Data by VLOOKUP & Conditional Formatting Part 2

    • 18. Generate Dynamic Source Data by INDEX and MATCH Function

    • 19. Build Chart Visualization By TRIGNOMETRY Function

    • 20. Building a Combo Chart Visualization

    • 21. Formatting Combo Chart Visualization

    • 22. Formating and Customization Techniques Part 1

    • 23. Formating and Customization Techniques Part 2

    • 24. Wrappup


About This Class

Project based learning is the most effective type of learning 

Whether you're learning to advance your analytics skill, or just looking to build analytics visualization application, we believe working on projects is the most effective way to learn. Instead of being taught theory, you’ll learn by building working excel applications.

Most people feel overwhelmed with excel formulas and functions, but with this course, you’ll make rapid progress on your projects so you always stay engaged while mastering the most frequently used advanced functions in a simple and practical method.

This course will provide you the most advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Most excel formulas and functions are complicated Mathematics and algorithms. This tailored course will breakdown it in a short and simple way so that you can apply it in your everyday professional job.    

The real world project will be presented from start to finish, complex topics are taught in a simple way by an instructor with 16 years of experience in data management and analysis area. The contents have been carefully crafted and clearly organized. The videos are high quality with easy to grasp examples. Most importantly you will master the most used excel formulas by doing the provided project examples. Each formula or function video is accompanied by the project exercise files, so that you can do it by yourself with your own pace.

By the end of the course you'll Master the most used Excel formulas and Functions, Data analytics from basic to advanced, Insightful data visualization and creative dashboard reporting and start writing your own formulas and functions from scratch for any demand you may think of.


  • Easily build dynamic tools & Excel dashboards to filter, display and analyze your data 

  • Go rogue and design your own formula-based formatting rules 

  • Join data sets from multiple sources in seconds with lookup, index & match functions 

  • Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.) 

  • Manipulate dates, times, text, and arrays 

Automate tedious and time-consuming analytics tasks using cell formulas and functions in Excel.

 You will master a broad range of Excel formulas & functions, including:

  • Date & Time functions and Categorization

  • Advanced Text functions

  • Powerful Statistical Functions

  • Calculation and Mathematics Functions

  • Counting and Summing Functions

  • Lookup/Reference functions

  • Formula based formatting

  • Logical and Errors operators

  • Conditional Formatting


1. Introduction: I'm dont turned on in general by profession, they become a data analyst and information designer. I have more than 16 years off experience in Dutton. This is in many international organizations, including the United Nations. This course is not a Lingzi theoretical beginner's course. This course is a real world project which will help you bring all your excel skills to build area. Waller Dynamic Excel in the text. We will be building a project starting from scratch and build final application of the process. You learn the most essential Excel function for unless advances for marketing techniques advanced it on, insist technique and many others with their union rolled with any off the pig general, advance it as a X. Of course, its course should be your final stop to challenge all your neurology in real life Project will start to its downloading ZA project files and explore the different components and variables off the project data. We'll be working on Georgia traffic accident location, open data and Atlanta weather. Open data will download those files, too. Our ah vocal disk. We'll go xango hit by cleaning and categorizing that data by the left, find and replace function and will use a different days in time function to get him arise. That dates into it is granular components. I wouldn't go to categorize the weather D top by political oppression and other takes formulas. We'll try to find a percentile of the precipitation data by the person tied rank function to find the type off the weather condition. Then we'll try toe assigns the way they're type I huge malfunction in the Villa car function using person time result and the way they're labeled by Con Katyn. It's vacations. Who's angle to join the traffic accident, location, data and the were data by Vale a cup and modification. And then we'll go toe from part creating and visualize seeing the data by creative dashboard. Well, first, summarize and populate the detail. Why is a powerful county fiction and try Find a system Orange boy custom formula in the risk level biological. If statements, then we'll go to bring real time with her data from free with her a p I provider and extract individual components by the foul fold Filter XML functions. Then I will try to find the wizard category with your type in with a little off the real time detail by planing with historical With Our data will use a large cooperators. It's a Villa Cup and my function toe. Blend it together and we'll try to find boys. Indexes a mark function and we find also the X and Y coordinates by the Tirico Metric vanquishing. We'll use the co sign The Sign in terms Ardian Exhale Function To get the dynamic inputs who don't write, try toe insert combo trust and we'll do a Siri's off four month in tow. Bring it life toe the chart visualization. Finally, we'll use advance it for marketing and labeling methods to bring a priest Tobu the Boring Road Geeta to a beautiful they know, meet real time Weather Analytics and by the end off, see scores you will muster. Some most used except for Morris and Frankish is it will be fun and you'll get some Most advance it. Ah, for Martine and on a ticks. Techniques to summarize by saying, Does he scores. You'll mustards the most used formulas and functions, and that's analytics from basic to what transit level and also insightful, that of the visual ization Creative dash Port reporting. You'll have the skill and carry to work with any formers and fink. Shin takes what? And wars, of course, also but its injuries, of course, and see you in the classroom. 2. How to benefit from the course: how to get some most benefit from this course. First, let's consider the structure off the course. It is this sense or lectures you take in one level will become the foundation off the lessons on the next levels. Careful, it is very important that he takes it course in order, step by step. No jumping around the lectures If it takes the course by jumping around the lessons or the lectures, most likely you'll get confused. It would be better if you wish once the course from Start fish and do the project again. It was a provided exercised foot. It's lecture hard across pony exercise file, and some external sources like wife, site thinks and FYI, practice properties makes perfect and too important a reminder. Assane. You get a lengthy former straws, of course, but don't worry about the wings off the form loss Onley concentrate on the concept is society can tackle similar problems 3. Download Project Files: first list. Download our project files. We need to different but interrelated data Traffic accident location, data and historical weather data. I intend to dollars the first data study the traffic accident. Location data from Chirchir State Open data. So let's go here to Google. Search Bar and such. For Georgia State's open data search. Torture the state's open data and hit. Enter. Ah, this is data. No state. So let's click the first result. They swam and we'll open up through the state data. We have many trees here to access that data Murder search for and the search bar. Let's try to search traffic accident locations and hit. Enter. We have to results here. Now open up the first result. The traffic accident locations. Open up it and ah, scroll down to see that data sit. No, we have the accident. I d here and didn't time. Okkert, As you can see, that data is from 2010 to 2000 18. It is eight years of data. We have the county here. We, uh uh there to location Ex me data and the way they're type and money. Other useful information. We can't download this data to excel his Pritchard. I can only hit here, preach it and click the link safe to eat here. Let's, uh, open new folder and name. It's real time data on our six and say that data here. Serve it. OK, now let's look for the ways of data. We wanted the ways of data to be the same area to the traffic accident location, data, and we need to historical with that. Get up with this region can pull the Wizard data from Atlanta with their forecast off his portal. So let's go ahead and such that site toe, Lexie Swan and, uh, we will open up his are historical data from the in Atlanta office. Porter Girl So quick. This is the National Weather Service click cease link Climate and past weighs over and we'll get will open up the online data. Weise so selects cease link. National went with the data. Ah, was select the Atlanta Select Atlanta area, and what we'll do is the 1st 1 pull up the men's Lee weather data we have there this choice a select ah year 1st 2010 And, uh, let's try for January and go can't cope Easy's Her data and pistols. Pridget firstly select its rightful can cope e and let's open up the traffic accident location Shit. And we'll post pissed in new shits here. Pace scenes, Right Click in pest and, uh ah, bullets for the February data. Also the primary select February here the moons and done. And then go Copia as usual and we put pistes IHS Teoh. That's appreciate below the general data. So we plucked from 2000 in 2018. It's a bit tedious, but you can use this exercise now if I it's ah into as a hitter. The death in the other variables here. DATs and the other. No, I can't. I feel it. Let's rename the these two Georgia weather data. Georgia. Weezer is enough. There's four months a little bit and, uh, well, so mean. And now we have to ah, projects data here. The traffic accident locations data into weather data. We will explore each date up in the next video 4. Exploring Project Data: now open up lecture to exercise five that we deluded in the last video. Okay, we have this raw data. Let's quickly for months. The hitter a bit first selects a future and will makes a form larger and pulled, make it larger and make it also bowled. And this field the programmed color. It's the selective dark orange color, and we can also expand any hidden text to do thoughts. DoubleClick owns a column online. We can expand any other come that we need horizontally, and he also and we can zoom in a bit for bait or view. There's so many and the other can see in the 1st 2 column column and B, we have X ray data. This is a two old location. Off the accidents occurred. We can rename thes us longitude and latitude. They ceased one longitude and job this holding longitude and this one latitude this expanded a bit and we have been column D accident I 80 his unique identified for each accident. Okay, if I select begins, he's an ember of accident occurred more than 17 towers. An accident occurred within the period off eight years, and we have here and call me the dating Time volume. He's a most important feet for our project. They tell us. Win each accident. Is Walker down to the minute. As he can see, the four month thing is a bit strange but will suckle to clean up and for month is annexed video by day's in time friendships. We have the hair to the day off a week end here also that count category. We have three county data for torture. We can feel that to be sure selected and ah Oh, Goto Hey, Dayton and future here. Three county. And so we have the location data, which is shows a street name. That's the swan ease. Ah, that old location data again. I think this is redundant. Let's leave it for now. And, uh, came along here we have the lighting contrition. And here the Weezer condition and, uh, traffic floor here. Traffic flow wins. Accident is occurred. Uh, and many other category and variables. So what? The state I standing us exactly where and when? A particular traffic accident is awkward and we can jump to the Weezer days. Huh? Lists, uh, bold. He's one on and it's make domain. He this one is, Ah, temperatures and minimum. Its maximum temperatures tear the precipitation this and snow amount. Let's see you mean aware in Georgia area that day a little less snow. So our main focus will be the precipitation data will be working to get analytics insights from the waiter and traffic accident data for our project will clean up. This data is the next video by tech ist date and time functions. 5. Data Cleaning and Customizations: his last video. We're exploring the components off road data. This we do will clean up, poured it a bit and remove honest three components from the data. First, open up the electricity exercise fine. And our main focus is called E That dating time That's accident occurred. As you can see, the state has a bit of strength for marketing is actually a UTC times on dates for month thing for okays who don't need it. So let's extracts the deeds off to some minutes First, let's remove the tea is a data volume. Seems we don't want it to be here. How can we remove it? I know you guess it will use a find and replace Made it first Highlight all the detail in Call me. We can press shift plus control and down Roky for fast selection and, uh, go to find into place Return the access par in the find Port capital T and Earth Place with a blank space process piece bar once and, uh, hate all replaced old bitten and close it. Now the tea has a gun. Take strength. Only the death volume. From this former thing, we will use a lift text function. First, let's insert new column before the time viewed. And let's call it, ah, date and time dates and time and will enter the left function here. We don't want this character in Cretu, including the colon. So one only is a 16 character from the left, so is equal to left. So next selects life function. And, uh, that takes will be e to coma. And the number of characters we went is extinct from the left 16 and close the press. Then he danger. Now we have excluded the unnecessary character buys lifts function. Double click to cope is don't. And now we have cleanup or data on that data sets is the next section will be working on date in time. Categorization made it. 6. Understanding Date Values: before working on with dating time functions. Let's understand how excited things are worse days in time and how its process. It's the key to understanding how tour, with the dates in time in Exhale lies on the date. Follow defies our serial numbers that excel US Science two dates that are entered our store US text Kangol 82nd. For more information, go ahead in tow. Go search bar and type. Did followers in Exhale the first street size size deathwatch Who's fun? Sheen in Exhale Convergence a debt in the text for months to Israel. Number that Excel. Recognize us A debt whenever we enter. Irritating to Hiss Hill, I feel will assign it its value to that sale. This will help us to make any time and death based or oppressions and Microsoft Excel. Please follow you want. He's assigned to January 1 in 90 00 This means the beginning off time is started as the state as far US Exhale is concerned. And from there off, Wallace reflects the number off incremental days in terms off whole numbers or fraction off day. Since he's starting debt, that is January 19200 for example, General Safety 90 00 has dates value off 30. If you want to find individual times with any day, we will see date follows within decimal points. We can see the deep value off the deeds are saying, too any sale from the former sales and clicking the general option. You can try this by that interning current time in any given sale. So let's sports that current time in new seal. Now we can at go to new shit and entered the malfunctions there, as he called to now. Now selects a nonfiction and, uh, now close breasts without internee criteria and go to four months and choose a general option, right click and, uh, for months sales and go to a child or Shane. So this is the death fall for six and current. Yet if you put the first dates, we can get fraction off that dates. Values also that these values give us the tools that we need to use date in time function to make any categorization conclusion in any other oppressions. The next video will be working on categorization off the state value to individual components 7. Date and Time Categorization: his last video who have seen how exhale process, days and times and this futile will be busy on working some busy categorization. Frankish in to Better Guys and filter or data day isn't time categorization functions simply to extract individual components from a given debt. First, open up the lecture. Five. Exercise Violent will pull the your the men's day minutes and seconds from this Given dead by the dating time Categorization vacations One importante here is we can Onley aggregate data at higher levels. We cannot get more Grandeur is on. There are data itself. They see an example Let supports that current time in ah, new sale Can you sort Ah, a new column here. Ah, and we can use and now function as we did previously to get the current time off to second recall to now and we get thes now time we can for months it a bit select this one and we can cope disease dead to a new slight. From this current time we come plopped the year the men's on the day, Man it's and second level. If the road Dita goes to the second context, not second school. So so now I can start categorization off or accident location data to do that. The list. Early forties current debt and double insert new columns. Ah, first, let's use this one a year in certain others column. This will be the men's, and this won't be a day, and this will be a minute, and this is the data is up to the minus. Let's for money's a bit now. We can start by extracting the year. You can use your functions as equal to year close breast and select after this dates and close its condole. Click to copious Don't and, uh, we can use so the man's function to extract immense equal to humans, men's and selective to the debt off the given day and became four months. It's ah, and double click to cope e don't and go here to extract the details by day function. Lex a day and select F tomb and for month as profess ah and can double click it to dunk can finally can find a minute by the manage function. Select minutes, and if to Andi came form what it's so we have categorized or data dating time variables. The same honor list categorized for the weather data. Also, it is becoming super easy to do that, So expect your Joey's or detox tough. Let's first inter new columns to apply. The detained time functions can insert for columns and start by entering the year. The men's duri and we don't have a minister from this data soul. It's dilated. I start from extracting Is a year from this given dates as we did previously? So lakes a to and this format it go to for months, cells in general, a click OK, double clicked to cope it down. Let's go here to extract the minutes. Also, um, men's select A to closed basis and list for months us a year. Double click and finally, let's extracts it day by the day function and it just for market. And so we we don't have a second. This is throw data, so we can categorize, too. Is that level, so Trick up will use the date and time categorization entrance to organise and filter our road eater by year. Men's Day. How and minutes so in the next video will be working, combining these ah date aid time Fearless 8. Combine Date and Time Fields: 9. Categorize Weather Conditions by Logical Operations: this lecture will concentrate manipulating thesis Weser data and the will creates new fields or dimensions. Took categorize or detail for deeper analytics off the road data. But before that, let's open up the lecture aid exercise file and clean up the Dietze a bit. There are some takes for rebels on the data. There is tea. Here it is M here. Well, so and, uh here militaries em There's t t we need to remove these forever's. It seems it will not help us for further Muncey's off the data. So to clean up the data first, let's Victor this hitter. To do that, you can go for select it and, uh, go ahead into data and a future. Now is that preciptation coral we see then necessary? The tech ist this Go down and there is m and T here. So we should dream of this one to do that. First, let's on Trickle select all and select Onley M and t click Ok, so we should remove this data. We don't need it to do that Lay select old jeez data right click and the least all the row . And there is also one take assed said we should remove. Go down. Here's Alexis one. And this disease this removes a T. We don't need it now. Latest Chuck all or select old. So are we. Clean up the precipitation data. Let's move on to the snow one. They see their necessary text here or so And don't we see m and t? So let's first no uncheck old or select own en and t So we don't want this data. The Duchess it so right click and police all. Is that all? Not a lick All. Now we have cleanup detail and will create a new Colin here. India. Categorize this wizard Peter into three different buckets off conscience. Dry rain and snow were these large call or predators and conditional statements to create the three wither category. So the condition will be if series no precipitation and no snuffle that they would be dry. This is one conscience and, uh, the other will be if precipitation is graters on zero. But there's no phone. This is snowfall. Is it cost 20 That will be rainy day and last ocean would be if there is precipitation. And if it is snuffle that they will be snide. A. So let's translate sees logic into conditional statements. This causes first weather category. Say call it ah W category. And, uh, we'll insure the logical conditions or statements here is equal to if open brace and to up breasts. Also, no deluge car, which wanna be if and two Or the expectation is equal to zero, and the snow or into is equal to zero. So the volume through will be that they will be dry day, so open double courts and dry closed devil courts. The other conscience be eighth open breasts and and the country would be he's and to graters on zero in this case and to or the precipitation these graters on zero. And it's a small graters. Arceneaux into small Yes, no or M two is equal to zero Gerald closed bris and coma. So it's a valuable values. Truly be that the worldview Randy Rainy day, close double quotes and, uh, the other last option. It will be if if this snow and M to ease greater San zero, there is snow full so great as an zeros. Ah, yeah, that they will be a snow day snow and value it false If all the ocean are false, this will be as its can call these others. So he has. We open up three press close. It hopes there is a problem. So we missed all these. Sterile him. A two should be zero snow should be so, uh, inter. Double click the co pizza dough. So we have get three weather categories. This sea is dry rain and snow category. It's for any with their conscience or any parts crown day. So to recap, we have used large car operators and conditional statements. Two kita grise. Our ways are detail, Pistone, as if you notice in this case, the precipitation and the snow fields the next video well creds categories based on these numerical Vialli's 10. Percentile Calculation: in the last few dio we have categorized are ways of detail, basic own values off as a fetus, normally the precipitation and just know Fielder's and this lecture. We'll go deep to create categories based on and new media called values. We will try to calculate percent ties off how precipitation and the snowfields percentile calculation. A little sauce to create political groupings based on destruction. Off our data points is oldies Georgia weather data. So let's open up the lecture. Nine. Exercise five first and us you remember in the last lecture, we have categorized the Wizard data into three buckets or categories. The dry rain and snow Kate and Gorey's butts. What if we want to know? Amount off these categories like if you want to call like favoring light rain, heavy snow, light snow and saw to categorize or vicar in this week Percentile consideration. Be great help. So to do that Ah, let's copy that death. Our food is into new toughs Scorpy for a suppressive petition that d saw on to new shades and well paced it. US values on Lee pissed us the virus on Lee. So we give this data as you can see there are many duplicates. We won't want to remove these applicators, since we don't want them for a percentile calculation. So to do that, go to data and click. Remove depletes Burton. So we get these Gallic books just simply click. OK, we have money Dip Lick its and 12 unique guys we want keep sees and click. OK, let's sorted in ascending order. So click A to Z and, uh, Cain called Aziz hitter for some time. God is he's percentile and will cause the next type. He's want These type came foremost it up. It's for just for good looking, wanting the background. We can make this blue. No, we can calculate the percent eyes. The result will use a percent I wrong functions. Let's until it's a former here. Start by Writing is equal to percent Tyronne Select Cease one. The Last Storm and Ari will be a two two down and we came Select control passes plus down or for far selection and want to make it absolute reference. So pressing for to do that in a coma six value shows Ah, that we want to call it quits. The percentile in this case this A To select a to and close breasts now you can double click to compete Down meant let's make its percentage we can formatted from the hometown and, uh, percentage Onda and, uh, this make it 12 small place. This is enough. Now we have a percentage in from little percent, 200%. And because he can see now, let's calculates the type it was used. A simple lunch car former here a is equal to the logical test will be be to, Let's son 0.5. So the value who, if true, he's this close hopes. Let's called these ah light light and come on out here and otherwise this false heavy double court insights of Devil Court states. Right, heavy and hating toe double click it to compete Don, and that gives this razor's, as you can see. Yeah, greater son, 50%. It's heavy lessons, as 50% is like, so that's great. Let's rename Z's top two that person tie precipitation percentile and you can change the color off the towers. So right click on right quickly and select as this blue good. So in the same honor, these calculates ah, percentile for the snow data missed. First copy. It's and it's great. New shits. Paste it. US violence on Lee. And this removed the duplicates Go to date are the Kareem Deplete button and these. Okay, we have some amount of defecates 60 unique watts on Lee. So these three, uh, date sorted ascending order it's called is percent time hands and x one beat him type as we did previously. Type this for money to base changes. Coloring nous makes bond just for good. Looking this in terms of the former here as equal to person. Trunk person. Wrong the select from that, your prime manner. Sorry. Will be from 82 s 16. Make it off. Sorry it reference by president for Come on and x will be NATO and we can close press now Adult click e to cope it down and skin sort nights. Yeah, this make ah wonder small place. Now we above 50 person and below 50% Can't gets the type as he called him. Type is equal to confections Here be to liaison 0.5 That ocean be perhaps can call it this light as you did previously and coma here. Otherwise the value for force will be heavy. Don't think it's Corp it down and UH, below 50% e se v under Assad's and be like That's three names is a snow percent. I the scratch this room change Curtis to green, so ah, to wreak up, we have created percentile. Rank us in percentile scales four pulls air informing snuffle and to have used percentile scared to allergic Ali values based on the distribution off our data. In the next video will be PC joining data from multiple sources. 11. Joining Data From Multiple Sources: the last lecture we have successfully created percentile rank sent scales and logically group values is done. The destruction off our data, the ways of data. And I won't go head to joining data. Two newly created percentile data in this picture. Namely the precipitation and snow percent. I will use ah value cough function with necessary conditional statements to pull data from multiple tops. Namely from this precipitation. Ah, and snow percentile tops. Yes, your ender. We have created these wither type for because a person Tyrion personal of feel this Now this integrate Is this a weather type ways? Our original waiter conditions on the Georgia always there. Ah, data. Tough talk can use, uh, and you call here and, uh, you can call it with their type or simply w type. Missed call is w type. The idea here is just to fear out the type off the snow or rain in the data base. Jones, A look up table we created previously. So whether it's ah, my train, Reverend Heavy rain. Well, the integrates that so let's just use if and Valeo cough functions together is equal to if and the logic artists who will be and to or the condition off whether category is equal to rain until we want to know there. Yeah, how heavy or light It tastes and you use the local function here. So the value through would be fi look up. So next a bill of cup. So it's a look up. Value will be the precipitation value, the things case they select l two Come on the table, Ari will be, uh it was precipitation percentile Thought so Selects from a to to see and let's make it. Now I've sold it. So press a four to do that sounds that column That index will be three. So ah, industry here and, uh, well will select exact much force. Or we can make it zero now close of Chris and, uh, coma. Now we'll enter the other option or so go back to ah, the ways of data And we will inter another If, uh, no. Uh, in two, I was always category is equal to snow. If it's a snow coma, we will look up. The small field is here from personal percentile. So we'll use a very local function again. Valeo cup, So Ah, look half value will be m toe and, uh, yeah, Coleman. Look up the table already would be that snow percentile table. So, like sat and make it of soldiers value by press scene four And, uh, a column number will history and we will choose exact march and close press. Now, if those ocean is false or the value if fault it will be as a country will be dry. So devil coat dry. And so against this treatment, it is very Lingzi. But don't worry. Almost links offs formula. Just concentrate on the concept is so a double quick took opiate down. So we have created a new weather conscience by it's a veil a cup Ah, that the functions as you can see weather type, help us the weather, compassion in detail whether they're in his light or heavy. And also this no amount the last. So we can create the wizard label in any calm here based on the conscience and type off the weather. So let's call this hitter with level or simply w level, and we'll use if state amount and concatenation off sees too. Column. So against interest is former two p two equal to ah if he called to if open breasts or select if a logical T stool p if ah end to and to cause means the ways that category ah is equal to rain but dry. So we've select Anto since we don't want to be dry and dry level only try is F otherwise is a follies force. If this conscience is not made, we will use the contact concomitant function, auto front and and double cuts, cuts and up rent and select anto the weather category and now death. Just close breasts light train. But for four month Inc that press is a space US good double click to cope AIDS a down. So to rake up, we have used the veil a cough, please conditional function to join the data from multiple source. 12. Joining Data by VLOOKUP and MATCH Function: As you remember, in the last lecture, we have used the Valeo cop function to drain data from multiple data source. And this lecture will move on to see how the veil local function can integrate with a nested march function toe automatically pull in New Desert says so our goal is to drain here the traffic accident location data with Georgia with their data together. First, let's try to drain the two different de time by it's a veil Cup function on Lee. So our common field will be so join I d that we previously created for this purpose and in these traffic accident location data here also, we have to join I d and I won't try to combine. Ah, it is three Ah filled is here from end to P from our either teeter talk into the traffic accident vocation data. So let's try to cope either Sweet corn header from and on to p one Onda go ahead and co P compressed it Here Best it in traffic accidents location data. Now let's start. Ah joining the weather treated with accident data boys are veil cup function So listen tours Local function here is equal to feel a cup. Select Valeo Cup. So come value P. It's a common field or the join I d. So go ahead and select that 80. Here, get it. This tea? Yeah, to. But we have to make it said De absolute reference since ah, Lok up value Always leave in column d so to fix. It's a cool, um, Dicks on Lee. Press it for three times, Auntie, does that door sign appear so? And, uh, coma Now table array will be the weather data go. Hayden's always a data tough, And, uh, we'll select all the fields from A to, oh p and my kids off Solis Friend reference since we are always looking, say, surface currents So we can press a four to fix us and coma. And the column index number will be from A to P, that is 14. This can count it and coma. The March type will be exact and also we can enter zero or purpose and began hitting Turner and gets this so results. This is working. It's a double greased carpet. Come. Okay, We have got some era. Was this era come Flat seas message value Note a favor are your well was This means the local function could Didn't find the cross pointing. Join i d and the weather data so you can ignore it. Thes message for now. Ah, and, uh ah can drug it too. Include our to viral social. But we must change a cone. Number two this 15 and extend for the will A little. And we tend. Ah. So, like suppose off the weight alone level and other type together and to double creek. It's down tickle pizza. And the other sales? Yes, So he can see it's working, But, um, we don't want these, uh, any ah, more rebels from data. So we want to remove it. We'll select. Yeah, I think this hitter select Go to data and select Wanna fits is any And, uh well, let it since you would go One is to appear in our data elect on press control 15 Donora, delete the room. And how select own so we killing up our data. So now we have done that Joining by using Onley is a vehicle function so we could do sees in march Clever way by integrating, uh might shoot my function So here and set him off. Colin number 40. Ah, Mr Off. Try writing hard coding. This column number You can mites a cone in there on the weather data. So, listen, that's a much function here. So, like so much. And so look up. But it would be this always er category or the blue category can make fix it. So one that's this time press If for until the dural sane before one appear And, uh, the local pop are a Now will be a column number. He's a weather data. So go hit the weather data and we can select all the header from a two p and look the hole hitter by president for and then, uh, cool, huh? The much type would be exact much so we can write zero for that. Ah, OK. There's some poor we have to fix this on. Okay, uh, maybe can rewrite it. This relates his on. Do it. Ah, Okay. Still not working. Okay. Coma and a much type later. Lefties exact much. Now close press. I think it's working now. That's good. Now this drug, it's first. Then we can directly hold two down to go pick down. Now. I think this working the much function is integrated with the Villa Convention to float that data. So to wreak up, we have used the way Look up with iMessage March function to automatically trying data based own the column hitters in, uh, Georgia with their data. 13. Analyzing Trends by COUNTIF Function: the last lecture. We were pz working on the Valeo Cup and much function to join data, and we were successfully trained. Oh, our traffic accident to the kitchen detail and the Georgia wither data together. And this picture will be working on summarizing and comparing our data for this proposed count. If and Summey function will be a great help, will use the count if function for this lecture first, let's try to analyze how accident traits change under the different with conditions. So to do that can create new shit. And let's call it chart visualization church, usually ice station. And, uh, you can't change color maybe yellow, bright yellow. And we can man, there are get to here and now we can't entered. Uh, yeah, different weather conditions. I'll start by dry and ah, light train followed by heavy rain here as an ex would be light snow and heavy snow. Lastly, heavy snow so we'll camp. You're how accidents. Yeah, Our Okkert changes in how the accident rate under each off these type off with their conditions. How's the weather conditions is affected. The accidents will analyze us so they starts. Ah, searching to waste when I say data first accident account or number of accidents under each off. So either conscience, so we can call is the swan number. Off accidents has me quite number off accidents here. And beautiful accidents ready to bit. And, uh, the next one will be sand number off days in the sample data, the weather data. So this will give us an involved days that can be categorized by each officer conditions. All these are conditions. Some try to heavy snow in the search. Valuable. That went, We weren't analyzes. Accident rate accident trades. That's accident or Kurt that each day so we can use a county function to Ally. Analyze east that dates, huh? We'll start by, uh, number of accidents here. Yeah, is equal to count if select county sanctions so arrange would be that data was the accident location. Now, here we are interested and the weather level data. So select shots. Yeah, Column and, uh, can make it obsolete by pressing f four there in the criteria. Ah, sets these controversies column Needs to meet to Ah, Count e said a role phone in charge. Visualization table. That's just be one. Ah, they start by Yeah, dry conditions. So the inter p warm or select be one and close breasts. So you get cease number accident and try conditions. Know how God thesis and can drug too include a Z other away. Their conscience also can try it Some member of accident ist or Kurt and the different weather conditions. And send next to be, uh, member off days, as you can see first and, uh, Atlanta, Georgia, who is a conscience. Almost dry and mostly accidents according dry this season. So let's start in similar fashion two counts the number of days under different conditions with conditions. So did is that we will use county function again. They called to account if the range will be the weather. Dita No way that the data hit selector with the level A and p The column p select column p and locate By pressing four and the criteria that needs to mate, he sees a church visualization top, uh, the one and you can close the bracelet and hitting turn because he's amount off number of days in dry this season so we can drug to include the other way their consciences. Tonight it So we get this number of days before different weather conditions are under consideration. The next will be, uh, accident. All right, this help pasta calculate accident is per day. You can simply ah, divide number of accidents, spies a number of days and can travel to in the rights to include the answer in this for market two decimal place is enough. So please help us to compare how sexy gentry it's changed under each off the weather conditions. Notice how accidents per day increase from dry, too heavy rain and by drop down under the small weather conscience. Why's that? It is not expected. Ah, it is expected to increase under the small condition. It's probably because our snow sample data is very no, this is Atlanta area. There is no watch small that maybe it's a reason. So it's not big enough to fill the reinstitution, so we better ignore the small ways are condition for these notices. Let's ignore it, and we have shed it for that. So to recap who have used it's a powerful county function to summarize and compare, our diese bass joins this peacefully criteria for our kids. The weather conditions table in the next picture will create airing for Zach decedent rates 14. Create Real time Data Feed by Free API: is the last section we covered. The court dad on analytics examples, including categorizing data. Biological operators find Marty medical oppression like calculating percent eyes and, uh, joining detail by the veil, a cup and much reference functions from people data sores. We also analysed trains off data by the powerful count if functions and destruction. We're diving to discover how excels we've service functions like a P I works. We'll see how to integrate some to I am neither source data how to pull real time data and will generate dynamic charts, sources and Butte combo chart to annoy eyes and visualize our great time on a date first, let's start by generating dynamic charts. Source. Detail by excels Wave surface applications confined here in Moore Function and Wave services. This service were interest in 2000 15. Version off Ex CEO. It is a powerful function, said I lost to pull brief time Nexium. You data directly into a warship. Throw on a P I. They start by creating free P I key from weather service campaign. It's all great and such. Google for free weather ap. I kiss free whether AP I keep can select sees different and tried to find a company with provide free with their a p I key. It was a notion that DOMA straight buys it. Epic Sue Weather Sorry sees Campana. I found its simple and intuitive, so I'll go ahead and sign up for a free e p I in Ah, it's son require you to verify your email after sign up and of two sides after you verify as email. That's a company sent it to you and ah, it gets an a p a ki and its documentation. Here is where the a p a ki is. Ah kee Number 30 is like long crumble. He's plan type and down is current away their feet. He don't so that forecasts with their feet. So let's copy first AP a key Kopassus key and we'll piss to are working in the orchard. Xia. Go ahead and copy. Paste it, maybe paste it Here this hill pissed Let's qualities a p I key a peek i ke It is our key and let's copy also now whether the current weather feet go down and let's use the 2nd 1 with http ISS co Pete and we'll pissed to an exhale just billows that a p I key here pissed . It's and its qualities whether service always on service and the less puts a current with their feed by the weather, Sir functions. So I'll go ahead and puts and if formula equal to with service, it's Alexis and less make they seen under devil courts. Hey, don't so no can close. The parents is so there is a current with their feet for the default place. It is the city's poorest will change us in next video, so ah, that's great. Ah, In the next video, we'll feature individual weather components and integrate the A P I wee's our ah data. 15. Extract Realtime Data by FILTER XML Function: Now we have the A P I key and these current weather feet off XML data Nando's and extracts individual components by the filter X female functions. We think it's a filter. Examine functions here to the main main main, um, under formula and exploiter except ill. So also it in change just on extension to X m l extension. Thanks, Bill, and we can change. Also, the current city was a default city. His porous. Who can change it by Atlanta? Seems our data is coming from Atlanta City area. So a King Jing is to Atlanta that much to historical ah, data. So let's start extracting the individual components from thes way their feet off. Long examine string first to begin. Delete. Sir, it was, Ah, a P I key disease. A p I key, I think. Yeah, I can relate it because we have already here the keys. Really, It's copying Corp it so it's not important to put here, so let's delete it. So now we were right. Individual components we want to extract. So first, let me level the cease to current conditions. This college is to be current conditions and in front offsides where we can also level results off the individual components so you can write the individual component is we want . But first this sea, what's components out of available can go ahead and look the documentation picks to a site can go to current. You have these karent or real time with R P I Yeah, strings and Fearless. We have his last updated in its release. Drink. He has a temperature in degrees searches and also down there is wind direction. Here's in humidity, precipitation. We have this old, the components or conditions. So we came in thirties. Yeah, conditions here. It kind of starts. Boy writing temperature temperature Here it will be in degree centigrade. Tim picture temperatures. The Knicks will be preciptation precipitation and the Knicks will be wintered. Arifin. Actually, it's not important for our analyses, but less so. Try to put it, Ondas. That will be humidity. It's not also important for the analytics part, but it is certain her pool how to To show how to extract the individual component is and exists latitude. Now the attitude help those pellets. Maybe e on the Knicks will be Longuet. You'd the donkey toot and yeah, that maybe last updated. This is very important just to show Let's to make his down since we will enter another ah, former layer Make it cease So we have thes components. Now let's ah, filter. Eat the individual components from the drink so will apply. The filter examines functions here Nick An extra start. Yeah, from the temperature temperature we could interviews a former here start my is equal toe feels her exit mail. Select it. The first is examined data So webs her the way. Their fees are examining data. So look it by pressing a four. Make it of solitude, fronts, coma and cynic's exports. So the experts will be done by Yeah, Devil Courts and Devil for or Slash and we will enter the strings. We can get the strengths off the temperature from the documentation here we have here the temp underscore. See for big center it Onda. We can call copy and paste it and close the double courts. Close the pot entous and hitting her so we have five. That means the current condition. In Atlanta, the temperature is five degrees. Integrate, let's move on to precipitation. Ah, coma. Double court that'll slash slash and submission will be here in millimeter copy and will paste it here. That's usually best and closer. Double coat and a closet bristle. So close the press sit into internal this serums. Precipitation, zeros, current condition the wind direction be before the exam. Ill manned. Yeah, Devil Cuts the forest slash wind Dad Action and double cuts Double close baseball This, uh, wrong. Maybe he's a string. He's wind Teoh. No, what? We but went something less correct. Ceased to Yeah, it toe wind. So this inordinate with the humidity will be also let's make it picked or xml close the Examiner Day Tomic East's of solid forms. Explosive as usual, Onda String will be humidity. It is because the opponent is This 96 attitude is not provided the documentation, but it's simple and make its soul. It's coma, you know. So that those best Yes, sir, and limited part feel the XML. Nexium, Your Data Express now is becoming very easy. The shrink who should be long. Let's right by a long. Maybe it's not current will not be correct. Ultimately, strike devil cuts and parents is a strong. I think it should be a long, long should be long. Let's make AIDS this May kids and in and in here this strikes nice working straight and our last one b last updated stink. It is no over the top. This one East must underscore days. They still go, Pete, and he can't pace it here past it. By future is formula as usual, extract ICS and mail. So snuff and last updates paste it caught now Miss four months based first latest marches to sales to give her smartest and notice for market bit. Yeah, Cain merchants and four months like Before might sell date you could select. This one is better. This needs decreases. Phone size. So the current is, he shows a current condition is updated as this time it's Philip's Very yeah, greens Time data. So Rick up. We have used faith Oryx in function to extract individual components from Onyx email strength. The next lecture will cut a Graze East Findings too much or historical weather data 16. Categorize Realtime Data by VLOOKUP & Conditional Formatting Part 1: the last lecture we have extracted individual, whether components or conditions by using the Fintor examine faction in this lecture will be busy categorizing this new findings. Too much our historical weather data categorizing this real time detail allows us to join together because current and historical information and, uh, eyes how past trends may have pockets, the Prison day or the wreath time data that we have. We'll explore how to categorise these three time weather conditions using a precipitation in Snow Dita and blended together with a historical quasar. Rikers. We have a Syrian birth. We have kept terrorized the's weather conditions for the Georgia Historical Role data. So let's see is thought, said Georgia with detailed tug. Here we have the wizard category and the weather. Keter worry looks us The precipitation data in column l and, uh, the snow details in color. Ah, in And, uh, it returns value off either dry or rave. Oh, are snow and the next he's as a weather type in Chloe O, it looks up. The actual amount off precipitation is it a range person tiled how or the snow percentiles top and returns a level off either right or heavy. There's a dependent on the amount off precipitation. Ah, a bizarre moment off the snow and, uh, with our label here feel the call of p return iss the core position off Ah, Volume column. And, uh, then I am in a similar way. We will create consistent feelings to keep categorise the real time data. The trust's visual is the top. He can applique it. The labeling from our historical always our data the 1st 1 be ways are category or with w kit a worry and the next one will be weather type. How are the lived type? And the last one will be with her little You can insert neuro here and, uh, go ahead and call it weather level ways I was able And so now we can't go places feel this using the real time information we just created the last time. So they start with the logic of conscience, the or statements here we will use ah, temperature and precipitation that, uh, it's equal toe if if precipitation is equal toe zero So that means is that he falling from the sky. I will call the quanta she to be dry. It's the other condition will be if and, uh, if it tricep Tischer. He's graters are zero a, uh, temperature is greater. Turn zero or equal to zero. The conscious will be rain sold. Call it rain. Okay. The other Cornish air will be if and, uh if And, uh, if any of the ah preciptation. His graters are zero, and the temperature is leads are zero. The pre turtle is zero close Beth Cova. That condition would be, ah, snow, snow. And if he's all conscious, are not spit or the value if force, you can call it as it's others. And the courts closed. Three braids, then hit. Enter, so the condition is dry. So this means the current condition in Atlanta, Georgia, He's dry. So we get this real time data. So is our next lecture will populate the other condition 17. Categorize Realtime Data by VLOOKUP & Conditional Formatting Part 2: This is a continuation from the last lecture. So now we will define as a waiter or type by using the similar conditional statements and the logics. This case will define the way their type so start by the if statement is equal to if open brace and the first logical test will be if the conditions that we just define called to rain is a precipitation is equal toe rain. So we will look up as a suspicion amount. Ah, from the precipitation percentile. Tough. So Ah, look of value would be the precipitation amount here. And so he too old and coma. So the table ari will be the first invitation percent I So go ahead and select toe table Select from a to to see and ah, look it by pressing F four to make it up. Soldier three fronts the next Here's a column index. So did we need Ah, the certain column That is, uh, three. So here the we need to make it approximate march as it's time we can t use the exact much since we did previously at this time will select uproxx mitt much because not a very good value of precipitation with have the value Ah, marching the historical table. So ah, what's the approximate much Does it it all those that data too much to the close value is in table, so close ease One end to go to the next. If statement the next option if go to the chart realization tough If the weather category is is equal toe snow off this time If it is a small if it is a snow Well, look up. Yeah, the small person type too much so Valeo cup. They look up and the local value will be the temperature volume this time. So temperature and, uh, Goma, the table array will be this no percentile. So go ahead and select out a table. And from a to C, select the ari and look it by Proceeded for to make it again of Solis refers, the color index would be three. And the much type is also This is also approximate much nicety deeds. Previously for the purpose imitation. The first option. So we can, uh, make warm clothes press. So if pausa option are not ah mate whole. That means if the condition is not either rain or snow our last option would be dry, so make it dry and closer press. And so the result is try So the weather type is not is a dry, so they're lasts We can create is a weather little here we can, uh, use a concatenation mated on. And if so, Jake, recall toe If if the weather type is dry uh, we just because select only that dry level So it's drying select. Ah, the way they're type as it is. So that means eat 18. So if is not dry, we combine the weather type in the weather category. So select first a live in the weather type and a front double courts space double caught and now also end and select the wizard category on. And so now Nicole's the brace make a close. The press hit into so the waiter level diesel so that dry, because the condition is not Santa at this time, is dry weather, so there is no injury snow or are dry. So to recap, we create real time detail categorization, which are laws us toe. Blend it with a historical with our data. The next video would generate dynamics or sit data is easing functions and formulas 18. Generate Dynamic Source Data by INDEX and MATCH Function: the last lecture. We can't have arised real time data that can be blended with the historical weather data. To do that, we have used the veil a cup and conditional ah, statements are logics together. This lecture will generate dynamic source of data using fan cations and formulas. We'll use index and much functions together to create source of data that can be used to drive a custom charts equalization Her snotty serve while use updated No, it was drying the last lecture house you remember now it is the light train in the weather type is also light because it is a precipitation is greater, son zero and the temperature is also different. Zahn the previous one my official sauce Our analytics is working properly so far. So we'll go ahead and open. Uh, exercise. Fine. The Lector 18 exercise five and will be working on the trust visualization, top world, great UN indicator or and middle to display the current accident, risk the location. We're interested in Atlanta CT area. So this will. This will help us to visualize and communicates the level off risk based on the current weather conditions in this area. So let's go ahead and call the this risk current risk and we'll probably it's ah ah, great. First, let's inserting new role here. Can I More? No, You're off. Solis. Cold cheese. One current risk apple. This Congress is real time risk. No, Right. Maybe we came name like, simply current risk. Call it current risk and we will populate. How is one off the index It accident rates that we created to last time and to do that would use their index and march vacations together. So they started with the index functions here. Ah, start by equals off equal sign in the index. So Alexander X function and the legs index it right for theory from B to D d five to d five And, uh, the call number would be one. Since he's one dimensional single Rory and the comb member of ah, now is a very important and we'll populate it with it was a much function Thistle. Yeah, Lendus It must function and the local value will be Ah, the current weather level here said You tunes toe he's still so if he's a local volume the leak it's a low coppery would be from B Juan to Ah, if one maybe let's do it by itself to d want do want is F no. The most type will be the exact much It will be an exact much and closer breasts one WordPress hitting true. So this kid's or from rise looking for the current weather level within these march all right in a row. Finding eat in this column on returning Zoff value in the index It all right, So the formula is working properly. The current accident rate is this one 3.19 That's is a light rain range. Yes. Excellent. And finally, we need to, uh, states that chart maximum volume. Let's call these charter marks then short so we can set it to be 10 can, uh, hard record. Really make it 10. Uh, just making it a little hard. Zahn The custom ridge moxie. So three cup. We have created the current accident risk based on a real time and historical data by using index, it in March functions in the licked next lecture with continuing working on charting, put us by the tree grow metric thing kitchens 19. Build Chart Visualization By TRIGNOMETRY Function: the last lecture. We have created the current accident risk based on read time detail this real time data and the historical weather data that we created previous lectures. This lecture will continue working on charges inputs by three room metric function. Exhale has a signal metric function. So we use that. So the teachers were going to create is a semi circle which has a total off 180 decrease. So we can translate the current volume until B nine us a percentage off the chart marts and say See nine and we can translate into Was that the equivalent number off degrees? So let's call this sale too. A generous cause It's degrees and Kenny enters a formula is equal to current ah current risk volume that he's cell. This is B nine divided by C nine it charged marksman times 180 degrees off the same circle . So the current of all you will be transit in to 57.49 degrees. We have gets a degree. So now let's try to find the actual called Drees the X and Y volume that makes this Diggory that's is a 57.49 degree. Still can't see a coordinates. Visual ization toe. Help You grasp this concept. We can open up a browser and search for units, circle geometry, open global and search unit circle geometry and click The first result this result and gets the so yeah, months. He's fun site and go down school down a little Pretend we have this visual is different so look at it looks What's happened? Win. We move the cursor a counter clockwise along the circle His angle grow and the signings co sign far e accordingly. So knots Ah, sign is X quality call sign is away volley. Let's fix it to the angle that we just created to 57.49 Does this single? Yeah, yeah, you can see this sign Value is 0.29 in sickle sign volleys 0.55 nine. So this is, ah, point that would usually to define It's a portion off the needle in our age chart. So let's go back to excel. India will at the two new feel this the white values and this still this first point would be origin or a grounding off the, um, needle for makes he swine Nick itself capital while volume and the next two will be X value x volume. So the first point will be zero for way in value can, uh, zero for X value. It will be the origin off the grounding, golfs and needle so ah, the energy needs of fixed such water into will never change it. So you can see is a semi circle actualization again force our drink You cannot completes the azar in office and needle which can vary from time to time Let's call it quits When value first we can use a sign function is equal to sign We'll select the degrees this cell toe still deny But ah which can't use degrees as it is we should converted to radiant So will reuse the right infection and Alexa degrees Close press in Duggan when other progress So now it gets a y volume enough the needle publishing So in the same honor this find x volume You can use a co sign function this time as equal toe course on and up embrace because more selected use a rod dance There I dance and close double close that gain so no . Yeah, well for our kids. We want the angle to grow to clockwise. So to make it we can. The import here negating volume cannot in negative here. It's simple toe sinfully mirror. Zack Kassian body. So to recap, we have created dynamic charts Source data. That's great. Coordinates off a little point that are a function of our current risk, which is based on a great time. Yeah, current conditions. That's not you can see. This is the the coordinates off the needle, the way volume and the ex falling. So we just, uh, developed its by techno metric function. The next lecture, we're going to start building the comport chart. 20. Building a Combo Chart Visualization: the last lecture we created the dynamic charts source data using Excel A Street No metric function based on our current risk inputs. In this video, we're gonna start building the chart visualization techniques. We'll be building a combo chart that is integrating to individual chart design to Geza. I will try to combine a donut chart with a scatter plot to create a simple and beautiful gauge. And needle visualization will be working on the dynamic source data that we created in the last video. First we'll creates a donut chart and there's a donut chart. I would simply serve us a background and give context to the question off the needle. For the scatter chart, input will have the dynamic charters data's X and Y volume. But for the donut chart, we can create SIM some source data. So go ahead and, uh, Inter some sample data. You can divide the circle to six place camp boots 50% for the half circle. Just called his first that donut slice. And, uh, well, we can enter 10% for the five ah slice drug Lee to don't we have the disease? 10. The person and the last would be 50% for his half circle, so no, we can, uh, input the a donut chart. Go to insert and, uh, e starts donut chart type here, insert it. So now it gets and it decreases a size a bit and position it to this site. Okay, can lead, sir. Title. And this also this ah selects data you can remove Busy that default one remove and we come at the new volume. Would we just create it? Qualities to donuts The serious name and the serious value will be the sample data. Select it. Click OK and K again. Now we can in tirzah the other ah charts scatter chart Less cold is needle nieto Greek. Okay. And okay again. Now let's Ah change chart type Go to change chart type here and select combo shirt down. Okay. For the donuts here. Yeah, select that doughnut itself. And for the middle would select sit scatter chart type is Carter chart type Click. Ok, so now now let's a beat the middle input. Go ahead and right Click on the chart Intellect, detail and Dietz a needle here. You did it. So we gets Ah, you did serious. Will interview Vaccine. Why? Volume for the X Value warrant. Oerter X coordinates Aziz, coordinate. And for the white volume Wait. We will enter the Y coordinates, so click OK, and OK again. So now we get old detail built into this convo charts, and, uh, we'll continue no for marching dis charts in the next picture. 21. Formatting Combo Chart Visualization: but will continue for months. Our chart at least four months. A donut first. So click and, uh, right click on the doughnut on, uh, go down and formats that a serious on this serious option Ah, we have this angle off the first slice. This make thes about 2070 degree. So that's Ah, beak, You still eyes who would be done? We can increase the size off the donuts. All sides also they see, make it, um likes a smooth 50 around 50% would be enough. Now they select segment big segment this segment so and go to feeling outline option, right click And for much data strays feel and not sign ocean and for the field like Northfield for the outline also know outline on a noble debt So it leaves 1/2 off the circle to be invisible. We wanted to be like this. You cannot just feel off with a feat color off the other slice can make the first slice to be field by green. Right click four months Data fits feel solid feel like sold it feel and then change color to dark Ah green For the next one we can make lighter green lighter in for the next slice. Yeah, OK, close the swan for say, it's one we can use. Yellow selects a yellow solid field. Go down and select this yellow and, uh, close it to see it select and lost on beat Our crate There are great is for but for do representation So he formats that donuts now next four months, a skater put to do that Select the wax. His first Celexa were access. It's a minimum. Makes a minimum not beautiful one and, uh, maximum to be one and closet and select the X axes. Also the same honor. Go to formats access and make as awas as a way of all minus one and a maximum. Yeah, to one Make it one and closes Kyllo's It So finalized its No. So we find we can, uh, click. Yeah, and for months, the line itself. Firstly, it's go ahead and do blitzes donated. And does Aziz scatter? So there's four months go for my dentist. Streets can change the color off the needle. Miss Mickey. It's a drug dark. Let's make it dark. We can change. Also other option Dev's can tell any option you want 10 tastes Trust visual ization. Send off this formatting. Now they start to no before that least four months. He's also go ahead again once again. Ah girl, hey, need for months that a serious for months and make its more feel I don't want any feel and no border or so. So now where is that? We clean up the charts and, uh, now we can test the trust usually different by changing the current risk level. Let's make it two is increasing. This week's seven it's also in creating one last taste will be nine. It is working well, so we have compliant charge types to put data from melt ical, source and Butte Custom. Ah, visualization. When we have gas through different formatting techniques to achieve this, that's great. We'll go on it and the next lecture? Well, toe on final A step, too. Bring this visualization to life 22. Formating and Customization Techniques Part 1: the last lecture. We have successfully appealed our real time charged graph. We see it is very beautiful. This lecture will be busy for marketing and presenting or findings more clearly. And we'll use ah, the field color hear distant borders and foreign styles and customs for marketing rules to design clean and published dashboard. So first, open up the lecture to into one exercise five and select or charter visualization talk would be for my team than mean chart when I hear and tried the unnecessary Ah tilts and yeah, Gospels. So this societal first, bring the chart. Yeah, Frontal. Ah, very time outputs will probe Bring this chart Dr Lee too. The real time data. Um oh, Can it's drug it gain next drunk and foods around here? Ah, Steele's not okay. Drug it's and we can't expanded these a bit. This one is more injury size graph. Don't on may be the internal corners Draghi to this site. Ah, nice, Peter. Good, Good Bullshit. So now this hides agreed lines Is it just a little meaning my destruction and created Quinn in simple look toe or abuse realization goods. Now it's clean. The decline is gun. Now this makes some text alignment which will make more readable still exists and make it center. Ah, I think the lemon is not working, so maybe he should Ah, no, I think is good. Now on Let's, uh, change the phones Killer. The whole phone color. Okay, my favor. Right? Want? Ah, my favorite Fontes Candra. So this better. And now this. Change the background. Oh, thesis. Uh variable. This makes this one and and explained it a bit. This decreases won his blanc. They created more. And let's, uh yeah foots background for these. Ah, one darker. This is Beta. Think next mitt make it bold on after court Less also change its unmarried first this one. And, uh, the background makes sees color and the phone color should be white. Let's do that dynamic bolt. Just do the same for the Paulson. Also Lanxess. Same background and font color. It's ah good now. So when he came to this variable and, uh, I think Ah, usually we don't want the user to touch this one, so we should hide it to height. We can makes a bugger on white. He's one option. You can make this white also so this again So you can make the bar bar ground in front. Toe white. This is one of Shin. Let's try another option, Miss. Uh, Barkindo, Right click you go to for months sales and, uh, custom accustom. Ah Changes General by three Quillin into three colon 123 Frankly, cookie nights become eso Weiss ihsaa values again. It's good. So we hide on this story. Change in the same Warner who can hide thea other source data. But just before that is add a level to help this chart. It beats more content context. We'll do it in the next video. 23. Formating and Customization Techniques Part 2: Now we'll hear the injection the most function as we did previously to create a dynamic text level here beneath the chart. It's Ah, move the charts First to interest that ticks. Ah, cane puts here. Okay, maybe here this. Merchants first, Mert and we can interpret the index in chance. Here starts. My is equal to index. So, like the index function, So the ari will be No, there is school level from pieces toe day six selected. And there are a number would be one since he's a one directional single her o r e the ah coloring. And there we can pull by the march function. So the lockup value will be the weather level here, injuring time data and the look up Ari will be, well, much ink this from try to Harry. And, uh, my Stipe will be exact. Much close her to breath and close inter. So he gets this result very school level. But let's make it more explanatory. Can modify its like sees. Yeah, we can't enter. Ah, on explanatory ah, level. They call it because current accident risk and colon here and this Ah, close that devil cooks. And finally, 1%. So it's he's saying. Current accident True, the level is dynamic. So bring eat the trust again. Please, uh, re arrange it. It's good, but first it's formats. Ah, text this. Make bolt. Let's make it board. You can change the background. Also, he like a neighbor ground. Ah, any clearly want It's good smoothies. He's one. Ah, I think it's better for me. And now rearrange Chart King on the script. So let's finally came home hides on necessary data, but to do that selects. And so we did before we can change your background in the phone to White is that was one option in the other option isn't four months l go to kissed him and Inter Ah three Cynical 12 c and okay. And then also these background changes one to white changes whites. So now everything clean and are chart is looking great. It's better if we interned the charges title. Let's call it greatest time Weather on athletics, Great time with our on analytics and, uh, spare we ah also puts the current city the current cities Atlanta can change the city. Okay, let's, uh ah. Four months. This sale only deletes Ah, three color. We bring the front. Let's change the background. Maybe this is better This change of front, toe white and then make it also born and large. Think this is looking great? So, to recap, we used to sell for marching roads present in communities or data any clear, powerful and beautiful way ST. We have transform it in a boring road. Data in tow, clean and organized dash warts so we can't successful. 24. Wrappup: Okay. Congratulations. You made it. We have come to the end. We have Kurt very important analytics concerts that can be applied to other areas off expertise. As you remember, we have used supplemental data states that is a Georgia traffic accident, location, data and the Atlanta City historical wither Data set just it. Does it help the Dawes explore hypothesis and uncover new a new politics insights we have clean. It arranges a teeter sit by using many text formless and heroes like a concatenation and also the left function in other takes. Formless. And also we have a table rights to the beasts in time field to it's a scramble of components and we have used a mere ISS woke up and reference functions mostly, uh, much vela coupland index function. Ah, with many a logical analysis approached. And also we clean up the same for the weather data and we used many narrow political and, uh, yeah, really reference functions. We have also used percentile rank function to categorize the type off Whether hey, also for the snow, we used the same Yeah, for Morris and charted visual addition. Come also, we used many formal us, including that powerful county from kitchens Soto. To see it again, you can occur the inputs to do that, we can right click and go toe form our cells and you can remove this three column so we can't drink now. The inputs it can repeats the process again, but four months and, uh, remove this colon so we brings imports here. As you remember, we used the county function summarizing. Agreed is a data and also because Tom Ridge with, uh, if statement and for current risk also, we use the index and my function to gether and here or so that trick geometric fun cautions a co sign into sign function, including the rodon's ah, to say tell X and Y coordinates. So ah, we can see also the X value you can do that repeats the process to uncover, um X volume. So this sauza co sign function bring the X volume for the chart and inputs. Also, we have used Ah, let's hide it again first. So he had it and oh, hey, we used a p I feats that by the wheel service function, and we also filter out individual components, buys a filter xml, Frankish ins and finally were applied data visualization and for marching design principles to communicate findings. Troy. Clear and Intuitive Dashboard I believe you have get a new set off exhales kids in carriage to tackle other similar products. Already skilled toe Azarias. The approach may be different, but the concepts will be the same. Analyze the detail around you and explore new way to visual aid. These data there are a little Friedel sauce here can use Microsoft's Helps center itself. You'll get countless tips. Can also used you to free tutorials. You'll get money Good videos on the subject you are searching for. Angie's Exhaled Tips website also has a good resource. I'm sure if you are do to keep there are many helpful resources so that you can become an elliptic six star. It's up. Finally. Thank you so much for joining me. I wish you a best on on sticks in developed