Excel Bootcamp
Advanced
This training enables you and your team to get the most out of Excel. It is designed for those who work frequently with Excel and want improve their overall skills in Excel- ranging from creating high impact dashboards to saving time through automating data cleaning in excel.
Get Offer
FOR TEAMS
Objectives
Topics
Pivot Table Fundamentals
- Understanding the power of pivot tables
- Pivot table structure
- Data design for pivot tables
Creating and Editing Pivot Tables
- Adding fields and rearranging a pivot table report
- Incorporating data source changes
- Inserting a filter field and adding layers
Customizing Pivot Tables
- Changing the report layout and formatting
- Adding grand totals and multiple subtotals
- Changing the type of calculation used to summarize values
- Adjusting the display settings for calculated values
Grouping, Sorting & Filtering
- Grouping dates and times, values, and text fields
- Using filters for row and column fields
- Filtering using the filters area, slicers and timelines
- Sorting in a pivot table
Performing Calculations
- Alternative methods for pivot table calculations
- Calculated fields and items
- Managing and maintaining pivot table calculations
Pivot Charts
- Creating and editing a pivot chart
- Applying conditional formatting within a pivot table
- Pivot chart limitations and alternatives
Internal Data Model
- Building a data model and managing relationships
- Adding and removing tables from the data model
- Building a pivot table using external data sources
Creating and Customizing Charts
- Understanding and choosing chart types
- Creating and customizing chart elements
- Exploring user interface choices for modifying charts
- Filtering and handling missing and hidden data
- Creating chart templates
- Applying best visualization practices
Advanced Charting Techniques
- Integration of dynamic elements into charts
- Applying area highlights
- Using conditional colors in charts
- Creating performance tracking and project control charts
- Time tracking with Gantt charts and timelines
- Visualization with single data point charts
Sparklines and Conditional Formatting
- Creating and customizing sparklines
- Specifying conditional formatting rules
- Using conditional formats that use graphics
- Conditional formatting with the REPT function
Working with Form Controls
- Inserting form controls
- Linking form controls to data and graphs
- Creating scrollable tables
- Substituting form controls with slicers
Formulas
- Entering and editing arithmetic, comparison, text, and reference formulas
- Formula operators and calculation settings
- Copying formulas and range references
- Working with external links
- Applying and using range names in formulas
- Troubleshooting formulas using formula auditing tools
Function Principles and Logic
- Understanding the structure of a function
- Finding the required function
- Adding intelligence with logical and information functions
- Combining functions for extended functionality
Data Analysis with Statistical Functions
- Calculating central measures
- Identifying extreme values
- Creating frequency distributions
Array Forumulas
- Entering and editing single-cell and multicell array formulas
- Math array operations
- Comparative array operations and aggregate calculations
- Join array operations
- Array constants
- Array functions
Manipulating Text and Data Cleaning
- Data import and structuring
- Removing unwanted characters
- Converting and formatting text, concatenating data
- Searching and extracting substrings
- Filling and deleting gaps
- Removing duplicates
Working with dates and time
- Dealing with dates and times
- Date- and time-related functions
Data Extraction and Validation
- Retrieving information with lookup functions
- Exact versus approximate lookups
- Looking up values to the left of a column
- Performing two-way lookups
- Performing multiple column lookups
- Case-sensitive lookups
Working with Tables and Lists
- Tables and structured references
- Advanced filtering techniques and database functions
- Outlining and subtotals
Training Formats
In Person
Team Training
At Your Office
Real-Time
Team Training
Online
Self-Paced
for Individuals
Online
FAQ
Who is my trainer?
We do not guarantee a specific instructor per course but we make sure your training is led by an expert in the field. We are able to indicate your instructor's name before booking the training as we see their availability for given dates. Please not that our trainers have experience across different industries and unique knowledge of applications of the analytical tools.
Will we need and NDA (non-disclosure agreement)?
It is common for the course participants to approach us with their specific problems based on the company data. It is often sensitive data and we would like to make sure you can feel save and secure about it. Therefore, we always recommend signing an NDA and send it along as our pre training preparation package. However, it is not required.
What is the language of instruction?
As we mostly train international companies, all our trainings are conducted in English unless specifically agreed otherwise; however, support is provided in English, Dutch, Polish, and German.
Will we receive a certificate of completion?
At the end of the training you will receive a certificate of completion, which specifies the learned contents of the seminar.
Can my training be customized?
Yes it is possible, however it may generate additional cost depending on the magnitude of changes. We would like to discuss it case by case to find an optimal solution. If you are interested in developing a training based on your data drop us a line.
The level within my team varies. What can we do?
Before the training all participants are asked to take a short survey. This helps us in understanding better the level across your team beforehand. Additionally, we are flexible and will adjust to your needs as we go. The instructor can quickly recognize learning curve of the group and adjust the pace during the training. Usually 10% of the course outline is moderated during the training.
What is the difference between in-person training and real-time training?
In-person training means our trainer comes to your office to host the training. Therefore, having a dedicated room where all participants feel comfortable is important. Alternatively, in extraordinary circumstances, we could arrange a training location for your team but this would come at additional expense. The Real-Time Training takes place as scheduled online sessions either via Zoom, MS Teams, Webex, Hangouts or another software of your choice. This setup allows for scheduling flexibility, trainings of remote teams and direct screen sharing for the questions. Training content is the same for both.
What happens after I send a training request?
Once you inquire for the training offer, we will send you a pdf document with exact course outline and our offer.
If you like it, you confirm the booking via email and we block the training dates that suit you.
We follow-up with pre-training preparation, access links and all detailed information so that the training can run smoothly.
