ComboProjects

Understanding matters

Excel Advanced

fast_carAdvanced Skills in Microsoft Excel

This 1 day course is designed for users that have a good background in using Excel. The focus is on creating more challenging formulas and functions. Instead of listening to ours of lecturing we spend most time playing with Excel. In the various sessions we learn how to use the keyboard shortcuts to accomplish common tasks.

Objectives

At the end of this workshop, the participants will be able to use many more built-in Excel functions and some obscure utilities that Excel provides. We learn a workaround to a nuisance with VLookup(). We also learn how to record a macro and run it.

Detail

  • Useful Excel options.
  • Understanding the Goal Seeking command.
  • Creating worksheet scenarios with the Scenario Manager.
  • Using VLOOKUP() and MATCH() together to add functionality to VLOOKUP()
  • Technique for using nested IF() function to add more logic to a worksheet.
  • Using concatenation in Excel to create summaries.
  • Creating a single sheet Excel template and a workbook template.
  • Protecting your worksheet cells. This is very useful in conjunction with templates. Also protecting worksheet tabs.
  • Adding a password to open a workbook. Also adding a password to modify a workbook.
  • Summary functions: SUMIF(), COUNTIF(), AVERAGEIF(), SUMIFS), COUNTIFS(), AVERAGEIFS() and the SUBTOTAL() functions.
  • Creating a Pivot Table from a list. Customizing a Pivot Table.
  • Creating a Pivot Chart.

Note

In this advanced level the user gets a lot of hands-on practice in order to do more complex analysis of the data in Excel. It is recommended to bring a memory stick to make a personal copy all the files.