Today we will investigate Microsoft Excel, with emphasis on it's formula, tables, graphing abilities & sorting especially in relation to technical applications. We'll also present the csv (comma separated value) file format.
Here is the "Quick Reference Card" for the "Get to Know Excel 2016" exercises:
Here's a shorter, printable version:
Some info on MS on Excel from Microsoft can be found here: https://support.office.com/enus/articl ... nUS&ad=US.
Key topics for today will include:
Common ShortCut key sequences.
Numbers  Formatting, with emphasis on Scientific and Engineering Notation (see link below).
Graphs  Multiple lines on a single graph, Graph & Axis Titles, Fonts, Major/minor Axis formatting, Scaling, Legends, Colors.
Copying Excel Tables & Graphs into MSWord documents.
Sorting
CSV files
Open Source alternatives.
Some common shortcut keys: Ctrl+: c = copy, x = delete, v = paste, z = undo last action, y = redo last action, ~ (in Excel) show formulas in all cells
Windows shortcut Keys from Microsoft: http://support.microsoft.com/kb/126449.
Engineering Notation in Excel:
Helpful links:
Here is a helpful link for formatting numbers in scientific and engineering notation: http://people.stfx.ca/bliengme/ExcelTip ... tation.htm. The Excel file for this link is available here:
Here's a link to Charts & Graphs in Excel.
You'll probably notice that most examples, demos, tutorials and such generally are related to business operations. However, Excel can definitely be useful for technical applications.
The attached file demonstrates the Maximum Power Transfer Theorem. We'll open this file in class to demonstrate how Excel can graphically show the change in expected results with a single data point change. This is sometimes called a "what if" scenario. "What if" I change this value to 3300? Once the spreadsheet has been created, "what ifs" are just a few key strokes away.
Make note in the graph how the horizontal axis is a logarithmic scale, allowing a wide range of x values (Resistance) to be presented in a concise format.
Sorting data in Excel:
A quick overview: http://spreadsheets.about.com/od/excel2 ... 07sort.htm
All the details: http://office.microsoft.com/enus/excel ... 73947.aspx
The important thing to remember when sorting multiple rows & columns of data is to select ALL of the data to be sorted in order to preserve the relational aspects. For instance, first names with last names, or names with addresses or names & addresses with phone numbers.
Here's a practice file:
Date and Time information can also be processed in Excel. Here are some links to show the time related functions.
The TODAY() and NOW() functions: http://spreadsheets.about.com/od/excelt ... nction.htm
Comma Separated Value files:
A common data file format is csv or comma separated value format. Excel (and most programs) will import this type of format, and many will export it as well. A csv file is a basic text file which contains no program specific formatting. If you save an Excel file in csv format, the contents of each cell in each column will be separate by a comma. When the end of row 1 is reached, a new line is formed in the file beginning with the contents in row 2.
If you open a csv file with a word processor, you might see something that looks like this:
1,2,3
,4,5
6,7,8
a,,c
If you don't see a value before a comma, that is because that cell would be empty. Here is the csv file. Open it in a simple text editor and then open it in Excel, to see how it looks. Notice how small (in bytes) the file is.
Data Acquisition equipment often create & store their data in csv files, so it's important for a technician to recognized this file format and understand how to open and manipulate the date.
Here is a sample csv data file:
Download this file and open it in Excel. We will perform some Excel data filtering and statistical analysis on this file and generate a graphical output. The result will look like this file:
Open Source Office Alternatives:
Libre Office: http://www.libreoffice.org/
Open Office: http://www.openoffice.org/
Google Docs: http://www.docs.google.com/
Week #16: (5/610) Technical Applications of Excel, csv files.

 Posts: 1561
 Joined: Fri Sep 04, 2015 6:59 pm
Week #16: (5/610) Technical Applications of Excel, csv files.
Post by rjagodowski » Mon May 06, 2019 4:22 pm
 Attachments

 excel2016quickreference.pdf
 (988.51 KiB) Downloaded 17 times

 Maximum Power Lab  Power Calculations.xlsx
 (16.45 KiB) Downloaded 18 times
