Excel Bootcamp

Excel Bootcamp: Topics Covered

Topics covered in the Microsoft Excel Bootcamp include the following:

Getting Started with Excel

• Getting to know the Excel Interface: learn to navigate within the Excel interface to access commands and functions.
• Data Entry using Excel: Understand various ways of entering data into an Excel file.

Using Excel Formulas

• Autofill in Excel: Learn to use Excel's predictive data entry
• Excel Calculations: Use excel for mathematical expressions
• True / False: Learn to have Excel test for matching data using true/false
• Functions in Excel: Discover essential AutoSum functions including: Sum, Average, Max, Min, and Count Numbers
• Excel’s Text Functions: Learn to use functions to modify text
• Multi-Input Functions: Learn advanced functions that require multiple inputs
• Constant (Absolute) Cell References: Learn to create constant cell references when required for calculations

Formatting Worksheets in Excel

• Formatting Excel sheets and content: Learn to provide visual cues to those reviewing sheets with formatting.
• Using the Format Painter in Excel: Learn to duplicate formatting from one cell to others.
• Applying Conditional Formatting: Discover how to apply formatting depending upon rules relating to content.

Working with Charts in Excel

• Understanding chart types
• Creating Line Chart: Create a line charts and spark lines to represent data visually.
• Column Charts using Excel: Learn to create column charts for data visualization.
• Pie Charts in Excel: Discover how to create pie charts for data visualization.
• Customizing charts: Adjusting charts to meet your design requirements.
• Creating Tables: Understand the functionality available when data is presented as a table.

Excel Workbook Management

• Printing & Exporting from Excel: Understand ways to easily print or convert Excel content to PDF.
• Using Multiple Worksheets in Excel: Learn to organize data by creating, moving, copying and managing Excel worksheets.
• Using Repeat Action: Learn to quickly duplicate prior actions in Excel.
• Saving Time with Excel Shortcuts: Discover shortcuts that save time and help you to work more efficiently.

Excel Worksheet Management

• Efficient Excel Navigation: Discover shortcuts for quick and easy navigation within Excel.
• Using Paste Special: Learn to apply formats and perform calculations on selected cells.

Understanding Text Functions and Validation

• Splitting Text: Adapt Text to Columns for splitting text into multiple cells.
• Joining Text: Combine text from different cells.
• Named Ranges:  Assign names to ranges of cells for referencing when conducting calculations.
• Data Validation: Create drop-down menus, making it easier to enter data accurately.
• Sort & Filter: Locate and organize data in large data sets using sort and filter options.
• Remove Duplicates: Learn to remove duplicate data from a sheet.

Using Database Functions

• VLOOKUP: Learn to use Vlookup to locate and return related information across different columns.
• HLOOKUP: Find out how to use Hlookup to locate and return related information across different rows.
• VLOOKUP - Closet Match: Discover how to use Vlookup to find the closest match when an exact match is not identified.

Understanding Logical Functions

• AND, OR: Find out how to use and/or to determine if one or more conditions are met.
• IF statements: Learn about using IF statements to return data based on the contents of other cells.

Working with Pivot Tables

• Pivot Tables: Discover how to use Pivot Tables to easily summarize large sets of data.
• Pivot Tables & Grouping: Create groups within Pivot Tables.
• Multiple Pivot Tables: Find out how to build several pivot tables within a single Excel sheet.

Using Statistical Functions

• Ranking: Learn about Rank.eq and Rank.avg functions for determining the position of a cell within a larger array.
• COUNTIFS: Discover the COUNTIFS function for counting cells based upon conditions.
• SUMIFS: Apply SUMIFS function to sum cells based upon conditions.

Advanced Chart Creation

• Combo Charts: Learn to combine two or more charts into a single chart
• Chart axis: Find out how to add a secondary axis to a chart.

Excel Cell Management

• Cell Locking: Learn to create powerful formulas by locking columns or rows.
• Cell Auditing: Understand the relationship between formulas and cells.
• Windows: Discover techniques for editing active windows.
• Hot Keys: Learn to use the ribbon as a custom set of personalized shortcuts.

Using Special Formatting in Excel

• Date functions: Learn to calculate dates using several functions
• Conditional Formatting of Formulas: Discover how to use conditional formatting of formulas using custom rules.

Understanding Advanced Excel Functions

• Nested IF statements: Learn to use nested IF statements to create multiple options for populating cells
• IF statements using AND/OR: Learn additional IF functions available by using AND / OR

Using Excel for What If Analysis

• Goal Seek: Adjust input values to find desired results
• Data Tables: Display the range of effects for one or two variables on a formula using Data Tables.

Understanding Advanced Analytical Tools in Excel

• Data Consolidation: Learn to summarize data from separate ranges, consolidating it to a specific output range
• Conditional SumProduct: Learn how using SumProduct with conditions excludes data that fails to meet specific criteria
• Pivot Table-Calculations: Learn to create calculated columns and rows using a Pivot Table to expand beyond source data
• Pivot Charts: Learn to build dynamic visual representations of data from pivot tables

Using Excel’s Advanced Database Functions

• MATCH function: Learn to return the column or row number of a lookup value
• VLOOKUP-MATCH: Create more accurate VLookUps through enhancing the determination of the Column Index number
• INDEX-MATCH: Efficiently return values and references from cells at the intersection of the row and column
• INDEX-Double MATCH: Learn to use a second Match function to create powerful two-way lookups

Learning Macros in Excel

• Recording Macros: Learn to record Macros involving formatting and calculations
• Relative Macros: Understand how to create relative reference macros used in ad-hoc reporting