Microsoft Excel Basics for Beginners - Learn Excel Fundamental Skills for Business and Work Smarter | Luis Carlos | Skillshare

Microsoft Excel Basics for Beginners - Learn Excel Fundamental Skills for Business and Work Smarter

Luis Carlos, Engineer, Web Developer and Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
28 Lessons (2h 1m)
    • 1. Trailer

      1:30
    • 2. Introduction to Microsoft Excel - What you will learn

      1:56
    • 3. Excel interface overview

      4:25
    • 4. Excel ribbon - How works and how to customize

      3:50
    • 5. How use and edit Quick access toolbar

      1:38
    • 6. Excel Workbooks - Create, Open and Save files

      5:18
    • 7. Working with Worksheets - Add, delete and change name

      2:44
    • 8. Automatic data with autofill - save time using this tool

      5:25
    • 9. Rows and columns - Add and delete

      4:07
    • 10. Hide & Unhide Rows/Colums and Worksheets

      4:20
    • 11. Change row and column size

      3:29
    • 12. Wrap, shrink and merge text

      4:05
    • 13. Practical exercise using Font tools

      3:27
    • 14. Format painter tool - Another powerfull tool to save time

      2:46
    • 15. Format Numbers Tool - Date and Time Format

      5:00
    • 16. Identify and Remove Duplicates Values

      4:10
    • 17. Sort and Filter Tools

      14:28
    • 18. Cell references to link data - Relative, absolute and mixed

      8:09
    • 19. Excel Formulas - How to create a formula using math operators

      4:43
    • 20. Use Functions in Excel - Syntax used to create functions

      4:07
    • 21. Add Numbers with Sum Function

      2:34
    • 22. Average Function for cells ranges average calculation

      1:35
    • 23. How to use Statistical Functions Min and Max

      2:00
    • 24. Count and CountIf Functions

      5:31
    • 25. Exercise with Logical Functions IF, OR and AND for single and multiple conditions

      7:10
    • 26. Conditional Addition with Sumif and Sumifs

      3:54
    • 27. Extract Data using Vlookup Reference Function

      6:08
    • 28. Hlookup Function to Extract Data

      2:43

About This Class

8f74216c

Hello, my name is Luis Carlos and I will be the instructor in this class of Microsoft Excel.

This class is made for beginners, being divided into different sections.

In the first part you will learn the interface excel and how it is divided. The ribbon customization and quick access toolbar will be another important point that allows to adapt the working environment to the needs of the user.

In a second part we will learn how to create and open excel files, add, delete and change the name of worksheets and in a third part of the course we learn to insert data manually and automatically and work with cell and text formatting tools.

The last part will be dedicated to formulas and functions that will be an important step to work the data automatically in the worksheet.

To follow all the lessons, is available a file exercises.xlsx in the  project section that can be used by the students to make all exercises made in the videos.

This class is compatible with the latest versions of Microsoft Excel (2007 / 2010 / 2013 / 2016)

List of main topics:

  • Excel interface
  • Customizing the Excel Quick Access Toolbar
  • Understanding the Structure of an Excel Workbook(anatomy)
  • Customize ribbon and quick access toolbar
  • Create, Save and open an Excel Document
  • Add, rename, delete worksheet
  • Enter text manually and automatically with autofill tool
  • Format data
  • Wrap, shrink and merge text
  • Format painter tool
  • Format numbers - Change format to date and time 
  • Cell references - relative, absolute and mixed
  • Create formulas using logical operators
  • Functions  syntax - Learn and apply the main used functions

Transcripts

