Class 7 (Objective Domain 2.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 7 (Objective Domain 2.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

6 Lessons (24m)
    • 1. Skillshare Introduction v3

    • 2. 2.3.1 Insert Sparklines

    • 3. 2.3.2 Outline Data

    • 4. 2.3.3 Manually Grouping Data

    • 5. 2.3.4 Apply Conditional Formatting

    • 6. Domain 2.3 - Exercises Video Solution

  • --
  • 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.





About This Class


Class 7 - Objective Domain Section 2.3 - Summarise and Organise Data

This is the seventh 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 section 2.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


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!
  • Yes
  • Somewhat
  • Not really
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.


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. 2.3.1 Insert Sparklines: sparkle under feature, which allow you to grace miniature chart inside or CSI Sheet south. The idea is that allows you to improve the visibility and readability of your worksheet without having to create full size chart, which may take up a lot of space. Spark lines are visual representation of each value in the horizontal or vertical range of cells. It's essentially a mini chart. Let's have a look at help surrounds passenger numbers for each month in 2017 from each of the departure airports. So here's the wrong information on Let's see what we can do. It sparked lines. I'm going to put Spark kind into the O column for each row of numbers, while I want to see if the trend from January to December over the course of the year, I just widen this kind a little to give more room to spare sparked line here on the insert time, we could see the Spark Line group and you can see that there are three types to choose from . We served with the line type, so I like taking this election. I get the create spark lines dialogue. I have to specify the day so I want to use to create my spark on because I won't see the trend from January to December for the first airport, I can select that cell range by kicking and Selby three on dragging to M three. The location I want sparked, like replaced, is shown here by default. It's a sound that I started the process off in understood correct cell. Now, when I pressed the okay button, I get my spark line showing a nice, even growth path old a period of time. I can also fill the rest of the data. Then you notice that all of the spark lines looked the same. That's because I was lazy and just increased every month by 10% for every airport. So that's what you would expect. But let's change some of the data just so you can see where there are different trends. We go to Catania and modify the figures, and you can see that the true representation off the data is being creative. I could have done this slightly differently. I'm going to delete thes spark line says and redo the experiment. This time, however, I'm going to select all of the data at once on, I'm going to specify the destination as the range of says, I want to spark lines to appear in 0320 15. Now I get spark lines, all of ones, this ranger says, now becomes a spark line group. Therefore, a former change to one of these says automatically applies to the other cells in the group . If I select any one of these surge, you can see that the whole group is highlighters. When you select any of the spark lines the spark tolerance Tuesday you become available with various options to show markers first in life points change The start of the sparked on by selecting from this drop down changes team colors here. I'm Indeed, I can also change each of the individual point colors. Also, I can also change. The type of sparkling was selected from one of the other two types condom or win loss. The Kalyan sparked on used a haIf of each column to determine the size of the value. The wind last chart type indicate whether each value is positive greater than zero r negative lessons here in this chart, every bar is the same shape and size. However, positive values appears higher. Bars a negative. Values appear lower bars. You can share selected spark on for use in these options. So if I select the first sparked line, like in selective declares from the group, or I can delete the whole group. 3. 2.3.2 Outline Data: occasional inquired with these next two lectures out of order off the domain. I did so because I felt that she needs to understand functions before tackling data applauding, which is one of the more complex subjects in the core except syllabus. Many worksheets become very large due to the sheer amount of information needed for analysis. To have organ is a data extract provides an automatic outlining to the neighbors you to insert sub totals whenever the selected field changes in value, you can make very big walk sheet easy to understand by setting it up so that you see only the sub talk for each group data. An important step is organizing the data so that the sub totals are displayed for the days groups you want with the Earth line feature turned on. You can quickly open up all off the selective group of data and collapse him again before creating the sub totals. You must sort the data by column to group these related roles together. Otherwise, use those who know me. But since you can also create multiple sub total for a single column or several layers of columns, for example, you can calculate to some on average septum for as many new Mary columns. If you require you mosad, each different type of sub total as a separate roto, identify the type of sub total. The key difference in creating multiple subtypes is hell you used to replace current sub talk adoption. Who in creating the first or only sub Topol for worksheet ensure that this check boxes turned arm when creating subsequent soap totals ensure that the check box is turned off. We use a helpless heavy several on flight day to demonstrate how this works. So here we have our flight data on. What I would like to do is to create a couple of outline soup totals who should be calculated whenever the departure airport changes. I'd like to see the number of flights on the total passed new numbers. The first thing you have to do is to sort the information base under departure airport. So if I click anywhere in this column, I can use the sort option to sort the data in ascending order. Note of the date of its soldiers. I can use the outline option in the data group to start creating my L Kline information The first thing I need to specify is where I want. Soap hopes to be calculation. The each change box allows me to selected feel I want which in this case is the departure airport. Now I specify what function I want to use when the change in departure airport occurs, we're going to use the sun function to add up the information in the number of passenger field. So I select the number of passengers feel in the ad sub talked to box. That's my 1st 11 Veltroni. Let's see how this looks. It's on left hand side. We have these three boxes 12 and three. As I click on these in turn, you can see the way that the data are presented. One is the grand total in Give me the total number of passengers from all airports to give me the somebody told them. For each of the departure airports, three would show me algae in line on the line data. But the nice thing here is that I could be selective if I only want to see the detail on the line, the core total. Then they can just click on the plus sign on that days is revealed, I can view the information in a very effective manner. Now I can have more than this level vote longing for my data. Suppose I also want to see a count of the number of flights from each airport. Well, I can specify for the level of sub total using the same technique. So I go back to the subtitle option here. I'm going to use same category to break up the data. But this time a specified can't on account the flight numbers. It's important to make sure that the replace currency of total check box is on ticked. That said result so the highest ever. We have 98 flight and 30 in tears, and one lived in 25 passengers. At the third level, we can see the number of flights on the passenger numbers from each departure. Airport sell Cork had five flights and 700 Canadian passengers 4. 2.3.3 Manually Grouping Data: Sometimes it may be necessary to create the groupings in your data manually, because I may not be any natural break in the cast privation data. He can do this by selecting the data you want to group on by checking the group command in the data group. This suit is for our C fe forced, even though there's only one my court. So we can insert a new row by right clicking and selecting and search now in the E kym, we can put it a new formula sub total, which is some. The number of passengers knows that this song has a number of folks functions which we can use. We're going to select number nine, which is a some function, and now we have to tell what it is that we want to add up. Let's repeat the process for Belfast. So again, insert new row un. Repeat the process. We can do this for all of the departure airports. Nobody can click on the also outlined option, and you consider the data happy note lined as you saw in the previous lecture. If you want to remove the outline, then you can go to the on group option on select the Clear outline Command 5. 2.3.4 Apply Conditional Formatting: We've seen many of the features in Excel, which allow you to format your work shifts to a hunter. They look, or to place an emphasis on certain data elements. Excel also have a feature named Conditional Formatting, which could be used to highlight areas of your walk. She's based on pre defined conditions with you. Specify. If those conditions are met, then the walk sheeted automatically form of the highlight self involved. This is a very powerful way to draw attention to specific data elements without the additional effort of mindedly changing the formatting. Each time you modify data or at new days to do a worksheet he gets is a conditional formatting here in the home tab. This poor family have quite a number of options, but you can see that the word rooms the cars a lot. That's because conditional formatting is based on the rules that you define. For example, you could specify that if a value in a particular sour over 500 then you want that sound to appear, but a red cell in order to warn you that the value specified have been exceeded, so you have to specify the conditions are the rules that would cause the formatting to change automatically. So first we look at the highlights, says rules option forced. This allows you to select pretty set rules or customizer owners to determine when specific four months to be applied. Despising means the highlights of ideas based on the relation to specific value you enter. So we have what we called Bernie in expressions greater than less than between and so on, which we can use to determine the format. And we specify, For example, in the past, in your numbers worksheets, we can apply conditional formatting to this range of cells, the total number in each month. To say that if this hope this lesson 10,000 we want to sell to be highlighted. So first select all of says, I want the rule to be applied to next. I selected conditional formatting menu on highlight rules, and then I'll choose the last option that brings up the less tan dialog box. Now I specify the Value 10,000 and then I construct the type of former you want to apply. I can select from the preset formers or, if I select the customer, the former self dialog box which we've seen before It's displayed. I can then special by any custom formatting I want. I'll just select the precept and we can see the effect so the cells up meeting criteria this best fight I know for a differently to all of you both bring attention to the fact that passenger numbers less than 10,000 are an issue to watch out for the other operated walk in exactly the same way. Try the medicine, see for yourself. Next we have the top bottom. Ruth, who survived a serious of preset rules based on the top or bottom ranking, says and range. So you use the same group of says. I can specify that. I want to say all cells between the top 10% in red, with a pink background on the bottom 10% in dark green with a light green background. Oracle decide that I want to highlight says we should above the average for this group as a whole. Next, you have the option to your days of bars, which allows you to embed a bar chart into a selected range of cells based on value in self . This type of four mounting over the quick visual representation of each share value in relation to the others. You can see the effect of the hover over the options the charts started shown within the cell itself. Visually, this could be far more effective than just seeing a group of wrong numbers. When I widened the canyon, the effect becomes even more pronounced. The color scares option allows you to show the relative value of each shell each other sound using court radiance. This example shows a red yellow green option with the lowest value highlight, with dark red dark green for the hi fi and yellow for the middle. About years, I concepts confused highlight values using icons. This example, a five bar strength meter similar to the wireless, never extend indicator on mobile computers, and devices indicate the relative value of each set all other self in the range. The set of the lower fights have no bar filled with the highest values have all. Barfield. You know all of these examples. You can see the same condition format applied to entire sad arrange if you change the value of any of these cells, the condition of former did automatically re evaluated under highlighting may change. Finally, a cell may have both. A manual former ONDA conditional format. If the cell contains a value that does not need any of the special fide conditions to sell , uses the manual format. 6. Domain 2.3 - Exercises Video Solution: open a workbook on in the correct worksheet which, like be four to G 10. Now we can apply conditional formatting. So here I say I like between and that we put in the two values that I want to control the condition Former Enough of 26,000 I got for Metis and yellow so I think the pull down and go to Costa Former, That's like yellow Phil. Then you could see the subs are highlighted in yellow. We can also put in data bars so I go to condition of four months. We haven't selected range of cells and go to date about then select the one that I want. So I want a red data about solid feel. But she also used texting condition four months so from a four day 10 conditioning for money. And I specify if the text contains the word advert I wanted in green We can change the four months that color want press OK and you can see two off. The sounds are highlighted in green because you have the world advert. No unclear all of the rules, the condition formatting on cue. Durer's not managers. We gotta clear growth so we can get rid of this on selected, said Clearers. Now it's like B four, g 10 and I want to play a yellow field where the cells are great in today's great and 40,000. So this the forced condition of putting on this range of South, you can apply multiple conditions to arrange yourself. So here's the forceful. If it's greater than 40 thousands, I want the yellow Phil. So we go to formation to pull their menu cause the former yellow Phil. But now I want to add another condition to that same range. So it's like the range again. And if the value is less than 10,000 then I want a blue Phil so I can have multiple conditions off the same range of cells. Now I'm gonna open up the flights. Workbook fights, detail, Workbook. What I want to do now is do l cloney. So we'll open up the file. Gotta love a Dacian to fire. But I want to sell totally change of the power to airport. So I select electric anywhere within the table. Never go to data and soup total on. We get this dialog box. I want to change in departure airport. I want to some the information, but there are other functions about this capable. So I want to some the number of passengers. So I press OK or the page break between the groups. In other words, every change of departure airport. You can see the answer is being shown here. Now, on the left hand side, you can see the outline. If I click on one, that's the highest level and see the total number of passengers is shown. And now I go to the next round which each of the airports and then I can like one of them like cork and show all of the details clicking on the plus side there can remove all the outlining clicking council total on saying remove all on It's all gone again I can my reinsert at Trump talked as well so I could mind insert a row. Ah, now I can put in a formula. We're going to see the formula now in the e 18. So this just heading about our subtitles This is equal to sub total and sub told the lousy two years a number of functions. So we continue the functions. I want number nine, which is the sum function? No, I havent put it in so I need to go back. Andi put in number nine chances which forces once or number nine is some comma and they're the range of says I want to sum up So I select the world for bad fast mover past for Belfast was a bracket Press enter and I get the answer That's what manually put in and a sub total.