Microsoft Excel: Intermediate / Advanced
Microsoft Excel Training Series | Level 2
Duration: 2 days (9 am – 4 pm)
Microsoft® Excel® Versions: 2010 | 2013 | 2016 | 2019 | 365 (Windows)
Delivery Methods: Instructor-led classroom (ILC) | Instructor-led virtual classroom (VC)
Course Dates: July 20/21, 2020 (Virtual) | August 20/21, 2020 (Virtual) | View schedule
Course Fee: $495 CDN person person (virtual classroom), $545 CDN per person (bring your own device) or $595 CDN per person + HST (Avantix Learning provides device)*
Custom training: This course may be delivered at your site or ours as an instructor-led or virtual classroom solution. Contact us at email@example.com for more information including savings for custom group training.
Note: Public scheduled courses are delivered as live instructor-led classroom training or virtual classroom training (as indicated). Bring your own device rates apply to live classroom courses.
This Microsoft Excel course is designed for users who want to move to the next level and focus on core competencies in Excel. Students will learn shortcuts to enter common functions as well as Excel key concepts and then move on to powerful conditional functions and tools including IF, SUMIF, COUNTIF, VLOOKUP and conditional formatting. Links will be created and managed between worksheets, workbooks and other programs. Students will review strategies to manage list or database data using filtering, sorting, subtotalling and validations. Using Excel data, charts will be generated and formatted and then a chart template will be created to save time. Students will also create pivot tables to summarize list or database data and will create simple macros to automate repetitive tasks. During this hands-on course, students will practice their skills and the instructor will include numerous tips, tricks and shortcuts. Each student will also receive a full course manual.
Prerequisite: Microsoft Excel: Introduction or equivalent knowledge and skills.
Location and timing: Public scheduled courses are held in downtown Toronto and run from 9:00 am to 4:00 pm.
Related training: View all Microsoft Excel courses >
INCLUDED IN THIS COURSE
- Comprehensive course manual
- Keyboard shortcuts quick reference
- Sample and exercise files (new samples added frequently)
- Refreshments (for classes conducted in Avantix Learning classrooms)
- Certificate of completion
- Follow-up email support
Working Effectively in the Microsoft Excel Environment
- Adding buttons to customize the Quick Access Toolbar (QAT)
- Controlling key Excel options
- Navigating and selecting cells using keyboard shortcuts
- Automating formatting using themes
Mastering Key Excel Concepts
- Creating formulas using absolute, relative and mixed referencing
- Naming cells and cell ranges
- Using names in formulas
- Editing named cells and ranges using the Name Manager
Tips and Techniques for Entering Core Excel Functions
- Using different strategies to quickly enter core Excel functions including SUM, AVERAGE, MIN and MAX
- Counting cells containing numeric or text values
- Inserting the current static date or dynamic date
- Extracting date information from cells containing dates using core date functions
- Using keyboard shortcuts to insert and display formulas
- Combining text strings in a formula
Using IF and VLOOKUP Functions to Display Conditional Results
- Using the powerful IF function to output conditional results
- Creating nested IF statements
- Creating formulas using SUMIF and COUNTIF
- Using the VLOOKUP function to look up data vertically in a list or database
- Dealing with VLOOKUP errors
Applying Conditional Formatting
- Highlighting cells using built-in conditional formats
- Clearing conditional formatting
- Editing rules for icon sets
- Writing formulas in conditional formatting rules
- Highlighting entire records using conditional formatting
Protecting and Formatting Worksheets and Workbooks
- Protecting worksheets and workbooks
- Using cell styles and themes to speed up formatting in multiple worksheets
Linking Worksheets and Workbooks
- Using link cells in formulas to link worksheets and workbooks
- Managing and updating links between workbooks
- Importing linked and unlinked Excel data into Microsoft Word
Summarizing Cells or Ranges from Multiple Sheets
- Understanding 3D references
- Inserting formulas that extract totals from multiple sheets using 3D references
- Considerations when inserting, moving and deleting sheets
Manipulating and Extracting List or Database Data
- Identifying the parts of an Excel list or database
- Best practices for setting up lists or databases correctly in Excel
- Inserting records on a worksheet or adding records using a data form
- Sorting records using the Sort feature with multiple sort keys
- Using a custom sort to sort records
- Inserting subtotals using the Subtotal feature
- Applying filters with multiple criteria
- Using custom criteria when filtering text, number and date fields
- Setting up advanced filters for more complex filtering such as overcoming the two criteria custom filter maximum
Converting Data Sets to Tables
- Why you should be using Excel tables
- Converting lists to tables in 3 different ways
- Creating formulas in tables
- Displaying totals for filtered records
- Converting tables back to normal ranges
Reducing Errors with Validations
- Applying validations to ensure users enter the correct type of data in cells
- Creating custom dialogs if users attempt to enter incorrect data
- Creating drop-down lists to make data entry easier and more accurate
Visualizing Data using Charts and Sparklines
- Inserting charts on a spreadsheet or on a new Excel sheet
- Applying chart formats to improve the appearance of your charts
- Changing the chart type
- Applying different types of formatting using various tips and tricks
- Saving time by creating a chart template
- Displaying trends using miniature charts in cells called Sparklines (in Excel 2010 and later)
Summarizing Data using Pivot Tables
- Creating pivot tables to summarize Excel list or database data
- Rearranging fields in a pivot table
- Applying formatting to pivot tables and fields
- Filtering a pivot table
- Creating a pivot chart
- Updating or refreshing a pivot table
- Applying filtering using Excel’s slicers (in Excel 2010 and later)
- Adding a pivot table button to the Quick Access Toolbar
Automating Tasks using Simple Macros
- Creating simple macros using the macro recorder to automate tasks
- Viewing VBA (Visual Basic for Applications) code created by the macro recorder
- Controlling macro security
- Running a macro using different strategies
- Writing keyboard shortcuts
NOTE: This is not a VBA programming course. If you are interested in learning more about writing macros with Visual Basic for Applications, check out our Excel VBA courses designed for non-programmers:
Register Now for a public course at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada.
Do you have any other questions? Contact us!
If you are looking for more information regarding versions, check out our Microsoft Excel version overviews.
To request this page in an alternate format, contact our staff.
*Prices subject to change.
Copyright 2020 Avantix® Learning Inc.
You may like
A pivot table can be deleted in an Excel workbook in several ways. You can delete a pivot table, convert a pivot table to values or clear data and customizations from a pivot table to reset it. When a pivot table is created from source data in a workbook,
Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently.
You can group by month, year or other date period in a pivot table in Excel. One option is to group by date periods in a pivot table using the Grouping feature. Alternatively, you can also create calculations in source data to extract the month name and the year from a date field and use the fields in your pivot table.
If you want to break, update or change links to Excel charts or worksheets in Microsoft Word, you’ll need to use the Edit Links to Files command. Unfortunately, this command is difficult to find. To make it easier to access, you can add Edit Links to Files to the Quick Access Toolbar.
You can convert seconds to minutes and seconds in Excel by combining calculations and custom number formatting …
You may also like
You can import or convert a PowerPoint presentation in PPTX format to Google Slides format in several ways. Google Slides will try to convert a PowerPoint presentation to compatible objects, tools and fonts but these programs do have differences so don’t expect a perfect conversion.
You can change the formatting of a table border or the borders of multiple table cells in PowerPoint by changing the line style, thickness (weight) or color. Different borders can be applied to cells in the same table. For example, you could apply a thick line to header cells and a thin line to the cells in the last row of a table.
You can rotate objects in PowerPoint by dragging the rotation handle, entering an exact rotation amount or by using keyboard shortcuts. To rotate an object means to turn it around its center. Shapes, images and other objects can be rotated in Normal View or Slide Master View.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | firstname.lastname@example.org