« Back to Class Listing

Microsoft Excel: Data Management and Manipulation

Description


Control data without data controlling you!

In this half-day course, we will discuss how Excel can be used to manage and manipulate data in Excel, both in flat file databases and relational databases. Create and import databases and tables in Excel while handling multiple sorting and filtering tasks using a variety of criteria. We will also discuss how to format tables and databases in a professional manner while using Excel’s Format as Table feature.

Discover how to retrieve the information you need quickly and easily using multiple filters and custom views. We will also employ Excel’s terrific Name Box and Name Manager features to make data clearer and easier to reference.

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.

Audience: We designed this course for current, experienced users of Excel who want to know more about managing and manipulating data

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

After completing this course, participants will be able to:

  • Sort and filter data using multiple criteria in order to get needed information with little effort
  • Download databases from other applications and servers
  • Create a data table or database avoiding common pitfalls
  • Format a professional and attractive table
  • 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
  • Employ Text to Columns to separate data from one column into multiple columns

Section: MS-EXCEL-MGMT (25AUG17)
Dates: Aug 25, 2017 - Aug 25, 2017 Time: 9:30 AM
Tuition: $185.00 Instructor: Greg Creech Inquire

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

    Lesson 1: Database 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

Class Schedule

Date Times Meeting Type Location
08/25/2017 9:30 AM - 12:30 PM Classroom Executive Park

Additional Information

Final Enrollment:
 
Clock Hours: 3.0