### Week #6: (2/29-3/4) Technical Applications of Excel

Posted:

**Mon Feb 22, 2016 3:36 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 2010" exercises. Some PowerPoint presentation from MS on Excel can be found here: http://office.microsoft.com/en-us/suppo ... 01726.aspx. You should watch the "Get to Know Excel 2010 - Create your first spreadsheet" PowerPoint presentation. This presentation has several videos embedded in it.

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.

Sorting

CSV files

Open Source alternatives.

Some shortcut keys:

Shortcut Keys from Microsoft: http://support.microsoft.com/kb/126449.

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

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:

Date and Time information can also be processed in Excel. Here are some links to show the

The

A common data file format is

If you open a

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

Here is a sample

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

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

Google Docs: http://www.docs.google.com/

Here is the "Quick Reference Card" for the "Get to Know Excel 2010" exercises. Some PowerPoint presentation from MS on Excel can be found here: http://office.microsoft.com/en-us/suppo ... 01726.aspx. You should watch the "Get to Know Excel 2010 - Create your first spreadsheet" PowerPoint presentation. This presentation has several videos embedded in it.

**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.

Sorting

CSV files

Open Source alternatives.

Some shortcut keys:

**Ctrl+**: c = copy, x = delete, v = paste, z = undo last action, y = redo last action, ~ (in Excel) show formulas in all cellsShortcut 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/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:

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 Separate 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/