Contact for queries :
020 3900 4072

If you are looking forward to advance your skills with Microsoft Excel 2016 and want to learn more advanced skills or want to learn the topics covered in this course in the 2016 interface, this Excel 2016 Advanced will be the perfect choice for you!

Through the course you will gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros. Additionally, you will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in your worksheets and workbooks. You will also learn about Excel’s many collaboration features, as well as how to import and export data to and from your workbooks.

What Will I Learn?

  • Use Advanced IF Statements
  • Use Advanced Lookup Functions
  • Use Complex Logical and Text Functions
  • Auditing Formulas
  • Working with What-If Analysis Tools
  • Protecting Worksheet and Workbook
  • Use Advanced PivotTables and PowerPivot Tools
  • Automate with Macros
  • Work with Form Controls
  • Ensure Data Integrity
  • Collaborate in Excel
  • Import and Export Data to a Text File

Requirements

  • Excel Introduction and Intermediate courses or equivalent experience.

Who is the target audience?

  • Students who want to expand their Excel knowledge.

Course Curriculum

Introduction
Introduction 00:01:00
Using Advanced IF Statements
Summarize Data with SUMIF 00:04:00
Summarize Data with AVERAGIF 00:03:00
Summarize Data with COUNTIF 00:02:00
Using Advanced Lookup Functions
Using VLOOKUP with TRUE to find an Approximate Match 00:04:00
Using HLOOKUP TRUE to find an Approximate Match 00:01:00
Using the Index Function 00:03:00
Using the Match Function 00:02:00
Creating a Combined Index and Match Formula 00:04:00
Comparing Two Lists with VLOOKUP 00:02:00
Comparing Two Lists with VLOOKUP and ISNA 00:04:00
Using Complex Logical and Text Functions
Creating a Nested IF Function 00:03:00
Using the IFERROR Function 00:02:00
Using the LEN Function 00:02:00
Using the TRIM Function 00:01:00
Using the Substitute Function 00:02:00
Formula Auditing
Showing Formulas 00:01:00
Tracing Cell Precedents and Dependents 00:03:00
Adding a watch Window 00:02:00
Error Checking 00:02:00
What-If Analysis Tools
Using the Scenario Manager 00:03:00
Using Goal Seek 00:02:00
Analyzing with Data Tables 00:02:00
Worksheet and Workbook Protection
Protection Overview 00:02:00
Excel File Password Encryption 00:03:00
Allowing Specific Worksheet Changes 00:01:00
Adding Protection to only Certain Cells in a Worksheet 00:03:00
Additional Protection Features 00:02:00
Advanced Use of PivotTables and PowerPivot
Using the Pivot Tables Charts Wizard 00:02:00
Adding a Calculated Field 00:02:00
Adding a Caculated Item 00:02:00
Apply Conditional Formatting to a Pivot Table 00:03:00
Using Filters in the Pivot Table Fields Pane 00:02:00
Creating Filter Pages for a Pivot Table 00:01:00
Enabling a Power Pivot Add In 00:04:00
Automating with Macros
What are Macros 00:03:00
Displaying the Developer Tab and Enabling Macros in Excel 00:03:00
Creating a Basic Formatting Macro 00:03:00
Running a Macro 00:02:00
Assigning a Macro to a Button 00:02:00
Creating a More Complex Macro 00:02:00
Viewing and Editing the VBA Code for an Existing Macro 00:03:00
Adding a Macro to the Quick Access Toolbar 00:02:00
Working with Form Controls
What are Form Controls 00:02:00
Adding Spin Buttons and Check Boxes to a Spreadsheet 00:02:00
Adding a Combo Box to a Spreadsheet 00:02:00
Ensuring Data Integrity
What is Data Validation 00:01:00
Restricting Data Entries to Whole Numbers 00:02:00
Data Validation Restricting Data Entry to a List 00:01:00
Data Validation Restricting Data Entry to a Date 00:01:00
Data Validation Restricting Data Entry to Different Text Lengths 00:01:00
Composing Input Messages 00:02:00
Composing Error Alters 00:02:00
Finding Invalid Data 00:02:00
Editing and Deleting Data Validation Rules 00:01:00
Collaborating in Excel
Working with Comments 00:03:00
Printing Comments and Errors 00:02:00
Sharing a Workbook 00:03:00
Tracking Changes in a Workbook 00:03:00
Working with Versions 00:02:00
Sharing Files via Email 00:02:00
Importing and Exporting Data to a Text File
Importing a Text File 00:03:00
Exporting a Text File 00:01:00
Conclusion
Course Recap 00:01:00
Course Certification
Order your Certificate 00:00:00

FAQs

This course is for anyone who's interested in this topic and wants to learn more about it. This course will also help you gain potential professional skills.

No prior qualifications are needed to take this course.

You can study this course from wherever and whenever you want. You can study at your own pace and from any device. Just log in to your account from any device and start learning!

Yes, there is a test at the end of the course. Once you’ve completed all the modules of the course, you will have to give a multiple-choice test. The questions will be based on the topics of the modules you studied. And of course, you can take the test at any time, from any device and from anywhere you want.

Don’t worry if you fail the test, you can retake it as many times as you want.

You don’t have to wait a minute after your payment has been received, you can begin immediately. You will create your login details during the checkout process and we will also send you an email confirming your login details.

We make the payment process easy for you. You can either use your Visa, MasterCard, American Express, Solo cards or PayPal account to pay for the online course. We use the latest SSL encryption for all transactions, so your order is safe and secure.

After you complete the course, you’ll immediately receive a free printable PDF certificate. Hard Copy certificate is also available, and you can get one for just £9! You may have to wait for 3 to 9 days to get the hard copy certificate.

Course Reviews

About

iStudy is a brand of Adams Academy Inc. Ltd. Company Number 10647280, England and Wales, VAT No. GB-267018794

11 Floor, 15 St Botolph Street, London EC3A 7BB
020 3900 4072
hello@istudy.org.uk

Last Tweets

Validate your certificate

Coming soon

top