Microsoft Excel basic to advanced and Power BI

U1: Basic Excel

• Introduction
• Cell, range, columns and rows, active cell, name box, formula bar
• Save, auto recover, and change language.
• Group defined names.
• Special glue.
• Insert a blank row every two rows.
• Select ranges, delete and add rows or columns, quick copy range.
• Undo, and, redo, delete, and, format copy.
• Working with sheets and windows.
• Keyboard shortcuts, and, autosum.
• Sort and filter group.
• Search and Select Group.
• Data tools group.
• Format cells.
• Shapes, and, images.
• Insert image into or over cell.
• Insert SVG file
• Data Entry, Autofill, Custom Lists.
• Comments.
• Links.
• Relative, absolute, mixed references.
• Conditional formatting.
• Tables.
• Merged cell in a table.
• Print views.
• Graphics.
• Forms.
• Edit links.
• How to determine the calculation mode.
• Copy data from the status bar.
• Percentage calculation.
• Navigation panel.
• Automatic data conversions.
• Show changes option.
• Check accessibility.
• Cell cleaning.
• Preview function value
• Improvements to copy and paste in Excel for the web.
• TRANSLATE and DETECTLANGUAGE.

U2: Intermediate Excel previous steps

• Functions and operators.
• Detailed analysis of a formula.
• Find syntax error.

U3: Artificial Intelligence

• ChatGPT

U4: Logical functions

• Conditional IF, Logical operators, IF.CONJUNTO.
• CHANGE function,
• The IFERROR function,
• IF.ND function,
• NOT function,
• The LET function,

U5: Statistical functions

• COUNT and COUNTA
• COUNTIF, and, COUNTIFS.SET
• MAX, and, MAXA
• MAX.IF.SET
• The AVERAGE and AVERAGEA functions
• The MODE.ONE, MODE.MANY, and MEDIAN functions
• The AVERAGE.LIMITED function
• COUNT.WHITE
• HIERARCHY.EQV
• K.ESIMO.MAYOR
• The TREND function
• PERCENTILE.INC/EXC, RANGE.PERCENTILE.INC/EXC
• QUARTILE.INC, and QUARTILE.EXC functions

U6: Mathematical and trigonometric functions

• SUMIF and SUMIF functions
• SUM, SUBTOTAL, and AGGREGATE functions
• Functions Integer, y, remainder
• The SUMPRODUCT function
• Calculate budget with actual sales
• ROUND, ROUND.EVEN, ROUND.ODD functions
• ROUNDUP and ROUNDDOWN functions

U7: Text and reference functions

• LEFT, RIGHT, UPPER, LOWER, PROPER.
• The LEN function.
• FIND and FINDING
• The EXTRACT function
• The REPLACE function
• The REPLACE function
• Correct errors between two strings
• The CONCATENATE, CONCAT, and CONCATENATE functions
• The SPACES function
• The REPEAT function
• The TEXT function
• EQUAL function
• CODE and CHARACTER functions
• T function

U8: Date and time functions

• Difference between 1900 and 1904 dating system
• The functions TODAY, NOW, YEAR.MONTH, DAY
• DATE.MONTH, and, END.MONTH
• The DATEIF function
• The DAYS function
• The WEEKDAY function
• The DATE function
• The YEARFRAC function
• The DAY.LAB, DAYS.LAB, and DAYS.INTL functions
• The WORKDAY.INTL function
• The ISO.WEEKNUMBER, TIMENUMBER, and VALUE functions
• WEEKNUM Function
• Calculate the difference between hours

U9: Intermediate Excel. Search functions.

• MATCH, and, MATCHX functions
• LOOKUP, VLOOKUP, and HLOOKUP functions
• The XLOOKUP function
• Resolving VALUE error in XLOOKUP function
• The INDEX function
• Sort duplicate values
• The INDIRECT, and, ADDRESS functions
• The OFFSET function
• The HYPERLINK function
• Create a sheet index with HYPERLINK
• The CHOOSE function
• Formula audit

U10: Information functions

• ES Functions
• Function N
• NOD Function
• SHEET function
• SHEETS function

U11: WEB Functions

• IMAGE function

U12: Python in Excel

• Python in Excel

U13: Intermediate Excel. Collaboration tools

• Book protection
• Change control
• What happened to shared books?
• Co-authorship

U14: Cell controls and correspondence

• Checkbox.
• Correspondence in Word with Excel.

U15: Intermediate Excel. Pivot tables

