MS Excel Full Course

Structure of an MS Excel Workbook Concepts of cell, column, row, worksheet, workbook, and application in MS Excel Changing cell sizes manually and automatically (Format → Row height, Column width, Hide, Unhide, Rename sheet…) Clipboard / Types of Copy-Paste Special Fonts, Alignment, Cells (Insert/Delete Rows, Columns…) Number / Format Cells – Data types and formats Add, Delete, Copy, Move Sheets Calculations in Excel, Mathematical operators Relative and absolute references Naming cells and selected ranges and using them in formulas Clipboard / Types of Paste Special Assigning practical problems, solution checks, group discussions

Sorting and Filtering Tables: Sort/Filter Advanced Filter Find and Replace Hyperlink Conditional Formatting Protection of workbooks, sheets, and cells Entering and editing comments Overview of Page Layout window Overview of Review window Text to Columns Flash Fill Remove Duplicates

Data Validation – Concept and application ConsolidateWhat-If Analysis: Goal Seek Grouping and Ungrouping data reflected in the file Use of Subtotal tool Exploring the View window Charts – Types

Text Functions Concatenate, Proper, Upper, Lower, Right, Left, Mid, Exact, Rept, Text, Trim, T, Code, Unicode, Clean, Char, Unichar, Len, Substitute, Value, Replace, Find Date and Time Functions Datevalue, Now, Edate, Eomonth, Hour, Today, Time, Timevalue, Weekday, Weeknum, Year, Month, Minute, Second, Day, Days, Date, Networkdays, Workday, Yearfrac Assigning practical problems, solution checks, group discussions

Statistical Functions Average, Averagea, Averageif, Averageifs, Count, Counta, Countblank, Countif, Countifs, Max, Maxa, Min, Mina, Median, Mode, Large, Small, Rank Mathematical Functions ABS, Ceiling, Combin, Even, Floor, Int, Mod, Odd, Product, Power, Mround, Round, Roundup, Rounddown, Sum, Sumif, Sumifs, Sumproduct, Subtotal, Roman, Randbetween Assigning practical problems, solution checks, group discussions

Logical Functions If, Iferror, Or, Xor, And, Not Lookup and Reference Functions Address, Indirect, Columns, Column, Lookup, Vlookup, Hlookup, Index, Match, Offset, Rows, Row, Areas, Hyperlink, Formulatext, Choose Information Functions Cell, Error.Type, Info, Iserr, Iserror, Iseven, Isodd, Isblank, Istext, Isformula, Islogical, Isnumber, Sheet, Sheets, Type, N Practical examples of function usage Assigning practical problems, solution checks, group discussions

Smart Tables – Concept, advantages over standard tables, calculations Pivot Tables – Concept, components, advantages, calculations, formulas, and modifications Displaying Subtotals and Grand Totals in Pivot Tables, flexible filtering (2D and 3D), use of Timeline and Slicer Creating Calculated Fields in Pivot Tables

Importing data from other sources (PDF, Excel, CSV, etc.) and their usage The concept and advantages of Power Query Creating a query (Importing from…) Query structure and steps Editing steps Basic operations with rows and columns Assigning practical problems, solution checks

Data types, transformations, and modifications Grouping and summarizing data (Group By) Merging data Appending data Adding columns (Custom Column, Conditional Column) Using most common formulas in calculated columns Assigning practical problems, solution checks

Automating tasks with Power Query Creating and summarizing different reports for automated future reporting Practical examples Assigning problems, verifying solutions

Summary of the provided information Discussion and resolution of problems presented by students Final Exam