Microsoft Excel – Pivot tables, 3D formulas, Advanced Formulas and protection

Understand how to use formulas that perform calculations between worksheets – they’re called 3D formulas and they multiply the power of your spreadsheet to create scenarios, forecasts, financials and more. Learn more about how to get the information you need using advanced filter.

Learn how to create a pivot table and change the value field settings to get some fantastic statistics about your data from many different perspectives then create a pivot chart to visually show the results.

Using advanced functions like CHOOSE, VLOOKUP, HLOOKUP, MATCH and INDEX and many other advanced functions within functions (Nested Functions) will enable you to get even more information about the data you already have.

In this course you’ll also learn how to protect your worksheet and workbooks so that the people you give your spreadsheets to don’t destroy the hours of time you spent putting your spreadsheet together.

Included Topics:

ADVANCED OPTIONS

• Task: Show the Developer Ribbon

3D FORMULAS BETWEEN SHEETS

• CREATING A VARIABLES SHEET
• Task: Re-create these worksheets using 3D formulas

FILTERING YOUR DATA

• Exercise: Using AutoFilter

CLEANING UP A DATA FILE USING AUTOFILTER

• Exercise: Cleaning up a database using AutoFilter

ADVANCED FILTER

• Exercise: Perform an Advanced filter
• Exercise: Advanced filter using Wildcards (search text within cells)
• Exercise: Advanced Filter for multiple search criteria (OR)
• Exercise: Narrow the results using Advanced Filter (AND)

CREATING A PIVOTTABLE

• Task: Insert a Pivot Table
• Task: Filter the data in a Pivot Table
• Task: Sort Dates by Month
• Test: Pivot Table
• Task: Change the Value Field Settings
• Project: Using the PivotTable
• Project: Adding information using a function and getting total sales value

PIVOTTABLE CHARTS

THE CHOOSE FUNCTION

• Task: Get month values and Choose
• Task: Automatically produce ratings
• Task: Choose the calculation you want to occur

VLOOKUP

• Task: Insert a VLOOKUP which tells us the Isle and price of a product
• Task: Perform vlookup using Named Ranges

HLOOKUP

• Task: Paste Special for Formulas, values, comments and formatting

MATCH AND INDEX

• Task: Find the match
• Incorporate cell information in text
• Task: Use Index to find the value in a matched row

NESTED MATCH AND INDEX FUNCTIONS

• Task: Performed a nested MATCH & INDEX function
• Task: Combine MATCH with IFERROR functions

PROTECTION

• Task: Open a protected workbook
• Task: Understanding cell protection
• Task: Finding Cells which contain calculations

 

Back to Microsoft Excel Training Courses outline Enrol now