COURSE COVERAGE

INTRODUCTION

Do you need to pull out relevant information from large data sets and make sense from the data? This is what our Excel For Data Analysis and Reporting course is designed to help you with: to show you a range of techniques that will assist you to get at the information you are interested in, quickly. We start by looking at how to get data into Excel from a number of different sources, then explore the best ways to display and manipulate that data to give you a professional result. We also use a range of advanced functions, descriptive statistics, forecasting tools, sheet controls and explore a number of advanced chart types. 

 TARGET AUDIENCE 

This intensive hands on three days course is aimed at existing users of Microsoft Excel who want to use the more advanced data manipulation and display features in order to produce meaningful, professional and interactive reports and charts. Delegates should have a very good existing knowledge of Excel and ideally would have attended, or have knowledge of the concepts covered in the Excel Introduction and Intermediate courses. 

OBJECTIVES: 

After completing this course, students will be able to: 

• 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. 
• Create a PivotTable for analyzing and comparing large amounts of data; modify the PivotTable view by using slicers and timeline 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. 
• Use the IF, IFS, AND, OR, NOT and IFERROR functions to test whether conditions are true or false and make logical comparison; use conditional functions to calculate a value based on specified criteria; 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; use financial functions for loan amortization and change calculation options and iteration limits. 

• Be able to pay attention to important data points such as deadlines, at risks tasks or budgets, create alert system. Also, make large data sets more digestible by breaking up the wall of numbers with a visual organizational component and keep on top of workload using conditional functions. 

COURSE OUTLINE 

• 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. 
• Reference cells, link worksheets and workbooks to interact and link excel data to microsoft word and powerpoint and consolidate date. 
• Use the Data Validation feature to validate data entered in cells; and use advanced filter options to display the data you specify and data consolidation. 

• Ability to understand the way excel stores dates and times, create dynamic date-based fields and perform complex time-based calculation 
• Able to generate descriptive statistics such as mean, median mode, standard deviation skewness, kurtosis from your data and interprete them. 
• Format data points in charts; create combination charts and trendlines; insert sparklines; use chart templates; and add and modify drawing objects and shapes. 
• 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. 

Module 1: Importing and Linking Data 

Learning outcomes: Import, sort and filter data from a number of different sources into an Excel spreadsheet 

Topics covered: 

• Importing Data from a text file 

• Importing Data from an XML file 
• Linking to an External Data Source 
• Using Microsoft Query to Control Linked Data 
• Modify and Update Connections 

 Module 2: Referencing, Sheet Linking and Consolidate 

Learning outcomes: Reference cells, Link worksheets and workbooks to interact and link excel data to microsoft word and excel and consolidate. 

Topics covered: 

• Relative, Absolute and Mixed References 
• Link to other Worksheets 

• Copy & Paste to Create Links 
• Working with Multiple Worksheets 
• Linking Worksheets with Formulas 
• Working with Multiple Workbooks 
• Linking Formulas Across Multiple Workbooks 

• Link Cells to other Workbooks 
• Update Links to other Workbooks 
• Edit Links to other Workbooks 
• Break Links 

• Insert Hyperlink 
• Remove Hyperlink 
• Link to Microsoft word and PowerPoint 
• Data consolidation 

  Module 3: Data Manipulation with Pivot Tables 

Learning outcomes: Summarise information from large data sets. Group, filter and perform calculations on summarised data and create charts. 

Topics covered: 

• PivotTable Geography 

• Rules of pivot table 
• Creating Pivot Tables 
• Grouping Pivot Data 
• Calculated Fields in Pivot Tables 
• Filtering Pivot Data 

• Changing the Pivot Table Display Methods and styles 
• Creating powerful pivot charts 
• Insert slicer and timeline 
• Drill down and drill up 
• Creating pivot tables styles 

  Module 4: Conditional Formatting 

Learning outcomes: Be able to pay attention to important data points such as deadlines, at risks tasks or budgets, create alert system. Also, make large data sets more digestible by breaking up the wall of numbers with a visual organizational component. 

Topics covered: 

