Introduction
This course is designed for individuals with basic proficiency in Excel, aiming to enhance their skills in utilizing all the key features and tools available within the program. The curriculum employs a Problem-Based Learning (PBL) methodology, where each section includes an introduction followed by real-world problem-solving exercises. This approach encourages active participation and hands-on learning.
If you possess a foundational understanding and some practical experience with Excel, and seek to advance your capabilities further, this course will be highly beneficial for you.
Course Content
Ribbon interface – Search Box* (Alt + Q in Microsoft 365) – Quick Access toolbar – Common keyboard shortcuts (Printable key card) – Text and Numeric Data in Excel – Understanding Date and Time in Excel (Epoch 1Jan1900 / 24:00 = 1) – Working with Workbooks and Sheets.
Cell Reference – Relative and Absolute Referencing – Components of an Excel Function – Nested Functions – Order of evaluation - Common Functions (SUM, COUNT, MIN, MAX, AVERAGE) – Using AutoSum (Alt + =) and AutoFill – Understanding errors in Formulas.
Fonts, Font and Background Colours, Borders - Subtle vs loud formatting – Data Formats (Number, Currency, Percentage, Date, Time, Text, Custom Formats) – Ctrl + 1 shortcut – Format Painter – Merge and Centre – Conditional Formatting Rules – Using Data Bars and Icon Sets in Conditional Formatting – Custom Styles.
Creating a proper list in Excel – Avoid Blank Rows and Columns / Merged Cells – Named Ranges and scope – Sorting and Filtering – Multi level sort – Grouping and Un-grouping – Subtotals – Finding and removing duplicates – Data validation and restricting entry.
What is an Excel Table? – Formatting Tables – Column Names - Table Names – Structured References in Tables – Syntax and Usage – What do @ and # mean? – Dynamic Ranges – Converting ranges to tables and vice-versa.
Chart Types – Create a column chart – Create a Line chart – Create a Pie chart – Changing chart data – Using tables as source data – Formatting Charts – Moving Charts to another sheet – Using Sparklines (charts in a single cell).
Print Preview – Paper Size – Margins, Orientation and Scaling – Horizontal and Vertical Centre – Headers and Footers – Print titles (top rows / left columns) – Print Areas.
Pivot Table Basics – Layout and Design – Report (Page) Filters – Pivot Table Options – Pivot Field Options (percent of row total, percent of parent, rank, etc) – Slicers – Timelines.
Get data from external sources (Excel files, Text and HTML files, PDF Files, Databases) – The Power Query Editor – Basic Transformations – Data Types - Applied steps and M Code – Loading Destination – Refreshing Data – Managing Errors.
Common functions and their usage with examples
- Logical: IF, IFS, OR, AND, NOT, IFERROR, LET*
- Math: ROUND, ROUNDUP, ROUNDDOWN, SUMIF, INT, TRUNC, CEILING.MATH, FLOOR.MATH, SUMPRODUCT
- Date and Time: DATE, TIME, (24, 1440, 86400), DAYS, EDATE, DAY, MONTH, YEAR, NETWORKDAYS.INTL, WEEKDAY
- Statistical: COUNTA, COUNTBLANK, COUNTIF, MIN, MAX
- Lookup: CHOOSE, VLOOKUP, HLOOKUP, INDEX, MATCH, FILTER*, UNIQUE*, XLOOKUP*
- Text: LEN, CONCAT, EXACT, FIND, LEFT, RIGHT, MID, UPPER, PROPER, REPT, TRIM, TEXT, VALUE
- Information: ISBLANK, ISERR, ISEVEN, ISNUMBER
- Financial: EFFECT, FV, IRR, NPV