Class 8 (Objective Domain 3.1 to 3.3) : 77-727 Exam - Techniques for MOS Core Excel Certification | David Murphy | Skillshare

Playback Speed


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

Class 8 (Objective Domain 3.1 to 3.3) : 77-727 Exam - Techniques for MOS Core Excel Certification

teacher avatar David Murphy, University Lecturer

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

9 Lessons (30m)
    • 1. Skillshare Introduction v3

      3:32
    • 2. 3.1.1 and 3.1.2 Create an Excel Table from a Cell Range and Reverse

      5:02
    • 3. 3.1.3 Add or Remove Table Rows and Columns

      1:27
    • 4. 3.2.1 to 3.2.3 Table Styles

      2:02
    • 5. 3.3.1 Filter Records

      4:50
    • 6. 3.3.2 to 3.3.3 Sorting Data

      5:25
    • 7. 3.3.4 Remove Duplicate Records

      1:11
    • 8. Solutions Domain 3.1 and 3.2

      2:28
    • 9. Solutions Domain 3.3

      4:25
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

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.

8

Students

--

Projects

About This Class

ad1f06e1

Class 8 - Objective Domain Section 3.1 to 3.3 - Create and Manage Tables and Filter Data

This is the eighth in a series of 10 classes designed to prepare you to take the 77-727 MOS (Microsoft Office Specialist) exam in Core Excel.

If you are a complete beginner then you should review all of the lectures. 

If you have some level of beginner or intermediate skills in Excel, then you might want to attempt the set project first and discover the gaps in your skills. These can then be addressed by reviewing the lectures.

So this series of classes is designed for everyone who wants to get independent certification of their Excel skills.

This class covers sections 3.1 to 3.3 of the Objective Domain for the 77-727 exam.

In addition, if you want to test yourself under exam conditions, then a further set of classes will be available (77-727 Practice Exams and Video Solutions) which provide you with 3 practice exams, comprising 105 project tasks, with which you can test yourself under exam conditions and time constraints. A full set of video solutions are available with these classes also.

When you complete this series of classes you will be fully prepared to obtain this valuable certification and obtain independent certification of your Core Excel skills from Microsoft, a qualification that is highly valued throughout the world.

Meet Your Teacher

Teacher Profile Image

David Murphy

University Lecturer

Teacher

