BIG DATA! Excel’s Information Management and Manipulation – Returning in 2021!

Course Description

Overview

This is an intermediate course that teaches you how to control data without data controlling you.   You will learn how to download and create databases and tables in Excel. You will accomplish multiple sorting and filtering using a variety of criteria. Retrieve the information you need quickly and easily using multiple filters and sorting. You will be able to format your table and databases in a professional manner. Plus, you will master the terrific Name Box and Name Manager feature of Excel.  We will learn how to separate data into different columns based on one column using the Text-to-Column Excel feature. We will work with functions, such as VLOOKUP and CONCATENATE.

Register for your class here. Thank you!

Prerequisites

To ensure your success, we recommend you first take the following courses or have the equivalent knowledge:

  • Excel Fundamentals, including formatting, such as merge and center and wrap-text
  • Excel: The 5Fs
  • Comfort with navigating and working with multiple worksheets in Excel
  • Know how to construct SUM, COUNT, AVERAGE, and other functions

Objectives

Upon successful completion of this course, students will be able to:

  • Sort data using multiple criteria,
  • Filter data to get to the information you need with little effort,
  • Download databases from other applications and servers,
  • Create your data table or database correctly,
  • Know important gotcha’s with Excel databases and tables,
  • Format your table in professional and attractive ways,
  • Use the Name Box and Name Manager to efficiently navigate, print, select, and add other functionality for your database and tables,
  • Create the CONCATENATE function to bring columns together and VLOOKUP to bring data from one table into another,
  • Employ Text to Columns to separate data from one column into multiple columns, 

Course Content

Lesson 1: Database/Data Tables Guidelines and Differences

  • Flat File Databases vs Relational Databases
  • Importing a Database from Access into Excel
  • Format as a Table

Lesson 2: Name Box and Name Manager

  • Naming cells and tables in Excels
  • Using Names for referencing, selection, and values
  • Editing and reusing Names

Lesson 3: Filters and Sorts

  • Using an Excel Database to manipulate data with filters and sorts
  • Filters – Top 10, Numbers, Text, and Date Filters
  • Using Custom Views to save your Filter and Database View
  • Using the AutoCalculation area of Excel based on filtered data
  • Sorting – One column sort and multiple sorting

Lesson 4: Advance Filter

  • Creating an Advance Filter criteria area
  • Setting multiple criteria for a Filter
  • Using date ranges and wildcard characters for the filter

Lesson 5: Database Functionality and Functions

  • Removing Duplicates
  • Combining columns using the CONCATENATE function
  • Separating columns using the Text to Columns feature
  • Exploring the VLOOKUP function for databases

Register for your class here. Thank you!