Excel Pivot Tables - 1/2 day

Performance-Based Objectives

Lesson objectives help students become comfortable with the course, and also provide a means to evaluate learning. Upon successful completion of this course, students will be able to:

  • Create a PivotTable based on sales data and make the summary easier to read by grouping data and applying formatting.
  • Use PivotTables to answer questions about sales by category and region by month and use a chart to view the summary graphically. Students will also be able to use a PivotTable to summarize the performance of sales representatives.
  • Analyze the buying patterns of customers by using PivotTable and a variety of summary functions.
  • Use PivotTables to summarize the results of two different kinds of business surveys.

Course Content

Lab 1: Make it easy to read
Activity 1A: Begin with you raw data
Activity 1B: Create a PivotTable
Activity 1C: Group some data
Activity 1D: Apply some formatting
 
Lab 2: See sales trends
Activity 2A: What were sales by category by month?
Activity 2B: What were the trends?
Activity 2C: What products are doing well?
Activity 2D: How did each sales rep do?
 
Lab 3: Spot market patterns
Activity 3A: What did customers buy?
Activity 3B: Who were our biggest customers?
Activity 3C: What products do best in each region?
 
Lab 4: Analyze product surveys
Activity 4A: Are customers satisfied?
Activity 4B: What's the most important feature?
 
Functions Using Excel - 1/2 day
 

Objectives

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

  • Create formulas and perform calculations involving date and time.
  • Use Database functions and filters to extract information they need from a worksheet.
  • Do some data analysis using Excel’s Statistical functions.
  • Handle formulas that sometimes result in errors; use Excel’s auditing features.

Lab content

Lab Activity 1: Track elapsed time

  • Experiment with cell formats and dates
  • Use the Now function to keep the current date in a worksheet
  • Create formulas to calculate elapsed time
  • Use the Workday function to calculate project due dates

Lab Activity 2: Get the information you need from your Excel list

  • Use the Dsum function
  • Use the Dcount function
  • Use the Sumif function
  • Use the If function
  • Apply filters

Lab Activity 3: Simplify statistical analysis with functions

  • Use the Average, Median, and Mode functions
  • Use the Stdev function
  • Find highs and lows with the Max and Min functions
  • Count items in a list
  • Apply filters to a list

Lab Activity 4: Apply error-handling and auditing techniques

  • Use the Iserror function
  • Nest the Iserror function within the If function
  • Use Excel’s auditing features
Customize your reports using Access - 1/2 day

Objectives

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

  • Create and enhance subreports in two different report contexts.
  • Add a chart to a report and modify the chart by changing properties and using Microsoft Graph.
  • Add special data and text effects to a report.
  • Share Access reports with users who do not have Access installed.

 

Lab content

Lab Activity 1: Use subreports

  • Identify the main report
  • Create a query
  • Add the subreport
  • Enhance the reports
  • Try it on your own

Lab Activity 2: Add charts

  • Create a pie chart
  • Dress up the chart
  • Change the data source and chart type
  • Try it on your own

Lab Activity 3: Add special effects

  • Include parameter values
  • Have conditional text
  • Create a watermark
  • Use fancy text
  • Try it on your own

Lab Activity 4: Share reports

  • Export a snapshot
  • Share the snapshot
  • Use Snapshot Viewer
  • Put in on the Web
  • View the HTML version

Produce Lists, Letters and Labels in Access

Performance-based objectives

Objectives help students become comfortable with the Lab, and also provide a means to evaluate learning. Upon successful completion of this course, students will be able to:

  • Create a group of form letters in Word to people whose name and address information is stored in an Access database.
  • Use various techniques in Word and in Access to sort and select the records you need to merge with letters.
  • Create quick lists of Access data in Word documents.
  • Create a catalog in Word using data from an Access database and see how to prepare it for use on the Internet or an intranet.

Lab content

Lab Activity 1: Fast form letters

  • Begin with your Access data table
  • Create the form letter
  • Select the data source
  • Add merge fields to the main document
  • Merge the data and the letter

 Lab Activity 2: Query the data

  • Sort the merged form letters
  • Filter records
  • Change the data source to an Access query
  • Prompt the user for criteria

 Lab Activity 3: Quick lists

  • Publish data with Word
  • Insert a list of data
  • Keep a list up to date

 Lab Activity 4: Create a catalog

  • Set up the main document
  • Insert the fields and merge the data
  • Dress up the catalog
  • Use your catalog on the Web