Excel 2016 videos and tasks

Task

Content or article title

Video Duration

Topics Covered

Get started with Excel

What is Excel?

0:01:05

Overview video:

• spotting trends

• charts and Pivot table recommendations

• saving and sharing

 

Create a Workbook

0:00:33

• create a blank workbook

• create a workbook from a template

 

Add, delete or rename worksheets

0:00:34

• insert a worksheet

• rename a worksheet

• delete a worksheet

Add text and data

Add and edit data

0:04:08

• add data

• edit data

• change the active cell

 

Search for data

N/A

• search for data within a workbook

 

Align text in a cell

0:00:40

• align a column

• align a row

• align all cells in a workbook

 

Select cell contents

0:00:57

• select a cell

• select a cell range

• select non-contiguous cells or ranges

• select a row, column or worksheet

• select non-adjacent columns or rows

• other ways to select cell contents

 

Move or copy cell contents

0:00:57

• move cells by drag and dropping

• move cells by using cut and paste

 

Format numbers in cells

0:00:27

• select cells and select format

• find additional format options

 

Merge cells

N/A

• merge and unmerge cells

 

Wrap text within cells

N/A

• wrap text automatically

• enter a line break

 

Change the look of cells

0:01:01

• choose cells, select and apply a style

• modify an applied style in cells

 

Insert or delete rows or columns

0:01:00

• insert a column

• delete a column

• insert a row

• delete a row

• insert a cell, shift cells, insert entire row or entire column

 

Resize rows and columns

0:00:40

• resize row width

• resize column width

• automatically resize all columns and rows to fit the data

 

Paste data by using special paste options

N/A

• using paste special when copying from Excel

• applying mathematical operation to copied data

 

Copy cell formatting

0:01:01

• copy cell formatting using copy/paste options, Format Painter and dragging techniques

• copy number formatting using the same techniques

 

Conditional formatting

0:00:58

• create visual cues using conditional formatting for selected data

 

Import/export data

N/A

• import/export data from a delimited text file

Design workbooks

Modify page setup

N/A

• page setup and layout

• page tab, margin tab, header/footer tab, sheet options

• insert headers/footers

 

Change magnification by using zoom tools

N/A

• zoom in/out of a worksheet

 

Freeze top and left panes

0:01:27

• freeze the first column

• freeze the first two columns

• freeze columns and rows

 

Move or copy worksheets

0:00:51

• move a worksheet within a workbook

• copy a worksheet in the same workbook

 

Change worksheet tab color

N/A

• change the text color for a cell or range of cells

• format the color of a worksheet tab

 

Insert and remove hyperlinks

N/A

• create hyperlinks

• change or modify hyperlinks

• delete hyperlinks

 

Hide or unhide columns

0:00:37

• selecting one or more columns and hiding them

• unhiding columns

 

Insert headers and footers

N/A

 

 

Display formulas

N/A

• display or hide formulas

• switch between displaying formulas or hide them

Add and format tables

Create and format tables

0:00:30

• format your data as a table

 

Sort data in a table

0:01:02

• getting ready to sort a table

• sort the data in a range or table

• custom sorting

 

Filter data in a table

0:01:03

• filter a range of data

• filter data in a table

 

Add a Total row to a table

0:00:34

• add a Total row to a table from the Design tab

 

Filter data with slicers

0:00:36

• convert data into a table

• use a slicer to filter the data

Print

Print all or part of a workbook

N/A

• print one or several worksheets or workbooks

• print all or part of a worksheet

• print an Excel table

• print a workbook to a file

 

Set print scaling

N/A

• scale a worksheet

• scale a worksheet to print in a different font size

 

Set a print area

N/A

• set or clear a print area on a worksheet

• set one or more print areas

• clear print area

 

Display repeating row and column titles on multipage worksheets

N/A

• print rows with column headers on top of every page

Add and format charts

Create charts

0:00:45

• create a chart using Recommended Charts

• add a trendline

 

Format Charts

0:05:31

• create a chart

• add and format a chart title

• change the chart layout

• change the chart style

• select a theme style to add color

 

Add sparkline charts

0:00:39

• add a sparkline to show data trends

• format a sparkline chart

 

Add trendlines and drop lines

0:03:01

• add and format a trendline

• select a value with Forward or Backward fields to project your data

