Course Badgei dont have words to explain but my overall experience is going well with you guys.
Excel Training: Advanced Tricks and Techniques
The Excel Training: Advanced Tricks & Techniques course helps you master advanced Excel functions like SUMPRODUCT, INDEX, and COUNTIF, enabling you to work more efficiently and create dynamic, interactive charts and reports.
Created By
27 STUDENTS ENROLLED
Loading video…
Nitty Gritty
- This course instrctor is a Microsoft MVP.
- This course covets the secret Excel magic tricks commonly used by the Power Users.
- CPD Certificate Included.
Last audited and Updated on : September 2025
Learning Outcome
- Master advanced Excel techniques that streamline daily tasks, reduce manual work, and improve spreadsheet performance.
- Learn how to use SUMPRODUCT, INDEX, and COUNTIF functions for complex, condition-based data calculations and reporting.
- Create dynamic, interactive charts that visually present trends and insights from complex datasets with clarity.
- Use wildcards effectively in Excel formulas to enhance search precision and simplify data manipulation processes.
- Apply custom number formatting techniques to make your spreadsheets more readable, professional, and visually appealing.
- Master advanced PivotTable features to summarise, filter, and analyse massive datasets quickly and accurately.
- Discover how to use the MOD function to solve practical, real-world mathematical and financial challenges in Excel.
- Learn how to apply the INDIRECT function to create dynamic references and build flexible Excel models.
- Combine advanced formulas and functions creatively to automate workflows and solve diverse analytical problems efficiently.
- Reinforce your knowledge by completing a comprehensive final quiz that tests practical mastery of Excel's advanced tools.
Course Overview
The Excel Training: Advanced Tricks & Techniques course is designed for individuals who already have a basic understanding of Excel and want to take their skills to the next level. In this course, you’ll explore a wide range of powerful techniques and functions that can dramatically improve your productivity, data analysis, and overall efficiency in using Excel. This course will provide you with the skills needed to tackle complex problems, streamline your workflow, and use Excel in innovative ways to get the most out of your data.
The course begins with an Introduction to advanced Excel techniques, setting the stage for the in-depth lessons that follow. In the Neat Tricks to Supercharge Your Productivity section, you’ll discover how to work faster and smarter by utilizing lesser-known Excel tricks that can help save time and effort. You’ll then learn how to use Wildcards with Formulas—a feature that many users overlook but can be a game-changer in complex searches and data manipulations.
The SUMPRODUCT Function section introduces you to this Swiss Army knife of Excel functions, demonstrating how it can be used for a variety of applications such as conditional summing and complex calculations. You’ll also master Advanced PivotTable Tricks, which are crucial for analysing large datasets and summarising information with ease. The MOD Function is often underutilised, but in the Hidden Power of the MOD Function section, you’ll see how it can be used to handle a variety of mathematical and financial problems that are commonly encountered in Excel.
The INDIRECT Function section explores this dynamic function, which allows you to reference cells indirectly and make your formulas more flexible and robust. Next, you’ll dive into the INDEX Function, a powerful tool that can replace VLOOKUP in many situations, offering more flexibility and speed in finding data across large tables. The COUNTIF Function is also explored in-depth, showcasing its ability to count specific data that meets certain conditions—an invaluable tool for data analysis.
Custom Number Formatting and Advanced Custom Formatting are covered to help you create visually appealing spreadsheets that are not only functional but easy to read. The course then moves into Advanced Chart Tricks, where you will learn how to create Dynamic Interactive Charts, a powerful way to visualise data and make your reports more engaging.
As a bonus, the course includes additional materials to help reinforce the lessons, and the course concludes with a Final Quiz to test your understanding of the advanced Excel techniques covered. Upon completion, you will be able to apply these advanced tricks and techniques to your daily Excel tasks, significantly boosting your efficiency and productivity.
Who This Course Is For:
- Excel users who are familiar with basic functions and want to deepen their knowledge.
- Data analysts looking to improve their skills in Excel.
- Business professionals who regularly work with large datasets.
- Financial analysts and accountants seeking more advanced Excel techniques.
- Project managers who need to organise and visualise data more efficiently.
- Office workers looking to increase productivity using Excel.
- Students who want to develop advanced Excel skills for their careers.
- Anyone interested in mastering Excel to enhance their work processes.
Requirements:
- Basic knowledge of Excel functions and formulas.
- Familiarity with Excel’s basic tools and navigation.
- No prior advanced Excel knowledge required—just a willingness to learn.
Course Instructor
Alan Murray
Alan Murray is an experienced Excel instructor with years of hands-on expertise in advanced Excel techniques. Having worked with various industries, Alan is passionate about helping professionals optimise their use of Excel through real-world examples and practical applications. His clear, step-by-step teaching style ensures that learners can master advanced features, improving their productivity and data analysis skills.
Accreditation
This Excel Training: Advanced Tricks & Techniques course is accredited by CPDQE, ensuring the training meets professional development standards. The CPDQE accreditation guarantees that the content is relevant, comprehensive, and recognized across industries. Completing this accredited course will help you enhance your Excel skills, demonstrating your commitment to efficiency, productivity, and data management in the workplace. The certification is valuable for both career advancement and building credibility in data-driven roles.
What Students say about us
The courses on the website are well designed, very clear and you can learn a lot of useful skills from them. A very
Enjoying the course at my pace, repeating what I did not understand and learning
Frequently Asked Questions
Everything you need to now about he product billing.
An Excel course is a training program that teaches you how to use Microsoft Excel for tasks like data entry, calculations, analysis, and creating charts. Courses range from beginner to advanced levels, covering everything from basic formulas to complex data tools like PivotTables and macros.
An Excel course in computer refers to training that teaches how to use Microsoft Excel, a spreadsheet software used for organising, calculating, analysing, and visualising data. The course typically includes lessons on formulas, functions, charts, data management, and automation tools like macros.
The best way to learn Excel is through a combination of online courses, hands-on practice, and real-world projects. Start with beginner tutorials, then progress to intermediate and advanced topics. Use platforms like Coursera, Udemy, or Microsoft Learn, and regularly apply what you learn to tasks like budgeting, data analysis, or reports to reinforce your skills.
Here are five commonly used basic functions in Excel:
- SUM() – Adds a range of numbers
- AVERAGE() – Calculates the mean of a range
- MIN() – Returns the smallest number in a range
- MAX() – Returns the largest number in a range
- IF() – Performs a logical test and returns different values based on TRUE or FALSE
VLOOKUP (Vertical Lookup) is an Excel function used to search for a value in the first column of a table and return a related value from another column in the same row.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
If you have a list of employee IDs and names, you can use VLOOKUP to find an employee’s name based on their ID.
It’s commonly used for data lookup, matching, and reporting tasks.
An Excel formula is an equation used to perform calculations or operations on data in a spreadsheet. Formulas begin with an equals sign (=) and can include numbers, cell references, operators (like +, -, *, /), and functions.
A PivotTable is a powerful Excel feature that lets you summarize, analyze, explore, and present large amounts of data quickly. It allows you to group and reorganize data dynamically without changing the original dataset.
The best method to learn Excel is a mix of structured learning and hands-on practice:
- Start with beginner tutorials
- Follow along with real examples to practice formulas, charts, and functions
Use Excel daily for tasks like budgeting, tracking, or reports
Excel can be easy to start but challenging to master. Basic tasks like entering data, using simple formulas (like SUM or AVERAGE), and formatting are beginner-friendly. However, more advanced features—such as PivotTables, macros, and complex formulas—can take time to learn.
Course Curriculum
| Section 1: Introduction | |||
| Introduction and What to Expect | 00:03:00 | ||
| Section 2: Neat Tricks to Supercharge your Productivity | |||
| 10 Paste Special Tricks used by the Pros | 00:12:00 | ||
| Excel’s Best kept secret and 3 ways to Use It | 00:07:00 | ||
| The Unsung Hero of Excel for Cleaning Messy Data | 00:12:00 | ||
| Edit Multiple Sheets at the Same Time with Ease | 00:05:00 | ||
| 5 Reasons your Excel Formulas are not Calculating | 00:06:00 | ||
| TOP SECRET – How to Make Sheets VERY Hidden | 00:05:00 | ||
| My Favourite Excel Shortcuts | 00:11:00 | ||
| Section 3: Using Wildcards with Formulas | |||
| Using Wildcards with VLOOKUP | 00:04:00 | ||
| Using wildcards with COUNTIF and SUMIF | 00:03:00 | ||
| Validate that Text is a Specific Number of Characters | 00:04:00 | ||
| Care for a Feedback | 00:01:00 | ||
| Section 4: The Awesome SUMPRODUCT Function - The Swiss Army Knife of Functions | |||
| The Definitive Guide to the SUMPR | 00:08:00 | ||
| Example 1-Count and Sum the Sales | 00:07:00 | ||
| Example 2 – Count the Occurrences | 00:05:00 | ||
| Example 3-Count the Unique Values | 00:04:00 | ||
| Example 4 – Sum the Top 3 Values | 00:03:00 | ||
| Example 5-Two Way Lookup Formula | 00:05:00 | ||
| Section 5: Advanced PivotTable Tricks | |||
| Two Reasons your PivotTable Counts instead of Sums | 00:07:00 | ||
| You Must Do this One Thing | 00:07:00 | ||
| Grouping Dates and Times | 00:10:00 | ||
| Using the GETPIVOTDATA Function to Pull Data from a PivotTable | 00:10:00 | ||
| Going Beyond the Sum-3 More PivotTable Calculations | 00:10:00 | ||
| Calculating the Difference to Previous Years Months Weeks | 00:06:00 | ||
| Create Your Own Calculated Fields | 00:04:00 | ||
| Using Conditional Formatting with PivotTables | 00:07:00 | ||
| Using Conditional Formatting with PivotTables | 00:07:00 | ||
| Five Useful PivotTable Settings | 00:09:00 | ||
| The Power of Slicers | 00:11:00 | ||
| 7 Slicer Settings you Will Want to Change | 00:09:00 | ||
| Section 6: The Hidden Power of the MOD Function | |||
| An Introduction to the MOD Function | 00:02:00 | ||
| Example 1- Extracting the Time from a Date-Time Cell | 00:03:00 | ||
| Example 2 – Prevent the Entry of Odd Numbers in a Range | 00:03:00 | ||
| Example 3- Calculate Total Balls Bowled from Overs in Cricket | 00:04:00 | ||
| Example 4 – Sum Every 3rd Row in a List | 00:07:00 | ||
| Section 7: The Magic of the INDIRECT Function | |||
| Example 1 – INDIRECT with Named Ranges | 00:03:00 | ||
| Ezample 2 – Referencing other sheets with Indirect | 00:05:00 | ||
| Example 3 – Return the Last Value from a Row | 00:05:00 | ||
| Example 4 – INDIRECT with VLOOKUP | 00:03:00 | ||
| Example 5 – Create Dependent Drop Down Lists | 00:04:00 | ||
| Section 8: The Incredible INDEX Function | |||
| Example 1 – Using INDEX and MATCH for an Advanced Lookup | 00:12:00 | ||
| Lecture 48 Example 2 – Create a Picture Lookup | 00:08:00 | ||
| Example 3 – Return the Value from the Last Row | 00:03:00 | ||
| Example 4 – Create a Dynamic Named Range | 00:13:00 | ||
| Example 5 – Sum the Last 6 Values Only | 00:05:00 | ||
| Example 6 – Retrieve a Range from a List of Ranges | 00:06:00 | ||
| Section 9: COUNTIF Function - A Mega Useful Excel Function | |||
| Using Cell Values with COUNTIF | 00:05:00 | ||
| Count values Between Two Dates | 00:03:00 | ||
| Prevent Duplicates in a Range | 00:05:00 | ||
| Compare Two Lists to Identify Missing Items | 00:05:00 | ||
| VLOOKUP for the Last Match in a List | 00:08:00 | ||
| Uniquely Rank Items | 00:05:00 | ||
| Section 10: Introducing Custom Number Formatting | |||
| Combine Text and Numbers in a Cell | 00:04:00 | ||
| Show the Weekday of a Date | 00:03:00 | ||
| Keep the Leading Zeroes of a Value | 00:04:00 | ||
| Display Negative Values in Red | 00:04:00 | ||
| Show Zero Values as Blank Cells | 00:03:00 | ||
| Section 11: Advanced Custom Formatting Examples that Users did not know was Possible | |||
| Display Symbols | 00:08:00 | ||
| Conditional Colour | 00:06:00 | ||
| Setting Thresholds | 00:05:00 | ||
| Section 12: Advanced Chart Tricks for Dynamic Interactive Charts | |||
| The Easiest Way to Create a Dynamic Data | 00:05:00 | ||
| Drop Down Menu to Select a Chart | 00:07:00 | ||
| Get Creative by Building a Dynamic Chart Title | 00:08:00 | ||
| Highlight the Max and Min Values Differently | 00:08:00 | ||
| Create a Scrollable Chart | 00:14:00 | ||
| Use Cell Values for Data Labels to Show | 00:05:00 | ||
| Rolling Chart for the Last 6 Months Only | 00:10:00 | ||
| Visualise Targets v’s Actuals | 00:04:00 | ||
| Using Check boxes to ShowHide Chart Data | 00:06:00 | ||
| Bonus Materials | |||
| advanced-excel-tricks-exercises [Download] | 00:05:00 | ||
| custom-number-format-characters | 00:03:00 | ||
| custom-number-format [Download] | 00:05:00 | ||
| Final Quiz: Excel Training: Advanced Tricks & Techniques | |||
| Final Quiz: Excel Training: Advanced Tricks & Techniques | 00:10:00 | ||
| Order Your Transcript | |||
| Order Your Transcript | 00:01:00 | ||
