Super Excel

Re-imagine Excel: be at the top of your game!

Objectives

This course will allow trainees to re-imagine Excel by obtaining simple and advanced knowledge. Get ready and upgrade to the latest and most innovative generation of Excel.his course will allow trainees to re-imagine Excel by obtaining simple and advanced knowledge. Get ready and upgrade to the latest and most innovative generation of Excel.

Learn about new tools, concepts and ideas that can be put into practice immediately at work with significant increase in productivity, effectiveness in analysis and problem solving.

We'll cover formulas, charts, tables, data types, Solver, Storytelling best practices and still see automation in Excel Dashboard, Online and Excel Mobile. Depending on the time available, we'll also cover Power functions that allow you to use business intelligence tools in Excel.

This course is aimed at users with some experience in using Excel who want to evolve beyond the basics. It's intensive and uses several practical examples. All files used in the course will be provided, including Excel and PDF files with the presentation slides.

This course is taught in Portuguese.

After nine editions, we have continuously improved the contents of this course in order to maintain the 100% satisfaction rating reported by our trainees. A professional training certificate will be issued on the SIGO platform, in accordance with Ordinance No. 474/2010 of July 8.

  • Who should attend

    Finance professionals, Human Resources. Controllers, Auditors, Consultants and Accountants.

  • Resources

    Trainees must bring their laptop with Excel installed (2010 to 2016 versions in English or Portuguese) and preferably with the Power functions (not obligatory).

    It's not mandatory but it would be beneficial to have an Office 365 account, as we will use this function to demonstrate what's new in Excel.

  • Duration

    16 hours

  • Requirements

    This course has no pre-requisites, however some experience using Excel is advised.

  • OCC Credits

    12 Credits (OCC) - The profesionals that attend this training course and that are certified accountants can submit their training certification issued by Portal Gestão to the Chartered and Certified Accountancy Regulatory Body to be credited the points obtained, depending on the number of hours attended (1.5 credits per hour of training.

Programme Outline

  • Day 1

    Introduction

    • Excel Course Introduction and Objectives
    • Introduction to Excel
    • Formula translator in Excel

    Essential Financial Formulas in Excel and more

    • Sum (SUM)Sum (SUM)
    • SOMA.SE (SUMIF) and SOMA.SE.S (SUMIFS)
    • Simple and compound interest
    • Future value
    • Present value
    • NPER
    • RATE function
    • PPGTO, PGTO, IPGTO
    • VAL and IRR
    • PROCV, PROCH and PROCX
    • Dynamic matrices - SORT
    • Matrix function - EXCLUSIVE
    • Matrix function - FILTER
    • LET function
    • SUBTOTAL and LET function
    • Lambda

    Power Query

    • Import Excel dataImport Excel data
    • Data ETL Process in Excel
    • How to structure files with similar structures
    • ETL techniques with Power Query
    • Data cleansing and transformation techniques with Power
    • Excel Query
    • Dynamic title in Power Query
    • Import data from the Web

    Data Types

    • Custom Data Types
    • Custom Data Types
    • Pour information dynamically into Excel
    • Matrix formulas in Excel
    • Action History
    • Geographic data type
    • My Organization Data Type - Power BI
    • Pivot Tables with Power BI datasets
    • Analyze data in Excel with Power BI Service data
    • CUBE function
    • Tables and PivotTables
    • Formatting pivot tables and creating calculated fields
    • Slicers and timelines
  • Day 2

    Dashboards in Excel

    • Scenario ManagerScenario Manager
    • Scenario manager summary
    • Achieving goals - part 1
    • Achieving Goals - part 2
    • Hypothesis Analysis Data Table
    • Hypothesis Analysis Data Table - Part2
    • Forecast - Moving Average
    • Forecast Sheet - Exponential Smoothing
    • Simple linear regression
    • Linear Regression with Data Analysis
    • SOLVER Supplement
    • Solver - part 2

    Building Reports in Excel

    • Introduction to graphicsIntroduction to graphics
    • Waterfall Chart• Waterfall Chart - Part 2
    • Boxplot
    • Radar Chart
    • Conditional formatting
    • Storytelling Tips
    • Structuring the Layout of a Dashboard in Excel
    • Layout - Titles of charts and cards
    • Insert icons into the dashboard
    • Creating pivot tables
    • Pivot tables result on cards
    • Graphics creation and formatting
    • How to set up a dashboard in Excel
    • Update dashboard with new data

    Excel Online

    • Excel Online Automate and TypeScript
    • Excel Online Automate and TypeScript
    • Automate in Excel online - Save actions
    • Update pivot Table in Excel Online
    • Power Automate in Excel
    • Add-on to Power Automate

Location and Dates:

  • Porto
    • Date: 28th and 31st of january of 2022, from 09h00 to 18h00
    • Address: Rua Engenheiro Ferreira Dias, 161 - Porto 
    • Note: This training is live-streamed
  • Lisboa
    • Date: 24th and  25th of March of 2022, from 09h00 to 18h00
    • Address: TBA
    • Note: This training is live-streamed

The Trainer

Isadora Lupchinski is our Power BI trainer. She is also consultant of business intelligence, where she desevolps implementation projects with out clients. Seh has a degree in Statistics and loves data analysis and result interpretation.

I believe that every person has a book within them, containing a unique value that they can offer to the world - and the book that I have within me, has competencies modelled with a statistically significant p-value – Isadora Lupchinski