With over twenty years experience as a systems analyst and project manager with significant experience in the design and implementation of major projects for some of the worlds largest companies, Dave Murphy changed career track in 2000 and has been a professional university educator for over eighteen years. His main interests are in Microsoft Certifications, Project Management and Business Process Analysis. Dave has a B.Sc (Hons) from Trinity College Dublin and an M.Sc Degree in Computing. In addition to obtaining PRINCE2 Practitioner status, Dave is also a Microsoft Certified Professional in Managing Projects with Microsoft Project, has multiple Microsoft Office Specialist certifications and is a MOS examiner.

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. Skillshare Introduction v3: Hi, My name's Dave Murphy Anime University lecture myself certified professional on a mosque examiner. This is wool class in a series of classes designed to prepare you to take the Microsoft certification exam. Examine 77 77. In Core Excel, the class is arraigned of people who are relatively new to excel on, particularly at beginners. However, if you're already in Excel User and would like to get official recognition for your skills , then this is the right class for you. If you're starting to learn except from scratch, I've included lectures in class War with Siri's, which will introduce you to accept to the Excel User interface had to navigate within excel until information is entered. Worksheets. If you have some familiarity with software, then you can skip the introductory lectures and go directly to the lectures on the specific elements of the curriculum court. In each class, which Microsoft called the objective domain, I've structured each class of that. You can go directly to the project and test your skills. That way. You know what it is that you have to brush open in order to get a little certification. The most important piece of information that you need to review at this point is the objective domain for the exam. This is a corrected and provided by Microsoft, and you need to ensure that you're proficient and every element of that objective domain. If you are to succeed in the exam, I've included a pdf off the objective remain with the project for this class so printed down have available as recovery rejection off the objective remain completely exercise. You can take each one off as completed. Looking at the contents, you can see that the objective remain is divided into five main sections and that each section have forever silt sections 1.11 point 21.3 and so on. Each of the classes in this series covers one or more elements off the objective domain on the lectures air number to indicate which L'm is being covered. The class projects are specific to the skill within that section of the demand covered in class. The most exams, based on real practical experience with software in this case itself, has to be used during the test, their normal with choice type questions. It's real experience with the product that being examined So if you pass than people know that you're proficient in the level which is being tested, they can be sure of your skills. Some of you may believe that your skills in a particular aspect of the objective remains a strong enough to pass already on that. You don't need to call me the lecture material. If so, fine. Just go to the exercises, calling techniques with that part of the off the domains on test your skills. Gaining more certification is a real advantage on a great way to enhance your CV. It's the number 1 90 certification in the warmth, with over a 1,000,000 exams taken every year. These classes will ensure success and gain that qualification. So go ahead and give yourself on edge in the market place. Get certified. 2. 3.1.1 and 3.1.2 Create an Excel Table from a Cell Range and Reverse: many worksheets consists of a rectangular block of data on which the road of data have a common structure and former excel after debility defined to define this block of a table threes. That, this seems facility is because you can use a variety of tools and excel such a selecting summary formulas, formerly sorting and filtering while many of the features available. Even without using tables, you'd have to manually select the range of served before using the features. If you define a range of south of the table, Excel knows that this group belongs together as a single unit, the same two of that and easy to apply because Excel New knows which have some food. If you look at the past, your number walk cheese, we can see that these roads all have a common structure consisting of the name of the airport on the passenger numbers in each month of the year. This is a good example of a range of cells, which can be categorized the table to create a table. Just think anywhere into cell range, then go to the insert tamp on click and table, the create table dialog, both splayed and you can see the Excel is guessing range of day that you want to use in the table. It's better to select the range of self for before inserting the table. So given that we don't want to do any analysis on the title of the worksheet, we can exclude that from the selection. I go back and select the data that I want force and then insert the table for the moment. Don't for you about what these dollar signs signify, as they will be explained later in the course. This box indicates whether the table information has headers while our selection does have headers, which is located in this row. So we want this check box to be arm. Now I can see how the table look from the press, the okay button to create a table. The data must be contiguous. That is the Muslim. Be any blank rows or columns in the range of cells. Also, the data must be ranged in row order with header at the top of each column on the data listed below. Notice that every table has a name and you can see here that the default name is provided by Excel as table one table to etcetera, which doesn't actually tell us something about the data. So it's a good idea to give the table a meaningful name. We'll call this long passengers. So I changed the name and press the return key. Now, no matter where I am in this worksheets, I can click on the name box of monetary passengers and brought directly to the table. Once the table selected, you consider at the table designed to of menu of activators, you have the option to turn on a Tokyo row, which has a new role onto the table like so, and calculate the total. Like so, this may not be appropriate in all cases on. Indeed, in this case, it doesn't make sense. Create this new total. So turns off every column on the table have a small polar may beside the Collier title, which gives access to a range of facilities. Such a sorting filtering on mother's. For example, if I want to sort the information off, a better be by airport. Then they construct the airport header on select sold from a two, says the information is no sort of from a upwards. Be careful. Hey, did this. Whoever noticed that the monthly total row is now within the table rather than at the end, because I selected the whole table. You cover all of these options and detail later on because so for now it's enough to be aware of the possibilities. Each Kalyan provides the same type of operators, so this is a powerful way of what you can analyze. The data also knows that in the total row, if I click on any of the cells, I have a pull down menu from which can select statistical functions to analyze the data. For example, I could change his role name to average and then indicate that I want to see the average will month by selecting the average fortune. Then I can use all the field to complete the serious. There may be occasions where you want to get the days of the table back to being just a range of cells, so you need to convert the table to back to being a regular set of rows and columns of data with their the table definition selected table on under the table designed to in the tooth group, click the convert to Range Command, and we can see that the data is no longer to find a table. The table name is also no longer in existence of the camp. See it in the list of available names. 3. 3.1.3 Add or Remove Table Rows and Columns: after his table creators. You can add or delete rolls on. Call you with data. Try a new kind of data at the far right of a table. Just enter any data into the first blank canyon Excel Automatically extend the table to include this new collier. Similarly, Excel extended taped include a new row of data that you enter directed below table as long as the total row feature has not been activators, as you can see in the table design tubes. If the Tokyo Rose activated, you must use one of the following methods. Manually. Insert a new blank data rope of clicking on the bottom right cell containing data the main suitable of the total row and then kicking the tab key Manually. Insert new blank days a robot clicking on the cell in the bottom data row. Then on the home top in the South Group, click the insert arrow and click insert table row below the insert broken in the home tab. Confused. Insert a new roll, call you anywhere in the table, not just at the last raw data. Similarly, on the home tab in the Cells group, you can use the delete button to delete rows of columns of data from the table 4. 3.2.1 to 3.2.3 Table Styles: you can change the way that the table looks, but using the table stars, which is available in the table tools design options as a hover over each one of the preset options. You can see the effect on the table on the way it looks. The table style colors option can be removed and reapplied later if needs again. You can also create a customized one, select a new table style and gave the style of name we called it from D M style. I want the Toblerone my table to be very distinctive. So select that role. No, I can bring up the former Selves dialogue box and change that role whatever way I want. All I have to do actually apply this course, um, style, and you can see that the total role with their bold and italicized the table tunes design Top also loves you to activate or deactivate older table formatting options, highlighting the first or last column or both. With the bold fund option by default, both options are turned off, banding roles but using different color shades for alternate alternate rows. This seizure often improve the readability of tables that are very wide because data in the same row has the same color shade by default. The banded rose option is turned on, but bandage columns is turned off. If you take the bandage columns, check boxes turn on. You can see that every second column is now highlighters. You can also turn the auto filter icon off. The final requirement in this section of the domain is to know how to turn to Total Row on Enough, which is just a case of selecting the table on in the table. Design options turned the automatic total on or off by clicking this check box. 5. 3.3.1 Filter Records: worksheets are often used, so enlargement of data. The term filter is used to describe the process by which you can extract data from a large amount of information by specifying criteria which will allow you to pinpoint exactly the data you wish to see rather than seeing all of the available data. Filtering does not change the content of your worksheet, only what you see of your work. She's no information. Seles is only the view of the days it has changed. We've already seen it when we create tables in, except that each kind get to pull there may which have access to the auto filter icons. These were activated by default for tables. You can still filter data, which is not being form of the table by activating the filter option from the days a tab on the filter option, the sort and filter group. This potent talk is the fare to pull them on enough. In its simplest form, the on filter fines under sprays, the rose for the value in the selected condom meets the criteria that you specify all of the rows of hidden from view until you change the filter criteria or you turned the old filter off. Nothing is the leasers, so you need not worry when the data seems to disappear. You see number, text or data filters displayed in this. Pull them and you depending whether the data in disliked it sounds or text you merrick or dates in nature. So let's look at each type. We look at one of the very columns, so expect always as an example, ignore the sort options with a moment of you see those in the next lecture. So, first of all, you'll always see a list which contains one instance off every value in the column in question, and they will all be selected by default. Now I can turn off the select off, default on. Instead, look for a particular value by just clicking on the one I want straight away. You can see now that I only see record future meet criteria specified. I also noticed that the head on the columns a year for the filter has this small funnel. I come indicating that the data has been filtered on, has been filtered on this condom in the sort and filter group. You can see that the clear command been activators. Just look serious. Election. Let me store all of the available data to the screen. That's a very straightforward way to filter data. The number filters option will be available on every calorie. Machar's numeric data on provide a way to specify more complex criteria for the filter. Each one of days will involve the custom auto filter dialog box so that you can specify what today's you want to filter out. For example, if I select the between option, I can never put in the values I want to search for in the dialog. I can also specify more 1,000,000,000 operators on be very specific about the numbers. I want to filter around to say I want to define records with value with great another thousands but less and four thousands. Then I select the Onda criteria. Unused appropriate operators great, another 1000 unless and 4000 click OK and we can see the result. The top 10 are filter actually loves you dissect any number rose, not just 10 and finally you construct The number of rows are percentage of roles, for example, 10% of 20 rolls, but result in only two rows being displayed after days in the column of text based agent. Then the command would be different, and you'll have the tech filters instead of the number filters option. In these cases, Excel, look for whatever snippet of takes you specify. So, for example, if I specify a or as the snippet and specify contained as the operator, then we will get our sea fate as the results. If we specify O N, then I get to Zoe's because that's snippet of text is in both of these airport names. If the data are dates, then we get a date Filter option instead, which gives a very large number of options to select from. You can also filter result er, that has already been filters. To say I want to see all records about passenger numbers in May exceeded 5000. Then I can easily find that information like so. But I also want see those records with the August numbers exceeded nine. Hasn't I can filter on the August column also, Now you can see that the filter icon is shown on both columns indicating a filter have been placed on both 6. 3.3.2 to 3.3.3 Sorting Data: sortie means organizing your data search can easily find the information you're looking for . For example, you notice of passenger numbers, you might want to see the data so that the highest total appeared at the top. And then the rest of this is in decreasing order. Excellent laces. Very easy by providing a sort option on each of the condoms dropped their menus in the table. So all I have to do is select calling. We want to sort on, in this case, the Total Canyon, and you can see that I consort from largest to smallest. This our filter option is available in each column. However it is, limbs it because you can only use to sort on one collier, especially when your data is in the table. Enough The ribbons to read itude buttons to enables. Sorting on the home tab in the editing group Click sort and filter. And you can say that we have the option to sort a to zed in ascending sequence or swords ed to a in descending sequence. We also have these options in the data tam on in the sort and filter group. The options and see will depend on the type of data your sorting. If the data is text, the remain of the space Short ages, ed and sword is that a If the data contains numbers, the river main with this place sort smallest to largest on sort, largest to smallest. If the data contains date values, the river manuals they sought oldest news on sort of news to oldest as an example of soldiers using text, we can select the airport name, call him and you can see that the sort from said to a man versus what is there already which happen sort of made is that in many cases you need to stop and more than one canyon to handle situations with multiple rows with the same value. For example, you may have a customer list with seven people having the same last name. In this situation, you want to sort the list using two columns one contained because from his last name and the other contained the first name. If you expand their example date for her piss airlines. You can see that we've added information on a new column indicating the airport arrival as well as the departure airport. I want to sort these data on two levels. The first thought I want is on the departure airport, and I want the information sorted alphabetically from A to Zed. Then I want to sort the information base in the arrival airport again, alphabetically from a present. So when I do that, I should see assorted list of the departure airport starting with R C fe on. Then, for each of the department departure airport, I should see the arrival airports listed in alphabetical order. To do this, we go into the data tab and then click on the sore bolton. This now provides a dialogue box, for you can specify multiple column for the sword order, so I want the first our primary sort, level to be departure airport. So I constructed from the list of available headings. Things will always reflect the head and your views in your table. There are options to choose from here, but values is a default on most use one. They specify the order, which we want to be a descent. Next, we can use this putting toe. Add a level of 30. You can see that the text says, then by So this is the next level from the primary one. You can also delete and copy levers. So in the second level I select the arrival airport and set to soar to be from a to that, let's say the result. So we have our C fe first, then within the record for receiving you committed at the arrival airport are listed alphabetically from A to zed. We have Brussels Dublin on Wrong. As you look down the list, you see the same pattern. The move up, move down buttons and only to change the selected sort level higher are lower in the sorting sequence. The options built into space, a dialog box that alleged to sort by column their roles and choose whether to treat the date as case sensitive or not. If you turn the case since of Option arm that Oprah case, those are treated as different characterised in lower case letters. For example, let her capital A is not the same as a letter small a My Days and has headers. Check Box tells Excel whether to treat the first row or column. Who says as labels are tied with for the data below or to the right. If you have headers, and you don't check this Box Excel Treaty hetero over the same type of data as all of the other information I'm including the sorting process Excel gives you the opportunity to select. With 64 columns arose, a sort levels. It's highly unlikely you're ever going to need to do that. It's important to know that although the example of showing has been based on the table, you can sort data in Any Ranger, says Evison, are constructed of the table. You don't have to define the data the table in order you to command in the days attempt on under home tab in the editing group sort and filter options. 7. 3.3.4 Remove Duplicate Records: they sell has the ability to find and remove duplicate data role from a cell range. This is offering youthful feature for walk Chief. With a lot of data on, Do you want to ensure that only unique values are present in the data set? If you select any cell in the table, you can go to table tools on design options, one of which is remove duplicates. You can say all Collins are only selected condoms to be used for comparing the data. Two status are just selected. The power to airport name and select the remove duplicates command. There you can see that Excel has removed every Cupid occurrence of an airport name into the part accordion. If you look closely, the reason my Belfast since there twice is that I've misspell it. You can also see this option in the days of tough removed you bigots option when you've execute the command, excel Italian. The number of duplicates removed on the number of e unique values remaining 8. Solutions Domain 3.1 and 3.2: case we're gonna open helps airline flight detail on gold compared data in the celebrates that I specified to a table. So we go insert table, you can see that you have the right cell range. Click OK. And now we have a table. We named the table by clicking on the table name box and typing in the appropriate name. When you have a table. Selectors, you have design options and you construct from two different styles that you want. So we want medium 14 that applies. The design also has the option to on checked Bounded rose are checked, bound, kind of dumb when they removed the filter. Now we convert to table back so click and go to design and have compared to range snow It's gone back to being arranged Never converted back to a table Gamba got into their table. I mean named this table of flights take, appear and type in flights. The design option then allows you to turn on total row. So you click on this check box I may have a total role with the bottom of the table Now in the total row, you can change functions They have a bunch of functions to sigh from you go for average, you get the answer in search of new columns in the rice you see in Sarah. Don't you have to take it selected? You considered kind to the right being widened slightly, Never create a new table style. So click on the pull down for the stars. Go to New. But here I want especially to modify the hetero select various elements off the tape to give your name dance like the one that I said is the hetero on Change that to blue Throw a former making blue I'm press OK and there we can apply that soil a new song practice our tour chambers so it could pull that man and click on the custom style. 9. Solutions Domain 3.3: so we open helps airlines flight details. Workbook now goes like they want to e 99. Go into the data option onto the sword options. The data source. Now we have the option to solve a particular heady this case destination on the values me wanted descending from Is that a local Adul levels last. We're going to sort using the flight number in Sandy or through effective light number. I understand these smalls two largest such fine. It's OK, it's up. This there were clear the fifth with going to data on. Sorry, don't filters go to filter by process. So we go into the destination, we can click our these after Process Island. There's are one record for process. Nobody wants sculpted process. Clear the filter. Next, we're gonna filter days it so that we're looking at the numbers of passengers. So we have a number filter army select between So between 75 greater than 75 and less than 150 press OK, you can see the records to spread. Next. We're gonna put another 50 shell records to meet quite tears at a point forward, the one we just did, but which also have a departure airport name, which is a text filter containing Phil contains The words are detects Om Yes, Okay. And once Mauritz filters clear although filters now never gonna show those records where the number of passengers is above average This is a number filter so down to above average , that's fine. So you can see we get tech filters and number filters. It depends on the data that we have we Northfield to buy sell color So you go that filled by color and we can select to call it a people filter on that's cleared off filter again Never going to sort some goes to sort on the departure airport feels we got sort departure airport ascending So that's from a to set They say Ford Now we go slate a subset of days So it's a small sets all the Belfast records on. We have to make sure you say my data has had unchecked and weaken dense like column A, B or C etcetera. No, we don't do that. Sort on removed, duplicate felt taken. Remove duplicates and we can select what we want to move the Jew tickets off this case departure airport it tells you how many it's actually taken away. You don't do that. I never removed you because based based on departure airport. Andi destination values So you can have multiple selections on this. So we've got two of them. Press okay? And again, it tells you how many Jew pickets aware what's going through move. We don't do that now. If in today's play only the destination with the text contained City. So this is a text filter. It's a text field on the destination field because sex filter, we can say, contains so good and contains on toe put in the world or a text city. There you go. Panama City, Panama City in Mexico City, etcetera. Clear the filter, and that's it Finished.