Designed for users who are very comfortable in Excel and want to go beyond what the average Excel user can do. Set yourself apart from your colleagues and impress your less Excel-savvy bosses. Accomplish more complex tasks and automate repetitive ones.
Learning Objectives
In this course you will combine, analyze, and display data using Excel's powerful features, and learn the basics of how to use macros to further extend Excel's capabilities. You will:
Use Data Validation to restrict data entry and create drop-downs
Use outlining tools to group and subtotal your data
Protect worksheets and workbooks from accidental changes
Use conditional formatting to highlight cells based on rules
Analyze data with Pivot Tables
Record and delete macros
Link and consolidate data across multiple workbooks
Import and export data and run web queries
Course Outline
Section 1: Enhancing Workbooks
Section 1.1: Customizing Workbooks
Comments
Hyperlinks
Watermarks
Background Pictures
Section 1.2: Preparing a Workbook for Multiple Audiences
Add Alternative Text to Objects
Modify Worksheets Using the Accessibility Checker
Manage Fonts
Section 1.3: Creating and Using Templates
Create a Template
Modify a Template
Section 2: Working with Multiple Workbooks
Section 2.1: Consolidating Data
Data Consolidation
The Consolidate Dialog Box
Consolidation Functions
Section 2.2: Linking Cells in Workbooks
Understand External References
Link Individual Cells
Link Groups of Cells
Section 2.3: Merging Workbooks
The Compare and Merge Workbooks Feature
Removing Duplicates
Transposing Data
Section 3: Protecting Workbooks and Worksheets
Protecting a Workbook
Protecting a Worksheet
Unprotecting Workbooks and Worksheets
Section 4: Outlining, Grouping, and Subtotalling
Grouping & Outlining
Subtotalling
Section 5: Importing and Exporting Excel Data
Section 5.1: Exporting Excel Data
The Export Process
Section 5.2: Importing a Delimited Text File
The Import Process
The Get External Data Group
Delimited Text Files
Methods of Importing Text Files
Section 5.3: Integrating Excel Data with the Web
The File Publishing Process
Publish as Web Page Dialog Box
Section 5.4: Creating a Web Query
Web Queries
The New Web Query Dialog Box
Section 6: Analyzing Data with Pivot Tables, Slicers, and Pivot Charts
Section 6.1: Creating a Pivot Table
Pivot Tables
The Create Pivot Table Dialog Box
The Pivot Table Fields Pane
Summarize Data in a Pivot Table
The “Show Values As” Functionality of a Pivot Table
Create a Calculated Field
Refresh the Pivot Table Data
Add Style to a Pivot Table
Work with Subtotals and Grand Totals
Introduction to PowerPivot
Section 6.2: Filtering Pivot Table Data
Use the Field Headers
Use the Pivot Table Dialog Box
Use Slicers
Section 6.3: Analyzing Data with Pivot Charts
Create Pivot Charts
Filter with Pivot Charts
Section 7: Automating Worksheet Functionality
Section 7.1: Updating Workbook Properties
Workbook Properties
Section 7.2: Creating and Editing a Macro (Overview)
What Are Macros?
The Record Macro Dialog Box
Name Macros
Visual Basic for Application
Copying Macros Between Workbooks
Macro Security Settings
Difference Between a Relative and Absolute Macro
Section 7.3: Applying Conditional Formatting
What Is Conditional Formatting?
The Conditional Formatting Rules Manager Dialog Box
Clear Rules
Section 7.4: Adding Data Validation Criteria
Data Validation
The Data Validation Dialog Box
At The Great Canadian Training & Consulting Company, we are raising the bar for what it means to provide GREAT learning.
Our live, instructor-led training is designed to help your team learn new skills that they can apply back at the office immediately! From Microsoft's suite of programs to practical professional skills, come learn with our experienced and engaging instructors today!
Draw processes and other diagrams using MS Visio to present impactful and simple hybrid multi-page diagrams with intuitive navigation.
In this course, you’ll learn the basics of creating, editing, and formatting documents in Word 2013. Discover how to leverage templates to build better documents, collaborate with other team members, create numbered and bulleted lists, and work with tables.
All of the necessary learning tools are provided, including class notes, computers, and software. All we require is a meeting room. Our unique training style will ensure no topic is unexplored, no question remains unanswered, and all participants exit the course more knowledgeable than they entered...
In this course, you will learn how to use Access to manage your data by creating a new database, constructing tables, designing forms and reports, and creating queries to join, filter, and sort data.
This advanced Microsoft 365 course is designed to help users collaborate on documents and secure information. This course also covers techniques for creating complex documents, such as forms or documents that require reference pages.
© 2024 coursetakers.com All Rights Reserved. Terms and Conditions of use | Privacy Policy