×

Status message

There is no scheduled class for this course. If you are interested in purchasing this course please click on the "Express Interest" button, fill in your contact information and submit a request for the course.
  • Course Length:
  • 2 Days Instructor Led

Telecom networks are continuing to transform in fundamental ways - cloud platforms are enabling networks to be run as software-based functions. This enables the management of these networks to become software centric and thus requires the use of scripting and software-oriented approaches to automate and manage tasks performed on these networks. Besides the network, all industries are starting to leverage feature-rich tools that analyze massive, varied data sets to complete tasks more productively and effectively. The Data Manipulation Workshop takes one of the most common techniques of storing data – Excel - and shows how to leverage several of its built-in and powerful capabilities to manipulate data. This includes Formulas, Lookups, Pivot Tables, Macros and Visual Basic scripting. This is a hands-on workshop.

Required Equipment
• Students will need a laptop with MS-Excel

This workshop is intended for anyone who wants to build knowledge and skills related to leveraging data tools to be more productive.

After completing this workshop, the student will be able to:
• Use filters to view data that is relevant in a context
• Sort large data sets to identify useful data
• Write conditions to format cells as per the data
• Use pre-defined functions to manipulate data
• Troubleshoot complex formulas
• Append additional information using Lookup functions
• Group and view data using Pivot tables
• Add a chart in a workbook to represent large amount of data
• Insert trendlines into a chart
• Create a Pivot Chart to analyze large amounts of data
• Record and execute macros
• Create a script using Visual Basic for Applications
• Course exercises will use Excel for illustrating concepts and methods

1. Data Manipulation Basics
1.1. Data storage (e.g., Workbook)
1.1.1. Content types
1.1.2. Data validation
Exercise: Assign formats to cells
1.2. Managing large data views
1.2.1. Sorting large data sets
1.2.2. Freeze panes
1.2.3. Filtering data and viewing
1.2.4. Advanced filters
Exercise: Filter and sort data
1.3. Presenting data
1.3.1. Conditional formatting
1.3.2. Charts
Exercise: Conditional format cells

2. Simple Manipulation Techniques
2.1. Using simple functions
2.2. Calculation and formulas
2.3. References, Constants and Operators of a formula
2.4. Manipulating text and numbers
2.5. Logical functions for decisions
2.6. Using date and time functions
2.7. Troubleshooting formulas
Exercise: Use of formulas
2.8. Synthesizing results
2.9. Lookup functions
2.10. HLOOKUP and VLOOKUP
2.11. INDEX and MATCH
Exercise: VLOOKUP Exercise

3. Grouping and Viewing Data
3.1. Rearrange data using Pivot tables
3.2. Data analysis using Pivot tables
3.3. Managing data sources
Exercise: Analysis using Pivot tables
3.4. Charts to represent data
3.5. Create and modify charts
3.6. Parts of a chart
3.7. Common types of charts
3.8. Combine Pivot tables and charts
Exercise: Chart exercises

4. Data manipulation and automation
4.1. Using macros to automate tasks
4.2. Record, create, edit, run macros
4.3. Macro workbook and shortcuts
4.4. Macros and security
Exercise: Record and execute Macro
4.5. Automation basics
4.6. Introduction to Visual Basic for Applications
4.7. Automation Using VBA in Excel
4.8. Review a Visual Basic script
Exercise: Write a Visual Basic script

Suggested Prerequisites

• Working knowledge of Excel