• Applying Multiple Conditions 
• Custom Conditions 
• Preset Conditions 
• If-Then Logic 

  Module 5: Advanced Reporting Functions 

Learning outcomes: Use a variety of advanced functions to interrogate and manipulate spreadsheet data 

Topics covered: 

• Vlookup and Hlookup Function 

• Using the GetPivotData Function 
• Using the Index Function 
• Using the Match Function 
• Using Index and Match Together 

  Module 6: Conditional /Logical Functions 

Learning outcomes: Test whether conditions are true or false, make logical comparison, sum,count, average based on criteria 

Topics covered: 

• If/ifs/nested if function 

• And/Or/Not Function 
• Sumif /Sumifs Function 
• Countif /Countifs Function 
• Sumproduct Function 
• Averageif/Averageifs 

• Sum/Count/Average Based on Multiple Criteria 
• Using the Choose Function 

Module 7: Data Analysis Tools 

Learning outcomes: Be able to generate descriptive statistics such as mean, median mode, standard deviation etc and interpret them. 

Topics covered: 

• Measure of central tendency 
• Standard deviation and standard error 

• Kurtosis and Skewness 
• Range, Minimum and minimum 

 Module 8: Date and Time Functions 

Learning outcomes: Understand the way excel stores dates and times as numeric values and ability to create dynamic date based fields and perform complex time based calculation. 

Topics covered: 

• How to add time to dates in excel 
• Extracting the components of a date 
• How to subtract dates 
• Use today’s date in excel 

• Retrieve numbers from dates with date functions 
• How to autofill dates 
• Date and time formatting 
• Performing calculations with time 
• Age calculation in excel 

Module 9: What-If Tools 

Learning outcomes: Be able to test out various scenarios; checking the impact of varying inputs on outputs, forcast and find solution to complex problem. 

Topics covered: 

• Using Goal Seek 
• Data Table – One Variable 

• Data Table – Two Variables 
• Create Scenarios 
• Show Scenarios 
• Edit Scenarios 
• Delete Scenarios 

• Compare Scenarios 

 Module 10: Formula Auditing 

Learning outcomes: Be able to display the relationship between formulas and cells, show formulas and find circular references 

Topics covered: 

• Error Checking 
• Evaluate Formulas 
• Show Formulas 
• Circle invalid data 
• Find circular references 

• Trace precedence 
• Trace Dependence 

Module 11: Financial Functions 

Learning outcomes: Do some financial appraisals and prepare a loan amortization schedule 

Topics covered: 

• NPV/XNPV 
• IRR/XIRR/MIRR 
• PMT/IPMT/PPMT/ISPMT 
• FV/PV/DB/NPER 

 Module 12: Protect / Share Workbooks 

Learning outcomes: Be able to protect your work and share your workbook for collaboration 

Topics covered: 

• Lock / Unlock Cells 

• Protect Sheet 
• Protect Workbook 
• Control Workbook Access 
• Share Workbook 
• Track Changes 

 Module 13: Automating Reports with Controls 

Learning outcomes: Automate the appearance of charts and data using a variety of built in controls and macr

Module 14: Advanced Charting Features 

Learning outcomes: Explore advanced charting options and techniques to display complex data in a professional way 

Topics covered: 

• Creating Combination Charts 
• Displaying a Secondary Axis 

• Modifying Axis Options 
• Display Data Trends 
• Charting an Acceptable Range 
• Charting Targets 
• Create and edit sparklines 

 Prerequisites: 

1. Each participate is expected to have access to a system during the training as this is a practical class and this would aid active participation and understanding. 

1. Prior knowledge of excel is a compulsory as this is not a beginner non intermediate excel class. 

Class Session : 10:00am – 4:00pm each day including Breakfast and Lunch time

Customize Your Training

This training solution and other training topics from McTimothyAssociates are currently available as residential/in-house option.

Please Note

McTimothy Associates Reserves the right to postpone or cancel a course due to unforeseen circumstances beyond our control. In such cases delegate(s) will be registered for the next run of the Course.

Duration: 

3 days  

N:B: All exercises and project files used on the course will be available for class use and take home.