Managing Data in Excel 2019 for Windows
Excel 2019 (Windows)
| Intermediate
- 8 videos | 42m 26s
- Includes Assessment
- Earns a Badge
- CPE
Excel offers a set of tools that allows you to explore more in detail data analysis and complex formulae. In this 8-video course, you will learn how to use different formulae to make calculations when you have multiple conditions imposed. You will also become able to forecast data by using the NPER function. Key concepts covered in this course include how to import, edit, and update data from a text file; how to import, edit and update data from a .csv file; and how to use the LOOKUP, MATCH, and INDEX functions to extract data. Next, you will learn how to run multiple conditions without nesting other functions; examine how to calculate averages by using one or more conditions; and learn how to calculate the smallest and the largest numbers that meet one or more criteria. Finally, learn how to count cells that meet one or more criteria; and how to calculate the number of periods to pay a loan and forecast loan approval that meet one or more criteria. In order to practice what you have learned, you will find the Word document named Excel 2019 for Windows: Managing Data Exercise as well as the associated materials in the Resources section.
WHAT YOU WILL LEARN
-
Import data from a text file
Import data from a .csv file
Find a value in a double entry table
Use the ifs and switch functions -
Calculate averages using one or more conditions
Use the maxifs and minifs functions
Find data with multiple conditions
Use the nper function to calculate a loan repayment period or forecast data
IN THIS COURSE
-
4m 39sExcel allows you to import data from a text file and edit as you need. With Power Query you can transform the data to fit your Excel file and then replace the data to highlight what you want to show. If you update the source document, the Excel file can be updated to incorporate the changes. FREE ACCESS
-
4m 38sExcel allows you to import data from a .csv file and edit as you need. With Power Query you can transform the data to fit your Excel file and then replace the data to highlight what you want to show. If you update the source document, the Excel file can be updated to incorporate the changes. FREE ACCESS
-
3. Finding a value in a double entry table in Excel 2019 for Windows6m 57sYou can use the LOOKUP formula to find a value in a double entry table that you have created in Excel. You can, for example, use it to find a client's address based on their last name. You can also use the INDEX formula to return a particular value in a table if you know its row and column coordinates. FREE ACCESS
-
4. Using the IFS & SWITCH functions in Excel 2019 for Windows6m 38sThe IFS and SWITCH functions allow you to test multiple conditions without nesting the IF function. With Ifs you can use different operators while with SWITCH you can get a result for a non exact match. FREE ACCESS
-
5. Calculating average with different conditions in Excel 2019 for Windows4m 3sIf you need to calculate the average in a certain range but have a criteria to apply, you can use the AVERAGEIF font ion. Excel also proposes the AVERAGEIFS function that calculates the average in a range if you have more than one criteria. FREE ACCESS
-
6. Using the MAXIFS & MINIFS functions in Excel 2019 for Windows4m 17sThe MINIFS and MAXIFS functions allow you to find the minimum and maximum value in a range after applying one or more criteria. You can use logical operators and wild card for partial matching if necessary. FREE ACCESS
-
7. Finding data with multiple conditions in Excel 2019 for Windows4m 53sYou can use the COUNTIFS function when you need to count the number of cells that meet one or more criteria. With COUNTIFS, you can apply more than one criteria in more than one range. Excel also offers the NOT function, that returns the opposite of a logical value. Combined with the IF function you can determine, for example, the availability of a product. FREE ACCESS
-
8. Forecasting data in Excel 2019 for Windows6m 22sIf you need to calculate how many months it will take you to reimburse a loan according to a fixed monthly payment and a rate, you can use the NPER function. You can combine the NPER function with the IF and OR functions and forecast data the meet one or more criteria. FREE ACCESS
EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE
Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.
Digital badges are yours to keep, forever.
