Microsoft 365 Excel Intermediate (2023): Customizations

Name: Microsoft 365 Excel Intermediate (2023): Customizations
Author: Bob Akbar

Explore how to customize tables and charts while incorporating data security in Excel

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Ackbar, and in these lessons, we will talk about the benefits of simplifying visual cues using conditional formatting, discussing when to use charts versus full available lists, and data security and privacy issues, and how to address them properly. In this lesson, we'll talk about conditional formatting. Now, you will notice that we have a column here for profits, and we're going to highlight that, and under the Home tab, we have this, Conditional Formatting. You will notice there is something called highlight cell rules, which we will start with. This is pretty simple. It says, give us something that is greater than, less than, between, equals to, or text that contains a date occurring or duplicate values. So let's start off with this one. Something that would make sense here is if we were going to do profits greater than $2 ,000, and we get the conditional formatting here, we have our range, which is I2 to I14, which is what we have highlighted. Then we're going to say the greater than, the cell value, and we will put 2 ,000. Now, down here, we have the option of filling it with different colors. Since greater than 2 ,000 is a positive thing, we're going to go with the green, and you can change your fonts here and your fill color, as well as the other font formattings. And you can also do the different border colors, shapes, fill patterns, and things like that. We are not interested in any of those for this example. So we're going to simply press done. And you will notice there are one, two, three, and very quickly, if we are looking for greater than 2 ,000 in our presentation, or if we're trying to show this to other stakeholders or other target audiences, they will see that those are the positive numbers. Now, if any number is under 1 ,500, we're going to say that is not good. They need to improve. So we can go back to the same thing, say less than, and this time we'll put 1 ,500, and we'll keep it red and click okay. So there we are. Very quickly, we can see who the good performers are as far as the profit is concerned and what the bottom or lower profiters are, which are highlighted by the red. Next, let's highlight some dates. And we can go back here and we can go here where it says a date occurring. With the date occurring, I want you to skip down to this one where it says yesterday. And this will say last month, last week. And this is helpful, of course, if we want to see something that is recent. However, if we want something that occurs at a specific month, then we will go to that specific day. And the way that we do that is date occurring, and we select this month, and we click done. And you will notice nothing shows up because of our dates are not within this month. So what we're going to do is we're going to change a few of these so that they are within this month. And you will see immediately the conditional formatting takes effect. That's one of the great things about conditional formatting. If the data changes, the conditional formatting changes. So say, for example, this one, that was an above average performance, 5 ,800 was the sale. Say there was a typo in there, and it was actually $580 instead of $5 ,800. It immediately changed the conditional formatting to match what it had changed in the value. Now, we can also do something that I find useful is the top and the bottom rows. Now, the top 10 and the bottom 10, you need more than 10 items. We do have them. So we can actually do that. So let's see who the bottom 10 of the commissions are. Asks us for the color. And this time, let's change it to red with the white font. There we are. And there it is. Out of the 12, these are the 10 bottom ones by default, leaving us the two top commissions. Now, we can also go back to our conditional formatting, and we can clear the rule from the selected cells or from the entire sheet. So we'll do the selected cells first, and we'll return and we'll do the entire sheet. And there we are. Let's do one more. Let's do now one that has text in there or strings. And we can go in here, and now we can take a look at something that is equal to, and we can do the same thing. We can type in the name of the city, and there it is. Another thing that we can do is we can actually highlight our entire table here. Go to conditional formatting and say equals to. Now, here in the cell range, we want F2 where it says highlight cells with cell value equals, and we can change this to select what we actually want to do. So we can click here and we can click on Toledo and say done, and it will do it. And it'll give us the rules so that we can manage them. Toledo was found in F2 to F14. That's where it's looking. And if we wanted to change something here, we can go to this sheet and we can edit. Please keep in mind we're doing these for example purposes. Focus on the actions that we're doing and not the actual data. This is a sample data. So if we wanted to change this to be greater than or equal to not between not equal to, there's another option that would make sense in this case. We could pick that. If we wanted to change our range, we could also click it here and let's go to commissions and we can change the values to be greater than or equal to, and let's pick 180. Keep in mind, please, if you are doing this, you have to change the different fields if you're switching between the different data types. And this time we'll do the yellow on the black background. And there it is. Our commissions now have been changed. So we can go through managing rules in this sheet. If you're doing something that is a repetitive formatting and you want to do multiple things, you can simply stay in this sheet or you can close this, highlight your selection such as commissions and go back up to conditional formatting. Stay tuned for the next lesson where we'll discuss chart templates. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 24m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

Getting the most out of Excel means mastering tables, charts, and data security. With conditional formatting, tables will visually tell us how the values measure. Also, charts from templates can be used to create appropriate visual displays. Chart templates provide an easy way to generate new charts that can be customized with legends, labels, and more. This course will also talk about data security, a top IT concern.

Conditional formatting is a powerful feature that allows a wide range of options for users to visually differentiate values in a field. These options include comparisons to the average and top and bottom of a range.

Data security is no longer just the responsibility of the IT department. It is now up to every user to educate themselves about data privacy. Many new laws, regulations, and organizational policies exist with very strict limits of who can have access to which information. This information may involve personal details about employees, contractors, students, or staff. It could also involve competitive information such as which regions or products are generating the most revenue.

What You'll Learn

Define conditional formatting and use it to visually differentiate values in a field, including comparisons to the average and to the top and bottom of a range
Apply conditional formatting to tables so values are visually represented
Create charts from templates and customize them with legends, labels, and more
Understand Excel templates as an easy way to generate new, customizable charts
Separate data and charts to support data security
Understand Excel data security and the responsibility every user has for data privacy

Key Takeaways

Conditional formatting is a powerful feature offering a wide range of options to visually differentiate values in a field, including comparisons to the average and to the top and bottom of a range.
Chart templates provide an easy way to generate new charts that can be customized with legends, labels, and more.
Data security is no longer just the responsibility of the IT department; it is now up to every user to educate themselves about data privacy.
Many laws, regulations, and organizational policies set strict limits on who can access information such as personal details about employees, contractors, students, or staff, or competitive information like which regions or products generate the most revenue.
Getting the most out of Excel means mastering tables, charts, and data security.

Frequently Asked Questions

What does this course cover?

This intermediate Microsoft 365 Excel (2023) course covers customizations across three lessons: Conditional Formatting, Chart Templates, and Separating Data and Charts for Data Security. It focuses on getting the most out of Excel through tables, charts, and data security.

What will I learn about conditional formatting?

You will define conditional formatting and learn how it lets you visually differentiate values in a field, with options including comparisons to the average and to the top and bottom of a range, so that tables visually show how values measure.

Does this course address data security?

Yes. It covers Excel data security, explaining that data security is no longer just the IT department's responsibility but every user's, and that many laws, regulations, and organizational policies place strict limits on who can access certain personal or competitive information. It also covers separating data and charts for data security.

What skills does this course relate to?

The course relates to Microsoft Excel, Spreadsheets, Data Manipulation, Excel Macros, SpreadsheetML, and the Microsoft Office Specialist (MOS) - Excel.