Microsoft Excel Course outline

Navigating Spreadsheets with Confidence and Speed

• Understand spreadsheet terminology, including worksheets, workbooks, columns, rows and cells.
• Work efficiently with Excel's tool ribbons, gallery commands, and Live Preview feature.
• Master shortcuts to quickly navigate through multiple pages of data.

 

Entering and Editing Text and Formulas

• Use AutoFill to complete the entry of sequential data with a minimum of errors.
• Perform basic calculations and use cell references to apply calculations accurately.
• Understand and use cell references to apply calculations accurately throughout a spreadsheet.
• Search for specific data and replace data selectively or globally.

 

Formatting Cells and Tables For Clarity and Visual Impact

• Use styles and style galleries to quickly format cells, tables and entire worksheets.
• Apply conditional formatting rules to visually enhance your data.
• Automatically display numbers as percentages, currencies, fractions, dates or in the format of your choice.

 

Printing Spreadsheet Data for Professional Appearance

• Avoid multiple printing attempts by adjusting margins and sizing in Page Layout View.
• Control page breaking and print options to print the desired number of pages.
• Define the print area to exclude unwanted data from your printout.

 

Creating Professional-Looking Charts

• Create dynamically-updating charts quickly with predefined layouts and styles.
• Modify chart components manually to graphically display information, to meet your specific requirements.
• Easily change chart types, without modifying the data.
• Use visual effects to design compelling graphic summaries and accentuate key data.

 

Managing and Navigating Large Workbooks

• Freeze row and column headings in place so that they will always appear when scrolling.
• Divide a larger worksheet into smaller, more manageable panes.
• Hide columns and rows, as needed, to emphasize key data.
• Define page breaks at logical points in your spreadsheet.

 

Our outlines are a guide to the content covered on a typical course. We may change or alter the course topics to meet the objectives of a particular course.

---

Microsoft Excel Course outline

Linking Workbooks to Manage Large Interconnected Data Sets

• Use 3-D formulas to link multiple workbooks.
• Reference cells and ranges in other workbooks accurately ... on the first try!
• Implement best practices for easy maintenance of linked workbooks.

 

Formatting Techniques: Beyond the Basics

• Combine functions and styles to format text.
• Automatically standardize the display format of numbers and text.
• Apply a theme for a consistent appearance between spreadsheets and other Microsoft Office documents and presentations.
• Quickly convert a row into a column and vice versa.
• Add background images and watermarks to a spreadsheet.

 

Managing and Manipulating Table Data

• Consolidate lists into collapsable outlines for simplified viewing.
• Target specific data for analysis with sorting and filtering
• Create powerful data filters with multiple criteria.
• Maintain data integrity with data validation and structured references.

 

Using Advanced Chart and Graphing Options

• Understand the powerful (and complex) chart formatting options.
• Create custom charts which combine two or more chart types.
• Make important features in charts stand out by including graphic elements.

 

Auditing, Collaborating and Securing Spreadsheet Data

• Test formulas and trace errors with Error Checking and Auditing tools.
• Include cell comments for increased usability and collaboration.
• Prevent accidental or deliberate data deletion by protecting specific portions of a workbook.
• Permit multiple users to edit a spreadsheet simultaneously, without creating inconsistencies.

 

Creating, Modifying and Using Excel Templates and Settings

• Quickly find the best built-in spreadsheet template for your needs.
• Design Excel templates with scalability and usability in mind.
• Understand how application settings affect Excel's functioning.

 

Our outlines are a guide to the content covered on a typical course. We may change or alter the course topics to meet the objectives of a particular course.

---

 

Advanced Functions in Excel

• Automate your work with logical functions (IF, AND, OR, NOT, IFERROR) that calculate values based on criteria you specify
• Apply math and statistical functions to calculate conditional sums and averages
• Use financial functions, such as the PMT function, which calculates loan payment amounts

 

V Lookups and Data Tables in Excel

• Create lookup functions to find exact or approximate matches in lists of data
• Use the MATCH function to determine a specified value's relative position in a list
• Use the INDEX function to return a value based on a specified cell in a range
• Create "what-if" scenarios with one-variable and two-variable data tables

 

Advanced List Management in Excel

• Set up cells to accept only valid dates, whole numbers, or other valid values you specify
• Use database functions to summarize values that meet complex criteria, for example, the total sales in a specific region for a specific year

 

Excel PivotTables and PivotCharts

• Create and use interactive PivotTables to analyze and compare large amounts of data from your workbooks or from external databases
• Make a PivotTable easier to read with effective automatic formatting
• Create a PivotChart to graphically display PivotTable data

 

Exporting and Importing within Excel

• Export your Excel data to a text file that can be read by other programs
• Import data from a text file and organize it automatically into appropriate columns
• Easily remove duplicate rows from your imported data
• Create an XML map that maps your Excel data to an XML schema
• Use Microsoft Query to access data in external databases
• Set up a Web Query to automatically pull data such as online currency quotes or stock quotes from the web

 

Analytical Options

• Perform a simple one-variable "what-if" analysis using the Goal Seek utility
• Perform complex "what-if" analyses with multiple variables using the Solver utility
• Expand your data analysis capabilities with the tools in the Analysis ToolPak, including Random Number Generation, Histogram, Regression, Sampling tools and many more
• Simplify your budget planning by creating and easily switching between Scenarios
• Save time by saving Views with different worksheet display and print settings

 

Macros & Custom Excel Functions

• Determine when a macro makes sense
• Record and run macros that work for you
• Enable and disable macros in a spreadsheet
• Make changes to existing macros using the Visual Basic Editor
• Create custom functions for your business, for example, a Profit function with sales and cost arguments

 

Conditional Formatting and SmartArt

• Use data bars or icon sets to graphically display how different cell values compare to each other
• Communicate with color by setting up color scales to automatically format cells depending on their values
• Get your message across by inserting and customizing SmartArt graphics

 

Our outlines are a guide to the content covered on a typical course. We may change or alter the course topics to meet the objectives of a particular course.