Design Better Spreadsheets
- The Golden Rules of Spreadsheet Design
- Improving Readability with Cell Styles
- Controlling Data Input
- Practise questions
Making Decisions with Logical Functions
- Logical Functions (AND, OR, IF, XOR, NOT)
- The IF Function
- Nested IFs
- The IFS Function
- Error Handling with IFERROR and IFNA
- Practise questions
Advanced Formatting & Data Management
- Fill cells by using Flash Fill
- Fill cells by using advanced Fill Series options
- Create custom number formats
- Group and ungroup data
- Create custom conditional formatting rules
- Create conditional formatting rules that use formulas
- Manage conditional formatting rules
- Format as table
- Comments
- Freeze Panes
- Practise questions
Formula Auditing
- Troubleshooting Common Errors
- Tracing Precedents and Formula Auditing
- Remove Arrows
- Show formulas
- Error checking
- Evaluate Formula
- Watch window
- Calculation Options
- Calculate Now
- Calculate sheet
- Practise questions
Data Validation
- Creating Dynamic Drop-down Lists
- Other Types of Data Validation
- Custom Data Validation
- Practise questions
Protect
- Protect Sheet
- Protect Workbook
- Allow Edit Ranges
- Unprotect sheet and workbook
- Practise questions
Working with Date and Time functions
- Understanding How Dates are Stored in Excel
- Applying Custom Date Formats
- Using Date and Time Functions
- Using the WORKDAY and WORKDAY.INT Functions
- Using the NETWORKDAYS and NETWORKDAYS.INT Function
- Tabulate Date Differences with the DATEDIF Function
- Calculate Dates with EDATE and EOMONTH
- Using the YEAR, MONTH, DAY, YEARFRAC, WEEKDAY, WEEKNUM, TIME, HOUR, MINUTE, SECOND functions
- Practise questions
Advanced Sorting and Filtering
- Performing Sorts on Multiple Columns
- Sorting Using a Custom List
- The SORT and SORTBY Functions
- Using the Advanced Filter
- Extracting Unique Values - The UNIQUE Function
- The FILTER Function
- Practise questions
Working with formulas
- Text formulas (LEN, RIGHT, LEFT, LOWER, PROPER, UPPER, CONCATENATE, T, EXACT, TEXT, TRIM, N, FIND, SEARCH,, MID, CHAR)
- Math formulas (RAND, RANDBETWEEN, ROUND, ROUNDUP, ROUNDDOWN, SUMPRODUCT, SUMSQ, ABS, ODD, EVEN, MOD, POWER, PRODUCT, QUOTIENT)
- Information formulas (ISNUMER, ISTEXT, ISBLANK, ISFORMULA, ISLOGICAL, ISEVEN, ISODD, ISNA, ISNONTEXT
- Statistical formulas (COUNT, COUNTA, COUNTBLANK, LARGE, SMALL, MODE, MEDIAN)
- Conditional IFs (SUMIF, COUNTIF, AVERAGEIF)
- Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS)
- Looking Up Information using VLOOKUP (Exact Match)
- Looking Up Information using VLOOKUP (Approx Match)
- Looking Up Information Horizontally using HLOOKUP
- Performing Flexible Lookups with INDEX and MATCH
- Using XLOOKUP
PivotTables
- PivotTables Explained
- Pivoting the PivotTable Fields
- Applying Subtotals and Grand Totals
- Applying Number Formatting to PivotTable Data
- Show Values As and Summarize Values By
- Grouping PivotTable Data
- Formatting Error Values and Empty Cells
- Choosing a Report Layout
- Applying PivotTable Styles
- Creating a Pivot Chart
- Formatting a Pivot Chart
- Inserting and Formatting Slicers
- Inserting Timeline Slicers Connecting Slicers to Pivot Charts
- Interactive dashboards
- Design principles
- Practise questions
Preparing Data for Analysis
- Importing Data into Excel
- Removing Blank Rows, Cells and Duplicates
- Changing Case and Removing Spaces
- Splitting Data using Text to Columns
- Splitting Data using Text Functions
- Splitting or Combining Cell Data Using Flashfill
- Joining Data using CONCAT
- Formatting Data as a Table
- Practise questions
Dynamic Arrays
- What are Dynamic Arrays?
- Introduction to Spills and Arrays
- Unique vs Distinct
- Extract Unique Values with Multiple Criteria
- Extract Unique Values by Column
- The SORT Function
- SEQUENCE function
- The FILTER Function
- FILTER with Logic: + Operator (OR)
- FILTER with Logic: * Operator (AND)
- Using RANDARRAY and RANDBETWEEN to Randomize Data
- Using XLOOKUP
- Practise questions
Macros and VBA
- Using Macros to Automate Tasks
- Recording, Saving and Running Macros
- Recording a Macro with Relative Referencing
- Multi-step Macros
- The VBA Editor
- Assigning Macros to Buttons
- Creating a Custom Macro Ribbon
- Practise questions
- Final Exam
Search 