Microsoft Office Excel 2016: Part 2 (Intermediate Level)
5.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
258 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Office Excel 2016: Part 2 (Intermediate Level) to your Wishlist.

Add to Wishlist

Microsoft Office Excel 2016: Part 2 (Intermediate Level)

Advance your foundational knowledge and begin taking advantage of some of the higher-level functionality.
5.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
258 students enrolled
Last updated 1/2017
English
English
Price: $25
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 7 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Visualize data with charts.
  • Use PivotTables and PivotCharts.
  • Working with powerful Functions you need to know.
View Curriculum
Requirements
  • This course builds upon foundational knowledge of Microsoft Office Excel 2016.
Description

In this course you'll learn create advanced workbooks and worksheets that can help deepen your understanding of organizational intelligence. The ability to analyze massive amounts of data, extract actionable information from it, and present that information to decision makers is at the foundation of a successful organization that is able to compete at a high level. 

Upon successful completion of this course, you will be able to leverage the power of data analysis and presentation in order to make informed, intelligent organizational decisions.

You will: 

  • Work with functions. 
  • Work with lists. 
  • Analyze data. 
  • Visualize data with charts.
  • Use PivotTables and PivotCharts.

This course covers Microsoft Office Specialist exam objectives to help students prepare for the Excel 2016 Exam and the Excel 2016 Expert Exam. 

Who is the target audience?
  • This course is designed for those, who wish to begin taking advantage of some of the higher-level functionality in Excel to analyze and present data.
Compare to Other Excel Courses
Curriculum For This Course
52 Lectures
02:26:24
+
Introduction and Course Overview
1 Lecture 02:27

In this course you'll learn create advanced workbooks and worksheets that can help deepen your understanding of organizational intelligence. The ability to analyze massive amounts of data, extract actionable information from it, and present that information to decision makers is at the foundation of a successful organization that is able to compete at a high level.

Preview 02:27
+
Working with Functions
19 Lectures 01:05:33

Learn how to 'talk' to Excel at a higher level to get the most out of your data. 

Preview 01:04

Learn how to ease your work by name ranges for use in functions and formulas. 

Preview 00:56

Cell names and range names are exactly what they sound like. They are meaningful names you assign to a given cell or range to make it easier to both understand what calculations are being performed in a formula and to reuse the references for a number of purposes. (Cell and Range Names, Names and the Name Box, Name Manager, The Create from Selection Command) 

Preview 05:34

Although it's certainly helpful to be able to name a range or a cell for easy navigation, the real power of this feature lies in the ability to easily identify references in formulas and to quickly and accurately insert references into multiple formulas. (Cell and Range Names in Formulas, Manually Entering Cell or Range Names, The Use in Formula Command Method, The Formula AutoComplete Method) 


Use Defined Names in a Formula
03:12

Learn how to perform far more complex tasks in Excel beyond adding up rows and columns and multiplying the sum by some other figure. (Function Categories, The Excel Function Reference, Comparison Operator Basics, Function Syntax, The COUNTIF Function, The TODAY Function) 

Locate and Use Specialized Functions
05:06

The logical functions in Excel provide a method for testing various conditions to calculate a result of a value, text, or a calculation which enable you to ask questions of your data.

Work with Logical Functions - Introduction (02:13)
02:13

The IF function returns one value if the logical test you enter as an argument is true, and it returns a different value if the logical test is not true. (IF Function)

IF Function
04:29

Learn how to combine logical tests. (AND Function, OR Function, NOT Function) 

AND, OR and NOT Function
05:40

The key to combining multiple calculations into a function in a single cell is nesting. Nesting is, simply, using a function as an argument within another function. (Nesting, Nested Function Syntax, Guidelines for Combining Functions) 

Combine Functions
06:18

Learn how to use other powerful IF Functions. (Functions Similar to the IF Function (SUMIF, COUNTIF, AVARAGEIF ))

Functions Similar to the IF Function
05:14

Learn how to work with of some of the more commonly used date and time functions. 

Preview 01:24

Learn how to master the most basic date and time functions. (TODAY, NOW, DATE) 

Preview 02:54

Learn how to work with more advanced date and time functions - not only important to all, who frequently deal with scheduling and analyzing data for particular periods of time. (The NETWORKDAYS Function, The WEEKDAY Function, The WORKDAY Function, The ISOWeeknum Function) 

NETWORKDAY, WEEKDAY, WORKDAY and ISOWeeknum Functions
05:28

