Nigerian Seminars and Trainings

Search all upcoming seminars, conferences, short management courses and training in Nigeria and around the World

business logo

In-depth Microsoft Excel And Business Data Analysis Training

By: Ciel Consulting

Rivers State, Nigeria

01 - 05 Jul, 2019  5 days

Follow Event

  

NGN 40,000

In-depth Microsoft Excel and Business Data Analysis

As you participate in each of the 4 courses you will master Excel tools that will clear away the pain of stumbling through your daily tasks. You will start with the basics, building a solid foundation that will give you further knowledge as you progress into intermediate and advanced level topics.

What Will I Learn?

  • Learn how to automate daily routine tasks within Excel
  • Learn best practices when creating macros and streamline your VBA code

Determine when it’s best to use the Macro Recorder versus VBA

  • Learn how to write custom VBA Macros to create more robust automation
  • Learn VBA programming concepts to create dynamic, re-usable code
  • Complete real-world Macro/VBA projects from beginning to end

At completion of this course you will have mastered the most popular Excel tools and come out with confidence to complete any Excel tasks with efficiency and grace. Below are just a few of the topics that you will master:

  • Creating effective spreadsheets
  • Managing large sets of data
  • Mastering the use of some of Excel’s most popular and highly sought-after functions (SUM, VLOOKUP, IF, AVERAGE, INDEX/MATCH and many more…)
  • Create dynamic report with Excel PivotTables
  • Unlock the power and versatility of Microsoft Excel’s Adin, PowerPivot
  • Audit Excel Worksheet formulas to ensure clean formulas
  • Automate your day to day Excel tasks by mastering the power of Macros and VBA

The training outline is:

Data Manipulation in Excel

  • How Excel handles different data types
  • Data consistency, starting with the end in view
  • Building Datasheets that can easily scale
  • Sorting

Cascaded sorting

  • Sorting across rows (left to right sorting, not the usual up to down sorting)
  • Sorting and Conditional Formatting to identify trends
  • Filtering
  • Data cleaning

Removing duplicates

  • Text-to-column
  • Grouping
  • Data Validation
  • Conditional Formatting
  • Data formatting

Using Tables (and when to convert to tables)

  • ii. Formatting for printing
  • iii. Formatting for email
  • iv. Data Review and formatting for 3rd party use
  • Named Ranges

Charts

Chart types

  • Line chart and when to use it
  • ii. Column chart and when to use it
  • iii. Bar chart and when to use it
  • iv. Pie chart and it’s dangers
  • Combining charts; when and how.
  • Dynamic Charts, using filter.
  • Best practices when making charts
  • Sparklines
  • Power Map and Power View (Excel 2013 and Excel 2016 only)

Pivot Table, Pivot Chart and PowerPivot

Pivot Table

  • Default Pivot Table
  • ii. Tabular Pivot Table
  • iii. Pivot Table Filtering
  • iv. Making a very dynamic regular table from Pivot Table
  • v. Calculations and Formula use with Pivot Table
  • vi. Advanced Pivot Table tricks
  • Pivot Chart
  • Pivot Chart and its limitations
  • ii. Dynamic Pivot Charts

PowerPivot (for Excel 2010, 2013 and 2016 only)

Power BI for dynamic dashboard and analysing millions of rows of data

Business Data Analysis

  • Linking sheets
  • Duplicating sheets (better than copy and paste)
  • Inserting sheets, labelling and coloring the professional way
  • Freezing Panes and splitting windows
  • Conditional formatting

To identify patterns

  • ii. Using formulae
  • iii. To make extremely intelligent reports
  • Lookup functions
  • VLOOKUP
  • ii. Hlookup
  • iii. Looking up the last data or pattern in a row or column
  • iv. Overcoming the limitations of Vlookup and Hlookup using index and match functions
  • Power Functions
  • IF, IFERROR, AND, OR, ISBLANK, and others in the same family
  • ii. TEXT manipulative functions to make a completely automated Dashboard
  • iii. COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables
  • iv. MATCH and INDEX to do the impossible

Other Functions

Math Functions

  • ii. Text Functions
  • iii. Logical
  • iv. Others
  • Formula Auditing
  • Goal Seek, Scenario Manager and Solver
  • Excel Web Query
  • Most useful Excel keyboard shortcuts

Executive Dashboards and Reporting

  • Best Practices
  • Executive Dashboards
  • Executive Dashboards
  • ii. Dynamic Reports
  • iii. Determining the KPIs and tracking them
  • iv. Strategic Insights and Analysis
  • Data Visualization
  • Having the audience/recipient in mind
  • E-mails and Excel reports

Excel to PowerPoint and Word

  • Linking PowerPoint/Word Charts to Excel
  • Embedding Excel sheets in PowerPoint/Word
  • Making a Powerful PowerPoint Presentation

Excel VBA

  • Recording Excel macros
  • Introduction to editing Macro codes
21 Oilfield Avenue, off School Road, Elelenwo, Port Harcourt Jul 01 - 05 Jul, 2019
NGN 40,000.00
(Convert Currency)

Victor 07039147221

Data Analysis Facilitators
NST PPC Advert