The Ultimate Microsoft Excel Essentials Training Course + Tips & Tricks to Boost Productivity | Lorenz DS | Skillshare

The Ultimate Microsoft Excel Essentials Training Course + Tips & Tricks to Boost Productivity

Lorenz DS, BI & DWH Solutions Architect

The Ultimate Microsoft Excel Essentials Training Course + Tips & Tricks to Boost Productivity

Lorenz DS, BI & DWH Solutions Architect

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
25 Lessons (2h 12m)
    • 1. Introduction

      3:32
    • 2. Getting Started with Excel: Tabs and Sheets

      7:00
    • 3. Getting Started with Excel: Navigation and shortcut keys

      4:59
    • 4. Using Formulas in Excel: Formula Evaluation

      3:47
    • 5. Math & Statistical Formulas: Count(if)(s)

      2:51
    • 6. Math & Statistical Formulas: Sum(if)(s) and Average

      4:34
    • 7. Cell Reference Formulas: Vlookup, Hlookup, Match and Index

      6:44
    • 8. Date and Time Formulas: Today, Days, Networkdays

      3:50
    • 9. Logical Formulas: If, And, Not and Iferror

      6:50
    • 10. Text Formulas: Trim, Concatenate, Left, Mid, Right

      5:18
    • 11. Fill Series, Absolute and Relative cell references

      4:50
    • 12. Insert, Delete and Hide cells, rows or columns

      6:26
    • 13. Number and Cell Formatting

      6:00
    • 14. Conditional Formatting

      7:40
    • 15. Format As Table

      3:42
    • 16. Copy and Paste Options in Excel

      3:57
    • 17. How To Create a Chart in Excel

      9:32
    • 18. Choosing the right Chart Type

      7:39
    • 19. Sparklines

      3:06
    • 20. Sort

      3:51
    • 21. Filter

      3:55
    • 22. Advanced Filters

      5:49
    • 23. Data Validation and Remove Duplicate Values

      6:28
    • 24. Pivot Tables

      5:39
    • 25. Pivot Charts

      4:11
  • --
  • 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.

32

Students

--

Projects

About This Class

44b4c571

Learn the Essentials of Microsoft Excel. A step-by-step guide for beginners with Shortcuts, Formulas, Tips & Tricks.

Learn the essentials of Microsoft Excel through this step-by-step course. The Ultimate Microsoft Excel Essentials Training course takes you through the basics of Excel, but also introduces some more advanced topics like advanced filters, pivot charts and pivot tables. The course also learns you some, shortcuts, tips and tricks for improving your productivity in working with Excel.

Meet Your Teacher

Teacher Profile Image

Lorenz DS

BI & DWH Solutions Architect

Teacher

Hi there!

Hi there. My name is Lorenz, I am a certified IT professional with several years of experience working as a consultant and functional analyst, mainly in the Business Intelligence domain.

Working experience:

I have worked for clients within the Energy and Financial sector, and have been involved in the roll-out of Business Intelligence and Data Warehouse projects and the implementation of BI solutions.

As an instructor, I want to share my knowledge and experience in BI and Data Visualization software. During these courses, I will explain the basic concepts, but also tips and tricks for creating Visualizations and Reports that best suit your purpose or the purpose of your end-user.

Course topics... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

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.

Transcripts

