GETTING STARTED

• Create an Excel file

• Excel Interface

• Rows, columns and cells

• Save an Excel file

• Open an Excel file

WORK ENVIRONMENT

• Work environment and Excel sheet

• Edit and hide rows and columns

• Delete, duplicate and move an Excel sheet

TEXT FORMATTING

• Create your first words list

• Create your first numbers list

• Select, copy, paste and cut

• Order, filter and remove duplicates

• Find and replace

• Fonts, text alignment and colors

• Exercise #1 – To do List + Solution

FORMULAS

• Structure of elementary Excel formulas

• SUM math function

• UPPER, LOWER and PROPER text functions

• Arithmetical operators and calculation logic

• Basic date and time functions

• Exercise #2 – Name and years + Solution

FUNDAMENTALS

• Header and footer

• Table style formatting

• Excel templates

• Hyperlinks

• Print preview

• Exercise #3 – To do List template + Solution

CELLS FORMATS

• Text and special formats

• Maths formats

• Currency formats

• Date and time formats

• Percentage format

FORMULAS

• Structure of basic Excel formulas

• COUNTA math function

• AVERAGE math function

• ROUNDUP and ROUNDDOWN math functions

• LEN text function

• CONCATENATE text function

• ROW and COLUMN search functions

• Exercise #4 – Text in five cells + Solution

TEXT FORMATTING

• Text in columns

• Conditional formatting

• Format Painter

• Transpose

• Insert comments or notes

• Advanced order

• Advanced filters

• Exercise #5 – To Do List cells text + Solution

CONTENT MANAGEMENT

• Insert picture

• Insert shape and text box

• Smart art, icons and 3D models

• Arrange items

• Insert background

• Name management

FORMULAS

• IF logical function

• IFERROR logical function

• COUNTIF math function

• VLOOKUP and HLOOKUP search functions + XLOOKUP Bonus

• LEFT and RIGHT text functions

• MID text function

• SUBSTITUTE and REPLACE text functions

• DATE, EMONTH and DATEVALUE date and time functions

• Date and time expert functions

• Exercise #6 – Functions Mix + Solution

CHARTS

• Introduction to charts

• Column and bar charts

• Line chart

• Pie chart

• Hierarchical chart

• Histogram

• Bubble chart

• Excel recommended charts

• Modify and move chart

• Exercise #7 – Revenue chart + Solution

FORMULAS

• ADDRESS and INDIRECT search functions

• FIND search function

• SUMIFS math function

• COUNTIFS math function

• MIN, MAX, MINFIS, MAXIFS math functions

• SUBTOTAL: not only a function

• CHOOSE search function

• TEXTJOIN text function

• ISERROR check function

• Exercise #8 – Find your word + Solution

DATA MANAGEMENT

• Introduction to Power Query

• Excel file data importing

• Connection management

• Data updating

• Advanced conditional formatting

• Data validation

CHARTS AND MAPS

• Introduction to advanced charts

• AVERAGEIFS for complete Excel file

• Combined charts

• Sparkline chart

• Maps and geography data

• Exercise #9 – Your map + Solution

PIVOT TABLES

• Introduction to Pivot tables

• Create a pivot table

• Excel recommended pivot tables

• Fields management

• Timeline

• Explore cells contents

• Slicers tool

• Pivot table formatting

• Exercise #10 – People pivot table + Solution

ARRAY

• Introduction to Array operations

• Multi parameter logic MMULT

• Random array and transposition

ARRAY FORMULAS

• Single cell array

• Multi cell array

• SUMPRODUCT math function

• MATCH+INDEX is better than VLOOKUP

• SORTBY search function

• Remove duplicates with array

• Exercise #11 – Store array + Solution

DATA MANAGEMENT

• Introduction to CSV, XML and JSON

• CSV importing data

• XML importing data

• JSON importing data

• Create a Pivot chart with imported data

• Exercise #12 – Warehouse data + Solution

EXCEL MASTER: MACRO

• Introduction to Macro

• Macro work environment

• Macro recording tool

• Exercise #13 – Macro operations + Solution

EXCEL MASTER: VBA START

• Macro to VBA

• Module1 components

• Variables and arithmetic operations

EXCEL MASTER: VBA OBJECTS AND COLLECTIONS

• Object-oriented programming

• Application, Workbook e Worksheet

• Range and Select

• Collections: put "s" to an Object

EXCEL MASTER: VBA PROPERTIES

• Name and Value

• Formula and FormulaR1C1

• Cells and Offset

EXCEL MASTER: VBA METHODS

• Select (not only an object) and Activate

• Add

• Copy, Cut and PasteSpecial

• Close, Delete and Save

EXCEL MASTER: VBA FUNCTIONS

• IF, ELSE and ELSEIF

• FOR

• MsgBox and InputBox

VBA

• Introduction to the VBA Project

• The required variables to start off

• Insertion Inputbox

• Setting the data cycle

• The SUM and AVERAGE Formulas

• The completed Project

MYCORP

• Introduction to the MYCORP project

MYCORP – THE WAREHOUSE

• Products Inventory

• Product-list

• Products OUT

• Products IN

• Products positioning

• From lists to tables

• Warehouse Recap

• Statistical charts

MYCORP – ADMINISTRATION

• Import the Customer File into the Administration File

• Set the payments methods and the shipping

• Set the shipments status

• Set the payments status

• Set the debt collection procedures

• EXTRA: Invoicing

• EXTRA: Import your first electronic invoice in XML

MYCORP – CHARGES

• Set the deadlines

• Set the calculation of a fixed charge

• Set the calculation of a range charge

• Expiry

• Set the extra costs

• Complete the MYCORP project