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 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.
Lesson 1. Enhancing the Excel Database and Tracking systems with the Name Box,
Name Manager, and Data Validation
Lesson 2. Tracking and Database Advance Functions
Lesson 3. PivotTables for Account Management, Sales, HR, Finances, and Marketing Demographic Analysis
Lesson 4. PivotTables with Calculating Items and Fields
Lesson 5. Time Saving Macros
Lesson 6. Charts - Part 2
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