Microsoft Excel: Beginners+Intermediate+Advanced plus (Udemy.com)

With the proven Progressive Practical Learning (PPL) methodology aiming to provide you with the mastery of Excel. Prepared for Learning On Go and Learning On Demand.

Beginners cycle comprises almost 4 sections: Excel Foundations where you will learn about notions regarding the spreadsheet and the heart of Excel, formulas. Then follows Basic Skills which is essential for proficient use of Excel, since these are the central elements of Excel. More Basic Skills section approaches other matters for you to acquire a sound base for the rest of the course, for learning Excel in depth. Formulas in Depth partially can be considered as part of the beginners cycle.

Formulas in Depth with a dedicated section, as the name suggests it’s for you to dominate the heart of Excel: formulas and all related notions and features, such as syntax, formulas replication, relative and absolute referencing, formula auditing tools, and defining names for the cells or range of cells to be used in formulas instead of cell references. Also, a fifth operation is introduced: concatenation, for joining text. The power and square root operations are left to the functions section.

Advanced Formatting, from basic to advanced formatting features, excel offers a wide set of predefined options especially regarding the numeric values and also full customization of numbers, dates, and time through codification which you are going to learn in detail, besides the usual formatting of the font, horizontal and vertical alignment, bordering tables and coloring the background, amongst others. Advanced formatting features, such as cells and tables styles, and conditional formatting deserve a full approach. This matter comprises 4 sections.

Worksheets are the stage where the spreadsheets are developed, so understanding their basics and manipulation is a must together with the interconnecting spreadsheets across several worksheets through cell referencing, including the placement of a range of worksheets in a formula for operating the same range of cells. Printing worksheets can be customized and include headers and footers and other aspects. Protecting worksheets and their structure is important for collaborative work. For a series of worksheets, representing each one a segment of an organization, the consolidation in a single one can be done with the Worksheets Consolidation tool.

Functions make Excel even more powerful since they embody calculations that otherwise would take several steps, some of them involving a complex process, but with this feature, you only need to provide the arguments and Excel delivers the result. You’ll learn its syntax and the relevant functions from categories such as Statistical, Math, Text, Logical, Conditional, Financial, Date, and Time. Lookup & Reference functions are approached under the Database section.

Charts are minded for providing a visual perspective of numeric values and Excel offers a vast catalog of types, sub-types, and variants covering all needs. Besides the predefined configuration, they can be customized in every single detail, through deep formatting tools. These matters are demonstrated through various types of charts such as Column, Bar, Line, Area, Pie, and Doughnut.

Templates constitute a feature for saving models of spreadsheets you need often for instancing them and just for introducing elementary data. You’ll learn how to create a model and manage it and also, use pre-packed templates that with some adjustments can solve your requirements, without the need to reinvent the wheel.

Tables and Database: When a list of data is formally defined as such, extra features are added. Besides this aspect, tables can be sorted and filtered, columns and rows grouping can be done with automatic summarizing functions.

For assuring the consistency of the data there is the feature Data Validation and since Excel is more and more a complement for other applications Importing and Exporting data is taught.

There are specific functions for searching data, namely VLookUp, HLookup, Index, and Match. Two new functions are also approached: XLookup and XMatch. There are also functions for dynamic numeric calculations, such as
Subtotal - with the option of ignoring hidden cells.

DSUM, DAVERAGE, and DCOUNT with dynamic criteria.

A Pivot Table aggregates data from a larger table and produces summary information. Besides the creation and recreation of a Pivot Table, other matters are approached such as redefining the settings of a field, creating calculated fields and items, grouping columns and rows, filtering features, and inserting charts.

Power Pivot it’s an add-in that brings to Excel the features of a database and one of the most important is to connect several tables through relationships and transform separate tables in a unified Data Model, and then create Pivot Tables out of it. Under the Power Pivot, extra fields based on formulas can be created and the Key Performance Indicators can be analyzed.

What-If-Analysis is for reaching an optimum value, directly or indirectly and all the four tools are approached.

Goal Seek, for finding out the value of a variable for reaching a goal.

Scenarios Manager, for storing and retrieving, as the name suggests, scenarios.

Data Table, for producing the results of the combination of the possible values of two variables.

Solver, for finding an optimal solution for a problem with several variables and constraints.

Macros it’s a tool for recording repetitive tasks for executing them through a button on the ribbon or a shortcut key. Excel transforms the recorded tasks in VBA code and, you’ll get acquainted with and do some editing for improving the macro by adding an InputBox and a Message Box. Planning is exemplified with a script and at placing Buttons in the Ribbon you’ll learn how to reconfigure the tabs of the Ribbon.

Graphical Objects, such as Pictures, Shapes, Icons, and SmartArt, can be inserted into the worksheets and restyled. SmartArt is a compound-type object which includes text features, so you can highlight some information, enriching your spreadsheets by making them more communicative and appealing. The content of a shape can be dynamic, in other words, it can be linked to a cell, a feature to be demonstrated through a SmartArt.