(Click Category to List Courses)
37 - ITC - Information Technology - Miscellaneous
ITC 235 - Microsoft Excel Data Analysis
Code | Start Date | Duration | Venue | |
---|---|---|---|---|
ITC 235 | 13 November 2023 | 5 Days | Istanbul | Registration Form Link |
ITC 235 | 04 December 2023 | 5 Days | Istanbul | Registration Form Link |
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