Course NT-Excel 365 (s) Microsoft Excel 365, intensive | nt.ua

(044) 390 73 35 (050) 352 68 64

EN RU UA

Course NT-Excel 365 (s) Microsoft Excel 365, intensive

AnalyticsBusiness trainingUsersBIExcelMS 365MS OfficeOffice 365MicrosoftAuthor's

In the course, you can deeply learn the basic tools of Excel for effective data analysis and modeling. The program is designed for a high pace of work and for "strong" listeners.

The course focuses on the use of modern Excel functions for calculations, data analysis, working with data in various formats/tables, transformation, calculation; combining different tables.

The following Excel tools are considered in the course:

  • Excel - more complex calculations - secrets of various functions, new functions;
  • Power Query - features of working with data downloaded from various regional standards; import and merge of data from external sources, their transformation;
  • Power Pivot – working with large volumes of data, creating a data model, features of comparing data with past periods;

After completing this course, students will be able to:

  • Perform various calculations in Excel;
  • Use the inquire add-on;
  • Use mathematical, statistical, date and time functions, logical functions;
  • Effectively work with functions, connections;
  • Make calculations using Quick Analysis;
  • Work with different types of data;
  • Quickly enter data into databases and tables, sort and filter them;
  • Consolidate specific data located on different sheets;
  • Create dynamic lists with standard summary tables and charts;
  • Use Power Query for: importing, combining files, tables and queries, grouping data, creating summary statistics, transforming information received from various sources for further analysis in an Excel model;
  • Use Power Pivot to: create pivot tables in a data model in Power Pivot, create pivot charts, dashboards, create data analysis expressions (DAX), use key performance indicators for data analysis.

Audience Profile

The course is designed for trained students who want to use the capabilities of Microsoft Excel tools to work with business analytics. For specialists who have attended Microsoft Excel courses and wish to deepen their knowledge of the additional data analysis module.

Before attending this course, students must have:

  • knowledge of Excel 2016 (I) course or equivalent knowledge.
  1. Basic concepts
    • Using the "autocomplete" feature for numeric data, combination of text and number, dates.
    • Autocomplete Lists.
    • Cell content and format.
    • Clearing everything that can be in the cells.
    • Using absolute and mixed references to cells and ranges when writing formulas.
    • Achieving the accuracy of calculations.
    • Using the inquire add-on to clean up formatting outside of tables.
  2. Calculations in Excel
    • The use of mathematical, statistical, date and time functions, logical functions in formulas ROUND, TODAY, IF, SUMIF, COUNTIF, VLOOKUP, HLOOKUP.
    • Nested functions.
    • Copying and moving formulas.
    • Formulas that refer to cells in other worksheets and other workbooks.
    • Work with connections. Features of using some functions when working with links.
    • IFS, multi-field comparison - IF and AND functions.
    • Replacing the use of multiple IFor IFS functions by VLOOKUP, HLOOKUP.
    • Finding and substituting data using a combination of the INDEX and MATCH functions.
    • Conditional sums and calculation based on several criteria SUMIFS, COUNTIFS.
    • Eliminate an error in a formula.
    • Capabilities (Flash Fill).
    • Secrets of the SUMPRODUCT function. Calculations using instant analysis (Quick analysis).
  3. Databases and tables.
    • Ways to quickly enter data into databases and tables.
    • Sorting and filtering.
    • Features of using filters for fields of various data types.
    • Calculations on filtered data.
    • Creating Tables.
    • Working with row totals tables.
    • Using formulas in tables.
    • Subtotals are simple and nested. Adding a group and structural criterion in ranges. Features of copying filtered and structured data.
  4. Consolidation of specific data located on different sheets
    • 3D references. Tool use.
    • Consolidation without connection and with connections.
  5. Create dynamic lists with standard pivot tables and charts (data on 1 sheet)
    • Create a PivotTable report based on worksheet data.
    • Layout, formatting and modification of pivot tables.
    • Working with a list of fields in a pivot table.
    • Subtotal and grand total fields in a PivotTable report. Show or hide grand totals for the entire report.
    • Grouping items in a PivotTable report (manual and automatic).
    • Additional calculations in pivot tables for quick analysis (percentage ratios, running totals, "parent" ratios).
    • Create, edit, and delete a PivotTable formula (calculated fields and calculated objects).
    • Using sections to Filter PivotTable Data.
    • Connecting Slicers to Multiple PivotTables.
    • Links to pivot table cells. GetPivotDate function .
    • Custom formats in pivot tables;
    • Features of obtaining several pivot tables based on one source.
    • Rules for copying the results of pivot tables;
    • Features of creating "unrelated" pivot tables.
    • Features of building summary charts.
  6. Using Power Query
    • Data Import Basics
      • Import files of various formats into Excel using Power Query;
    • Import all files from a folder
      • Combining files. Adding new files
    • Excel sheets in Power Query
      • Merging tables and ranges in the current file.
      • Combining Ranges and Sheets
      • Aggregating data from other workbooks
    • Reverse pivot table in Power Query
      • Transposing and tricky cases of reverse parsing pivot tables in Power Query
    • Join tables and Power Query queries
      • Query types (combine, append).
      • Types of relationships between tables in queries and their use.
      • Using "connection only" queries.
      • Loading query results into an Excel worksheet and into a PowerPivot model.
    • Grouping and Summarizing in Power Query
      • Grouping data.
      • Creation of summary statistics.
    • Power formulas Query
      • Create custom columns.
      • Conditional logic in Power Query.
      • Data Type Conversion Functions
      • Transformation of information obtained from various sources for further analysis in an Excel model .
  7. Using Power Pivot
    • Pivot Table Calculation Mechanism (Data Object Model)
      • Connecting various Excel tables to the model.
      • Create relationships and build a pivot table from multiple tables in Excel.
    • Extend Data Model Relationships with Excel, Power Pivot, and DAX
      • Add a link using Chart View in Power Pivot;
      • Extending the data model using calculated columns;
      • Create calculated columns;
      • Create a relationship using calculated columns;
      • Creating a hierarchy; Using Hierarchies in PivotTables
    • Create a PivotTable from PowerPivot Data
      • Create a PivotTable report from data from multiple tables and different data sources.
    • Create a PivotChart from PowerPivot data
      • Adding a PivotChart to an Analysis.
      • Creation of dashboards on the data of several tables.
    • Adding Slicers to PivotTables and PivotCharts
      • Slice formatting;
      • Using slicers to analyze PivotTable and PivotChart data.
      • Connecting Slicers to Different PivotTables and PivotCharts
    • Data Analysis Expressions (DAX) in PowerPivot
      • Basic information about DAX formulas;
      • DAX Syntax Specification for PowerPivot (DAX);
      • Syntax requirements;
      • Naming requirements;
      • Functions in DAX; DAX operators and constants;
      • Data types in DAX.
      • Data analysis using calculated fields (measures).
      • What is the date table for?
      • Time logic DAX functions (day, month, quarter, year) - comparison of data from previous periods.
    • Using KPIs to Analyze PowerPivot Data
      • Create a measure and a KPI.
      • Analysis of the implementation of plans.
      • Analysis of indicators in comparison with previous periods.

Sign up for the closest date

Course Code

NT-Excel 365 (s)

Length, days (hours)

4 (32)

Closest dates

on request

Price, UAH

Get
Logo EC Networking Tehnologies
(044) 390 73 35
(050) 352 68 64
Facebook Instagram YouTube LinkedIn Telegram

Copyright © 2024 Networking Technologies