This course exposes several advanced features of Excel for What-if analysis, database analysis and how to build calculations using advanced functions as well as how to automate processes.
Build complex calculations with advanced functions.
Link, export and consolidate data.
Limit data entry and build complex models with Lookup functions.
Analyse data with simple PivotTables and PivotCharts.
Create and modify Macros.
Protecting your data
Workbook protection
Protecting cells
Protecting structure
Circular references
Locating and deleting circular references
When circular references are necessary
Formula error checking
Range names
Defining range names
Using names in formulas
Navigating with names
Using named constants
Documenting range names
Functions
Math and rounding functions
The AND function
The OR function
Nested IF function
The VLOOKUP function
The HLOOKUP function
Transposing data
Concatenating functions
INDEX and MATCH functions
Using help for functions
Data Validation
Limiting cell values
Creating drop-down cell lists
Validation error messages
What-if analysis tools
Using goal seek
Importing data
Importing CSV files
Text import wizard
Workbook linking
Workbook linking
Dealing with broken links
Data consolidation
Consolidation by cell position
Consolidation by headings
Exporting data
Saving as a different file type
Exporting to Word
Pasting data with a link
Copying a graph with a link
PivotTable reports
Create a PivotTable
Rearranging data
Applying filters
Adding fields
Modify field settings
Refreshing data
Formatting a PivotTable
PivotCharts
Creating a PivotChart
Filtering a PivotChart
Macros
Recording a Macro
Editing a Macro
Relative/Absolute Recording
Stepping through a Macro
Macro buttons
Customising the toolbar
Writing a line of code
Deleting a Macro