Mastering Essential Excel In 3 Hours: Essential Excel from A-Z for Beginners | Damtew Engida | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Mastering Essential Excel In 3 Hours: Essential Excel from A-Z for Beginners

teacher avatar Damtew Engida, Information Designer & Data Analyst

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

75 Lessons (3h 27m)
    • 1. Power of Excel

      1:48
    • 2. How to use the manu system

      2:53
    • 3. How to use the quick access toolbar

      3:08
    • 4. Understanding Workbooks And Worksheets

      2:13
    • 5. How to use the formula bar

      1:51
    • 6. How to use the status bar

      2:22
    • 7. How to use the navigation tools

      1:52
    • 8. How to use shortcut menu and mini toolbar

      1:48
    • 9. How to create a new workbook

      1:35
    • 10. Data entry and Editing in Excel

      2:30
    • 11. AutoFill Data Entry Methods

      1:58
    • 12. Understanding Dates And Times

      2:28
    • 13. How To Use Undo And Redo

      1:58
    • 14. Understanding Dates And Times

      2:28
    • 15. How to Add Comments

      1:31
    • 16. Using the Save and Save us command

      1:41
    • 17. Create simple formulas

      3:14
    • 18. How To Copy A Formula In To Adjacent Cells

      1:33
    • 19. How To Create Year To Date Totals

      1:58
    • 20. How To Create A Percentage Change Formula

      2:18
    • 21. Understanding Absolute and Relative Reference

      3:04
    • 22. How To Use SUM And AVERAGE Function

      2:20
    • 23. How To Use Other Excel Functions

      2:32
    • 24. How To Style And Effect Fonts

      2:19
    • 25. How To Adjust Column Width And Row Hights

      1:58
    • 26. How To Use The Alignment And Wrap Text

      2:54
    • 27. How To Design and Use Borders

      2:17
    • 28. How To Use Special Formatting Methods

      3:16
    • 29. How To Format Dates And Numbers

      2:49
    • 30. How To Use Conditional Formatting

      2:39
    • 31. How To Create And Use Tables

      2:58
    • 32. How To Insert Shapes And Others Visual Elements

      4:53
    • 33. How to Insert and Delete Columns and Rows

      2:48
    • 34. How To Hide and Unhide Rows And Columns

      2:45
    • 35. How to Move, Copy and Insert Data

      2:52
    • 36. How to Find and Replace Data

      3:21
    • 37. Exploring Page Layout Methods

      3:24
    • 38. Page Setup and Printing Controls

      3:15
    • 39. How to Preview Page Breaks

      2:43
    • 40. How to Create Charts

      3:31
    • 41. Exploring Chart Types

      4:01
    • 42. How to Format Chart

      3:21
    • 43. Axes, Titles and Other Chart Elements

      3:03
    • 44. Exploring 2016 New Charts

      5:15
    • 45. Freezing and Unfreezing Pans

      2:14
    • 46. How to Split Screen Horizontally and Vertically

      2:27
    • 47. How to Collapse and Expand Data Views

      2:09
    • 48. How to display mulitple workbooks and worksheets

      2:59
    • 49. How to rename, insert and delete sheets

      2:18
    • 50. How to move, copy and group sheets

      2:54
    • 51. How to use formulas to link workbooks and worksheets

      3:40
    • 52. How to locate and maintain links

      2:12
    • 53. How to Use Count IF, Sum IF and Average IF Functions

      3:41
    • 54. Using The Vlookup Function for Exact match table data

      3:23
    • 55. Using The Vlookup Function for Approximate table data

      3:23
    • 56. How to Protect Worksheets

      2:27
    • 57. How to Protect Workbooks

      2:28
    • 58. How to Assign Passwords Wokbook

      1:18
    • 59. How to sort Data in Excel

      3:07
    • 60. How to Insert Subtotals in A Sorted Data

      2:53
    • 61. How to Use Filters

      2:38
    • 62. How to Split Data in to Multiple Columns

      2:25
    • 63. How Use Flash Fill for Splitting and Combining Data

      3:07
    • 64. How to Remove Duplicate Data

      1:17
    • 65. Using Validation Tools to The Best

      3:49
    • 66. How to Use Goal Seek

      3:42
    • 67. How to Use Scenario Manager

      5:08
    • 68. How To Use Solver

      4:19
    • 69. How To a Data Table

      3:36
    • 70. How to Create PivotTables

      3:21
    • 71. Manipulating PivotTable Data

      2:42
    • 72. Date and Time Grouping

      2:30
    • 73. Extended Grouping Analysis

      1:53
    • 74. How to Use Slicers

      2:54
    • 75. How to Use Pivot Charts

      2:57
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

55

Students

--

Projects

About This Class

This course was designed to focus on the most essential of EXCEL. It covers all the features and capabilities that brand-new users need to get and work in Excel within a short period of time. This mega course covers Data management tools, Data Analysis tools, Function and Formulas, Charts, PivotTable and PivotCharts, Lookup and Reference, Printing and Page setup, and many other Essential Excel. After you finish this course, you'll have the skills you need to start building easy-to-use, accurate spreadsheets in a short period of time.

Each lesson 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 essential Excel features by doing the provided project examples. Each lesson is accompanied by the project exercise files so that you can do it by yourself at your own pace.

   

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

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

  • Manipulate dates, times, text, and arrays 

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

  • Date & Time functions and Categorization

  • Advanced Text functions

  • Calculation and Mathematics Functions

  • Counting and Summing Functions

  • Lookup/Reference functions

  • Formula based formatting

  • Conditional Formatting

Meet Your Teacher

Teacher Profile Image

Damtew Engida

Information Designer & Data Analyst

Teacher

 Damtew focuses on uniquely crafted data visualizations and insightful analysis that both engage and enlighten others. For data visualization and analysis purposes, He mainly focuses on free mapping and analysis tools. 

