Week #16: (5/6-10) Technical Applications of Excel, csv files.

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

Week #16: (5/6-10) Technical Applications of Excel, csv files.

Post by rjagodowski » Mon May 06, 2019 4:22 pm

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:
(988.51 KiB) Downloaded 3 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 1 time
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/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 1 time
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 1 time

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 2 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 1 time

Open Source Office Alternatives:

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

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

Google Docs: http://www.docs.google.com/
(988.51 KiB) Downloaded 2 times
Maximum Power Lab - Power Calculations.xlsx
(16.45 KiB) Downloaded 1 time

Post Reply

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