1. Trailer: Hi, My name is Scott English, and welcome to the Microsoft Excel last four years. Excel is one of the top tools that helps to analyze and managed it that far personal are professional projects, and together we will learn and use some of the best tools provided by Excel. In the first part of this glass. Let's watch different sections off, excel in their face and understand what are the main competence, such as a world who work, ship and sail? Can I drive you to some amazing features that will help you to format and manage your diet formulas? Infections are another important continent. Get help you uneasy white to create automatic and efficient processes in your data sets each lesson s practical exercises dead can be used in real world using son off the most important tools and functions used by excel. At the end, you are able to use excel like a professional. Enroll now and work smarter. Read Excel 2. Introduction to Microsoft Excel - What you will learn: Microsoft Excel has become one of the most widely used business stools that allows to manage and analyze a data set in an easy and efficient way. Whether you are using it for business or to help manage personal data, Excel gives you the right tools to enable you to accomplish all yard needs. In this class, you will learn the fundamentals of Excel that LP to create, edit and manage your data into spreadsheets. You will work with men, components and tools, understand our Excel interface instructor it and out to customize the different sections to optimize your working area as well. Some basic procedures, such as create safe files at and remove spreadsheets in your Excel workbook. The use of formulas and functions is another important section, allowing users to perform many operations and tasks in a fester and automatic way. You will learn the differences between formulas and functions, syntax used and out to use them together. All essence ever guided exercises to test the knowledge acquired in the different parts off the glass. In the next video, let's learn and understand demain sections of Excel interface as well. Some important tools available in the working area 3. Excel interface overview : When you open a filing, excel, you watch and see a more or less complex interface where you can distinguish different sections inside the application. This is the first step in this car's understand Excel interface. To take advantage off application capabilities, you can find two main areas in Excel, one at the top, where you have menus with multiple options and agreed area below where you enter the data. Let's make first an overview and see some important details about the area where user inserted ITER and some of the important tools available. First concept learn is the worksheet that is this mine work area in Excel, where you keep in manipulate the data. They made up off cells displayed in a great system, and each cell is the intersection off a row represented by a number and call them represented with the latter. Each Excel file that we call workbook can have an unlimited number of worksheets. Death appears as steps at the bottom often excel window like you see now in the image where I have to work sheets with respective names at the bottom on the right side of worksheets, Um, or resentful and vertical scroll bars that are useful to navigate in the worksheets. When you select one or more cells in a worksheet, do result his reference as a selection, and you can see the reference off cell that is selected in the name box that is, on the left side, parts that can be used to wheat functions to assign names to cells for disguise. I select the sale, be five and as you can see the name box, tell us the reference off the selected cell. If I select lord than one cell. The first cell that I select to take the multiple selection is the sale that appears in the name box at the right sides off the name books you EV formula bar that can be used toe enter and edit sell information in the upper left off the Excel window. You will find the Greek Assess toolbar, which have the commands most useful that you can use at the bottom of the workbook window. You have the status of our that show was useful information about the current selection on the worksheet and, like do quick assess toolbar can be customized at the right. You have the view bottoms that allow you to free each between normal view page layout of you, our page break purview and to finish at the right side off the view buttons, um, zoom tool slider, which allows you to zoom in and out worksheets area in the next video, Let's see the section at the top cold ribbon that as multiple tabs and commands to work in exile Thanks. 4. Excel ribbon - How works and how to customize: after knowing area where user insert and edit data and some useful tools. Let's see the section at the top called Ribbon that contains all commands and man using Excel being divided into tabs for easy navigation in the commands that you want to use like is well now from the left to the right, you F home insert by age layout formulas, data review view and our people, where each step contains the group off related commands. Being useful to quickly find the command that user want to execute. Watching now on Ted example. You F seven different command groups clip art funds, alignment number styles, cells and editing in addition to the command Cinder. Even Excel makes many of these commands available with right click menus that you will see in another lessons to finish the ribbons. Let's see some customization that we can do with them when option that you ever is minimized or even if you want deception, do not see the commands just need to right click on re even area and F to choose collapsed there even to see again the ribbon and respective commands just needs to in check. Other great option is select the main commands that you need to use in order to customize excel ribbons, use right click under even area and choose customized there. Even in the options list, you can click on the new tab, rename and select the least of commands that you want to include in this new tap from the least on the left side. That's what I do know. I have a new tab with the name step test, and for this new tape, I had two commands front in front size that are in front group off the home tab after this election. Just need to click OK to accept the changes. And you see that now we have this new tab available under even. Another option is to customize the existing tabs and groups always being able to ever are removed. Groups off even taps. I now go to do alignment, group off the own tab and remove this group that is no longer available in the own tab. After that, I do do reverse procedure by adding this group again, being very simple. Ribbons are now finished, so let's see in the next video out to customize quick assess toolbar in order to optimize our work area. Things 5. How use and edit Quick access toolbar: other important tool that you can customize is the quick assess toolbar that is a universal toolbar with main commands. This full bar can be used, as the name says, to assess quickly to some of the most used tools to customize the quick assess toolbar. Right. Click on any parts off there even, and you see the option customize. Quick, assess full bar into dialogue box. You can select the commands that you want toe ever or remove from the quickest Estelle Bar being very similar, like you did with ribbon customization. Another option to go figure is to excess. The quicker says still, bar on the left side, where you have this bar and click on the right side off the bar to see the options that are active. Being able at this moment to enable are desirable, Maher commands. In this example, I disable safe, tool and then enable again. All active commands are available at left site. As you see now, we're are available for different commands. Thanks for watching 6. Excel Workbooks - Create, Open and Save files: I Let's see now out to create, open and save Excel files. Excel files our Cold World books. When you start a new project in Excel, you will need to create a new workbook. There are several ways to start working with Excel files. You can simply create a new workbook starting from a blank document choosing that file oxeant New and Click Blank workbook. The other option is select at Imply It that our models prettified that you can use being available different types off online templates. Situation that you see now in case often existing file that you worked previously. Just need to open a workbook that you saved in your computer using the temp file an option open. Besides your computer, you have the option to open a workbook using your one drive, which is an online file storage service included with your Microsoft account. Finally, if you have open it a workbook recently, you can always rouse your recent workbooks option instead, off search for the file. Another great option that you have, if you usually work with the same workbook, is to pin it, do a faster access to Pena workbook G to choose open and you're recently workbooks will appear best with mouse over the workbook that you want to pin and click on the Bean. Aiken, making that workbook stays at the top lists being more easy to access files you see now in the image when I make open and I have a Siri's off files with a pin to get them quickly. Other important action in Excel is to save a workbook in order to access and edit later when option is safe. Files locally to your computer using two ways to save a file. This safe option where you Onley need to choose a file name and location just the first time after death, you Onley need to click the safe command to save the file. That's what I do now. Create a New Excel file, and I choose the safe option, selecting the folder where I want to save the file and giving a name to the workbook. After the finally saved. When you select the save option, it no longer asks for the file name. The other option is safe as discover Meant is used to create a copy off a workbook while keeping the artisanal using another name our location. We'd save as you always have to give a file name and choose file location using previous file. If I select now, save S u f to choose the name and location to the file with this Excel. And like older versions, you can save a workbook to the cloud using one drive like I told you to open files. Apart from these choices, a good option to be enable is the outer recover, which is a good method for not having data loss problems in the file tab U F Options menu. We'd save Tep that has the option to save other recover information periodically that you can configure for five minutes and in which I have enabled the option. Keep the last other side version. If I close without saving that allow users to ever access to a recent backup file. That's the end of this lesson. Thanks for watching 7. Working with Worksheets - Add, delete and change name: I welcome to this new view where I talk about excel worksheets. When you creates a new document in Excel, your workbook appears with a single worksheet called shit one. Sometimes you need more worksheets to add your data being a very easy process to wear them at the bottom. Off the document, you have a tap that represents your work shits with respective name to wear the new sheet clear condom pulls button on the right side of the tep, and you will insert and you worksheet at the right off the current sheet. That's what happens now. They're tired. Then you work shit called sheet, too, to the little work shit. The quickest method is to right click on the worksheet tap that you want to the elite and select delete option. This type of procedure using right click over the tab also allows Add Inc and you works it as we did before. But this time, by selecting the insert option like I'm going to do now, the next step is to rename the worksheet that is very simple to execute. You just need to double click on the worksheet. You want to change the name and right. Then you work your name like I do now, giving the name new underscored shit. Although performing these actions in a certain way, there are sometimes other methods to perform these procedures which are sometimes also available by using driven taps like insert and you work. Shoot. You just need to go into the home tab sales group and choosing command Insert shit to finish the final detail. You can always change the order off the worksheets by clicking on the worksheet tab and dragging to the position that you want. Like I'm doing now where I put the work shit on the left to the final position at the right . Thanks for watching. 8. Automatic data with autofill - save time using this tool: I. Let's learn now out to put data in a workbook manually and automatically using out of fuel tool starting with manual mold toe winter data. You just need to select a cell and begin typing data. The text appears in the sale and in the formula bar. If you press enter, the data is accepted and cursor will move down one cell. When you want to delete information that has already been interest, just select the sales and pressed the delete key. You can always navigating a workbook to choose any cell using mouth or the arrow keys from the keyboard. Now let's see a great tool, which will save your time to enter data. The Excel Auto Feel feature is a smart methods that can be used to feel a range of cells with either a repeat value, a series of values or just a cell format saving a lot of time making. Now a simple example. To understand, I enter value into the start cell a one we value, too, and used the mouse to drag the feel andl represented by a small square across the range. Off sales to be field Excel feel the selected sales when you dragged the field andl across a range off cells for this guy's repeat value to data. Insert in start cell corresponding to copy sells option debt. Copy the initial sell across the selective range you can choose. Do I Excel Field the selected cells being possible. Repeating the value in the first sale like ideas are using out a few options to F A sequence for the first cell value. Selecting feel Siri's option. That is the second option. Another way to use the out off you is to select two values. Helping Excel to recognize a pattern that is not a simple increment by one. This can be done by typing the 1st 2 values off your Siri's into 1st 2 cells of a range. Now, instead, off only select first cell, Select boat off the cells and track the field lentil across the range to be filled. That's what I have now. First Selby one With number five ending the 2nd 10 Dragging Excel real automatically Recognize the pattern from the two initial cells and continue these across the selected range using option feel. Siri's Excel put multiples off five in each cell. Five 10 15 20 and so on. If you simply select copy sells Excel copied the two members with same order to the selected range. Like you can see now. Besides the numbers, we can use this tool with dates by default. If you just type a single light and dragged feel anvil that's real complete in a Siri's by adding one day to the date as it happens now, where I Yet the date. 11 2018. Adding one day for each cell, corresponding to the option. Feel Siri's or feel dies in the alto. Feel options. If we want to change months or years instead of life, you have to truth the option. Feel months or a few years. If you only want to include the weekdays. Better did not include Saturdays and Sundays. You have another option to set the date. With these exclusions, you have to choose the fuel weekdays options and you see a jumped from the five to die. Eight. Debt corresponds from a Friday to a Monday, excluding weekend. Thanks 9. Rows and columns - Add and delete: when you want working in Excel, there are times when you have to wear new data as well Other moments that you need to delete unnecessary data. In this lesson. Let's learn out to add and remove rows and columns to inserts and delete data to remove a row. It's very simple. In this example. I want to remove data from Row five, so you just need to select the number off the light to be removed. Access the own tab group sales and select delete rose. Other option and more faster is to have the line also selectors right click button and select the elite option to remove a call. The procedure will be similar, but instead off having the line selected, I have to select the column letter, then just access the option. Delete calm through the ribbon or right click and select Delete to the elite. Edge isn't rose or column. Click and drag with the mouse pointer toe. I light all the rows and columns that you want to delete. Then the procedure is the same by selecting the delete row or column option. Finally to delete Non edge isn't rose are columns at the same time you have to select the first and then with the control key all down. Select D, Others none. Edge isn't rules are columns, and after that, just choose option. Delete using right click button or or even commands in the own tap. Now to add rules and columns in your Excel file to add a single roll toe, a worksheet click on the row. Other debt. Select entire rule and just need to right Click on the selected role toe. Open the context menu, where you choose inserts and you see deaths and euro should be added above the selected role. Other option is to use the own tip and select insert sheet. Roath. A different scenario is to add multiple adolescent rules. You have to tell Excel all many new roles you want to add to the work shit by selecting the same number off the existing rules. For example, you need to add three new rose to a worksheet. Inro. Ever click and drag with the mouse pointer to highlight three rows where you want the new rose ev. It then just tried. Click on the selected rose and shoes inserts from the menu and the tree new roles should be added above the selected Roath to the columns. You have to do the same. Click in a column other to select the entire cold, then right, click and select insert and then you call will be inserted on the left side, selecting to arm arkels and selecting insert column. Make that two or Mark Combs will be added to the work. Shit. Thanks for watching. 10. Hide & Unhide Rows/Colums and Worksheets: Now let's see a scenario where you are working with the big amount of the item. Sometimes it's useful for the situation. Two eyes are an ID, rows and columns. To be clear, the data you need to analyze Let's now see these future available in Excel debt allows users to IEDs data. The first step to do this procedure is select the rows or columns you want to. I'd you can simply select one row or column are multiple columns are rows that are side by side, using shift key and none adjacent rows and columns using control key. In this example, where I you have a table with multiple rows and columns. I want to columns to ID B nd. So I select these two columns, and after that I go to cells group in size on tap and click format, selecting ID and an ID where you have option either rules or I'd columns for this case, I I'd to call him, So I click option to I'd columns other methods that you can use his right click after selecting the columns or rows and then select option. The columns are now eaten and you see a team double line death. Tell us where are even column now to make the reverse operation. There are a few ways to select which rules our columns. You want to an hive. You can simply right click titine double line indicating an even roar column and click an either option. If you simply want to an ID all rows and columns in a single step. Select all cells using the keyboard shortcut. Control I Our cleat could be low named box like I do now in the home tip under the Sale Group Week four months, ID and and I've and I'd columns option if you want to show all the columns or an ID rules to show again all the rose besides being possible, ICT and I'd rows and columns users can also I'd worksheet taps being very useful when we have a workbook with many tabs where some of them are not necessary to the current work. To apply this option, we only need to have the tab that we want Toe I'd active and in the cells Roop off step home. Select formats ID and I've I've sheet option. Other way is right. Click over the tap and select. All right to perform the reverse operation, select the an ID sheet option in the same tool group choosing Then the correct shit. Another option is used, right Click again on the existing tap and select and I've thanks. 11. Change row and column size: I welcome again after learning to ads and delete rows and columns, let's see did available tools that we have in excel to change I've and with in rows and columns to change the eyes of a rule are the width of a column. I used the same selection method used to add and remove rows and columns for the case off rose after select Rose that I want to change the IIF. I use right click button and just have to select the role either option and right value debt. I want for the role I. Another possible option is to use the own tap into ribbon and inside Cell Group I select the format option and select Row I further columns. The procedure is similar by selecting the columns and choosing the comb with option by clicking with the right mouse button or by selecting this option truth step home riven, just as I did with Rose. But now selecting column with now three tips. If you put mouse cursor between the other off the two columns, click and drag mouse to left or right. I can change the with off the left column manually. Being a quick and simple white with Just with this change can also be made on the rose by placing the mouse between two rows. Other andric mouths a pin down changing I've off the role of of. You can also check that if you make a double click between two column others. If any cell on the left column, as some content do with off the cone, will be adjusted to the content of the cell. And now to finish. If I want to change the AIF toe all rows, are we to all the columns? I can use the fast methods where I first select all cells by clicking on existing spice on the left side off Coleman A and above the first row. And now I just need to go to the option format available on the rebound and change. For example, the rules value to 20 and calm, with 2 15 being visible. Now the change is applied to all rose and columns. That's all for now. See you next lesson 12. Wrap, shrink and merge text: Let's watch now some changes that we can make in text using some useful pools. Sometimes you have data in yourselves that is too large to fit inside, and two different situations can happen first, if column to the right is empty, a long text string extends over the cell. Barter into the column. And second, if the Edison to call them contains data. A text string is cut off at the cell border situation that you see now in this table that I f in the workbook. One off. The possible solutions is to change the with off the column like you learned in another lesson. But it will not always be the best solution, because if you ever very long text in a cell, the column will get a huge wreath. To solve this situation, you can use rep tool that display all the text in one cell without overflowing, the cell content is displayed in multiple lines instead of using a single line. It's that situation that you can watch in this example where I f text that is cut in the sale be six. With this cell selected, I goto own tap and in the alignment Group I select the Rep text option, which makes that the content of the sale is divided into lines to not overflow. Another way to access this option is to use right click button, select for a month cells to have alignment and then option. Rep. Text. There is another simple method that is used to put the cell contento visible inside the cell in Steph. Off placing in multiple lines. Excel shrink the content using the shrink to fit option that is available by clicking on the right button off the mouth. We'd sell selected option four months. Cells tip alignment and select shrink two feet. Option. Now the youth off merch cell tool that is useful if you want to make that a label or title in Excel is merge in multiple columns. That's the example that I show in the World Book, where I you have the table with the title at the top occupying Onley Cell a one. I want this title inserted within the group off sales a one to C one for these, I select the sales from I want to see one select step own group alignments and finally Marge and center option merging the selected cells. Other option. Like the Rep text tool is using right click select format sales tip alignment and then murdered Cell option thanks. 13. Practical exercise using Font tools : Let's look now to fund her of commands display. It's in there even to format. Text. Excel as this group of commands inserted in the arm tap, which allow hours to do a lot of changes, like the text font in this drop down box and respective fronts size at the right, where you have two different options to change the size. Using drop down box are these two buttons that increase and decrease fun size to create different font styles? U f the bolt italic and underlying option. Other commands available are Fun caller to Change text caller and the sale Big ground color to finish the border options that let you wear borders in a sale or a group off cells, depending the type off border that you choose you can define. For example, Onley outside the borders are on Lee. One side's like in this example that I define a border at the top off the selected cells To better understand the stools. I have a table with some data for which I will make some changes. The table as a title at the top and two columns. We'd some data at the right I have in the table. The changes to make. Starting with changes. I have the title in Selby one and I want title merged between cells B one and C one. So I have to select this range and select merge sales and center option. The next step is modified. Text front to verdana fun size 2 30 and different style to bowl. The last change in the main title is apply Color green to the background color. No, let's look to the table data first I want text with color blue option that I select now the text Fontes, Calibri and 11 is the value for the form size. The final change in the table is applied border inside and outside the table, selecting the option. All borders thanks. 14. Format painter tool - Another powerfull tool to save time: now a great tool that will help you to format your sales quickly. The format painter, This tool simply copies from acting from one place and applies it to another, helping you to speed things up, being a perfect tool when we have a very extensive that the set that needs to be formatted . I use now the table that I changed previously, and I select the title at the top and choose the format painter, too, to copy the type of formatting applied when I click the title of them for the matter table . Excel applied the same format settings on that cell, being now the to table titles. Similar even faster is if I select the old table and then click on the format painter option, then select them for my table. That changes completely to the new formatting immediately. But there is an important tell toe. Understand. This new table is similar to the previous because the data is arranged in the same way, with same number off rows and columns. It's a new table toe format. If another rule, you watch that the formatting off this last line would not respond to what you want because the table where the format is copied s eight rows, we data and the new table s one more rule applying to this new line the beginning off the formatting off the table. Conclusion If we apply the formatting to several lines below, the formatting off the table will be repeated as you can see a trick that can be used to work around the situation. If our table as more rows in the original, is copy formatting that I want from one single line and applied this copy to the new Rose edit in the table situation that happens now where I call before marching from a single rule and apply to the new rules. 15. Format Numbers Tool - Date and Time Format : Microsoft Excel allows different types of formats in numbers inserted in cells such as date or time, Excel, stars, date and time values as serial numbers. And it's only using self formats that forces the number to be displayed with that or time formats. You watch this situation now where I have in sale a one did ites one one 1980. Debt corresponds to serial number 292 to 1 difference is that in Selby won. I have the original white. Did I t start being converted to a date format in sailing? One. Let's look now you can convert number two these different formats when you ever number, defined and selected. If you click right mouse button, you have access to the format numbers. Option where you see a Siri's off taps detail number allows to display sales with a different type of format. You see that I have in options date and time, just like I said before. If I change the category off the selected November today too, the result change from number to date in preview being equal to the site in cell a one. You can also make these changes to the number format, using the group off commands number inserted in the tap home by clicking the bottom right I can. That gives you the access to the same options this introduction was. Just get that, I d. That although you are working with date and time format Excel, see this information as numbers, but in Excel, it will always be faster to write your data in the format that you want. It's obvious debt to write a right you will not put a number. That's what happens now where I write a new dating Sally too. Day two month, two end year 2017. And when I access the format cells, you see that that format has already be assumed and can be changed to a different type off date format visible on the right side of the window. So now I select another type of format to represent diets with the minus symbol to separate day, months and year, and I want the month with texts, abbreviated instead off a number If I want a site like the original value using minus symbol but month without text, you need to select custom option that recognizes date format, and you just need now to put minus symbol between day, month and year. And you get now did ites with this new format. Now, in another cell, I insert the time 13 hours and 20 minutes. And when I go to format sales, it's visible there to sell this formatted s custom. So I changed the category two time with the type am or PM. The time is now with the formats that I want. As you see, there are available different formats to date time and for other categories, which brings a great diversity off options to present your Excel data. Thanks. 16. Identify and Remove Duplicates Values: from time to time when we have many rules with data in a work shit, you may need to identify duplicate Rose in a data set in Excel Excel Latest versions provide this capability. We just a few mouse clicks. Two wild lights duplicate data using conditional formatting being an easy way to identify, duplicate rose and decides if you want to remove them. To understand better this feature, let's make an example using a table with multiple rows where I you have some duplicate records. To make this checking for this table, you must select the cell range that you want to analyze. Then, in the tap home used the tool conditional formatting where you have the option. I light cell rules for duplicate values. In the box. Deaths appears next to values wheat. You choose the formatting. You want to apply to the placate values where I select light red feel option and then click . OK, you see that's using this tool. We get two rows were all data is similar, and you have another rose with just some columns with equal lighter like in rows three and six, where Onley age is similar. This is a great methods to analyzing. I light your duplicate data, but it's possible to use another tool that remove repeated data being possible to select the columns that Excel analyzed. If data is duplicated, notes that using this feature the duplicate data will be permanently deleted. So to remove duplicate rose first, you select again all the range of cells. Next, I select tip data and in Data Tools group I have the option removed applique kites. Then appears the window where you can select the columns where we want to see if data is equal. This election is important because if you select on Lee, the First column, whenever unequal record that appears in this column will be considered as a duplicate and will be deleted even if the next column already contains a different value. It's what you see now in the stable where I F three people named Louise, but on Lee do completed Rose F similar data road to and rule. For If I choose only the name, call him to analyze the kite. The last two rows we'd name Luis will be deleted in disguise, role for and seven remaining on Lee Road to with name Louise on the other end. If you select all columns for duplicate records will be deleted on Lee. One of them, because in disguise you Onley f two roles were all data. Is eople corresponding to rose too? And far 40 situation row for is elitist thanks. 17. Sort and Filter Tools: Sometimes when you have a spreadsheet with a big amount of data, it might be necessary to start the same data alphabetically based on the names we dina column or numbers from small to large, depending the type of data sorting that you want to get with Excel Starting to This is a very simple desk. However, there is much more power in how you can sort N'Dri order Did ITER tables that you use in Excel? Let's learn now out to use this tool in Excel sorting data is very simple. Using just two click process to reorganize the data in your spreadsheet in My Excel workbook, I have a table with more than 400 rose with the information about employees off a particular company starting within his example, I want to organize information by the names Debt I F. In first column debt. Tell me employees first name to make that change. I just need to click in one selloff. That column, now in their even select on tap and in the editing tool, select the sort and filter button that is on the right size off there. Even in the 1st 2 options you ever thought A to Z and Z two I. This will start the Excel data either direction, depending on the option that you want in disguise. I want names from Z to A so I select second option. And after that, rules will be arranged. If you now want to start by numbers from smallest to largest in disguise Edge. I select a cell from the Edge column in cleat, the smallest to largest option Resuming to make a simple start, you have to click inside the column off data that you want to start shoes sort, option and excel. Well, reorder all the data based on the criteria that you truth for. That column So far, we have seen how to perform. Simple are drink using just one single column. But we have the option to start BIST on two or more columns to use this advanced start. Just need first to select one cell inside of your data and choose the sort and filter option and then choose custom sort 40 situation. I select custom sort. And before I choose to call him to start, I enabled the option. My data s other to excel. No debt. First data row represents table. Other now using drop down option next to sort by. I have aged selected because was the selection that I made previously using simple starting . But if you want to choose by another column, just need to change the selection to the column that you want to start by. For my example, I keep age and art it from smallest to largest to where the second sort level I click on add level at left side, creating a new row in the sort options that allows the new start level choosing first name and sort from A to Z be seen out debt. The table is started by age, and when I have equal ages, I saw by first name from A to Z other great tool that we can using Excel is the filter, which allows in a quick way to display only the information debt user wants, removing all other data from view. The main difference between filter and sort options is that using sort option, the entire table is rearranged. En does not I'd any entries just puts the data into a new order. What not happens with filters. Distal also provides a Siri's off sort options such as alphabetical ordering and number ordering as previously seen. So how can I have a filter in excel to wear than automatic filter? First, you need to have your data with the other row where you were defined column names off the table like I f in the same table that I used for sir to with a large data table. After selecting one cell of the table you can use to taps home where you have sort and feel that option indicted, tip in the ribbon where you can select, sort and filter tools using the filter button I used for this case data tab to be different from the previous exercise. Using filter option. The drop down arrows appears in each off the other cells in the first rule. Now, with filters that that the next step is out to apply them in Row one. In each column you see a drop down arrow. That means that filtering is added but not applied yet you can click in the arrow for the calm that you want to apply the filter. For my example, I apply filter, insert, calm edge and is visible that by default all ages are selected that is caused by selectable option at the top that is selected and checking death box. You dis elect a later from the table, and now you can check individually each box to select the data debt you want to see. For these example, I select ages from 55 to 60 death I see now, after applying the fielder, it's visible. That's now to drop down Arrow Aiken in the field Road column changes to a filter I can telling as that the filter is now applied in this situation. I applied only one filter to a column, but it's possible to have situations where you were. Multiple filters applied, and that's what I'm going to do now. To apply Excel filter to multiple columns just repeated the same step that I used for single calm. But now, for multiple columns in this next example, I apply to filters. I want to display people from city Bristol with edge between 20 and 30. To display these filtered data, I go first to age goal, where I select ages from 20 to 30 and then I go to calm city and select the option. Bristol now using other filtering options that can help you filter specific data types, such as text numbers and dates. When you want to apply advanced text filters, you ever Siri's off options available. If you click on the drop down arrow in a column with text data, you see option text filters where you apply some advanced options like cells. Death begin with it are, and we'd a specific character. Our sales death contain our do not contain a giving character or word. I now present an example where I want to see the cities that begin, we'd be to do this. I go to text fielder option and select begins with a window bumps appear where I place this condition in the text field, placed to the right size when clicking Okay, Onley appears the data in which the respective cities begin with letter B. But of course, you can apply more than one criteria, which can be used with the end our our operators. For this new case, I want to add to the previous example the cities that start with the letter D. I will then use the second text field. Where I add the criteria begins with the Now that I click. OK, I get the rose in which the city start by B RD. Besides the text fielders, there are the number filters that are used to create filters applied to numbers. Excel have many options for number filters like filter numbers equal are not equal to a specific number greater than less than or between the specific numbers and fielder Top 10 art bottom 10 numbers to apply these filters. The method is very similar. In one of the previous examples, I selected the ages between 20 and 30 years, a filter death. I can now also apply using this option for these, I go to the age column and select the option number filters where I have a lot off right iria options that can be applied where I select writer Dan or equal to option that can be changed according to the criteria that user wants to apply. And below I use option last, then are equal to. So if I want the I. Just between 20 and 30 I put in the first text field, the number 20 to get the I just greater or equal to 20 and below. I put value. 32 f. I just equal or less then 30 40 skies. I have to use the and operator in order to get the data debt correspond to this range off values from 20 to 30 If I just want to get people with age under 50 I m a fielder with condition is less than 50 years. When you click OK, you get the result. You can always combine multiple filters with text and numbers to get information that you want to finish to clear the filters, we have to go to each of the combs with filters applied and select the clear filter option already used the same option into data tab. To remove the filter feature. You just need to uncheck filter option in the data tap. Like I do know, Thanks for watching. 18. Cell references to link data - Relative, absolute and mixed: all people that use excel need to work with data that is often related. This chapter will be fundamental to understand. All data is processed in excel and the importance of using references debt work as an address that links the different data. The use of references saves a lot off time when working with large amounts off data and makes processes more efficient. I now present a very simple example off a worksheet debt contains in column a least off 10 names, and I want that same least repeated in seven other columns. The desire behavior is that as you make up sites to the names off Cold Night, the other seven calls automatically uptight. If I simply write the data in each off the columns and take two minutes in each of them, I will take a total off 16 minutes to change the data. If I changed the data on Lee from column A and the other columns changed automatically, I will only take two minutes to change the eight columns, saving a lot of time in the second situation. It's here death. The importance of references in Excel arises. As I initially said, the work gets addresses that allowed to connect. Did ICTR sell references in Excel Evan, Important Role being available? Three different types of references. Correlative, absolute and mixed. Let's understand what our cell references all they work and in which situation they are used, As I said in the first videos, the cells are identified by their reference that is composed by letter that identify row and a number identifying a column. There are three types off cells references like a cell related absolute at mixed that be I've differently when copies and field to other cells related references change When a formula is copied to another cell, absolute references on the other end remains fixed no matter where they are copied. Mixed references like the name says, is a mix using the two previous references related and absolute. Starting with related. That is the default referenced used in Excel because when you want to copy multiple cells, they change always based under related position off rows and columns. Using a simple example to be more easy to understand. I have a Siri's off numbers in column A, and I want to have the same values in column B side by size, using the sale references Debt is given by the role number and column letter to make a copy using a cell reference. First, I select this nation sale. That is my Selby, too. Then I use equal operator, followed by the reference off the cell that I want to copy. So I write reference a to after press. Enter Selby to stays with same value as sell a to If you click Bottom writes off Selby to and dragons the results that you get in the cells. Off column B We'll always be related to the corresponding sell on its left side. Reason why this type of reference is called related, but there are situations where you do not want results based, unrelated references but related with a specific cell we'd row and column Fixed to make calculations are copies based on a reference that does not change. We use absolute references. I make now the same operation with the group of cells off column I, but this time using an absolute reference to define an absolute reference in a sale, I used the dollar symbol before the number and laughter debt refers row and column. Using this method, you keep a row and calmed fixed. I now get the results for the remaining sales, and you see that it is not change if I drag the cells always getting value. 10. Since the reference used is always in relation to sell a one and now to finish mixed reference, that is a mix we're parts off. The reference is absolute and other part is related. Where on Lee can be copied? References across Rose are columns being the reference off. One of them fixed. I now use column D to create the example off mixed references where I just fixed row or column using in Cell D to the reference A to fixing called I with dollar symbol. What happens if I dragged the cells down? I have no changes in the results, since my reference is always in column I that is fixed, getting the same result when I used related reference. And if you Onley fix the line to what happens now as I get the results for the cells below , you watch that the result does not change getting always value 10 since Roe two is fixed and the results that you get is related to sell A to working in disguise is an absolute reference. This is not the most used reference type, but it is important to understand how it works. In most operations performed with formulas and functions are used related and absolute references, as will be seen in the next videos Thanks. 19. Excel Formulas - How to create a formula using math operators: Microsoft Excel can be used for basic arithmetic operations like subtraction or multiplication. Using simple formulas in a worksheets you learn previously out to use cell references, which will be very useful, making finalists easier and more efficient. First, let's understand what use a formula. The formula is an equation. Debt performs a calculation like when you use a calculator. Excel is a perfect application to do that type of operation being possible to execute formulas, debt, add, subtract, multiply, divide or something more complex using cell addresses. Also known SL references To get cell values. Let's look to some examples off simple formulas where I use standard operators such as a blue side for addition minus for subtraction s risk for multiplication and forward slash Far Division you saw in another video debt to get a cell value using references, we start to using the equal operator. The same happen. We'd simple arithmetic operations and any kind off formula in the stable. I ever least of numbers, and I want to write a formula that makes basic operations like addition, subtraction, multiplication division and average and in the table. On the right side, I have the symbols used in excel to perform this calculations to the first guys I ever in addition, and to do this formula first, I select the sale where I want that the final result to appear corresponding to the sale see, too, and type equal signal. Then I select reference off the first cell a to and used addition symbol and second sale reference Be, too. When I press enter, the result off the addition appears. And now I dragged the sell down to copy this formula to the other cells, getting the other three results. The great advantage of using the references is that when I change the numbers one and two, the formula update automatically since the reference cells don't change the second calculation is subtraction using the same numbers. The only change their time make is used off minus signal. Instead, addition, Symbol dragging First sale down. I get the other results Similar position. But now for multiplication, using s risk that is used to multiply two numbers. Next, I have the division with forward slash symbol used to divide the two numbers and to finish I want average off two numbers. You know that an average is equal to the addition of all numbers and then divided by how many numbers there are for this guys, I can use the result off the formula that as the addition that I calculated in column C and divide death addition by two giving the average okay like you're watching this video. The use of farmers in Excel is very simple and intuitive, being very used together with functions to perform calculations in a work shit. In the next videos, let's understand what are functions and see some of the most important functions used in excel Thanks. 20. Use Functions in Excel - Syntax used to create functions: Okay, let's now understand what is the difference between a formula and a function? The formula is in arithmetic expression used to calculate a value, and the function is a bread. If I ng formula debt already is available in Excel, performing a specific calculation based on values called arguments, Debts user select. In the previous examples, I used formulas to make some calculations like additions and averages with numbers, but now I can't perform calculations. But using the Excel defined functions, let's know, learn out to insert a function. First step is select the cell where I want to the result to appear for this guy cell D to. Then I click the insert function button and the dialogue box Insert function appears where you can select a function functions that are divided by different categories. If you do not know function category, you can choose all the categories options to least all the function names. Or you can simply use the text field at the top and right and name off the function that you want to use. For this example, I write this M toe find dysfunction at the bottom off the dialogue box appears irrelevant. Information explaining what the selected function does. What can be useful if the user doesn't know what a specific function does? Foursome. You can read that adds old the numbers in a range off cells. Now we the selected function. I click OK and function arguments. Dialogue box appears the next step is click in the range box to select the Range A to B two . Debt contains numbers to the addition. As you see in function arguments. Window. You have more than one range box number one to tree, and so one, which can be useful if you want to select multiple ranges off none, edge isn't cells. I choose now, okay and result appears in Saudi to, and you see that in this cell is visible the function with the respective range inside appearances. So another option to other function is write a function name in the cell that receives final results like I did with the formulas. When starts writing into sale, the function name, at least off functions will appear, depending the text that I write. Being easy to access the required function. After choosing it, I select the cell range debt will be inside appearances if I select more than one, none adjacent to range of cells. The range will be separated by a semicolon. Now that you know, all functions work, let's watch in the next videos some of the most important functions available in excel. Thanks for watching. 21. Add Numbers with Sum Function: Let's watch the first function in Excel the same that I did a small approach. When I explain our function work, some is categorized as a mat and trigonometry function. The function is used to some all cells that are supplied as multiple arguments. Being one of the most popular and widely used functions in Microsoft Excel, let's no make a simple exercise to apply dysfunction when I f three sets of numbers. The first calculation to be might is the sum off Group one appearing in Cell Age to the result. I then go to Tab Formula and select the insert function option and select in the Mets and trigonometry category defection. Some Now I inserts in the list of arguments the sale range to some, and I have my final result in salads, too. I'm now going to make the same. Using Doonan. Edge isn't cell ranges for the situation. I placed the first cell range in the number one box, while the second range is placed in number two, and so we get the same off these two sets off numbers in the formula bar. You see the format of the function where the cell ranges are with the inference, is separated by semi colons, using another method for the same off the three cell ranges, I first write the some function in Sale J two and inside appearances. I put the tree cell ranges all separated by semi colons, getting the addition off these three cell ranges. Let's watch in the next video other important function. The average function. See you soon. 22. Average Function for cells ranges average calculation: now the Excel average function that returns the average off numbers. Having a syntax similar with some function receiving one arm, Marcel ranges To understand how it works. I have a table with the average using a formula in which I made the same off two numbers, and after that, to division by two, I now calculate again the average off numbers but using two different methods for the first methods. I combined functions and formulas using the same function in Cell D to and dividing by two . To get the average off these two numbers for the second methods, I use only the average function to use it. I go to insert function option inside formalist step. This function is inside statistics category and after choose average, I just need to select cell range. Returning at the end. Do correct average value that is equal to the value that I get with the first method. As expected, Thanks and see you next. Video 23. How to use Statistical Functions Min and Max: Let's see now to other statistical functions mean ed marks that are very simple to apply. Starting with me. Dysfunction returns the smallest numeric value in a range off cells. Defection s arguments. Debt can be provided s Constance cell references or arranges. Looking at this example, I have a table that shows the number off car sales in a particular car star over several days. If I used the mean function and at the range off cells containing the number off car sold, this function returns the lowest value off car salt for one day. For this particular case, I choose range between see tree and C 14 and I get the lowest value that is in cell C six, corresponding to 27 cars. The other function that I can use is Max that is very similar with mean function. But instead of return, the smallest value returned their largest numeric value doing the same exercise. But now we dysfunction. I select the same arrange off sales. But now my expectation is that it returns the I guess value and that what happens showing value 95 debt corresponds to the I guess value off the cell arrange. Thanks 24. Count and CountIf Functions: I welcome to another lesson. You saw some statistical functions, like average or mean and Max. Let's see now, other to statistical functions named Count and Count. If first, let's watch count. Dysfunction counts the number off cells in a range that contain numbers. Do it simplify these function. I use the same data table from previous exercise, where I have the number off car sold on different days. I want to get all many days I you have in this table to return that value. I use count function to return number off cells containing numbers, if you remember from previous lessons. Excel source, date and time values as serial numbers. So if I use count function in arrange that contains sites, this will be seen as numbers. Using the function. I select the range with dates from Sell a Tree to sell a 14 returning for this example. 12. Answering to the initial question. I have 12 days represented in my table. If now I simply changed the date to text, what happens? Excel See that one of the sales is no longer remember, so the function returns 11 instead of 12. Now, a quick exercise to calculate the average off car sold using Sam and Count functions. As you know, the average is the same off the numbers, divided by the number off numbers. I can get the average if I at all car sold using the same function, divided by the number of days that sales were might using the count function. This is what I do now where I used the same function with the cell. Arrange See tree. See 14 dividing using the count function with cell, arrange a tree a 14. Another possibility is the cell range debt was used in function sam with cells from C treat to see 14 and I get now the average off car sold Excel as another function very similar called county, if that. Instead, off count, the cells that contain numbers in a range counts the number off cells. Debt meet. A condition for dysfunction no longer applies Onley numbers in sales, so you can analyze text or other type of data that meet the criteria. Infections, arguments that look box. You see the two different arguments received by the function, the 1st 1 in which you have the range off sales that you want to analyze and the second where you define the condition toe. Apply to the cellar range, using logical operators that you see in this table where I show you the most used first equal to second, not equal to less than less than or equal to Greater Dan and finally greater than are equal to using two examples. First, I want to get on many days. Martin. 40 cars have been sold for the scenario. I have to select the range with number off car sold being my condition. The cells with valley rater than 40 using the greater than signal. So I f in the table eight days in which were sold more than 40 cars. If I change condition to know number off days that were sold at least 50 cars, I use logical operator greater than or equal to 50 and you see that two final result change . Thanks for watching 25. Exercise with Logical Functions IF, OR and AND for single and multiple conditions: I Let's not present logical functions. Where and of the most important in this category is if function one of the main advantages off dysfunction and generally all excel functions is that can simply be used individually are combined with a set of other functions are formulas what gives a great ability to work data from excel? So how does this if function works? Using a sharp definition? If tests a condition, debt in functions, arguments corresponds to a logical tests and returns the value if that condition is true or another value. If condition is false, develop that the function returns can be text simple or complex arithmetic calculation and in some cases can return other function. To understand better to function. In this first example, I have a table. We do tests classifications for far students. And for the first scenario, I want the average off the two tests where, if the result off this average is less than 50% being this my condition, I have to write certain test to say that the student will have to take a certain tests, and in the case, off average is equal or greater than 50%. The text to write is approved. This guys is the most simple situation that you can use with dysfunction having a single condition and two possible situations applying Now this casing excel. I opened the function in the sale where I want a result for the first student. In the first argument I put to condition if average that is in Saudi too is less than 50%. Now that I have the condition, I write in the second argument the value if the function is true, corresponding to text third test and in the case off condition false, that corresponds to an average greater than or equal to 50%. I write approved After I get my results. I just simple copied the cells below and get the results to the other tree students. You see that you have two guys is officer tests for John and Martha and two cases of approved Let's see now how to use multiple conditions with the function in said, using a single condition for these parts off this exercise, I used the logical operator or which allowed the function toe work with multiple conditions for these particular guys to propose and taking into consideration the performance into tests by the four students. Onley one Tests need to be greater or equal, then 50% for the student to be approved. That's the reason why I used the function are because I need now to conditions one for each test were on Lee. One of them need to be true for disguise, be greater or equal to 50%. The our function will be inserted as the first argument off the function, which receives the multiple criteria that will be tested in the or function. The different conditions are separated by semicolons. For this guy's criteria for the first test is given by the sale be too and must be equal or greater than 50% while the second criteria will be best to equal or greater than 50%. Joining the two conditions in the our function. If the conditions are is through, you write approved and if the condition is false, meaning that the two tests are less than 50% you write certain tests. The function with multiple criteria using our function is now completed. The next example is very similar with the previous. The only difference is that I need to do to conditions to be true. That's why I used the end function that is through when all conditions inside are through. I have two tests and both need to be greater or equal to 50%. So I write this condition in the first argument off the function using logical function and we're test one need to be equal or greater than 50% and test two with the same condition after right this condition I put approved if the end function is through in case of false I right, certain death dessert condition is now my for the scenario. All the students are not approved because at least one off the tests is less than 50%. If you change now, test two of Laura to a value greater or equal to 50%. You see that Laura Bess is from sir test to approve because the two tests meet the condition. Now you know, out to use the function for different situations, using only one condition and multiple conditions together. Really logical operators are end, and thanks for watching 26. Conditional Addition with Sumif and Sumifs: I we have already seen out to make additions, being goalie necessary to use the some function, selecting the cell ranges that we want to add. The next step is to do additions but following specific conditions defined by the user. We've seen this table, an example that shows the number off booked rooms for trio tells between January and April . If I used the stable data and simply some the range off cells in column D I get the total number off booked rooms containing the tree hotels. But now I want the total number of rooms reserved for each hotel being an ideal situation to use the same. If function where some is made based on a condition, they'll tell death. I truth watching now the semi function arguments in the first I have the range sales where I apply the condition corresponding to the hotel name least between cells, see tree and see farting being the criteria. The Artal name in disguise is Hotel Madrid. Debt corresponds to sell F two. The same range is the cell range that contains the number off booked rooms. So I put the range de tree de farting. The final result shows the total booked rooms for our Tell Madrid and to have the book rooms to the other two hotels just need to copy the function to the other two cells where I have the different conditions in cell F three and F for as expected, the same of these three values is equal to the sum that I get initially in column D that, as the total addition without any criteria applied for disguise. We used a single condition hotel name, but we can add more than one criteria using December ifs function debt I use now keeping the criteria for the hotel name. But I at the second condition to choose the month. As it happens, with first situation, we continue to have some range that now is applied in first argument and the hotel name and respective condition is applied in second and third argument. Finally, I add the new range of sales corresponding to the months where I defined the condition January that is in sale J two. We have now the same of rooms reserved for the Hotel Madrid's in the January month, when we change the Auteuil name and month celeb sites to the information required. As you can see now, Thanks 27. Extract Data using Vlookup Reference Function: it's time now to look up. Function developed up is maybe the most used look up function inserted in look up and reference category. Dysfunction is responsible by vertical look up, searching for a specific value in the first column off the table and returned to value in the same row using column in the exposition. But let's see a simple example to understand the different arguments used by the function and how it works in the spreadsheet, I have this table to the right where I f d identified three different objects available for sale using an I. D. Number in college. Okay, idea. One far pencil, two for Penn and three forever with the respective prices in column em on the other side. I have total sales off these brothers uring a specific day in Table one, where I only F brother tidy for each sale, with the respective quantities sold as they see the two tables ever commenting for MOESHA COLUMN The I D. It's this field that allows in table one to get the correspondent brother name in Table two . Let's see now the arguments off the Villa Cup function data Lalas To get this data The first argument is the look up value, which is the common value off the two tables in disguise. I want to look for the i D off the sale a tree in the second table in order to get correspondent brother name in the second arguments table Ray, I select the table data where I want to extract the information. For this example, it will be the data range K three m five. An important detail is that the idea column that allows me to relate to the two tables must be the left most field. When I select the table array, which happens when I choose this range call index them is a certain argument that tells me which calmed off the table Ray as the information that I want to get the left. Most column corresponds to number one. The second number to win so on for this guy's calm with the product name corresponds to the second column off the table, right? So desert argument receives the value, too, lest argument receives the Valley. True, if we are looking for approximate value are false. If you look for an exact match, that is a situation that we have. I want to find an I D that is equal into two tails. We get then results by clicking OK showing product name identified by I. D to Ben. If I copied the function to the next line, we see that the table right as moved down and and wanted to be a V er, since the table rate data need always to stay with the same arrange. So I created this data range with absolute references to work around this problem. Now I copied the function again, and I can see that I get the product name and column B corresponding to the correct I D. That I m. In column I for the price unit calm. I use again the same function, not being very different from what I did with the brother name. The first argument will be the sale a tree again and same data range for second argument table array. The difference is the certain argument, because now I want to get the prices off the products being disinformation. Insert column off the table array. So let's put the number three into call Index Nam arguments, and the last argument remains false. So using this function, I get the unit prices for each of the products in table one and now to finish after getting product names and unit price In column e. I want to find a value bite in each of sales. Pulling, being a sincerity. Multiply the price unit in self Fitri by the number of units sold in cell d tree situation visible now where I copied this formula to get the results for all the Kaiser's thanks. 28. Hlookup Function to Extract Data : age look up is an excel function that look up and retrieve data from a specific role in a table being very similar with popular Villa Cup dysfunction stands for a resentful where look up values appears in the first rule off the table moving or is entirely to the rights . The big difference is that Villa Cup searches for the value in a column. An age look up searches for the value in a row dysfunction. As for arguments to look up and get data from a specific row in the table using this example, I f. I D client debt is a common label into two tables and this I d help me to get names debt I have in Rhode Tree returning those values to calm, be in table two. So in the first argument, look up. I use sale reference. A certain we'd first i d. From table to that, I need to find in Table one for second arguments. I defined data range B to F six range, where I find my D clients and correspondent names. The row number that returns the one to decide ITER is my cert arguments where number one corresponds to first row from the selected data range, the I D client number two to second rule for names and so on. As I need the client's name, I put in the third arguments. Number two, lest argument receives two possible values. False. If I want an exact match for the look up value being, the situation that happen and value true if we have an approximate match as expected, I get din a Meghan that match with I d Tree. And now I just simply copy function to the remaining ID's getting Detrick values, Thanks.