Excel Data Analysis Techniques
This Excel training course will teach you how to analyse and summarise worksheet and database information, consolidate data, forecast and plan budgets, assess risks and draw out information from datasets to use in reports or calculations.
This training course is valuable for anyone who uses Excel regularly to make decisions based on data. This course is aimed for higher level Intermediate to Advanced users of excel who already have a fairly good knowledge of Functions and Formulas. If you want to learn more of the basics about the Functions and Formulas in Excel then do look at our Excel Functions and Formula training course, or for even more advanced knowledge into the world of Macros and Visual Basic then look at our Excel Visual Basic (VBA) training course
This course is about harnessing the power of Microsoft Excel to analyse business problems and identify solutions. You gain the knowledge and hands-on skills required to exploit Excel's intermediate and advanced features, such as PivotTables and PivotCharts.
A reasonable understanding of Excel formulas.
Topics covered in this course
Review of Basic Function
- Basic Cell Referencing
- Fixed and Mixed Cell References
- Shortcuts for Fixed Cell Referencing
- Using Natural Language and Named Cells
- Using the Name Manager
- Referencing Named Cells
Tables and Named Ranges
- Using Data in Tabular format
- Using External Constants
- Creating a Data Table
- Named Table Function
- Formatting Named Tables
- Assets of Named Tables
- Adding Header, Footer and Total rows
- Using Named Table Data in calculations
- Named Row and Column conventions
Data Functions and nested Formulas
- Using Today and Now
- Calculating working days
- Interpreting data variations with the IF function
- Streamlining calculations with referencing
- Developing nested functions for multiple conditions
- Capturing information with lookup functions
- Applying techniques to implement and troubleshoot nested calculations
- Basic Conditional formatting techniques
- Good Structural techniques for building workbooks
- Using Traffic Light analysis
- Filtering and Sorting based on conditional formats
Analysing Data Sets
- Using the Autofilter
- Using the Subtotal Function
- Extracting unique lists of records from an Excel data set with the Advanced Filter
- Analysing data sets with filters and aggregation
- Using Subtotal Feature
- What are Pivot Tables used for?
- How to create a basic PivotTabel
- Presenting PivotTable reports effectively with PivotCharts
- Examining data patterns with Sparklines
Testimonials for this course
- Project Support Officer, Corporate Real Estate, AIG April 2016
- Team Secretary SJAtrees. September 2017
- Senior Investigator, Apple, January 2016
If you can no longer attend a course you have booked on, the following terms will apply depending on how many calendar days remain before the course date:
More than 30 days: You will be offered a full refund, or you may change to alternative course dates, or you may change the name on the booking.
21 to 30 days: You will be offered a 50% refund, or you may change to alternative course dates, or you may change the name on the booking.
14 to 20 days: You will be offered a 25% refund, or you may change the name on the booking.
0 to 13 days: No refund is offered, however you may change the name on the booking.