MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course by prepaway along with practice test questions and answers, study guide and exam dumps provides the ultimate training package to help you pass.

Managing & Formatting Data

1. Objective Domain Intro

Alright, congratulations on finishing the first objective domain, and let me officially welcome you to the second: managing and formatting data. In this section, we'll cover data formatting and validation, including grouping data, removing duplicates, and managing conditional formatting rules. Number formatting, including Excel custom format syntax data validation, where we limit the values that tell groups and subtotals to hide, select columns or rows, and summaries our data; remove duplicates; and extract unique values from a data set are among the skills we'll cover. autofill and flash fill to generate data based on patterns and conditional formatting, including formula-driven rules to format cells using custom logic.

There are numerous applications for these skills, but some of the most common are the ability to toggle views without manually hiding rosary homes, highlight cells that meet a specific set of criteria, and detect and apply patterns automatically. to fill data, apply drop-down lists, avoid user input errors, and create heat maps to visualize data while hiding underlying values. So if you're ready, let's get started.

2. Custom Number Formats

To start us off, we'll be looking at Excel's custom number formats. So far, we've used Excel's predefined number formats such as general currency and percentage. But Excel contains a variety of predefined custom formatting options and allows you to define your own formats using custom syntax. And we've actually had a small taste of this already when we used custom date formats for numbers. Custom formats can contain up to four conditions, which are separated by semicolons. The first is the format for positive numbers, the second is the format for negative numbers, the third is the format for zeros, and the last is the format for text, meaning that you can choose what to display in case a text string is entered instead of a number. Now let's look at a quick example and consider this monthly expense's value. If we were to press CTRL 1 and give it this custom format, the negative number would now be displayed like this:

Now, because this is a negative number, it applied the formatting after the first semicolon here, and you don't need to worry about the syntax for the moment, but it's telling Excel to give the number a red color, wrap it in parentheses, and add a dollar sign and thousand separator. Pro Tip: If you want to build custom formats of your own, select an existing custom format similar to what you're looking for and make any necessary adjustments rather than starting from scratch. Now, to be able to build your own custom formats, you need to be familiar with Excel's custom format syntax. So in the left column here, we have these symbols that we can use to define our custom formats. The first three are used to hold a place for a digit, so the pound symbol will display nothing. If nothing is entered, the zero will display a zero and the question mark will display a space. So if we entered the custom formats that we have to the right here with a value of often, then these would be the results.

Now, the period and the comma are used to set the location of the decimal point and the thousand separator. The percentage sign multiplies the number by 100 and then adds the percent character. The forward slash sets the location of the fraction separator, and the number of digits after the separator will indicate which denominator to round up to the asterisk, which repeats the character after the asterisk until the cell is full, like the period. In this example, the underscore enters a blank space the width of the following character. And it's common to see this within open parentheses, as we see here when working with a currency. As a result, the spacing between positive and negative numbers will be the same, with negative numbers enclosed in parentheses.

Quotation marks are used to insert text, and the at symbol displays the cell's text as it is. Finally, you can write the names of basic colours between brackets to display the list's contents in that color. So now that we have this for reference, let's head over to Excel and build some custom formats here in Excel. We're back in the mortgage calculator tab in our course project workbook, and what I want to do is apply a custom number format to our total profit. Now we know that custom formats can contain up to four conditions: positive, negative, zero, and text. But since this is a formula-based value, it won't really be displaying any text. So we just need to focus on the first three conditions, which are positive, negative, and zero. Now I actually like the way the positive number is displayed.

Now it's nice and standard, but let's look at our negative values. So I want to change the price to 100 since we know from experience that this returns a negative profit. OK, so it looks like the negative value is displayed just like the positive, but with the negative sign. And I'm not a huge fan of this since it doesn't really stand out, and a negative profit should be a sign of concern. So instead of this format, I want the number to be read and wrapped in parentheses. So let's select our total profit cell and launch the Format Cells dialogue box by pressing CTRL 1, and we can go to the custom category. Now, even though we just learned about the custom syntax that Excel uses for these and we could just try and write this ourselves, we don't need to reinvent the wheel here, so we can just use the pro tip and find a custom format here that resembles what we want. So let's take a look around here. I'm seeing the word "red" here, "red and red," and a dollar sign.

Let's give this one a shot. So it looks like for the positive values we have the dollar sign, which is good, and the 1000 separator, which we want. And then at the end, it's adding a space the width of a set of closing parentheses, which we don't want. So we can go ahead and delete this part before the semicolon. The negative number is then given the red color, which is perfect for wrapping in parentheses, dollar sign, thousand separator, etc. So that's exactly what we want. Now that I'm here, I actually want to give the positive values a green color. So what we can do is literally just type the word "green" between brackets here and then press okay. And there we go. So our negative format looks really good. Let's move our price to $200 now and take a look at the positive format. Okay, I instantly regret this. Now that I think about it, the green colour was total overkill. So let's go ahead and delete this. I'm going to select it again, launch our dialogue box again, and get rid of this terrible decision. Press OK and okay.Much better. And now let's use the Goal Seek tool to change the value of our total profit to zero.

