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.7 (31 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.
229 students enrolled
Take This Course
  • Lectures 42
  • Length 3 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
Wishlisted Wishlist

How taking a course works


Find online courses made by experts from around the world.


Take your courses with you and learn anywhere, anytime.


Learn and practice real-world skills and achieve your goals.

About This Course

Published 9/2015 English

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


  • 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


    • 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.

    What are the requirements?

    • Users should be familiar with Excel and entering data into Excel

    What am I going to get from this course?

    • 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

    Who is the target audience?

    • Excel users
    • People learning Excel

    What you get with this course?

    Not for you? No problem.
    30 day money back guarantee.

    Forever yours.
    Lifetime access.

    Learn on the go.
    Desktop, iOS and Android.

    Get rewarded.
    Certificate of completion.


    Section 1: Introduction
    Overview of Tables and Formulas in Excel

    Introduction to the Tables and Formulas with Excel course

    Section 2: Tables
    Table Section
    Introduction to Tables

    Download the Employee Master data file


    This lesson shows how to create and format a Table


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


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


    In this lesson we review how to filter dates in Tables


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


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

    Download the Sales Data file
    Practical Activity Tables

    The answers to the practical activity for Tables

    Section 3: Conditional Formatting
    Introduction to Conditional Formatting

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


    Learn to highlight the Top 10 items using Conditional Formatting


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


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

    Practical Activity Conditional Formatting

    The answers to the Practical Activity for Conditional Formatting activity

    Section 4: SUMIF, AVERAGEIF and COUNTIF Formulas
    Introduction to SUMIF Section

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


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

    Practical Activity SUMIF

    The answers to the SUMIF Practical activity exercise

    Section 5: Date Formulas
    Introduction to Date Formulas

    Learn to use the Year, Month and Day formulas


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


    Learn to use the NetworkDays and Workday formulas


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

    Download the Formula File

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

    Practical Activity Date Formulas

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


    The answers to Practical Activity number 2

    Section 6: Text Formulas

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

    Section 7: IF, HLOOKUP and VLOOKUP Formulas
    Introduction to IF, HLOOKUP and VLOOKUP Formulas

    Using the IF formula in calculations


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


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

    Section 8: Conclusion

    Conclusion to the course

    Students Who Viewed This Course Also Viewed

    • Loading
    • Loading
    • Loading

    Instructor Biography

    Ian Littlejohn, Excel Business Intelligence and Power BI Trainer

    Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and techniques making it easy for learners to harness the power of Excel PivotTables, PivotCharts, PowerPivot, Power Query, PowerView and Power BI.

    Ian has been training learners on these powerful technologies for a number of years making it easy for the business user 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 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.

    Ready to start learning?
    Take This Course