Week #13: (4/16-20) Technical Applications of Excel and CSV Files

Post Reply
Posts: 1776
Joined: Fri Sep 04, 2015 6:59 pm

Week #13: (4/16-20) Technical Applications of Excel and CSV Files

Post by rjagodowski »

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.
(988.51 KiB) Downloaded 75 times
Some info on MS on Excel from Microsoft can be found here: https://support.office.com/en-us/articl ... n-US&ad=US.

Key topics for today will include:

Common Short-Cut 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.
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:
(29.5 KiB) Downloaded 75 times
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.
Maximum Power Lab - Power Calculations v18.1.xlsx
(16.92 KiB) Downloaded 77 times
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/en-us/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:
Name Sort File v18.1.xlsx
(11.21 KiB) Downloaded 73 times
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:


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.
csv Test.csv
(26 Bytes) Downloaded 75 times
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:
csv Example.csv
(23.22 KiB) Downloaded 69 times
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:
csv Example.xlsx
(151.27 KiB) Downloaded 69 times
Open Source Office Alternatives:

Libre Office: http://www.libreoffice.org/

Open Office: http://www.openoffice.org/

Google Docs: http://www.docs.google.com/
Maximum Power Lab - Power Calculations.xlsx
(16.45 KiB) Downloaded 75 times
Name Sort File.xlsx
(11.19 KiB) Downloaded 81 times
Posts: 13
Joined: Tue Sep 12, 2017 6:16 pm

Re: Week #13: (4/16-20) Technical Applications of Excel and CSV Files

Post by kakerr0001 »

complete week #13
Post Reply

Return to “EET-255 Advanced Topics in EET/Automation”