LEXICON
Loading Events

« All Events

Data Analysis in Modern Excel

July 5, 2021 @ 8:00 pm 10:00 pm

15000PKR Training Fee | 10% for alumni or member of professional bodies

This hands-on Excel data analysis training will expand your ability to analyze large amounts of data and professionally present your results. Gain the knowledge and skills to build more sophisticated spreadsheets, perform What-If analyses, apply functions, manipulate PivotTables, and use the advanced features of Excel to make and present better business decisions.

Learning Objectives:

  • Leverage features of Microsoft Excel to facilitate business decisions.
  • Perform “what-if” analyses for developing budget and project plans
  • Summarize and analyze large amounts of data using PivotTables and Excel features Automate Excel processes

Course Outline

Analyzing Data with Functions

Referencing and Defined Names for working more effectively with data

  • The topics you will be learning in this module:
  • Cell referencing and naming;(Absolute, Relative and Mix)
  • Creating named ranges;
  • Managing named ranges;
  • Calculations with named ranges;
  • and Automating processes with named ranges.
  • Aggregations with Criteria; (Sumifs, Countifs, Averageifs, Maxifs, Minifs)

Cleaning and manipulating text

  • The topics you will be learning in this module:
  • Functions for Combining Text; (CONCATENATE, CONCAT, AMPERSAND or &)
  • Functions for Splitting Text; (LEFT, RIGHT, MID, LEN)
  • Cleaning Data & Changing Case; (TRIM, CLEAN, CODE, UPPER, LOWER, PROPER) 
  • Removing and replacing unwanted characters. (CHAR, VALUE, SUBSTITUTE, REPLACE)

Working with numbers and dates

  • Converting Data Types; (Formatting and Custom Number Formatting, Date Function, Text Function)
  • Understanding dates and basic date functions; (YEAR, MONTH, DAY, TODAY, NOW)
  • Generating valid dates; (Combining text & Date Function, Using text to column (Pro Tip))
  • Calculating days between two dates; (DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL)
  • and Calculating dates from a given date. (EDATE, EOMONTH, MOD, TRUNC)

 Logical and lookup functions

  • The topics you will be learning in this module:
  • Performing logical operations with IF, And, Or;
  • Applying advanced logical operations;(Nested IF, IFS)
  • Categorizing data with VLOOKUP; (Range LOOKUP)
  • Matching data with VLOOKUP; (VLOOKUP COMBINATIONS, ROW, ROWS, COLUMN, COLUMNS)
  • and Advanced data matching with INDEX and MATCH.

Advanced Formula Techniques

Part 1 Array Formula

  • Introduction to Array Formulas; (Control Shift Enter or CSE)
  • Concepts of lifting & Pairwise lifting, Broadcasting and implicit intersection;

Part 2 Advanced Lookup Functions For Dynamic Reporting

  • This module builds on your array capabilities and explores a range of functions to create dynamic lookup ranges – INDIRECT, ADDRESS, OFFSET and INDEX.

Part 3 Financial Functions and Working with Dates

  • Financial Functions (FV, PV, PMT)
  • Loan Schedule (PMT, EDATE)
  • Net Present Value and Internal Rate of Return (NPV, IRR)

Optimizing Workbook Models with “What-If” Analysis

Planning for contingencies

  • Managing variables in worksheets with Scenarios
  • Comparing and contrasting different data sets with Scenarios reports

Quantifying variables in a workbook model

  • Determining the magnitude of a variable with Goal Seek to achieve an end value
  • Calculating the optimum variable values in a worksheet model with Solver
  • Access the sensitivity of variables with Data Tables

Summarizing Business Information

Organizing workbooks and links

  • Managing external links

Consolidating ranges

  • Building 3D formulas to analyze worksheet data
  • Summarizing multiple sources of Excel information into one worksheet (Consolidation Feature)

Formulating Decisions from Database Information

Tables for automating data manipulation

  • Creating, naming and removing tables
  • Formatting and selecting in tables
  • Sorting and filtering tables
  • Performing calculations with structured references
  • Automating processes with tables.

Pivot Tables, Charts and Slicers

Interpreting and refining data with PivotTables

  • Defining data summaries interactively
  • Summarizing data sets with grouping and aggregation
  • Comparing related totals dynamically
  • Filtering details with Report Filters and Slicers

Visualizing and exploring PivotTable reports

  • Presenting PivotTable reports effectively with PivotCharts
  • Examining data patterns with Sparklines
  • Analyzing multiple tables of data with Power Pivots
  • Discovering and presenting information with Power View

Data Visualizations

Data Visualization using Conditional Formatting, Sparklines and Number Formats

  • This module we will explore a host of data visualization tools that do not include charts. We will learn about conditional formatting, including custom conditional formatting using formulas, drop down lists, and macros. We will then move on to learning about sparklines, shapes, and custom number formats.

Mastering charting techniques

  • We will look at standard charts such as line charts, pie charts, and scatter charts – to help you visualise your data.
  • We will also look at charts to add to your standard toolkit such as area charts, donut charts, and bubble charts – which will add that wow factor to your work.

Specialized charts

  • Hierarchy charts, waterfall charts, funnel charts, and geospatial charts.
  • We will also look at customising and creating our charts when we look at population charts and gauge charts – giving you ideas to be innovative and creative.

Enhancing Excel Usage with Macros

Automating repetitive tasks with Macros

  • Simplifying complex tasks and reducing errors
  • Bulletproofing routine editing and formatting
  • Invoking macros with Form controls

Meet Our Trainer

Abdul Haseeb, ACA

He is a target-driven, result-oriented, qualified Chartered Accountant and Financial Management Professional currently working as Chief Financial Office (CFO) at Volka Foods International. He has Excellent Expertise of 10+ Years as Finance Manager, Finance Business Partner and Manager Audit & Assurance. Impeccable relevant experience of Strategic Planning, Financial Decision Making, Financial Management, Treasury Management, Financial Analysis, Financial Statements & Financial Reporting, Financial Planning, Auditing and Budgeting & Forecasting, & Taxation. Excellent Expertise in Financial Projects Management, Planning, Development & Implementation.

Trained by one of big 4 CA firms Deloitte Yousaf Adil, Chartered Accountants, he is a Microsoft Certified Excel & Word 2019/365 Expert by Microsoft and having expertise in

  • Microsoft Excel (Beginner to Advanced), Macros-VBA
  • Data Science and Data Analysis,
  • Data Visualization,
  • Machine Learning, Tableau & Python
ScheduleDeliverables
• Starting from July 12, 2021
• Days: Monday to Thursday
• Timing: 8:00pm to 10:00pm (Pakistan Time, GMT+5)
• Duration: 2 weeks
• Live interactive Training on ZOOM
• Comprehensive practice material
• In case you missed any session we will be providing recording
• E-Certificate
• 16 CPD Hours

Leave a Reply

Your email address will not be published. Required fields are marked *

Chat Now
Welcome to LEXICON Chat!
Welcome to LEXICON
Let's connect on WhatsApp to help you further.