This course is ideal for
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.

What you will learn


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.



Topics covered in this training course

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

Related

---