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.8 (25 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.
201 students enrolled
$19
$20
5% off
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

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

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:

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.

    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

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

    Curriculum

    Section 1: Introduction
    Overview of Tables and Formulas in Excel
    Article
    04:52

    Introduction to the Tables and Formulas with Excel course

    Section 2: Tables
    Table Section
    Article
    Introduction to Tables
    Article
    Article

    Download the Employee Master data file

    04:32

    This lesson shows how to create and format a Table

    03:50

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

    04:02

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

    03:18

    In this lesson we review how to filter dates in Tables

    06:14

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

    04:53

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

    Download the Sales Data file
    Article
    Practical Activity Tables
    Article
    06:49

    The answers to the practical activity for Tables

    Section 3: Conditional Formatting
    Introduction to Conditional Formatting
    Article
    05:23

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

    03:30

    Learn to highlight the Top 10 items using Conditional Formatting

    04:02

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

    07:54

    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
    Article
    06:04

    The answers to the Practical Activity for Conditional Formatting activity

    Section 4: SUMIF, AVERAGEIF and COUNTIF Formulas
    Introduction to SUMIF Section
    Article
    09:45

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

    08:29

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

    Practical Activity SUMIF
    Article
    08:42

    The answers to the SUMIF Practical activity exercise

    Section 5: Date Formulas
    Introduction to Date Formulas
    Article
    08:47

    Learn to use the Year, Month and Day formulas

    05:40

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

    04:50

    Learn to use the NetworkDays and Workday formulas

    03:53

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

    Download the Formula File
    Article
    11:22

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

    Practical Activity Date Formulas
    Article
    04:46

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

    07:05

    The answers to Practical Activity number 2

    Section 6: Text Formulas
    05:45

    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
    Article
    04:59

    Using the IF formula in calculations

    07:09

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

    05:18

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

    Section 8: Conclusion
    00:51

    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