Length: 1 Day
Learn to perform advanced data analysis, collaborate and automate workbook functionality.
To ensure your success in this course, it is recommended that you have completed Microsoft Excel - Managing Data and Large Workbooks or possess equivalent knowledge.
WHAT YOU’LL LEARN
After completing this course, students will be able to:
• Use the IF and IFERROR functions to calculate a value based on specified criteria; use conditional functions to summarize data; use PMT function to calculate periodic payments for a loan; use text functions to extract data strings; use date functions to calculate duration in years, months, and days; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
• Use the VLOOKUP and HLOOKUP functions to find values in worksheet data; use the MATCH function to find the relative position of a value in a range; use the INDEX function to find the value of a cell at a given position in a range; and use data tables to project values.
• Use the Data Validation feature to validate data entered in cells; and use advanced filter options to display the data you specify.
• Format data points in charts; create combination charts and trendlines; insert sparklines; use chart templates; and add and modify drawing objects and shapes.
• Create a PivotTable for analyzing and comparing large amounts of data; modify the PivotTable view by using slicers to filter data and by rearranging fields; improve the appearance of a PivotTable by changing its field settings and applying a style; and create a PivotChart to graphically display data from a PivotTable.
• Export data from Excel to other formats, and import data from a text file into an Excel workbook; and use Microsoft Query and the Web query feature to import data from external databases.
• Use the Goal Seek utility to meet a target output for a formula by adjusting the values in the input cells; and create scenarios to save various sets of input values that produce different results.
• Run a macro to perform tasks automatically; record macros; assign a macro to a button in the worksheet; edit a macro by editing VBA code.
Advanced functions and formulas
• Logical & Conditional functions
• Financial functions
• Text functions
• Date functions
• Array formulas
Lookups and data tables
• Using lookup functions
• Using MATCH and INDEX
• Creating data tables
Advanced data management
• Validating cell entries
• Advanced filtering
• Chart formatting options
• Combination charts
• Graphical objects
PivotTables and PivotCharts
• Working with PivotTables
• Modifying PivotTable data
• Formatting PivotTables
• Using PivotCharts
Exporting and importing data
• Exporting and importing text files
• Getting external data
• Goal Seek
Macros and Visual Basic
• Running and recording a macro
• Working with VBA code