Tables and Formulas with Excel
4.6 (35 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.
265 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Tables and Formulas with Excel to your Wishlist.

Add to 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.6 (35 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.
265 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
  • Full lifetime access
  • Access on mobile and TV
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
    Expand All 42 Lectures Collapse All 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

    Download the Employee Master data file

    Download the Employee Master File
    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

    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: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

    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
    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
    About the Instructor
    Ian Littlejohn
    4.4 Average rating
    1,249 Reviews
    8,503 Students
    11 Courses
    Excel Power Tools, Power BI and Google Data Studio Trainer

    Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and Google Data Studio.  Ian is an experienced trainer that teaches techniques and tools making it easy for learners to harness the power of Excel PivotTables, PowerPivot, Power Query,  Power BI and Google Data Studio.

    Ian has been training learners on these powerful technologies for over 10 years making it easy for business users to easily create management reports, develop interactive dashboards and generate new insight and intelligence from business data.

    Ian has over 10 years of Management Consulting experience and he brings this knowledge and skills to his training course showing Excel and Google Data Studio users how to easily create sophisticated management reports, perform data analysis and create amazing interactive dashboards without using any complex programming or specialized tools.

    Ian has consulted and worked with major organizations in the Banking, Insurance, Manufacturing, Telecommunications and Logistics industries across a number of countries and continents.