Microsoft Excel Data Analysis for Engineers and Scientists

Excel Data Analysis Training Description

Our 1-day Excel data analysis training for engineers and scientists training will show you how to effectively use Excel to process engineering and scientific data. Key topics include using cell and range names to track data, using key functions like VLOOKUP, MATCH, COUNTIF, SUMIF and IF in data analysis formulas; using advanced array formulas and logic to process data; organizing and storing data on the worksheet; analyzing worksheet data with various Excel data tools; building charts; pre-processing worksheet data and working with text files; and linking Excel to other programs like Word and PowerPoint for reporting purposes. Also discussed is how to use pivot tables and Power Query to manipulate and filter data.

Key Skills Learned in Our Excel Data Analysis Training

Key Skills You'll Learn in Our Microsoft Excel Data Analysis Training

Key Excel skills learned:

How to build Excel worksheet formulas using various types of cell references and debug them
Review of Excel stores data on the worksheet, cell format discussion
How to name worksheet cells and ranges, the key to building sophisticated, self-adjusting Microsoft Excel formulas
How to build proper worksheet tables, the key to using Microsoft Excel's data tools and creating data storage systems
How to turn a worksheet range into an Excel table and use it in data analysis formulas
How to use logical formulas in Conditional Formatting to flag data
How to use Group, Sort, Advanced Filter, AutoFilter and Subtotal to manipulate Excel worksheet data
How to use Excel worksheet functions like SUM, MIN, MAX, COUNTA, AVERAGE and SUBTOTAL to analyze data
How to look up and merge worksheet data using key Excel worksheet functions like VLOOKUP, MATCH and INDEX
How to use Excel functions to summarize and count data like IF, AND, OR, SUMIF, SUMIFS, COUNTIF and COUNTIFS
How to process complex data sets using array formulas and logic
How to parse text on a worksheet using Text to Columns and the Remove Duplicates features
How to import text files using Text Wizard
How to use Excel functions like LEFT, RIGHT, MID, SEARCH, SUBSITUTE, LEN, REPLACE, TRIM and CLEAN for parsing worksheet text data
How to create engineering and science charts including combination charts
How to create, control and format pivot tables and pivot charts including slicers and timelines
Introduction to using Power Query to filter and pre-process data
How to link Excel with PowerPoint and Word to create reports

Excel Training Skill Requirements

Excel Data Analysis Training Skill Requirements

Select this Excel training if you or your group:

Have opened and saved a workbook file
Know the basics about worksheets, cells and using cell references like A1
Have copied and pasted worksheet data
Have performed basic cell formatting tasks like coloring a cell, bolding, aligning...
Have typed data in worksheet cells and built basic formulas like =A1+A2
Have used a worksheet function like =Sum(A1:A10) in a formula or have seen it before

Microsoft Excel Data Analysis Topics Outline

Important: We focus our Excel training on what our customers need. When training begins, we analyze those needs and shift our Excel training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Formula Design, Formula Debugging, Cell Naming and Sheet Linking for Engineers and Scientists

Review of engineering and science formula construction and the different types of cell references that can be leveraged in their construction
Review of how Excel really stores data including cell formats and dates
How to debug your formulas and then map their dependencies on the worksheet
How to use cell and range names in your formulas, the key to building adaptive worksheets that respond to data changes
How to manage cell and range name editing and deletion in your workbook
How to pass information between worksheets and workbooks using names and cell references

Microsoft Excel Data Analysis Training Free Repeats

Key Things to Know About Building Engineering and Science Tables on the Worksheet

Overview of what makes a table on a worksheet and how Excel and later VBA can utilize them in mass data processing schemes
The 4 basics rules for table layout in Excel that allow it to act like a database
Different types of tables and how they impact what worksheet functions and formulas you use when analyzing your data
How to create Excel tables, name them and manage them on the worksheet
How to use a slicer to filter Excel tables

Worksheet Function Operation, Argument Lists and Nesting for Engineers and Scientists

How a worksheet function operates in a formula and what is an argument list
Review of how to analyze worksheet data using basic functions in your formulas like SUM, MAX, MIN, COUNTA, AVERAGE and SUBTOTAL
How to use cell references, names, constants and Excel table structured references in worksheet function argument lists
Overview of worksheet function nesting and its importance in analyzing engineering and science data
Methods for creating nest worksheet function structures in formulas

Looking Up and Merging Engineering and Science Table Data

Primary and foreign key discussion, how lookups work
How to use VLOOKUP to lookup table information and merge it
How to use MATCH and INDEX to lookup table information and merge it

Logic Overview, Condition Construction and Key Logic Worksheet Functions for Engineers and Scientists

What is logic and how can engineers and scientists use it in formulas
What are logic conditions and how to create them by looking for triggers in your data
How to build logic conditions using the relational operators: <, >, <=, >=, < >, =
How to build logic conditions for IF functions using the relational operators (<, >, <=, >=, < >, =) and various worksheet functions like ISBLANK, ISNUMBER, ISNA, COUNTIF, COUNTIFS, LEFT, RIGHT, MID,...
How engineers and scientists can build multi-condition Excel logic formulas using the IF, AND and OR worksheet functions