• Part 1
• Part 2
• Part 3
• Part 4. Filters
• Part 5. Group or ungroup dates
• Part 6. Grouping fields
• Recommended pivot tables
• Part 7. Data segmentation and time scale
• Sort row labels, and, column labels
• Part 8. Calculating values
• Part 9. Calculated field
• Part 10. Calculated element
• Part 11. OLAP tools, and IMPORTING DYNAMIC DATA
• Part 12. Linking pivot tables
• Part 13. Consolidating data
• Part 14. Obtaining data from external data
• Part 15. Pivot table from dataset
• Part 16. Example. Displaying report filter pages, and, difference between months
• Example: Show achieved or not achieved
• Sort data segmentation
• Analyze data
• Summarize by week and center
• Show details

U16: Intermediate module examples

• Determine if a year is a leap year.

U17: Functions, Forecasts, Solver, Data Analysis

• BDSuma
• BDExtract / BDMAX / BDMIN
• Forecasts
• Hypothesis analysis
• Solver

U18: Data types

• Quotes and geographic information
• Data Types. Currency
• FIELDVALUE Function
• Create data type in Power Query
• QUOTEHISTORY Function
• Data Types and Images in PivotTables

U19: Advanced module. Financial functions

• The functions PAYMENT, PAYINT, and PAYPRINC
• The PAGOPRINCENTRE and PAGOINTENTRE functions
• The LINEAR AMORTIZATION and PROGRESSIVE AMORTIZATION functions
• The DB, Y, SYD functions
• The COUPON.DAYS function
• The VA, VF, NPER, and RATE functions
• Calculating the depreciation of an asset using DB
• INT_ACUM Function
• The PERCENTILE RANK function
• NPV Function

U20: Office 365 Scripts

• Office 365 Scripts.
• Automate script with Power Automate.

U21: Automate tasks with Power Automate

• Copy folder and add the word Spain_.

U22: Array Functions and Formulas