Excel's text functions let you manipulate text in cells to extract portions of text to other cells or combine them. 

Work with Text Functions - Introduction
00:47

This basic text functions let you manipulate text in cells to extract portions of text to other cells or combine them to produce e.g. full names or addresses. (The LEFT Function, The RIGHT Function, The MID Function) 

LEFT, RIGHT and MID Functions
02:40

To extract text from cells that contain values of varying characters, Excel also includes the FIND function. (The FIND Function) 

FIND Function
03:19

This feature splits a single column of text into multiple columns. For example, you can create first and last name columns from one column of full names. (The Text to Columns Feature) 

Text to Columns Feature
03:38

One of the most powerful text functions available in Excel is the CONCATENATE function. This function enables you to concatenate, or join together, text strings from multiple cells into a single cell. This function can save you massive amounts of time when you need to pull together data from multiple cells that already exists in your worksheets. (The CONCATENATE Function, Text Concatenation with the Ampersand, &) 

CONCATENATE Function
02:59

Excel contains three text functions that are very useful for formatting text to display it in the desired format. (The UPPER, LOWER and PROPER Function) 

UPPER, LOWER and PROPER Functions
02:38

Quiz - Lesson 1
5 questions
+
Working with Lists
8 Lectures 14:33

By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries. 

Preview 00:30

By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries. (Sorting, Clean Data, Multiple Column/Row Sorting, Quick Sorts, Custom Sorts) 

Sort Data
03:00

In this topic, you will learn to filter data, which limits the rows of data you have to review in order to find what you are looking for. 

Preview 00:36

When you filter data in Excel, you do not affect the actual data entries; you alter only how Excel displays your data. Learn how to use this feature to your advantage. (Filter, AutoFilters, Custom AutoFilters, Advanced Filtering, The Criteria Range, Filter Operators) 

Filter Data
02:29

Similar to advanced filters, you can use database functions. These functions allow you to find the data you are looking for and perform calculations all in one step. 

Query Data with Database Functions
00:42

Learn how to query data with database functions. Database functions, essentially, combine the functionality of Excel functions with the functionality of advanced filters. (Database Functions, Database Function Syntax) 

Database Functions
03:12

Learrn how to outline and subtotal your data which may be especially useful when you are working on a spreadsheet with a large amount of data. 

Outline and Subtotal Data - Introduction
00:36

Learn how to outline data and perform calculations on subsets of data. (Outlines, The SUBTOTAL Function, The Subtotals Feature) 

Use Subtotals to Summarize Data
03:28

Quiz - Lesson 2
4 questions
+
Analyzing Data
7 Lectures 18:26

By converting your raw data into tables, you will be able to take advantage of additional reporting features without affecting any of the data you have entered into your worksheets. 

Preview 00:44

Excel tables contain robust functionality that enables you to organize, change the display of, and perform calculations on worksheet data quickly and easily. (Tables, Table Components, The Table Tools Design Contextual Tab, Table Styles and Quick Styles, Quick Analysis) 

Create and Modify Tables
06:49

Learn how to apply basic function to your data in tables. (Summary, Average, Count)

Use Summary Functions in Tables
03:21

Learning additional conditional formatting options beyond the basics is the next step to help you analyze data trends.

Preview 00:41

In addition to the preconfigured conditional formatting options available in Excel 2016, you have the option of creating completely custom conditional formats to suit your needs. A mayor step in becoming an Excel Expert! (Custom Conditional Formats, Rule Precedence) 

Apply Intermediate Conditional Formatting
02:44

Learn how to examine and evaluate the data in one column, and then apply the specified conditional formatting to another column. And how to format the cells in numerous columns based on criteria in a different column. 

Apply Advanced Conditional Formatting - Introduction
00:35

The tools available in Excel enable you to create specific rules you can use to apply conditional formatting and to tailor the display of conditionally formatted cells using an incredible array of options. (Use a Formula to Determine Which Cells to Format Rule, Cell References and Conditional Formatting, Guidelines for Applying Conditional Formatting to Cells Based on Values in Other Cells) 

Use Logical Functions to Apply Conditional Formatting
03:32

Quiz - Lesson 3
3 questions
+
Visualizing Data with Charts
8 Lectures 23:20

Charts are a great way to interpret data, as many people need to see data visually to comprehend it better. Plotting data in charts can make spreadsheets less confusing when incorporated in your workbooks. 

Preview 00:38

