EXCEL FOR DATA ANALYSIS AND REPORTING
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.
This intensive online 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.
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.
Class Session: 10:00am – 2:00pm with a 30 minutes break session