MS Excel Tutorial Viewing

Prologue to the Excel Video Guide

DTE Interns, please complete security awareness training

By Emily Wilson

Emily Wilson

Location: Lake Odessa (W Lansing)School: GVSUMajor: Statistics (Math)Job: Challenge DetroitWorkforce Dev & CompHistory: +4 yrs. in retail3 Random Facts:2 Chinchillas Mateo and Mannix6 Marathons (1st Detroit)Traveled to +25 states & 8 countries

Microsoft Excel Youtube Video Guide

By Emily Wilson

DTE Interns, pleaase only use downloaded data from the folder or teams chat for this tutorial

Navigation the Guide

Back to Home

Show interactive content

Introduction

Back to Index

DOWNLOADS

Copy and Paste Data

Back Page

Corresponding YT Video

Next Page

Additional comments including downloadable practice sheets, and additonal insturctions will be provided.

Email or comment in the chat if you need additonal support

Index

Overview

Beginner Levels

YouTube Settings

Table of contents for all Excel videos are provided on this page along with links and associated practice datasets.

How to change YouTube Settings and additional video viewing reconmmendations.

Videos for beginner levels one to three (25 min. each) with practice data to follow along.

Info

Pivot Tables Function

VLook Up Function

IF Function

This section is the most important out of all the videos. Highly used and should be reviewed on a detailed basis.

A video that demonstrates how to use the function. Also, index match video (3 min) as an alternative.

A video that demonstrates how to use the function.

Overview

01

05

Microsoft Excel Tutorial Beginners Level 1

How to VLookup in Differrent Workbook

06

02

Microsoft Excel Tutorial Beginners Level 2

How to Use IF Function in Excel (Step by Step)

03

Microsoft Excel Tutorial Beginners Level 3

DTE Interns! Make sure you have downloaded the Excel Files Folder, to follow along with the videos.

04

Excel Pivot Tables Tutorial for Beginners

YouTube Video Settings

Application

Screen Options

YouTube Settings

• Lower right corner of YouTube video is a gray gear
• Subtitles Feature: click on "CC" option

• Video quality: toggle with a lower frame rate if needed

• Playback: Adjust for faster/slower instruction

• Follow the time stamps
• Focus on practicing instead of recalling functions.

• Same for coding
• Apply not memorize every detail

• Split Screen View
• click on the widows icon
• located between FN and ALT key
• on the lower left of the keyboard

• Click on the left or right arrow
• Select the two screen views

Lesson 1 Microsoft Excel Beginners Level 1

Topics: Templates~Layouts~Formatting Cells~ Views~Formulas~Insert Function

Lesson 1

1:07 Starting up 1:27 Recent documents and pinning documents 1:54 Templates 2:34 Layout - Tabs, ribbons, and groups in Excel 3:48 Rows, Columns, Cells and Ranges 5:49 Worksheets in Microsoft Excel 6:52 View and zooming 7:30 Inputting Data 10:56 Formatting Data 13:38 Wrapping Text 14:22 Insert Row/ Merge & Center cells 15:41 Currency formatting 16:20 Print view 17:02 Add cell borders and colors 18:18 Basic formulas in Microsoft Excel 21:17 Copy formula 23:45 Freeze Pane 24:52 Basic Microsoft Excel functions 28:59 “Tell Me” in Microsoft Excel 29:43 Inserting charts in Microsoft Excel

Dataset used: 500 Top Grossing Movie xlsx.

• Click to play the video
• Click the lower right 'YouTube'
• Use the split screen and follow the tutorial

Lesson 2 Microsoft Excel Beginners Level 2

Topics: Sort~Autofil~Rounding~Cell Relationships

Lesson 1

Microsoft Excel Tutorial - Beginners Level 2

Downloads: 500 Top Grossing Movies xlsx.

• 0:00 Start
• 0:52 AutoFilling cells in Excel
• 4:18 Add your own autofill
• 5:49 Sorting data in Excel
• 8:56 Using filters in Microsoft Excel
• 12:45 Round functions in Excel
• 15:40 Relative Relative cell references
• 17:14 Absolute Relative cell references
• 18:55 Absolute Absolute cell references
• 20:17 Relative Absolute cell references

Lesson 3 Microsoft Excel Tutorial Level 3

Topics: Copy~ Special Paste~ Transpose~Format Cells~ Add Links

[Pre-Instructions] Lesson 2

Microsoft Excel Tutorial - Beginners Level 3

Day Online Units Sold In Store Avg. Price Revenue Monday 84 15 15 Tuesday 54 9 18 Wednesday 36 17 16 Thursday 48 8 22 Friday 62 10 14 Saturday 22 23 23 Sunday 11 19 18

REVENUE = Quantity * CostTOTAL REVENUE = REVENUE1+ REVENUE2 +....+ REVENUEn

1. Format the data above into a table

• (design and allignment in excel does not matter)

2. Change the format for the Price Column into dollars

• Use two decimal places

3. Create a formula to calulate the Revenue Column, copy the formula for everyday

• Figure out which column(s) are quantites

• Use the revenue formula for each quantity
• Calculate the total revenue
• Do not forget single brackets to inform excel the order Formula =(col1*col3)+(col2*col3)

Lesson 2

Microsoft Excel Tutorial - Beginners Level 3

• 0:00 Introduction
• 1:12 Simple copy/paste
• 3:23 Transpose paste in Excel
• 5:21 Copy and paste formulas in Excel
• 6:53 Paste a link
• 8:47 Special paste in Microsoft Excel
• 10:18 How to use Flash Fill in Excel
• 12:53 Different ways to use Flash Fill
• 15:28 Hyperlinks in Excel (link to websites)
• 19:16 Create a directory or table of

contents with internal links

Downloads: None

Lesson 3 Microsoft Excel Tutorial Beginners Level 4

Topics: Conditons~ Drop Menu~ Drop List ~Slicers

Lesson 3

Microsoft Excel Tutorial - Beginners Level 4

Download: Excel Practice Data Level 4

• 0:00 Introduction
• 1:04 What is Conditional Formatting in Excel?
• 1:44 How to use Conditional Formatting
• 12:18 What is Data Validation in Microsoft Excel?
• 12:53 How to create a drop-down menu in Excel
• 15:31 Create a drop-down list from a list
• 20:07 How does a slicer work in Microsoft Excel?
• 20:54 Add slicers to your worksheet

Lesson 5 Excel Pivot Tables Tutorial for beginners

~The most important video of this series~

Lesson 4

Excel Pivot Tables Tutorial for beginners

Download: Example Pivot Table

Lesson 5 VLOOK UP Function

~An alternative is called Index Match~

Lesson 5

How to VLookup in Differrent Workbook

Download: VLookUp in Different Workbook & Look Up Data

Lesson 6 IF (Conditonal) Function

Lesson 6

How to Use IF Function in Excel (Step by Step)

Download: None

Pre-Instructions

• Copy the the text on the left of this section before starting the video.

Time Stamps

• 0:00 How to Use IF Function in Excel 1:03 What is the Syntax
• of the IF Function
• 3:33 How to Copy the Function IF
• to the Rest of the Column
• 5:35 IF Function is Dynamic

Month Sales status Goal 60 January February 31 March 56 April 98 May 48 June 73 July 48 August 73 September 48 October 41 November 107 December 154

Extra Resources

Excel Cheat Sheet with shortcuts (Located to the left) Practice Data Resources Located above:

• Government (Energy) xlsx. format
• Kaggle (Energy) csv. format
• Duke Energy Initiative

The End