Return to “EET255 Advanced Topics in E.E.T./Automation”
Jump to
 Electronics Systems  General Information
 ↳ Career Related Links and Information
 ↳ Career Information (General)
 ↳ Instructor Information
 EMakers's Club
 ↳ EMakers General information
 ↳ Meeting schedule and locations.
 2020 Spring
 ↳ EET120 Fluid Power
 ↳ EET240 Robotics & Automation
 ↳ EET250 Control Systems
 ↳ EET250 Lecture
 ↳ EET250 Lab
 ↳ EET255 Advanced Topics in E.E.T./Automation
 ↳ EET260 MicroProcessor Applications
 ↳ EET260 Lecture
 ↳ EET260 Lab
 ↳ EET265 Advanced Automation
 ↳ EET265 Lecture
 ↳ EET265 Lab
 ↳ Lab Information for All Groups
 ↳ Weekly Progress Reports  Carwash Project
 ↳ Weekly Progress Reports  Filling Project
 Previous Semesters
 ↳ 2019 Fall
 ↳ CSE110 Information, Reading and Video List
 ↳ CSE110  General Information
 ↳ EET101 PCs, Robots & Programming
 ↳ EET101 Lecture
 ↳ EET101 Lab
 ↳ EET104/5/6 Safety, Diagrams, Troubleshooting
 ↳ EET104 Safety & Health in Industry (Mondays)
 ↳ EET105 Technical Diagrams for Automation (Wednesdays)
 ↳ EET106 Troubleshooting (Fridays)
 ↳ EET120 Fluid Power
 ↳ EET200 Solid State Electronics
 ↳ EET200 Lecture
 ↳ EET200 Lab
 ↳ EET210 Digital & Linear Circuits
 ↳ EET210 Lecture
 ↳ EET210 Lab
 ↳ EET250 Control Systems
 ↳ EET250 Lecture
 ↳ EET250 Lab
 ↳ EET260 MicroProcessor Applications
 ↳ EET260 Lecture
 ↳ EET260 Lab
 ↳ Basic Subnetting
 ↳ 2019 Spring
 ↳ EET101 PC's, Programming & Robots
 ↳ EET101 Lecture
 ↳ EET101 Lab
 ↳ EET120 Fluid Power
 ↳ Fluid Power (Public)
 ↳ EET210 Digital and Linear Circuits
 ↳ EET210 Lecture
 ↳ EET210 Lab
 ↳ EET235 PLCs 2
 ↳ EET235 Lecture
 ↳ EET235 Lab
 ↳ EET250 Control System Theory
 ↳ EET250 Lecture
 ↳ EET250 Lab
 ↳ EET255 Advanced Topics in E.E.T./Automation
 ↳ EET260 Microprocessor Applications
 ↳ EET260 Lecture
 ↳ EET260 Lab
 ↳ EET265 Advanced Automation
 ↳ EET265L Lab  Day
 ↳ Weekly Progress Reports  Cold Beverage Dispenser
 ↳ Weekly Progress Reports  Warehouse Storage/Retrieval
 ↳ EET265L Lab  Evening
 ↳ ELE111 Internet of Things
 ↳ ELE111 General Information
 ↳ ELE111 Reading List
 ↳ ELE115L  Bigos
 ↳ General
 ↳ MechElec WFD Training
 ↳ MechElec (Public)
 ↳ MechElec WFD Training Cohort 2
 ↳ 2018 Fall
 ↳ CSE110 Intro to Comp (A+)
 ↳ CSE110 General Information
 ↳ EET101 PC's, Programming & Robots
 ↳ EET101 Lecture
 ↳ EET101 Lab
 ↳ EET104/105/106 Safety & Health, Tech Diagrams, Troubleshooting
 ↳ EET104 Safety & Health in Industry
 ↳ EET105 Technical Diagrams for Automation
 ↳ EET105 Public
 ↳ EET106 Developing Troubleshooting Skills
 ↳ EET200 Solid State Electronics
 ↳ EET200 Lecture
 ↳ EET200 Lab
 ↳ EET210 Digital and Linear Circuits
 ↳ EET210 Lecture
 ↳ EET210 Lab
 ↳ 2018 Spring
 ↳ CSE110LIntro To Comp
 ↳ CSE110L General
 ↳ CSE220 Security Reading List
 ↳ EET120 Fluid Power Technology