He is very passionate to share and help people better understand and communicate their information using data visualizations, visual explanations, infographics, and Geo Mapping

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Power of Excel: X Series on extremely versatile India's program. It is the world's most popular in powerful spreadsheet program eternities for data management in storage, a large amount of data can be start and quickly processed in Excel by filtering, sorting and doing calculations, Surgical announces charting in other a meeting beauty and functions. It could be used in finals toe for me to reporters, charters, simulations and do what if honors his own data. Plus, it has a lot off finance unspecific functions. If you're keys, sales rated, you need Exhale to do totals, average margins, vehicle collisions, charts and create wonderful dash borders, interactive mains and many other indicators you can easy for Datanalisis Exile is a powerful tool for data analysts. Also, it has amazing future like people tables to console it in focus, segment off your data from a large data set. If you work it, start off engineering. You needed to do calculations. Portrait projected figures. Conte's Velicka functions in charts off Morris types. They also have a range off beauty and functions specific to the area off, such as Convert sign co sign in town functions. The application or fix It is limitless. So let's dyinto Explorer 2. How to use the manu system: excels Menu system consists off a serious off tops, followed by a group off icons. The entire menus system is referred to us the ribbon. The most common Tubby's home tub in the home tub. Loss of formatting till solved by level on the left side here, including some related to numbers. Some additional command type pictures to the right. Also in the Easter Tom. Here we have people tells Yeah, and the different charters in graph and some other rated futures. And there's the other is a page layout. Tough? It has a lot to do, is printing and also some other rated futures. The formal atop has also a lot to do these formula, which has, ah, a building form loss in some other futures here. Us. Well, the death atop contains important data management tools like sorting, picturing in as there ah, which is related to deter management and also the really top. You're probably will most use us often listed here, but we have here a spell check in some protection features like workable protection and work shifts. Protection is are very important. Then or so the view tough hostilo to to be some off the visuals. We have also that developer top the last one. And here is a final tall Can we have a picture for fire management? Ah, print export and azar ah stuffs here can get talking to our standard views off X help I is a pressing escape over this Left are here are also additional sections in this little room It show us different features that we might not seem here on the home tubs operates. You'll see a trace it toe how with views at Tufts and they're even. The default is what we are seeing right now. If you want to show the tops only can treat just to focus the data we can also also hate or even also good can return it back by clicking Gain saw Excel ribbons system is very visual and intuitive will use morsels of futures so out this course 3. How to use the quick access toolbar: above the river may no system in the upper left corner. Off your screen ease was called the CUC Access Toolbar. Now it's has only three buttons in it, a save button on undo button and airy toe button. But we cannot do it. We can put buttons up here for frequent use, for example, who use that devil Underland Common frequently become party to the cook access toolbar we can right quick, and we can add to it so we don't want to go now. In sehr will save our time greatly, so you can come here and, uh, ah do was a coup Mont. Yes, it's feet or so, and we can do the other tools in the same manner. And we can also add some from the default least like this. Sort of sending. We need more can go toe more commanders can make any command we needed frequently like this chart. And if we have searched choice here for you, we need more can choose command this, nor genes that are even, so you cannot any command we want. And if that's where not enough there is a choice called own coal mines. So here there are tons of options here it 1000 trees available. Can it in equal man you want and, uh, top down order that we see here will cross pronto the left to the right order After you select our traces quick. Okay? And we have here can remove food. They want it And we can customize that the kick access to war or so that can't, uh, make on adjustment and click. OK, he wants a original. Yeah, Setting can go toe customize a quick access to more and ken recesses Click. Yes. Now we have, You know, one It looks when we first start Excel can puts ah Creek Excel toolbar Bill does everyone If you change your mind, we can return it by, um Right clicking. So the cake access to worries on excellent tool 4. Understanding Workbooks And Worksheets: in exile. A worker book is a file, and interchangeably. The fire is work. Work we can see is a name off five or is a workable cut the top off the screen. If it is a new fight, then you will be Book one. We can open up to see Easy's in auction can go to file and and new and click these blank or cook. So hit it is every work work is comprised off. At least one work shifts you cannot shits here. It's a plus sign, and, uh, we can delay treats. I can rename the sheets and can't move a tow. Any position Want we can change the color off that top, like sees can get all the options by right clicking owns that name. Most of Shea's all work shit or simply shades is comprised off columns and arose Columns are vertical and identified by laters. Girls are horizontal, and they are identified by numbers. There are a bust 16,000 column and one milion roast down every city and exhale on an exhale treat has on address. Right now. I mean a or all three services listed here, who called so three cup workable book is a file, and the file is a workbook. We have a working shit each more than a 1,000,000 row and Moore's on 16,000 columns. 5. How to use the formula bar: on an exhale screen there just peeled off. The river is referred. Tow us a formula bar. The formula bar right here contains information it's active. Still contains for mission. Otherwise, it will be blanc If I click. For example, in Cell J one, we see that its contents presentation. If I click just of him, it will be number. And if I click Blunk, it will be shown netting. Now the reason is called Formula Bar. He's If I go to a still contend a formula. The formula will be visible here in the former bar here. The formalize this plate, as you see because us move this active still around, either by pressing are rookies or pointing with a ma zing clicking somewhere. You want to know whether it's still contents value or it take us or a formula. It is very common to look at the formula bar frequently. There are many shortcuts in the other tools for finding former that will that will discover in this court for now keeps the points that the formula bar is extremely useful. Let you knows what is going on, and on each individual cell 6. How to use the status bar: are supposed home off the screen below the sheet tops, you see on area referred to us the start spar theory of fun. You'll see the world ready in the lower left corner when you text certain actions. Like if I church keeps, look here, it say's keeps look, but there are more uses. If I select the Riven column here, call E as opposed home of screen. There is a few It's testicles. Measures here is tells me what's average Ravenna that counts and the total of the some off the Aravena For right. Clicking the Start spore. You'll see a huge list off forest entries. It can turn these on and off. These are absent minimum, the maximum mark Simone and and medical count. So we'll see here. The over its account is that minimum and medical count and some oxen to some. So this touch spar keeps us inform it as a member of the totals for different sales that we might have highlighted. There is also normal here, which gives us a typical excelled view and the next button two varieties called specially out, and it is Hunde. As we start to work with different data conducts the title and a local. So here is also the Pidge bread Few and deceased Ah also tell us to adjust and to make you ready for print will discover more in later section. On this. The last one is here. The zoom bar. We can zoom in and do not us. We want to view our A data. 7. How to use the navigation tools: When you move the mouse off a finger on the trigger part, the cursor around Exhale screen will also move. But at different times, this pointer will change if I slide collect Ward and puts it on a boundary between Rose to thing to a two way up down arrow. If you slight on this column hitter, it becomes a one way down arrow. If you go down in the steel and point to its corner, the most point of changes from a three dimensional plus into 18 plus so the most pointer will change accordingly. You can press control plus page down to move one. She to There's all right. If you'd like to move leftward the candles wis with control plus page up, I cannot really use a mouse for this action. Also, if you have another work open, you can go to the view top in there, even and off to the right. You see a button called switch windows. No, when you click the button, you'll see other cooks if they are open so we can't switch as we want for you. We are working on different projects, or were cooks can easily switch windows so to Rick up. We can use a mouse controls plus down and our parole and the sweet window for easy navigation in our projects. 8. How to use shortcut menu and mini toolbar: the settlement system is extensive. There are so money command here. In addition to Aziz, Exhale House. So money handy tools to get our job. Stern. The 1st 1 is a shortcut. Mean, if I want to insert a new row here, I can simply right click and see. The options are available. There is Hey, insert, do you late and in a house. That stuff she's a shortcut manner can get this future in. So money places he is a top. If I right click, I have self so money oceans here, village, Roy name and top color and others. If also I right click on the main menu. Have this shortcut tools in my hunt. So it's the great Oh, the other useful future. It's a mini tool bar. You can get many tool of war. Just win, right click some miles. I can use this Ah, for four month Inc Ah, purpose, for instance, this for months he's highlighted one, so it's very easy. Instead, off searching the stores from the main menu can use some efficiently, so any time we use the right mouse button will have this kind off manners that will add for speed and efficiency. 9. How to create a new workbook: If we want to create a brand new pork book, we can simply press control and end for new will have workbook with a name book one in my case book for But there is another with great way to start the workbook If we need specially cook starting template can goto file top and click new So here we have So money trees we have so many choice Tres to select start our new workbook We can select the category Also kind of search form the Internets who don't get the fit. So let's select one off a tenancy in auction in because they saw protect, uh, the budgets campenni budgets. So and, uh, we can, uh, modify nuts. We need No, that's great template so we can use a bunch of template two feet for our needs. 10. Data entry and Editing in Excel: data entry. He's the most basic things we can do in Excel. But there are some rules, some concepts that we should become family with dissenters a course of revenue in off the street IQ giants here I would enter the name off. The Tifton stood down. I entered 1/4 across here. They started by and drink the name off. The take giant is we can rocket by the most always arrow keys here and a two they start for sitting in Trained to be Gogel, then down our oh to get the next sale. Inter the Facebook in the last one will be Microsoft. I crossed soft here. I missed the f here can it didn't eat either by difficult king on it or we can, uh, we can go to the formula bar and a d'Isere simply. It's a simple candidacy here, So I interior with me now called This Hater Toe Courter. One Q two and Q three less interest. So a random raven and obedience 1st 4 couple one under tern ity and on to 24. The next line is 140. They're like I mistakenly changed a zero to all it automatically become left online notice has become left online. Solstices told me that import is not number so good and changed. Zero Ah, always zero Nice become right on light can enter the other numbers. What's the peak is the basic idea is numbers are always right are lined and the text are left aligned. 11. AutoFill Data Entry Methods: the auto feel feature off Exhale arlo sauce to quickly put the data in the sale. For example, it's Stiglitz's quarter and indoor it by out of fuel mated at least called the Swan key. One If Draghi to see site cross, we'll see. Excel is I understood what want to do and giving us suggestion the next injuries Kittle so it can drug it and feel automatic. Ali, we can use it for amends. This entry also, if we start here April in drug, it's don't exhale Autumn article. Fear for us. The consequence. Chief news is some time, um, it can work, um, for shortened approach. Also, this, uh, start there was John here and drug. It's don't it. You'll feel as you want it. And, um, sometime we need to drug. It's a port also. Let's try here, April in drug, it's a port. So is working. Also. It's a very, um ah, nice. That put and we can do it for data entry. Also listened her here 11 23 and drug. It's don't it automatically feels the next test down work. So sort of future is excellent 12. Understanding Dates And Times: Excel has a great computing capability when it comes to working with dates and times. And also it has a number off built in functions that are lost to work with dissent time If she entry, we cant her Ah this Isar bhai slash or Duff place inter by slash surprise The first jacket 81 23 And, uh, we can't enter the same value is a separator here the results will be the same dates Dash Juan dust to any to me to be the same. Yeah, it's the same result Some country may start with the months and others may start with a debt You can for months Ah, to chair to change it to this type and click Ok, so, uh, it looks like they soon on da Philly mistakenly intrude the dates like this one to serve tea 2020 Exhale put it is that means it does not consider it us it dating tree since because there is ah North 30 days in fury. Vantage chain checks is by as his former type So it doesn't changes so it does not understand that is ah defiling. So she corrects is on 28. It would be. That's good. It's coric can also enter time became type like for 14 and 38 can make it. Ah, like these on also seven TPM. I think it's p m. So it understand what went went to do. 13. How To Use Undo And Redo: if you make mistakes under is a great help. For example, if you realize dot the faithful Caribbean who here has a mystic? I could go select and deal Isha the import. But if I want to keep it for now, I can't undo my oxen. I'll go to the kick action toolbar and click see on better Here the shortcut keys Contra plast it. These aren't easy key stroke combination issues Undo my action But it has more than that. If you click this little Arul, we'll have a trace from where we want to undo our action can and do upto 100 off our trust actions. What if we are? We need to return there. Andrew, we have very to Britain. The shortcuts key for redo is contra plus y. We can't ready to ah from we went to to we do it There are some exceptions actually that tweet, undo and redo does not work like fuII deletes his stuff and went to undo We can do that and and is a few top old soul. Most action cannot be undo and redo. So this Andre and redo are really hunde and valuable tools could press controls it and control way talk says for CUC 14. Understanding Dates And Times: Excel has a great computing capability when it comes to working with dates and times. And also it has a number off built in functions that are lost to work with dissent time If she entry, we cant her Ah this Isar bhai slash or Duff place inter by slash surprise The first jacket 81 23 And, uh, we can't enter the same value is a separator here the results will be the same dates Dash Juan dust to any to me to be the same. Yeah, it's the same result Some country may start with the months and others may start with a debt You can for months Ah, to chair to change it to this type and click Ok, so, uh, it looks like they soon on da Philly mistakenly intrude the dates like this one to serve tea 2020 Exhale put it is that means it does not consider it us it dating tree since because there is ah North 30 days in fury. Vantage chain checks is by as his former type So it doesn't changes so it does not understand that is ah defiling. So she corrects is on 28. It would be. That's good. It's coric can also enter time became type like for 14 and 38 can make it. Ah, like these on also seven TPM. I think it's p m. So it understand what went went to do. 15. How to Add Comments: adding a comment and XL series So poor easy. Sometimes we need to add comments to explain our users. Maybe Howitzer Tain, former workers and this or create this assumes that the Quarter Street revenge is uproxx meets for Microsoft, and we need to show its this to do that right. Click the sale and, yeah, here we can write. That data is approximate as it is a message around, too, and show and we can recite the books if we need it. Right size. If we click outside, we can see a little radar, which in the kids that comment we can't eat or deletes a comment by right clicking again. Here is the teats are the least common. You can show or hide a comment from the review. Tough here we can show old comments. Are we came? Yeah, height comment. So adding the comment and it's still can be a great help when we need it to show 16. Using the Save and Save us command: we have two options in case of saving data in Excel save us and as a safe option, we usually you say us, we we want to save a workbook for the first time and we need to name and save it to another location. It's right this concept in action. We have this worker called manipulating Data entry in Exhale To do that, go to file and you have these options. It's too you you save us here. Here we can determine the police. We want safe this Judas PC. We can choose desktop and here we can diese file named and the final can be day signed tree and eating and you can choose the four months here Save us type we have Manu list is here Now we can choose any like PDF or others. Let's chooses older version if we need it. Sorts save clicks have and continue. So we did it and set it in. The older version can see of any four months we needed to update our war from times time became used to but or use. The control is in short 17. Create simple formulas: creating and manipulating from Los in exile is a common a job for many Exhale users. We have here the tech giant's quarterly riven in millions of dollars and one kicked roses in exile. We write former US best own location, So let's try to inter formula to get the total sales off the first quarter. Here, the disease we can enter former here in B six. We start always by equal sign. So where we can try first by typing to sail. Ah, first be three and before and be five before here. Now it's history, but B three that before and be five. So he gets a total presenter and we can do this in simpler method. You can simply selects a sale. Let's do lettuce and try again. It calls to here and will select a seal these three plus before plus B five. Now we get the same total. There is one more option to gets us some or the total. They tried to find the same in average Ah, off the yeah camp honey. So this inter, there's some form here. First, let's call this hater total and these are marriage, so we can somebody's recorder. Start these equal and, uh, some and open brace and Celexa sales from that be 32 thestreet and percenter can close. The press now gets a total, and also we can do in the same honor. The average is equal to average the Lexus one and operates. So let's a sales from peace three to history and close the press and enter. So he gets a total into the average can drug it for the others. Campenni also So these are really simple formula, but contracts formulas are also used in the same honor. 18. How To Copy A Formula In To Adjacent Cells: is the last lecture. We created a simple formula here to get 1/4 1 total and the campaign to talent the average off the quarters here. We want to do the same. Force it as 1/4 and companies. You can use our previous formula and, uh, seats. We can double click on it. And also they're related Says we don't need to rights here. And as a former you can drug. It's here and across all the quarters so we can drive. Ah, here to quarter three toe exhale automatically change the sale reference to copy the former double click one off the formal, we can see the change here. The same references change it the same one we can drug. Ah, this former to don't get a campaign in total and also for the average down sold for all the former Star Corp it so it is very easy. Super easy for exit 19. How To Create Year To Date Totals: the last lecture we have seen hard to create. Simple from los. Now I can go further. Tokyo Great. Your year to debt totals This Furcal quit profits Here it is simple. We can subtract the expense from profit. Let's ah B three minus. Before now we can drag to copy is a former to the other cells. So now gets profits. The car creates a year. Today totals for the quarters across. First, we should keep in mind the first quarter year to date totals equals the profits does miss B six. It cost be five. You can post simple from raw here basics because to be five, that's 29 29. So the next quarter it's cute, too. Your year to death total would be the first quarter. Plus that second quarter that we can put a formula here is equal to B six plus C five here . So basics or be five Blast C five. So to conclude the for the other quarter, we can simply drugs formula of to ah, quarter full. So we gets a year to date totals for the other quarters we can eat. It's ah, simple former for others also 20. How To Create A Percentage Change Formula: in the last lecture. We try to find it a year to date profits. Now let's try to find the person to change of profit. BA tuned the courses from quarter 1 2/4 4 obviously is A first quarter will not have any change. So the percentage increase who started in court to hear taught gets a change from quarter one to quarter to We can simply subtract this change can subtract quarter to ah course. I warned from Carter to and was his a change, and we divide it by 1/4 1 Let's insert that simple former here is equal to ah see six minus B six and we'll divide by be six here Quarter one mm basics. We can't inter. Ah, this for more. We should enclosed eat by parentis. Otherwise they result will be frank. So this is a result. If we didn't enclose his appearances, so the result will be very different. It's 59 the strong. So we should consider always as order off operation. So now we can drag Ah, this formula to copy toothy earlier quarters. Now, this is a change we can decrease the decimal to this small place is enough can make it personage. So he's a change 21. Understanding Absolute and Relative Reference: this lecture will discover the importance off, off solid and relative sale references in Exhale This START Boy Understanding Reality sale Reference. First ladies used an example for this. If the camp money off this employee wants to increase their salary, say, by $3000 it's simple. We can simply at, ah, thoughts, uh, amounts is he called Toe Beetle base salary plus $30,000 the increment plus ah, anaemic in tourer and copies down. So this is a new salary. But look, the rate forms. This is peacefully and this one before he's on B nine. So the sale references changing for each employee. This man, it's a sale references relative. It will change from time to time. How was the absolute difference type? It would be a fixer type. Let's use on a 13 year. Oh, for, uh, this to understand it. If you want to increase their salary, say, by three persons here so we can enter a former here, the formal would be is equal to be to beetles. That means a base salary times the in cream mint that is 3% plus that base artery salt is in cremant, and we can drug it down to see they fix for the first salary. It's OK, but the rest our cities. It is a referencing. Ah, on a pencil. So this makes these Ah, off so leads. Let's make it the Salish fronts absorb it. We can change it by Press Inc if for so now it is off solitary fronts, this drug, it's a gain. So nice is working. It's referring that form, so the percentage in Greece as we need it 22. How To Use SUM And AVERAGE Function: summoned average are the most commonly used functions and exhale if you want to create it total. Here we came Use a function Some is equal to some and we can select uh, data from the left Some And here Cain turrets. So this is a total. But there is a faster way to do this Can use the auto. Some feature off exit King, go to the home Tough. It has shortcut Also out then equal. Now we can put in a sale Eyes three and we can cross also equal These a result exhale under sons What want to do can simply to get the result are we can simply it elector sale We want to find a total and clicks auto some function He's also faster We cannot also from different places like from is this table and the table well can point any and where Lie here around here and, uh can press salt in equal. Now you can select a sense we want toe are together and present here It's a result you can find the average also from this drop Ah, down Ganga to Otto some and he is the average you can select. Also there is the maximum or the minimum here. If you wanted to do so is great 23. How To Use Other Excel Functions: except has over 450 functions. It is a hugely saw function. You goto the formal atop the ribbon and slide over one was entries. Drop down. You can see zump here. Here are he. There is a hugely soft financial function we may never use, most off some. He is a logical function here, and the next one will be the text function and our saw that date and time functions. And it here is that look up and read France function and ah must entry grow metric function . Also the other function statistical, engineering and other functions. Well, maybe. Or women with this huge list or function in Exhale can get a small two went over the cursor , he said. It's a small tip here. It will give us a hint when to use the formal. This is Trey. Want example? If you want trunk, there's this new salary. What function is available to us? There is Iran coverage function can use that's can enter. The formal here is equal to Runk average. It's a late run coverage, and we want this number trunk and the reference will be this list or cancel X column may see and close breasts and enter. Oh, that is a mystic. First select the number Here is the number Sita comma and the reference will be sick. So we can, uh, opiates don't It is. Ah, number one on their list. Cultists. Runt is so you can explore other function as well. 24. How To Style And Effect Fonts: first open up for Martine Exercise fights. Here is a phoned type sheet. We have thesis stuff. Sorry. Table is a default phones type. It is Calibri and D. Forced phone sizes. 11. Previously it was Ariel with phone size 10. Here there are a lot off phone type selection select from we can see. Ah, it in auction list. Select till and see how it changes. There's a bunch of selection because as you prefer and wish, that's like this that C and also we can make partof its bold or italic and also underlying feel. We wish Onda eso we can change. Ah, it's so on doozies and we can change the color off the phones and also we can change the background. Let's make, um, days phone and or so curved. I'm not cursed, but the background Let's make this lighter on the list. Changes stand to make it uniform. So, um, and here there is more possible from this. Delic walks. It is effective, struck through sport, give it and sub scripts and phones Tyler's home. So the possibilities money 25. How To Adjust Column Width And Row Hights: adjusting Rose and column, which is are very simple and straightforward. For example, if you want to make waiter things, took column and we can select first. And, uh, when we points here in the intersection off in A B and C and click we see is a current would size so we can drug. It's a sui need to the left or to the right. We can also use the best feet made it. You can select the Coleman we want and double click it here. So exhale automatically. Justice the Coleman weeds. If we need toe, apply no for the whole sheet we can select here and double click on one off the Kahlan. So in the same honor we can just the rule we dissolves, they select it, and drug, too, don't so we can increase size, and also we can double click to automatically. Just here is a form of Calmund. We have a row hating Kalume hate adjustments because they let any role come and gives our moment. We want toe adjust, so it is very easy and straightforward 26. How To Use The Alignment And Wrap Text: open up the exercise for first. This lecture will see the application off. Ah, line rap and merch features take a result. More typically lift aligned and numbers are automatically right online. As you see here, numbers are automatically left aligned by default. Sometimes we need to change this default set up for instance if you want all the line Ah, the's right can go ahead and make a line right. And you can you can make it center. Nizar, if unit the same honor, we can line the Topsiders eyes are rights or preferably center if any Take us is offer flying the sale. Ah, listen, DCIS June volume just to show June salary detail can modify lacks is now, as you see is overflowing so we can simply select end can make wrap text Now it looks good so further we came Just some adjustment can make it bottom line, center line or upper of a line. If we need to change the angle of Texas, we can simply try this option. There is a lot now as a first car or 40 until or any angle we can make also, ah use Ah, the optional features here you can select his first go to additional future can use these Uh uh It takes the land can rotates any uncle and click Ok The other important feature is a merged future If you need to merch cist like this title we can go ahead intellects a seal We want to merge and use emergent center or mercy Onley can do the same can change background It's up his our best option. 27. How To Design and Use Borders: we can make special in forces to search 10 states in exile by designing in styling borders . There is a border feature here in Exhale. If you want to stand out certain XYZ, we can simply select. Then they go to produce and maybe Alexis teak outside borders nor simply outside borders. So now it's beginning. Stand out. So we can, uh, see more porter features also. And here isn't more. And we have cease yeah, style can select any style can Celexa presets Also here McKeen also, uh, change a killer. They selected thread color. Ah, we can select Sees, uh, the borders. And finally you can click OK? No, it's so looks good. If we change our mind, we can go ahead and, uh, select no borders, not the skin. And we can also use that drum. Made it. We can troll Mon Aldi each sale our group off sale. And also we came. That change is ah, killers on the line type. They see it in action least Alexis line type and a colors rate. So scripts 28. How To Use Special Formatting Methods: exceed house, different voice to for months. Numbers and characters in this world shoot we have. This is tough profile. We have the Social Security numbers. He's, Ah, phone numbers and the salary data. The phone number sent Social Security numbers our randomly generated. It's focused on their Social Security numbers. What's wrong with disease? In numbers, we usually see dashes. Spittoons are characters in Social Security numbers. So how do we format it? Brothers on Ah, typing that Duchess Minority. We have a simple mated. We can right click to get the four month cell or shorts Cat contra plus one. And, uh, here gets, um, a special characters. You have a special. So he sees a Social Security numbers so he can select it and click. OK, now it looks great. The Duchess are no, truly is a data that only is a four months in the same honor became four months of phone numbers. Press control plus 12 gets a four month cell. We can't right click and gets a former civil as well. And here the special characters we have this four members you can select and click. OK, so now the four month looks good for the salary. We usually add a dollar sign here. You can simply ah Kik four months it Hear that when we try toe widen the column here. The format is not pretty good as you look. So we can talk this polling by going toe Former cell and choose another for marching. Yeah, made it so go toe controls. Pass 12 Former sale. Here we have currency. You can select one off. It's nice to excrete. It is always widens a column. In a little more change, we can decrease that dismal sweet and center it now It looks great and the next trip lecture will work on for marching Dating remembers. 29. How To Format Dates And Numbers: in Exhale. The standard display off dates his as we see in column E Thought is summons day and year for just here. No leading zeros for the menacing or the day we can form us is in a number off wise. I don't There is a kid for Martine Way and see. Ah, drop down Ah, this here we can't tries along and short day for month this rises and double click to expand. So if you are not okay with this there is more for multi ways. In the four months says press control one to access a kick. And here we have ah more auction A lot off option here you can select one would like to appear Cancel Lexus one and no, we see the change. We can also customize this can go toe again former sales and he we have custom You can customize away like you toe up here Forssell It rises Odd to why here sees a change A year ? No, we have customized in the same honor. You can customize a time whistle the shorts four months. Here is the general stuff. We can't like thoughts, but we have ah or more tracing that form our cells Control plus one talks. Is that and we have here? Yeah, a choice. You can select one off it and you can see the change we can only saw Customize. Ah, this time here is a custom. My No, We came access one feet into Customized the way we like it. Legal k Finally. So the next picture who work on conditional formatting methods. 30. How To Use Conditional Formatting: consciousness for more Thing is grateful for highlighting data, making it sound out we can find it here in there. Home tough. There are various futures and option available, its margins at these salaries to stand out in a different way. So go to a country for marching and tries the first Ah, one highlights sales roads here he'll have money traced. Let's tried first. Ah, one Greater Tucson. So exhale automatic, Carney said. Just's but King Ah, say it. Our import here, content to cover highlight Also goto system here and select what even color you want. They select It's yellow one and click OK, this clean this room to try as if. Okay, now goto here and, uh, for conscience formatting and clear roles from the selected. The six now can try the color skill it high lattice highlights from small to large or vice versa. This got so good. This clears ah rule for one more time to try other conditional formatting. Maybe we can try that. I can sit here, it's clear and go to I can say it's We have, uh, many icons still select from this tries e storm. And here it is. If we need to modify any off the rules can go toe the conscience for marketing and manage roles. Here there is. Eat it rose So we can modify any perimeter here and click OK The same honor. You can explore the other conditional for month thing techniques. There are a lot and can try and 31. How To Create And Use Tables: tables are a common feature off Microsoft Excel. We can use, um, either to analyze or present data in systematic ways. We have this sample data. It is a small top, but we can use it to explore tables. Hey, is the insert tub Ah ah We can insert tables or we can use a shortcut. Control plus t t for tell Click one off the sales. Ah, the table here. And um Ah, we can click this table so extensive Just a syringe can modify or use the suggested range. We automatically transfer to design top click OK, and here it here we have a design top for the till can modify the table in many ways, here can change the cutter off the table. Let's some selects this one and ah, here We have also bonded draws and bonded columns attend Choose wana fits If you want to make change exit understand this What we're going to do is a table Let's say we need to increase the salary by $2000. This inserts that is called a six hitter New salary, new salary they centers of former are here the new formula the base salary plus 202,000. When pressing, enter exit automatically entered, the others can use UH, auto. Ah, this future or cold slicer. Hey slicer. First, let's some insert a new now column. A department. Let's call these department now let's insert a slicer. Select this department. Now they can resize this, and we can select either Tanna lays or Tito enduring marketing cells. Whatever wants, there are a little future off the table to explore. 32. How To Insert Shapes And Others Visual Elements: This is a flat work shit. And we can at many visual elements here, we cannot camp on your logo cannot pictures and canned ships. And as it most off this features begin on the insert off here. We can't insert pictures. Ah, we can get from this illustration looking insert either from our computer or from online. Yeah, they such our local click in Served here like in 30 in search logo and leg and insert. You can just, ah size into insert new role This may be enough. It's insert new throw here was to a wedding and their all too accommodates Ah, local little bits Don't no, it's fits. That's great. This march is just one for hater can also insert ships from, uh, these chefs store here. There are turn off ships to select from help low girls Kishan and basics shapes, rectangles and anything you want can, uh, in disease hexagon. We can press 52 now. Make it perfect on and we can, uh, that just sees also. Now it's insert some take us after reciting feet was a hater place in serves. Um, maybe I say elefant exhales tough solar data. This four is an example. Data can modify the excitement a bit can change a cutter. You can go to four months and change the background color. Maybe these green and puts a hater. Make some adjustment. We can increase that text sighs now this looks good. The other is we can insert also smart object Here There is a smart object. There are more zan 200 smart objects. We have a rich ma Trix Promet There is money. Let's insert one. Ah, maybe this process gear so, uh, very size resize and we can't eat pizza ticks They say Okay, cause this one fears one phase one and yes, there may be his to And, uh, certain on these three can change the color from hair. Let's makes see Swan. It looks beautiful. We can also change a background. You can see the program good for months and you can't make this'll light. Hello. So we can easily add visuals. Toe are boring data 33. How to Insert and Delete Columns and Rows: often times we need to change their listeners off work. Shit. Maybe we want to insert a new column or a new role. Sometimes you may need to treat or column or a row. Okay, Linda, those ace quickly and easily. First open up exploring worksheets in Italy. I was fine. Nah. So if you need to insert any decorum, for example, here, with me Yeah. Ah, I need this stuff. I d here. So we can simply insert is that? Yeah. The home tub there. Insert. She's calm here. So the tick ists Ah, maybe using the right click right? Click and insert. Can he lights and insert the place we want to insert? Charge it. Here is the stuff I d so excel moments we want to start a column. The same mourner we can insert Rose from the home tab Peiser or we came inserts Ah, by simply, um, making right click here and insert here. So we get these new rope. Here's a little problem. However, Why inserting the whole row? We have distorted the data. Here is the right. How can you insert rogues out? Disrupting this data you can This is simply select the sales and try it. Click a select seal and right click and insert. No, we can't. Shifts is down. So now the death safe. And when we insert our new rope so you can delete roles and columns, not surely you can select the rows and columns. Want to drill it? Right? Click on Zen lit Kommandant. 34. How To Hide and Unhide Rows And Columns: when we work, who is certainly Sof data. There may be times, so we don't want to see part off the data very often. We don't want to get rid off its, but we simply want toe hide it. It was a moment if you want to hide. Here, for example, is a Social Security Numbers says it is more personal or omitted from printing. We can hide it. Select the column first. You want white and right click and make eat height. Select right click. He's a height now when hide it now we don't see. Yeah, The Social Security column here is a devil line to indicate there is a hidden column here. If we need to do to on High Dates is simple. To do that select surrounding column here and right click, and we'll make it on height. Nice buck, or we can simply double clicks Devil line here, and it will be also on height, the same honor. We can't hide Ross. Also, if more gun this employee Morgan, one or less organization and hide him from the least can simply select in right click and make it height, sect and height. So again, if another time we need to see all our privet stuff can simply double click these Ah, definite line here Or we can go to right click and click on height So click sits great or right click So by selecting surrounding make it on height So now listen Please return it. So why didn't hunt hide? Feature are a great way to present our data. 35. How to Move, Copy and Insert Data: most off the time would use. Ah, this rear preference co. P move and inserts this begin for coping data Oppressions can use a normal oppression against the Knicks that data and press control, plus, uh, see to cope e and then controls plus v toe paste our data. There is a faster way to do that. You can select that data and drug eat and press control key. Why Drug Inc now drag and press the control key. Look as small plus sign up ring to indicate that we want copy that data can Really is it in the place we want to it toe up here. Now it's copied the same honor. We can move roles and call them as will. For example, let's move this phone members before the Social Security numbers begins and x A column and pressed a shift key and put the place we want. Toe leave became a insert detail a little. If we need to move, went insert here. Water white above the Robert boats begin Simply select and drug and press the shift key as we move and we can put their above. Robert Nice above the robber post became searched a simple way we can move in. Copy. Work this also right. Click the top and you will get a copy or move Command Here. Here came. Move. Oh, creates a copy too. Ah, this workshop works. You guys are or into new orchids. 36. How to Find and Replace Data: Sometimes we need to look when find data in a large priest. Or we may need to replace data with other imports. This case finding replace feature off Excel will be a great help. They want to find a particular employee name. For example, this list we can go toe finding press. Ah, uh, hometown here and find Waas. Saiz firstly, select circle. Um, and, uh, we keen types the name off the employees. You want this Find Robert find all. So accept. Finds one employee and lift list, and it highlights this old soul. Is that data? So it can close if you get what we need and we can find in replaced data. Also, we have fear employee status. This imagines that we want to change or contract contract employees to part time. Employees can go toe finding, replace and, uh ah, here a place this releases. Let's find contract employees the list. I want to replace Wee's a part time. Now, replace old. So you get at least three employees. That starts. So we have replaced. All is a Serie employees studies. That was a contract. A plea. Are changes toe part time studies we can use for muddying. Also 50 Neat to change color A different color can does. That's also the option they selects Ists yellow, um, cutter and find ups less change off criteria This ah, find a part time this time and change ways. Uh, contracts, places contract. So we have six employees. Chain cold starts. It's ah, color yellow. 37. Exploring Page Layout Methods: we never We need toe print work. Shit. It is better to get the paint preview first can get sir Print review from the Kick Access Toolbar here. Print preview into print. Or we can use a shortcut control plus f toe or can use a file and print comment. A fire and print I can see is a preview here with other savings and controls. Here it says it's to page. It's Skip to return and do some change here, turn and, uh three. Yeah, change. Here's Orrin Titian to portray its and see the preview again. It's good to find and print. Now the pigeon number become 16. It's reduced to secrecy whole Orc on setting up the pitch for good printings and next lectures with knowledge works. The others we can use. Orso's print titled Futures No here. So that's a title will appear on each page. It's a late roast repeats at the top. Select zero we can select, See strong and click. OK, now it will appear on every page. We can make some design and the layoff from the pig got so click descent Ah, we can go Is a design top here. We came change or title. This college held information and here reeking, uh, enter the page numbers. Page can see p jawan. Maybe we can call eight page and blank space and we can enter. He's Ah, Debt Anderson the time. So now it's begins. Great. Let's see our modification in the print preview. Scott. A print preview, fine and print gate. Now, kids, he's a modification. There is more print futures and will explore it and next lectures. 38. Page Setup and Printing Controls: we can get printing controls from the page layout. Here there is additional Delek books containing the controls we can click, sees a small box, and we have this Della books On the pitch tub, we have the or intuition. You can total either portray its or landscape or on Titian. Here there is a scaling now is this 100%. We can just to some percentage say it person. So it's a page size well, frank, and it will reduce the number off pigs. And also the other is feet, too. We can feed to the ah data to one pitch or number off page. Say you can't say tits. Eight pigs. These with this crease pitched toe eight page. There are also other controls here. The other is Ah Marchionne's. Well, this margin features also can you decrease increase to control the layout. There is a top right bottom left you can see always are changes from the print preview. Here, gather control is a header and footer section. We can customize a hitter and footer here. I can click this button. Let's try, uh, cost. Try to customize the food her and the left section. We can in terms of page number, maybe is immediate sections title off the page has in formation can states sons in the right section We can enter that dates in two time and we finish We can click OK, And also here we can close that Delic Books is a sheet top The shit Tuggle We can say its a print area roast print as we did it in the last picture and we can sit Ah asi azar years. It is a prince you can see It's a great lies on and off And also we can changes, as are features They are These are the most years print controls we should always see Arsene reasons I print preview Can it finish lights is 39. How to Preview Page Breaks: the page. Brick preview is another approach to get start printing. Look, it gets is here. Page break preview. We can click, Will seize a pitch. This page brakes this page. It was one in cities page 30 one. The document is cut and this will be part printing. Can make some adjustments here this goto page rehoused and increase the march int. This use is ah, no more nights became to 89. So let's use, uh, narrow merchants. We can also decrease the column, size its electoral and drug it a little bit. Two left and nice. Looks better now we came drug. Uh, also the page break to include toe include a za whole peach can drag it to write to the rights. Nice become one pitch. Let's see Now is a print preview to see our changes and mortification. Nice became to anyone Peace. It's greatly reduced. We can print the act fit. Ah, a prince selection or the entire book. Ganso's elects the pitch we want from No, the documents they say can print from page to page four as we wish to dio. But before printing, we have two makes a few 100% can use the slider to make 800% so we will work on page layout controls the next lectures. 40. How to Create Charts: concrete charters in exile in an embarrassed voice. First, open up the chartists exercised five and first war treat. We have the easiest data so called embassy trading. It is a global system data. Let's use it for creating charts is first highlights that detail We want to create the chart. This credit, it's elect this data, we can start by us. And if foreign made it plus a form can get a church immediately right on the same worksheets here, let's ah ah Decrease the size to see the data and not his main system is change it well. Got the chart tools designed top and four month a top quartile. The most underway created charges to go to insert top care This first select sir, data got insert and here we have record 100 charts is can't arise on it gives us a Callister charged or we may use our choice here. You can use, uh, provided dawn. So this is it. Same charts with the last one we created. There are miniature types here. We we'll explore in the next lectures. There is another way to create chance. It's delete Theodore int her lakes. No, the data and see it's a preview is no time off type, so you can try this. There's also another would create charters. They select the detail and there is a small books at the possum. And so just kick analysts is if you click AIDS, it will take us to charge us here. This click it and here is charts. We can see the preview off. They provided charts sharing our data grades. So in the next picture, look busy working on exploring the different chart types. 41. Exploring Chart Types: there are many chart types to select from that are going toe work on different data types. Let's so use our sample later to explore the different chart types, please, for some minutes, detail. Selects is some sample data, and we can press. Plus, if one for kick this will create a different chart, it is a clustered chart type. Most of the time it will be the best chart it shows, said Dita. Clearly, and what is going on here on the data? It's decided The king changer. Just chart types. However, we have the with chart. I'd better in here we can click, it's and, uh, we can change it to Stucked column Stocked his cluster Clemen, this is stacked column says there may be this swan if he's a street d one on uh, can. Trey sees. This may not be the best chart, but we can see how it looks scarce. Great. We can try also the part trot types go to a chart type and change the part graph. This is a different operation stocked on 100% tucked can just style. It's it's and we can try the line graph source. Well, let's go to chart types again, and she's a version. The selected this one with the markers. This maybe is a discharge type for these data because we have a time variation in our data . It shows how the seats are changing form. Time to time from January to June. Can try also the pie chart a part chart Ah can all do is a limited amount off data. Let's change that. Let's go to pie chart. Select the date signed. Go to part chart this. Use these three d pie chart. You have all this data, but it deals with only the national states data. So pie chart is not the best feet for this type off data escapes not. And there are also, as of charge types, let's go to explore here. Have area chart. Um excited charged, stuck chart surface charts, retort shirt with inspiration. He's, uh, he's one of his telegram was a parrot tow truck violation. The books and Whittle chart his water for chart and Compass charged. Here you can combine to charters in one place, so there are many options. Select from 42. How to Format Chart: for Martine Charges is a funny and easy experience. When we create it charters, everything's in place for a presentation. This highlights a detailed and create a new truck, but to in certainty will use a recommended charters this time recommended. We can use these Landgraf for this particular data is a commented one. It's size, it's a bit Here we have the chart title and categories Reza Time Variation We can it Dietze title Monarchy Oh, are we Can Pique is a title from the sale of France This peak from the sale Ah, Blake and equal sign. And so, like the title from the sale The City trading script We can change the layout Social here we have acutely out option There are a lot of often to select from, um I like the first choice here we can TTC oxy start, stifle and click end call It sells in millions of dollars Sales in millions off dollars can change the style off Is this chart? Also we have here Highlight can choose any one of peeling. Let's cuz swarm not say swung. Maybe no se swarm has changed the storm. No, this is better. Can change four months here there are a lot of shows select from we don't We can't elect any team this time. I can experiment any four month ng inputs. Let's go home and change occur And he are so cutters phoned these excess I can't exercise and we can't change again. This tire, this may be much better. The big Guy Days Excel has all the power to create more appealing charters, so to design easily. 43. Axes, Titles and Other Chart Elements: the various components off the chart. Our refer to US chart elements as we selected chart the outer area. New supporters is referred to US Chart Area, and the inner area is called a plots area. We have this frequent elements is to yeah, right side of the chart. The paint birth in the plus sign and the fun of the paintbrush will help us to select style as we did in the last picture. They select a lighter style at this time. Select the first stone. It looks, uh, let click to reveal it. The plus sign rivers hating shaft elements. The first element Easy access the primary horizontally in vertical axis. Yeah, we can switch on and off his Condron. He's the Axis titles. We can show the horizontal axis if you wish to do continue the chart titles. Swill. Ah, data levels. We can also positions point above below and we can't switch on and off since he's had a data table gains a digit and, uh, trendline everything king for marts that individual chart elements double click the elements that you wish to four months. And here there are ah burnt off ocean to consider changing can change, occur and charged elements end other criteria. So it's good here we have the access can deal easy access lemons A. Also now, um, other features so we can form out and deal charge elements in a number off ways. 44. Exploring 2016 New Charts: this lecture will explore new Chartres that are available in 2000. Exhales 2016. The first nutrient We're going to explorers a dream up in sunburst short bulls used to two parties to a whole When several columns off categories for the hierarchy. This click Cohen was a sale and try first a dream up. Go to insert and certain stream up this deal. It's a legend, since we donate it on and can't be size eight bit. So these shows are Give us a big picture to see our data as you see my fuck cheering hard a large share. This is true because the solid distribution is so high it's expected. The other is a semberas chart to give us a say more to put except in a different made it. It's ah, tries. That's the late first and go to insert and will insert the sun first shot. So these, uh, inner portion is the department, and auto one is the status He also won. Fracturing has a large portion was expected for Milady data, and the next charts is a history Graham Trust List this move on to the right to see the data his telegram shows the destruction off data Drop it into beans, It's insert starts Go to here and inserts his telegram. Let's resize chart to see there data We can modify the being weeds Deva click to results No , it's ah makes the bean weeds Maybe 10,000. Look on Ah change So we can also modify the other stuff. It was a style we wish to dio get a sense. So this someone wanted the party to chart the party toe shows the related portion off each factor to the totals we have here is regional sales data. Ah, this insert the Pareto chart won't moved to here His perimeter Let's resize it so and it shows us what is happening to the data compared to the other, the other is a books and whisker chart. Let's move on to rights to see, uh, that data here it shows this category in place. The for Asian was in this set off data. Ah, this inter sides have these safe state up off the different states. Okay, this is, uh, box and whisker. Listen, we can four months a detained make as a modification. Yes, well and of clay can uh, this shows the mean and, you know, points and other mortification fleeces. And the final from the last chart we're going to see is a waterfall chart. It shows a comet effect off a serious off positive friend. Negative values. Inserts are go to start. And here is a water for chart later. Its size it's and we'll have here negative value. It shows the flaw. Dita sort of evolved These, uh, Moses. So these are some off the new charters? 45. Freezing and Unfreezing Pans: When we work with large treater schooling to lift into down, we lose sight off the top row and the first column Most of the time. We want to see the huge Erian's of first columns exhale hard on Excellent future to funder . No cities type. We can freeze the top row first. Well can go too few and freeze ponds and freeze top role. Now the top prize. Fix it while with crow Eat too dumb and the same honor We can freeze the first corn this unfit, is it? If this first end frieze First column in Sir John No, we can't scroll to the rights and the first column is visible. There may be times to keep pause and with me are some column and rose to be easy. Vallas Well, countries's for appointing the selector to the place we want to freeze Now This ah points here two D two that keeps the three columns A B and C animals was a top row. Now this good tuf response And now first on freeze and freeze parts now can cheat cause us the result. The three columns are fix it, Score it, uh, to the left and the top. Roy's also fix it as we need it 46. How to Split Screen Horizontally and Vertically: we are looking at says he's lists, and sometimes it is advantages. To be able to see different parts off list at the same time, in one way, is to use the speech. Future off. Exhale begins please the screen horizontally in first. Carly if he wants please to shayt a lot of this screen, top and bottom, we will use horizontal split. This test starts position selector around the mid point and go to feel and split. Now we can compare a data or check names or do as there's stiff look. The bottom is going, and top one is also screwing as well, the same manner we can split the screen first. Carly. Let's first deal. It's a horizontal horizontal split. I can double click to do. It starts first point selector around here and go to view and split. Now we have to screen can move this pleats lying and compare or do as it's tough in the screens. You can also splits the screen horizontally and vertically at the same time, really, and we don't need it and it will make things complex. But you can do if we need it. We can point a Cem Ito screen and go to split. Now we have four windows are four splits and you can't compare or to other steps. If Senate is there, the future is at our hunt. 47. How to Collapse and Expand Data Views: we have a huge work for it here. If you want to present this information and we don't want to bother our audience visa details, we can collapse. Thes and sure, only some off the data like you can Sure, only the quarters to do that. Ah, point selector One off sale. Ah, and we'll go to Dita and grope. And here we have auto outline. Now that data is ready to be collapses too short only what we need to in display or sure off a click there will see on Lee the quarter's data here. Also, if you click to you can collapse the detail off the self and focus on the results. If I click, one will end up ah only the net profit we can return block. Why? Simply clicking the plus sign or the cool last numbers can also clears outline by going Teoh I'm group and clear the group. So is this way can use lumps message to focus on our data 48. How to display mulitple workbooks and worksheets: We will be working on how we can display different workbooks in a single window and will be able to display also a different worksheets off a singer workbook. We have no three workbooks opened, and we can't display each in two simple medics. Either we can use a control plus top keys, or we can select one from the views and switch windows because elect one of the works it here. And if we toe open, this three workbook together can go to view and arrange Windows this God to view. And here arrange all we have a list off to select from It's tries it tired and click OK now we can see each workbook independently and work what even went to do can compare or co pee in peace data from one workbook to the other. Let's delete so that this to workbook to try the next one. Do we need to display and work on different Sheet's off the same workbook we need. First open a workbook in New Window. This creates that Let's go to hell and here we have a new window scripts this do one more time new window, and now we can go to the view and arrange old options. As this time we can check windows off active for book? No, we have Ah, different. Wash it off. The same workbook. No, to Kieser. You have this shit and have the next shit and the search she does. 49. How to rename, insert and delete sheets: thesis. Three. Oppressions, rename, insert and let are super easy to do, but they are very sensual works with monument tools. We have this short war book and things sheets on it. If you want toe rename a work, it's simply double click on it. And start typing is a new name. Its three names that other charts here Double click and call it new charts so we can simply change it. The name. If you want to insert new sheets, for example, before for month Chartres Street here. Yeah, simply right. Click on it and insert so between insert and click. OK, we can also use a shortcut that his shift plus F 11 so it's also creates a new work shifts . Look when a purse shift plus F 11 hip hop in new cheese he wants the new sheets to be after can simply used two plus sign. He is the right council. Delete sheets. If you feel we no longer need it. Two DUIs Odds. Simply right. Click and decollete extend warrants. I ever actually is permanent. We cannot undo it If you did, and mystic so should double check before week. Leak. Delete 50. How to move, copy and group sheets: moving orchids don't need any command. We can Greek in drug to the place we want to insert become place is here and we can move this shit to this place Also, copying sheets is also very 100 there is See, we can't right click to the sheet. We want toe have a copy fits And here we have the move or copy command the copies a sheet we need to click sis checks books and we can copy to new Order book or to existing where work work Here this creates two new brand workbook. Here we have the new workbook with its content and form our thing from the original within group shits if we want to do it all four months together, say let's group these three sheets together from the charts, charting them and send for much charts together to do thoughts. Select worn and hold 50 Key and post served Shoot Now press shift and quick the search it. Now we have crop it, he says. Group. It's increasing. Roi hates own one off the sheets. To see a zit effect at least increase. Select all first and increase the rowhite and check the other sheets. So it's Appier's on the group of she's and we can see the other sheet is also he has a shit is it is not affected. If you do not want a grouping anymore, we can right click one of the group sheets and click I'm group can get a right quick and on group shits now, as a group has get. 51. How to use formulas to link workbooks and worksheets: we have here three sheets each contents different quarter of data course along quarter to artistry in They're a vandal. We want art the total off the quarters in different shit. Here we have the totals. Let's find the total off the quarter from the shades started for Google and type. The former here is recall, too. And go to quarter on that data. Look the sheet That's history plus and go to 1/4 to select said Ah raven for cool and finally go to ah quarter three sheets and select the quarter driven and pressing tour. Now we have thes total. You know we have to. This end we came truck. It's looks a former. He's so based Lingzi but would have ah, the totals. Results type ink He's from war condos is in another makes. It is equal to start with. Is he called to and some, like some function send to select the first quarter. Press Inter Sorry, press shift and select is the last quarter. Now presenter. We have the same result and the formula will be a bit shorter. Zahn the previous one. How what's the data are in different workbooks and want to some thumb in a new war. Coke. Here we have a new workbook and we want Ah, the bring and some This 3/4 riven I'm gonna do it. Start by, is equal to a call to and go to this shit and select for Koga and go toe quarter to select the next one and go to the last quarter and select Ravens. Now we have these total so even toe formalize a bit Lingzi But what gets the total from the different shits? A script? 52. How to locate and maintain links: we have to work. Book opened here. Let's try toe pull the total off Goggle riven from workbook to toe workbook One to do thought simply the types of formula is equal to and will go to work book tool and will select a total off the gun raving the sale reference Goto workbook toe and select Be three This is this wears off Google Raven from the three fit. Now we have connected the tour book by formula. How do we know we have a link in this workbook Can go to data good to detail And who see the links Here, here We have idiot links. If this is not active, that means we don't have a link in this workbook, for example food elites formula see links Will this up here? This undo that option and ah to which a shit is blinkered We can simply click end it did the links Here we have the she's connected It's time we have a new updates own that other sheets he sealed will also be updated Can open source data de theater bricks link You don't need it anymore and And checks a start off links so this will work as a link 53. How to Use Count IF, Sum IF and Average IF Functions: in this lecture will see the county formally Frankish in supplication count if some if and other average if foundations. Let's start with the county of functions. We have a list off category, which is Ah, it's a surprise and riven generated. We want to know how many items are available in each department. Calculate that will use account if thank Asians. So this, uh, Inter that useful for Mora here the items part is equal to count. If select the county functions, syringe will be column be That is a department and the criteria Yeah, coma. And the criteria will be, Yeah, the kitchen itself. The name off the item that is a kitchen nand close a press cause breast and inter Now we have ah, the result. You can double click to cope it down or drug it down. You know, we have the total item each department. What if we want to know the total riven generated for each item's and this case will use the summit functions. So listen, turns out Formoso here the venue part ah is equal toe some eighth. Some if and the range will be the same with the profess that is a department and Goma The criteria. I will be the kitchen. Send him off category and the some orange will be the river column that it column E select company and, uh, double click. Now we have this results. We can drug it down toe play for the other category as well. Now we have the total riven for each item in the departments bus, but most least we can see the average function. Our goal is to find the average price for each item category. So to do that, who can? Intra Zia Verity, Frank Asians here was equal to our overage Eve and the range will be the same. That is a department column and criteria will be able to the same man. Jap overtrained you'll be now the sea call. This is a price can close the Chris and we can drug toe a plate down. Now kids thes So these are the powerful tourist. Any easy to use as well 54. Using The Vlookup Function for Exact match table data: his last lecture. We have used how we can use a veil cough function with up works, mate. March upwards. This lecture will be working on the exact much type. We have two tills in this wash it. We have the Atlanta City temperature data and so thes some local table with precipitation in type off weather it brings. We're going to find the weather type in Atlanta City by comparing these pre defined weather category. Will use a veil cough function to find seats. Place in terrorist former here in H two is equal to veil a cup, so local value would be the precipitation amount and colon G and, uh, select that sail. That's a column and coma. The table Ari will be. Is this still from J to K? Selects a two column and, uh, the column number will be too starting from left to right. It will be, too. That's just yeah, it is K sits coma, and the march type would be the exact march self selected and close. The press here would get the result to cope it down. We have to make the table Ari absorbent reference, so don't click to get at him formula the click and will, uh, would be able to, ah, make it absurd. Three fronts press it for to make it absorbed it. Now, who make the Saudis Now we can drugs to down. So, no, we have all these data exact much from the precipitation table. 55. Using The Vlookup Function for Approximate table data: the vehicle function is a very important and powerful function in Excel. There are two major kinds off Villa. Copy this one for up Rik Smits. The other is for exact March type. They started with the approximate approach we have here. It discount great double. Please don't The subtitles here in column de and all approximates that dis countries comparing with the look up table. Yeah, they're here in D and it's called the vehicle function was pick a data from this column. Compare its value visa, a Z amount and up away the approximates discount from column J. It's Inter just for more. Starting these they look up is equal toe here because to fail a cup and the look up table is column c the subtitles. But it's Saito and the table. Our raise. Ah, this table that is a discount table. It's so selected. And the column index would be to starting from left to right it is. Call them to that's number two. So now ah, we can select the mice type coma. The marked much type will be approximate much closer. Brace and enter. Now we have this amount. If you want to copy the formula to down. However, we need to make sure. So low cup table is absolutely it is reference should be obsolete. So go and select a reference. Ah, for us DoubleClick toe access the former and press If for to make it absolutes Now it is fix it. No can double click to compete down. So now I can compare the discount No. Is that this country and the sub toted So this is good, for example, off using the look of function for an approximate much The next lecture will explore how Villa Cup will be used in exact much. 56. How to Protect Worksheets: we have fear on employee data. We don't want anybody to change this data even we don't want. Ah is this trunk is visible to anybody except for the managers. Top places Effect will use work. Shit protection fishes This first on look all the locked sales to do socks for slicked and right click full finds a four month sale. Here we have, the protection is looked. Just make his own look. Now let's select sale. We want to look. We want column B in Fulham. See first, let this some on hide the column. Dean's It's around its height, height and now they go to for months sale and walk it again. Now Good to reveal and protect sheets. The review. Home top. He's a review now. Protect Sheets says. Protect. She's for locked sets. We can put our password and one more time to double shake our passport. Now we have the effect. It's great, great docked. So if we end to make any change, we can't. He then Ah, we can't on hide social. He came on height. It's so this, uh, help us to protect our fit became on protect shit by going again to review and so on protects. We must not forget our personal driver because you forget lost are fit 57. How to Protect Workbooks: if we weren't only certain people open this file. We can protects a workbook as a whole. We will put a parcel to protection, owns a fight to do that, go to fight and see loss, say vase on the PC. And here we have an option. General option. Passel toe, open the file or pass off. Modify the fire. Either we can use one of faith or we kind of play at bulls option. Let's try the first option. The center or parceled here is it. Remember passers two digits and interest? A possible again. Now let's close the fire first. Ah, opened fire again. Close. If I and uh will open the file again, control plus W to closing Contra plus open again fire. Now it's requires the puzzled can intercept past or toe opens fire the same procedure. We can try the second option Goto file again and save us and on to us option. We have a general election here. Knowledge to use a second option possible to modify. It's possible that once again and saves a five in the yesterday place. Now it's a close foil Landry Open control plastic viewed close and toe turn Contour plus o . Now it's ask Is it possible or to open it and read? Only you can enter the past for Since we have a we can, uh, open read only now it opens. 58. How to Assign Passwords Wokbook: the last lecture we have seen. How toe protect the content off our workbook in this lecture will see how to protect the structure off our work. Cook, it's threatening. Try it In auction we have here Protect workbook Mont feel workbook Protect Come on, click it and will enter Possible to it. Click. It's required Passport We can reenter possible again. Looks a fix here. Right Click in. We can't insert, delete hide or on height or move the shit we can Unprotected a workbook by leading the password Deletes apart sort. Now we on protects a workbook. We have restored the other option also. 59. How to sort Data in Excel: sorting is a most comment ALS for data management in general. We have the this list on this portrait, and the list is more in particular. Order right now can easily source this leased by employees, name or department or contract status, or by higher debt, or possibly by their salary amount to apply a simple sort like we need to sort by in plane him far be it curly. Click on the comb and go to deter and sort. Is that a two seat? Or is it to a can do that? Try the A to Z it. Now we have rangers a detailed by employees can ah use also And the department, if you need, can sort by their salary amount. No, this time King used also other mated. You can start Ah, the list by multiple off criteria to do that go to detained sort Now we have the city Look books. Call him in sort value Lastly order Hey there from largest smaller store from, um it is a reverse order. This sort by department makes a order eight z and at another labor now sorted by employees name. And, uh, the order will be eight z again and at another level this time it will be a status Shorten it to see again. And finally, one more order labor. This time we'll sort by employees salary and the order will be from largest to smallest. Now click OK and sees effect how we have sorted by department and then by cleaning than by status and finally by their celery amount. So there are a lot off voice two sort our data in many, uh, water like we did these. 60. How to Insert Subtotals in A Sorted Data: If you want to have the sub totals for each department, we can model insert a formula to get the subtitles for each department. However, we have a dynamic tool to get the safe totals. ATM article we have here soft totaled future. Is that detail Management's main. We must sort the data eight off time for if we expect there coherent results. You have sorted the data in the last lecture here list for Alexa department. That means we never is the department's changes. That's a total would be also change it and ah, we can sit next to function some, ah, others para meters. And here you can select It was a year salary and click OK, and see the result. Now we have a sub totals for each department. We can go. Oh, one step shorter to see Onley the department And here we have Condense it. Ah, data. If you want a copy thesis data to new sheets, you can go toe home for select to select Go to homeland, go to special and select visible sales on Lee. Now I can go into pissed Ah, first scorpy that data we didn't compete and robot and pissed. Now we have his condense it form off our date data, some scripts. Ah, we can And group. If it don't it is that go to subtitle and remove all this will remove all the subtitles from the list. 61. How to Use Filters: sometime. We do want to see some rose and height. Other rose Reeses filter Future would be 100 tool for us First, make sure or details months MPT, Ross and MPT column Simply click on one off this hill and go to deter in filter and we see filter iroh as the top off the call they see for for example, the department we have here on alphabetical list off the departments least says electoral off the department. First on, select all and then select. Ah, like manufacturing one is enough manufacturing. Ah, it say's four out of 15 3 cards. You can clear the filter. Ah, if you want to see all the data again for Texas, we have additional option. Uh, here. Um, how can I don't? The finance Our status can filter what we want. They saw select all and we're clear for takers. We have additional ocean here. We have equals not we called begin res, stone fitter and others and four numbers. Also, we have a lot of freedom. It option available. We came Ah, do maybe two on top 10 lists. Try the bt own can get fined A data between 15 off 70,000 and, um, unpleasant 80,000. Now, this should be 7000 So the salary p tune Z strange will be filtered. Great. 62. How to Split Data in to Multiple Columns: Hey, do we have employees name? And we want to sort this data by the last name off our employees, but your first and last name all in the same. Call them. How can you support it? East's and sorts of data by only the last name. Here we have a future called Ah, Texas! Two columns Here it gets the data and take us to columns. First list concert. One more column here, just before the department and ah, now a Select a calm and go to data and click text toe column Here we have a wizard to do on the job. The default choices deal omitted. Characterise chest comas or Thomas support each other. Ah, are fix it. Weeds. Park it. Let's use a dilemma deduction and, ah, this click next. Now a list of options are available for the deal Emitters. The semicolon top coma, spacing others for our kids and names are supported by space. So select space and look. Ah, he's effect and then finish the result. Now we have supported the first in the last name in Tokyo limbs great and call. Ah, these last name and she's first name. And now we can sort of guitar by the last name A to Z and have sorted 63. How Use Flash Fill for Splitting and Combining Data: his last lecture. There are splits. Ah, Data in column in by take US toe column Features This lecture will do the same job by flat fee flat Phillies in new future and exhale, starting from 2013 version off. Microsoft Excel This first combined this, sir name spi Flash feel And to do that first inserts a new column in here. Ah, understand, son. We're start typing the name either the least order or in the reverse. Order that and to go to war data and fly fifth to do it. This ah, Charles and me kill. And this girl too. No data here. We have flash feel, and, uh, you can see that description, Tom article feel in values Click will have cease. We have complained in a simple made it scripts can use fly field whose police detailed. So hey, such, uh, and neither column Still, these are first and, uh, making split sees Ah, name into velocity made first name. Yeah, it's uncertainties column Here. I'm here List. Add more. Yeah. James for Porter. Try to Yeah, start from the last name. That's Fumiko and good to data and flash feed. Now we split the last name. And in the similar we can do for first name they started Charles End, Good toe, uh, data. And now we have done. It's so you can delete thoughts and great and call cheese. 1st may have displeased the details successfully script. 64. How to Remove Duplicate Data: if we encounter to play, kiss in a za data and we want remove them. We have an excellent feature called remove Duplicates. This list. We have some duplicates like here we have the same name Terry and assembly to us, and we want to remove it on Click anyone off the sale and we'll go to remove duplicates here. So you we can select all the columns removed blankets here because the leg all the comes or some off it and click OK, now it says two duplicates. Values found into removed is a very important features, especially our digitize huge and hard to find duplicates values. Monolith. So now we have removed all the duplicates in the data. 65. Using Validation Tools to The Best: Sometimes we want to validate data before go zones or shit. Here in this work shit we have on employee I t. We want to limit the number off characters I d will contain to do that selects a column and and good to detail here in the States off addition here we have sitting input Ah, message and on error Others for the validation criteria The select text things and select exact uh and he called her in The number of characters will be four. Its state is four and ah, input message The title off the miss It can be reminder Call it a reminder and the import music can be must be for character Must be for cut up to and click OK, legal candle sees effect. Now we have this message If you into five character will get a nearer as cocked so it's or king means he would don't want to top every time we enter data you can use also and a validation meted to elect it as a list did results Celexa Colon here we want to a plaint. Ah, well, go to ah Dita. Call these maybe contract type and wait If it's and go to a detail. Select first a column and you go to its Ah, here and sating parts electoral list. Hand for the source. Select Ah, this he from C one to C three and now click. OK, now we can select from the drop down list. That's great. Even we can move the lace to other sheet. Oh, can't cope E it's and ah, move, cut and copy to as a sheet and you can check. It's still walking and we can't even hide it. We need to height and it is walking, so data validation is a great great tool for us. 66. How to Use Goal Seek: the goal. Seek Help us to find the right input for the violently we want. Well, don't Twerk exercise Here we have, sir, KMT See Nero. We want to borrow money. And the contraception is Aren't 50.5 enter straight. We 60 men's is pale. If you want to pour over 4th 4000 are mentally payment would be 840 if we can't have for paying 840 every minutes and we want toe increase the term off payments the state. How can we just it this case goal seat will help Oz to do the job. Teoh too. Data and what Yvonne says and called sick. If you once the payment for it to change. And the volume two 500 for example. If studies capacity and since we can't change the interest, determine spies at Camp Pony so is it changing seal would be J five. That is a term. So they saw process. And now the term off payment. We'll set to 100 safety resistor can for 500 door every minutes. Let's try another example Here we have list off salary in the camp pony became profitable and increase their salary boy, this person to point to one person some of the total salaries around 57. Miller, as you can see. But the campaign he has a capacity to increase after 55 million. How can we figure it out? This increases red This first, find some off the new fowleri. That's something will pay our own 53 million in 251,000 and its use the cultic goto again data and click goal seek now so looks ah total And it took this volume and here 55 million and changing. Sil, will Pisa, right? Put the rights and see the result So some management can increase the cider even off to 5.5%. 67. How to Use Scenario Manager: we have here a one year since protection here is the other mentions, saves amount Our depends on the saints in the general, First Man said. Sometimes we and want to know the maximum ah production and the minimum projection from the first men's became minority. It's creating other version off the shits. But now scenario one. Roger gratis for us. Different group for value or See Nero and we can switch the BA tune some between. The result. Off Seen arrows. Now let's create 30 for thesis sales projections. Let's create standard High Seal and Lo seal the scenario to defy this three. See Nero go to detail. What if I abilities and click scenario monitor? Let's give it a name. This called Eyes First Senior Standard and, uh, changing seal would be, ah, sales king excels would be to sell shipping, and, uh, goods can cook the coma and Celexa goods and click okay and click OK also. And this creates another. See Nero for optimised success. It's Call it Ah Must maybe, you know, high sale and the change changing seal. Ah, the ah 1st 1 would be I came cities 800,000. This is optimistic. And so it's making 800,000. The shipping It will be also 140,000 140,006 SMIC it want for Sarah 30 Is the coast off, girls? Ah, is do Chris too. 700,000 and click. Ok. Ah, this creates a searched See Nero the option That is a pessimist success option. This college say Los El and changes sits to be Hm? This if the sale should be less, let's make it 500,000 and the shifting to be 110,000 and ah, the costs off good became increase and said to 980,000 and click OK, now we have three. See Nero and, uh, yeah, we can show and one by on observes a change on the profit. Ah, so you see you can't shows a change can talk the created senior who's and the kick access to more. He's close. He's to do that, go to the key access to more And here more Commons to Lexa old commands option and to hear be off, down, down the, uh see Nero art it's and click Ok, now we can joke now. No help! These three Sendero managers 68. How To Use Solver: the last lecture house in these off gold seek where we are old changed The result off former but changing one from Waas components ovaries more sophisticated Ashinoff that God seek this whole Trid. We have a total here in a total dependence on what's up ring in a lot off that the other cells and components How much is that? Profit would be higher se 260,000. How can we change optimized imports? Still there Help Waas to guess Start in a simple would. So let's force to beat us over odd ends To do that, go to fine. And here option he, uh, audience. You have the sober click. Good too. No, Have it in the main Now Check sees Jake Books and Ping. And do we? We open the coal seek here. We'll have these Della books. So our objective is to be increased? No, this total So Mitt marks mean its volume We hear 20 60,000 sealed shipping in was a goods so they had to be keen. Gets optimized volume. Yeah, seal and a good We want these bone saw There is no that much significant. Leave it for no Because Kip It's Girl two Gela books again subject to constraints. They sought some constraints. Here it's birth, constant to be cell, the make heat. Yeah, the sun 140,000 and at the corner strains. This makes this shipping. Yeah, doing 7000. And finally, one constraints. The food should be. It is means greater son like thin through Tom's. No so much, much blobs on these charts. I think so. So about two automatic just It's so late, he said. Riedel's So keep this soldier who wants it. Oh, we can start Who's perfect? So Okay, no, you see the results. 69. How To a Data Table: in this orchard. We have a serious off interest rates across the wrong went different payment option down to colon. And this he's ah, payment period. It is 16 minutes. So how can we produce in Ma ticks off comparison table? So to compare is payment with it is interest rates this first get a master from raw can put some muscle from raw Here in B three, we'll use a payment. Ah, function pmt function. So this poster from raw here, equal toe pimp T and, um, it's a requires three arguments. 1st 1 ease. They're right here. That is 3% is eerie rate. So we'll divide it by tilled and it's coma, then under off carriage is 60 minutes a one. It's like a one and coma. And finally the amount off borrowing that is 2004 this period Closer present Inter. It's a negative. Resolved, actually is expected. Yes, for the sake off feeling this changes to a positive for this body's minus. So this you someone's of payment amount. Now let's creates it. Dates are table is known. Is this mustard from wrong? This highlights the sales Enough bombs We won't to generate some more tricks, including their role and ah column. So go to date data. And what if analysis and data till we have no raw imports sells? It should be 3%. And the column in input will be to talent as B two. So click OK, now we have a Matics off comparison tables. Just we can four months this numbers to be more readable, right click for months, save and number. We don't want to show the painting so we can even formative even better this change because of her. And OK, no saw century to we need ah, kids select any color you want. So this is what we have Journal editor Did Ma Trix based on the Muster former up? 70. How to Create PivotTables: people Table is a powerful Microsoft Excel feature. Here we have a list off data. Now we can analyze this data. Buy filter sorts sub total grope. Indie azar may touch the two, analyzes data from a different perspective. And to get very good insight people trouble would be hunde and easy method. So toe inserted people table Select one off the sale and go to insert mean. And here we have people tipple and gets us Delek books. The first option will help pass to determine the range of the detail. We can use entire shit or part off it, and it's a conduction with her pastor determined where we want to put the people trouble. Their new shit or the existing shit can click after we select and, uh, the left. We have a placeholder for the people and on the right. We have the people table Fielder's here we have the agent. Perak State cast simmers in this example, and, uh, this will help us to filter. And here individual Ross and individual columns here the values or as ah NIU medics are analyzed. Letty is in action. Let's struck, sir, Agent to Ross Field. So the agents are listed. And let's also drugs that revenue to Zoe values and some of revenues automatically up here here so we can sort of resolved if you wish to do so. They started from small to not, and so we came for much. As a result, no. For months. Let's make this song green at least different shirts as a result. Byproduct. Drug. Two columns Here comes the columns Havana and the agent. The Ross It's for market can design from that results from the scientific can make it also bond it, or they're also and columns well, explores. Ah, morning, next lecture. 71. Manipulating PivotTable Data: in the last lecture we have created sees people tables on our agent Caesar. Odetta This exploring more Let's find other breakouts and another insight on the customer. Mm part to do that. He supports the product Yeah, and the plots of product and inserts a customer in two columns. Or can and checks a product and checks. Kahlan Bizarre kissin Well, so you can't simply drug took columns. Can't find other breakout. Also, we don't want to see agent names now here. And it We want toe plots, Agent, and we want to see the regions so we can't simply on chicks. Ah, agents. Or we can truck this states to rose. Now we have sits by region and a December It was a Sam table can t vs order. Let's move the region's till columns and it's more for the States to columns part and Kissinger to zero can see Plea drug and job. So now it's much. Peter looks good. You are update our source data and we want the update Our play on the people table. We should use the refresh mated. We can get the refresh mentored. Yeah, on our lives top. Ah, let's try Dietze Saad sent go to the analyzed top and can refresh tough Dietze table. The next lecture will see how we can use people tables for grouping data and time. 72. Date and Time Grouping: to see the detained time groping. Let's make some changes on R P vote tales. Let's Ross P Dates and states be regions we have group is a detail always that deeds. Ah, but it is not compact in the Kansai eyes. It will be great. Maybe we can ah ableto groups this data by men's or here or quarter to do that right Click on one off their debts and who gets the group command there Just clicks Group here list off option from second to years. Let's select for now. Nunes Quarter and year sender. See the result. Click OK now a smart beta. There's very compact and con size. This move. Ah, that did two filters Move now, as he said, Roll ah, date to filters and look, Now it's become much better. I can see is a year with 1/4. We can also reverse Yeah, disorder. This ah reverse quarters is a year now. See the first quarter with the difference no years. His council also be good for comparing dates on can go to design tar, bend on sub totems or in the lace up photos and on and off the grand total for new total. There are many ways to analyze that data can experiment with every possibility 73. Extended Grouping Analysis: in the last lecture. We have group it detail by date. In our people, tales can also see some other ways to grow up our data as well. Say, for example, we want to grope these two regions to warn Norsigian and this'll Swon two sows region to do sets select a tour edging No assistant knows waste and right click and then grow up. Now we have groped and change the name to notes. There's change The name to notes is the same honor. Select this to region Yeah, some assistance Southwest and right click and group and changed the name to sauce on Lee It Selves. Now we can clubs to the compact for off are groping And we can also play with other analyses on Do we came Yeah group also rose if we need to If you want to can do that can so we can make our own groping with any data with this made it this far powerful and effective 74. How to Use Slicers: slicer Sartre introduced in Excel 2010 and it is a powerful tool to use lesson for machine feature. Wally Let's see how it works to insert the slighter go to analyze stuff. And here we have insert a slicer, you can select the feared or fielder's well you want to include. He's a slicer in select agent products stating customer and click. OK, now we have the four slicers we can reach size Ah can arrange. Ah, some then ah, that we we want to. And no, we can also give different colors just two different shit each and even we can't wrap into two columns for managing the space we have. This gives is too and put it here. And my size is a bit and change cutter the state to green sees maybe orange and 61 blow. Now we can experiment with the slicers. No, let's see. Uh, that nose east a data this with her since Ah, just click the nurse East Data. And here we have selected a certain product in this state. Well, we came clear the filter. If you we don't want it, you can also experiment as they are as well, you can select multiple item by pushing. It's a shift key, so a slicer he's great to doing filtering in visually creative made it kindle. It's a slighter do one needed any more from the table. 75. How to Use Pivot Charts: in the chart section. We were creating different charters to limit our data. And with people tables, we also have the ability to create charts. How's this least off detail? No. Before getting people table, however, can create a people chart from the beginning. So to insert people chart, select one of for data and go to insert. And here we have people charts as you did for people. Table begin selects arrange int the place we want toe place the people chart. Let it be to new sheets this time. And it's like, Okay, now here we have a people table hand charts new area here instead, Off the roll, we have access and instead off. Uh, Collins. We have a legend, this truck agent and the number of fight I'm sold. It's a truck. Ah, agent here to patrol and, uh, number fightem salt. So they follow you. So here we have. Ah, this chart can change a chart up here. The design top and changed chart time. Go to design Tough and Jane chart type. Nice change, Jimmy. Disease horn. Nice folks. Better he having the ah charge style or so we kin do as our ah design options. This may be good. Qingqing charts Time Celso From here, this makes the swarm It's much better Qin Dry. Other possibility off the people chart to analogize our data different ways.