Microsoft Excel Training Courses in Houston

Excel: Level 1

Excel: Level 1

Course Content:

Lesson One: Getting Started with Excel

Learn your way around the Excel window, the basics of creating and saving Excel files, and how to view and arrange worksheets for ease of use. Learn the basics of Rows and Columns – the structure through which Excel stores your information – and how to manage them. Explore techniques for quick entry and basic calculations.
Details:

  • Become familiar with the various elements of the Excel window and their functions, including the ribbon, tabs, galleries, row and column indicators, sheet tabs, and more.
  • Access the help files in Excel.
  • Establish global application settings.
  • Create a new blank workbook, create a new workbook based on a template, open an existing file, and manage workbook information.
  • Become familiar with various Excel file types.
  • Learn how to save a workbook, use the Compatibility Checker to check file compatibility when saving your workbook to an earlier version of Excel, and recover an unsaved workbook.
  • Make your workbooks easier to use by renaming your sheet tabs. You can also insert and delete sheets.
  • Excel organizes data in rows and columns. Learn how to select, insert and delete, resize rows and columns, and freeze and unfreeze panes.
  • Learn how to select cells and ranges, enter data in a single cell and multiple cells, move and copy data, delete data, and use AutoFill to copy data or enter a series of data.
  • Become familiar with the Quick Analysis Tool and instantly analyze your data.
  • Use Find and Select to edit contents across a workbook.
  • Become familiar with the Excel Status Bar to quickly view data information.
  • Become familiar with document views, workbook views, and page layouts to change the display of your spreadsheet.
  • Learn how use the Office Clipboard.
  • Review the cut, copy, and paste commands as well as the paste options.
  • Use the Apps for Office feature to insert an application in to your worksheet and enhance your content.
  • Become familiar with printing options.
Lesson Two: Formatting in Excel

Explore a variety of techniques for giving data a visually attractive, easy-to-understand appearance. Place emphasis on data in your worksheet to make it easier to find.
Details:

  • Enhance the look of your worksheet by applying formats. Once you find a format you like, you can copy the format to other cells using the Format Painter.
  • Learn how to use the Mini Toolbar to quickly apply formatting features to your content.
  • Work with the Office Live Preview feature to preview formatting options before they are applied.
  • Become familiar with cell border and fill options to draw focus on data in your worksheet.
  • Apply text alignment and cell number formats to your data and use the Format as Table feature.
  • Apply styles, create a new cell style, and merge cell styles to your worksheet cells to highlight or add appeal to your data.
  • Apply a theme to your spreadsheet, edit a theme, and save a theme.
  • Set up header and footer elements on your worksheet.
  • Use the Auto Fill features to automatically fill a row or column with data and use Flash Fill to reformat or split data.
  • Use the Quick Analysis tool to instantly conditionally format data in your spreadsheet.
  • Create an Excel file using a template, modify a template, or create your own custom template.
  • Work with the Excel screenshot feature to capture a picture of an open window and place it in your Excel document.
Lesson Three: Getting Started with Formulas in Excel

Create basic formulas applying the correct structure to your data. Create formulas using the function libraries of Excel.
Details:

  • Formulas allow you to develop a wide variety of data from simple addition to complex calculations. In this lesson, you will become familiar with creating formulas using the Formula bar, AutoSum, Formulas ribbon, and the Insert Function dialog box.
  • Use the Quick Analysis tool to instantly add totals to data in a spreadsheet.
  • Simplify creating a formula by using the built-in functions in Excel.
  • Use relative and absolute references in your formulas.
  • Learn about using named ranges and using structured references for formulas in your table.
  • Become familiar with common errors in Excel formulas and how to fix them.
Lesson Four: Working with Multiple Worksheets in Excel

While data are routinely stored on a single worksheet, Excel does not limit your calculations to that sheet. Use a variety of methods to collect, calculate, and present information from many sheets simultaneously in the format you choose.
Details:

  • Group your worksheets and use the Fill Across Worksheets feature to quickly copy worksheet data to multiple worksheets. You can also link information between worksheets to update the copied data whenever the source data has changed.
  • Work with your worksheet tabs and move and copy worksheets to new and existing workbooks. Hide and unhide your worksheet tabs.
  • Customize the tabs in your workbook by giving each worksheet a different tab color.
  • Create formulas and functions that contain references to cells that are on separate worksheets or workbooks.
  • The Consolidate feature enables you to summarize multiple worksheets that contain related lists into one location. You can choose to display a total of each item or use another function to calculate the data in a different way.