And let's see how that's formatted so that we can access the data for whatever analysis goal we have in mind. And we want to set this cell's profit to zero by changing our price. So press OK. There we go. And okay, so there's really nothing wrong with this, but instead of the number zero, what I'd want is to just display a horizontal dash. So, control one, let's go back into the formatting. And let's add another semicolon here at the end to move over to our zero formatting. And so, again, we just want a dash. And I'm actually going to wrap it in quotation marks since it's technically a text string. So let's do that. Press OK. And it looks like it worked, which is awesome. So we've got our zero condition, our negative condition, and our positive condition, which I'm going to return to our original price of 207. So being positive looks good. And there you go. Custom number format in Excel.

3. Data Validation

Next up is one of my favorite tools in Excel: data validation. Data validation allows you to limit the types of values that a cell will accept, such as whole numbers, ranges of values, dates, text, and so on. And this is great, don't get me wrong, but the real game changer for me is the data validation list, and the list option is used to create a drop-down menu containing specific items. These can now be entered directly or obtained from a sale range. And as an example, let's say that we have this information for a loan and we only have three down payment options, so 510 and 20%. So to make sure that no other values can be entered into the cell, we can use a data validation list, which we can do by going to the data tab, clicking Data Validation, and selecting the Allow List option. And in this case, we typed in the values ourselves, as you can see in the source.

And as a result, it will add this cool little dropdown menu to the cell that not only prevents other values from being entered but also makes it easier to switch between the options. Add an input message or error. Alert to customize what users see when they select the cell or enter invalid values, and know that only the stop type alert prevents users from actually entering the data. The others will simply display a warning message. Now let's jump to Excel and apply data validation to some of our cells here in Excel. We’re back in the mortgage calculator tab of our course project workbook, and what I want to do is add data validation lists to our down payment, our interest rate, and our term length. Now, if you recall from our Scenario Manager example, which I'm actually going to open up right now, you'll see that each of these has two options.

So the down payment can be $20 or 10%, the interest rate can be $5 or 4%, and the term length can be 30 or 15 years. And we can use Scenario Manager to display these different values. But what Scenario Manager can't do is prevent us or any other user from entering other values in these cells.

So in this case, since we only want them to be able to select those two options, for each of these, we're going to use data validation. So I'm going to select the first cell; it's going to be our down payment, and I'm going to go to the data tab and click on Data Validation, which is right here. and you'll see that it will open this data validation window. Now, right now, you'll see that any value can be accepted within this cell. And we can change this so that it's a whole number, a decimal number, or, in this case, a list. But there are other options, like date time, a certain text length, or custom criteria entered using formulas.

But in this case, we want a list. And the list of values that we want for our down payment is going to be 10%. Simply write those and 20% down. So we type those into our source and press okay. And now you'll see that this little arrow pops up to the right of our cell, and if we click on it, we get this nice little drop-down menu where we can select those values. So not only is it making it easier for us to select these, but we actually can't type in anything else. So let's say we made a similar mistake like we did before and entered 20 and then the dollar sign instead of percentage. When we go and press Enter, we'll get this message that says that this value doesn't match the data validation restrictions defined for the cell.

So now I can write it correctly. And there we go. Now for the interest rate, we're going to do the same. So, again, for data validation, we want to allow a list, and in this case, it's going to be four and 5%. Press Enter and there we go. Now, if you recall, I mentioned that for the source values, you can either type them in like we've been doing or we can obtain them from a cell range. So for the term length, I'm actually going to write the options right here. So 1530, and it looks like it has formatting here, which we can remove. And now I'm going to select my term length, go to Data, do data validation, select the list, and instead of typing in 15 and 30 here separated by commas, we can just select our range, press Okay, and you'll see that we get those options. That's pretty cool.

Now the other input that we have in this worksheet, aside from the down payment, the interest rate, and the term length, is actually the price per night. So we could use data validation there as well. So, once again, data validation, and in this case, I won't list because the amount can range from one dollar to more than $1,000. So in this case, we want to allow a whole number. And that's because Airbnb actually doesn't allow you to set a price with decimals. And it's currently requesting a variety of information from us. So we have a minimum and a maximum. But we can change this, and we actually just want it to be greater than zero, because clearly the price can't be less than zero, and it can't be zero either. So I'm just going to press OK, and let's see what happens. So we have 207. Now we can type 100, which is fine because it's greater than zero. And if you try to type zero, you'll say that again; we're getting this error.

