Managing Data with Excel
Managing Data in Excel Training Manual
Sterling Business Advantage, LLC
What is Sterling Business Advantage?
We are an IT services provider focused on helping our clients save time and money by more effectively using the software they use to do their job. We provide training and help our clients solve software problems that prevent them from being effective at work. We develop software solutions to save time and money by automating business processes.
A Successful Project
Sandy is a financial analyst who spent all day every Monday and Tuesday downloading and importing timesheet data into Excel. Then she ran a series of macros she created to reformat and summarize the data to produce reports for the project manager and other stakeholders. This process worked but it was time-consuming and error-prone requiring extra diligence on her part to make sure everything transferred correctly.
We worked closely with her to produce a solution which automated the process using Microsoft Office. The resulting solution eliminated most of the manual steps and created the necessary reports in seconds. This reduced the total time she spent on the process from 16 hours per week (over 800 hours per year) to less than 15 minutes per week. This saved the project over $20,000 per year that could be used to create other, more detailed reports.
What repetitive tasks do you perform at your computer? We may have a solution available. We have developed solutions to automate invoices, calendars, email and reports.
Table of Contents
What is Sterling Business Advantage? 3
A Successful Project 3
Module One: Getting Started 7
Workshop Objectives 7
Module One: Overview/Perspectives 9
What is a database? 9
Common Database Terms 9
Examples of Excel Databases 10
Limitations of Using Excel as a database 10
Types of Data 10
Text Data 11
Numeric Data 11
Formulas 11
How Excel Stores Dates and Times 11
Module Two: Importing and Exporting Data 12
Getting Data Into your workbook 12
Getting data out of your workbook 14
Working with an External Database 15
Module Three: Setting up your data in Excel 16
Leave No Empty Rows 16
Leave No Empty Columns 16
Surround The Data List with a Blank Row and a Blank Column 16
Create the Table 16
Using the Database Tools 17
Expanding the Database 17
To complete the database formatting: 17
Module Four: Excel Lookup Functions 18
Working with Lookup Functions 18
Using a VLOOKUP Function 19
Dynamic searching using MATCH and INDEX 20
Module Five: Excel Database Functions 23
Module Six: Using Time Saving Tools 26
Using AutoFill 26
Using AutoComplete 29
Sorting Data 31
Filtering Data 33
Module Seven: Managing Your Data 39
Transposing Data from Rows to Columns 40
Using the Text to Columns Feature 41
Checking for Duplicates 44
Creating Data Validation Rules 46
Consolidating Data 49
Module Eight: Grouping and Outlining Data 51
Grouping Data 52
Adding Subtotals 53
Outlining Data 55
Viewing Grouped and Outlined Data 56
Module Nine: Working with Forms 58
About Excel Forms, Form Controls, and Active X Controls 58
Using a Data Form 61
Using a List Box Control 64
Using a Combo Box Control 67
Using a Spin Button Control 68
Using a Scroll Bar Control 69
Module Eight: Review Questions 71
Module Ten: Wrapping Up 72
Words from the Wise 72
Parking Lot 72
Lessons Learned 73
Completion of Action Plans and Evaluations 73
Managing your data in Excel (Case Study) 74