1. In-depth Excel Training, Executive Dashboards & Business Data Analysis
This training is aimed at making you extremely good in Microsoft Excel, dashboard making and business data analysis, teaching you with live business scenarios. It’s intended for Sales Managers, Financial Analysts, Business Analysts, Data Analysts, MIS Analysts, HR Executives and power Excel users.
You will get lunch, a branded DVD with over 20 training videos and practice files, training notepad with pen, a comprehensive training reference material, a training certificate from us (a registered Microsoft Partner) and after training continuous learning access.
The training will be facilitated by a team lead by a Microsoft recognized Excel Expert and Microsoft Certified Trainer who is a Microsoft MVP alumnus. We have had participants of our trainings from Guinness Nigeria, Nigerian Breweries, Schlumberger, Chevron, Periscope, Brains and Hammers, GfK, Palladium Group, First Bank, Citi Bank, Dalberg, PMI, NLNG, GSK, Promasidor, SaveTheChildren, Mobil, FMDQ, NSE, Biofem Pharmaceuticals, Total, Vodacom, Nestle, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), SABMiller, IBM, Airtel, Diamond Bank, ECOWAS, Ministry of Finance, Transmission Company of Nigeria, Nokia Siemens Networks and DDB.
Reach Michael on 0700ANALYTICS, 01-631-1885, 0808-938-2423, 0806-312-5227 and mike@urbizedge.com or Hannah on 0802-118-0874 and hannah@urbizedge.com or Emmanuel on 0908-482-5064 and emmanuel@urbizedge.com or Hope on 0808-266-9002 and hope@urbizedge.com to register.
Date: Thursday 19th November 2020 to Saturday 21st November 2020
Time: 9:00am to 5:00pm daily with tea break and lunch break
Venue: UrBizEdge Limited, 70b Olorunlogbon street, after Banex Hotel, Anthony, Lagos.
2. Big Data Analysis, Power Query, Power Pivot and VBA in Excel
The training is aimed at already proficient Excel users who work in report/analysis heavy roles dealing with large amount of raw data (even spanning 2 to 200 million rows) and have Excel 2016 or above.
If you qualify for this, email team@urbizedge.com with details of which industry you work, your role and your most advanced use of Excel. Optionally, we will reply with the details and schedule for the class. We do this because from experience too many people misjudge their needs — going for what sounds fancy and new rather than what would really give them the practically useful skill they need. Thanks for understanding and complying.
1. In-depth Excel Training, Executive Dashboards & Business Data Analysis
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)
- Formatting for printing
- Formatting for email
- Data Review and formatting for 3rd party use
- Named Ranges
- Charts
- Chart types
- Line chart and when to use it
- Column chart and when to use it
- Bar chart and when to use it
- 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
- Tabular Pivot Table
- Pivot Table Filtering
- Making a very dynamic regular table from Pivot Table
- Calculations and Formula use with Pivot Table
- Advanced Pivot Table tricks
- Pivot Chart
- Pivot Chart and its limitations
- 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, labeling and coloring the professional way
- Freezing Panes and splitting windows
- Conditional formatting
- To identify patterns
- Using formulae
- To make extremely intelligent reports
- Lookup functions
- Vlookup
- Hlookup
- Looking up the last data or pattern in a particular row or column
- 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
- TEXT manipulative functions to make a completely automated Dashboard
- COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables
- MATCH and INDEX to do the impossible
- Other Functions
- Math Functions
- Text Functions
- Logical
- 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
- Dynamic Reports
- Determining the KPIs and tracking them
- Strategic Insights & 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
2. Big Data Analysis, Power Query, Power Pivot and VBA in Excel
1. Big Data Tools in Excel
a. PowerQuery to connect directly to over 100 data sources (including big data DB and data warehouse)
b. PowerPivot to analyse millions of rows of data fast and with a vertipaq compression engine that keeps the files small
c. Data Models in Excel
i. Create relationships between different datasets
ii. Enable automated report generation
d. Formula optimization for large data
2. Report Automation in Excel
a. Automatic consolidation of data in different files using combine & load
b. Automatic consolidation of data in different sheets even when the rows/data can grow
c. Setting up recurrent reports in ways that minimize manual work
d. Maximizing data linking and formulas to achieve scalable and agile reports
3. Advanced Data Shaping and Transformation a. Split Columns (advancement of Text-to-Columns)
b. Unpivot (a relatively new powerful tool)
c. Group data
d. Transpose tables
e. Cleaning out error values
f. Replacing error values
g. Auditing
h. Inserting steps (without using undo)
i. Other data transformation steps
4. Creating Dashboards in Excel
a. Understanding what dashboards are and when to create one
b. Different ways of creating dashboards
c. Pivot Table and Pivot Chart approach
d. Formulas and charts approach
e. Using non-native Excel charts
5. Charts
a. Column chart
b. Line chart
c. Pie chart
d. Bar chart
e. Area chart
f. XY Scatter chart
g. Stock chart
h. Surface chart
i. Doughnut chart
j. Bubble chart
k. Radar chart
l. Treemap chart
m. Sunburst chart
n. Histogram chart
o. Box and Whisker chart
p. Filled map chart
q. Waterfall chart
r. Funnel chart
6. Advanced Formulas
a. Lookup functions – VLOOKUP, LOOKUP, HLOOKUP, XLOOKUP and others
b. IF, IFS, IFERROR, SWITCH, AND, OR, ISBLANK, and others in the same family
c. INDEX, SUMPRODUCT, RANK
d. WORKDAY, NETWORKDAYS, DATEDIF, EOMONTH and other special date formulas
e. TEXTJOIN, CONCAT, FIND, SEARCH, MID, LEFT, RIGHT and other text formulas
7. Planning and Strategy Tools in Excel
a. Goal Seek
b. Scenario Manager
c. Data Table
d. Solver
8. Integrating Excel with Data Science tools
a. R (using BERT)
b. Python (using xlwings)
c. Others
9. Excel VBA
a. Recording macro
b. Coding from Scratch
i. Userforms
ii. Modules
iii. User Defined Functions