Learn how to create Charts, that help worksheet viewers to quickly and easily interpret the data in a worksheet. (Chart Basics, Chart Types, Chart Insertion Methods, Recommended Charts).

Create Charts
04:28

A well-formatted chart can mean the difference between simply delivering information and making an impact on your audience. 

Modify and Format Charts - Introduction
01:09

Excel 2016 provides you with a vast array of options when it comes to modifying and formatting your charts. (Modification vs. Formatting, Chart Elements, Chart Elements Guidelines, The Chart Tools Contextual Tab, The Format Task Pane, The Chart Tools Buttons)

Modify and Format Charts
05:15

Excel 2016 includes a wide range of advanced charting features that enable you to display widely varying sets of data together, include forecasting trends on your charts.

Use Advanced Chart Features - Introduction
00:29

A dual-axis chart is, simply, a chart that displays two sets of information on the same chart. The main advantage to dual-axis charts is the ability to not only display two different sets of data simultaneously, but also to format the different sets of data independently of each other. (Dual-Axis Charts, Forecasting, Trendiness, Trendline Types, The Format Trendline Task Pane) 

Create a Dual-Axis Chart
04:27

Working with advanced charting features can require quite a bit of chart formatting and modification. And, it's likely you'll need to reuse at least some of your charts. (Chart Templates) 

Create a Chart Template
03:02

If your data is misrepresented or presented ineffectively, key insights and understanding are lost, which hurts both your message and your reputation. (Order Data Appropiately, Space Bars Appropiately, Reduce Unnecessary Labeling, Increase Readability, Chart Type Considerations, Line Margins, Maintain Itegrity) 

Visualizing Data with Charts - Best Practice
03:52

Quiz - Lesson 4
3 questions
+
Using PivotTables and PivotCharts
9 Lectures 22:05

PivotTables combine some of the most powerful and useful types of Excel functionality, such as sorting, filtering, summary functions, and subtotals, to give you an incredible level of control over how you view your data. 

Preview 00:17

To take advantage of the functionality and flexibility of PivotTables, you must first understand how to create them. (PivotTables, Recommended PivotTables, The PivotTable Fields Task Pane) 

Create a PivotTable
04:03

Excel 2016 provides you with a number of different tools and commands you can use to organize the structure of your PivotTables. Knowing how these tools work and understanding how PivotTable structure translates into actionable intelligence are the keys to getting the answers you seek. 

Analyze PivotTable Data - Introduction
00:38

(The PivotTable Tools Contextual Tab, Summarize and Show Combinations, The GETPIVOTDATA Function) 

Analyze PivotTable Data
04:29

Like standard Excel charts, PivotCharts are graphical representations of numeric values and relationships among those values. The main difference is simply that PivotCharts are linked to PivotTable data, whereas standard charts are linked to either a range of data or a table. 

Present Data with PivotCharts - Introduction
00:50

Learn how to translate your PivotTable data into PivotCharts. (PivotCharts, The PivotChart Fields Task Pane, PivotChart Filters) 

Present Data with PivotCharts
05:01

Slicers are a tool that gives you a high level of control over PivotTable filtering. is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables. 

Filter Data by Using Timelines and Slicers - Introduction
00:54

Learn how to apply Slicers as a high level of control over PivotTable filtering, one that is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables and PivotCharts. (Slicers, The Slicer Tools Contextual Tab, The Report Connections Dialog Box, Timelines, The Timeline Tools Contextual Tab) 

Filter Data by Using Slicers and Timelines
04:18

Hope you enjoyed this training and that I see you in the Microsoft Office Excel 2016: Part 3. In that course, you will build upon the skills you have acquired by working with multiple worksheets and workbooks, use lookup functions and formula auditing to troubleshoot your workbooks and fix errors, and to share and protect workbooks from unwanted changes. You will also learn to automate Excel functions, create sparklines and map data, as well as to forecast future data. 

Preview 01:35

True or False? PivotTables can only summarize values and cannot perform any other calculation.
Quiz - Lesson 5
4 questions
About the Instructor
SONIC - High Quality Training for Over 20 Years
4.2 Average rating
388 Reviews
6,411 Students
51 Courses
Training Company

SONIC Performance Support is a leading provider of e-learning and performance support solutions. We proudly serve individuals, government agencies, schools and businesses of all sizes. SONIC Performance Support provides high-quality video training, that is used by well-known domestic and foreign companies. With video based training, you have a personal coach explaining and demonstrating the task at hand. You can stop the video, perform the task in the application, and rewind if you want to review.