(Click Category to List Courses)

37 - ITC - Information Technology - Miscellaneous


ITC 235 - Microsoft Excel Data Analysis

Code Start Date Duration Venue
ITC 235 18 July 2022 5 Days Istanbul Registration Form Link
ITC 235 05 September 2022 5 Days Istanbul Registration Form Link
ITC 235 31 October 2022 5 Days Istanbul Registration Form Link
ITC 235 26 December 2022 5 Days Istanbul Registration Form Link
Please contact us for fees

 

Course Description

Microsoft Excel is one of the most powerful and popular data analysis desktop application on the market today. By participating in this Microsoft Excel Data Analysis and Dashboard Reporting course you'll gain the widely sought after skills necessary to effectively analyze large sets of data. Once the data has been analyzed, clean and prepared for presentation, you will learn how to present the data in an interactive dashboard report.

Course Objectives

  • Understand and identify the principles of data analysis.
  • Understanding how to summarize and present data clearly
  • Using effective design principles when creating and presenting data.
  • Taking full advantage of the built-in tools Microsoft Excel offers in order to analyze and build dashboard reports.
  • Building interactive dashboard reports.
  • Analyzing Excel data using Excels TEXT based Functions.
  • Understanding Data Mine using Excels Array Functions
  • Building Interactive Dashboard Reports with Buttons and Drop-Down Menus
  • Describing Streamline and Analyze Data with Excel PivotTables and Pivot Chart

Who Should Attend?

  • IT professionals
  • Accountants
  • Managers / Supervisors
  • Project Managers
  • IT Analyst / Business Analyst

Course Details/Schedule

Day 1

  • Analyzing Data Effectively with Microsoft Excel
  • Using Excels PROPER () Function on text
  • Create Consistency with Excel UPPER () Function
  • Microsoft Excels Paste Special Feature
  • Replace Meaningless Data with Excels CHOOSE() Function
  • Extract Date Values Using Excels TEXT() Function

Day 2

  • Building the Excel Dashboard – Look up Data
  • Preparing the Data Using Format As Table
  • Creating a Drop Down Menu with Data Validation
  • Looking Up Data with Excels VLOOKUP() Function
  • Cleaning Up Data with Excels IF() Function
  • INDEX() and MATCH() an Alternative to VLOOKUP()

Day 3

  • Building the Excel Dashboard – Filtering Data
  • Adding the Order History Table
  • Formatting Orders as a Table
  • Using Excels Advanced Filter Feature
  • Record Macro for Advanced Filter
  • Filter Order Records On Change of Customer (VBA)
  • Modify the VBA Filter Code

Day 4

  • Building the Excel Dashboard – PivotTables and Pivot Charts
  • Why Use PivotTables
  • Summarizing Order Info with PivotTables
  • Prepare PivotTable for Customer Filter
  • Creating the VBA Procedure
  • Declaring VBA Variables
  • Assigning Values to VBA Variables
  • Connecting the Filter to the PivotTable
  • Update Pivot Chart Based on Customer Selected (VBA)
  • Customers with No Orders Error

Day 5

  • Building the Excel Dashboard – Final Touches
  • Hiding Extra Worksheets and Columns
  • Cleaning Up the Excel Default Settings
  • Hiding the Chart and Resetting the Slicer (VBA)
  • Protecting the Dashboard
  • Implementation tips