Microsoft Excel Basics For Beginners - Incl. Spreadsheet, Formulas, Charts, Pivot Table [2021] | Tamas Benko | Skillshare

Playback Speed

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

Microsoft Excel Basics For Beginners - Incl. Spreadsheet, Formulas, Charts, Pivot Table [2021]

teacher avatar Tamas Benko, Drawing

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

25 Lessons (5h 39m)
    • 1. What's in this Course?

    • 2. Get The Most Out Of This Course

    • 3. How To Set English Display Language

    • 4. Getting Started With Excel

    • 5. Highlight Max Value on a Chart

    • 6. Conditional Formatting

    • 7. Map Charts

    • 8. Bar Chart With Icons

    • 9. Chart In Infographic Style

    • 10. General Shortcuts

    • 11. Shortcuts for Navigation

    • 12. Shortcuts for Data Entry

    • 13. Formatting Shortcuts

    • 14. Selection Shortcuts

    • 15. Shortcuts for Formulas

    • 16. Miscellaneous Shortcuts

    • 17. Relative vs Absolute Cell References

    • 18. Working with Dates

    • 19. SUM(), SUMIF(), SUMIFS()

    • 20. MIN(), MAX(), AVERAGE(), COUNT()

    • 21. VLOOKUP(), HLOOKUP()

    • 22. INDEX(), MATCH(), XLOOKUP()

    • 23. PivotTable Made Simple

    • 24. PivotTable From Multiple Tables

    • 25. Congratulations!

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

Master your Excel skills in 2021.

In this special Microsoft Excel - Excel From Beginner to Advanced class you can learn the most important features of Excel focusing on data visualization and productivity. I'm using the version Microsoft Excel 2019 which is similar to Office 365 Excel, but the course is 90-95% compatible with Ms Excel 2010, 2013, 2016 versions.

If you're a beginner, in the first section you can learn Excel basics in 30 minutes.

Topics covered in the Getting Started With Excel lecture:

  • quick overview about the user interface

  • how to save your excel worksheet

  • drag & drop gesture

  • excel rows, columns, cells

  • how to move between cells quickly

  • excel data entry

  • excel data types

  • excel formulas & references

  • excel operators

  • how to select cells, ranges, columns, rows in excel

  • copy & paste data

  • AutoComplete feature

  • AutoFill feature

  • what is function

  • define Name

  • how to sort & filter your data in excel

  • how to create a simple chart with a few clicks

  • multiple worksheets

  • zoom function

Then we're going to go through several data visualization techniques which you can use to create Excel charts that will impress your audience.

Projects covered:

  • highlight max value dynamically on an Excel chart

  • discover Excel's conditional formatting feature to highlight key data in your table

  • how to create a map chart that reads well by the viewer

  • how to use icons in your bar charts to make it exciting

  • how to create a progress chart in infographic style

I dedicated an entire section to Excel shortcut keys showing you over 100 Excel shortcuts. The more keyboard shortcuts you know the faster you can work in Excel and more productive you can be.

Shortcuts covered in seven categories:

  • general shortcuts

  • shortcuts for navigation

  • shortcuts for data entry

  • Excel formatting shortcuts

  • selection shortcuts

  • shortcuts for formulas

  • miscellaneous shortcut keys

I created a section for the most frequently used Excel functions. You will find lots of real-world examples in these topics:

  • Relative vs Absolute cell references explained

  • working with date functions

  • SUM(), SUMIF(), SUMIFS() functions

  • MIN(), MAX(), AVERAGE(), COUNT() functions

  • VLOOKUP(), HLOOKUP() functions examples

  • INDEX(), MATCH(), XLOOKUP() functions to search data

In the last section we're getting into an advanced Excel topic. I explain how one of the most valuable Excel features works, the PivotTable. Many people are afraid to use it, but I'll make it simple for you. We're going to build several reports, and create a PivotChart too. I will also talk about database design principles. We will create a Data Model, and I will show you how to use PivotTable with multiple tables. So, you'll be able to use Excel as a real database.

By taking this class you can really take your Excel skills to way above the average.

Don't worry, if you're an absolute beginner. I'll explain everything step by step.

If you're already familiar with Excel, this class is a great refreshing session, and I'm pretty sure I will be able to show you new, interesting things.

Please watch the Intro video to see what you can expect from this course. Enjoy learning!

What's in this course?

  • Ms Excel basics & advanced features in a 5.5 hours training
  • Focusing on Data Visualization techniques you can stand out with
  • Over 100 Keyboard Shortcuts To Speed Up Your Work
  • Most Frequently Used Excel Functions With Examples
  • PivotTable Made Easy
  • Database Design Principles
  • PivotTable Advanced (Using Multiple Tables)


Exercise files and links for each lesson can be found under the Projects & Resources tab.

Meet Your Teacher

Teacher Profile Image

Tamas Benko