Lesson Five: Working with Charts in Excel

Charts allow you to present numeric information in an attractive, easily edited, and highly customizable graphical form. Choose one of Excel’s many built-in styles to present your information for maximum impact, and display the impact of trends or forecasts according to data you choose.
Details:

  • Create a chart and use the Quick Analysis tool to instantly chart data in a spreadsheet.
  • Learn how to quickly create an appropriate chart based on the selected data of a work sheet using Recommended Charts.
  • Format chart elements and use the Quick Format buttons to format and design your chart.
  • Modify your charts by including new data ranges, editing the labels, including a title, and adding data labels.
  • Add appeal to your charts by formatting the font, color, border or pattern of different chart objects such as the axis and data series.
  • Add trend lines to your charts to display trends or forecast future results.
  • Select from a wide variety of chart types to display your information in the best possible format. Create Sparkline and Combo.
  • Become familiar with all the options related to formatting chart data labels.
  • Save your custom chart as a template for reuse.
Excel: Level 2

Excel: Level 2

Course Content:

Lesson One: Functions in Excel

Explore Excel’s wide variety of built-in functions in the function libraries.
Details:

  • Explore the Statistical functions of Average, Min, Max, and Count.
  • Dates and times are values that can be added, subtracted, or used in other calculations. The Today function displays the current date in your worksheet.
  • Use Logical (IF, AND, OR, IFERROR, etc. ) functions to perform specified actions on your data and return values.
  • Use Lookup and Reference (VLOOKUP, HLOOKUP, MATCH, INDEX, etc. ) functions to return a result in a table for a specified value.
  • Add text functions to your worksheet to extract or convert characters.
  • Use the Convert function to convert one measurement unit to another.
  • Choose to display either value or reference using the INDEX Function.
Lesson Two: Sorting, Filtering, and other Data Commands in Excel

Explore a variety of techniques for displaying precisely the information you need in a given moment or for a particular purpose. Use subtotals to stay apprised of activity in smaller groups of numbers.
Details:

  • Filter data in your worksheet and use the sort ascending, sort descending, and sort dialog box to rearrange the data list.
  • Use the Quick Analysis tool to instantly conditionally format data in a spreadsheet.
  • Apply conditional formatting criteria to display data with particular emphasis.
  • AutoFilter is a quick and easy way to display only those rows the meet the criteria you specify.
  • Find and remove duplicate data in your worksheet.
  • Use Advanced Filters to query your data and display only the information that meets the criteria you specify.
  • Become familiar with filter operators.
  • Allow Excel to quickly calculate subtotals to a list based on the data of a particular field.
  • Copy filtered and subtotaled data.
  • Use the Text to Columns feature to separate data in one column to multiple columns.
  • If there are formulas for which you need up-to-the-minute values, the Watch Window enables you to view cell contents without having to navigate to that particular cell.
Lesson Three: Working with Tables in Excel

Convert data in your worksheet to a table to organize and analyze data.
Details:

  • Create a table in Excel to allow for easier management and analysis of information.
  • Modify an Excel table and add a row, update the table properties, change the table format, and add a formula to a table column.
  • Use AutoFilters in your table to hide all rows that do not meet established criteria.
  • Add the total row to a table to allow for the quick display of different values.
  • Enhance your table with Slicers to filter data and show the current filter status.
Lesson Four: Working with PivotTables in Excel

