a. PowerQuery to connect directly to over 100 data sources (including big data DB and data warehouse)

b. PowerPivot to analyse millions of rows of data fast and with a vertipaq compression engine that keeps the files small

c. Data Models in Excel

i. Create relationships between different datasets

ii. Enable automated report generation

d. Formula optimization for large data

a. Automatic consolidation of data in different files using combine & load

b. Automatic consolidation of data in different sheets even when the rows/data can grow

c. Setting up recurrent reports in ways that minimize manual work

d. Maximizing data linking and formulas to achieve scalable and agile reports

a. Split Columns (advancement of Text-to-Columns)

b. Unpivot (a relatively new powerful tool)

c. Group data

d. Transpose tables

e. Cleaning out error values

f. Replacing error values

g. Auditing

h. Inserting steps (without using undo)

i. Other data transformation steps

• a. Understanding what dashboards are and when to create one

• b. Different ways of creating dashboards

• c. Pivot Table and Pivot Chart approach

• d. Formulas and charts approach

• e. Using non-native Excel charts

a. Column chart

b. Line chart

c. Pie chart

d. Bar chart

e. Area chart

f. XY Scatter chart

g. Stock chart

h. Surface chart

i. Doughnut chart

j. Bubble chart

k. Radar chart

l. Treemap chart

m. Sunburst chart

n. Histogram chart

o. Box and Whisker chart

p. Filled map chart

q. Waterfall chart

r. Funnel chart

a. Lookup functions – VLOOKUP, LOOKUP, HLOOKUP, XLOOKUP and others

b. IF, IFS, IFERROR, SWITCH, AND, OR, ISBLANK, and others in the same family

c. INDEX, SUMPRODUCT, RANK

d. WORKDAY, NETWORKDAYS, DATEDIF, EOMONTH and other special date formulas

e. TEXTJOIN, CONCAT, FIND, SEARCH, MID, LEFT, RIGHT and other text formulas

a. Goal Seek

b. Scenario Manager

c. Data Table

d. Solver

• a. R (using BERT)

• b. Python (using xlwings)

• c. Others

a. Recording macro

b. Coding from Scratch

i. Userforms

ii. Modules

iii. User Defined Functions