Class 6 (Objective Domain 2.2) : 77-727 Exam - Techniques for MOS Core Excel Certification | David Murphy | Skillshare

Playback Speed

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

Class 6 (Objective Domain 2.2) : 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 (33m)
    • 1. Class Introduction

    • 2. 2.2.1 Merge Cells

    • 3. 2.2.2 Create Conditional Formatting Rules that use Formulas

    • 4. 2.2.3 Format Cells by using Format Painter

    • 5. 2.2.4 Wrap Text Within Cells

    • 6. 2.2.5 Apply Number Formats

    • 7. 2.2.6 Apply Cell Formats

    • 8. 2.2.7 Apply Cell Styles

    • 9. Domain 2.2 - Exercises Video Solution

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





About This Class


Class 6 - Objective Domain Section 2.2 -  Format Cells and Ranges

This is the sixth 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.2 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?
  • 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.


1. Class Introduction: 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.2.1 Merge Cells: merging, says this feature commonly used and tech Sabres to identify a group of south together. So, for example, the help sailors marketing bushes. I've put that title in cell a one, but what I would like is for it to be evenly distributed across the kind from eight F in order to make more centralized on professional looking. I can do this by marriage in the appropriate cells. When you marry shows Excel, removes the ages separating select itself and treats the group as a single cell. I just select the cells I want to marriage and then click on the marriage and center booking here. Just both merged, says Incentive. The content. At the same time, notice that the only sell reference available now is a one, the rest on available because, in effect, developing marriage into a one, you can see that there are a number of options here. Marriage and center marriage across married shelves and onward Selves. The marriage across option will only marriage select without centering the texts. The own marriage option does exactly that embarrasses the merging of the cells. If you mirror surfeit of data. Morton, just the left. Most sell this message warned you that the data on the other cells or other than the most left left cell will be lost. You can also merged. Veritably like So merging says, can also access big going to the former Chairs Dialog box, which is available on the right most button. Just select themselves and then click the right most putting to select former cells. The alignment Tamp has this option to turn a marriage on or off. 3. 2.2.2 Create Conditional Formatting Rules that use Formulas : alignment refers to the position are placing the data within the cell. You can align sell content horizontally as well, vertically. Although you most commonly used one of the horizontal alignment controls by default, Excel assigns general alignments and new value. Dent's into a worksheet. This means that Excel aligns numbers and dates the right while it aligns text Davis had left. As you can see, indeed, examples also by default, says airlines on the bottom, we should only be noticed if you increase the row height. You can also change the vertical alignment to the top or middle of Sabah. Using these alignment options on the home. Top the alignment options on the ribbon, our top align middle alive on bottom line. Then here we have a line left center line on a line right day. She can also be indented within the served by clicking on one of these two buttons, which provide left and right indentation like so every time you click on one of these buttons, it increases or decreases the level of intento indentation by one we saw in the last sector . Here, you can access to former itself dialog box from the right most button on how we can use the marriage check box in the alignment top. There are other options in this style of which are very youthful smell, particularly the alignment options. For example, this orientation box allows you to dry to exactly the angle that you would like the days of to be positioned like so. We also have access to all of the alignment options available on the ribbon. 4. 2.2.3 Format Cells by using Format Painter: Once you form under a cell or arranger cells, you may want to duplicate it for money in the remaining power for your worksheet. Example. Visor to called the former painter that enables you to copy to sell for money quickly for more there in the worksheet to another. So now, rather than copying the content of a south or range of South, all you're doing is copying the way that selective cells have been formatted. You can then apply that same for money elsewhere in your worksheets when you get to you used to using this to if a really useful way to speed up the formatting of your work. So if I said like this said I'm right clicking this, you can see the paint Bush icon that is the former painter. We're like taking this notice that the Carson now has a paint Bush shaped decided, indicating that the former painter is active. You can apply the copied former to a single cell or to a range of cells. Now, when I select, this ranger says, I just click on it and the four money is applied. The cursor is no back to normal on the former painter has been turned off. Alternatively, if I double click on the former painter when I've selected the former thing, I want to apply the former pain to remains active. Until I pressed the escape key. The same functionality can be accessed on the ribbon from the clipboard group on the home top. 5. 2.2.4 Wrap Text Within Cells: you can enter up to 22,767 Carter of text into a cell, as I was, the cells to the writer empty the extra text that were nothing instance. Sell. It will overflow into themselves on the right, However, there is a way to me to take rap within the cell, in other words, as to take from me. See edge of the cell. It'll automatically wrap onto the next night and expand the width of the cell. If needs be, this will keep the text within the left and right boundaries of the cell. Excel increases the width of the row automatically to accommodate the full length of the text. If you turned off the rock text feature for a sell, the text will revert back to displaying on one line to turn text wrapping on. Just select the ranger says you want to apply wrapping to right, click on the most and go to the former cell command. Then see the alignment stamp on turn on the wrap text check box. You can see the effect immediately 6. 2.2.5 Apply Number Formats: given Alex as designed primarily to manipulate numbers. The formerly of numbers is usually the most common type of forming apply to a spreadsheet. There are a lot of different number formats that you can use to meet a variety of needs. The easiest way to see the difference between the very farmers. It's a local one number, and then we'll change the former to see the difference. So here we have our number 1234.56 If I select a cell right click and then go to the former itself Dialog box, the number topped provides access to all of the possible numeric formats This preview in to show the sample of what the number will look like for each of the four months, but you can select. You can also access these options in the home tab. The number group on By clicking on this small our to reveal the same dialog box. The general category is a default former for all cells. When you enter numbers into a known former cell, excel the space and exactly as you enter them, except that it does not display trading zeros after the decimal point. So If I type in 1234.0 you can see that zeroes don't splay. If I type in 1 to 3.1 it does play the 0.1 discriminated worksheet somewhat harder to read because the numbers in the column will not be lined up by the decimal points as you can save a type in these numbers. A further problem is that if you enter number that much larger than the width of the cell, excel automatically changed. The former to scientific notation on the number will look very different like this. To get more control over the former of numeric data, you can use the number option, which is a standard former for numbers and separate values of 1000 and higher. Using the period of full stop, you can choose to use a common separator if you wish. But checking this box the number of digits after the decimal point must also be specified. But the default is to you. Also have the option show negative numbers in one of these formats. If any number has more than two decimal digits, the number former were rounded to the nearest second decimal digits. As with all for Matty district for display purposes only. And if you look at the former by, you can see the complete number is what is actually starting themselves. The currency former. It's similar to the number former, except that a currency symbol, for example, the dollar symbol is shown on the comma. Separator is automatically displayed so you can select the current symbol you want from this. Drop them and you condemn. Choose the former Do you want from the available selection. The accounting farmer. The similar to the current reform, except that negative numbers appear in brackets on the currency symbol appeared at the far left of the South. Positive values into the space to the right of the value. To ensure the definite point off, line up the date. Former category displays date Fatty's in various ways. One complication is the region of the world, which a computer is set. For example, if you live in the United States, then your default. Dave Day former will be m slash D sash white, white, white Why and being want D being day Wiping year? Her vision computer sets to the United Kingdom. Your day format is D day slash Mm Fash Why? Why? Why? Why The d indicates of Excel displays single digit Dave ideas at one digit, but they're filling in the left with zero a d d indicates a two digit day value with leading zero for single digit values. The same formatting applies to the month. The new values If you enter your day, Fay with the month as a name, for example, September 827 16 8 September 2016 or September the 80,016 that Excel will display is using the custom date format of D dash. Mm mm. Dash white. Why were mm mm indicated first tree carted off the months. Name whoever. If you enter your day value with the month of the number, for example, nine slash eight slash 16. Then accept display using the day former that matches your region As described above, the asterisk indicates that this former changes with computers reason of setting, as described at the bottom of the dialogue box, the time farmers will once again be determined by the reason two, which a computer is set. Excel always shows percentage value of 100 times the number of value. So if I type in 20 on form at the number of the percentage, you can see that it becomes 2000% this former each other percent of shine at the right hand side of cell. You can also specify the number of decimal places with default being too. However, if you picked the percent style button in the number group of the home top on the ribbon acceptable use a percentage former for the decimal place value of zero. Personally, I find this. He's just type in a number with the percent of shim of directly afters. Excel understands this number to be 20%. The faction former converts decimal digits to fractional values based on the fraction type that you select the scientific notation we usually use. This former in scientific application for very large and very small numbers excelled shells only one digital left of the decimal point, and you can control the number of significant digits by specifying the number of decimal places. Special former category is for miscellaneous items such a full numbers and ZIP codes. So if I specified locations the United States, you can see that there are a number of special former suggest for the Social Security number, which is specific to that region of the world. If you can't find the exact phone what you want in the other categories, you can create a former of your own with the custom category. You also have the choice of using the ribbon for the more frequently you formatting features these options have found on the number group on the home top. So we have a drop down for the number formers, multiple accounting for months, percentages and separators on. We can also specify the number of deaths in places by increasing and decreasing them here. 7. 2.2.6 Apply Cell Formats: a fund is style of texts. Changing funds will alter head attacks, and numbers appear in the worksheet you can access to found command. Home Tab on the Funds group Excel applies any changes to the entire set or range of sounds that you've selected. You can also select individual characters on numbers inside of cell and change the font of size. The Fun. Tampa's occasion The former says Dialogue box, which is access by clicking on the dialog box launcher. You can also active from the right most button former turf and then click on the funds. Tough this time the Navy's you select many fun tree laters for my four money options available. Like So, let's have a look of each option. Wanted those. I noticed that the preview end of shows you the effective use elections before you applied into the work she's fund. This refers to the tai face of the text characters that excel display a set of characters in the same time Face is called the phones. You can see that there are many of Evelyn Excel font style. Most funds could be former, bold, italicized or both sighs. Sighs refers to the height of a character with the proportion of width. Most of the funds are scared of it. That is to have a variety of sizes. So you just select the fun size you want to apply, and you can see the difference immediately. Underline this. Poland shows you the options available for underlining the content of a cell. Be aware that in underline is not the same with sad border. If selected, the underline appears inside the cell where border lines appear, allowing these selected edges of self color. This pulled in legislate from a vast range of colors, including the more colors option, which allows you to create your own. Whatever you slept would change the color of the characters. In cell effects. You can use special character effects such a strike to superscript and subscript the options on the ribbon hair contained the most frequently used found options and could be changed directly from here. We've seen that you can underline the content of a cell or cells sell Borders are different borders. Separate groups of days from each other to improve the readability, or cheese, especially include the large volume of numbers you can get to the border feast by invoking the former Selves dialog box. As we've seen the minutes ago, you can also get at all of the border choices in this polar Manu on the fund group. The border feature enables you to draw lines around any or all of the four edges of a cell or a range of cells. The dialogue boxes play several presets. Lime take Mrs Color and style options unless to specify where the lines will appear. Let's look at the dialog box force, the line group alleged to choose a line sign and our color for the border if you want. Different lines are color for specific bores. You have to select the style of color and then click in the border area for the appropriate border. So if I want a thick red line, then I select them like so at the moment. There's nothing to see here because we have yet to indicate which board we want the formation to be applied to. If I click on the outline preset, you consider all four borders have changed. The buttons around the outside of this window act as toggle switches. In other words, they turn off and on the selection. So if I click on this one the top border, it no turns out selection off. If I check, never again the border returns. The non preset removes all borders. I cannot strict directly inside the box on board the borders appear and disappear, so there are a variety of ways in which to set borders was whichever way you choose. The selection will be applied to the South, which is slated previous to invoking the dialog box. Using the board of putting Under Heaven is a faster method of applying borders to dissect itself. You can click on the button to apply the border, using the current border setting for this book. Or you can check the our for two despite a drop down menu with commonly combined border for a cell, no step by default. When you start, excel, the borders boating on the river said to bottom border as you selective and border option from the drop down menu, the icon for the Board of Putin changes to show the new setting. This convenient feature allows you to minimize mask clicks while applying borders to multiple cells throughout walk sheet. You can also fill, says and ranges of says with background colors to increase the visual appeal of the overall worksheet. Patterns and colors can help draw viewers attention to particular part of your worksheet or serves to divide it off visually from the rest of the information. Diskin prove useful when trying to highlight the sum total drove or differentiate heading information from from normal data. The fill color button in the ribbon begins with a default color off. No Phil. If I select the field color of blue, you can see the effect in the sample preview area. The more colors provides a wide range from which you conflicted a standard or customized colors. In this small box, you can see the difference in the color of it currently is on the new selection that you're reviewing. The pattern style allows you to select apart from very selective cells, we should be applied to make those cells. Stan Elf, Maldives In worksheet. You can also apply color to the pattern peas in the pattern color button on the ribbon. You can access many of the same choices by clicking on the field, but in the funds group, this poor lamb provides the same options. Other provided. Using a former Tears dialog box, Brossel provides access to theme colors for selection. This Poland alleged to quickly change the color of the text within the cells that you've selectors. 8. 2.2.7 Apply Cell Styles: a style is groping our specific format setting for a South such as fund size and color. When you apply, it started to sell Excel format in the same way. If the silence changed, Excel applies to changes to all cells with that style to maintain a consistent look throughout the worksheet with minimal effort. Most of the style options coming, accessed here in the Stein's Group of the Home Top. For example, if I select this group of cells, I can now apply a preset style to the ourselves. But just clicking on one of these buttons. If you consider over, however, over style, the selection changes to look exactly like that star. There was just a preset former that we can apply to a selection. The more button here allows me to get access to an even bigger range of stars with a set of theme stars in different colors. You can see that each one has a specific name to see which style has been applied to selected cell, just highlight it and go to the south size group in the ribbon. The applied style would be highlighted with the border, So if I go from cell to cell, you can see that the style applied is highlighted. You can also modify your decides to see if your particular taste just highlight aside and right click to select the modify option. This being the performance serves dialogue, which condemn views to modify and saved. In this style, you can know see that the passion of Applied to Accent three is no part of that style knows that the sales, how I had already applied this style also changed when they modified starter have been applied to them. If you apply style in the workbook and modify that style anyway, then those changes will be automatically applied to, Olive says, which that style have been applied for says with numeric data. There are a group of quick stars available Select from here if you want to create a brand new style than the new style, Bottom invoked this dialogue here you can give the starter name and then specify what you want that style to look like 9. Domain 2.2 - Exercises Video Solution: on. The first thing we're going to do is Sakti three p 14. They're going to format this election being currency. So on the ribbon you have the option here on this pull downs. Just a currency. We want to center line. So we have the lime adoptions here. Now, anyone would go clean. Heading happens, airlines mostly revenue 2000 and 17. So we have a heading in. Now we want to marriage and center that immersion center. You select the sounds that you want to marriage across so that E won't people in. So we select all those cells. Now click on the marriage and Central on there is we apply styles that now actually I haven't applied style. But if you click on the polar menu, click and title and that star will be applied. But what? Firstly we're going to change sides will be four masters Change the fund too bold. Onside advances 22. That's okay, it's modified. Stein. Now I can go in and I can click on Apply the side, which I've done. Now we got to do the same thing to a one. We're going to apply the title side as you can see You can't see all the information because it's bigger. So we need to modify the width of the columns, make fully visible. They're going to go to sea to go chase, heading, click and see to and change heading 2% contribution because it's wider than said, it's going to go across into the D area. What we want to do is wrap this text off, select a cell. I moved the option up here to wrap the texts, but we need to increase the row wit so that we can see all the information so well. Just drag down here to increase the size of the with the rope. No, it also feel Q three to go to future in order. Feel it, Forward it down. Let me go to E 15. I mean own feel across. No, I'm going to enter formula. We're going to Q 15 on entered formula. The sum equals some Q three to Q 14 so it's basically the total off the airports of each of the airports of the told for all of the airports. Now, in the 16 I entered center Chief 15 divided by that total that so defined by the total that's gonna give me percent. She went a price center. I get this number, but I need to form without percentage because that's what it should be. A So I click others and I go to percentage and they were allowed to feel last. Okay, so, you know, happy have all the percentages? No, I want to put that into the Collier MSI so we'll see three. So, selectors no, I click in two C three, right click and pay special are going to pay special from the from the ribbon if you ation now on pay special. I want to say that I want to only devalued, unable to transpose them. And you can see that it was a very nice He put the formatting selling right, So I make that a percentage. I liked all these cells. I make the percentage, which is correct. No, I select e 16 to p 16 and they want to change this style of that. Okay. So I can go up into the style options here stars picking the pull down on I construct one specified, which is 60% accent. Six. Here we go back. A little change now in the information in the same range. I want to left line Antarctica left line and then in then twice. But taking on the Indian put twice. Next thing on all feel beat three. So we get generally for being marsh. And so on a next time go to angle counterclockwise by going up into this little option. Open the home with him so you can see it's indicated by a B with the narrow point upwards to write. You save different options on this when you click on the polar for what's now. I've taken this pull down on a different options. Counterclockwise is one of them. They can see the effect. Next, I'm going to create a new style click on the pull down and select a new start I would give to sign the name my numbers. And now we can change the formatting to be what I wanted to pay. So I very for must I feel cause like, make this any way I want. Really, I create my own stuff there. We have blue accent will like light or 40% never have that. Don't comply. That starts in the so actually says I that I have now. I just go up and take a pull down menu and apply that custom site. My Norse, that's this.