PivotTables enable you to organize and summarize your data so that it includes the information you want to display and excludes the rest. Change a PivotTable’s focus by moving the fields to different locations, and create PivotCharts to represent the data in a graphical format.
Details:

  • Field buttons in PivotTables enable you to quickly change the information being displayed. Calculated fields can be modified to display the data using a different calculation or a new calculation can be added to meet your individual needs.
  • Group fields to broaden the view of the selected field. This feature works especially well to group dates that span several years into months or quarters, but can be used for text and numbers as well.
  • PivotCharts present you PivotTable data in graphical form. Just like PivotTables, the information viewed can be quickly changed by using the field buttons to include or exclude field items.
  • Using Slicers allows users to quickly see how a PivotTable is filtered and change the filter settings.
  • The Timeline feature allows for users to filter the PivotTable based on date information.
Lesson Five: Customizing the Excel Environment

Customize the settings in Excel to create a personalized environment with interface options that you need to easily access.
Details:

  • Customize the Excel ribbon with groups for your frequently used commands.
  • Add features to the Quick Access Toolbar and customize the order of the buttons.
  • Customize the Excel Options General settings including interface options, default fonts, backgrounds, and start up options.
  • The Excel Options Proofing settings can be customized to change how Excel corrects and formats text in your worksheet.
  • Customize your preferred settings for saving workbooks, AutoRecover exceptions, offline editing options, and preserving the visual appearance of your worksheets.
  • Modify the Excel Options Advanced settings to personalize editing, cut, copy, paste, image size and quality, print, chart, display, formula, data, and compatibility options.
Excel: Level 3

Excel: Level 3

Course Content:

Lesson One: Collaborating with Others

Working with others on a single workbook can quickly become unmanageable, resulting in multiple files and e-mail messages containing recommended changes which must be manually entered. Instead, click a button to track editions made by each person, merge them all into one workbook, then decide which changes to accept and reject. Send worksheet data to others in the body of an e-mail message, and password-protect your workbook for added security.
Details:

  • Protect your cells with data validation. Data validation ensures correct data is entered into a data list. Specify the type of data to be entered into a cell.
  • Assign password protection to your workbook to either prevent an unauthorized user from opening the file or to prevent someone from saving changes to your workbook. You can also protect the worksheet by preventing other users from using selected features such as inserting or deleting rows or columns. You can also protect the structure of the workbook to prevent users from inserting, deleting or moving worksheets.
  • Send workbooks by email as an attachment or in the body of a message.
  • Set up a workbook to be shared so that other users can view and/or edit the workbook while you are working in it. Save a copy of the workbook for each editor and email the copies as attachments or save the files on a network drive.
  • Enable the Track Changes feature to enable you to view changes made to the workbook. You can use the Accept or Reject Changes feature to browse the workbook by each change made and select whether to accept or reject each change.
  • Add comments to cells in a workbook as a note to yourself or for another user. Comments can be edited and deleted.
  • View changes made to a shared workbook by merging the edited copies with the original workbook.
Lesson Two: Exporting and Importing Data in Excel

Export and import data from other sources in to Excel to share your information with other people and include data from other sources.
Details:

  • Export your Excel data to share your information with other people. Become familiar with the various export file types.
  • Just as you can export data in Excel to other application, you can also import data from other applications in to Excel. Learn how to import data from common sources including importing data from text and from the web.
  • Create a web query for data imported from a webpage so that the data will automatically update any time data on the webpage is updated.
Lesson Three: Working with PowerPivots in Excel

A PowerPivot, like a PivotTable, summarizes complex lists of information. While a PivotTable is limited to summarizing one source of data, a PowerPivot can summarize multiple sources of related data and has the ability to link to external data.
Details:

  • Enable the PowerPivot Add-In feature in Excel and learn how to start PowerPivot.
  • Create a PowerPivot Data Model and link a table to a PowerPivot Data Model, add data from an Access, Excel or Text file.
  • Create and manage PowerPivot relationships to show related data from different tables together.
  • Combine the PowerPivot and PivotTables features to allow for creating Key Performance Indicators that provide a graphical representation of the current status against a target goal.
  • Learn the common DAX functions and create a calculation in PowerPivot.
  • Use the Power View feature to create interactive data presentations using Key Performance Indicators.
  • Filter data in your Power View report to show only the data you want to focus on.
  • Highlight data in your report to examine a subset of your viewable data.
Lesson Four: Working with Macros and Objects in Excel

