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
