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:
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.
Download the Employee Master data file
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
The answers to the practical activity for Tables
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
The answers to the Practical Activity for Conditional Formatting activity
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
The answers to the SUMIF Practical activity exercise
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
Learn how to correct dates that have not been correctly entered into Excel
The answers to the first part of the practical activity for Date formulas
The answers to Practical Activity number 2
In this lesson you will learn how to work with Text 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
Conclusion to the course
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.