Microsoft Excel Course Online November 4, 2024 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: Find out how to perform calculations that involve multiple cells or ranges simultaneously.
• 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 Charts: Create line charts and spark lines to represent data visually.
• Column Charts using Excel: Learn to create column charts to visualize data.
• 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 options and benefits of organizing data 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 to navigate efficiently within Excel.
• Using Paste Special: Learn to copy formatting or calculations from one cell to another.

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:  Apply names to cell ranges 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 data across different columns.
• HLOOKUP: Find out how to use Hlookup to locate and return related data 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 depending upon the values 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.
• Advanced Pivot Tables: Find out how to build several pivot tables within one Excel worksheet.

Using Statistical Functions

• Ranking: Learn how Rank.eq and Rank.avg functions help determine a cell's position within a larger array for comparative analysis.
• COUNTIFS: Discover the COUNTIFS function to count cells that meet multiple conditions for more precise data filtering.
• SUMIFS: Learn to sum values in a range that meet multiple specified criteria, enabling complex and conditional summing.

Advanced Chart Creation

• Combo Charts: Understand how to combine multiple chart types into a single chart for more comprehensive data visualization.
• Chart axis: Find out how to add a secondary axis to a chart to better display and compare different data sets.

Excel Cell Management

• Cell Locking: Learn to create powerful formulas by locking columns or rows.
• Cell Auditing: Trace and analyze relationships between cells to identify errors in formulas and data flow.
• 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
• Customize Formatting with Formulas: Apply custom formatting to cells that meet specific criteria defined by your own formulas,.

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: Modify parameters to achieve specific target results in your calculations.
• Data Tables: Display the range of outcomes based on different variable inputs placed into a formula..

Understanding Advanced Analytical Tools in Excel

• Data Consolidation: Learn to summarize and combine data from separate ranges into a specific output range for comprehensive analysis.
• Conditional SumProduct: Discover how SumProduct performs calculations that can exclude cells that do not meet definitions you specify.
• Pivot Table Calculations: Learn to calculate columns and rows within a Pivot Table to derive new insights into your data.
• Pivot Charts: Build data visualizations from Pivot Tables to enhance data interpretation and presentation.

Using Excel’s Advanced Database Functions

• MATCH function: Master the MATCH function to locate the exact row or column number of a specific value, enhancing your data referencing skills.
• VLOOKUP-MATCH: Combine VLOOKUP with the MATCH function to accurately determine column indexes, increasing the reliability of your data lookups.
• INDEX-MATCH: Utilize the INDEX and MATCH functions together to precisely retrieve data from specified rows and columns, improving your data extraction capabilities.
• INDEX-Double MATCH: Employ a second MATCH function with INDEX for advanced two-way lookups, enabling more versatile and dynamic data retrieval.

Learning Macros in Excel

• Recording Macros: Learn to record Macros that can apply multiple sets of formatting in a single step, or perform multiple calculations with one click.
• Relative Macros: Create automated actions based on the position relative to the active cell, allowing the macro to be applied flexibly to different cell locations