« Back to Class Listing

Microsoft Excel Level 3: More Excelling at Excel

Description

Microsoft Excel has hundreds of features, functions, formulas, and other things that can make managing your data easier. In this course, we will discover how to formulate smarter worksheets by understanding Excel’s time format and using Date Calculations such as TODAY and YEAR.

We will also learn to use Data Validation criteria to keep data accurate and streamline input with dropdown lists. From there, we will construct logical comparisons between given and expected values using IF…THEN…ELSE statements to make data more readable, locate information in a workbook, and avoid duplicate entries.

We will use advanced functions to repackage data into more useful forms—such as correctly formatted addresses—using the CONCATENATE function to pull data together from two or more columns. Split data using delimiters and the Text to Columns feature to deconstruct names, dates, and other data into more useful formats.

We will also go more in-depth with PivotTables, saving time with macros, and how to create the most useful types of charts for presenting your data.

After completing this course, you will be able to:

  • Establish criteria using Data Validation to more easily search for data
  • Record macros for repetitious actions that save time and assign the macro to keyboard shortcuts
  • Create multiple IF-THEN-ELSE statements for more efficient functions
  • Develop functions—COUNTIFS, SUMIFS, AVERAGEIFS, DSUM, DCOUNT, and DAVERAGE—that give results based on criteria
  • Develop formulas and functions in Excel for tables and databases
  • Construct advanced functions to find and display data with VLOOKUP/HLOOKUP and IF-THEN-ELSE.
  • Create the CONCATENATE function to bring columns together
  • Employ Text to Columns to separate data from one column into multiple columns

This class is taught using Microsoft Excel 2016 on our lab PCs, but the content applies to Excel 2007, 2010, and 2013 for Windows. Material learned in this course may not apply to Excel for Mac.

Section: MS-EXCEL-LVL3 (09MAY18)
Dates: May 9, 2018 - May 9, 2018 Time: 9:00 AM
Tuition: $375.00 Instructor: Greg Creech Register
Lesson 1. Enhancing the Excel Database and Tracking systems with the Name Box, Name Manager, and Data Validation
  • Adding Data Validation, Message boxes, and restrictions to the database and worksheets
  • Creating Drop Down Lists and customizing lists for ease of input and selection
  • Calculate values from a database table

Lesson 2. Tracking and Database Advance Functions
  • Removing duplicates
  • Date Calculations using the Today() and Year() functions
  • Database functions based on criteria: DSUM, DAVERAGE, DCOUNT
  • IF…THEN…ELSE functions
  • SUMIF, COUNTIF, and AVERAGEIF
  • Combining and separating columns using CONCATENATE and Text to Columns
  • VLOOKUP and HLOOKUP functions for tables

Lesson 3. PivotTables for Account Management, Sales, HR, Finances, and Marketing Demographic Analysis
  • Quickly analyze data based on multiple criteria
  • Create a PivotChart to easily visualize PivotTable data
  • Filter data with slicers and Report Filter pages
  • Analyze products and services by customer and territories
  • Create summaries with COUNT, AVERAGE, and SUM functions
  • Modify a data source to incorporate into a PivotTable

Lesson 4. PivotTables with Calculating Items and Fields
  • Recognize important restrictions in using calculated items and fields
  • Calculate data items within a field
  • Create calculated fields without changing your data source

Lesson 5. Time Saving Macros
  • Macros for changing formatting such as fonts, shading, borders, and more
  • Macros for sorting
  • Macros for page-setup and printing

Lesson 6. Charts – Part 2
  • Creating Comparative Data Charts including trend lines and bar, column, area, line, and other charts

Class Schedule

Date Times Meeting Type Location
05/09/2018 9:00 AM - 4:30 PM Classroom Executive Park

Additional Information

Final Enrollment: May 9, 2018
Clock Hours: 6.5