EXLL3: Microsoft Excel - Data Analysis and Reporting

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.

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 

Advanced charting 
• 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 

Analytical tools 
• Goal Seek 
• Scenarios 

Macros and Visual Basic 
• Running and recording a macro 
• Working with VBA code