Instead of repeatedly performing routine and/or complex tasks, let Excel remember them for you so they can be carried out with a click or a keystroke – then store them on your Quick Access toolbar for easy access.
Details:

  • Enable the Developer tab in Excel to access groups of options to create and run macros.
  • Learn how to use the Microsoft Excel Trust Center to keep your documents safe and your computer secure.
  • Macros replay a series of events such as keystrokes or mouse clicks. Learn how to record a macro, assign it to the Quick Access toolbar or shape, and then run the macro.
  • Insert ready-made shapes to your worksheet and learn how to resize, move, group, and format shapes.
Excel: PivotTables

Excel: Pivot Tables

Upon successful completion of this course, students will be able to:
  • Build a PivotTable.
  • Analyze data using PivotTables.
  • Present PivotTable data visually.

Course Content:

Lesson 1: Building a PivotTable
  • Insert a PivotTable
  • Create a PivotTable Based on External Data
  • Categorize PivotTable Data
  • Customize a PivotTable
Lesson 2: Analyzing Data Using PivotTables
  • Create Custom Calculations in a PivotTable
  • Restructure a PivotTable
Lesson 3: Presenting PivotTable Data Visually
  • Format a PivotTable
  • Present a PivotTable in a PivotChart
  • Print PivotTables and PivotCharts
Appendix A: Solvers
Appendix B: Goal Seek
Appendix C: Troubleshooting PivotTables

 

Excel: PowerPivot

Excel: PowerPivot

Course Description:

You will make use of the PowerPivot add-in to import data from various sources and create a dynamic report. This course is for students with a sound working knowledge of Microsoft Excel 2010 and general computing proficiency, including those who will be using Excel to make business decisions.

Prerequisites:

This course is meant for people with a sound working knowledge of Microsoft Excel and general computer proficiency.

Upon successful completion of this course, students will be able to:
  • Become familiar with the PowerPivot application and import data.
  • Manipulate data in a PowerPivot worksheet.
  • Create reports using PowerPivot data.
  • Use DAX functions in PowerPivot.
  • Distribute PowerPivot Data.

Course Content:

Lesson 1: Getting Started with PowerPivot
  • Explore the PowerPivot Application
  • Import Data from Various Data Sources
  • Refresh Data from a Data Source
  • Create Linked Tables
Lesson 2: Manipulating PowerPivot Data
  • Organize and Format Tables
  • Create Calculated Columns
  • Sort and Filter PowerPivot Data
  • Create and Manage Table Relationships
Lesson 3: Creating PowerPivot Reports
  • Create a PivotTable
  • Create PivotCharts
  • Filter Data Using Slicers
  • Present PivotTable Data Visually
Lesson 4: Using DAX Functions in PowerPivot
  • Manipulate PowerPivot Data Using DAX Functions
  • Extract Data from Tables Using Functions
  • Work with Time Dependent Data
Lesson 5: Distributing PowerPivot Data
  • Protect Reports
  • Save Reports in Different File Formats
Excel: VBA

Excel: VBA

Course Description:

You will automate your job tasks in Excel. This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA). Prerequisites: Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data.

This course is meant for people with a sound working knowledge of Microsoft Excel and general computer proficiency.

Upon successful completion of this course, students will be able to:
  • Develop a macro.
  • Format worksheets using macros.
  • Create an interactive worksheet.
  • Work with multiple worksheets.
  • Perform calculations.

Course Content:

Lesson 1: Developing Macros
  • Create a Macro Using the Macro Recorder
  • Edit a Macro
  • Debug a Macro
  • Customize the Quick Access Toolbar and Hotkeys
  • Set Macro Security
Lesson 2: Formatting Worksheets Using Macros
  • Insert Text
  • Format Text
  • Sort Data
  • Duplicate Data
  • Generate a Report
Lesson 3: Creating an Interactive Worksheet
  • Determine the Dialog Box Type
  • Capture User Input
Lesson 4: Working with Multiple Worksheets
  • Insert, Copy, and Delete Worksheets
  • Rename Worksheets
  • Modify the Order of Worksheets
  • Print Worksheets
Lesson 5: Performing Calculations
  • Create User-Defined Functions
  • Automate SUM Functions

Contact ExecuTrain of Houston