Tables and Formulas with Excel [2020 Edition]
4.4 (963 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
3,224 students enrolled

Tables and Formulas with Excel [2020 Edition]

Learn to use tables and create powerful formulas in Excel. Learn SumIF, Date, IF, VLOOKUP and Text formulas
4.4 (963 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
3,224 students enrolled
Created by Ian Littlejohn
Last updated 6/2020
English
English [Auto]
Current price: $20.99 Original price: $29.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • 12 articles
  • 5 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Easily create, manage and filter tables to answer key questions of your data
  • Learn the IF, HLOOKUP, VLOOKUP and other functions for powerful capabilities
  • 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 functions to easily calculate filtered data
  • Use Date formulas to calculate time / date intelligence
  • Use Text formulas to manipulate text entries
Requirements
  • Users should be familiar with Excel and entering data into Excel
Description

**** Updated Feb 2020 - This best selling Excel course has been updated ****

Recent reviews:

"This training was very helpful in upgrading knowledge in Excel with the help of practical exercises. I would definitely recommend this course."

"Excellent, efficient and informative course. The exercises after each set of videos was particularly useful."

"I found the course very informative. I am pretty good with excel but I learned quite a few tips and tricks that I can implement."

In this course you will learn how to use a number of formulas that will help you get your work done with less effort, as well as how to leverage the benefits of Excel tables.  You will also learn about using conditional formatting. 

This course is lead by Ian Littlejohn - an international trainer, consultant and data analyst with over 50 000 enrollments on Udemy and consistently high reviews.  Ian specialises in teaching data analysis techniques, Excel Pivot Tables, Power Pivot, Microsoft Power BI and Google Data Studio. 

**** Life time access to course materials and practice activities.  100% money back guarantee ****

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 you to practice the skills that you have been learnt on the course.

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

Tables

  1. How to create and format Tables

  2. How to easily filter data in text, numeric and date fields

  3. Easily Sum, Average, Count and use Max and Min in your tables

  4. 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 this course is for:
  • Excel users and people learning Excel
Course content
Expand all 54 lectures 03:16:21
+ Introduction
4 lectures 06:15

Introduction to the Tables and Formulas with Excel course.

Preview 02:48
Overview of Tables and Formulas in Excel
00:56
Download the Training Data Files
00:04
+ Tables
11 lectures 39:23
Introduction to Tables
00:15

This lesson shows how to create and format a Table.

Preview 07:03

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

Preview 04:36

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

Filtering Numeric Data in Tables
04:57

In this lesson we review how to filter dates in Tables.

Filtering Dates in Tables
04:33

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

Aggregating Numeric Data in Tables
05:07

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

Using Slicers to Filter Data in Tables
04:43
Download the Sales Data file
00:04
Practical Activity Tables
00:37

The answers to the practical activity for Tables.

Practical Activity Tables Answers
06:31
+ Conditional Formatting
7 lectures 27:21
Introduction to Conditional Formatting
01:13

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

Highlight According to Cell Rules
05:08

Learn to highlight the Top 10 items using Conditional Formatting.

Conditional Formatting - Top 10 Analysis
04:08

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

Data Bars, Color Scales and Icons
05:06

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

Using Manage Rules
05:22
Practical Activity Conditional Formatting
00:30

The answers to the Practical Activity for Conditional Formatting activity.

Practical Activity - Conditional Formatting Answers
05:54
+ SUMIF, AVERAGEIF and COUNTIF Formulas
7 lectures 27:55
SUMIF Section
01:14
Introduction to SUMIF Section
01:00

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

SUMIF, AVERAGEIF and COUNTIF Formulas
07:45

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

SUMIFS, AVERAGEIFS and COUNTIFS Formulas
05:26
Additional Formulas
05:23
Practical Activity SUMIF
00:22

The answers to the SUMIF Practical activity exercise.

Practical Activity - SUMIF Formula Answers
06:45
+ Date Formulas
12 lectures 48:35
Introduction to Date Formulas
01:33
Date Formulas Section
01:48

Learn to use the Year, Month and Day formulas.

Year, Month and Day Formulas
07:51

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

WeekNum and WeekDay Formulas
04:14

Learn to use the NetworkDays and Workday formulas.

WorkDay and NetWorkDays Formulas
04:29

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

Date and Today Formulas
03:41
Download the Formula File
00:03

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

How to Correct a Date Field in Excel
08:21
DateDif Formula
05:54
Practical Activity Date Formulas
00:25

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

Practical Activity - Date Formulas - Answers 1
04:10

The answers to Practical Activity number 2.

Practical Activity - Date Formulas - Answers 2
06:05
+ Text Formulas
2 lectures 06:47
Introduction to Text Formulas
01:22

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

Working with Text Formulas
05:25
+ IF, HLOOKUP and VLOOKUP Formulas
5 lectures 20:16
Introduction to IF,HLOOKUP and VLOOKUP
01:05
IF, HLOOKUP and VLOOKUP Formulas Section
00:45

Using the IF formula in calculations.

The IF Formula
04:17

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

The VLOOKUP and HLOOKUP Formulas
08:20

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

An Example of VLOOKUP Formula
05:49
+ Additional Formulas
5 lectures 19:22
Introduction to Additional Formulas
00:35
EOMONTH
01:50
RANK
01:40
SUBSTITUTE and SEARCH
04:50
MATCH and INDEX
10:27
+ Conclusion
1 lecture 00:24

Conclusion to the course.

Conclusion
00:24