Essential Data Analysis Formula Design for Scientists and Engineers

How to use SUMIF and SUMIFS to summarize your worksheet data
How to use COUNTIF and COUNTIFS to count how many instances of a value you have in your data
How to use string concatenation in Excel formulas, the key to creating adaptable formulas and displays for data tools
Overview of how scientists and engineers can use array formulas to analyze large amounts of complex worksheet data
How scientists and engineers can use simple Matrix algebra operations on the worksheet
How to create Excel array formulas that process large amounts of data using the IF function
How engineers and scientists can create Excel array formulas that perform advanced lookups and table merging using MATCH, VLOOKUP, HLOOKUP, INDEX, ROW, COLUMN...
How to write Excel formulas that adjust to changing data using Excel tables and dynamic name formulas, the key to developing self-adjusting Excel data tools and displays
How engineers and scientists can construct Excel formulas that can automatically toggle between tables and other worksheet information using the INDIRECT functions
How to create Excel formulas that bracket data ranges on the worksheet using MATCH, ADDRESS and INDIRECT

Must Know Text Analysis Techniques for Engineers and Scientists

How to analyze, split and clean worksheet text using the text worksheet functions like RIGHT, MID, LEFT, SUBSTITUTE, TRIM, CLEAN, SEARCH and REPLACE
How to use the Text to Columns feature to separate delimited data into separate worksheet columns
How to use Text Wizard to import downloaded text files
How to use the Remove Duplicates feature to retrieve unique values from a column

Using the Group, Advance Filter and Sort Tools on Engineering and Science Data

How to group worksheet rows allowing them to be hidden and displayed using the Group feature
How to use the Sort feature to sort tables based on value, date/times and colors
How to use the Advanced Filter to build advanced table filters

Using Conditional Formatting to Flag Your Engineering and Science Data

The uses of conditional formatting
How to use other conditional formatting rules (i.e. graphical bars, icons...)
How to use the 2 most important conditional formatting rules that color cells based on formulas and cell values
How to build logical formulas in the conditional formatting rules that flag data

Creating Engineering and Science Charts

How to create engineering and science charts: XY Scatter, Column, Line and Pie charts
Chart formatting tips including picture placement, data labels, error limits and professional coloring
How to combine two or more chart types into one chart
How to layout multiple charts on the same worksheet to create reports
How to make charts that react to user control

How to Create Pivot Tables and Pivot Charts on the Worksheet

What is a pivot table and pivot chart?
How to arrange and name worksheet data for a pivot table to use
How to create pivot tables from worksheet data
How to control pivot tables using filtering and grouping
How to refresh and remap pivot table data
How to create different pivot table summary types
How to format a pivot table using styles and the Format Cells dialog box
How to make slicers and timelines for pivot tables
How to format and control the options for timelines and slicers
How to construct, edit and format pivot charts
How to use the filters on a pivot chart and arrange them
How to use the pivot tables, pivot charts, slicers and timelines to create dashboards and reports

Linking Excel with Word and PowerPoint

Basic linking between the programs with copying and pasting
How to link cells, ranges and charts with Word and PowerPoint

Using Power Query to Access External Engineering and Science Data

How to use Microsoft Query to query external data and create an Excel table that is linked to its data source
How to use Power Query to link with an Excel workbook, text file and database and create an Excel table
How to perform various key tasks in Power Query like merging data, replacing characters, splitting dates, cleaning text,...

Microsoft Excel Data Analysis Training Details

Audience: Engineering and Science
Time: 8:30 AM - 4:30 PM
# of Days: 1-day
Type: Hands-on, live, instructor taught training
Manual: 2000 + page Microsoft Excel-Aided Engineering/Science manual full of Excel tips and how-to-do topics included with the training
Platform: Windows 7 - Windows 10, Mac users welcome
Excel Versions: 2007-2016, Windows and Excel for Mac 2011-2016
Free Repeats: Repeat your Excel training as many times as you like, no date or limit, click here for details
Examples: We review our attendees personal examples in our training instruction
Training Formats:
- Company Onsite
Additional Training:

Onsite Details

Onsite Only

Onsite Details

Microsoft Excel Data Analysis Training Contact Information

Contact EMAGENIT Directly

Phone Number: 1.866.924.6244
Business Hours: 8:30 - 5:00 PM PT
email: excel_data_eng_sci_question@emagenit.com

Your Contact Information

*Name:
Company:
Company Web:
Country:
Phone #:
Ext:
*e-mail:
	Contact me directly by phone when this is received

*Required information to complete form. Privacy Policy

Workshop Questions

Please fill out the box below with your workshop questions and EMAGENIT will contact you.

Request Workshop Information and Submit

	Send me on-site workshop information
	Send me public training workshop information
	Send me self-study manual information
	Send me live online course information

Before clicking the Submit button, please fill in at least your name and email under Your Contact Information. Privacy Policy