Data Analysis and Business Reporting Techniques Using Excel


OBJECTIVES

  • massaging and normalizing unstructured data.
  • Performing reporting and analysis using Pivot Tables. Also, creating customized scorecards and management reports.
  • Design dynamic reporting models using different modelling techniques. Perform What-If-Analysis.
  • Integrate Excel with many different file types such as access, web, text, SQL, and other databases. Reports will update automatically.
  • Perform repetitive tasks and generate reports efficiently by recording, running and editing Macros.

Outlines

Data Massaging Tools And Techniques

  • 3D formulas
  • Merge and consolidate data
  • Data validation using Custom, numbers, lists, dates, text length
  • Text Functions: left, right, mid, concatenate, value
  • Name Ranges: Naming, editing, and managing cells and ranges
  • Statistical Functions: Subtotal, sum-if, sum-ifs, sum-product, Count and sisters: count, count-if, count-ifs
  • Search other sheets and files: Looking-up data, texts, and values using v-lookup

 

Pivot Tables

  • THE 20 RULES
  • Creating pivot tables
  • Editing pivot tables
  • Updating information in a pivot table
  • Adding fields to a pivot table
  • Changing the layout of a pivot table
  • Grouping pivot table data
  • Understanding calculated fields

 

Data Modeling

  • Data modeling best practices
  • Creating a basic dynamic label
  • Formula-driven visualizations
  • Introducing form controls
  • Using the button control
  • Using the check box control
  • Using option button controls
  • Using the combo box control
  • Using the list box control

 

Data Integration

  • Linking Excel with text files
  • Using get and transform data
  • Perform data transformation
  • Linking Excel with multiple Excel files
  • Append data
  • Merge tables
  • Grouping data
  • Add custom columns

 

Introduction To Learning The Ultimate Tool In Excel: Macros

  • Macro basics
  • Planning a macro
  • Designing your control board
  • Recording macro
  • Testing macro
  • Editing macro
  • Macro workshops
  • Advanced filter with macro
  • Visual Basic for Applications

المواعيد المتاحة

جاكرتا
من 25-11-2024    الى 29-11-2024