| Introduction |
MS office Versions(similarities and differences), Interface(latest available version), Row and Columns, Keyboard shortcuts for easy navigation, Data Entry(Fill series), Find and Select, Clear Options, Ctrl+Enter, Formatting options(Font,Alignment,Clipboard(copy, paste special)) |
| Referencing, Named ranges,Uses,Arithemetic Functions |
Mathematical calculations with Cell referencing(Absolute,Relative,Mixed) Functions with Name Range, Arithmetic functions(SUM,SUMIF,SUMIFS,COUNT,COUNTA,COUNTIFS,AVERAGE,AVERAGEIFS,MAX,MAXIFS,MIN,MINIFS) |
| Logical functions |
Logical functions:IF,AND,OR,NESTED IFS,NOT,IFERROR Usage of Mathematical and Logical functions nested together |
| Referring data from different tables: Various types of Lookup, Nested IF |
LOOKUP, VLOOKUP, NESTED VLOOKUP, HLOOKUP, INDEX, INDEX WITH MATCH FUNCTION, INDIRECT, OFFSET |
| Advanced functions |
Combination of Arithmatic, Logical, Lookup functions, Data Validation(with Dependent drop down) |
| Date and Text Functions |
Date Functions:DATE,DAY,MONTH,YEAR, YEARFRAC,DATEDIFF,EOMONTH; Text Functions:TEXT,UPPER,LOWER,PROPER,LEFT,RIGHT,SEARCH,FIND,MID,TTC, Flash Fill |
| Data Handling, Data cleaning, Data type identification, Remove Duplicates, Formatting and Filtering |
Number Formatting(with shortcuts), CTRL+T(Converting into an Excel Table), Formatting Table, Remove Duplicate, SORT, Advanced Sort,, FILTER, Advanced Filter |
| Data Visualization: Conditional Formatting, Charts |
Conditional formatting(icon sets/Highlighted colour sets/Data bars/custom formatting), Charts:Bar,Column,Lines,Scatter,Combo,Gantt,Waterfall,pie |
| Data Summarization: Pivot Report and Charts |
Pivot Reports:Insert,Interface,Crosstable Reports; Filter, Pivot Charts, Slicers:Add,Connect to multiple reports and charts Calculated field, Calculated item |
| Data Summarization: Dashboard Creation, Tips and Tricks |
Dashboard:Types,Getting reports and charts together, Use of Slicers. Design and placement: Formatting of Tables,Charts,Sheets,Proper use of Colours and Shapes |
| Connecting to Data: Power Query, Pivot, Power Pivot within Excel |
Power Query: Interface, Tabs; Connecting to data from other excel files, text files, other sources, Data Cleaning, Transforming, Loading Data into Excel Query |
| Connecting to Data: Power Query, Pivot, Power Pivot within Excel |
Using Loaded queries, Merge and Append, Insert Power Pivot, Similarities and Differences in Pivot and Power Pivot reporting. Getting data from databases, workbooks, webpages |
| VBA and Macros |
View Tab, Add Developer Tab, Record Macro:Name, Storage Record Macro to Format table(Absolute Ref),Format table of any size(Relative ref),Play macro by button,shape,as command(in new tab),Editing Macros |
| VBA and Macros |
VBA:Introduction to the basics of working with VBA for Excel: Subs, Ranges, Sheets. Comparing values and conditions, if statements and select cases. Repeat processes with For loops and Do While or Do Until Loops |
| VBA and Macros |
Communicate with the end-user with message boxes and take user input with input boxes, User Form |