And if we type a negative value, we get an error as well. And even if we want to type a positive value but with a decimal, then we get another error. And before we finish, I actually want to go back, and you'll see that aside from the settings where we specify the actual data validation itself, we can also enter an input message for our cell and an error alert. So let's use the input message first, and we can call it just the price per night. And the input message can simply be "Please enter a positive integer." So they know that it needs to be greater than zero and a whole number.

And for the error alert, there are three kinds. So the first is the stop error. And like I mentioned in the pro tip, the stop error is the only one that is actually going to 100% prevent our users from entering anything that's outside of the data that we specified. So let's select that. The title can be "invalid price," and the error message can also be "please enter a positive integer." So let's press okay. And now you can see that when we select that message, we get our input message. So please enter the price per night and a positive integer. And if we want to type anything that's not a positive integer, so 99, 99, then we'll get our stop alert.

So for an invalid price, please enter a positive integer. And even if I click "try again," it won't let me enter this value. So let's actually go and change this to one of the other styles. So, warning. Now if we use these and we enter a value that's not allowed, you'll see that here it's allowing us to continue even though it's incorrect. So that's the difference between these warning signs. Let's go back, select the information, and here it's just going to change the logo. So again, 99, 99, please enter a positive integer. Okay, but the value is still accepted, so I'm going to change it back to our stop press okay, change our number back to 207. And there we go. So we can hide this column. And now we have data validation rules in all of our mortgage calculator's input cells, which will help us avoid user input errors.

4. Group & Ungroup Data

grouping and ungrouping cells. The group option is used to hide selected rows or columns from view. And what happens when you group them is that Excel automatically generates toggles to show or hide individual groups as well as buttons to show or hide all rows or columns in a level with one click. And as an example, consider this worksheet with sales and expenses for a business.

You'll see that in the final column, we have the totals for the first quarter, and in the last row for sales and expenses, we have the totals for each of their individual components. And what the group option allows us to do is hide the details and columns B through D, then the rows for the individual sales and expenses components, and end up with this summarized version of the data. And we can use the toggles that you see on the left and on the top here to drill down into the underlying data very easily. Now, a pro tip, which I only discovered recently and have become a huge fan of, is that you can use the Control H shortcut to hide or show the toggle buttons.

So doing this won't actually remove them, but it will hide them from sight and get rid of the grey spaces around them. That being said, let's move on to Excel and use the group options here in Excel. I'm actually going to start in the Mortgage Calculator tab of our course project workbook, and some of you may have noticed, but we've already had a sneak peek at the group options here. And you can tell from this toggle that we have right here. So if we click on the plus sign, you'll see that it will unhide columns L through N, which are grouped, and this is where we have our Fixed Payments option. So we can use that to hide it again. And I don't know about you, but this grey area above my column headers kind of bugs me. So what we can do is use a pro tip and press CTRL 8, and it will actually hide it from view.

Now, it's not removing our group. You'll see that the columns are still hidden. And if we press Control Eight again, the toggle is still there as well. But it's a nice way to be able to work with the standard view of a worksheet and still be able to use groups. Now, I'm actually going to move over to the "Amortization Schedule" tab, and what we have here is the amortisation schedule for the payment of our mortgage. So each row represents the start of each month starting on September 1, 2020. And you'll see that the loan amount, down payment, interest rate, and term length are all taken from our mortgage calculator and the data that we have here. And that the monthly payment we calculated with the PMT function is also what we're paying here. Now, this payment is broken down into principal and interest.

And the principal is what you're actually paying toward paying off your loan, and the interest is what you're paying an interest. So if you add these two together, you'll see that you get the amount of the total payment. Now the running principle and running interest are simply the total sum that's been paid towards each at each point in time. And the balance is the starting loan amount minus the principal after each month. And you'll see that as we complete the 30 years in the term, the balance ends up being zero.

But we're not just here to talk about amortization schedules; we're here to talk about grouping rows and columns. So maybe we're not always interested in looking at the running principle or the running interest. So what we can do is select these columns and then go to the Data group, and you'll see that it will add this toggle, which we can use to hide them from view and then show them again. And maybe we want another view where we're just looking at the date and the balance due for each date.

So then we can group the columns from column B and drag across all the way to the payment select group again, and you'll see that now we have two different levels, so we can toggle these individually using these or we can use these options over here to toggle between the views. So that's going to be all of them hidden, then these shown, and then all of them shown. Now we've grouped columns so far, but what about grouping rows? Maybe we want to group our rows by year so we can select the rows for 2020 and then press Group as well. And this is going to allow us to hide them. Now I'm actually going to stop there before going on to the rest of the year because there's a better alternative to this that we're going to see in the next lecture. So to ungroup them, I'm actually going to select them, select them again, and then just simply press ungroup, and there you go. grouping and ungrouping data in Excel

Prepaway's MO-201: Microsoft Excel Expert (Excel and Excel 2019) video training course for passing certification exams is the only solution which you need.