Formulas and Functions
Exploring audit tools to examine and evaluate formulas00:00
Constructing common functions with the aid of the function wizard and the Formula tab00:00
Implementing names in formulas and functions00:00
Worksheet Formatting and Presentation
Drawing attention to data with formatting00:00
Working with templates – Protecting cell ranges and worksheets00:00
Analyzing data with conditional formatting00:00
Getting Data In and Out
Transforming data with global replace and data tools00:00
TEXT and DATE functions00:00
2013 Flash Fill assistant00:00
Setting WISE (Written, Integrated, Synergistic, and Expansive) goals00:00
Sorting, Selecting and Subtotaling data
Subtotaling a Filtered list with the subtotal command00:00
Implementing Tables and Table Formulas00:00
Subtotaling a table with the Total Row00:00
Filtering Tables with Slicers00:00
6: Present Data Visually
Leveraging charts to create a visual presentation00:00
Creating charts using wizards00:00
Adjusting chart settings, including titles, labels, and chart types00:00
Working with Views of the spreadsheet00:00
Setting up your workbook for printing00:00
Introducing Advanced Features
Implementing dates00:00
Summarizing data from other worksheets with 3-D formulas00:00
Designing IF functions and conditional formatting00:00
Analyzing summary data with Pivot tables and slicers00:00
Working with Data
Connecting to Data in an Access Database00:00
Creating a Web Query00:00
Importing Web Data using a Saved Query00:00
Working with Hyperlinks00:00
Creating and Working with PivotTables
Understanding PivotTables00:00
Creating a PivotTable using Worksheet Data00:00
Creating a PivotTable using an External Data Connection00:00
Laying out a PivotTable on a Worksheet00:00
Modifying PivotTable Fields00:00
Using a Report Filter00:00
Refreshing a PivotTable00:00
Formatting a PivotTable00:00
Using VLOOKUP to Add a Column to a List00:00
Enhancing PivotTables
Working with Summary Functions00:00
Creating a Slicer00:00
Applying Filters00:00
Grouping Data00:00
Using a Timeline00:00
Creating a Calculated Field00:00
Creating a Calculated Item00:00
Creating Advanced Formulas
Apply Range Names00:00
Range Names00:00
Adding Range Names Using the Name Box00:00
Adding Range Names Using the New Name Dialog Box00:00
Editing a Range Name and Deleting a Range Name00:00
Using Range Names in Formulas00:00
Use Specialized Functions
Function Categories00:00
The Excel Function Reference00:00
Function Syntax00:00
Function Entry Dialog Boxes00:00
Using Nested Functions00:00
Automatic Workbook Calculations00:00
Showing and Hiding Formulas00:00
Enabling Iterative Calculations00:00
Working with Multiple Worksheets and Workbooks
Use Links and External References00:00
Consolidate Data00:00
Automating Workbook Functionality
Apply Data Validation00:00
Search for Invalid Data and Formulas with Errors00:00
Work with Macros00:00
Analyzing Data with Logical and Lookup Functions Use Text Functions
Text Functions00:00
The LEFT and RIGHT Functions00:00
The MID Function00:00
The LEN Function00:00
The TRIM Function00:00
The CONCATENATE Function00:00
The TRANSPOSE Function00:00
Use Logical Functions
Logical Functions00:00
Logical Operators00:00
Logical Operators00:00
Logical Operators00:00
Logical Operators00:00
Use Lookup Functions
Lookup Functions00:00
The LOOKUP Function00:00
The VLOOKUP Function00:00
The HLOOKUP Function00:00
Use Date Functions
The TODAY Function00:00
The NOW Function00:00
Serializing Dates and Times with Function00:00
Use Financial Functions
The IPMT Function The PPMT Function00:00
The NPV Function00:00
The FV Function00:00
Organizing Worksheet Data with Tables Create and Modify Tables
Table Components00:00
The Table Tools – Design Contextual Tab00:00
Styles and Quick Style Sets00:00
Customizing Row Display00:00
Table Modification Options00:00
Apply Basic Conditional Formatting00:00
Sort and Filter Data
Filter Operators00:00
Removing Duplicate Values00:00
Use Subtotal and Database Functions to Calculate Data
SUBTOTAL Functions00:00
The Subtotal Dialog Box00:00
Summary Functions in Tables00:00
Database Functions00:00
Visualizing Data with Charts
Create Charts00:00
Charts00:00
Chart Types00:00
Chart Insertion Methods00:00
Resizing and Moving the Chart00:00
Adding Additional Data00:00
Switching Between Rows and Columns00:00
Modify and Format Charts
The Difference Between Modifying and Formatting00:00
Excel Table
This module explains how to create data tables in Excel
Excel Data Tables00:00
Summarizing Data00:00
: Create an Excel Table
: Pivot Tables and Pivot Charts
This module describes pivot tables and pivot charts and how to work with them.
Pivot Tables00:00
Pivot Charts00:00
Editing Pivot Tables and Pivot Charts00:00
: Importing Data from a CSV File
Dashboards
This module describes Excel dashboards, how to create them and the role in data analysis in Excel pivot
tables.
Creating a Dashboard00:00
Data Analysis in Excel Pivot Tables00:00
: Create a Dashboard
: Hierarchies
This module describes hierarchies and time data.
Hierarchies00:00
Time Data00:00
Creating Hierarchies
: The Excel Data Mode
This module explores the Excel data model and looks at ways of extending it.
Using an Excel Data Model00:00
External Data00:00
: Explore an Excel Data Model
