Microsoft Excel Level 3: Excelling At Excel
Further sharpen your Excel skills
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.
This course is offered in a HyFlex format. Students can attend online or in-person at our Century Center location. Please register for the option that best suits your needs.
Course Outline
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
Learner Outcomes
After completing this course, participants 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
| Type | Date | Time | Location |
|---|---|---|---|
| TBA | TBA | TBA | TBA |
Microsoft Excel Level 3: More Excelling At Excel
Contact Hours
6.5 hours
Course Fee(s)
Tuition non-credit - $375.00
Instructor(s)
- Greg Creech