Objectives: Big Data is the current buzz topic. How do you manage more data than you know how to handle? There are challenges managing large and small amounts of data. There is a need to analyse, share, collaborate, report, summarize, control and govern data. There are growing regulatory and compliance requirements concerning the analysis and storage of data that is collected.

This two day course involves both data analysis but also some more advanced Excel techniques will be shown and explained. Also, reporting tools, data manipulation and searching. We will also cover publishing your Excel data in SharePoint along with some Excel Business Intelligence and designing some basic Excel Dashboards.

This course is very hands-on and practical. Each participant will be encouraged to discuss ideas and problem-solve relating to spreadsheets and data sets, utilising the tools and techniques covered during the course. At the end of the course delegates will have learned:

• To utilise advanced functionality in Excel for data analysis and decision making
• Properly handle data collection from multiple sources and data retention in Excel
• Integrate data from multiple sources
• Summarize data to quickly gain a better understanding
• Employ advanced features to assist in complex problem-solving
• Visualise data through the graphing functionality of Excel
• Construct a frequency distribution to analyze data and translate it into relevant information
  

Target Audience: Users of Microsoft Excel who need to get more from data analysis, and those needing to obtain analytical skills for working around their day-to-day spreadsheets.

Duration: 2 Days

Module 1 - Data Analysis and Microsoft Excel

• Excel - spreadsheet or engine for analysis?
• The hierarchical structure of Excel
  

Module2 – Ranges, Anchoring, and Data Tables

• Range references in Excel
• Anchoring references in Excel
• Best practices for databases
  

Module 3 - Sorting and Filtering

• Sorting in Excel
• Filtering in Excel
  

Module 4 - Databases

• Copy from External Source Files
• Importing Data from MS Access
• Importing Text Files
• Text to Columns Wizard
• Applying Auto and Advanced Filters
• Extracting Data with set Criteria
• Selecting Visible Data Only
• Simple and Multi-level Sorting
• Applying automatic Subtotals
  

Module 5 - Working with Data

• Search for Values within a list
• VLOOKUP( )
• HLOOKUP( )
• Database Functions
• Array Functions
• Group and Outline
• Link with MS Access & Access Queries

Module 6 - Conditional Formatting

• Advanced conditional formatting
• Data bars
• Formula based rules

Module 7 - Functions

• Logical conditional,
• error
• textual
• nesting (for increased power)
• array
• IF functions
• multi-cell array functions.
• practical solutions for complex data sets.
  

Module 8 - PivotTables

• Overview of Pivot Table Reports
• Calculation Types and Field Settings
• Calculated Fields
• Group and Sort Data
• Extracting Detail
• Apply Value Field Formatting
• PivotTable Options

Module 9 - Pivot Charts

• Overview of PivotChart Reports
• Create a PivotChart
• Modify Styles and Formats
• Re-arrange Fields
  

Module 10 - Frequency Distribution

• Frequency distribution tables
• Analysis ToolPak histogram
  

Module 11 - Scenario building in Excel

• Setting up Excel Scenarios
• Creating the Scenarios
• Showing the Scenarios
• Adding Scenarios to the Excel Ribbon
  

Module 12 - Practical Session
What to do when:

• Data is too large or complex
• Response times are slow
• Performance is insufficient

Excel Business Intelligence

Module 13 – Excel Services in SharePoint
An overview of some Excel Services in SharePoint

Module 14 – Creating Excel Dashboards

• Dashboards – use of BI with Excel Dashboards
• Data Layout
• Creating Dynamic Dashboards
• Merging and Consolidating Data
• Using Shapes to make better Charts
• Using Alerts to draw attention to dashboards
  

Content 3