Advanced Data Analysis Techniques Training

Introduction:

Nowadays, it is no surprise that corporate staff is overwhelmed by the abundance of unstructured data. ERPs and databases have evolved to a point where they can house large amounts of data. The question now is, what do you do with this data to add value? This advanced data analysis techniques program will introduce hands-on business intelligence (BI) to allow you to clean, normalize, and interpret large volumes of data.

You can establish historical relationships, analyze the current situation, and predict future strategies. BI's borderless application covers operational, tactical, and strategic business decisions. It spans all departments and cascades down to all users who perform data cleansing, reporting, analysis, modeling, integration, and automation.

In this advanced data analysis techniques course, we use MS Excel exclusively as an ultimate and readily available BI tool, allowing you to develop an exclusive level of expertise and add immediate value to your job and company.

Defining Advanced Data Analysis Techniques:

Advanced data analysis techniques encompass various methods for inspecting, cleaning, transforming, and modeling data to discover useful information, inform conclusions, and support decision-making.

This advanced data analysis techniques course dives deep into these techniques, covering not just quantitative but also qualitative, historical, case study, and customer data analysis techniques. Participants will gain insights into common data analysis techniques and the instrumental tools that enable efficient and meaningful analysis.

Targeted Groups:

• Business Professionals.
• Business Analysts.
• Data Analysts.
• Research Analysts.
• Finance Professionals.
• Marketing and Sales Professionals.
• HR Professionals.
• IT Professionals.
• Administrative Staff.
• Supervisors, General Business Professionals, and staff from any function need to learn and apply state-of-the-art data analysis techniques to their daily business reporting and decision-making.

Course Objectives:

At the end of this advanced data analysis techniques course, the participants will be able to:

• Boost Excel Business Intelligence (BI) expertise in data slicing, dicing, massaging, and data aggregation and modeling.
• Perform data normalization, consolidation, report writing, analysis, and reconciliation.
• Link Excel with Access, Web, Text, Internet, SQL, ERPs, and other databases to develop dynamic BI models, dashboards, scorecards, and flash management reports.
• Advance and enhance the look and feel of reports using dynamic visualization techniques
• Acquire numerous tips and tricks that enable them to work efficiently.

Targeted Competencies:

By the end of this advanced data analysis techniques course, the participant's competencies will:

• Massage and normalize data.
• Report, analysis, and reconciliation.
• Interpretation of large data sets.
• Model and what-if scenarios.
• Establish data integration.
• Develop dynamic dashboards and scorecards.
• Design key performance indicators.

Course Content:

Unit 1: Data Analysis Tools and Techniques:

• Consolidate data from separate files and sheets.
• Know advanced data validation using lists, dates, and custom validation.
• Powerful array functions.
• Cell management tools: left, right, mid, concatenate, value.
• Name, edit, and manage cells and ranges.
• Subtotal, Sumif, Sumifs, sum-product, count, Countif, and counties.
• Look up data, texts, and values using Vlookup.
• The incredible table-tools technique.
• Slice dates into day names, weeks, week numbers, month names, years, and quarters.
• Text-to-columns and dynamic trimming using trim and lens.
• Manage texts and numbers using replace, find, and substitute.
• Text change functions.

Unit 2: Mastering Data Reporting:

• The 20 must-learn pivot-tables tools.
• Create pivot tables.
• Number formatting techniques.
• Design report layout.
• Sort in ascending, descending, and more sort options.
• Filter labels and values.
• Expand and collapse reports.
• Drill down option.
• Summarize values by sum, average, minimum, maximum, count.
• Show values as % of total and % of.
• Pivot table options.
• Insert formulas.
• Data analysis.
• Copy pivot tables.
• Create pivot charts.
• Dynamic chart labeling.
• Master the slicer.
• Show report filter pages.
• Linking pivot tables and pivot graphs with PowerPoint.
• Conditional formatting with pivot tables.
• Design reports using the get pivot data.

Unit 3: Data Modeling and Integration:

• Spinner.
• Checkbox data modeling with the if function.
• Option button data modeling with if function.
• List box data modeling with choose function.
• Scenario manager.
• Link Excel with text files.
• Link Excel with databases (Access).
• Understand the linking of Excel with SQL.
• Link Excel with the Internet.
• Link Excel with Excel.

Unit 4: The Look and Feel:

• Chart and visualize techniques.
• Use the camera tool.
• Work with formula-driven visualizations.
• Use fancy fonts.
• Leverage symbols in formulas.
• Work with sparklines.
• Create unconventional style charts.

Unit 5: Tips and Tricks:

• Control and protect your reports, worksheets, and workbooks.
• Data entry form.
• Custom list.
• Text-to-speech.
• Understand advanced conditional formatting.
• Shortcuts for your daily work.