Excel 2013 Advanced

Name: Excel 2013 Advanced
Rating: 4.4 (24 reviews)

Take Excel to the next level with advanced formulas, What-If Analysis tools, advanced PivotTables, Macros and much more

Created byIntellezy Trainers

Last updated 4/2018

English

What you'll learn

Use SUMIF, AVERAGEIF, and COUNTIF
Use Advanced Lookup Functions
Use Complex Logical and Text Functions
Auditing Formulas
Work with What-If Analysis Tools
Protect Worksheets and Workbooks
Use Advanced PivotTables and PowerPivot Features
Automate with Macros
Work with Form Controls
Ensure Data Integrity
Collaborate in Excel
Import and Export Data to a Text File

Course content

15 sections • 64 lectures • 2h 47m total length

Introduction1:03

Summarizing Data with SUMIF4:27
Summarizing Data with AVERAGEIF2:10
Compute conditional production averages with averageif in Excel 2013 advanced. Copy with absolute references and verify results; note the syntax matches sumif and countif.
Summarizing Data with COUNTIF2:03
Learn to use countif to count cells in a range that meet a specific criterion, with the two-argument syntax (range and criteria), and contrast with countifs for multiple conditions.

Using VLOOKUP with TRUE to Find an Approximate Match4:10
Using HLOOKUP with TRUE to Find an Approximate Match1:48
Using the INDEX Function2:58
Using the MATCH Function2:05
Learn how the match function returns an item's position in a range using look up value, look up array, and exact or approximate match types, with a product id example.
Creating a Combined INDEX and MATCH Formula2:51
Comparing Two Lists with VLOOKUP2:36
Use VLOOKUP to compare two lists for exact matches, revealing items in List 2 not in List 1 by returning N/A for missing entries; this works across sheets or workbooks.
Comparing Two Lists with a Combined VLOOKUP and ISNA4:50

Protection Overview1:42
Explore protection features for cells, worksheets, and workbooks in Excel 2013 advanced, including encryption, password protection, and digital signatures. Learn how to set permissions, restrict access, and safeguard workbook structure.
Excel File Password Encryption4:36
Allowing Specific Worksheet Changes2:29
Protect a worksheet with the protect current sheet feature, choosing what users may do, such as inserting rows or columns, formatting, and using filters, pivot tables, and scenarios.
Adding Protection to Only Certain Cells in a Worksheet3:52
Unlock the worksheet, then lock selected cells and protect the sheet to prevent changes; use format cells to set locked and hidden, and hide formulas from the formula bar.
Additional Protection Features2:42
Explore advanced protection features in Excel 2013, including mark as final as an honor-system indicator, restrict access via rights management, and a digital signature that ensures workbook integrity.

Using the PivotTable and PivotChart Wizard2:08
Use the PivotTable and PivotChart Wizard to create PivotTables from a list or database, add it to the quick access toolbar, and place results in a new worksheet.
Adding a Calculated Field2:56
Adding a Calculated Item1:24
Applying Conditional Formatting to a PivotTable2:35
Filters in the PivotTable Fields Pane1:56
Creating Filter Pages for a PivotTable2:23
Enabling the PowerPivot Add-In3:20
Enable the Power Pivot add-in to combine multiple lists into an Excel data model and create pivot tables from related data, with custom formulas and KPIs.

What are Macros?2:39
Displaying the Developer Tab & Enabling Macros in Excel2:56
Enable the developer tab in Excel to access the VBA editor, macros, and macro security settings, with the default to disable all macros with notification and enable content when needed.
Creating a Basic Formatting Macro4:55
Record a basic formatting macro in Excel 2013, name it, assign a shortcut, and store it in the workbook. Stop recording to avoid looping.
Running a Macro1:37
Assigning a Macro to a Button2:18
Creating a More Complex Macro4:05
Demonstrate advanced Excel 2013 macros using relative references to format names across rows. Understand limitations like no undo and editing via VBA, plus basics of import/export, pivots, and charts.
Viewing and Editing the VBA Code for an Existing Macro6:17
Adding a Macro to the Quick Access Toolbar2:02
Add a macro to the quick access toolbar in Excel 2013, customize its symbols and header, and run it across all workbooks via the personal macro workbook.

What are Form Controls?2:37
form controls add interactive buttons and objects to spreadsheets, making data entry easier and the interface more user-friendly. ActiveX controls offer deeper VBA customization for flexible design.
Adding Spin Buttons and Check Boxes to a Spreadsheet2:47
Explore adding spin buttons and check boxes in Excel 2013 advanced, comparing regular form controls with Active X controls, and using macros or custom VBA code to respond to interactions.
Adding a Combo Box to a Spreadsheet1:40

Requirements

Basic computer skills and Excel 2013 Introduction and Excel 2013 Intermediate courses or equivalent experience.

Description

This course will teach students advanced concepts and formulas in Microsoft Excel 2013. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions including INDEX and MATCH, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students w

With nearly 10,000 training videos available for desktop applications, technical concepts, and business skills that comprise hundreds of courses, Intellezy has many of the videos and courses you and your workforce needs to stay relevant and take your skills to the next level. Our video content is engaging and offers assessments that can be used to test knowledge levels pre and/or post course. Our training content is also frequently refreshed to keep current with changes in the software. This ensures you and your employees get the most up-to-date information and techniques for success. And, because our video development is in-house, we can adapt quickly and create custom content for a more exclusive approach to software and computer system roll-outs.

This course aligns with the CAP Body of Knowledge and should be approved for 2.75 recertification points under the Technology and Information Distribution content area. Email info@intellezy.com with proof of completion of the course to obtain your certificate.

Who this course is for:

Students who want to expand their Excel knowledge.

Excel 2013 Advanced

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 1min

Using SUMIF, AVERAGEIF, and COUNTIF3 lectures • 9min

Using Advanced Lookup Functions7 lectures • 21min

Using Complex Logical and Text Functions5 lectures • 15min

Formula Auditing4 lectures • 10min

What-If Analysis Tools3 lectures • 11min

Worksheet and Workbook Protection5 lectures • 15min

Advanced Use of PivotTables and PowerPivot7 lectures • 17min

Automating with Macros8 lectures • 27min

Working with Form Controls3 lectures • 7min

Requirements

Description

Who this course is for: