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
- Excel Introduction and Intermediate courses or equivalent experience.
Who is the target audience?
- Students who want to expand their Excel knowledge.
|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|
|Tracing Cell Precedents and Dependents||00:03:00|
|Adding a watch Window||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|
|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|
|Order your Certificate||00:00:00|
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.