I love to teach new skills to students, so I'd like to see you in my class!
And please don't forget to hit +Follow button to stay up to date with all my future classes.

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. What's in this Course?: This course is different. In addition to the basics, I'm going to teach you skills that will help you to stand out. I'll be focusing on out of the ordinary data visualization, showing the best features of Microsoft Excel and sharing many tips and tricks which can really make an impact on your work. Welcome. My name is Thomas. I am an IT engineer. I've been using Excel for over 25 years now. I've seen so many people using Excel on a very basic level. Excel offers you so much more. By the end of this course, you'll be better in excel than many people who have been using it for years. You'll be able to create materials that your teammates, your boss, or your audience will be impressed with. If you are a beginner, you can start with the 30-minute Getting Started lecture where we go through the basic concepts. In the second section, I'm going to show you how to create something special. How to visually represent your data better than most Excel users usually Do. You will learn how to create a chart where the max value is highlighted dynamically. Conditionally format your data table to pinpoint key elements. Create a map chart that is better than the standard Excel heatmap. Cheer up your bar chart with icons and show progress in infographic style. In the third section, you can learn over 100 excess shortcut keys, which can greatly speed up your work on different areas like navigation, data entry, formatting, selection, formulas, and more. In the next section, we will discover the most frequently used Excel functions, looking into a lot of examples for manipulating dates, summing up values in different ways using min, max, average and count functions and searching data with VLookup, HLookup, index and match functions. In the last section, you will understand how one of the most valuable Excel features works, the PivotTable. Once you understand it, you will use it all the time in your data analysis. We will also create pivot chart from our data. As a bonus, you will learn how to build a solid data structure with multiple tables, how to connect them so you can use them as a real database. You an idea as students for this course, if you are new to Excel, because I will explain everything step-by-step. If you are already familiar with Excel, that's okay too. And make sure that you learn a lot of new things. Thank you for watching this intro video. If you'd like to take your skills to the next level and rolling discourse. Now, I hope I can see you in my special elixir course as a student. 2. Get The Most Out Of This Course: First of all, thank you for taking this course. Please watch this intro video. It contains important information. I designed this course to make it interesting for both beginners and students who are already familiar with Excel. To some extent. You don't have to take two lectures one by one in chronological order. Actually, it's more refreshing to pick your visualization lecture on one day and another topic on another day. My only suggestion is if you are a beginner to pick the first lecture of a section for starter, it may contain general information for the other lectures in that section. Pixel has been on the market for decades. It has many different versions, but fortunately, the user-interface hasn't changed significantly since the version of Excel 2007. In this course, I use the exert 2019 inversion, which is pretty similar to 365 version. Every new release of Excel brought some new features. But in most cases, we are going to use features that work in version 2010 and above. I will add version related information where it's needed. The follow-along lectures effectively, you will need the English display language in your Excel. In the lectures, you will see the menu items, forms, and settings in English. If you are, each cell is installed with other than English, you will find further information in the next video about how to set it. There are also some operating system level original settings like date, format, decimal, and thousands separators. You don't need to change them. I will use the US English settings, but if you have something else, it shouldn't be an issue. Your original settings will be applied in downloaded resource files automatically. Using keyboard shortcuts can save you a lot of time. I'm going to show you as many as I can. As I'm working on Windows ten operating system. I will use the Windows keyboard terminology. But don't worry if you are a Mac user. Most cases you can map the Windows keyboard shortcut Mac keyboard using this table. Pause the video now and make some notes if it's necessary. If something doesn't work, check the keyboard shortcuts section, general shortcuts, lecture resources where you can find a link to the official Microsoft page listing or Mac keyboard shortcuts. I hope you will enjoy this course and learn many new things. See you in the next video. 3. How To Set English Display Language: If you are excise, this Pe language is set to English. Men feel free to skip this video. Even if you install the or Excel with other than English Language, you can download the English language back and set it. You can install any languages in your Excel without any extra cost. And you can switch between them at any time. Let me show you where this language setting is in my exert 2019 version. Let's go to Options language. Look for this section of his display language. Click on this link. And the installer display Language window pops up. Choose English. I cannot because I have it installed already. And click Install, then follow the instructions. It's pretty straightforward. Once you installed English language, come back to this form. Select English here, and click on the Set as preferred button. You may need to restart your excel at the end to make English display language active. If you don't need English language anymore, come back here and set your own preference. Unfortunately, in other versions of this form may look different. And I also experienced that in order version, the related website link has led to an obsolete Microsoft page. If you run into an issue like this, open a web browser, visit in the search field, typing Office language back. Find a link that leads you to the official Microsoft website. Click on it. You should see something like this. Scroll down a bit. Choose your office version here. Choose English language from the drop-down list. Download the version that fits your operating system, install it, and follow the instructions here. Alright? Once you have the English language set, you can start the course. See you in the next video. 4. Getting Started With Excel: If you are an absolute beginner, you should definitely watch this lecture. By the end of this lecture, you will have a good understanding on the base concepts of Excel pretty quickly. If you are already familiar with Excel, please check the content and if you feel yourself confident on these areas, feel free to skip this video. Now let's get into it. When you start Excel, you can open a new blank workbook by clicking on this icon. And you will see something like this. During this course, my mouse cursor will be marked with this yellow circle, so you can easily follow whatever I do. Wherever I do it. At the top, you can see the name of this file. On the top-left, this area is called the Quick Access Toolbar. The first button is safe, and you can see it's shortcut key in the tooltip Control S. When you see a shortcut key like this, it means that you press and hold the Control key on your keyboard. Then you read the other key And finally released control. So I hit control S and it invokes the Save As panel. You can choose the location of your file. Here. I hit escape to quit this form. These are the undo and redo buttons. I haven't done anything yet, so they are not active. They work the same way as in any other software. It's worth to memorize their shortcut keys, Control Z and control y. Here you can customize this Quick Access toolbar. You can add or remove patterns. The next row is the main menu. As I'm clicking through, you can see the underlying items are changing. This section is called the ribbon. The examiner structure has responsive design. It means that it may look differently depending on your screen resolution. My screen is a full HD or 1080 P display. But if I take my window size smaller, you can see that the look of the patterns will change. If you use a lower resolution screen, you may need to look for the icons or buttons on the ribbon to follow along. I hope it won't be an issue. If for some reason your menu or Ribbon disappear. Look for this ribbon display options button on the top right. And click on Show tabs and comments item. This name box shows the active cell by default. You can also access certain parts in your Werbach weekly through these drop-down list. It's empty. Now, we need to get familiar with other concepts before we use it. I can change its size by dragging the separator. By the way, when I say drag and drop, it means that I move my mouse cursor to a specific location on the screen. I press the left mouse button, audit down, move the mouse to another location, and release the left mouse button. So this action is called drag-and-drop, and you will use it all the time with different purpose. This insert function button will be very useful if you are a beginner. It invokes a kind of function wizard, but we will look into that later. This is the formula bar. I'll explain soon what that formula is. We'll come back to this sphere then. This middle part is called worksheet. You can see its name here at the bottom. The worksheet consists of columns and rows. Columns are marked with letters. Rows are marked with numbers. Each letter and number combination identifies a cell on the worksheet. This dark green border represents the actual cell, which is now a one. You can also see it in this name box. This A1 is called a cell reference. We will use Sarah Frances, or the time. You can change the active cell by clicking with the left mouse button somewhere else. Or you can use the arrow keys on your keyboard to change position. Now let's see how data entry works. I'm going to create a simple data table with some serious data. In the first row, I enter column headers. You can enter data in the active cell by simply start typing. I hit the tab key to complete the data entry and go to the next cell on the right. Notice that the text in cell A1 extends beyond that column. Move your mouse cursor here over discount separator. Double-click with the left mouse button. Pixel auto sizes come a. Or. We can also drag and drop this column separator to adjust the width of this column. Let me type in the other column headers. I'm using the tab key to move to the next cell. It would be good to auto size all comes at once. Let's select these columns. This will be a drag and drop action I was talking about. I press the left mouse button here on column B, Hold it down, drag the mouse cursor to column H, and release the mouse button. And we have all of these columns selected. Now I double-click on one of the column separators and each column will be adjusted to the text length. Each cell has a datatype in Excel. By default, it is set to general. You can see it here. I click on this down arrow to see the drop-down list. General means that the data in the cell has no specific format. Excel tries to identify it and display accordingly. I go to sell a2. I type in this state. This time I hit enter. Notice that the active cell is now below the cell I just edited with the Tab key. You can step horizontally. We'd enter. You can step vertically. If I go back to cell a2, you can see that Excel has identified this data as a date. This is because the data I entered corresponds to my original date settings, which is US English in month, day, year format. I enter a product here. This time I hit Control enter. Remember when you see a keyboard combination like this, you hold down Control, hit the second key and release control. If you enter a textual data in, the data type will be general. Let me enter a unit price here. I hit control enter. Another data type has remained general. But notice that Excel right aligned the cell. We usually want to see numbers this way. Let's change its datatype to currency. Clicking on this button, we get the Accounting format. I choose this currency format instead. You can change the number of decimal places. With this button. I type in a quantity. The net amount should be a calculated field because we have all the information in column C and D. When you want to calculate the value, you use an Excel formula in the cell. Formula starts with an equal sign. I type in an e cosine. Now we need to refer to this cell. I simply click on it. And we got the Sarah France C2. In the formula. We need multiply operator here. I typed in an asterisk sign. And we need a Sarah France to D2. This time I type in debt. Watch how colors happens to connect these references with the SAS R formula. We calculate this mathematical expression, c2 cell value multiplied by D2 cell value. By the way, you can drag and drop reference like this. But we don't need to change it. So I move it back. I hit enter. We get the calculated value in cell E2. Notice that we can see our formula in the formula bar. And we could edit it there too. I hit escape to quit from the Edit mode. I don't know. Let's say there is a 5% tax on this product. I type in 5% and hit Control Enter. Notice that we got a percentage format automatically. The tax amount will be calculated field again, I start formula. This will be the net amount multiplied by the texts percent. I hit Control Center. Excel has realized that net amount has a currency type and apply it on this cell to. That's good. Now the gross amount formula will look like this. Net amount. We use the plus operator and the texts. I hit Control enter. All right, we have one row of data. Now I'd like to format this header text all at once. You can select a range of cells with the drag and drop action. I press and hold the left mouse button on cell A1. Move the mouse cursor to cell H1 and release the mouse button. Now I can format these cells together. I could click here quickly to make these texts board. I could use the shortcut key Control B, as you can see it in the bubble. Or I can hit control one to involve the format cells dialog window. This is where you can perform all kinds of formatting. Here, you can choose datatype, but we don't need that for the header. Instead, I go to the Phone tab and choose the board font-style, slightly bigger phone size. And I choose a fear color here. I hit OK. Let's change the column width one-by-one. I select the cells and align them to the right with this button. I select column a and choose center align. Good. Now let's see the autofill feature. I select cell a2. If I drag and drop this corner node like this, Excel auto fears the SAS for me. I hit Control Z to undo. If I hold down control as I'm dragging, was that plus sign in the mouse cursor. Excel will copy the same value all over. This is true for date fields are number fears. The autofill feature behaves differently. I drag the corner no downwards without control. I hit Control Z to undo. Now I hold down control as I'm dragging. You see it's the opposite. I hit Control Z to undo. Excel can also realize patterns. Let's say we have cells with these data. I select them and use the autofill feature. Now I select this says and hit Delete on the keyboard to delete their contents. I autofill transaction date count without holding control. Ok. To complete the table, we just have to fill in these three fields. These other ones will be calculated. Now if you want to quickly enter data in an area, select the range like this. Notice that the active cell is this one. I start typing the product. Excel automatically completes the rest if the texts you've typed in matches the pervious value in this column, I can hit tab at this point and item one will be entered. This feature is called auto-complete. And now the next cell is active. I can type in the unit price, hit tab, the quantity heat tab. And this way you can enter data in arrange pretty quickly. I, if you hit Enter, your active cell moves vertically. And from the bottom, it jumps to the next column inside the selected range. Which Shift-Enter you can go backwards. And similarly with shift tab, you can go back one cell horizontally. Now I enter another item with a different name. And I start typing in the next row. Autocomplete doesn't find an exact match, so it doesn't offer anything. However, if I press odd down-arrow key, a drop-down list appears with the values. And with the arrow keys I can choose from them. It's pretty useful. I select this one and hit enter to select it. I hit tab to go forward. Let me enter some more data quickly. Now, I'd like to copy these two items. We can choose the copy button here for the shortcut key Control C. Or we can click with the right mouse button over the selected range, which invokes the context menu and choose copy from here. This context menu is very useful. It contains the most frequently used features depending on where you invoke in how these data are on the keyboard. I click here and hit control V to paste the data. I hit escape to stop the selection. You can also use the autofill feature on multiple cells like this. I hold down control to copy these values. Now I select the cells containing the formulas. I can drag this corner node down, or I hit Control Z to undo. A newer versions of Excel, I can double-click on this corner node to autofill. And as you can see, formulas have been copied over. I double-click on this cell and you can see that Sarah Frances have been also updated. High heat escaped to quit. Oops, I forgot this field. Let me just enter some variations. At the formula fields have inherited the correct data type, but some of these cells have not. I can select this column, set currency datatype, and decrease decimals. Good. Now I click on this cell at the bottom of the table. Let's click on this autosome button. Excel boots the sum function into the formula. Function can make simple or complex calculations or transformations on its arguments. Arguments are inside parentheses. So foreigners can contain not only Sara references and operators. But functions as well. You can combine these elements and get pretty complex formulas. You can even ambit functions inside other functions. We will talk about Excel functions in more detail later. But to have the sum function does, is to add up values in this range. This is how you can refer to multiple or says, or a range of cells. I could also choose another range with multiple columns and rows. But let's go back to this one. I hit control enter. And we get the sum of this column. By the way, if you select arrange which contains numbers, you can see the result of this frequently used aggregated functions here. Let me format this cell. Now I click on this one. Let me show you the insert function result. I click on this effects button. The insert function window pops up. By default, you can see most recently used functions here. Excel has over 450 functions in different categories. You can choose by category, or you can use the search field. I'm looking for the function that returns the max value in a range. I click on and go to search. I need this max function. I hit OK. On this window, we can see the arguments of the function. Excel has put a default range in this argument feared. And you can see cell values here. But you can select another edge if you like. Anyway, the default value was good. Let's use this range. I hit OK. And we got the max value of this come. Okay, what else can we do here? We can insert a column. I select this one. Practical were the selected range to involve the context menu. I could choose Insert here. But let's use the shortcut key instead. I hit escape to hide the context menu and hit control class to insert a new column. This will be the product group. I can also insert row or actually multiple rows this way, right-click. Insert. I undo, redo this pattern. If you use arranged frequently, you can name it and use that name as a reference. Let me select this range. I go to foreigners and click on defined name. These default settings will be just fine. I hit OK. Now let's create another name for the header. I will call it table header. You cannot use space characters in the name. I hit OK. Now if I click on this name box drop-down list, I can choose the name range and it will be selected. You can also use this name and age type of reference wherever you can refer to Sarah arranges. For example, you can use this name in the Sum function as an argument. We get the same result in a more readable format. Let me delete this row I selected, right-click here and hit delete. Good. Now let's talk about two other frequently used features, Sort and Filter. Makes sure your active cell is inside the column you want to sort by. I go back to the Home ribbon and click on this sort option. Table rows have been sorted by this come. Now I click inside this column and choose Sort Largest to Smallest. Pros are sorted by gross amount. I can filter out data by clicking on this menu item. There is also a shortcut key for this control shift L. Now we have the sphere turtle goes on each column header. Let's say I'd like to see only item one transactions hit OK. Now the other rows have been Heaton. I turned this filter feature of. Now let's create a chart from this table. I go to the insert ribbon from mixer version 2013. You can see this Recommended Charts button. Click on it. As the active cell is inside our table, Excel analyses are data and suggests different kinds of charts. I choose this one. I hit OK. And we have a chart. It's very basic, but it's something you can start with. The chart selected. We have an extra menu item here, design. You can choose from pre-defined charts styles. I choose this one, and I select this color scheme. If you double-click on the chart, you can choose from tons of settings on this Format Chart, pan on. The chart has many elements and each one of them has its own settings. We will discover many of these properties soon. We have been working on this sheet one. But in most cases you need extroverts sheets inside the overbook. You can create another worksheet by clicking on this new sheet button. It's a good practice to place different data tables on separate worksheets and named them properly. I double-click on this one and given name. At the bottom right, you can see the zoom slider. You can zoom in and out by clicking on the plus minus buttons. Or you can use the shortcut keys control alt plus and control odd minus to achieve the same. So I hold down control and ought. And I hit the plus key several times, and I release control and art. Let me pull the slider back to 100%. All right. I think we have covered the basics. By now. You know the user interface. You know how to navigate on worksheet, how to edit Sass, format cells. You know the terms datatype, reference, data range, Formula, AutoComplete, autofill. You know what a function is, you know how to copy and paste, sort or filter data. You also know how to create a simple chart. Actually, you already know a lot. In the following lectures, we are going to get your Excel skills to the next level. See you in the next video. 5. Highlight Max Value on a Chart: In this lecture, you're going to learn how to create a chart like this, where the maximum value is highlighted dynamically. This is kind of a warm up exercise, but still you will learn a lot about Excel. Now let's get into it. Let's click on cell A1 to make sure that it is the selected cell. Let's create a simple datasheet or we have the days of the week in column a, the corresponding sales data in column B, I type in the abbreviation of Monday. I click on cell A1 again. I grab the bottom right corner of the cell by clicking and holding the left mouse button and pull all the way down to sell a seven. And I release the mouse button. This autofill option may not work in older versions of Excel. If you don't get this result, please type in the days quickly. I click on cell B2 and type in values for each day. I entered the value heat, enter and enter the next one. Now let's create a header for this data. I flick on row one on the side to select the entire row. We could right-click to code the context menu and choose Insert from here. But let's use the shortcut key instead. Often, there is a key combination on the keyboard as an alternative to cause certain actions in Excel, using shortcut keys can increase your productivity. So I tried to teach you as many as possible. And you can decide if it's worth to remember. If you have a selected row or it can be a karma as well. You can hit control plus to insert a new one. I click on cell B1 and enter the column header. I hit the tab key to finish the data entry. I, if you double-click on this column separator, the column width will be set automatically. Let's click on this B header to select the entire column. I'm going to make some formatting on this one. On the home ribbon. In the alignment group, I click on the central atom. I would also like to see the currency sign. Now you could click on this accounting number 400 button. But I'd rather choose from this dropdown list, the currency. This button, we can decrease the number of decimals. Great. My click on cell B1. Let's make this header attacks board. You can either click on this button or as you can see, the shortcut key in the bubble, you can hit Control B. Let's select column a and make the entire column bored by a single click. Good, very basic formatting. You will do this all the time. Let's click on one of the cells with the data. We're going to insert a chart. If you are in a date range, Excel, we realized this and make a bunch of settings automatically, so you don't need to do them manually. And let's go to insert and click on this Recommended Charts button. This feature was introduced in version 2013. You don't have that, Don't worry. He won't use this. I just want to show you as an option for creating a new chart. Excel looks at your data and offer different chart types. You can click on them to see on the right how certain one looks with your data. But I just hit cancel. Now, let's insert a simple 2D bar chart from here. As you can see, Excel has automatically identified data and laborers and put them on the right places on the chart. This is create. However, it doesn't seem kind of impressive, right? You can see charts like this all the time. It's just boring. It doesn't get the viewer's attention. In some extra effort. We can make it attractive. Let's see how to do that. Let me drag and drop the chart somewhere here. It's important to grab its frame when you want to move the whole chart. That's because a chart has plenty of elements inside and it's easy to click on them. If you mess up something, he does Escape key on your keyboard to quit from the action. And if it's necessary, hit Control Z to undo your last section. And notice that we got a new menu item design. This is because we have the chart selected. Here you can see chart styles. Let's click on this button to expand the list. If I hover over the items, I can see how my chart looks in a certain style. I'd like to set this dark on, so let's click on it. If you have an earlier version of Excel, you may not find the specific style. Don't worry, I'll show you in a minute how to create one like this from scratch. Discharge is definitely an improvement comparing to the default. It has a gradient background. The bars have gradients as well, plus they have shadows around. The layout has enough contrast to make it nice, but many people will probably find this feature in Excel. So we need to do some more to stand out. Let's click on this change colors button. Here. You can choose from different color palettes to customize your chart further, let's choose this monochromatic palette. Very good. Now let me show you step-by-step how you can create a chart layout like this. Even if you have a new version of Excel, it's good to know how you can customize the tiny details of your chart to make it unique. So let's click inside the dataset and create a standard bar chart again. Let's start with the background. Make sure that only the outer frame of the chart is selected. Right-click on the chart somewhere here, and choose format chart area. By clicking on other location of the chart, you may see a different menu item here. Find the one that starts with Format settings panel pops up on the right. This design was introduced in version 2013. In earlier version, a dialogue window pops up, but the structure should be the same. I hope you find the option that we will use and make sure that the paint bucket button is selected. Chart option is shown above. If it's not, choose chart area from the drop-down expanded fear group, and choose the gradient field option. From the preset gradients drop-down, choose this gray bottom spotlight. Tab shows radial. Let's click on this middle step. For the sake of simplicity, we delete this one. So click on this remove gradient stop button. Matt is first step defines the color of this bottom center area. Let's click on this color drop-down. And from the second dad come, choose this thirty-five percent gray. Now let's click on the second step and choose this 15% gray. And let's click on the chart title element. This is a textural element. So let's go to whom will you do the formatting here? From the font color drop-down. Let's choose this 5% light gray. Let's stay the font size bigger. I right-click over the title and choose font item. Onto character spacing tab, I set the expanded hit OK. Now we have some space between the letters. And let's make the title board. Now click on the vertical axis to select it. Let's set it's colored 15% light grey. Let's select the horizontal axis element. We can now just simply click on this button because it's color will be the same as the vertical one. I would also like to change the color of this horizontal axis line, which I can do here. Let's set it to the thirty-five percent gray. I click off the chart to see how it feels. Good. We also need to change the grid line column. Let's select them by clicking one of them. Makes sure that you see these nodes here. Let's set it to the thirty-five percent gray tone. Now click on one of the data bars only once. So you see the selection nodes. If you click again, you will select only the single bar. We don't want that because in this case, we will change the property of this one specific bar. We want to format the whole series. So I click outside and click on one of the bars again. Let's go to feel options. Click on gradient field. Remember, the first gradient stuff we are define the color of the button. I choose this 50% gray. I click on the other stuff, which refers to the top and said this twenty-five percent gray. And click on this effects button. Expands shadow item. And from the presets, Let's choose this outer centre on. Now we have a slight shadow around the bars. We are almost done. We just need to take the bus wider. Click on one of the bars, go to Series options, and set the gap width slider to 100%. Excellent. We have a pretty similar look than the other one. And let me close this banner and see how we can highlight one of the parts with a nice color. And you work with data in Excel. It's a good practice to format your dataset has an Excel table. It has many benefits and make things simpler. So let's click inside our dataset and go to Insert. Here, you can see this table button. You can also see it shortcut key, Control D. Let's look on this table button. The create table dialogue pops up. Excel automatically sets the range for the table. It's from cell A1 to be eight. Don't worry about these dollar signs in the formula for now. We'll talk about it later. This checkbox shows that our table has header, which is this. Let's hit OK. Now before we look into this new table format, notice that our first chart has become wider. That's because r can be width has changed. It seems that our chart is linked somehow to our table. I click on this undo button at the top so you can see the change. So we descent direction. I went one step back to the state before the XO table creation. Let's select our first chart, clicking on its frame. Right click somewhere here. And from the Context menu, choose Format, Chart Area. Click on the Size and Properties button. Under the properties group, you can see the default setting, move and size which cells. As discharge overlapped column B, its width was affected by the width of the column. We don't want this. So let's click on this don't option. Now I click again on our dataset. Go to Insert, click on table and heat. Okay, good. Chart has not been affected. I click on undo and redo to check that. That said this option quickly for the other chart to. So we have this new table layout. Let's add header for the days. I grabbed discount separator to take column B wider. I don't like this default blue table layout. Let's change that. Makes sure that the selected cell is inside the table so you can see the design menu item. Let's go there. Let's open this table styles dropdown. I click on this one. Good. Now I'd like to color the highest bar on the chart in a way that whenever data change in the table, the color formatting automatically follows that on the chart. So how can we do that? Currently, we have one data series shown in column B. We need the second one, so-called ghost data series in column C. Let me show you what I mean. Currently, Thursday is the day of the week. If i enter 400 in this cell C5, See what happens. First, article has been extended with one extra column automatically. Second, we got a second bar on the chart with a different color. If you didn't form it a later set as an Excel table, this wouldn't happen. We could also add extra rows to our table that Excel would realize. So this is one of the benefits that the Excel table format offers. Let me close this former chip panel so we can focus on our chart. Also, lets click on the second chart, heat hold f ten to code the selection pane. Here you can see your objects on this worksheet. Let's click on this eye icon to hide the second chart. And I close this Selection Pane. Good. Let's set a bright color for the second bottom left mouse click on it only once. Right mouse click on it. And from the fear drop-down, choose more fear colors. We get the colors, dialogue. There are two tabs here. You can set your color in different ways. On the standard tab, I click on the slide green one. You can pick any color you like. Heat. Okay. We're getting somewhere. Let me rename this column header. I take this column wider. I select it and hit center aligned. We don't care about the number format for this column. Now, would it be possible to shift this green bar right over the gray one? Yes, it would. Select the green bar. Right click on it. You could select Format datapoint menu item, but it's just faster to double-click on the green bar to call the former datapoint panel. Make sure that you are on the series option step. We need the series overlap slider to achieve our all. Watch what happens as I am dragging it to the right. This is exactly what we need on 100%, the green bar, highest degree one. Very good. So we should achieve somehow that we show value in column C only in that row where the value on the left in column B is the maximum. It's time to use some simple Excel functions to solve this. But what is an Excel function? Think of an Excel function as a black box. It receives some input data, does a calculation or transformation on the data, and it returns a result. We don't need to know how the function does its job. We just need to know the name of the function so we can call it and the input parameters structure it accepts. So each Excel function has a unique pain. There are hundreds of them in Excel, nobody knows all of them. You will probably use the 1020 most popular ones. We will go through them in a lecture later. In this case, we need a function that can return the maximum value from column B. Let me delete the value from this cell. By the way, you can use the arrow keys on your keyboard to navigate between cells. So you don't necessarily need the mouse. I used the mouse in navigation, so you can follow along easily. Anyway, to make calculation in a cell, we need to enter a formula. Paul formula in Excel starts with an equal sign. So cell C2 is selected and I start typing in this formula bar here. We could also type in this formula in the cell directly, but you will see better this way. So I start with an equal sign and I type in the function name that will perform the operation. It will be the max function. As you type Excel shows the matching functions below. You can also see in the bubble what the selected function does. Now let's type in an opening parenthesis. The hint bubble starts to show the parameters of this max function. I. In this case, this number one parameter doesn't say too much, but in many cases, this hint bubble is useful. I select the range for the max function like this. I click and hold on cell B2, drag the mouse cursor to cell B8 and release. Excel puts this table camera reference into our formula. A table reference like this makes your formula dynamic. This is another reason to use Excel table. You can add new rows to your table and you don't need to update your formula. I tap in the closing parentheses and hit Enter. The formula, returns the correct max value, 400. This is good. If I click on other cells in column C, You can see that Excel has automatically put our formula in each cell. This is another useful feature of the Excel table. So we don't need to copy the formula into each cell. And let's go back to c2. Let me drag this bottom frame of the formula bar to make it bigger. You can also click on this button to expand and collapse it. Now, I will break down our formula into separate rows. You can enter line breaks in the formula by hitting Alt Enter. So I push this max function here. We are going to use another function to show this max value only in the right cell. Let's use a conditional statement. The function name will be, if you can see its description in the bubble. I type in the opening parenthesis. The first parameter is a logical test. If it's true, the function will return the second parameter. Otherwise, it will return the third one. That's put together our logical test here. So the condition will look like this. The max function Resort is equal to cell B2 value. I click on it and examples the reference in the formula. Then I enter a comma to separate the next perimeter. I hit enter for the line break. So if the above condition is true, then give back cell B2 value. So I copied this Sarah friends to the keyboard with Control C. Don't worry about the syntax of it for now. I click on the next line and hit control V to paste it. I enter another command for the next parameter. Pete out and therefore a line break. And let us define what value to return in case the logical test result is false. We want a blank cell. So I type in to double quotation marks, that marks and empty text value. Let's not forget the closing parenthesis and hit Enter. And it's working. Notice that we have the same formula in each row. Now let's test it. Let me enter 424. Wednesday. Column C is updating and the chart is showing the new data. Perfect. Let me get rid of the line breaks in the formula. I use them for demonstration purposes only. Let me set back the formula bar i. If we have two eco maximum values, the chart will look like this, which is correct. So using Excel table, we can have this formula that is very general. It has a clean syntax and it does what we expect. Us, exert table wherever it's possible. Now let me show you a trick you can use to analyze the formula. It's useful when you write your own or when you need to look into other people's work. I select this part of the formula in the formula bar. So it's basically the max function. I hit F9 on the keyboard. Excel calculates the selected part. The result of the MAX function is 420. Now let me select the other part of the logical tests or condition. Hit F9, and it shows the value of cell B5. Now let's select this logical test. Hit F9 again. It returns true. If you use this feature, makes sure that you hit escape on the keyboard when you're finished. Hitting Enter would overwrite your formula with this value, which is something that you probably don't want. So I hit escape. Now let me show you another function that you can use in testing. I'd like to have random numbers in column B, so we can test chart behavior quicker than entering values manually. So I click on cell B2, select Value and overwrite it, the cosine. So I started a formula. At a function that returns a random number is RANDBETWEEN. I start typing and as soon as examiners to narrow down the search, it's there. At this point, I can hit Tab key and the function gets into the formula along with the opening parenthesis. Now the RANDBETWEEN function expects two parameters. We need to define the range of the random number. Basically. So I enter 100 for the first parameter, heat comma, and 500 for the second one. At this point, you can hit tab without the closing parenthesis. And Excel will complete the formula. If I check cell B3, it will still show constant value, not the new formula. Actually, we need a clean column in the table to get the autofill feature work. So let me select the new formula in cell B2. Privately connect, heat copy. Select this range. Pete, delete. Click on cell B2, clicking the formula bar, right click and hit Paste. Now, if I hit enter, the formula gets into all the other cells. Great. Now we have random values in each cell. Friday has the maximum value, and the chart reflects this. Now I hit F9 on the keyboard. If you are not inside a formula, Excel will recalculate. All says. Results seems correct. Now I'm hitting F9 to test the chart with different random values. Nice. Let me close this Format Shape panel. It seems that our chart title has lost in the process. Let's click on the chart. Click on this chart elements button, and check char title. Let's select this default text. Go to the formula bar, enter an equal sign, and the con this header cell B1. Hit Enter. Good. Now our chart title has been linked to the column header. What do you think is discharged better than the default one? Now you can save your work by clicking on the Save button. You can see the shortcut key as well, Control S. Or you can hit F2 to code the Save As dialogue window. Let me save this file quickly. You can download it from resources if you need it. Now let me show you an important setting in Excel. Let's go to File. Button, click on Options. Excel Options window pops up and click on save. Here you can see the save auto recover information every five minutes. Make sure you have this option checked. This can save you a lot of headaches in case of an unexpected power outage. I like to keep the setting as five minutes, so I won't lose more than five minutes work. Whatever happens. If you don't have privacy concerns, you may want to set the cloud service location for your auto recover file. Because keep in mind, your local storage can break at anytime. It's a very rare situation. But if it happens and you don't have backups on other devices, you know what I mean? Hit OK. And let's say that you'd like to change the color of this green secondary bar. Double-click on it called Format panel. Go to the fear tab. In the series option. Make sure that you select this cos theta series max value. Why is it important? You can individually format each bar on the chart. As we have only one from the green. We cannot see on the chart if the whole series is selected or just one for Sunday, choosing the series from the series Options drop-down is the safest solution. Now let's click on the color drop-down. It more colors. And I choose this slide purple. Hit OK. I'm hitting F9 to recalculate and test a new color. All right, let me show you one more thing before the end of this lecture. We can hide our ghost data series by clicking on column C. Right-click and choose Hide. Wait a minute, are highlighted, bar has disappeared. Actually, we need to make a setting to bring it back. Right-click on the chart somewhere here. Click on select data. Select data source dialogue pops up. We will work on this later, but now we just need to click on this button hidden. An empty says, we need this last option. Check it. And it OK. Hit okay again. And we are good. Our chart works like a charm. How can you unhide hidden column? Select the columns on both sides, right-click and unhide. All right, we have completed this lecture. If you are a beginner, I know there were so many new things to remember. I hope you manage to follow along and you liked the result. It took some time to prepare this chart now, but it's really just a few minutes extra work if you are already know the drill. We are at the beginning, I blend to show you more cool things that are possible in Excel. I hope you will enjoy this course more and more as we are diving into Excel. See you in the next video. 6. Conditional Formatting: In this lecture, I'm going to show you how you can format your data using conditional formatting. This is a great tool for highlighting certain parts of your data quickly. Now let's get into it. I listed book categories in column a. We have last month's sales data in column B and actual amount sales data in column C. These are randomly generated values. So please don't draw any conclusion based on this table. As usual, you can find this excel file in resources. You can find this conditional formatting button on the home ribbon. But before we use it, we need to select the range of data you want to apply the formatting on IT. Consent C2. Go down the left mouse button, drag the mouse cursor down to the bottom and release. Now I have this range selected. Now I click on conditional formatting. In this upper section, you can create formatting rules quickly with a few clicks. We can highlight numbers based on these logical conditions. We could create rules based on texts or date. Let's create greater than rule. So I click on this one. Here is the rule description. And Excel boots a default value in this field. I'd like to highlight cells that are greater than 10 thousand. So I enter this value and I like to have a different color scheme. This green one. Hit OK. And we got the SAS formatted based on the given condition. Clicking on this Manage Rules item, we can check our existing rules and change them. By default. This form shows formatting rules for current selection. We don't have any rules on this active cell. The list is empty here. Let's choose this worksheet from the list. And here is R0. Clicking on the Edit Rule button, we can customize it. Each Formatting Rule has a type. And here you can change its settings. We could change the colors here. For the background, you can choose any color you like. And for the text to I hit cancel. Close. If you want to delete rule, you can use one of the clear rules menu items. I click on this one and the rule is gone. Now I select this range again. This time I like to see the top 10% says, let's use red border this time. Here we go. Hi, I'm due with the undo button. Highlight this data bar feature. You can choose a gradient field. For a solid sphere type. I click on this orange. If you want to, you can create these orange bars in a separate column. I select column D. Right-click, hit insert. I enter a simple formula here referring to actual Mencius values. I hit control enter. And I pulled the corner node down to the bottom. Or a new versions of Excel, you can just double-click on this corner node. I go to Manage Rules. Selected. Click on Edit Rule button. And I check this show bought only option. I hit. Okay? And okay, again, I select column C and choose clear rules from selected says. And we have a layout like this. I select last month's sales data. You can apply colors case on these values. The darker tone means higher value, but you can customize it, of course. Now I select these variation data which show the actual Man series increase or decrease compared to last month. This time, I choose from these icon sets. If you wonder what's the condition for the icons, let's check it. Manage Rules. Click the rule. Edit tool. These are the default settings. I'd rather like to have a 50% for the green. And below 0%. It should be read. I hit okay. And okay, again, not exactly what we expected. Let's go back to the room. The conditions are good. I think I ran into a software back here. If you have the right icons in the table, that's good. But I tried to use a different approach to get this done. I said the number type here for both. I enter 0.5. which is basically 50% for the green icon. And a 0 for the yellow icon is fine. Hit OK. And I have the right items. Let me copy this formula to the right. I select only this cell. And clear rules from selected SAS. High copy down the formula. Go to Data Bars and choose this one. I go back to Manage Rules. It rule and check this show bar only option. Hit OK. I take the column width wider. So this is another way of showing variations. Now let me duplicate this data sheet. I hold down control on the keyboard. Click and hold on this sheet title. Drag the mouse cursor here and release. And I have a copy of sheet one. I double click its name. Let's call it. She'd do. I choose clear rules from entire sheet. I select these two columns. I hold down control as I'm clicking on their headers. Right-click Delete. Okay, so we have our original data. Now let's say I'd like to format an entire row based on a condition. This is something that you cannot do with these menu items. But you can create pretty complex rules with this new rule item. But first, let's select the entire data range. And now the new rule. We need this last item. Use a formula to determine which cells to format. I just need to create a formula or a condition here. Let's create the formatting first. I click on format. Let's go to the fear tab. And I choose this green color. Hit OK. But what's the syntax for a formula like this? Let's start with the symbol one. If actual month's sales data is greater than 10,010, highlight the role. I start the formula with an equal sign. Actual amounts data are in column C, So we need to refer to that column. The way to do that is to enter a dollar sign than the column letter. And the row number where values start. This is row number two. We need the logical operator greater and the value 10 thousand. This conditional formatting rule will go row by row and check if the value in column C is greater than 10 thousand. If it is, then the condition will return true for lateral and these green background via b applied. Let's see if it works. I hit OK. And it's working. And let me create a second rule to highlight the actual amount. Stop Salem. I select the range. New rule. Use a formula. Set the format. Feel. I choose this orange color for the background. Go to found. Choose white for the text. Heat. Okay? Now the formula will look like this. If dollar C2 equals the max value, I use the max function. I select this range. I closed the function, then use this formatting. I hit. Okay, perfect. I go back to Manage Rules. Now notice that there is a hierarchy between rules. Our last rule is on the top. It means that Excel, we process that last. This is important when you are formatting rules overlap each other. Just like in this case, if I change the order with this arrow, I hit OK. Then you can see that are greater than 10 thousand rule green background-color overrides the orange one. High-heat Control Z to undo. All right, I hope you learned something useful. See you in the next video. 7. Map Charts: In this lecture, you are going to see how standard XOR map chart looks. And you will learn how to create a better, more flexible, more readable, and more compatible one. You might want to consider using a map chart and you have geographic location-related data. Now let's get into it. Can download this map chart Excel file from resources. But you don't necessarily have to. First, let's see the standard map chart. You don't need to follow along now, just watch for a few minutes. Here you can see the list of states in the United States. Let's say we have revenue data from each state. I'll tell you in a minute why I highlighted these fields. Let's see what I can do with this table. Cursed. I click inside the table somewhere. So xo can recognize the data structure. Then I go to Insert, click on maps and click on this one. Excel creates a heatmap from our table, admin track and drop it here. I replace the title quickly by double-clicking on it. Let me take discharge bigger by dragging and dropping its corner node. If you rarely see a chart like this, it may seem impressive. But let's see why I don't use it. First of all, older versions of Excel cannot handle it. Second, it uses the Microsoft being meg database through the internet. So you need to be online. Now let's take a look at this grey area. Excel says no data for Colorado or for argon. So we do have data for these two states too. The next issue we dismiss chart is that it can have difficulties to recognize some locations. In this case, we can add some extra hint to being like typing in us before the state name. Now it shows data for our console. Let's try this for Colorado. In this case, it doesn't work. Let me delete this state name. Now it's working. You get the point. You may face with challenges. If you use this feature. If you look at the list, there were other states to the bean couldn't recognize by default. I here comes the main issue. The main reason why I don't use the standard map chart. Tell me which state perform the best in terms of revenue. I have no idea. Now let me show you a great feature of Excel. And example for conditional formatting. I select column B by clicking on it. Here. We are on the home ribbon, and I click on this Conditional Formatting dropdown. I choose top, bottom rules. Top ten items. As you can see, Excel has already highlighted the top ten revenue, which is great. I need only the top one, so I replace this number with one. Here. You can choose from several different formatting options. You can even customize it, but the default setting is just fine. I click OK. Now if I scroll down, we can find the top revenue. Wyoming. Based on the map. I wasn't sure which state was doing R. So let me sum up why I don't recommend this char type. This feature is available only new versions of Excel. It requires internet connection. There can be issues with identifying some locations. And the most important reason against it is the readability Shou. All right, let me show you another nonstandard adoption for geographic data visualization. Let me click on the sheet on worksheet. Let's say that we have regional revenue data. By the way, I didn't find a way to get being mapped, recognize these US regions. Anyway, let me add the revenue Khan. I'm going to use the RANDBETWEEN Excel function to generate random revenue data. This will have the testing of the chart. So after the equals sign, I type in the function name. Left parenthesis, let's set the minimum revenue to 100 K. I hit comma to separate the second parameter. And let's set the maximum and new to 500 K, closing parenthesis and hit Enter, and we got to random value. Good. Now I click on the cell and double-click on its bottom-right corner. Pixel, automatically post this formula into the cells below. It recognizes where our table ends. With our revenue says selected. I click on the Format drop down and choose currency. Become is too narrow. That's why you see these hash marks. I double-click on discount separator to resize the column width. Let's decrease the number of decimals by clicking on this button. Good. Let me align the Revenue title to the right, this button. We have our simple data table. Let's see how we can visualize these data. Make sure that you click somewhere here outside the table. As a first step, we will create an empty chart. We don't want exert to recognize any data. Let's go to insert. And from scatter Charts, choose this one. You will see in a few minutes why we are going with this one. Let me drag and drop it here. And the trick will be to set a map image background for discharge. Then we will show the revenues as different size circles on the map. Double-click on the chart area to call the Format Chart Area panel on the right. You can also right-click on the chart and choose this menu item. Make sure you are on the sphere tab. And under the field options, click on picture or texture fill. We can set a picture source here. So I click on insert. You can look for a map on the internet with the second option. But I prepared a map for this lecture, so I click on a file option. Please find this US regions JPEG file in resources and downloaded to your computer. I click on the image and hit insert. And it has been loaded as background into our empty charged frame. It's been distorted a bit. Let's take the frame bigger by dragging this node. Something like this. I intentionally set grade dawns for the regions. I wonder revenue data to stand out. So let's take this map paler by increasing its transparency. I drag the slider to, let's say 50%. Now we can hide the grid of this worksheet I go to view. Actually, we need the server to be selected. So now I can uncheck gridlines. Okay, and how can we show our data on this map? Let's right-click on the chart and choose select data. This is where we can assign data source to our chart. Currently it's anti. I click on the Add button. The edit series window pops up. Series means data series in this case. And data series basically means a column in our table. So our revenue come with those numbers is a data series. Anyway, you can see how a scatter chart looks like. It defines a grid with x and y coordinates. The horizontal axis is the x-axis, the vertical axis is the y axis. If you have X, Y coordinates for a data point, you can place it on the screen. By default, we have this blue dot here on position 11. So if we provided x, y coordinates for this regions, then we could place a blue dot for each of them at the right location on the map. Let's find out this coordinates. I hit Cancel and cancel for now. Let's add two extra terms, x and y here. Let me feel these cells read. Once. I type in one in cell C2, I hit control enter to finish the data entry and stay on this cell. I double-click on this corner node for autofill. I click on cell C2 again. Drag the corner node to the right. Click on the cell D2, and double-click on this corner node. Let me select these two header says, Go to home and align, right? Good. So we have two new comes, basically two data series, x and y. This is what a scatter chart needs as an input. So let's right click on the chart area. Choose, Select Data. Click on add. Let's click on this series x values field. Now we can select the x values in the table. I click and hold on cell C2. Drag my mouse cursor all the way down to cell C6. And I release the mouse button, and I click on this y values field. Select its content. Hit delete. Now select the y-values in the table. It okay. Now the chart has something to show the series one heat, okay? Has all the regions in the table has the same 11 coordinates. They overlap each other here. That's okay for now. Let's change our coordinate system so we can better locate the regions. Click on this horizontal axis. Let's go to the series Options tab, and click on axis options. Now the auto options are turned on, but we don't want Excel to change these coordinates system dynamically. So let's set fixed values. I enter 0 for minimum and ten for maximum. Horizontal axis calibration has changed on the chart. Let's set the same for the vertical axis. 010. Good. Let me take the chart size bigger so we can see a more detailed grid. Very good. So we have a ten by ten grid. Now it will be easy to set the coordinates for each region. I start with West to 4x, dx, and aid for the y. As you can see, we got one of the blue dots in position on the chart. Now, I enter coordinates for each region quickly. Feel free to copy them into your worksheet. We can change them later if you need to. Let's select the chart frame and click on this chart elements button. We can hide the horizontal and vertical axis. So I uncheck this one. I'll leave the gridlines Check For now. We might need to fine tune x-y positions later. Good. Let me add the proper title to our chart. Now the next step is to assign the revenue data series to our chart. Actually, we need another form of scatter chart that accepts a third parameter, that will be the revenue. So let's right click on the chart. Select Change Chart Type. We are going to use this bubble chart. Click on it. The first one is selected, which is fine. Let's hit OK. Now coordinates have been messed up, but we are going to fix them quickly. Let's right click on the chart, choose, Select Data. Click on series one, and hit edit. We just need to set the fear it's properly. Let's look on serious x-values feared and select the range on the table as we did last time. It seems that the SUS y-values we, it contains the right reference. Let's select this last series bubble's size, weird, delete, and select the revenue values in the table. Good. Pete. Okay. It okay, again, now we have these circles that represent revenues at the right spots. As our revenue cells have the random generator formula, I can hit F9 on the keyboard to recalculate them so we can test the chart how it looks with different values. It seems that note East x-y positions need correction. Let me fix them. Good. And that changed southwest a little. At this point, I think we can turn off the grid lines. Let's work on formatting. Click on one of the blue circles to select them. Quote borders. Click on solid line. Choose white. And let's turn down the width to 0.25. bt. It will look like this. Select the surplus again. Go to this effects tab. Expand shadow settings. In the presets, drop-down. Choose this one. And the circles seem like they are on the second layer above the map. Now let's select the chart frame. Go to size options, and check this lock aspect ratio checkbox. The proportion of the map seems okay, so let's look at using this option. From now on we can resize our chart, keeping the aspect ratio. Let's expand this properties group and choose this last option. This will make sure that the chart doesn't move or get resized, whatever we do on the worksheet behind. Now let's add the feature to our chart so it shows the biggest revenue with a different color. To achieve this, we need an extra column in our table, a so-called ghost series. Go series. We derived data from our existing ones in a way that we can use to display X-ray information on our chart. We are going to show the max revenue in this column. So let's add the column title. We are going to use a simple formula here. As usual. Let's start it with an e cosine. We are going to use the IF function. Its first parameter is a logical test, which will look like this. If this cell B2 value with ego to, let's use the max function and select this range. Closing parenthesis and comma. So if the value in this row, in the revenue column is equal to the max value of this range. Then return this value from the revenue come because it's the maximum one. I hit comma and return an empty string or text. Otherwise, I type into double quotation mass, which we result an empty cell, closing parentheses and hit enter. Now I select cell E2 and double-click on this corner node to send this formula down to the bottom. Something is not right. Let's check the formula in the other cells. As you can see, this range inside the max function is changing. This is because we use relative Sarah Frances in the function argument. If you copy a formula with relative Sarah Frances into other cells, Excel. We change them according to the new position. In case of the left side of the logical condition, this is good. In this row, we want to compare cell B3 with the max revenue. But in case of the range inside the max function, it should be fixed or locked. We need an absolute cell reference here. So let's click on Sally to select this range B2 to B6 and hit F4 on the keyboard to change it to absolute reference, these dollar signs represented by if we copy the formula downwards again, I'm using a different method this time dragging the corner node down like this. Then our formula, we'll do what we intended. I hit F9 to recalculate revenue Fields. And Max ref column is showing the expected result. Let me use the Format Painter to format the max rave calm. I click on this cell, I click on the Format Painter button, and I select Sass in the max ref. Come. Let me take its sides wider and align the title to right. Alright, so we have a ghost data series that shows value only for the top region. Now let's see how we can use this on the chart. Let's write the chart area somewhere here and choose, select data. It's time to give a meaningful name to the Series1 because we will have a second one. Select it and click on edit. Is the cursor is blinking in the empty Series Name field. Let's click on cell B1. Hit OK. Now we know which data series This one is. Let's click on Add. To add our maxflow f goes data series to the chart. This cell, Y1, Y2, be the name. Click on the X values feared and select x values from the table. Delete this default value here, and select y values from the table. Delete this default value from the bubble size field. And select this range in column E, heat. Okay? Now we have our second data series setup. Heat, okay, again, and this is the result on chart. It looks nice, doesn't it? I'm hitting F9 to recalculate random revenue data. And the orange circle is changing position dynamically based on the data in max ref column. So this is the trick, how you can add the second layer to your chart to different colour. Now let's select the orange circle. I'm setting the same wide borderline for this element. Good. And these are default colors of Excel. Of course one more. I usually use, Adobe is great online tool to choose harmonic colors for my charts. You can see the website address here, but I put the link in resources as well. I use the complimentary color harmony rule, select two contrasting colors that work together. You can drag these nodes to change the hue, saturation, and brightness of your cars. And you find the color codes here that you can copy and paste it into Excel on the colour panel. Now let me go back to Excel. I have a nice color combination that I will set. So I select the blue data series first. Make sure you see these selections on all of them. I go to File Options and double-check here if the revenue series selected. I go to the code drop-down, select More Colors. And on the custom tab here at the bottom, I type in the color code I chose. I hit OK, and it's there. Now I click on the orange one. I double-check if the whole max F Series is selected. I go to more colors again. And I type in my other color-code. Pete. Okay. It looks like this. I'd like to show the revenue data on the chart. Let's right-click on one of the green circles and select this add data labels, item numbers have appeared, not the ones we want. Let's click on one of them to select. In Label Options. You can see these options. It seems that by default the y value is checked. That's why we see Y values on the chart. Let's uncheck it and check the bubble size. Now we have the right numbers. In labor position setting. Click on Center. Now let's set the white color for the text on the home ribbon. Good. I'm hitting F9 to test again. You see this naughty circle. The value doesn't fit. Now I don't want to take text size smaller. I'd ride the light, take the circle bigger. Let's select it. Go to Series options. And let's increase the scale bubble size value. I think 140, we'll be OK. You can also have an option here. You can experiment with what I stick to the first one. Now I select one of the number laborers on the chart and make them bored. It's getting more readable. I think. I'd also like to take the stopped value texts bigger. So I click on it. Don't forget that we have today two series that are overlapping each other, S for the labels. Well, if we check it in Label Options drop-down, we have only data labors for revenue. Now, these are the green circles. But you see this on the red circle, to don't forget, there is a green circle behind the red one. However, it's a good question why the red circle doesn't cover the green circle label. But that's the way how Excel works. We need to turn on the data label for the red circle too. So let's select the red circle. I double check here. If it's the whole max rep data series, right-click on the red circle and choose add data labels. Let's select the second label. Make the same settings as for the green one. Has an extra step. I increase the font size here to 14. Now as you can see, the second label overlaps the first one. We need to set the background fear for the second one to hide the smaller label. So I go to File Options. Click on solid field. And from the drop-down here, you recent colors at Become the red one. Good. I hit F9 to test again. I think we could improve the layout with a few extra settings. Let's select the chart title and increase the font size to 18. And let's make it board. I right-click on it, select Font. And on the Character Spacing tab, I set spacing to expand it. Heat. Okay. I think we should also change the position of the background map. So I select the chart frame in the field options for this picture and texture fill option. Let me just increase the transparency to 60%. So we have these offsets settings here that can fine tune the position of our map background image width. I play with the settings a little. I hit F9 to recalculate. I'm satisfied with the result. Now let me show you some color variations. I won't talk too much this time. I'm going to perform the same steps in formatting that we did in this lecture. I select the chart, makes sure that only the outer frame is selected and hit control D to duplicate the chart right on it. And choose cut. Now I create a new worksheet by clicking on this plus button. And I hit control V to paste the chart. Turn off grid lines visibility. This time I'm going to use standard Excel colors that you can pick any time. So you don't have to deal with color codes. Actually, using this relation we derived click on Add Chart Element is the quickest option. So this layout is using one color with the lighter tone for the max value. Let me duplicate this chart again, weed control D, and cut and paste it on a new worksheet. I will set the tone for the surplus. It's a good practice to check the series Options drop-down to see what elements you selected on the chart until you feel more confidence in navigation. This slide green requires dark text color and a little bit darker border line on the silicon. So in this lecture, you learned how to visualize geographic data nicely from design principles point of view, this one is better than the standard elixir heatmap because the viewer gets an extra information, the size of the circle to recognize differences. People can recognize size differences quicker and more accurately than tone differences with the same color. I hope you found this lecture useful. See you in the next video. 8. Bar Chart With Icons: In this lecture, we are going to learn how to create a chart like this where we represent data amends with pictograms, changing position dynamically upon data change. Now let's get into it. Let's say we have statistical numbers about dogs and cats. We'd like to visualize that 51% of married people own a dog and 32% of the same group on a cat according to a research. Let me enter these data quickly. Let's create a simple bar chart. Make sure your active cell is inside your data. Go to insert and choose this clustered bar charts that we move the chart here and take its size bigger by dragging this corner node. I, you could choose charles tired here to make it more interesting. But this is something that anybody can do easily. Let's go a little bit further than that. The words dog and cat can be replaced by pictograms. The human brain can process symbols faster than words. So this would be a good decision from design point of view. Click on a server somewhere here on the right side of the chart. We're going to insert the icons here. Let's go to the insert ribbon. There is a new Insert icon feature in Excel version 2019 and Office 365. If you don't have that, don't worry. I'll tell you what to do then in a minute. Click on icons. If you have this button, scroll down a bit and choose these two. Hit insert. These two icons have been placed on our worksheet. Now I click here and show you how you can insert pictures. If you don't have this new India Taiwan feature. You can find two PNG files in resources. Feel free to download them. I go to Insert again. But this time I click on pictures and choose insert picture from this device. I select these two PNG files. I'm holding down shift as I'm clicking psi select both of them and hit insert. You can drag and drop an icon or a picture on a worksheet like this. By the way, notice that we have the reverse order of pets on the chart compared to the table. We can change that by clicking on one of the pets. Click on the Solve button and choose this option. Or we could sort by number on this column. Same result. I'd like to add this action to the bar in a way that it moves. We did dynamically. If the chart gets a different input data, the icon should follow. We are going to solve this soon, but first, let me make some adjustments on the chart. I select this title element. I click on it again to edit. I don't hit enter when I finished because it would add a line break here. I click somewhere else. I click on the title again to select it. On the home ribbon, I can increase the font size and make it bold. Now I double-click on one of these blue bars. The Format Data Series banner pops up on the right holder dialog window with the same settings in older versions of Excel. By dragging this gap width slider, you can change the width of your bars. I'd like to have rounded edges, psycho to feel options. I expand this border group. I tap in 20 here. Hit Enter. Let's hide these radical Gridlines. I click on this chart elements button. And uncheck gridlines. I'd like to have a grey background bar here. I think the chart will look better. A chart consists of one or more data series. These two blue bars represent a data series. If you have them selected, you can see it's definition here. It seems a bit complex for first nouns, but it just refers to three areas in the table. We have a column label, two row labels. And the data. Anyway, to have gray bars behind the blue ones, we need a second data series because it will have constant 100% values. We don't need to create an extra column here. We just need to add a new data series in charge setup. Hierarchic somewhere on the chart. And choose this select data item. Select data source dialog window pops up. We are going to deal with this section. Here you can see our only data series at the moment. It refers to these data represented by these blue bars. Now we add button, we can create a second data series for the gray bars. I click on, Add. An orange bar has appeared on the chart. I enter a name for this new series. Excel has put a default value here. One means basically 100%. This is good, but we need a second bar. So I enter another one here, separated by a comma. These curly brackets represent an array, a series of data, just like these two says here. I hit the tab key. And you can see the second bar. Good. I hit OK. Here you can see our second data series we have just created. If you look at the chart closely, you can see that the second series overlaps the blue one. We'd like to have it in the background. So we need to change the order here. On this list. I select this background gray series, and click on this up arrow and Excel flip the order. Very good. I hit OK. Now let's add rounded borders to the background bars. I click on one of them. Make sure you see the selection nodes on both orange bars. You can double-check selection here. The whole background series is selected. Under field and line border. I entered 20 in the v3. And I choose this 5% library tone here. I expand field and said the same calorie as well. Now I go to the series Options. And I pulled this series overlap slider all the way to the right. Now I select this horizontal axis label. And under x is options. I rewrite the auto value to one. Actually, it hides the rounded edges. So let's try 1.1. This looks better. With this minus value. We can see the left-handed AIG says, well, that's fine. Let's look at minus value2. I need to enter a different value and type in minus 0.01. again, to override the auto feature. Alright, now we can hide this horizontal axis. I click on Chart Elements. Right arrow, uncheck, Primary Horizontal. Right-click on one of the blue bars and select add data labels. Person values appear. I click on one of them. If you have difficulties to select them, you can also choose data laborers from this dropdown list. Let's go to Label Options. And under Label Position, choose insight based option. On the home ribbon. I said its color to this 5% library. I make it board. I increase its size and choose an arrow phone type. Like this one. I think I said this phone type for the title to. I click on this bar. And once more. Notice that only this one is selected now, the other one is not high. Go to File Options. I choose More colors here. I click on the standard tab. I said this light blue color, heat. Okay. I need to set it for the border to fortunately it's here under recent colors. I click on the other bar, make sure only this one is selected. I go to More Colors. Standard tab, and choose this color for cats. I said the same color for the border. Now here comes the trick for the icons. We are going to add the third layer to our chart. It will be different chart type, the scatter chart. You will see in a minute. What is scattered chart is it can display dots on x and y positions. Well at the end they will not be dots but icons, but we are starting with dots. So we need a new data series with XY coordinates. These XY coordinates, we will define the location of our icons. The x coordinate will be this present value. Let me create a column for this. I simply refer to the value in column B, like this. I enter an equal sign to start a formula in this cell, I click on cell B2 and heat control enter. I pulled this corner node downwards to copy this formula here. Good. So we have the horizontal position of the dots. How do we get the vertical positions? Let's say that we split this area into two parts. 0 will be here at the bottom, two will be here at the top. Then the position will be 0.5. for cat and 1.5 for dog. You'll see this in a minute. Now right-click on the chart. Select data, add new data series. Let's give a name icons. This default value is just fine for now. I hit OK. And we have a third data series. This will display the icons. I hit OK again. I select this new dark gray bar. Right-click on that and choose Change series chart type. You can use different chart types inside a single chart. Here you can see our data series and their chart types. Now I change the chart type for the icon series to scatter. I hit OK. The dark gray bar has turned into a dot. That's okay. I want the dot to use these XY coordinates from table. Right-click on the chart area. Select data. Click on icon series. Heat added. I click inside values feared. I select this range by clicking on cell C2, hold down the left mouse button, drag the mouse down to cell C3 and release. I click on the y-values, feared. I delete it's content. And I select this range in the table. I hit. Okay? And okay, again, we have these two dots almost in position. We just need to fix the vertical axis scale. I click on it. I go to x is Options. And type in two for maximum and 0 for minimum. And our dots are in position radically. Now we can hide the secondary vertical axis. Soon we are going to replace these dots. We design icons. But before we do that, we have to set the proper size and color for them. I put the dog icon here and set its size. Now we the dog I can select it. I go to format. If you have icons in your Excel, then you see this ribbon. If you insert pictures, I'll show you in a minute what to do because you will see a different ribbon here. For the icon, you can click on this graphic fear button to set the color. I choose this 5% library. For the cat, I used this insert picture. I move it here. I set its size the same way. I go to Format. And under color, I choose the light is great on here. Unfortunately, these colors don't work. But we can go here on the Format Picture panel. Expand picture corrections. I said brightness to 100% and said the contrast to minus 57% are actually on this beam bar. We need a lighter tone. So I go with minus 56%. I also change this labor color to white. I click on it once and twice. So the other one is not selected. And I said White here. This is a good example that the same colored the slide gray tone can look different on different background. So you may need to correct that. Now I select the dog rag to conduct and heat cut. Now it's on the clipboard. Click on this dot. And zooming to do this right? I click on once more to select only this one. The other one is not selected. Now I hit Control V on the keyboard to base the icon from the keyboard. I go back to 100% zoom level. We need to adjust the dogs exposition, but that's not a problem because we have it in a separate column. I click on the cell C3. And in the formula bar I extract, let's say 0.05 or 5% from the original value. I hit Control Center. It's not enough. Let's make it 0.07. Good. Now let's select the cat. I hit control X to cut it. I zoom in. I click on this dot twice. So the dog is not selected. And heat control V to paste a cat. I zoom back. We just need to copy this corrected formula for the cat. I grabbed this bottom right corner node and pull it upwards. And it's there. Maybe we need two different correction for the cat icon. Yes, 0.05, we'll be better. Now we can hide this left vertical axis. And we are done. I changed the person values in the table and chart updates accordingly. We can also resize the chart if it's needed. I close this panel and delete these objects. Now if you want to, you can move these cells somewhere else and hide them. I select this range. Hit Control X to cut. I select the cell here, for example, I hit control V to paste. Before we had these columns, we need to do one more thing. Right-click on the chart area. Select data. Here you can see this hidden and empty says button. Click on it. Check this show data in hidden rows and columns. Option. Hit OK. Heat. Okay, again, now I can select these two columns, right? The condemn, choose Hide. And we have a clean worksheet with the data and the chart. Feel free to use this chart creation method for any entities that you can assign pictograms to. Alright, I hope you liked this lecture. See you in the next video. 9. Chart In Infographic Style: In this lecture, we are going to create this male female. I can chart. The technique that will be discussed can be used with any pictograms to create an eye-catching infographic or dashboard element wherever you want to represent key percentages and you can connect icons to them, it's worth consider using this method. This solution works in Excel 2010 version and above. Now let's get into it. Let's click on cell A1 to make it active, I type in 65%. Hit Enter. Click on cell A1. It's important to make the cell active. Before we move on. Let's go to insert and click on this very basic column chart. Let me grab its frame and drag and drop it here. Notice that even the simple chart object consists of several different elements. You need to have the same selection that I have in the video in order to see the same options. If you mess up something, don't worry. Just hit the escape key on your keyboard to quit from an action and hit Control Z to undo if necessary, and start older. It takes some time for beginners to get used to access logic and behavior that's perfectly normal to eliminate mistakes, let's hide some elements that we want. Use. Recon this chart elements button. Let's uncheck this primary Horizontal Axis and Chart Title. Now make sure that you click outside your chart somewhere here. Let's go to Insert and click icons. This icon library was introduced in Excel 2019 and Office 365. If you have an earlier version of Excel, no worries. We will use a better option anyway. Just hold on for a few minutes. Excel offers lots of icons in this library. Let's go to the people group on the left. Click on this mail icon and hit Insert. And there is a way to assign an icon or a picture to the column on the chart, but we need to set its color first. We have several options to do that. You can choose from this graphic sphere drop-down under Format menu. Or we can right mouse click on the icon and choose from the feared dropdown. I usually use this option to tell the truth, not fan of Excel's theme colors. I like to choose my owns. So let's go to more fear colors item here, can find two taps. On the standard tab. I choose this turquoise blue. Hit ok. Now here comes the trick. Let's right-click on the icon. And choose copy. Or you can alternatively hit control C on your keyboard to copy this icon to the keyboard. Now let's click on this blue bar. Make sure that its corners look like this. So it's selected and hit Control V on your keyboard to base the item. Good. Now let's click on cell A1 and typing 25, for example. Now if you hit Enter, the SAS selection will go to cell a2. If you want to stay on a one heat control enter. And the chart has updated, but not the way we expected. The scaling of the vertical axis has changed. I'd like to see a solid 100%. Let's double-click on it to call the chart options pain. Let's click on the axis again to selected so that the pane on the right shows axis options. Let's click on this last Axis Options button. Expand this first item. Notice that there are auto labels next to bounce values. It means that Excel automatically scares the axis based on the data value. We don't want that. So in the minimum field, let's type in 0, it enter. And in the maximum field, Let's type in one, hit enter. One basically means 100%. And the vertical axis now reflects this. And it will stay that way. Whatever percentage we give. Let's type in 45. It control enter. Let's type in 85. It control enter. The mail icon size is updating, but it's still an underway we want. Fortunately, there is a setting that can help. Let's click on the icon so it becomes selected. Click on the first paint bucket icon. Inside the sphere section. Look for this stack and scale with option. Click on it. And the icons starts to look like it should. Let's type in 100% in cell A1. That's okay. However, Demand seems a bit stretched. We change that. Let's click on it. In series options. Let's pull this gap width slider to the left to about 85%. This will make the gap smaller between the icon and the frame of the chart. Let's click off the chart, see how it looks. It's still not perfect. Watch these gaps at the bottom and the top. We have 100% input value, but the icon doesn't cover this precisely. Let me take the icon size bigger. The reason is that we have a gap on our source Eigen to that meet Control Z to undo. Let me show you why this is in the shoot. I type in 98%. The chart still shows the full man. 95. It starts cutting. That C1. Nothing it's shown three, still nothing. We need at least 4% If you don't need high precision and you have icons in your exome version, you can go with this option. Otherwise, you can use the following solution. Based on out-of-the-box thinking. I took the standard Excel icon imported into Photoshop, cut the top and the bottom, made the surrounding white area to transparent, inverted image. So the main body had become transparent and saved it as transparent PNG image. You can find both the male and female versions in the resources max to this lecture. Please download both PNG files to your computer. Now let's select the icon on the chart. Let's go to feel options. As you can see now, it's picture or textual field. Let's set it back to solid fill. Let's select the original icon and hit Delete on the keyboard. That select the chart. Makes sure that only its frame is selected. Let's go to Insert, click on pictures and choose this device. Okay, the place where you saved it from resources. Click on the male image file and hit Insert. The transparent image has been inserted inside our chart area. That's why it was important to select the chart first. Then do the insert. If I drag the image, you can see how it covers the underlying bar chart. So the image we just imported hasn't been shaped hole in it and white-collar surrounding. Now I'm trying to resize it. No, it's the frame of the chart. Let's undo control Z. I click the image again, go to Format. Here we can set its size. Let me take its height is smaller. So I can grab the image frame nodes. I move it a little bit downwards. Now I can grab this node to pull it upwards and release the mouse button. Operating with objects in Excel takes some practice. Don't get frustrated if you do this for the first time. I let me enter some test values. Let's see 99. I can see that little slot on the top. Let's try 1%. That's good to 3575. Now it would be good to make this remainder area grey. Let's select the inverse. I can hit Control X to cut it. Look somewhere here outside the chart and hit control V to paste it. Now let's deal with the chart to have that gray background bar. Click on the chart. Make sure that you see exactly the selections. Right click on the blue area and choose, select data item. You should see this select data source dialog window. This is where we can tell the chart which data to use. We have only one data series at the moment, which is basically one number, the seventy-five percent. This data series is shown as a blue bar on the chart. We're going to create a second data series to show a gray bar. So let's click on head. Excel automatically puts another series to chart this orange one. We can give a name to our new series. Let's call it background I. In the second field, you can see the series values. By default, there is single one here. It's just constant number. If you look at the chart, this one will be actually 100%. This is exactly what we need. So let's hit OK. Heat. Okay, again, we just need to recover the second bar. That's right-click on it. Field and choose this 5% gray. Good. Now, we will need the slide is gray bar below the blue ones. Somehow it's possible. Make sure that one of the bars is selected. And click on this last series Options button. The solution is the series overlap option. Drag the slider all the way to the right. Now the grey bar highest W1. We just need to bring the blue to the front. Right-click on the bar, go to select data again. The only thing we need to do is to change the order of this series. Click on Series1, which is the blue bar. And click on this down arrow to move it down. Perfect heat. Okay? Now let's click on the inverse. I can hit Control X to cut back on the chart frame and hit control V to paste with the right arrow key on the keyboard. Let's move it into position x. Let's test it. But first, let's hide the horizontal grid lines. Charter amends uncheck gridlines. Let's see. 0%. Good ON percent, good, ninety-nine percent good, 100%. Let me zoom in. You can also use Control Alt plus to zoom in. I grab this node to change the height of the inverse Ackermann. Something like this. Alright, it seems fine. Let me go back to 100% zoom level. You can also use control 4t minus two zoom out. Let's select the vertical axis. You can hit Delete derived it, or you can uncheck this check box. Let me drag this node to the left to hide this line and pull this right one, center, the icon. And the good thing in that we inserted the icon inside the chart frame is that we can resize them together. So if I take the chart narrower, VI can read, we'll follow something like this will do. Great. Let me enter 85 so we can see the chart better. Now let's select the frame of the chart. Right-click and set outline too. No outline. Now makes sure that only the chart frame selected and hit control D to duplicate the chart. And I grab this copy and move it somewhere here. We are going to create the female pair. Let's select column by clicking on that, I'd like to insert a new column. We can do that by right-clicking on the Selection and choose Insert or rediscount selected, you can hit control plus on the keyboard. I actually can sell a1 and type in mail. I hit enter and type in female into cell a two. This time I finished data entry with the Tab key. This way the active cell will be the one on the right. I'll click on cell B2. Excel automatically format percentage. When we entered percentage value for the first time, I set it back to General. Remember that eighty-five percent is basically 0.85. Now I click on cell B2 and start an Excel formula. A formula in Excel calculates something and it always starts with an E cosine. Our formula will be very simple. It will calculate remainder based on the value in cell B1. So I type in one minus and I click on cell B1. Excel puts the Sarah France into the formula. So the formula will use the value in cell B1. To calculate the result, I hit enter one minus 0.85 equals 0.15. let's select these two cells by clicking on cell B1 and dragging the mouse cursor to cell B2 and release. I click on this percentage button to format the cells. The cells have the same values, but now they are showing the values in percentage format. If i enter 65 in cell B1 and hit enter the formula in cell B2, we are calculate the new result. Now, as you can see, our second chart still showing the sixty-five percent. We need to change its data source to take the value from cell B2. But before that, let's click on the inverse icon on the second chart, makes sure that its frame selected, hit delete. We don't need this mail icon on this chart. Let's select this blue bar. Right click and hit Select Data. The background series is good densities. Let's click on series one and hit edit. Now as the cursor is blinking in the series name field, Let's click on cell a2. The Sarah France gets into the series name field. So if we updated the title in Sally to, it will be reflected here in charts settings. At the series values field is pointing to cell B1. Let's delete it from here and click on cell B2. All right. Now our second charge data source has been set properly. Heat okay. It okay, we can now choose the femur data thirty-five percent. As I'm changing mere data, female data follows. Good. Now let's pick a color for our second chart. Color selection matters if you create something visual for people. So we will be sophisticated. We're going to use design principle, a color harmony root, big derived on. First, I check the color code of our turkeys. On the custom tab at the bottom, you can see this hexidecimal value. This identifies our color. Let's copy it. I hit cancel. And let me show you a great tool that you can use in your work. Even if you are not an expert in colors, this is a great habit to create harmonic charts. This is a tool on the Adobe website. You can find a link in the resources of this lecture. This is the color wheel. On the left. You can choose from different color harmony rules. Let me paste our color code here. I'd like to find a complimentary color for after quiz. So I click on this one. Actually, I need based our color code again. This is our color. I'd like to get a similar saturation and brightness. So I bought this node outwards somewhere here. Let me paste our code again because I turned the wheel. So I'm going to use this complimentary pair, far turkeys. Its color code is this one. Let me copy it. I hit hard tab to go back to our excel file. I click on this blue bar. And this time I said it's color here on the Format Data Series panel. Let's go to fill into dropdown, pete more colors. And on the custom tab at the bottom based on new color code. Heat. Okay? Now our second chart has this matching orange tone. Now make sure that our second chart is selected. Let's go to Insert Pictures. This device, I double-click on the female inverse icon file and it's inserted inside the chart area. Now it would be pretty hard to click on its corner nodes to resize it. Instead, let's check the width of the other one. Control C to copy to the clipboard. Click on the orange one and pays the value. Hit enter. Notice that the width and height values are linked together. As I changed the width, the height followed. But that's okay. We know that the width is correct. We just need to alter the height. Let's drag and drop it to the bottom of the chart. And pull the upper note. Two somewhere here. Let's test it with different values. I am changing only the male value because the female value is linked to it. Let me zoom in to see it better. And watching the orange chart. Maybe I need to change a little bit. And at the bottom as well. It looks okay to me. Now let me zoom back to 100% and close this Format Shape panel. Hit out f ten to open the selection pain. This is panel where you can see your objects on the worksheet. Currently, we have only two charts. Let's name them. Double-click on it to edit. Now click on assess somewhere here. Go to insert. But this time let's click on shapes. We add background frame to our charts. So let's click on this rectangle. The mouse cursor turns into a cross. Click and hold on the worksheet somewhere here and drag the mouse cursor to somewhere here. And release. Let's name our new object. Objects on the worksheet have a hierarchy. Now this blue rectangle is on the top, adding the two charts. But if I click on the Send backward button twice, it will go to the background. Good. Let's right-click on it. Set the fill color to white brick on the outline, and set it to this 15% gray. Let's reposition the chart's a little. I click on the first object on the selection pane, hold down control, and click the second one. Now I can drag and drop both of them somewhere here. Let's set the meal value to 51 so they are not equal. And let's add some textual amendments to the charts. So let's go to insert text box and click somewhere here at the level of the shoulders. The first information will be the person that you value. But of course, I'd like to make it dynamic. So we place a reference to the value field in the text box. If you want to do this, we need to start typing in the formula bar right here. So I entered the equals sign to start the formula, and I simply click on cell B1. Excel automatically puts an absolute reference to the sphere in the formula, I hit enter. The value showed up next to the chart. That's great. Excel has technical glitches here and there. It has lost connection with deformed type. Let's choose Calibri, and let's choose 48. In the size dropdown. Lets make it board. And in the recent color section, click on our turkeys. I align these tags box manually to the shoulders. Now let's insert another text box for the data title. I used the same method. But this time the reference cell will be a one. That's it. That's formatted. I click on the Font Color. Come on Excel. Let's click again. Good. Let's set the size to 24 and drag and drop it here. And let's create a separator line. So go to Insert. But this time, click on shapes and line. I click and hold with the left mouse button somewhere here. I drag the mouse cursor to the right. I hold down shift on the keyboard to get straight horizontal line and release. Under the Format menu, click on Shape outline. Choose our color. Shape outline again. Go to weight, which refers to the line weight, and click on three Beatty. Good. Now I'm pressing the up arrow on the keyboard to move this line upwards. The summary. Let's set the line width is slightly smaller. 0.75 inches, we'll be fine. Actually, I like to have a board text here. So let's go to home and click on the board button. Or you could use the shortcut key controlled BY. Texts was getting wider. So let's adjust the line below. I bow to insert again. He textbox somewhere here. And let's type in some description for the chart. I re-size it, move it into position. I left align everything here. Let's add this 50% gray for its color. It seems okay to me. Let's name the objects properly on the selection pane. It's a good practice to keep things organized. I changed the order as well. Now I select all of these new objects holding down Control on the keyboard. I'd like to group them. I right-click here to get the context menu. Now, the group menu item is not there. Let's start the selection over. This time. I right-click here. And here we go. Let's click on group, ungroup again. Now are selected objects have been grouped. Let's naming. Now I can move the entire group like this. But let me just undo it. Control Z, this position is just fine. Now make sure that the group selected and hit control D to duplicate that copy has been created that I can drag and drop the next to the female icon. Let's drop it here intentionally in the wrong position. Now click on one of the groups. Hold down, shift on the keyboard, and click on the other group. Make sure that only these two groups are selected. Let's go to Format line drop-down and choose a line top. Now the two groups are in the same position vertically. Let's select only the second group and hit the left arrow on the keyboard to move it to the left. I think. Okay, now, let's name the second root properly. Out we need to do is to change the references in the text box. Let's click on the percentage one. Go to the formula bar. B1 refers to the male value. We need cell B2 here. So let's correct formula. Hit enter. Unfortunately, Excel resets the formatting to the default, which is annoying. So click on the text box selected. Let's go home. Let's set the size to 48. Make the text board. And said the orange column. I select title textbox. I need to change the Zara France to a2. I need to play around with the size setting because Excel is not perfect. I set it to 24. Make the text board and said the color. I select line. This time I need to go to Format. And said the shape, outline, color here. Let's fix the width of the line. I'm holding down shift as I'm doing. If you struggle with selection on the worksheet area, because objects are too close to each other, use the selection pane to select an object. Let me adjust the size here in the wheat field. And let's select the frame. I take the bit narrower, like this. I hold down shift and select all the elements. You could also do this on the selection pane, holding down Control. I right-click here and choose Group. Let's name the group. Now, I can move all objects together. All right, let's kick off the chart area to see how it looks. What do you think? I think it's pretty clean, eye-catching design with harmony colors. Notice that I left whitespace around the elements to lead them to breath. This is one of the design principles that makes your visual representation better. Let me do one last thing. I click on cell B2 and create a formula that can return random numbers. I'm going to use an Excel function called RANDBETWEEN. We discuss Excel functions in detail in a separate section. Now it's enough to know that we can give two numbers as parameters to dysfunction and it will return a random number between them. I'd like to have a number between 1100. Hit enter. Okay, we are working with percentages here. We need to divide the result by 100 to get a random number between 01. Good. Now you can hit F9 on your keyboard to recalculate the random value. I'm pressing F9 now, and you get different values each time. This way you can test a chart like this. Let me close the selection pane. Alright. I hope you learn new things and found this lecture useful. As usual, you can find the complete digital file in the resources. See you in the next video. 10. General Shortcuts: In this section, I'm going to show you more than 100 excess shortcut keys. Shortcuts can save you time when you work in Excel. You don't need to memorize all of them. Just pick the ones that you think you would use a lot. You can find these shortcut keys in pdf in the resources. Most of the shortcuts here work in every version from Excel 2007. A few of them may not incase use other than English keyboard layout. If you use Excel on a Mac, here is a map form modifier keys. Many keyboard shortcuts will work on Mac the same way, but somebody or not, find the original Microsoft resource link in the resources. For more information. I categorize shortcuts and put them in separate lectures. Let's start with some general shortcut keys. And you see a modifier key like Control, Alt or shift in a shortcut. You need to hold it down and hit the other key in the shortcut. So all the keys have to be down at the same time. When you use the art key, you need the one on the left. I type in some data quickly. Control Z. Control, reduce, control, halt, plus zoom in. Control, minus zoom out. F1. I can look for the undo function, for example, f1, context menu. Or you can use the context key on your keyboard if you have one. Control, F1, show, hide, the Ribbon. Shift f. Insert new worksheet. Control N, create new verb. Control. W, close, verbal, F4, close Excel. I don't want to do that. So I hit Cancel Control S, safe. F to Save As dialog control. You can completely turn off this page breaks. In options. Advanced. Display options for this worksheet. Disable show page breaks. If you want to hide these page breaks without changing the access settings, you need to reopen the workbook. Alright, in the next lecture, we are going to discover navigation shortcut keys. See you in the next video. 11. Shortcuts for Navigation: Let's see some navigation shortcut keys. Arrow keys moved to different directions. Then move to the next cell. Shifted that move to the previous cell. Now let me add an extra column with some values. So you can better see how the next shortcuts work. Control arrow keys to move to the edge of the current data region. Control Home. Moved to the beginning of a worksheet, essentially to sell a control hand. Move to the last cell on a worksheet, the lowest tooth row of derived most used column. If I enter data into the self control and we'll make this one active. Even if I delete its content, it will remain unused. Cell page down, move on screen down in the worksheet. Page up. Move one screen up in the worksheet. Page down. One screen to the right in the worksheet. Page up on screen to the left in a worksheet. Control up previous worksheet. Control page down. Next worksheet. Show ribbon access keys. In case of freeborn access keys, you heat keys one after the other. You don't hold down any of them. Ribbon access keys are exceptions from this point of view. I can hit old age. A, C, the center line, the selection, control, G, go to dialogue. I type in C1. Hit OK. And the active cell will be c1. Control F. Find and Replace dialogue. Shift F4. After find, find the next match. Control shift f four after find, find the previous match. Now I insert an icon. I insert a shape, and I insert a chart. Show Hide, Selection Pane. You can select your objects here. The selection pane is useful when you have many objects or some of them overlap each other. Let me delete these objects. I hold down shift and select them one by one and high-heat delete. Alright, in the next lecture, we are going to discover data entry, which shortcut keys See you in the next video. 12. Shortcuts for Data Entry: Now let's see some shortcut keys that can speed up your data and tree. Control plus insert cells, rows, columns. If you are on a cell, it invokes the insert dialogue. You have different options here. I hit OK and Control Z to undo. If I select the column control plus we'll insert a column on the left. If I select a row, control blast will insert a new row above. Of course, I can select and add multiple columns to control minus. Deletes selected says, rows or columns. If I am on a cell, control minus invokes the Delete dialogue, I can choose what to do. I hit cancel. If I select the column, control minus will delete the column. If I select the row, control minus will delete the row. Delete, remove cell contents. Control-c, Copy content to the keyboard. Control V based content from the keyboard control. We open based pressure dialogue. You probably need an English keyboard for this as an alternative. Try old as E in this order. It works for me. This shortcut key burbs in Excel 2013 and above it, these options you can do interesting things by pasting only values, formats, validation, and more. Control x ket content. Now when you fill in a cell with data and you are finished, you have several options to complete the entry. You can hit Enter. It will complete the data entry and select the cell below. You can hit control enter. It will complete the data entry and the active cell, we'll remain the same. You can hit Shift Enter. It will complete the data entry and select the cell above. Enter to add a line break inside the cell to the active cell. Shift F2. Use the edit command, control semicolon, enter the current date. This doesn't work for me. You probably need an English keyboard. So I invoke the today function instead. Control. Until the current time. Control D, field down. I enter a number here. I select this range and hit control D. There is another way of doing this. I select this range. I hit F2, I added the cell, and I hit Control enter. Control our field, right? I can do the same thing horizontally. Control ie, invoke the flesh feel feature in Excel 2013, and it works like this. I give an example to excel in this cell. And I hit control ie. It invokes the flesh fury feature and tries to recognize patterns in adjacent columns and feel the current one. Shift F3, insert a function, shift, drag and drop. Move a selected row or column. Control Shift, drag-and-drop. Copy a selected row or column. Drag and drop, move a selected rule or come to another worksheet. Control, drag and drop, copy selected rule or come to another worksheet. Alright, in the next lecture, we are going to discover formatting. We shortcut keys. See you in the next video. 13. Formatting Shortcuts: Let's see some Formatting shortcuts. Control one, opened the format cells dialog. Control Shift F. Night also invokes the format cells dialog by the Format Font tab, VOP, active. Control, be bold font. Control, high detailing formed. Controlled new underline. Control. Five, strikethrough, ALT. H, a right-align cell content. Control nine. Hide selected rules. Control Shift nine, unhide selected rules. You have to select the adjacent rows before control 0. I'd selected cans, control shift 0. Unhide selected columns. This one doesn't work on my keyboard, so I am just using the context menu. Our age. Oh, hi. Auto fit column width. We invoke a menu item. Basically. There are also shortcuts for each important format type, but unfortunately, they don't work on my non-English keyboard. So I just list them. Feel free to pause the video and give them a try on your keyboard. Let me add column headers. And I remove the formatting examples in column a. Now let's continue. Control D. Create table dialogue. The active cell has to be inside your data range before you hit the shortcut. Let me set the different tables tie for this one. Control f, x3, name manager, odd shift, right arrow, group selected rows or columns. Shift left arrow, ungroup selected rows or columns. Control k, insert, edit, hyperlink dialogue. I have a link that I copy to the clipboard. I hit Control K, and I paste the link here. Now I can click on this link and it will open the webpage. Alright, in the next lecture, we are going to discover a shortcut keys for selection. See you in the next video. 14. Selection Shortcuts: Let's see some selection shortcut keys. Shift arrow keys creates selection. Control shift down arrow keys, extend selection in the data region. Control a. Once, selected range. Twice, select the whole table. Three times, select the entire worksheet. Was that the active cell remains the same. Control asterisk. Select the current region around the active cell. The active cell will be the top-left Control Shift home. Extend the selection to the beginning of the worksheet. Control shift, and extend the selection to the end of the worksheet to the last used cell, even if you deleted it's content. Control space. Select an entire column in the data range. Space. Select an entire row in the data range. Shape. And I come and read the chart. Out f. Open, close selection pane. Here, you can select individual objects. Holding down Control. You can select multiple objects. Holding down Control verbs on the worksheet area to let me hit delete. All right, in the next lecture, we are going to discover a shortcut keys that you can use in formulas. See you in the next video. 15. Shortcuts for Formulas: Let's see some shortcut keys that can be used in Formula creation. F4. Relative absolute cell reference toggle, clock column or row or both in a reference. F9, evaluate selected part of a formula. Let me enter some numbers and create a formula here. So I can select parts and evaluate each of them separately. We'd F9. This shortcut is useful in complex formula creation for when you need to analyze someone else's formula. Hit escape to quit. Now the next three shortcuts don't work on my keyboard. If you have an English keyboard, give them a try. Alt equals sign, photo, sun, heat the shortcut. Next two datasets Vered when numbers. This shortcut invokes this auto some menu item. Control apostrophe. Copy formula from above it. The shortcut below a cell that contains the formula Control Shift apostrophe. Copy value from above. Similarly, hit the shortcut below a cell that contains a formula. Control shift, you expand, collapse for millibar. Escape, cancel an entry in the cell or formula bar. F2. Added cell contents. Control shift, and select all the text in the formula from the cursor position to the end. Alt, enter a line break in a cell. If you have a complex formula, breaking it into lines can have to analyze it. Alright, in the next lecture, we are going to discover some miscellaneous shortcut keys. See you in the next video. 16. Miscellaneous Shortcuts: Let's see some miscellaneous shortcut keys. I remove the table formatting so I can show you the shortcut. If you need quick filtering on your data, use this visitor control shift. Theater toggle. Control D. Create Table, control shift T. Table, total row title called F1. Create a chart from the data in the current range. Create a chart from the data in the current range on a separate charge sheet. Controlled the duplicate chart. Let me replace these quantities using the RANDBETWEEN function. There are four types of free calculations that you can invoke it. Shortcut keys. Here are the definitions. Usually you will simply use F9. Let me Insert PivotTable quickly. If you don't know what a PivotTable is, maybe you'll have a dedicated section to discover. So here we have this pivot table that is taking data from the table on the left. If I hit F9, the RANDBETWEEN function will generate new values or the pivot table doesn't get refreshed. I need to hit F5 to refresh Bureau table data. The active cell has to be inside the pivot table. Out f 11, open the Microsoft Visual Basic for Applications. Editor. Out f. Open the macro dialogue. All right, I hope you learned some new shortcuts that will save you time. See you in the next video. 17. Relative vs Absolute Cell References: In this lecture, we are going to understand a key concept of Excel, relative versus absolute cell references. It's important to know what they mean and which one to use in different situations. Now let's get into it. In Excel, you can refer to a cell by its column letter and row number. For example, I can refer to cell A1 like this. This simple formula multiplies the value of cell a1 by two. This a1 in the formula is a relative cell reference. Why is this called relative? Because if I copy this cell downwards, Excel will change the reference automatically. So in cell B2, it refers to sell a2, and so on. When do you use this relative reference? You often want to auto fill a column with the formula. If you use a relative reference, you won't need to edit the formula in other cells. Now what if I want to multiply column a values with three? I can create a new formula, just like in column B. But there is a smarter solution. Let me create headers for these columns that contain the multiplier. So I insert a row at the top and type in 23. Let me delete these values. The new formula will look like this. Now what happens if I copy down this formula? Excel automatically changes the second part of this formula too, because it's also a relative cell reference. But we would need to lock it to be one. This is where the absolute Zara France is coming into the picture with the dollar sign. I can look part of the reference. In this case, I'd like to lock the row. So I put the dollar sign here before the row number in reference b1. I autofill again, we double-click on this corner node. And now the reference to cell B1, we remain in each cell and the calculated values will be correct. Now let's copy this formula to the right. The second part of the formula has changed to cell C1, which is good. This is because column B was not locked. It was updated to column C During the copy. To make this formula copy work, we need to look column a here. So I enter a dollar sign before the column name. Let me auto fill this column b, we double-click. Results won't change here, which is good. So we have a Locked column here and a locked Roe here. Now I can copy the formula to the right. And it will be correct for column C. I autofill this canon. And the results are correct. Let me create further multipliers here. And let me delete these cells. At the beauty of this relative and absolute references in this formula is that now I can fill in the entire table in a few simple steps and we will have a complete multiplication table. Notice that we have one simple, well-constructed formula here. Now I select this range. I hit F2 to enter to edit mode in cell B2. And I hit control enter to feel all selected SAS, we do active cell B2 content, which contains our formula. I hit Control Z to undo. You could also copy the formula this way by dragging it to the right and drag this row downwards. Same result. Now let us see a typical example for an absolute reference. While we looked both the column and the row in a cell reference. Let's say we have values in USD currency. In column a. We have a USD euro conversion rate in cell B1. And we'd like to create a formula that calculates your values here. This is how it looks with relative cell references. But we need to do is to log both the column and the row in this B1, Sarah France. Now I click here, so the cursor is blinking next to this B1, Sarah France. And I hit F4 to change the reference type to Absolute. By the way, F4 is a toggle. If I hit it once more, only the rho will be locked. Next time, only the current will be logged. And next time we get the original Sarah France. But in this case, we need both as absolute. So I hit F4 again. Let me format the cell as you will. I double-click on the corner node to auto fill this column. As you can see, the formulas will be correct in other cells to they refer to cell B1, the conversion rate. Now if we want to copy and paste this Euro data into another column, we need to take an extra step to look column a in the first part of the formula, I double-click for autofill. And now if I copy this range into another con, the formula will be updated accordingly. It will refer to column a and cell B1 because I locked the right parts of the cell references. Although if you use these relative and absolute cell references properly, you won't have data integrity issues even if you insert new columns or rows into your data range. The references will update properly. In order to use the right type of Sarah France, you need to think ahead a little, but I am sure that you will get used to it quickly and you will know which one to use in which situation. I hope this lecture was useful. See you in the next video. 18. Working with Dates: In this lecture, you're going to learn the practical use of date functions. If you use Excel, you will work with dates as well. I'm pretty sure being able to make transformations on dates is a very useful skill. Now let's get into it. You can download the Date functions Excel five from resources if you need to. But it's okay if you just listen to this lecture and make notes as needed. And you work with data in Excel. You need to know that they are stored as integers, numbers starting with one. You can see the serial number come here. I entered some examples. Notice that the formatting of the cells is set to general. This is the default format on a cell in Excel. Now let me just simply referred to cell a2 in cell B2 like this. And I double-click on the bottom right corner node for autofill. So we have the same values in column B. Now, I select this column by clicking on its header. And from the Format dropdown, I choose this short date format. So I formatted this column as dates. I'd like to know that I used the American date format in this lecture. That first tag is the month, the second tag is today, and the third tag is the year. You may see a different format in your exert depending on your original settings. Let me take this column wider dragging discount separator. Here also align the text to centre. And I give a column name. Now as you can see, number one, which was formatted as a date, has become January first, 1900. This is the earliest date that Excel can handle with its state functions. Date from year 2020 is stored as a number like this, 43,978. So it's important to remember that these dates in column B are basically numbers in the background. There are functions that expect dates in their arguments or parameters. Sometimes the state argument is referred a serial number in the functions hint bubble. So if you see the serial number as an argument in a function, it refers to a date perimeter. Alright, now let's see some very simple date functions. You can extract a month has a number from a date with demand function, I start typing and I got the function on the list that I need, I simply hit tab. Excel will put the function name in the formula bar, including the opening parenthesis. As you can see, this function expects only one argument, the serial number. I can refer to a cell here with the date. So I click on cell B2 and I hit Tab to complete this formula, Excel automatically adds the closing parenthesis. The return value is one because the date is in January. Let me autofill discount by double-clicking on the corner node. So we have the month numbers extracted from dates in column B. We can do the same with days. The function name is DE. It works the same way. I give cell B2 as the argument, and it returns the day number from the Tate. And the third function here is obviously the year, which returns the year number from a date. This time I hit Control Enter, so the selection will stay on the cell. You can save a mouse-click display. Alright, we will use these functions for more complex date manipulation soon. Now I go to the second worksheet, date formatting. Let's see how we can customize date formats. We're going to use the text function. The first argument is the value. This is identical to Syria number. The text function works not only with dates, so that's why the argument name is different. I give sell a2 as the first parameter, and we therefore, I changed the reference type to absolute. For the column a, I hit F4 three times to look only the column for the Sarah France. I do this because I blend to copy this formula to the right. It will save me sometime soon. I hit comma and I enter format text here inside double quotation marks. This time it will be very simple. Single D. With this format text, we get Day from the date. Now I click on cell B2, but this time I drag the corner node to the right. Now you can see why I Locked column a in the formula. I just need to change the format text. Let's see what a double D shows. We get the day in two digit format. Let's align these columns to the right. It will look better. Now let's add one more D to the format text. We get the name of the day in short. And for these in a row, we get the full name of the day. Now let me copy these days down here. Let's see demand created format text. We're going to use the letter M for the man. Let me fill in the headers quickly. The function verbs the same. Column a here, read F4. I hit it three times. This time i o for this line. And changed the format text. So this is how you can get different month formats. High double-click on the bottom right node of the selected range. And Excel auto fears this whole area with the formulas. You could probably fill in the formula yourself for the years. Let's see how it looks. The only difference here is that the year has only two digits and four digit formats. Now let's see how we can get the last day of the month. The function that can have is the E0 month. It needs a data argument. And enter 0 if you'd like to get the last day of the actual math. Now because the cell has the general format, we see that integer value, which is the date. We just need to format it. I select the entire column and said This short date format. Good. The alignment to center. We can see that it works as we expected. Now I copy this formula to the right. Well, I forgot to look column a in the formula. I can grab this blue border and place it on cell a three. Now I place the cursor here and lock its column with F4. With minus one. In the second argument of this function, we can get the last day of the previous month. And no surprise, if i enter one here, we will get the last day of the next month. Very good. Now let's see another way of getting the same result. This time, I'm going to use the date function. The date function expects three arguments as numbers, the year, the month, and the day. In this order, we already know functions that can return these values. So I use the year function first, which cell? A, 15. I lock the column with F four in the reference. Then I use the months functions the same way. Now, I have an issue here because the formula covers the cell I want to click on. Anyway, I type in $1.15. You can also start to edit the formula and the formula bar. Then you won't have this issue. Now here comes the trick. We add one to the math value and give 0 for the day argument. So we the plus one, we shifted mass to the next one. With 0. In the last argument, we shifted the date back with one day. And together this resource, the last day of the actual month. And it works with the other dates to my if we delete this plus one from the formula, we step back a month. So the return value will be the last day of the previous month. And you might find out we need to enter plus two here to get the last day of the next month. And let's see how we can get the first day of a month. The trick here is that we go back one month in the math function, so it returns the last day of the previous month. Then we simply add one day to debt. The result will be the first day of the actual amount. For the first day of the last month. We just need to subtract one more from the mom. And the first day of the next month is easy. We need to get the last day of the actual months like this. And the plus one will add one day to eat. So the result will be what we want. Let's see the same results with the date function. Lowercase or uppercase in the formula doesn't matter. So we simply enter a constant one in the third day argument. For the next one, we just need to subtract one from the month. And here we just need to add one month to the actual this worksheet. We are going to explore the weekday function, but before that, I create a fully formatted date in this column so we can better compare results. The format text will be a composite one containing everything about the date. Right line will be better if you provide only the date as parameter for the weekday function between return the day of the week. It ranges from one Sunday to Saturday by default. Now there is a second optional parameter in this function. We can choose from different numbering methods. I double-click on this one to set it. The return type argument will be three. So the first day of the week we'll be Monday this time starting with 0. I. Here comes the trick. If we subtract this result from the original date, we will get the date of Monday on debt Week. This is just an example how you can use date functions creatively. Occasionally, you might need the week number of your date. There are functions for that, but be careful, this is a tricky topic. There are different methods to calculate the week number in a year. If you work in a team, you need a consensus about which one to use. I have already feared in column a, we dates and I have custom formatted them in column B. First let see the week numb function with the default setting. So I simply add the date as an argument. In this case, the Vietnam function starts counting with the week that contains January first and Sunday is used to start a week number. So this date to January first will be week one, and so on. And the second optional parameter, we choose Monday as the first day of the week. And in this case, the week number will change on every Monday. So on Sundays, these two formulas, we return different results. At the ISO week numb function follows ISO standards. Reeks begin on Monday and week number one is assigned to the first week of the year that contains Thursday. It has simply one input parameter, the date. As you can see, this is another way of showing weak numbers. Sometimes you need to calculate the number of days between two dates. Let's see how you can do that. We have data in column a and due dates in column B. You can get the days between them simply taking the due date and subtract the original date from that. Now if you want to include both the original date and a due date, just add one to the same formula. Now let's say you need the number of working days between the two dates. Network days is the function you need. The first two argument had obvious Hein, the optional third argument. You can add a range of dates with holidays. So I select this range here. And heat control enter I auto-fill the formula downwards. Pixar signs that we may have an issue here. Let's check the second Sarah. Yes, I forgot to lock the Sarah France to make it absolute. As you can see, the autofill has shifted arrange down because this is a relative cell reference. Let's fix that. I click on cell E2. I select this range reference and hit F4. Actually it's enough, the cursor is there. I autofill again. And now the holidays reference is correct in each cell. So we calculated the net working days between two dates, taking holidays into consideration. Now there is a more sophisticated version of dysfunction, Network Days dot i and its first two arguments are the same. And in the third one, we can tell Excel which day or days to use as a weekend. I double-click on Sunday only option. And the fourth argument is the holidays. Don't forget to hit F4 here. Good. Now let's say that we'd like to calculate due dates based on number of months. We can use the update function that expects the start date and the number of month. It's important to log the row number in this case, because I want to copy this formula downwards. So I hit F4 to times. I need to form a discount my state. And we have a due date using one months as input value. Actually, this needs to have a general formula. So let me fix that. Now. Let's say we have two months period for due dates. And just need to copy the formula from column B. Well, first we need to load the column in this reference. So I click here and type in dollar sign. Hit Control enter. Now I can simply copy this formula. And because I used the right absolute Sarah Frances, I don't need to change anything. These values used the two months period. This function works with minus values as well. So we get dates one month before. All right, let's see how we can add certain number of birthdays to a date. Let's say we want to add 15 workdays to these days, taking the holidays into consideration as well. We are going to use the verb Da function. The second parameter is the number of work days. Let's hit F4 to lock it. And the third parameter is the holiday range. As usual. We look it as well. Let's format these cells as date. So Excel adds 15 verb-based the original date. The difference in the ion TO version is that you can tell how to handle weekends. Now let's see a final reward example for date functions use. We have invoiced dates in column a and payment conditions in column B, 710306090 days. We have an actual date here. Let's say that this is the date of today. You can use the today function to get the actual data. By the way, if you want to use a formula or expression as text in a cell, put the single code to the beginning. So x-a one tried to identify the sand content as a formula. The first step, let's calculate due dates based on the payment conditions. The formula is simple. We just add the days to the invoice date. Now in this column, I mark some of the items I spade by typing in. Yes. In this column, let's create a formula that tells if an invoice has an expired due date. It simple. We just need to compare the due date with the actual date. I hit F4 to lock it. As a result, we get true and false values in this column. True means that due date has expired for that invoice. Now let's extend the formula. So we check the B8 column as well. I want to show true value here only if the due date has expired and the invoice has not been paid. So we will use to logical condition with the end relation. It looks like this in Excel. I use the AND function. The first parameter will be the condition that we have already put into column E. And the second condition will be this one. Check if the cell is not equal to yes. So this formula, we return true only if both conditions are met. The due date for this one has expired, but it was paid, so the result will be forced. The due date for this one has expired and it is not paid. So the result will be true. And so on. So we have a nice formula that tells if an invoice needs attention. Let's visualize this information. Let's color those due dates with red which have expired and the invoice hasn't been paid yet. Let me copy this formula to the keyboard. How I select column C, because I will set the conditional formatting on disk icon on the home ribbon. Find this conditional formatting drop-down, and choose new rule. We are going to create a New Formatting Rule. Here, you can select from role-types. I click on this last one because we have a formula. I type in the equal sign and paste the formula from the keyboard. Now we need to make a little change on debt in order to make it work. Has this rule will be applied on the entire column. We have to replace the row number four. We'd won. Otherwise the formatting wouldn't work. We may have further invoices in the table. So it's a good practice to use the conditional formatting on the entire column. I click on this format button. And on the phone to tab, I set a red color here. I hit okay. So where this condition is true, the text in the cell will be read. Hit OK. Now we got the quiet useful visual representation of unpaid invoices. I'm checking correctly, derived says are colored and it seems they are. In this lecture, you learned a bunch of application of date functions. I hope you found them useful. See you in the next video. 19. SUM(), SUMIF(), SUMIFS(): In this lecture, you're going to learn the use of different some functions to add up values in many different ways. The sum function ads where you use this function all the time in excel. Let me create a ten by ten matrix with random numbers. You can add individual cells like this, separating them with commas. You can add constants. You can use this autosome feature on rows and columns. It has a keyboard shortcut out equals sign. It doesn't work on my keyboard, but if you have an English keyboard, Give it a try. So my active cell is at the end of this row. And I heat this autosome button. Excel selects this range and I can hit Enter. Let's double-check the result. I select this rule. And here at the bottom, we can see its sum. You can use this autosome on a calm too. You can mix the arguments. Of course. I select this range. I hit comma. I select this range. I hit comma. I select the cell. And I could add as many arguments as I want. I hit Enter. Let me copy this matrix to a new worksheet. You can also refer to SAS or ranges on another worksheet. I entered the sum function here on Sheet one. I go to sheet two. I select this range. You can see in the formula bar that your reference will look like this, including the sheet name. I go back to sheet one and I hit Enter. Now let's say that we have the same data structure on multiple worksheets. And we'd like to add the same parts on all of them. I create another copy of this random matrix on a new worksheet. We can use so-called 3D reference. This is how it looks. So we add the A1 cell on all the three worksheets. We use the range of worksheets in the reference. 20 plus 90 plus 44 equals 154. Now let's see an extended some function. With the SUMIFS function, you can sum values in a range that meet your criteria. Dysfunction verbs in Excel 2016 version and above. Here we have sales data from June and July. You can find this excel file in resources for download. Let's answer some questions with the SUMIFS function. I'd like to get the sum of sales above two tiny. The first argument is the range where we want to check our criteria. So I select this range. I hit comma. The second argument is the criteria. In this case, it's a simple comparison. I need to add it to the function between quotation marks. I hit comma. The third argument is the range we want to take the values for the sum. This will be the same as the first argument. In this case. Closing parenthesis, control enter are the SUMIFS function adds the sales that are greater than 220. Let's check the result. I select values that meet the criteria. I'm holding down control as I clicked the SAS. And the result is correct. I'd like to get the sum for banana says only. But this one, the criteria range will be in column B. The criteria we'll be banana. And the sum range will be this one. Let me check the result. And it's correct. By the way, you can also use wide characters in your criteria for partial matching. This one we, the asterisk signs, for example, matches orange and banana too. Because asterisk substitutes any number of characters. The question mark substitutes any characters on one certain position. Your criteria can include dates to, let's sum the series of July. We are checking these dates. The criteria. We'll be a bit tricky, but I'm sure you will get used to it. We use the greater or equal operator in quotation marks. We use an ampersand and I'm adding the date, July first with the date function. The sum range argument is just as usual. Closing parenthesis, control enter. Let me check the result. And it's correct. Ixl has the SUMIFS function for multiple criterias. Let's answer this question. Sumifs works the same way as sum. If just the order of arguments is different. It starts with the sum range. And after that, I can add as many criteria, arrange criteria pairs as I want. Let's add the first criteria range. The criteria. The second criteria range and its criteria. Closing parenthesis, contractor. Let me check the result. And it's correct. Alright, I hope this lecture was useful. See you in the next video. 20. MIN(), MAX(), AVERAGE(), COUNT(): In this lecture, we are going to discover some very basic Excel functions, min, max, average, count, and their extended variations. Now let's get into it. As usual, you can find this excel file in resources. We have a similar table, just like in the SUM function lecture. I added one extra column here. I use the text function referring to column a. And using the triple, the format text. Let's answer some questions regarding these data. I have already feared in these cells here, I just hit them with white font color. I'm going to revere them one by one. Plus the minimum sale value. The mean function will tell. It's very simple. You can give one or more arguments to dysfunction. The logic is the same as for the sum function. You can use the same possibilities in the arguments. Single cells, multiple cells, ranges, and so on. In this case, we refer to this range. And it will answer the question. We can get the maximum with the max function the same way. Average sale, average function. We can get the number of sales with the count function. And there is another version of comb function, the current blank. This can be useful, for example, if you have a large set of data and you want to check if there are empty cells. These functions above also have versions with criteria check. However, the EFS versions are available only from Excel 2019365. But I'm going to show you how alternatives. So the question, what's the minimum banana cell are the mean function doesn't have mini version. It has mean ifs version only. The argument structure is the same as for the SUMIFS. In the first parameter, we refer to the range where we are looking for the minimum value. Then the criteria range and criteria pairs come. In this case, we have only one condition. We need rows that include banana in this range. Now what if you have an older version of Excel? You can get the same result with this combination of functions. The outer function is a simple min. And we nest and if function. So the argument of the min function will be the result of the if function. Inside. The function is simple. We check these blue range. If it contains banana. If it does, then it gives back the value from this red range. The result of this if function will be a set of sale values and array. And the mean function will pick the lowest one. However, there is a trick you need to apply in order to make this work. You need to complete the formula with Control Shift Enter, which is the way of entering a formula like this called array formula. Look at these curly brackets in the formula bar. If they are not there, the area formula won't work. Let's answer this question. We need the max function. The first argument is the range. We are looking for the max value. Then we check the first criteria, orange in this red range. To get only june transactions, we use two criteria. Both of them will be applied on this range. First, we check if the date is greater or equal than June first. Second, we check if the date is smaller than July first. And that's it. For order Excel version, the solution is a bit more complex. Let me expand the formula bar. I've added line breaks to the formula to make it more readable. This will also be an array formula. These curly brackets indicate this. Outside. We have the max function. Inside. We use three nested if functions. Look at this if function this way, it takes this range and it goes row by row and checks if the actual cell egos orange. If it does, then the next if function, we are check the date from the red area. If this condition is true, then the next if function comes and it performs these check. If all the three conditions meat. Then it will give back the corresponding value from this cs range to the max function. So the max function will get a set of values for all the three conditions are met. And it will pick the biggest value. Don't forget to finish this area formula with control shift enter. Now I know this is kind of complicated. If you struggle with a formula like this, try to build simple ones first. You can also use the shortcut key F9 in the formula bar to analyze a complex formula, remember, you can select part of your formula. Hit F9 and E to assure you that partial result. This part, for example, evaluates the condition on each row. The first part of the red area. We return true till this row. Then it will return false. For these rows. I hit escape to read from the formula without changing it. I collapse the formula bar. Now we can answer this question with the average if function. This is kind of simple. The first parameter is the criteria range. Here we are looking for Monday. And the function will calculate the average from this red area for the values that belong to Mondays. Now let's see this complex question. We use the average if's function. It gives an error message. Let's see why. This first condition looks for Friday transactions. We have one here for banana, mom for Apple, and another one for Apple. So we don't have transaction for orange. In this case, the function returns this error. We can handle a situation like this with another function. I click here at the beginning of this formula. I'm going to use this IFERROR function. If it's first argument is in error, then it will return the second argument. Let me enter this sign here in its second argument. And we get the more user-friendly result. Hi-fi enter banana here. We will get a normal value because there is a banana sail on this Friday. Let's answer this question with the COUNTIF function. It's pretty simple. The first argument is the range. The second is what we are looking for. We have seven banana says or together. And if we have a more complex question regarding quantity, we can use the COUNTIFS function in newer versions of Excel. We just need to enter criteria ranges and criteria pairs. All right, I hope this lecture was used to see you in the next video. 21. VLOOKUP(), HLOOKUP(): In this lecture, we are going to learn how to use VLookup and HLookup functions. You use these functions to look up data in a table and return a related value. Now let's get into it. The V stands for vertical in VLookup function name. Let's say we have an employee dataset and we have a separate form where we want to select an employee from a drop-down list and get the related data from the left. You can create a drop-down list like this. Make sure you are on the cell where we want to create the dropdown. Lets go to data, click on data validation. Here. From the dropdown, lets choose list, clicking the source field, and select this range where the employee names are. Heat, okay? And we have an employee drop down. Now let's use the VLOOKUP function to get these data. I start typing the function name. It's very important that dysfunction and looks for a value in the leftmost column of a table. If you need to look for a value in other columns, you will need the index match function pair or the x lookup function. Anyway, let's see this VLookup arguments. The first one is the lookup value. We look for this employee. So I click on this cell, I hit Common. In the second argument, we have to define the range of our data. So I select this area. I hit comma. The col index NUM argument will be two, because we'd like to return the age of the selected employee. The age is the second column in the selected range. I hit comma. This last argument, does VLookup, If you want approximate match or an exact match? Most of the time you want an exact match. I'll show you an example for approximate match on the next sheet with HLookup. But now I double-click on exact match. I hit control enter. And we got these employees age. Let's choose another employee. And it works. I align the cells to the left. Now let me copy this formula and see if it works with the job title. We obviously have to change the for now. Nothing refers to the right place. We could rewrite the formula for the specific cell, but I like to use general solutions. I hit escape and go back to our original formula. I click here and log the lookup value serif France VDD F4. I select the second argument and hit F4 again to lock it as well. Now I hit Control, enter the formula steel works. Now I can copy it again. And I just need to change the col index am argument to three to return the job title, which is in the third column. I hit Control enter. Let's test it. It works perfectly. Now I copy this formula downwards and change the col index Nam too for hit Control Enter. We need the date format on this cell. So I go to home and said This short date format. Perfect, it works as expected. However, I'd like to point out and issue here and suggest a solution. What if we hire a new employee? Let me add the next row here. Our dropdown doesn't realize the change. You could go to change the data validation, source reference, and all the formulas. But that's just not nice from system design point of view. Let me undo some steps and show you a good practice. I have talked about the benefits of Excel table. We are going to utilize it. Make sure you are inside your dataset. Hit Control T, and click OK. Let's add a meaningful name to our table object. Let me choose a different table style. Unfortunately, exert doesn't allow us to refer to this table come directly in the dropdown. So we need an intermediate step. We need to define a name object which refers to this column. Let's see the syntax of referring to this table. Calm. I started formula and select all the data in the first column. This is how it looks. I copy this reference to the keyboard. I hit escape. Now let's go to formulas and click on define name. I will call this object employee names. And I paste the reference from the keyboard. I hit OK. I, if I go to the Name Manager, you can see that we have our Excel table. And this named range here. I close this window. Now I click on the employee drop-down, go to Data, Data Validation. And here in the source feared, I enter the name of the name object we just created. I hit OK. Now I add our training to the employees and the list has been updated automatically. As well as the formulas. We have an empty row here that we can fix quickly by dragging the corner of the table upwards. Good. I'd like to point out that we have a list here where values are unique in the first column. Keep in mind that VLookup with the exact match option returns the first match. Few have repetition in your data. The answer is not the VLookup function. For example, if you had two employees with the same name, you would need to assign unique IDs to your employees. Look for the ID instead. But that's a more complex task. Vlookup long cannot solve it. Now if I hit delete in the cell, we get error messages here. If v lookup doesn't find the lookup value which is now an empty string, then it returns this error. You can handle this VD if error function like this. So this is how you can use the VLookup function. And let's see what HLookup is good for. Let's say we have a list of sales reps. And there's some says, we have a bonus table. We'd like to assign bombs per cent for each person based on his or her performance. The h function is perfect for this. The h stands for horizontal in the function name. This function works the same way as VLookup, but it looks for value horizontally in a role. Let's create our formula. Arguments are the same, we just need to translate them to this horizontal approach. The lookup value will be the cell value table array. The area of area looking will be this range. I hit F4 to lock this entire reference. Don't forget, we will copy this formula downwards. That is why we need this area logged. Now here is the difference. We need to define a row index Num. In this case, we need the value from the second row. So I enter to here. And in this case, we definitely need this approximate match option. So I enter through here. I hit Control Center. I copy this formula downwards. At reformat the spheres as per cent and I increased decimal. Now the approximate match works this way. If the value that we are looking for reaches a grade, but it's still below the next one, then the corresponding person will return. It's pretty useful. There is one important thing to note. In case of approximate match option, you need to provide sorted data in the row here. Otherwise, it won't work properly. All right, as usual, you can find the 6.5 phi and resources. See you in the next video. 22. INDEX(), MATCH(), XLOOKUP(): In this lecture, you are going to learn how to use the index and match functions. We will also talk about the x lookup function, which is a modern and flexible version of the old ones. Now let's get into it. Feel free to download this Excel file from resources. Here we have a similar data table to the one used in lecture relook up and HLookup. I have just added an extra column is first one employee ID. It's very common that you have complex data structure with more than one table. It's a best practice to use an id column for each data entity. In this case, for employees. It is also called primary key in database design. With ID fields, you can create relations between tables. This is getting interesting. Many want to use multiple tables in a pivot table, for example. But anyway, let's get back to the topic of this lecture. Here we have an employee drop down list. If you don't know how to create this Hodge, the previous lecture about VLookup and HLookup functions. Just as a reminder, I'm using data validation on this cell, referring to a name object, which refers to employee name column in the employees table. Now if we want to extract these data from our table, the VLookup function will not be able to handle this because the employee name, what we are looking for is not in the first column of the table. We need to combine to other functions, index and match. Let's start with the match function. It looks like this. It's first argument is the lookup value. I click on the dropdown because we are looking for this employee. I hit comma. The second argument is the lookup array. You can enter a range here. But we are going to use the benefits of our table object. I select all the data in this column. And Excel will automatically add the table come reference to the function. I hit comma. The last argument is the match type. We need an exact match. So I double-click on this one. I hit Control enter. The match function returns the position of an item in an array. In this case, it returns the position of this employee. In this column. We can also say the row number of this employee. We will use this row num In the next function as an argument. So let's see the index function. Its first argument is the array where we are looking for the answer. We are looking for the age of the employee. So it will be discovered. You could use this range reference. But we have a table, some using this table account reference. The second argument is the row number. We already have this value using the match function. So I click on this cell. They use it as a Sarah France. And because we have only one column, we don't need the third optional argument, the current numb. So I hit control enter and we get the age of this employee. However, I will use a more general solution here. Let's refer to the entire table here like this. And said the column NUM argument to three. Cause the age is in the third column. I hit Control enter. Now we are going to combine these two functions into one formula here. Let me copy this formula to the keyboard. I hit escape. I paste it into this cell. I hit enter. And I copy this other formula to the clipboard. Hit escape. And pasted here, replacing the Sarah France in the row NUM argument, I hit Control Center. So what this complex formula does is to take this employee tables third column and it returns the value from this rule. This is what the match function answers. Looking for the employee name. In this column, I made reference to absolute. So I hit F4 to lock the cell, I hit Control enter. Now I copy this 400 downwards. I just need to change this column NUM argument. For job title. It will be four. Because it is the fourth column. I hit enter. And that changed the column num here to five. Because this one is the fifth column. I hit enter. That's format the cell as short late. And we are done, are formed showing the right information. If you have an Office 365 version, I encourage you to look at this x lookup function and try it. This is a flexible replacement for the order functions with more features. It can return multiple values in an array. If there are multiple matches. You can add NOT_FOUND message in an argument. You can use wild cards in the lookup. And it has the binary search option, which is the fastest way of searching in large set of data. But if you need backward compatibility, because you have users with order versions of Excel, unfortunately, you cannot use this function. Alright, I hope this lecture was useful. See you in the next video. 23. PivotTable Made Simple: In this lecture, you're going to learn what a PivotTable is, what it is good for, and how to use it in many different ways. Pivottable is an intimidating topic for Excel beginners. But I'm going to show you how simple it is. I'm sure that from now on you will use it all the time. Let's get into it. Pivottable simplifies data analysis, and it also makes it a lot faster. You don't have to spend time with writing formulas or studying function arguments. Even if you have thousands of rows of data with a few clicks, you can create many different kinds of reports quickly. You can group your data, count them, show totals, sums, averages, etc.. You can also use it to make comparisons, check patterns, and trends. Pivottable is a powerful tool for making good business decisions. Once you have your pivot table, it's very easy to visualize its data. We build chart. But in order to utilize all the benefits that PivotTable offers, you need a good data structure. First, name your comes properly. Second, make sure that one specific column contains one specific type of data. If your data table is well organized, PivotTable creation will be fun. I created a dataset which we will use in this lecture. You can find these in PivotTable XML file in resources. Feel free to download it so you can practice on your own. After this lecture. Let's say we have a training company selling online courses. In this table, you can see sales transactions. Each transaction has a date. We are selling courses. So we list the course title, its category, and the instructor who created the course. Each course has a list price. We may sell the course with the discount. Revenue is a calculated cell based on the list price and the discount. We have a sales channel and it's cost. The profit margin is also a calculated cell, taking the revenue minus the marketing costs. And we have course ratings from students. Plus we have age information about them. We have over 1000 rows in this table for a two-year period. Let's see how we can analyze these data, predicts our pivot table. My active cell is inside this dataset and I go to insert. Let's see what this recommended PivotTable list offers. If you have ordered version of Excel and you don't see these baton, don't worry, we are going to create a pivot table from scratch in a minute. So it can sum up revenue by course. Category by instructor. This one is useless. Sum of marketing cost. That can be interesting. Profit margin by instructor, that can be useful to and so on. Now I hit cancel and I'm going to show you how to put together a pivot table on your own. So I click on this pure Table button. To create pivot table dialogue pops up. Excel automatically locates the range of your data if your active cell is inside the data. Here you can choose if you want to insert the pivot table into a new worksheet or an existing one. If you want to place it on this worksheet, choose this option. Click inside is filled and click on an empty cell in your worksheet. But I usually put my pivot table on a separate worksheet. Say go back to this option and I hit okay, a new sheet has been created. You can see this image here, which is a place holder at the moment for the pivot table. We also got to New Menu ribbons on the top, analyze and design. These can be seen only if your pivot table is selected. So if you don't see them, click inside this area. And we got a new pivot table fields panel on the right. If for some reason you close this panel and it doesn't come back, even if you click on the pivot table, you can click on this field list button on the analyze step. Or you can right-click on the PivotTable and choose the Show Field List item in the context menu. Here on the ribbon, you can name your pivot table if you use more than one in your workbook. I encourage you to do so. Let me drag and drop this worksheet here. When you work with pivot tables, Excel uses the term feared for cum. Wherever you see the sphere term on forms, it means basically a column in your table. These are the columns or fields from assess transaction worksheet. You can check them individually and the check ones will show up in the pivot table. Let's start with the symbol one. I'd like to see the revenue grouped by category. I check the category field. Excel automatically post the field into one of these areas. In this case, it decided to put it into the row section so our categories appear in roles. Notice that whatever you do on this panel, Excel updates the PivotTable instantly. If you have a large set of data and your computer slows down, checked is defer, layout, update, check box, and click on the update button when you are finished with your structure. Now, I check the revenue field. Excel applies the sum function on this and puts it into the values area. We can form at the values here, I click on this drop-down and I choose Value Field Settings. I click on this number format button. The format says banner pops up. Alternatively, you can right-click on a column and choose Number Format, menu, item. Let me set currency and 0 decimal places. High heat. Okay? So with a few clicks in a minute, we summed up the revenue by category. Now let's drag and drop this category field into the Columns area. And I drag and drop the transaction date into the rows area. In case of date fields, Excel automatically adds further grouping fields to the period table. You can see them here. And here as well. You can draw them down by clicking on the plus buttons in the table one-by-one. Or on the Analyze ribbon, you can click on these expand collapse field buttons. Or you can right-click here and indeed expand collapse item, you have further options. I'm not really interested in quarters. I can simply drag and drop the sphere outside the rows area. And it will be deleted from the Bureau table. So we have a pivot table that shows revenue by month and cause category. We can turn off these grand total of values. I right-click on it and choose remove Grand Total. Similarly on this one. If you want to turn it back, click on the pivot table, go to design. And here you have several options. You also have display options for subtitles. In this dropdown. I could clean this bureau table by unchecking or the fields here. Or I can click on this clear all button on the Analyze ribbon. I will keep this pivot table and create a new one. So I hit undo. My name is Worksheet quickly. I create another pivot table on another worksheet. I'd like to see the profit margin by instructor. That's format the data. I right-click and choose number format. We can sort this data by right-clicking on them and choose Sort Largest to Smallest. For example. By clicking on this button, you have a bunch of options to sort and filter instructors. This can be interesting if you have more data here, but I can filter on the instructor name. We deliver a theater like this. I clear the filter. Or I can filter on values showing going instructors with the profit margin of 01. Let me clear the theatre. I'd like to see the person value compared to the grand total annex to this profit margin cum. Let me grab this profit margin field here. So we have a duplicate. I click inside this column and right-click to involve the context menu. I choose Show Values As person of grand tutorial. And here they are. Let me rename this header. By the way, on the design ribbon, we can choose a different style for our PivotTable. Let me choose this one. Let's see some grouping options. Let me select these two instructors. I click on this one, I hold down control, and I click on this one. I right-click and choose Group. I can rename this group here. Let me turn on the subtitles. Let me put the other instructors into one group. So I hold down Control and select them one-by-one. Right-click group. And I named the group R. Notice that sorting works inside the groups from now on. If I want to sort by group Toto, I need to show subtitles. Click on one of them. And sort of how I can hide subtitles. I like to add the sales channel to this report as a filter. I checked the sales channel feared except puts this into the rows area, metabolite to have it in the theatres area. So I drag and drop it here. Filter drop-down has appeared here at the top. I can check, for example, which instructor performs the best in affiliate campaigns. You can set multiple filters with this checkbox. Let's say I'd like to see the amount of discount was given by instructor. If I look at my data, there won't be a column like this. It needs to be calculated. Let's create a calculated field in the pivot table. Make sure you are inside the pivot table so you can see the Analyze menu. Let's go there. Click on the spheroids items and sets drop down and choose Calculated Field item. You can give a name to this new field. I click in the formula field, I delete the 0. I click on the list price feared. I hit Insert field. I entered the minus sign, and I double-click on the revenue field to insert it into the formula. I click on the Add button and hit OK. Now let's create another pivot table. I name this worksheet first and create a new pivot table on a new worksheet. I'd like to see a new report by age group. So let me check these two fields and need to drag and drop customer age wierd here. Let me format the revenue come quickly. Right-click Number, format, currency, 0 decimal. This Taber quiet useless in this format. Let's group the H field. Make sure your active cell is on one of these values. Right-click and choose Group. Excel realizes your minimum and maximum values in this column, and we can set a grouping range, ten, we'll be just fine. I hit OK. Let's make this grouping better. I go back to the group dialogue, right-click group. And I said 24, starting at feared. I hit OK. Perfect. We have a pretty even distribution here because I populated this H come with random numbers. But if you had real-world data, you could create a nice distribution chart from this period table. It would show which age group is your primary target. So you could address them more effectively. By the way, you can move a pivot table with this button on the Analyze ribbon. You can move it to a new worksheet, but now I just place it inside this one. Ideally dislocation feared. I click on a cell and I hit okay. Alright, let's create another pivot table. I'd like to see average customer ratings by instructor and month. Let's put it together. I check transaction date, instructor, and customer rating fields. I drag and drop the instructor to the Columns area. And the customer rating into values. In Value Field Settings. I changed the aggregate function to average. I click on number format. I choose number one decimal place. I hit okay? And okay, again, I expand date fields. Let me remove this quarter sphere. We get division by 0 error messages in some cases. And some of the cells are empty. Let's see why. And I'll show you in a minute how to fix that. I'm going to check Catlin Sears in these two months. I go to a datasheet. I hit control shift L to turn on theatres on the columns. I choose Catlin only here, so only her cs will be shown. We have an error for August. She had only one sale with no rating in this month. Okay. What about September? She had no serious in September. I think the empty field is just fine in this case. Let's get rid of this error messages. I right-click on the PivotTable and choose pivot table options. Here and the layout and format tab. I check this for error values, show checkbox. I hit OK. And error messages have done. Let me hide this grand totals. Now let's create a pivot chart from these data. On the Analyze ribbon, click on pivot chart. I choose the line type and hit okay. I take this column narrower. Let me close this down and take this chart bigger. We could spend some time with formatting discharged to make it look nicer. But let me just set this dark style under the design menu. Let me hide these fields here. Right-click. I, right-click. Hide. Let me double-click on this vertical axis and set the constant five for the maximum and one for the major unit. It seems obvious that Jasmine is our best instructor based on customer ratings. And Jamie also performs excellent. Our Kristina and Kathleen definitely need attention. A pivot table and chart like this can help you to pinpoint issues in your business that need to be addressed. Now one more important thing. Let's say we have new data coming into our later table. I insert a row here, adding the sale for Catlin for September. If I go back to the PivotTable, nothing seems to be changed. You need to click on this refresh button on the Analyze ribbon or heat the shortcut, or F5. Or right-click on the PivotTable and choose refresh. And the new data will appear in both the pivot table and on the pivot chart. Now read the refresh. The first column width was auto sized. This can be annoying if he spent some time with adjusting your column width. Let's right-click on the PivotTable, Pivot Table Options. And I uncheck this Auto Fit option here. Heat. Okay, good. Finally, let me add the slicer for discharge, which is kind of a filter. I click on Insert Slicer. I check instructor and heat. Okay? In this Coulomb's field, I enter seven. As we have seven instructors. I resize the slicer. I said this style. If you are not impressed with the default styles, that I would understand, you can create your own style. Here. I rearrange these objects like this. I right-clicking on the slicer and choose Slicer Settings. I uncheck display header and click OK. Now I can filter data by instructor. I can hold down control to select multiple instructors. And we drag click on the Slicer. I can clear the filter to show all of them. Let me name this worksheet quickly. Alright, I hope you found this lecture interesting. Feel free to use these test data to experiment with PivotTable. See you in the next video. 24. PivotTable From Multiple Tables: In this lecture, we are going to learn how to design a solid data structure in multiple tables on multiple worksheets, and how to create a pivot table from them. Data model feature that we are going to use is available in Excel version and the 13 and above. Let's get into it. This is kind of an advanced topic, a reward business logic and its workflow can be very complex. Be prepared for facing lots of challenges, and you decide to turn your business flowing to Nick's sad database. This lecture can not be a complete guide on that, including all the possible pitfalls. I'd just like to give you an introduction to this topic and give you clues for further discovery. And you plan your data model. You probably start with the texture description. Let's take our imaginary training company as an example. We have instructors who create online courses in different categories. Each course has a published date. You store several information about our instructors. I fullname, age, phone number, and email address. We record each sales transaction. It includes the transaction date, cores, and the sale price. We'd like to create a data structure which we can maintain easily, like inserting new data. We'd like to create reports from the data quickly and the data model should be easily extended later without affecting our existing application logic or reports. And you create tables in Excel, you basically create data model, data structure, or relational database. There is a method existing to do this, right? It's called database normalization. It was first proposed by Edgar F chord and English Computer Scientist in 1970. The normalization is a database design technique. It's the process of structuring your data in a way that you eliminate data redundancy and maintain data integrity. First, you identify or railroad entities and separate them into their own respective tables. Entity attributes, we re columns in this table. You also create relations between these tables. All of these can be accomplished by applying some former rules called normal forms. They are called first normal form, second normal form, and so on. I'm not going deeper into this theory because it's way beyond the scope of this course. But feel free to search for the term database normalization if you are interested in. Let's see how it looks in practice. You can find this excel file in resources. I have identified our data entities and put them on separate worksheets are most important data entity is called courses. Each of our data entities or tables will have an id come. In case of course is table. It's called Course id. Each course has a unique identifier in this first column. It is also called primary key. We can use this ID to refer to a course in other tables without storing redundant data there. The maintenance of course data will also be simple. We can edit them in one single place. In the course is table. We store all the data that relate to a course in this table, like its title or published date. Now we could also store its category here, but we would have data redundancies ten plus. It's more elegant to store this information in a separate table. Each category has a unique ID, so we can refer to them in the courses table. We apply the same rule for instructors. They are in a separate table, having a primary key in the first column, which we use in the courses table. An instructor can have multiple attributes like name, age, phone number, email, and more. Here is our sales transactions table. Each transactions has a unique ID. The transaction id. Each transaction has a date, of course. And we refer to a course in each transaction. For simplicity on transaction can have only one course in our data model. If you'd like to allow more courses in a transaction, we would need an extra table containing transaction details, but we are keeping things simple now. In the last column, we store the same amount. Now let's see how we can use these tables on separate worksheets in one pivot table. As a first step, I am going to convert all of our dataset into Excel tables. Make sure your active cell is inside your dataset. Hit Control T, Heat. Okay. It's important to give a name to our table so we can identify that easily. I entered the name of our entity here and hit Enter. I said these great Table Style. And I perform the steps for the other worksheets as well. By the way, if you cannot use space character in table name, but that's okay. Alright. Now make sure you are inside this table. Go to insert and click on pivot table button. New worksheet is just fine. Check this, add this data to the data model option at the bottom. This is an important step. Heat, okay, let me drag and drop this worksheet over here and name it. Now this PivotTable feared Spanner looks almost the same just like in the previous lecture. However, there is one exception. This old tap here. Click on that. Here, we can see our tables and there comes. At this point, we need to define relations between our tables using the ID fields. Make sure you are inside this PivotTable placeholder. Go to Analyze and click on relationships. I, it would be good to have this auto detect feature work, but unfortunately it doesn't. So I click on the new button and set the relations one-by-one. And we use this core side, the primary key in another table, like in this transaction table, it's called foreign key. I heat. Okay. So I created a relation between sales transactions and courses tables. Let me do this quickly for the other relations, as I connect courses with categories and courses, instructors, I hit OK. I hit close. My Excel can visualize these connections for us. Let's go to Power Pivot and click on Manage button. A new window pops up. Click on this diagram view button. I maximize this window. Here you can see our database structure. Let me adjust the layout here and there to see things better. You can drag and drop these tables. And change their size to see all the column names. These lines represent relations. If I hover over a line or click on it, the connected columns will be highlighted. So you can check if they are the right ones to one. And the asterisk means that one row from this table may belong to many rows in the other table. It seems that everything is in place in our data structure. So I close this window. Now we can put together our PivotTable. Let's create a report that contains sales by instructor and date. I drag-and-drop instructor name field here, and transaction date here. It seems that Excel has a buck here. Let's go to Analyze, Refresh, refresh all. And the panel is showing the transaction date field. Now, let's format these Value Fields. I click on a number, number format, currency, 0, decimal, heat, okay? I right-click on this date area. Let me group them by month and years. And I remove the transaction date from here. Just to make sure let's check January sales. We have some rounding taking place, but they are correct. Now let's see another report. Make sure your active cell is inside the pivot table. Go to analyze. Clear, clear all. I'd like to see some of sales. My course. And the Let me quickly format these numbers. And I hide grand totals here. Let's order the list by 20-20 cells. And our report is done in a minute. All right. This is how you can create a pivot table from multiple tables. I hope this lecture was useful. See you in the next video. 25. Congratulations!: Congratulations. Please accept my appreciation for getting this far. I hope you learned a lot and you feel confident using Excel by now. I have an extra minute. Please read my course. Your constructive feedback would help me a lot. For example, tell me which part did you like the best. Thank you in advance. I wish you well. Goodbye.