• add drop lines for better readability

Analyze chart data

Quick analysis of data

0:00:37

• use the Quick Analysis feature

• add a chart

Advanced features

AutoFill time spans

0:00:53

• using the Auto Fill feature to fill cells with data that follow a pattern or series

 

Split data

0:00:51

• using the Convert Text to Columns wizard to split data

 

Combine data

0:01:44

• combine data with the Ampersand symbol (&)

• use the CONCAT function to combine data

 

Build custom numeric formats

0:00:57

• create and build custom numeric format to show numbers as percentages, currency, or dates

 

Validate cell data

0:01:18

• use data validation to make rules to restrict the type of data or values others enter into a cell.

 

Insert subtotals

N/A

• insert subtotals

 

Remove duplicate records

N/A

• remove duplicate records

 

Create reusable lists

0:03:38

• create and use a custom list

• sort using a custom list

Use formulas and functions

Create formulas

0:01:39

• what is the formula bar

• create a formula that refers to values in other cells

• how to see a formula

• enter a formula that contains a built-in function

• relative references

 

Display formulas

N/A

• display or hide formulas

• switch between displaying formulas or hide them

 

Name cells and ranges

0:01:46

• naming a cell

• defining names from a selected range

• using named cells in formulas

 

Use Trace to fix formula errors

0:04:10

• defining Trace dependent cells and Trace precedent cells

• using Trace dependent cells to fix a formula

• using Trace precedent cells to fix a formula

 

Locate functions

0:02:17

• find functions and categories of functions

• using the Function library group

• find more information about functions

 

Calculations using functions

0:07:00

• using functions and specifying data in the function arguments box

• using Insert Function

 

Perform calculations by using MIN and MAX functions

N/A

• perform calculations

 

Perform calculations by using the COUNT function

N/A

• perform calculations

 

Perform calculations by using the AVERAGE function

N/A

• perform calculations

 

Perform logical operations by using the IF function

N/A

• perform calculations

 

Perform logical operations by using the SUMIF function

N/A

• perform calculations

 

Perform logical operations by using the AVERAGEIF function

N/A

• perform calculations

 

Perform statistical operations by using the COUNTIF function

N/A

• perform calculations

 

Format text by using RIGHT function

N/A

• format text

 

Format text by using LEFT function

N/A

• format text

 

Format text by using MID function

N/A

• format text

 

Format text by using UPPER function

N/A

• format text

 

Format text by using the LOWER function

N/A

• format text

 

Format text by using the PROPER function

N/A

• format text

 

Format text by using the CONCATENATE function

N/A

• format text

Create and format Pivot Tables and Pivot Charts

Create Pivot Tables

0:00:45

• create and insert a pivot table for a table or range

• build out your pivot table using Pivot Tables Fields pane

• move selected fields in the pane to design your pivot table

 

Work with Pivot Tables

0:01:30

• arrange the data in your pivot table with Pivot Table Fields pane

• filters, columns, rows and values

 

Group data in Pivot Tables

0:01:37

• group data

• group selected items

• name a group

• ungroup grouped data

 

Filter data with Slicers in Pivot Tables

0:00:36

• filter data in a pivot table using slicer

• filter data manually

 

Create Pivot Charts

0:01:04

• create a pivot chart

• create a chart from a pivot table

Collaborate with others

Share workbooks

0:00:41

• share a workbook and apply permissions

 

Add and review comments

0:01:30

• add a comment

• review comments

• see all comments at once, show or hide comments

• move a comment

Password-protect workbooks

Password-protect workbooks

0:00:45

• require a password to open an Excel file

• protect workbook with encrypted password

 

Add a watermark

0:01:17

• use an image or logo to create a watermark with Header & Footer function

Workbook properties

Inspect a workbook for hidden properties or personal information

N/A

• remove hidden data and personal information in Office documents

 

Inspect a workbook for accessibility issues

N/A

• use the Accessibility Checker to find accessibility issues

 

Inspect a workbook for compatibility issues

N/A

• issues that cause a significant loss of functionality

• issues that cause a minor loss of fidelity

 

Modify document properties

N/A

• view or change the properties of a document

Quick Access toolbar

Customize the Quick Access toolbar

N/A

• add/remove commands

• change the order of the commands

• group the commands

• move the Quick Access Toolbar

Share this page: