Excel 365 – Data Management

Overview

This 8-clip course gives you an in-depth exploration of the tools available for data analysis and examines complex formulas in Excel for Office 365. Students will learn how to use Power Query to transform data to fit in an Excel file. This course demonstrates how Excel allows you to import and edit data in a CSV (comma-separated value) file. Next, you’ll learn how to use the LOOKUP formula to find a value in a double-entry table. This course goes on to show you how to use different formulas to make calculations when multiple conditions are applied. You will examine the IFS and SWITCH functions to test more conditions without embedding the IF function. Next, you’ll learn how to use AVERAGEIF, MINIFS, and MAXIFS. You will learn how to use the NPER (required number of periods) feature to calculate the number of months required to repay a loan. Finally, you will learn how to use the IF and DNA functions for data prediction.

Objectives

• Import, edit, and update data from a text file
• Import, edit, and update data from a .csv file
• Use LOOKUP, MATCH, and INDEX functions to extract data
• Apply multiple conditions without nesting other functions
• Calculate average values using one or more conditions
• calculating the smallest and largest number that meet one or more criteria
• counting cells that meet one or more criteria
• calculating the periods required to pay off a loan and predict loan approval , which meet one or more criteria