Sale!

Certificate in Microsoft Excel Spreadsheets (Advanced)

$149.00

Learn the Advanced skills using Microsoft Excel for Office support and administration tasks and working with data. Work with data sets across multiple tables and sheets and consolidate detailed finance reports into easy to understand data. Learn Goal Seek, Solver and protect sheets, cells, ranges and other areas of your spreadsheet so other staff can use it without ruining complex functions.

Includes:

  • Video Tutorials
  • Practice files
  • Downloadable Training Manuals (PDF format)
  • Assessment tests
  • 12 months course access
  • Tutor & Central Student Support

CLICK HERE to ENROL for $25pw

Description

Databases, Filter/Sort, Named Ranges

  • Looking at Excel program options, automatic calculation,
  • Error checking rules, customising the ribbon and quick access toolbar.
  • The format of databases: delimiters, headers, records, fields, flat file vs relational data,
  • Filtering and sorting data and different data formats like Comma Separated Values (CSV), TXT, Tab separated values
  • How to open data in different programs.
  • Naming Ranges and using Named Ranges in formulas.
  • Data validation and drop down lists and mastering find and replace.
  • Use the Power Query Editor
  • Group and Filter data to make it easier to understand
  • Summarise data by fields
  • Append one Excel data file onto another that has more columns
  • Combine information from two columns for a bakery sales report
  • Produce a sales report by order date and remove duplicates
  • Merge data from related sources to provide meaning home insurance reports

Pivot tables, 3D Formulas, Advanced Formulas and Protection

  • Understand 3D formulas between worksheets,
  • Clean up data using advanced filter,
  • Create a pivot table and change the value field settings,
  • Create a pivot chart to visually show the stats,
  • Using advanced functions like CHOOSE, VLOOKUP, HLOOKUP, MATCH and INDEX,
  • Look at advanced functions within functions (Nested Functions) and
  • How to protect the worksheet and workbooks.

Goal Seek, Data Consolidation and Solver

  • Find out how much you can afford to pay for a house using goal seek and financial functions,
  • Consolidate the information from several sheets,
  • Understanding the grouping of data,
  • Use solver to explore possible outcomes depending on input variables.
  • Understand how to forecast future income
  • Internal rate of return
  • Net present value and other financial functions