This course is a Project Based Learning course (PBL). This course is for students who wish to learn core skills in Microsoft Excel, and Access. Students work through hands-on projects to hone skills in data entry and management, formula creation, email management and a vast variety of commonly used email, spreadsheet, and database tools.

This course prepares students for the Microsoft Excel 2019 Associate certification.

Software Requirements

● Microsoft Excel 2019 and Microsoft Access 2019

o Note: Microsoft Access is only available on a PC. This section of the course REQUIRES a PC and is not Mac compatible.

o Students may use the online version of Microsoft Excel within O365. MS Access is not available as part of O365.

● Microsoft® Windows 7® or higher, or Mac® OS 10.3 Mavericks® or over operating system; 400 MHz or faster processor; 512 MB of memory (RAM); at least 2 GB of hard drive space; Adobe® Reader®

Course Outline

Excel to the Next Level

● Identify elements of an Office application window.

● Use the program interface.

● Create a blank workbook.

● Identify parts of an Excel workbook.

● Apply font formatting.

● Apply number formats.

● Apply cell formats from the Format Cells dialog box.

● Apply cell styles.

● Edit data in a cell.

● Enter data in a cell.

● Clear cell formatting.

● Differentiate among relative, absolute, and mixed references.

● Name a table.

● Insert Sparklines.

● Format text by using RIGHT(), LEFT(), and MID() functions.

● Format text by using UPPER(), LOWER(), and LEN() functions.

● Format text by using the CONCAT() and TEXTJOIN() functions.

● Use a mixed reference in a formula.

● Use a relative reference in a formula.

● Use an absolute reference in a formula.

● Reference a range in a formula.

● Reference a table in a formula.

● Use the Average function.

● Use the SUM function.

● Use the Max function.

● Use the MIN function.

● Use the Count functions.

● Use the If function.

● Create charts.

● Create chart sheets.

● Add data series to charts.

● Switch between rows and columns in source data.

● Add and modify chart elements.

● Apply chart layouts.

● Apply chart styles.

● Add alternative text to charts for accessibility.

● Differentiate between the Save and Save As commands.

● Close a workbook.

● Apply a cell border.

● Apply a cell fill.

● Apply a cell style.

● Arrange open Excel windows.

● Change the worksheet view.

● Open a second Excel window.

● Create a workbook based on a template.

● Open an existing workbook.

● Apply built-in conditional formatting.

● Remove conditional formatting.

● Preview a worksheet.

● Print a worksheet.

● Copy a selection.

● Move a selection.

● Name a range.

● Select a cell.

● Select a range.

● Fill cells by using Auto Fill.

● Paste a value.

● Paste column width.

● Paste transpose.

● Paste without formatting.

● Delete a cell.

● Delete columns or rows.

● Insert a cell.

● Merge vertically or horizontally.

● Use Merge & Center.

● Use Merge Across.

● Insert columns or rows.

● Identify compatible file formats.

● Import data from a .csv file.

● Import data from a .txt file.

● Search for data in a workbook.

● Use the Name box.

● Apply a theme.

● Create a custom header or footer.

● Insert a built-in header or footer.

● Insert a hyperlink to an external location.

● Insert a hyperlink within a workbook.

● Freeze a worksheet row or column.

● Remove a hyperlink.

● Set margins.

● Set page orientation.

● Set page size.

● Set print scaling.

● Add or remove a Quick Access Toolbar button.

● Enter more than one value for a property.

● Modify properties on the Info page.

● Use the Go To command.

● Configure page setup options for printing.

● Print an entire workbook.

● Print selected content.

● Protect a cell.

● Save a workbook in an alternative file format.

● Set a print area.

● Locate and correct accessibility issues.

● Locate and Correct Compatibility and Accessibility Issues.

● Locate and remove hidden properties or personal information.

● Mark a workbook as final.

● Navigate to named cells, ranges, or workbook elements.

● Protect the current worksheet.

● Protect the workbook structure.

● Apply a table style.

● Apply the None style.

● Convert a table to a range.

● Create a table from a range.

● Modify cell alignment, orientation, and indentation.

● Format cells by using Format Painter.

● Wrap text within cells

● Add or remove a table row or column.

● Adjust row height and column width.

● Apply row or column banding.

● Configure a total.

● Display and modify workbook content in different views.

● Emphasize the first and last column.

● Display formulas.

● Paste data by using special paste options.

● Identify a Header row.

● Insert a total row.

● Apply a basic filter.

● Explain the purpose of a filter.

● Explain the purpose of sorting.

● Sort by multiple columns.

● Sort by one column.

Success with Access

● Describe the purpose of a database.

● Close a database.

● Close a table.

● Create a blank database file.

● Create a table field.

● Identify elements of the Access window.

● Save a table.

● Add a record to a table.

● Change the data field type.

● Delete a record.

● Differentiate between a record and a field.

● Edit data.

● Select a record.

● Create a Quick Form.

● Differentiate between a table and a form.

● Enter a record in a form.

● Explain the purpose of a select query.

● Navigate through records in a form.

● Create a query using more than one table.

● Run a query.

● Save a query.

● Sort query results.

● Use the Query Wizard.

● Create a Quick Report.

● Explain the purpose of a report.

● Format a report.

● Print a report.

● Delete a database object.

● Export a table to a different format.

● Hide or display an object in the Navigation pane.

● Import Excel data into a database table.

● Create a relationship between tables.

● Delete a relationship.

● Explain the purpose of relating tables.

● Set a foreign key.

● Set a primary key field.

● Add a table description.

● Add a total row.

● Create a calculated field in a table.

● Filter records in a table.

● Find and replace data in a table.

● Sort records in a table.

● Use a calculated field in a query.

● Use comparison operators in a query.

● Use numeric criteria in a query.

● Use text criteria in a query.

● Add or modify a label on a form.

● Add, modify, or remove a control on a form.

● Customize a form header or footer.

● Insert a picture on a form.

● Set a form control property.

● Switch to Layout view.

● Add a control to a report.

● Add or modify a label on a report.

● Customize a report header or footer.

● Group or sort fields in a report.

● Insert a picture on a report.