Skip to main content

Excel - Advanced Formulas and Functions

Course description:

Excel is a powerful tool for summarizing and presenting data. In this course, you will learn how to summarize and compare data for multiple entries using formulas and functions. You will also learn how to incorporate Excel items such as Named Ranges, Conditional Formatting and Wildcard Characters to formulas and functions. We will explore formatting text using functions, finding the number of days between two dates, and creating nested functions. Finally, we will discuss using VLOOKUP as a way to combine worksheets and find data.

What you should already know:

You should have an intermediate level of skill in Excel. You should be able to create spreadsheets, rows, columns, basic formulas, and basic functions such as SUM, AVERAGE, MAX and MIN. Additionally, you should have a familiarity with the Excel interface and understand how to AutoFill data.

What you will learn:

By the end of class, you will learn some more complex formulas functions such as VLOOKUP, IF statements, and more. Using practice files and activities, we will walk through how to use these statements to take some of the work out of updating and managing data in workbooks.

What you will need:

For this course, you will need a KU Online ID.

Course objectives:   

By the end of this course, participants will be able to:

  • Understand the basic structure of formulas and functions
  • Use logical functions such as IF to perform calculations on line items
  • Use conditional functions such as SUMIF to summarize specified data in a worksheet
  • Use text functions to combine, extract, transform, and trim text in cells
  • Use date and time functions, such as TODAY, YEAR, and DAYS, to calculate spans of time
  • Use LOOKUP, VLOOKUP, and HLOOKUP to automatically reference values contained on other worksheets or to combine worksheets
Category(s):
Office Productivity
Cost:
Handout:
No handout is available
How-to:
No how-to documentation is available

Prerequisites

We highly recommend that you attend any prerequisite workshops listed below since familiarity with the skills and concepts taught in them is essential to benefit fully from subsequent workshops.

However, if you have independently learned the skills and concepts of the required workshop(s), your "equivalent skills" will be accepted as a substitute for attending the prerequisite workshop(s).

To help you determine your skill level, click workshop titles below (if any) for descriptions which outline the topics taught in each workshop and that are assumed in later sessions. You are expected to know the commands and concepts listed within the descriptions for the prerequisite workshop. In most cases, you can also refer to workshop handouts and other helpful materials from the workshop description page.

In addition to any specific prerequisites listed, you should have, as a minimum, basic computing skills.

While we don’t have any required prerequisites, you will want to be comfortable with creating basic formulas, navigating a workbook, and using autofill to copy and paste cells within a column or row. These are topics covered in our Excel 2016 – Essentials course.