• Array formulas
• Dynamic Array Formulas vs. Legacy CSE Array Formulas
• Matrix spilled or overflowed
• Precautions, and, limitations
• Overflow error
• Implicit operator @
• Spilled range operator (#)
• Use of the dollar symbol
• The matrix function SORT
• The matrix function ORDERBY
• The UNIQUE function
• The TRANSPOSE function to reverse rows by columns
• The SEQUENCE matrix function
• The FILTER matrix function
• Report tied matches (FILTER)
• The FREQUENCY function
• The matrix function MATRIZALEAT
• The MMULT function
• The TEXTBEFORE, TEXTAFTER, SPLITTEXT functions
• VSTACK and HSTACK functions (VSTACK/HSTACK)
• Repeated consecutive birds (hstack)
• The TOCOL (ENCOL) and TOROW (ENROW) functions
• Countries that improved and those that worsened (ENCOL)
• Fruit counting (ENCOL)
• The WRAPCOLS and WRAPROWS functions
• The TAKE and DROP functions
• The CHOOSEROWS and CHOOSECOLS functions
• Fruits between dates
• The EXPAND function
• WRAPROWS function error

U23: Tasks

• Filter by more than one criterion.
• Replace the last letter "a".
• Fill empty cells with earlier or later dates.
• Fill out invoice with 23s and zeros.
• Split sales from table1 to table2.

U24: LAMBDA Functions

• LAMBDA function
• BYCOL function
• BYROW function
• Examples1. BYROW
• Examples2. BYROW
• Left and right chain operations (BYROW)
• MAP Function
• Examples1 (MAP)
• Examples2 (MAP)
• The REDUCE function
• Examples1 (REDUCE)
• Examples2 (REDUCE)
• Extract special characters (REDUCE)
• The SCAN function
• The MAKEARRAY function
• The ISOMITTED function
• GROUPBY, PIVOTBY, PERCENTAGE

U25: Convert numbers to text

• Convert first character from the right to text
• Convert to text first, and, second character from the right
• Correct errors and test in another book
• Convert third character to text
• Convert the fourth character to text
• Convert the fifth character to text
• Convert the sixth character to text
• Convert the seventh character to text
• Union of all digits

U26: Regular expressions

• REGEXTEST, REGEXEXTRACT, REGEXREPLACE.

U27: Power Query

• Introduction
• Power Query. Part 1
• Option type changed
• Create route dynamically
• Power Query. Part 2
• Power Query. Part 3
• Power Query. Part 4
• Nested IF Example
• Power Query. Part 5
• Power Query. Part 6
• Power Query. Part 7
• Power Query Zone. Generate holidays
• Power Query Zone. Splitting compound names
• Calculation of time difference
• Types of data
• Create tabular model from model with multiple headers
• Upload multiple PDF files from a folder to Excel
• Getting data from an image
• Join multiple tables with Excel.CurrentWorkbook()
• Excel.CurrentWorkbook example continued
• Excel.CurrentWorkbook to choose which tables to load
• Group by to calculate the standard deviation
• Recursive Functions
• M. Language Part 1
• M. Language Part 2
• How to create lists
• #table
• Text Functions
• Time functions
• #date, Date.AddYears, dateTime.LocalNow, Date.From, Duration.Days
• GroupKind.Type
• Text.Upper Function
• Text.Repeat Function
• List.Generate
• List.Select and Each
• List_Dates and Date_Day. Report with dates with and without sales
• ListZip and TextSplit
• List.InsertRange, and, List.Count
• List.TransformMany to create multiplication table
• Change regional settings
• Record.Field Function
• Filter by three criteria with Table.SelectRows
• TableInsertRows, TableRowCount, add blank row between provinces
• Table.FromRecords and Table.AddColumn
• Table.SelectRows, and, List.Contains
• Table.FromColumns put multiplication tables into columns
• Table.RemoveMatchingRows, Table.FromRecords, Table.ToRecords
• Table.Profile. Remove empty columns
• Table.Skip Function
• Table.AddIndexColumn and Table.FromValue
• Table.SplitColumn and Splitter.SplitTextByDelimiter
• Table.SplitColumn, and, Splitter.SplitTextByCharacterTransition
• Table.TransformRows, and, Table.Transformcolumns
• Table.UnpivotOtherColumns, unpivot columns
• Unpivot other columns with null values
• Poorly structured model
• Normalize model
• Create cumulative
• Append tables with different headers
• Find center for each product
• Make a column with the subtraction of the previous cell
• Provinces and centers with a quantity less than or equal to 5
• Search by the initials of a province
• Total by cross date with center
• Convert row model to columns
• Exact match search
• Combine books with different sheet names
• Calculate discount per unit
• Accumulated per day, and, month
• Insert 3 rows in each row of the model
• Filter by province
• Sort by our criteria
• Remove top rows dynamically
• Remove columns starting with Num dynamically
• Append queries using a function
• Calculation of total by employee and group
• Calculation of the total per shopping center
• Filter by three criteria
• Report by centers and equal products
• Attach queries with M
• Create index with duplicates
• Maximum date by province
• Monthly sales by date and province
• Single list case sensitive
• Average minus total across a parameter
• Recover record with province and maximum date
• Products by province
• Dynamically split a column
• Create dynamic index
• Calculate the difference between hours and minutes

U28: Power Pivot for excel 2016 (valid for excel 2019)

• What is Power Pivot.
• Start the Power Pivot add-in.

U29: Main icons in Power Pivot

• Adding tables to the data model
• Power Pivot Menu Icons
• Get started managing your data model in Excel 2016
• Main menus of the data model
• Creating relationships between tables in Power Pivot
• Pivot table from Power Pivot model

U30: Importing data into Power Pivot

• Data sources to build the model
• Getting data from an Access database
• Get data from a .CSV or .TXT file
• Getting data from Excel
• Obtaining data from other sources

U31: Pivot Tables and Charts in Power Pivot for Excel 2026

• Pivot tables and charts
• Advanced Actions in PivotTables
• Possibilities to create multiple dynamic charts
• Segmenting dynamic tables and charts
• Dashboard or control panel with Power Pivot

U32: Processing data in Power Pivot in Excel 2016

• Format model data.
• Different options with existing filters.
• Perform actions on columns.
• Hide tables and columns.

U33: Calculations and formulas in DAX

• AutoSum and Count
• Statistical operations with autosum
• Introduction to DAX formulas
• Calculated or Measured Columns
• Excel Legacy Functions
• Functions used in Excel
• Intermediate formulas in DAX for Excel 2016
• Other DAX functions in Excel 2016

U34: Calculating KPIs in Power Pivot for Excel 2016

• What are KPIs?
• A look at the KPI tool.
• Create KPIs.

U35: Sharing and saving your models

• Save data models in different locations.
• Share your data models with collaborators.
• Protect Excel workbook with data model.

U36: A look at Power View

• Introduction and installation.
• Key actions in Power View.
• Create your first dashboard in Power View.

U37: Power Map

• Power Map

U38: CALCULATE Function

• CALCULATE function.
• CALCULATE and CROSSFILTER.
• CALCULATE, FILTER, ALL, KEEPFILTERS.

U39: Examples

• Example: Create a dynamic slicer
• Report showing dates with and without sales
• Calculate amount and shipping per product
• Calculation of budgeted minus actual sales
• Calculation of sales and advertising costs
• Calculation of total by product and seller
• Accumulated in power Pivot
• Calculation of total by product
• Discount calculation based on quantity
• Ranking of totals
• Calculation of totals without weekends
• Totals by centers and products
• Calculation of discount by units sold
• Total by department and employee
• Maximum minus minimum calculation error
• Total count by date
• Average not counting zero values
• Calculation of the grand total
• Calculation of units and total per product

U40: Macros. Previous steps, environment and first macro

• Introduction
• How to save a file with macros
• Macro recorder
• Our first macro
• Ways to protect a book
• Application Object
• Collections and objects
• Cells and range
• Other properties of Range
• Working with sheets in VBA
• Arrays in VBA
• Example of matrices
• Other FOR loop parameters
• The FOR...EACH loop
• The IF...THEN statement
• Variables
• Thisworkbook
• The INPUTBOX function
• The SELECT CASE statement

U41: Macros. Advanced filter, and reports with advanced filters

• What is an advanced filter?
• Filter Report via a macro with advanced filter.
• Another way to filter with advanced filter.
• Advanced filter with graphics.

U42: Forms

• What is a form?
• Forms. Part 2
• Forms. Part 3
• Listbox
• Forms. Part 4
• ComboBox Control
• Alignment, togglebutton, wordwrap and multiline
• CanUndo, CanRedo, UndoAction, RedoAction
• Accelerator and caption. Assign shortcut to a control
• Picture, loadpicture, left, top, height, width
• The controls collection
• Cut and paste methods
• Dbclick event, CanPaste property, and Paste and Copy methods
• DragBehavior, and EnterFieldBehavior properties
• ShowDropButtonWhen property, and DropDown method
• GetFormat, GetText, SelText, and TextLength
• KeyPress, and, Move events

U43: Economic-financial dashboard (scorecard)

• Introduction
• Definition and context
• KPI concept. Key Performance Indicator
• The map or composition of a dashboard
• Strategy when creating a dashboard. Audience and message
• KPIs and tables needed when creating a dashboard
• Strategy when creating a dashboard. Data source

U44: Dashboard. The most important parts of a dashboard

• The options or configuration sheet
• The auxiliary sheet of a dashboard
• The Data stage. The beginning
• The Analysis stage. The data, once processed
• The Visualization stage to present the information

U45: Dashboard. Building the dashboard_Previous steps

• A walk through our future dashboard, now finished
• Audience and message to convey with the dashboard
• Determining KPIs and chart types.
• Sketch of our dashboard.
• Tables and data required in the dashboard.
• Origin and method of obtaining the data.
• Tips for configuring dashboard sheets.
• The Cover, Auxiliary and Configuration sheets.
• Special controls for the dashboard.

U46: Dashboard. Building the dashboard_Stage 1. Data

• Import data by merging tables. Option 1.
• Import data by relating tables. Option 2.
• Import data with connection to Access. Option 3.
• Import data via clipboard. Option 4.
• Import data with connection to Excel. Option 5.
• Problems with data connections.
• Update connected data.
• Add other necessary columns.

U47: Dashboard. Build the dashboard_Stage 2. Analysis.

• Assumptions before starting to build the dashboard.
• Creating the first pivot tables.
• Replicating pivot tables.
• Filtering pivot tables.
• The averages table. AVERAGE and IFERROR functions.
• The IF function in the table of averages.
• The pre-dashboard sheet. Month titles.
• Other figures needed on the dashboard.
• Speedometer figures on the dashboard.

U48: Dashboard. Building the dashboard_Stage 3. Visualization

• Basic dashboard display areas.
• Create text boxes for numeric KPIs.
• Create the dashboard combo chart.
• Speedometer graphics.
• Create the speedometer charts on the dashboard.
• Create the ring charts.
• Create the income statement.
• Create data segmentation.
• Format the slicer panel.

U49: Dashboard. Building the dashboard. Automation and protection

• Automate dashboard zoom.
• Protecting our dashboard.

U50: Another Alternative Dashboard

• Preview of our new dashboard.
• The Data sheet. The source table of the dashboard.
• Sales table by route and agent.
• Create pivot table for Sales by Agent section.
• Create the ring chart in the dashboard.
• Bar chart for ranking of best clients.
• Special bar chart for best selling products.
• Create a calculated field for sales commissions.
• A splash of color with conditional formatting.
• Create grouping of dates by months and years.
• Data segmentation to filter everything in a coordinated manner.

U51: Excel Tricks

• Introduction.
• 3 tricks to speed up Excel performance.
• 5 tricks from Excel's 'Options'.
• Remove gridlines from a sheet.
• Minimize the size of an Excel file.
• 2 tricks to highlight cells automatically.
• Restore Excel default settings.
• Delete everything in a cell or range of cells.
• Change negative sign from right to left.
• Force phone number format.
• 5 Tricks to improve our graphics.
• Prevent an area chart from plotting zeros.
• How to make an interactive graph.
• Charts within a cell with Repeat.
• 3 tricks for cell editing.
• Drop-down lists in Excel.
• 5 tricks for entering data quickly.
• Comments inside formulas with the N function.
• Insert icons and symbols in Excel.
• Insert an equation in Excel.
• Fix an error made in multiple formulas.
• Leave unique values ​​in a list.
• Filter a table field by more than two criteria.
• Sort a table by more than one field
• Tricks for aligning graphics and shapes.
• Opening Excel workbooks.
• Quick Launch Bar in Excel.
• Tips for selecting ranges quickly.
• 3 Quick Cell Hacks That Save Time
• Debug tables imported from an ERP with a macro.
• Transpose rows to columns or vice versa.
• 5 tricks to protect our information.
• Share a workbook and review changes.
• Detect differences and duplicates in lists.
• Allow VBA to run macros on protected sheets.
• Using CodeName in VBA to avoid inconsistencies.
• Two ways to do cumulative sums.
• See a preview of what will be printed.
• Sum ranges with hidden or error rows.
• Move around Excel more quickly.
• Various tricks for handling text.
• Adjusting columns and rows in Excel.
• Print Excel professionally.
• Filter a table by color.
• Cool tricks to impress the boss.
• Create a new Excel workbook from a sheet.
• Merge cells horizontally.
• Lock objects on row deletion.
• Pivot table with multiple source tables.
• Calculate the quarter corresponding to a date.

U52: Excel Charts

• Introduction.
• Comparison between different data in a graph.
• Evolution and trend.
• Compare what has been achieved with a goal.
• When the important thing is to see the parts of a whole.
• Cause and effect using graphs.
• How to show how fast an increase or decrease occurs.
• Abundance or scarcity in a sample
• Pareto chart
• Charts with multiple categories
• Surface graphics.
• Choropleth charts and sparklines
• The outer area and plot area of ​​a graph.
• Horizontal, vertical and secondary axes of an Excel chart.
• Title, legend, and axis titles of a graph.
• The dividing lines.
• Data labels.
• Series and categories.
• Trend lines.
• Other elements.
• Colors, formats and sizes.
• Pie charts.
• Labels, legends and titles in charts
• Multi-series charts.
• 3D and textures not recommended.
• Clarity in a chart.
• Avoid visible zeros in line graphs.
• Charts with Excel tables.
• A graph in Power Point.
• Simple tricks to learn for an Excel chart.
• Images in column charts.
• Maximums and minimums in a graph.
• Separate slices of pie charts.
• Vertical scale with negative numbers.
• Line graph with multi-colored sections.
• Compare data to the average.
• Increment and decrement symbols.
• Interact with the chart using drop-down lists.
• Compare data in a chart with a margin strip.
• Add dynamic legend to a stacked column chart.
• Horizontal axis two different data.
• When the scale does not start from zero.
• Ring chart.
• People plugin.
• Custom sparklines.
• Line graphs.
• Charts with data segmentation.
• Charts with time scales.
• Gantt charts.
• The Forecast tool.
• Bar graphs with human figure icons.
• Map graphics.
• Speedometer graphic.
• Thermometer Charts. Part 1.
• Thermometer graphs. Part 2.
• Conditional formatting with formulas to highlight alternate rows.
• Conditional formatting for charts.
• PivotChart from a PivotTable.
• Limitations and peculiarities of dynamic graphics.
• Histogram
• How to create a pie sparkline.

Course evaluation

• This course contains a final exam

G-Tools: For Students

• Exclusive access to cutting-edge student tools: improve your employability, participate in exclusive events, take advantage of our intelligent virtual assistant, and more.

⚑ Certificate of completion

• Your personalized digital certificate, a unique badge of your achievements, with international validity, course duration and QR code for instant verification.