Tables and Formulas with Excel
4.5 (42 ratings)
318 students enrolled
Wishlisted Wishlist

# Tables and Formulas with Excel

Learn to use Tables and create powerful formulas in Excel. Learn powerful SumIF, Date, IF, VLOOKUP and Text formulas
4.5 (42 ratings)
318 students enrolled
Created by Ian Littlejohn
Last updated 2/2017
English
Current price: \$20 Original price: \$30 Discount: 33% off
30-Day Money-Back Guarantee
Includes:
• 2.5 hours on-demand video
• 14 Articles
• 1 Supplemental Resource
• Access on mobile and TV
• Certificate of Completion
What Will I Learn?
• Easily filter Tables and ask questions of their data
• Easily aggregate data in Tables using Sum, Count, Average, Max and Min
• Use Conditional Formatting to highlight data according to a range of conditions
• Learn to use Data Bars, Color Scales and Icons in Spreadsheets
• Use the SUMIF, COUNTIF and AVERAGEIF formula to easily calculate filtered data
• Use Date formulas to calculate time / date intelligence
• Use Text formulas to manipulate text entries
• Learn the IF, HLOOKUP and VLOOKUP for powerful capabilities
View Curriculum
Requirements
• Users should be familiar with Excel and entering data into Excel
Description

Welcome to this course on Tables, Conditional Formatting and Excel formulas. This course is for all Excel users who want to take their Excel skills and capabilities to the next level.

This course will increase your productivity as you use tables, conditional formatting and formulas with ease in your Excel spreadsheets. The course is presented in an easy to follow step by step approach. The course also includes practical activities for the student to practice the skills that have been learnt in the course.

The course will provide you with a comprehensive understanding of the following:

Tables

• How to create and format Tables
• How to easily filter data in text, numeric and date fields
• Easily Sum, Average, Count and use Max and Min in your tables
• Use Slicers to easily filter Table data
• Conditional Formatting

• How to apply conditional formatting rules
• How to easily highlight the Top 10
• Learn to use Data Bars and Color Scales
• Learn to apply icons

Formulas

• Learn SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF and COUNTIFS
• Learn a range of Date formula such as Year, Month, WeekDay, NetWorkDays amd WorkDay
• Learn Text formulas such as LEFT, MID, RIGHT and TRIM
• Learn to use the IF formula to create powerful 'IF Then' logic
• Learn the HLOOKUP and VLOOKUP

This course is presented in Excel 2013. A minimum of Excel 2007 is required to be able to work with the Tables, Conditional Formatting and some of the formula functions.

Who is the target audience?
• Excel users
• People learning Excel
Curriculum For This Course
42 Lectures
02:50:36
+
Introduction
2 Lectures 05:55
Overview of Tables and Formulas in Excel
01:03

Introduction to the Tables and Formulas with Excel course

Preview 04:52
+
Tables
12 Lectures 34:45
Table Section
00:00

Introduction to Tables
00:18

00:07

This lesson shows how to create and format a Table

Preview 04:32

In this lesson we review how to filter text fields in Tables.

Preview 03:50

In this lesson we show how to easily filter numeric fields in Tables

Filtering Numeric Data in Tables
04:02

In this lesson we review how to filter dates in Tables

Filtering Dates in Tables
03:18

In this lesson we cover how to easily aggregate data using Sum, Count, Average, Max and Min

Aggregating Numeric Data in Tables
06:14

Learn how to use Slicer to easily filter data in Excel Tables

Using Slicers to Filter Data in Tables
04:53

00:04

Practical Activity Tables
00:37

The answers to the practical activity for Tables

06:49
+
Conditional Formatting
7 Lectures 27:56
Introduction to Conditional Formatting
00:32

In this lesson we cover how to highlight cells according to different rules

Highlight According to Cell Rules
05:23

Learn to highlight the Top 10 items using Conditional Formatting

Conditional Formatting - Top 10 Analysis
03:30

In this lesson we cover how to use data bars, color scales and icons with conditional formatting

Data Bars, Color Scales and Icons
04:02

In this lesson you will learn how to use the Manage Rules interface to be able to create conditional formatting rules

Using Manage Rules
07:54

Practical Activity Conditional Formatting
00:31

The answers to the Practical Activity for Conditional Formatting activity

Practical Activity - Conditional Formatting Answers
06:04
+
SUMIF, AVERAGEIF and COUNTIF Formulas
5 Lectures 28:43
Introduction to SUMIF Section
01:21

In this lesson you will learn how to use the SUMIF, AVERAGEIF and COUNTIF formulas

SUMIF, AVERAGEIF and COUNTIF Formulas
09:45

In this lesson we learn how to use the SUMIFS, AVERAGEIFS and COUNTIFS formulas

SUMIFS, AVERAGEIFS and COUNTIFS Formulas
08:29

Practical Activity SUMIF
00:26

The answers to the SUMIF Practical activity exercise

Practical Activity - SUMIF Formula Answers
08:42
+
Date Formulas
10 Lectures 49:03
Introduction to Date Formulas
02:01

Learn to use the Year, Month and Day formulas

Year, Month and Day Formulas
08:47

In this lesson you will learn to use the WeekNum and WeekDay formulas

WeekNum and WeekDay Formulas
05:40

Learn to use the NetworkDays and Workday formulas

WorkDay and NetWorkDays Formulas
04:50

In this lesson you will learn about the Date and Today formulas

Date and Today Formulas
03:53

00:03

Learn how to correct dates that have not been correctly entered into Excel

How to Correct a Date Field in Excel
11:22

Practical Activity Date Formulas
00:36

The answers to the first part of the practical activity for Date formulas

Practical Activity - Date Formulas - Answers 1
04:46

The answers to Practical Activity number 2

Practical Activity - Date Formulas - Answers 2
07:05
+
Text Formulas
1 Lecture 05:45

In this lesson you will learn how to work with Text formulas

Working with Text Formulas
05:45
+
IF, HLOOKUP and VLOOKUP Formulas
4 Lectures 18:15
Introduction to IF, HLOOKUP and VLOOKUP Formulas
00:49

Using the IF formula in calculations

The IF Formula
04:59

In this lesson we review how to use HLOOKUP and VLOOKUP formulas

The VLOOKUP and HLOOKUP Formulas
07:09

In this lesson we review an example of the VLOOKUP formula being used

An Example of VLOOKUP Formula
05:18
+
Conclusion
1 Lecture 00:51

Conclusion to the course

Conclusion
00:51