1. Introduction: Hello. Welcome to this Microsoft Excel Essentials. Beginners scores. First of all, why would the scores be important to you? Well, actually, Microsoft Excel is one of the most used spreadsheet programs within small, medium large enterprises. Therefore, Masaryk cell can really be a huge boost to book your professional and personal life. It actually doesn't matter if you're an accountant, consultant or even a business owner. The chances that you will be using Excel in your career will be very likely. Although this course is mainly target towards beginner students, some basic knowledge on Excel might really be helpful, and this course might even be beneficial to intermediate users. Get the broader understanding some of the main function all of these within, except it's excellent, so much to offer. There are always new things you can learn. Okay, let's have a look at the course overview, so he course starts with a small introduction on Excel. What are the main purposes for using Excel? What is the screen layout in Excel, and how can you easily navigate between taps and sheets? We'll also look at some frequently used shortcuts and excel that really make your life easier after the introduction section will have looked at some of the basic formulas in Excel, so mathematical formulas such as counting some and also cell reference formless, will be covered in Part one and sexual pupil covered mostly dating time from US. Logical formulas and also text manipulation form us and, after working with some of the basic formulas, will have look at conditional former thing and also sell Former thing. How can you improve former thing off your dashboard by using different cell on number four Months after that, we're going to learn how to create a chart in Excel. Because Exelon has so many different chart types, it's also important to choose the right chart type as best suits your dashboard. Excel also has many options for sorting and filtering data within a table or a certain range. So also have a look at some fast minutes for using fielders in Excel and using sorting. After that, we're going to look at five tables and five charts in Excel, which offer many benefits compared to regular tables or charts, throw some more dynamic and can easily be changed at the end of the first section, which is the introduction section will have the possibility to download to Excel files. One Excel file contains the exercises without answers, and the Other Excel file will contain the exercises with answers and also the final dashboard that you're going to build from scratch. So during each course and during each lecture will have look at these exercises. There are also many different versions of itself. Within this course, this tutorial were mainly going to use Excel two dozen 10 which is slightly different from more recent virgins. Such a subversion 15 or version 16 of Excel, which also include do chart types and some additional functionality, such as a power query functionality. However, these will not be covered during this corpse, so the power query functionality will not be covered and has already mentioned it's also a beginner scores. But although discourses targets to give her students, it's also a great course or just intermediate students just like to know more about. Okay, now that you know what the court is about, let's get started and I hope you enjoy the rest of the scores 2. Getting Started with Excel: Tabs and Sheets: As you can see, I have Mike Silver Book Open and at the top of my workbook, I can see the name of my workbook book one below that I am different steps, and each of these steps contained ribbons, which can be customized according to your preferences. In the middle of screen, I have my spreadsheet at the bottom left corner. I have my different worksheets. I can simply add it the name of my worksheet by double clicking on the worksheet and typing , for example, test. I can also insert a new worksheet by going to the I can hear clicking on insert worksheet. As you see now, a new worksheet is created. I can also move a broadsheet simply by dragging and dropping an existing worksheet in between two other worksheets. And I can also hide existing worksheet by right clicking, going to hide. Then my worksheet is not visible anymore to a night to the worksheet. I simply right click on any sheet in critical on hide here I select a sheet I want tonight if I want to change the color of my feet. So I want to change this color than I right click on the sheet and I go to tap Kohler, I change the color of my feet. This can be very useful if you have many different sheets and excel and you want to distinguish some sheets from each other or you want to highlight certain important sheets let me now show you the different steps in excel. So the first step is the file tap tapping green on the left. If I go to safe, I can save my workbook So I can save, uh, the workbook under a specific name. Because the true safe ass this case I want to change the name of the workbook to test. And I quick safe. Yes, I want to replace the workbook. As you see now, all the worksheets within this World book are saved under the workbook test. If I want to reopen my workbook, I can go to open Nice. Select the workbook. I just saved here. Likely come open. And now my workbook has reopened. If I now go back to the falter at the top left corner, then you see that there are some other options here. For example, info will give you all the information about your workbook. And as you see, there is a possibility to protect your workbook. So here you can protect your workbook from being edited by certain users. You can protect your worksheet already in time brick structure, so that you just cannot create any new sheets. You can also grant access to certain people, which gives certain people access to at it or remove data while others can't. If I go to new in the file tap that I could choose to create one of the office templates. These are standard templates offered by Microsoft Excel that can be used to create, for example, a budget spreadsheets or a calendar spreadsheet. I go to options within the felt up that I could change my user preferences for working with Excel. For example, I can go to language and select the language in which I want to work. For example, if I want to change my language to Dutch and I simply select Dutch and select Dutch as a default language and all the user interface elements, buttons and taps will be translated to Dutch. If I now go back to the file tab and I go to Prince, that I can choose which selection I want to print. I can print my active sheet, which is a cheat that I have currently open. I can also prints my entire world book, which will bring all the worksheets with in my workbook, where I can print a certain selection or researcher range of cells in the second tap, which is the home tep. I can choose the self former thing. I can choose my phone type here. I can choose alignment of myself number for mating, and I can also choose which style I want to give to myself. We'll cover this part in Section four of this course on the second step, which is the insert step. I can choose to insert a five a table or a pie chart. I can also choose to insert a normal table or a regular chart here. And there are some other options, such as illustrations or spark lines will cover this within Section five, which is about creating charts with itself and Section seven, which is about creating five charts and five tables within the TERT that which is ill patient layout app. I can choose my page layout so I can set certain margins left or the right I can set a background, which I never really use, But you can change is if you want. Within the fourth step, which is a formula step. I can use certain former's. We'll cover the foremost within Section two and Section three of this course there two ways to insert formless. So one way is to select your formula from the function library. Other way is to directly type it's into yourself, then we also have the data tap here. I can choose my data source. I can said the connection to a sequel server, for example, or I can choose to get data from a certain website. I can also choose a set of filter, and I can also remove duplicates on Do some Other things here will cover the filter and sorting section within Section six. This course, which is about sorting and filtering, put in the review tap. I can choose to correct my spelling. I can also add comments to certain cells and I can protect my sheet same way as I can protect its true the file that in here. So this is also useful if you want to protect certain sheets or protect your Holberg book from being edited within the view tap. I can change my work, she few. For example, I can at page breaks, or I can choose to remove grid lines here within free. Spain's I can choose to freeze certain rows or columns with in my spreadsheet. This is very useful if you want to scroll down with the your worksheet, but still want to see the first row or column off your spreadsheet. For example, if I want to freeze the top row, I can select free stop pro. As you can see now if I scroll down, the top row is still visible. Finally, within the Excel, add in step. I can see all my excel edits, which I have installed. 3. Getting Started with Excel: Navigation and shortcut keys: if you're frequently using Excel might be very helpful if you know some key shortcuts. This is a brief overview of some of the most frequently used Excel shortcuts. So first of all, we have the enter shortcut. If you press enter in a certain cell, it will execute the formula and it cell or to remove one cell down the escape. Key issues for escaping a formula in a cell. So, for example, if you're accidentally editing a formula in a cell, you want to cancel those changes here. You want to undo those changes, you compress escape. The arrow keys are used for moving up or down in excel. So if you want to move one cell up, one seldom left right to compress the corresponding Iraqis. You can also use the tab key to move one cell to the right. If you press the control and a rookie, you move to the last non empty cell within a certain range. For example, if you present control key and the left arrow key, you will move to your last non empty cell on the left. If you present control key and the right arrow key, you move to the not last non empty cell on the right. If you present control key and the down arrow key, you move to last non empty cell at the bottom. If you President, control, key and the donkey, you move to the first cell of your sheets, so that will be the cell at the upper left corner. If you present control key and the key, you will move to the last non empty cell at the bottom right corner. If you select a cell reference and president at four Key, you can change the cell reference from absolute to relative. Or you can change it the other way around from relative to absolute. If you press shift and F 11 you will create a new worksheet with your workbook. If you press control shift and the equal to sign, you can insert a new cell, a new column or in a row. I know go back to excel. Suppose I want to use the enter key to move to the next cell. I step into a cell president, enter key, and, as you can see, I moved to the next cell below. Suppose I want to enter a former for example so G tree equal to so seek re close. No, no press. Enter my former. It's not complete yet, so I get an error message. No. Suppose I want to cancel my formula. Just press escape. I cancelled my formal. I can use the arrow keys to navigate to navigate to another cell Goes the president key to move to the right If I press control down, I moved to the last known anti cell within my call. If I press control left, I moved here last empty cell within my row. If I press control up, I moved to a first known anti cell into my column, and if I press control left, I moved to the first non empty 17. My role. If I press control, I moved to the first cell with him a sheet if I press control. And then I moved three last non empty cell at the bottom right corner of my sheet. I want to enter in absolute self recurrence. I just enter my normal cell reference. I step into my cell reference press F four, and as you see there now, dollar signs at it to my cell reference. So my cell reference has become absolute. If I now press the F work e again, the cell reference is relative. If I press shift and F 11 then I create a new sheet, which is add it to the left of my grand sheet. If I press control shifts and equal to that, I can insert a cell by either shifting the current cells down, shifting them rights, inserting in the Cairo or inserting entire column. Some other frequently used shortcuts in Excel are, of course, control. C Control V. If you want to copy, paste certain cells or control X. If you want to cut a certain cell. If you want to look up a certain value, you can use control F to find or replace a certain value. 4. Using Formulas in Excel: Formula Evaluation: what you're using. Formulas in excel and especially when you're using necid formless, which are formless in formless. You can use the formula Arctic options in Excel, for example, if I want to evaluate each element within my former individually. So if I want a d buck each part of my formula individually, I can use evaluate formula if I want to check my formula errors I can use and we're checking. I can also use trace proceedings to indicate which elements impact the outcome of my formula, or I can use trace dependence to indicate which cells are actually affected by my formula. Finally, you can also consult a help function within Excel, which also offers a lot of information on using formless in itself. And I'll go back to the sales order sheet in Excel. I go to formless, I suppose I want to show all the formless within my sales order sheet. I can click on show formless and as you can see, all the formal asking column G are now visible. Suppose I want to sum up my sales amounts in cell G two. I can enter people to some and select column G. If I not want to trace back the proceedings. I can click all trace proceedings, and, as you see, there is an arrow from column G to sell J two. If I click on trace proceedings again, then I see that sales amount depends on quantity unit price. Suppose I have multiple formless within my sheet that I can use trace proceedings and to raise dependence to get more insight on dependencies between my formers to remove the traces, I just click on Remove arrows if I have a formula which contains a lot of nested formal us . For example, if I want to calculate the discounted seals amount based on the number of units sold, and suppose that I have a 25% discount gaze, a number of units sold is greater than four. 15%. Discount engaged. A number of units salt is greater than tree, and a 5% discount engaged a number of units. Salt is greater than two that I have this formula. If I now want to evaluate this formula, we can evaluate formula. I know. Click on evaluates. As you can see, I evaluate the first condition, which is for greater than one which is to. So I go to the next part is a tree greater and to it was four. So it's for greater than two. Yes, true between his forces for greater than three. Yes, true, a C three, which is for greater and four no falls. So I gets the value false. The value false is 220 times, which saw a 2020 sales amount times 0.85 which is 187. And I get my results 187 so I can use the evaluate former to step into each of my message, formless and calculate my final results. 5. Math & Statistical Formulas: Count(if)(s): Let's have a look at the count and count if formula within Excel the count from allies used when you want to count quantities within excel. If you want to count the total number of cells within a range that meet a criteria, you can use the account if formula, in case he would have multiple criteria. You can also use Count ifs, so basically the count formal is used. When you're working with quantities, let's go back to the sales order sheet in Excel. Suppose I want to count the total number of sales orders. I can use the account formula. As you see I have 64 orders in total. Suppose that I only want to count the orders that took place in store A. I can use count if it's my type equal to count. If then I select my range on Mike Arterial, which is store A. As you can see, I have 36 orders in store A. Suppose I want to count the total number off cash payment orders within store. A. I can type count ifs when I select the store range I type criteria store A. My second Carty arrange will be come I, which is payment methods and they're the criteria is cash payments. As you can see, I have 11 cash payments within store eight instead of vertically typing my formula within my Excel sheet, I can also selected formula from the formula Step figure three formula step more functions Statistical It's like my county formula that I can enter my criteria and range within this window. I can also go to insert function and select the count from a lot from the list of functions . As you can see, Excel also has the option to county total number of cells that are not empty or a total number of blank cells that are empty. To do this, you can select the Count A and the Count Blank Formula with Excel, which is also similar to using the count Former 6. Math & Statistical Formulas: Sum(if)(s) and Average: similar to the count and count a formula When Excel, which is used for quantities, you can also use some and some if, when you want to add up amounts within a certain range, the sum formula is used when you want to add up cell values within a certain range. When you want to add a condition to net range, you can you see some if formula. When you want to have multiple conditions, you can use some gifts important here. Is that the sum? If formula uses arrange as a first element of the formula and some rage as the last element of the former would any some s former? Some range is that the beginning of the formula and the criteria ranges at the end. Fine. I'll go back to the sales order sheet. I can calculate the total sales amount by using the some formula. If I type some and select my some range, I can get the total sales amount, which is 77,468 euros. If I want to get the total sales amounts of Store A, I could type some if I slept. My criteria range, which is column C. My criteria is store A. That's like my some rage, which is sales amount, my percenter. As you can see, I have about 23,000 euros in sales amount in story. If I want to know the total sales amount off kitchen tables with in store A, I can use some ifs. I select a some range, which is sales amount. I select the first criteria Range, which is store column C. I entered my first criteria, which is story. I didn't select my second criteria. Ridge, which is item. Condi and I enter my second criteria, which is kitchen table. And as you can see, I have a tender to 33 heroes in total sales amount. I can also go to insert function and select my function within the drop down menu. I can also go to formless Matt, a selected some or some if formula within this list similar to the some. I can also calculate the average winning a certain range by using the average formula in excel. If I want to add a criteria to that, I can use average. If if I want to create an average with multiple criteria, I can use average ifs. Suppose that I want to calculate the average sales amount within store. A. I can type average if range calm. Sea criteria store a average range seals amount, which is column G. As you can see, I have an average of 641.16 euros within story. Suppose that I want to calculate the average unit price of a kitchen table with in store A type average ifs. Average range is unit price. Column F CARTY rearrange one is column c store Okay, criteria range to its column D kitchen table presenter, and I can see that the average unit price is 277.67 euros. 7. Cell Reference Formulas: Vlookup, Hlookup, Match and Index: the view. An H look up cell reference Formless are used when you want to look up a certain value within a specified range. They look up, stands for vertical look up and looks for a value within the most left column of arrange returns, the value on the same row and the Khan specified in the formula. The H Look up, which stands for horizontal look up, looks for a value within the top row of arrange returns. The value on the same column in the road specified. To illustrate this, I will show this example, but if you look up, we're looking for the look of value job within the table. A 12 d six. We want to know the occupation of John, which is calling three occupation. So if you look up for my looks, really value Jon in the left. Most column of the table, which is in name column. Then it returns the value on the Turkey column, which is the column occupation and returns this value, which is a software engineer. So first informal looks at the left. Most column. When it finds a match there, it looks for the Third column, which is specified in formula and returns this value. The H look up formula can be used in the same manner, but the difference here that the h look a former looks for the match with me Top row. As you can see here, it looks for occupation within the top row, and then it returns the value on the fourth row, which is software engineer. The index and match formula are used. When you want to look up a cell reference within a certain row or column, we want to return the position of a certain cell within an airy the index from returns the value of a certain row or column with an airy So, for example, if you want to know the elements on the ter TRO entered column, the match formula on the other hands returns the position of a cell within a certain area. Let's have a look at this, this example. So, for example, here I want to know the elements on the Fort Row and third column of the stable like use. The index formula specified my range here with specified a roll number, which is rolled four column numbers country and at the intersection. I find the value software engineer. The match formula can be used to find the position of a value within a certain range. For example, here I want to know the position off John within the name problem. So I used the match formula to look for the value Jon within range a one to a six, which is at row for If I now go back to the exercise of Step Within Excel, I can use the V look up commands and the H look up commands to verify which payment method was used for order number 16. By using V look up, I can type equal to be look up, then specify my look up value, which is 16 because I want to look for order number 16. I specify my table airy, which is a 12 I 65. I specify my call him in next number, which is column nine, because I want to know the payment method, which is called nine and then I want to have a exact match. I presenter and as you can see, the payments met it used for order number 16 was a bank transfer, claimed methods the same manner I can use. H Look up. If I type h look up, then my look of value will be payment methods because I want to look on the top row. My Harry will be serious orders a want to I 65 and I want to find the payment method Use for order number 16. Order number 16 is grow 17 within my table, Harry. Then I say I use calls. Press enter. As you can see, I also get bank transfer here so I can either choose V look up or h look up to get the same value. If I want to find the value of a cell given a certain column and row, for example, if I want to find the value off cell D five within the cells order step, I can use Index to do this. I type in next. And then I specify my airy. I specify my rule number, which is rule number five, and I specify my column number, which is felon for because column These calling for press enter And as you can see, the value of this intersection is sofa. You can see Selvi five corresponds to sofa if I want to know how many orders took place before the first order was placed in store B, I came was a match formula. As you remember, the match formula is used when you want to find the position of a value within a certain range, so I can type equal to match specifying my look up value which is store be. Specify my look up area, which is column C within the sales order tap so I can type C 12 see 99 for example. I only can use a column for the look a Perry. So I cannot use the whole table because then my formula won't work anymore. And I say match type exact match, which is zero because I do not want to count the first row because that contains Heathers. I do minus one press enter. As you can see, the first order restore be was after four orders 8. Date and Time Formulas: Today, Days, Networkdays: Excel also has formulas to calculate days in times the tree. Most used formulas within Excel for Basin times are two day days and network days. Today returns today state. If you want to have today's date in today's time, you can use the now formula Days returns the number of calendar days between two dates. A natural base returns the number of work days between two days, so network days actually Onley takes into account the number of work days, which doesn't include weekends and holidays. If I now go back to the Excel sheets, I want to know today's date. I can type equal to today. As you see, I do need to enter any elements. Just press enter. I gets two days dates. If I want to know how Maney counted days, there are between two day and last orders date. I can type equal two days 3 60 because I want to use calendar days, not work days. Enter my start date, which is the last date within the order step. So I type lacks. Give me, which gives me the maximum Select the order date range. So this gives me the last order dates for end date. I enter today's dates. Press enter. And as you can see, I have for, ah, nerds days between two days date and last order date. If I want to know how many workdays there are between those two dates, I can used the natural days formula. So I type equal to natural days again. For a start date, I enter Max. Select the order dates. Call him for end date. I select today's date. As you can see, I have Onley 290 work days between those two days. Suppose that I want to have the day, month and year of a given date. Separately, I can use the They form A to get the day sidetracked day. Then select today's date. Press enter and as you can see, this returns me Then, which is a day off today's date. I want to get the month of two days dates. I thought month Select today's date. Get two months. I want to get the year of a date. It's like to hear it's like my dates. They get 2017 similar to today. I can also use now. I talked equal now, which also gives me the time. So now I get not only the date but also the time. If I want to get the hour minutes, we can get our selected dates time. I can also get the minutes by tapping minutes. It's like my date time and get the minutes. 9. Logical Formulas: If, And, Not and Iferror: Excel also offers the possibility to work with logical formulas. Logical formulas are used when you want to build a logical reasoning within excel. You may want to use if, if you want to return a value based on a certain condition, you can use ends if you want to return. True, if multiple statements are true, you may want to use not if you want to return. True when a certain statement is false, return falls when a certain statement is true, so they're not. Formula actually gives the opposites off urological test. You can also use if error if you want to return a certain value in case of a letter, which can be useful if you want to replace formula errors by fixed values. Let's have a look at this example Here we use the formula to look for the value job in case the value of his self response to John. We put a match, and in case there is no match, we put the value no match, and, as you can see here in the church road, there is a match. We get the years old match in the other cells. The result is no match because the name does not correspond to job. Here's an example of the end from a here we have two statements. One statement is that the H should be 29. The second statement is that the occupation should be software engineer and, as you can see, neutered row, both statements are true, so we get the end result. True for the other rose, the statement is false. So we get the result falls. Here's example being not formula, they're not formula inverts the result of your statement. So, for example, if we have a true statement and will give false if we have a false statement, it will give true because only neater dro we have name it corresponds to John. We have a true statement on. Does he enters old is false formula Errors can occur whenever you have a formula, it's not correct. For example, you can have the name error. When Excel does not recognize a tech senior formula, you can have a value error when there is a wrong type argument within your formula. When you divide by an empty cell or divide by zero, you get the divide by zero error. You can also have a reference Edward. When there is a cell that does not exist, your formula refers to that cell. This could happen when you have a column that has been removed and therefore resell does not exist anymore. Finally, you can also get a editor when the column width is not wide enough to display all your characters. Let's go back to the Excel Sheet. Suppose that we want to add a column. Sales amount exceeds threshold next to the payment amount column to check visas amount exceeds the threshold of 250 euros. We can add a column here. This is order tap. We call it seals amount exceeds threshold and then we type are formula, which is equal to if sales amount, it's greater than 250. If this test is true, we say yes. If this statement is false, we say no. So, in that case, the amount does not exceed the threshold. We didn't feel the formula till the end next to this column. We want to add a Albert flag because we want to know which of these seals amounts. We're not spade by bank transfer, so I type out it flag within my formula I type equal to and because I need to check two statements, the first is it seals amount should exceed threshold. So I typed j two equal to yes. Secondly, I type not because I do not want the payment methods to be bank transfer pain minutes. So I think not payment methods equal to bank transfer which will give me all the payment methods that are not made by bank transfer this case cash payments. So now I have a statement with two conditions. Firstly, disease amount should exceed threshold and secondly, the payments should not be made by bank transfer. I now feel this formula to the end. And as you can see, there are two orders and have a cash payment amounts created and to mid 50 year olds in case there would be some data missing or some data would be in an incorrect formats than my formula will not work and will actually given error message. If I do not want this, I can use, if ever, for example, if I would change the unit price to a non numerical value, then I get the value error because formalized, not recognizing the value in cell F to, which is because it is in non numerical value. If I want to replace this error by error message missing data I can use, if ever and had devalue if error missing data. As you see now, the audit flag has missing data and does not contain the value. Our message. This is very useful if you have multiple calculations on your sheet and you want to find a proper way to deal with error messages. 10. Text Formulas: Trim, Concatenate, Left, Mid, Right: when you're importing or copy pasting external data from a website or another source into Excel, you might want to reshape the format of this data. Excel offer some functions to manipulate text data. For example, the trim function removes some of the leading or trailing white spaces within a text. Con. Captain eight on the other hands joins several texts into one cell and the left middle right function return a number of characters of a given cell. The left function returns a number of characters starting from the left. The mid function returns a number of characters, given a certain starting position and length, so it takes a number of characters in the middle of a text and the right function returns. The number of characters starting from Genz attacks was starting from the right. This is an example of a trim function. As you can see, the movie titles contain some leading wide spaces and also additional white spaces in between two words and after using the trim function, all these extra white spaces are removed, but only the single white spaces are kept. This is an example. They can captain eight formula. Suppose I have two columns. One column is the movie title on the second column is the release here of the movie, and I want to put the release year in between brackets. After the movie title, I can use the Katyn eight formula so I put gas in eight than my first cell, which is a movie title. Then, in between semi columns, I put a white space in a opening bracket. Then I put my second cell, which is the movie release year, and then I put in between semi columns, my closing brackets in the white space. And as you can see, the result is that I have in my movie title, then the white Space opening bracket, My release year in the closing records. Additional white space. So, as you can see, I can either choose to enter a cell reference in my concocted a formula or I can enter my text in between semi com's. This is an example of the left mitt and right formula with Excel. Suppose that I have a movie title, which contains category re title on the year of the movie. Suppose I want to extract the 1st 8 characters in the movie tied I can use the left formula to extract the 1st 8 characters. As a result, I get Thriller. Suppose I only want the 1st 17 characters starting from character 11 which is the title. Then I can use the mid formula. I especially find my starting position, which is 11. Position 11. I have be and my length is 17. So if I can't starting from B the character 17. It's nine. So my result would be Blade Runner 2049. Suppose I only want to last four characters of the title, which is the year number liking Jozy right for Mama. I specify the number of characters starting from the end, which is for and as a result, I get 2017 and I'll go back to the sales order sheet. Suppose I want to add a column order tech in between order data store. I go to the sales order sheets and I insert a new column. My name is column order tank. The order Tech has the following syntax. It starts with the order number falling by the store A or B underscore item underscore quality. So type equal to Katyn eight and as a first element. I use the order number which is held a two. Then I use the right formula to get the last character of my store because I only want to have a or B I didn't want to have store. So what rights? D two which is my store number of characters. One Because I only want you lost character. Then I use underscore. Then I use the item name. Then I use underscore again. So because I have a text here, I put it in between semi columns and then I used quantity. Press enter. And as you can see, I have created my order tank and I feel the formula. And as you see all the order techs have agreed. 11. Fill Series, Absolute and Relative cell references: to refer to a cell in Excel, you can either use an absolute relative cell reference when you choose to use an absolute or fixed cell reference. The cell references fixed. It does not change when you're formalize, copied or field, not herself. If you use a relative so reference than the cell, reference is variable and the row or column changes when the formalize copies to another cell by default. Excel makes the cell reference variable so relative, but you can also make its absolute by adding a dollar sign next to your column. Paro. You can also do this by pressing the F four key in Excel. So by standing on your cell reference and pressing F four, if you add a dollar sign next to the row and column of a cell reference than both the row and column fixed, so both of them are absolute. If you only had a dollar sign next to the column off your cell reference that only the column is fixed and the roe is not fixed to the road with variable relative. If you only adds to the dollar sign next to the road, then the rope is fixed and the column is variable, so in case the column is fixed and the road was variable Onley. The Celera will change when you copy the cell to another row. But the column will not change as the colony is fixed in case you're ro is fixed. A column is variable than euro will not change when you copy the cell to another row, but your column will change if you can't be the Celt. Another column. I now go back to my sales order sheet in Excel, and I want to read a column. This counted unit price on discount sales amount in my sales order sheet based on the DIS countries in Cell B 36. So I won't be 36 to be a fixed cell reference. So a absolute celebrants and now and my two columns discounted units, price and discounted seals. The Mount a type equal to unit price times one minus my Select the discount rates and they press four on the cell reference. Beat 36 to make it fixed Knepper Center To calculate this current sales amount, I also that equal to hey, selectees steals amount times one, minus the discount rate and I make B 36 a fixed value fixed so Reference Medical Center to feel the discounted unit price in this kind of sales amount formula, I can double click on the plus sign in the bottom right corner of the cell. I double click, considered to be formless field for the entire range of the table. Do the same for discounted sales amounts, so I double click on the plus sign. Oldie values are calculated. I can also do this even more quickly by selecting boats, the unit price and sales amount discounted formula and then double clicking on the plus sign. And as you see, both ranges are now filled at once. And as you can see, so be 36 states fixed, and these sales amount and unit price sell references invariable. I suppose that he only wants the column to be fixed. Then you can at a dollar sign next to the column, and you don't add a dollar sign next to the road. I suppose you want the road to say fixed nothing color, and you can at a dollar sign next hero and do not add a dollar sign next to recall 12. Insert, Delete and Hide cells, rows or columns: next, they'll you can insert leads or I had certain cells or sheets. This can be done by either going to the home tab and clicking on insert or delete. In case you want to insert or delete a rope or column. You just right click on euro or column and press, insert or delete. If you insert any row, the other rows below will be shifted down. If you insert your column, the other columns on the right will be shifted to the right. You can also insert a new sheets by going to insert on the home tab or by clicking on insert worksheet at the bottom left corner of your workbook or by using the shortcut shift F 11. Finally, you can also hide certain cells or sheets. If you want to hide a cell, this will only have effect. When you use protect sheet or protect workbook. You can also hide certain rows or columns by right clicking on the roller column and pressing hide. You can also hide a sheet by right clicking on the sheet you want to hide and clicking heights. If you want to unheard your sheet, you can right click again and press on height. If you want to unheard a row or column, you can select the previous and the next Roar column and click a night. On the other hand, if you want to unheard a certain cell, they protected sheet over a book. You have to un protects the cheat or workbook, which your password. You can also choose to lock certain cells or an entire sheet from being edited by also using protect sheet or protect workbook. If I now go back to my Excel file, suppose I want to insert a new cell. I can go to insert cells, and as you can see, I can either choose to shift the current cells down or shift because themselves were right . I choose to shift the grand cells downs so I select shift cells down. Press OK, As you can see, the cells are shifted down. Suppose I wanted lady cell again. I click on delete cells. Shift sales up okay and the cell is lead it. Suppose I want to delete the calm currency, my sales order step. When I go to my sales order step, I select the column. Currency. I right click the elite As you see, the column is deleted. I can also insert a new column by right clicking on insert. And as you can see, a new column, it's at it. Suppose I want to hide the column. Sales amount exceeds threshold like a right click on this. Calm and press high. And, as you see the column is, hide it because you call him. It's not visible anymore. And as you can see, call Jay is also not visible. If I want to. A new height Column J. I just select the entire range from column to column K right Click a night and calm James visible again. Suppose I want to hide a certain sheet. So suppose I want to add my exercise of sheet. I can right click on my exercise of sheets on breast hide, and as you can see, the sheet is hidden. If I want to show the sheet again, I can right click on my sales or the sheet quick on high, and I can a nice my exercise sheet. If I want to insert a new sheet, I just click on insert worksheet. If I want to remove a sheet, I just right click on Do it. I can also move or copy. She'd likely on move or copy if I want to hide all my cell formless within my sales order Step I select all myself. I right click former It sells. I go to the protection tap. As you can see by default. If I protect my sheet, the cells will be locked but will not be hidden So formulas within cells are still visible . If I want to hide my foremost within myself, I can select in press OK, And if I now go to review protect sheet then I can enter my password. For example I think this okay, we re enter my passwords test press OK. And as you see now, my formless are hidden. If I go to, for example, sales a month, which is quantity times unit price. I see that the former is not visible anymore. And if I click on the formula, then I get an error message saying the cell or charge that you're trying to change his protected and therefore read only to modify a protected seller Charts first removed the protection. So if I click on one, protect cheat again and enter my passwords. I can protect myself. As you can see now the formulas are again physical. I suppose I still want myself formless to be visible. But I want to protect myself from being edited. I can right click my cells go to form it sells and the select hidden no myself only locked from anything. Press OK if I know protect my sheets But I can still see myself formless. But when I type into myself I get a message that the cells are read on Lee and are therefore also protected. I can also protect my entire Berg book by clicking on protect Workbook entering a password This I re enter my password test. Does he see now my entire Berg book is protected? 13. Number and Cell Formatting: there are many options and excel to form it yourselves or the content of yourselves. If you go to the home tab, you can see that there are three ribbons, font, alignment and number where you can define yourself on text formatting in the front ribbon. You can choose your fault, character size or even at borders to assert themselves in the alignment ribbon. You can choose to increase or decrease the margin between the cell border and the text of yourself. You can also merge certain cells into one cell, or we can wrap the text of a certain cell so that the text becomes visible on multiple lines within yourself. If it exceeds the cell border, you can also choose your number style so you can choose if you want to have percentages if you want to have dollars. If you want to have a date for months, you can also choose to add or remove decimals in your number for months. In case you want more options. You can also right click on a cell and shoes form. It sells, and I'll go back to my sales order she to excel. Suppose I want all dates to have a valid date. Formats go to the number former thing and select short date. The long date formats will also give me the day, but I choose to have a short date. Formats. Suppose I now want all header row title savvy 30 degrees counterclockwise rotation. Tonight I select my hetero. I go to the alignment Riman and I choose angle counterclockwise. And as you see now, all the elements in the top row have a 45 degree counterclockwise rotation. Suppose I want the charity degree rotation. I right click on form. It sells, I go to alignments and I select orientation 30 degrees. I click. OK, as you see all the elements in the top row now have a 30 degree counterclockwise rotation. Suppose I want the text in the hetero to be bold. Then I select the hetero and click on Bolt. Suppose I want all text too heavy. Left text alignment. I press control A to select all my fields and I click on the left alignments. Sign here. As you see all my texts now are aligned to the left of myself. Suppose I want the top row to be frozen so that it's kept visible. When I scroll down, then I select my top room. I go to a few Free Spain's and I freeze the top row. I can also choose to freeze the first column if I click on Freeze First Column. I can also choose to freeze both the first column on the top row by clicking on Selby, too, and clicking on Free Spain's, which will freeze all the paints at a given intersection. Suppose I wanted column with of all columns to be set to 14 that I can select all my columns. Then I go to for months column Woods and I choose column with 14. I can also choose to how to fit the column wit, which will fits the column Woods to the content of each cell. If I go back to the view tap, you can see that there are some other options, such as view grid lines. If I d select grid lines, then my grid lines are not visible anymore. Suppose that I want all the currencies within my sales order sheets to have a euro currency . Then I can select the unit price sales amount Discount iTunes price on discount sales amount I go to number and I said like currency. As you see now, these amounts have a Euro currency in front. Suppose I want to add more decimals. I can click on the increased decimal sign here I can add decimals. If I want to remove decimals, I can click on decrease decimal. Suppose that I do not want the euro sign but I want the letters. He you are in front of my number. Then I can go to more number formats or I can right click form. It sells go to number And here I can define the symbol. So I type You are that's liked euro You are from drop down list. Unless you see now the Euro sign has been replaced by letters You are If I want to customize this former I can even go to the custom tab and create my own custom form a type No, I click ok and all the euro science have been replaced by the letters You are 14. Conditional Formatting: engaged. You want to form itself based on a certain condition or rule. Then you can choose conditional formatting in conditional formatting. You can choose how you want to firm it a certain range or selection off cells based on a given condition or rule. For example, you can choose to form itself is based on a certain text or value condition, Or you can choose to form a Onley, top or bottom range of cell values. You can also define a formula that defines which sells should be formatted. There are many different former types you can choose from. You can choose to have bars, skills or even I consents to form A T cells apply to your given rules in case he would have multiple rules for a given selection. You can choose the order in which you want to apply rules by going to manage rules In the conditional former truce manager, you can see that you can choose which rule to apply first here. You can also choose to not apply a second rule in case the first rule is met by selecting stop. If true, I know, go back to my sales order sheet, suppose I want to mark the cells in rats that haven't added flag equal to true. Then I can select my column audit flag, go to conditional formatting new rule and former Onley cells that contain sell value equal to true. I slept my formats, which is Phil Brett? Yes, OK. And as you see all these cells that are audit flag equal to true our now former that in red however, suppose that I want the entire oh to be marked in red. Then I have to change my rule. So I go back to conditional formatting. I now go to manage rules. Did you see my rule is visible here? First of all, I want to edit my range. So I go to applies to and I select my entire table Epicenter. Now I go to edit room so I select my room, go to any true and I changed my rule type to use a formula to determine which sells to form it. And here I type equal to que one equal to true. I had a dollar sign next to my column k. They do not add a dollar sign next to my row because my rope is variable. My column is fixed because I'm checking for the value. True. In column K which is audit flag, I press OK, we could apply. Okay. And you can see that the entire row is marking Rhett. In case that the order flak is true. Suppose I want to add another rule to my conditional formatting that I want all rose to remark in green in case audit flag is equal to false the night go to conditional formatting manage jewels. I create a new room. It was a formula to German which sells to form it a night press equal to K one with com que fixed equal to false my formats is green click Ok, okay. And I copy my range for my other from and I click will apply and I can see that older Those are creating Casey ordered Flak is false No, there was a red in case the order like it's true. Suppose I know. Want to delete the first rule? I just select the first rule and click on delete. True, I can also change the order deals by clicking on the move down or move up Parasite Suppose that I click stop. If true, then the 2nd 2 will not be executed in case the first through and the second who are both through. So I know deletes the first room completely true. As you can see, the rule is now not visible anymore. We can apply taken. OK, I go back to the exercise of step. Suppose that I want the columns, sales amount and discount sales amount. Seven. Aiken set to distinguish treat categories. Sales order amount less than or equal to 50 seals. Older amounts in between 500 years and 1002 or seals. Order amounts greater than 1000 0 that I go to the seals order tap and I selected column. Seals amount on discount sales amount. I click on conditional formatting Heiken sets, and I select the rating I concert at the bottom. Now go to manage rules. No edits the I can set rule and I can change the values here. So I want a complete star. I can When the value is greater than 1000 type number. I want healthy store when the value is in between 501 thousands. So when it is greater than or equal to 500 choose number, and I want an empty store when it is in between zero and five hundreds. There are some other options, like data bars. So if I select, for example, my column seals amount, I go to data bars, then the bar is filled according to the value in myself, so the larger the cell value, the larger delights of the bar. I could also use color skills there. Red represents a lower value, and green represents a higher value. I can also highlight unique or duplicate values by going to highlight cell rules and selecting duplicate values. Are going to manage rules, a new rule and selecting the rule type from its on lee unique or duplicate values. 15. Format As Table: suppose that I have a range of cells, that I want to form it as a table in Excel, that I have different options or table styles. I can choose for a bandit row or column style where I have striped rows or columns. I can choose for a regular table style that does not have banded rows or columns. I also have the option to form with my hetero, which is the top row of my table. I can add a total room which will give me by default the some off all values at the most right column off my table. I can also choose to add a total to other columns, and I can even use other functions instead of some, such as count maximum for average instead of former thing a range as a table. I can also choose to do it the other way around so I can choose to convert a table to a range. And I can also change the name of my table, which I can use to refer to my table in yourself. Formula. Working with tables instead of ranges in Excel has some benefits. For example, you can easily sort or filter data within your table, and you can also easily at column or row totals, and I'll go back to my sales order sheet in Excel. Then I can select my range of seals orders and I click on insert table. My table has headers. This is a range of my table, so I click OK, and as you see, my range has now been converted to a table. I can choose my table style here. So suppose I want this table style that I can select this style. I can also add a total road by clicking on total row. And as you see, my total row is at it, the bottom of my table and the total discount of sales amount is calculated here. I can also it's a total for sales amount here, so I select some. Suppose I want to have the average total. Then I can select average. As you see the average sales amount on average. Discount of sales amount is calculated to a movie Total room. I just uncheck the total road. Suppose I want to resize my table. I could just click on the resize table and I can edit the range of my table year. I can also rename my table year, and I can convert my table back to arrange by clicking comfort to rage. Click yes. And as you see now, the table is converted to arrange. But the format of the table is kept, so I still have the same blue layout as in my table. But my table is now being converted to arrange. I can easily spot the difference between table and a range because I do not see the the filter and sorting herro in my head row and also I do not see the table to step. 16. Copy and Paste Options in Excel: If you choose to copy and paste cells within Excel, you may notice that there are different based options. For example, you can, based on the values of a cell, which will Onley based e values and not the formless. You can also choose to paste the formulas of a cell and not the values. And you can also choose to copy based values and form us without the cell for months or with the cell for months. If you copy a row, you can choose to either base it as a role or base it as a column so we can actually transpose your own or column in Excel. And you can also choose to keep the column width off your source or your target. Suppose I want to copy based the sales amounts in my sales order sheet to a new tab that I can select this column. Then I press control. See, I opened a new worksheet Press control V, and as you see now, the cells have a reference letter. That's because I base it the self formulas instead of the cell values. Suppose I want toe based the cell values and keep the source for my thing. I click on the control Aiken and I go to based values, values and source. Former thing, as you see now, the values or copied instead of the self formless by default. Excel, always based diesel formless. And in case Excel does not recognize your cell references within your former, you will get a reference Arab. Suppose I only want to base the values and do not want the formatting. Then I press control V again, and I choose based values, and I get the cell values without informative. Suppose I copy based values from a text editor to excel that each new line will be converted two in a row, and each new step will be converted to a new column. Suppose I goto a text editor and enter a step Be. Then I press enter and I enter one step two percenter again. When I enter three Step four Copy Paste this into excel. Then I see that I have two columns am be and three rows total. You can also choose to use text to columns to manually define your rows and columns. For example, if I have a space, be a presenter, they have one space to enter three space for I copied this into excel and I want to have a B into two separate columns that I select. These three cells click on text to columns. I choose my original data type press next, and I defined my separation. The limiter, which is space. I click next. Click finish. Did you see? I converted my text to columns. 17. How To Create a Chart in Excel: to create a good that report in Excel, you might want to add some visual elements, such as charts. Charts can be incidents into your dashboard by going to the insert. Step is selecting a chart type you see have many chart types column Charts line charts by charts, bar charts and also combinations of different chart types. If you want to insert a chart, you just select your job data and choose one of these chart types afterwards. He might want to customize each of these charts elements, which can be done any chart layout step, which is visible after you created your charts. Most chart types, such as column line or scatter chart types, have a horizontal and vertical access. You can even any secondary, horizontal or vertical access and customized the access title or labels. You can also add a title to your chart and at labels to your data and at a legend to your chart. In case you're working with multiple categories, such as in a pie chart or stacked column or bar chart, I go back to my sales order sheet in Excel and suppose that I now want to add a chart showing the sales amounts over a period of time. Then I select my column order. Date. My column seals amount, and I go to insert line and I select a line chart. Suppose that I now want to move this charts to a new sheet like Right click on the charts. Select move chart. I enter a new sheet, which I call sales overview. Click OK, and as you can see, the chart is added to the worksheet sales overview. Suppose that I want to changed the title of my charts. Then I don't look like on the chart title, and I enter a space in between seals and amount, and I entered hero in between records. I want to removed elections. So I click on the legend seals amount. We complete and the legend is removed. Suppose that I want to at its my numbers in my vertical access. Then I click my vertical access numbers and I right click form of access. I go to numbers and I select decimal places to because I don't want three decimal places, great clothes. And now now there are only two decimal places if I want like and also at a trend line by going to trendline, for example, choose a linear trend line. But I do not want this, so I remove the trend line. Suppose that I do not want any horizontal grid lines and I go to grid lines, Primary or isn't grid lines? I select non. Suppose that I want to form of my data Siri's than I can right Click My Gator, Siri's former data, Siri's and for example, I can choose to decreased the wit of my lines. So I go toe line style, go to wit and decrease the width to one point for more format options. I can either go to chart ooze formats or I can right click on the elements. I want to form it to change the chart, type or data Siri's. I go to design and to change the elements within my layout, I go to layoff step. Suppose that I know want to create a line chart, which shows both the sales amount and discounted sales and out. Then I can select my columns, order dates, seals amount and this counts sales amount, and I go to insert line. I select a line charts. As you can see, I have now created a line chart, which combines both the sales amounts and this counted sales amount into one chart. I now moved his chart to the sales overview. That and I now want to change this charts because I do not want to have lines, but I want to have a stacked column chart. So I go to design change chart type, and I select the stacked column chart. If I select Clustered column, it will give me a separate column for both sales amount on discount sales amount. If I select stacked column, it will give me one column, which combines seals amount on discount sales amount. I can also add a chart title simply by going to chart layouts and selecting the layout upon . And I called his chart seals and miles Okay, euro between brackets and I changed form of my vertical axis labels, so I only have two decimals. I also add a title to my Dashboard seals overview, and I also at the total seal the mom, which is equal to please some of the sales amount. You see this order step. I also add another chart, which I call seals by store and I calculate the total sales amounts off store a total sales amount store. Hey, the total sales amount store be, which is equal to some. If the range store bacteria store A some rage seals the mountains. Do the same for a store. Be now. I want to insert a pie chart so I select my total sales amount for each store and I go to insert by charts and I answered my by trucks. I also want to add a title to my chart and also at percentages. So I select Layout six, and I changed the title to seals bar score and puts percentage in between records, but that I also want to add The euro amounts to my chart than I right quick on my data, Siri's and I click on former data labels. I go to label options, and I select value to change the format of my percentages. I go to number and I enter to that's symbols percentage and press clothes. Finally, I go to for months and change the labels to a white text field. I also changed my text field to bolt and my sales Overview Data board is now complete 18. Choosing the right Chart Type: there are many different chart types and excel, and therefore it is important to choose the chart type that best fits your purpose. For example, if you want to show a trend over a certain period of time, you could choose a line chart. You can also choose to create a pie chart, which is used when you want to show a distribution or proportion. For example, if you want to show the distribution off sales by region, you can also choose a bar or column chart, which is mainly used when you're using categories. For example, when you want to compare the sales amount over different product categories, you can also use stacked or clustered columns. For example, when you want to compare the amount of sales over different product categories from this year last year, you can also use a area chart which is suitable for showing the magnitude over a period of time. For example, if you want to show volumes, this might be suitable. You can also use the stacked area chart, which is suitable for showing the distribution over time so you can look at this as a pie chart, which also adds a time dimension. For example, if you want to show evolution off the proportion off male and female inhabitants in a certain population over time, you can use a scatter or bubble chart in case you want to show a correlation between two variables. For example, if you want to show the correlation between age and the chance of having a heart rate disease, the bubble chart at a turd dimension and is used when the dots in your scattered plots represent a certain size, for example, the number of inhabitants within a certain area. And I'll go back to my Excel sheet. And as you see, I already have two charts in my sales overview. The sales amount chart shows me the total sales amount on discount sales amount over a period of time. Because I have a time that I mentioned on my horizontal access. It will be even better to use a line chart in this case, so I go to design change chart type, and I select a line chart. As you see now, I have a line chart, but the difference between my sales amount on discount sales amount it's not that visible, so I could use a area chart in this case, too. Clearly, I like the difference between my sales amount, discounted seals amount and that using a regular instead of a stack chart because I want to show the difference between the sales amount on this kind of sales amount. And I do not want to add both of them up for the sales by store chart. I chose a fight chart because I want to show the distribution of sales for each store. Suppose I want to add a chart to show the number off items sold within each store. And I have five categories kitchen table, chair, bet sofa and TV stand that I first create a table. I have two columns. Store A A store. Be no ive five rules. Get your table chair, you bet. Sofa on TV stand. My title will be number off. I sold and I used the some its former. I specify my some range the sales order step, which is quantity. I specify my first party, a range which is store. I specify my criteria, which is store A. I specify my second criteria range, which is I tell, and I specify my second criteria kitchen table. These colds are all fixed, and then my store row is also fixed on my Hi item column is also fixed, and now I feel this former and I have my number of items sold for each store and item category. Now I insert a chart, so I go to insert column clustered column. And as you see, I don't have any data selected at the moment. So I go to select data. I add my first legend, serious name, this story a serious values. This this Siri's I am my second. Siri's serious name is store Be. That's like my Siri's values. Okay, and then I I want to change my horizontal axis labels because now I only have 12345 But I want to have the I think, getting renames. So I go to horizontal axis labels that it's and I select my range. Okay, my charter screed. Suppose I want any title and I go to chart title above chart on any title number tightens sold. As you see, there are many other chart types you can choose from, so you can just play around and creates a new charts to prove your dashboard 19. Sparklines: the version of Microsoft Excel 2010 and beyond offer the possibility to create spark lines so spark lines are actually charts within a single cell. These single cell charts are actually very easy to create are also very compact and easy to feel or copy to other cells. There are many different spark line chart types You could choose from a line spark line, a column spark line where a wind lost spark I. You can also choose to add certain markers, or you can choose to highlight the highest or lowest point within your spark line. You can also choose to highlight the first or lost or negative points with your spark line . If I now go back to my Excel sheet, I go to the sales order step. And suppose I want to add a new column, which I call order sales trends, and I want to add a spark line to this column showing my lost five orders. Then I can go to insert spark line line. I select my data range. So I start from the fifth cell because I want to show the order trend from the lost five sales. I select my data range. That's like my location, which is so number six and six. And if I now fill this cell too, the entire range with my column, then I see that all spark lines are created for each row. Suppose that I want to highlights the lost order. Then I go to the design tap and I select last point. I can also change my colors so I can change to this color. I can even change my marker caller or spark color here. And finally, I can also change this line Spark line to a call him spark line, my selecting convert to calm spark line. I can also choose a win loss park line in case I would have positive and negative amounts. I can also show my I point low point negative points. First point. As you see spark lines are a very nice way to visually highlights your current cell within a certain Trent No, there for also a very powerful tool when you're creating visual dashboards. 20. Sort: there are different ways of sorting data and excel. One way is to use sort with a filter if you add a filter to a certain range of cells that you can also sort his data using the filter arrow sign. It can also sort data without using a filter simply by going to the sort filter menu and selecting sort here. You can also choose to add multiple sort levels, which allows you to build a certain hierarchy. Not only can you sort on values, but you can also sort on cell color, font color or even sell I can, which is especially useful in combination with conditional formatting. Fine. I'll go back to my sales order sheet in Excel, and I want to sort my sales orders according to the order dates, and I can simply click in the column order dates. Ni sore from Louis two oldest Suppose I first want to sort on order dates, so I first want to start the newest to oldest order dates and then want to sort on store I first want, have store a and then store be then I can first sort on store, and then secondly, I saw it on order date. Suppose that I would convert my table to a range that I do not have my arrow sides in my column headers. So then I have to go to data soared I, specifying my first sort level, which is order date I sort of values newest to oldest. Then I had a second level where I source by store values also basically click. OK, And as you see now, my data is first sorted on order date and then sort of storm. Suppose and I want to add a turd Drew where right want to sort according to the sales amount? Aiken, I won't have full stars on top off stars in the middle and empty stars at the bottom. Then I can go to sort. I had a sort level. I soared by seals mount I sort on So I can I specify Gold Star on top. Then I had another sorting level. I specify seals mount still Aiken off coaster took and then I had another sort level. Specify seals alone's so I can Silver Star the night Chris. OK, if I would add my filter again and I click on the filter zero sign. Then you conceded I can also choose sort by color and here I can select sold myself. I can. 21. Filter: similar to sort. You can also add a filter to arrange yourselves in excel. There are many options off using filters in Excel. The easiest way to filter for certain values is to just select those values in the filter selection area. You can also add a text filter, my selecting one of the text with options. In this list, you can also filter by color or by I consents similar to the sword functionality. You can also have multiple filters within the custom. Also filter menu where you can use end or logical operators or even wild cards to build a certain filter logic. In case you would not be familiar with wild cards, the store or Asterix character represents one or multiple undefined characters. The question. More character represents one single undefined character. If I now go back to my Excel sheet and I want to show only the orders of a place in store A that I can click on my store filter and I select story, I can easily see that the filter is now active because I have a filter sign next to my arrow in my store column. And if I slightly move over the arrow that I can see that my fielder store equals two story to clear my filter is simply click on clear. If I want to add a text filter, then I can go to one of my columns, for example. Item. I click on the Arrow sign and I go to text filters. Select one of the options, for example, equals and I enter my criteria, for example, equals Chair, which I can also select in the drop down list. Four equals bet, and this will give me all the chair and bet orders. I now clear his filter and suppose I don't. I want to do the same thing but using the order tag, then I can go to the column order tack. I click on my filter arrow. I go to text filters contains and I enter contains chair or contains bit, which will give me the same results. I can also use a wild cards, so I clean my filter, go to the call order tack text fielder's equals and night enter equals store, chair, store or equals store, bet store. The store will represent any Siris of characters, so this would give me the same Result week, okay, and I get the same results. Suppose I only want to display the orders within a sales amounts that have a Gold Star, Aiken, that I can go to sales amount. I click on the filter arrow filter by color, and I feel that by so I can gold star. As you see, there are many different ways of filtering data in Excel. And then the next lesson, we will look at some more options for using advanced filters in Excel. 22. Advanced Filters: barred from regular fielders. You can also use advanced fielders in excel. These allow you to add multiple filter criteria. You can also easily be modified because he specify your field of swimming in certain filter range. Within this field, the range You have to specify your columns on which about the filter and also the values for which you want to filter. It is very important that the names of your columns within your filter range correspond to the names of the Comes with your Lestrange so this strange is arranged or table off values that you want to filter, and the filter range contains your field criteria. Let me now demonstrate this in Excel. If I open a new sheet and I have a list range on a copy based, these values to excel in every criteria range my copy paste my filter values. Then I can use these criteria to filter this range. I do this by going to data filter advanced. I specify my list range, which is this range. I specify my criteria range, which is this range, and I click OK and that is he now. The values within my list range correspond to my criteria rage if I changed your values within my criteria range. For example, if I change value one value to and I reply my filter going back to advanced click OK than my list range contains a new filter values. Clear the filter. And as you see, I can easily modify my crit era range to update my list range. Suppose I want to add a customizable advanced filter in the sales order tab to filter on order date store item. Then I go to the seals. Order tap. I create my advanced filter, which I call filter criteria but is in bold. Then I enter my first criteria, which is order Date. I have to make sure that I do not make any typing errors because otherwise Excel will not recognize my filter criteria. So I type order dates and I entered my second criteria, which is store, and I entered my turkey criteria, which is all right, Um and I had a nicely out through my filter criteria range. I also add a yellow backgrounds as I want. They feel the criteria to be easily recognizable. I suppose that I now enter my first criteria. Order dates to be the first off August 2016. Enter my store criteria, which is store A and that also wants Toby. So Inter story it's would be and through my item, which is a kitchen table. And suppose that a novel to oblige us filter criteria to my list range. Then I can go to data the last filter. I specify my list range. I specify my criteria range, which is this filter criteria range. Here I click. OK, Ideally, you could also copy the criteria to a separate sheet, and I can now use my filter criteria to feel to my list range. 23. Data Validation and Remove Duplicate Values: Excel opportunity possibility to restrict users from entering certain data into a cell. This might be useful if you have a cell that, for example, only can contain the miracle characters, and you do not want users to add off a battle characters. You can also assist users in entering a certain cell input by showing a input message. Whenever you two clicks on a certain cell, you can customize this input message in the data validation options within Excel. You can also add error messages in case of the user, enters an invalid inputs and blocked the user from entering invalid data into yourself. Another frequently used data to wouldn't excel is he removed public, its data to, which allows you to remove duplicates or double values within a certain range. If I not go back to my sales order sheet, and I want to add a restriction to block users from entering alphabetic characters within the miracle fields such as order number, order, date quantity, unit price, sales amount, discount itude, price and discount CIA's amount. Then I go to the C s order sheet and select my columns. I go to data validation. Some things allow decimal and I enter a minimum and maximum Suppose And now on my order, number and quantity to have a old number. Then I select those two columns. Go to data validation. Nice. Select. Oh, number I also had an error message. Did a stop sign the title Wrong input formats, Please enter a whole number. And I was like my order date. Go to data validation. Here is select settings date Enter my start date, which is 010127 16 For example, my hands from my end dates Which could be for example, 31 12 2017 I entered a error message. Wrong inputs performance. Please enter a date for months. Okay for store I do not want users to add a text longer than 10 characters. So I allowed text lines between zero and 10 characters. I suppose that I want to display a custom input message whenever the user enters a store. Then I can go to input message and to my title store Actually input message Please specify the store name. I also displayed error message and I want the warning sign instead of a stop sign This time my title will be store name too long. My message will be. Please enter a story that is less they 10 characters. Well, okay. And I want to enter a alphabetic character, for example, in my order, number or date column that I get the error message wrong in performance. Please enter a whole number. If I do the same in my order date column, then I get the error message. Wrong input format. Police enter 88 for months. Suppose that I click on one of the elements in the call of store that I have a message saying Please. Beth fired a stormy Suppose I enter a text longer and think characters. Then I get a warning message. Police enter a storming that it's less than think character small Continue. So here I have the option to continue yes or low, and I select no. So if you want to allow your user still to be able to proceed after entering a invalid input, then you can use a warning message. If you want to block user from entering an invalid input, then you can use the block message. Finally, suppose that I want to remove orders with a double IQ its order tag. Then I go to remove duplicates. Here, I can select my column order tank. So I first un select all. Then I select my calm order tank Click. Ok, yes. My data is Heather's. Okay? No duplicate values found So I don't have any public its order text, which is normal. I click. OK. And as you can see, you can use this functionality to clean your data from duplicate values. 24. Pivot Tables: Apart from using regular tables and excel, you can also use five. It's tables. Five tables offer many benefits compared to normal tables because they're more dynamic, easy to create and also offer easier customization. Two. Created by the table. You just have to select a range of cells or a regular table, then go to insert five table and choose the fields you want to add to your table so you can start from a regular table and then choose the future You want to add to your private table . Afterwards, you have to drag and drop the selected fields into one of the areas below. As you can see your four areas, the first area report fielder allows you to add a filter to report. You can also choose the columns, which you want to add on the roads within column labels and roll labels, and you can also select values which corresponds to the fields of your table. You can also customize the calculation type here, so in this case it would calculate the sum of the sales amount. You can also choose to have count or average or maximum of a certain dimension. If I now go back to my sales order sheet in Excel, and I want to create a private table based on the sales order. Stable any sales order sheet. Then I can select my seals or table. Just click on any cell in the table. I go to insert five table. It's like five table as you can see my table, it's already selected. I choose to create a five table, which is placed on a new worksheet, I think. OK, now I choose the fields I want to add to my private table. Suppose I want to add order dates, store item and CEOs amount, and I want to be able to filter on item. I want to have two columns, which is Store A is Toby. So I drag and drop store to the area. Calm labels and I want to have the total sales amount for each order date. So my role labels correspond to the dimension Order date and my values correspond to the some of the sales amount. Suppose I want to change some to a different setting. Just click on the arrow sign next to my summer values and I select value field settings. And here I can choose the calculation type so I can choose from some count, average maximum minimum and saw in this case, I do not want to change calculation type. However, I do want to change the number for Mitt. So I could come number for months and go to currency where I select two decimals and I want to have you in front of my number. So I select symbol you are night first. Okay. Okay. When my bible tables complete, as you can see, Aiken, filter on item here. I can select, for example bet which gives me to see his mom 30 item category. Bet I can also easily modify my Bible table. Suppose that I want to have the role able ordered attack instead of order date. Then I just dragon drop wonder dates back to the selection of fields. You see, this is now removed and I dragon drop order tack into bureau labels area. Suppose I want to be able to filter on payment methods instead of item category. Then I just drag and drop item back to the selection fields and I dragon drop payment methods into the report filter area. Suppose I am on both the sales amount on discount sales amount. Then I simply drag and drop. Discounted seals amount into the values area. And as you can see, I now have two columns for each store column. So my top columns are These tour comes and the need that I have the steals amount Discount Sears Amount column He also noticed that the Values label is at its to the column. Labels area. Suppose that I want to have my some of Seal's amount and some of discounts. Here's amount. Columns, bop and need that I want to have recep category. Store a store. Be then I can simply drag and drop the values label on top of the store label. And as you see now, these some of CEOs amount on discount sales amount are at it on top of the store called labels. Suppose I want to group my orders or or attack role labels for each order date that I can add. Sethi. A field order dates on top off the field order tag within my role label area. And as you see now, I have the summer off sales amount, and this candidacies amount for each order date in order. Tack and I can now simply either show details each order date by simply clicking on the icon next to the order date in case I didn't want to see the order tax. 25. Pivot Charts: similar to five tables. You can also use five charts in Excel. Biba charts are always based on a private table. In case you select a normal table or range of cells, and you create a five. It's chart than example. By default, create a five table for you so by the charts, cannot exist without five tables similar to pipe tables. Pirate sharks also offer many benefits compared to regular charts. They are more flexible, more dynamic and easy to customize. You can simply drag and drop the fields within your five table to a new area and your five A chart will be date. If I now go back to my sales order sheet in Excel and I go to the sheet where I created my pipe table and I want to insert a private chart, then I simply select any cell in my private table. I goto pirate table tools, options by charts, and I select my chart type. In this case, I would want a clustered column chart, so it's like Clustered column. I click OK, and as you see my Bible charts dynamically created, I can now customize my private chart. Would I want to customize my vertical axis values than I. Right. Click my vertical access for mitt access Go to number. That's like category currency. I want to have two decimals and the letters You are in front of my numbers. So I type you are and select my currency in the drop down list. And I close this window. Suppose I would know. Add a filter. I think category bet to my chart. Then you see that my five table is also a dynamically updated. So each time I update my Bible table, my pirate chart would also be updated. And each time I update my private chart, my private table will also be update. So suppose that I would update my some of sales amount and change this to average CEOs amount that I will also get the average sales amount in my chart. Suppose I know 185 table and five chart that give me some of the sales amount for each store that I simply insert a new Bible table, go to insert by a table, people, I select my range, which is these sales orders sheet. I select the entire table here. Okay, I choose my destination which is this sheet I click. OK, I select my fields, which I want to add to my report. So I dragon drop sales amount to the values area, and I drag and drop store 20 area. I now want to have a Bible chart so I won't eat by chart. That gives me the sum of CS for each store. So I go to options in the Bible table to step on. Go to five chart. I select a bar chart. Click OK on my bar chart is dynamic recreated based on my table. I can choose to change the title to seals by store. Euro and I can add percentages, and they can add percentages to my pie charts by choosing one of the chart layouts. Okay, my sales report is now finished, and if you want, you can also make some small adjustments to improve your dashboard.