11. Making Use of Microsoft Excel

Although not as capable as MATLAB and more cumbersome to use, it is reported that when required, some users can use Microsoft Excel for simple engineering calculations. [LIENGME16] [1]

11.1. Basic Usage

Our K-State Polytechnic Library has prepared a summary of using Microsoft Excel. Thanks to Katherine Jones and Pam Bower for this. In case you did not already know, yes, Pam is my wife <3.

11.2. Available Functions

Excel provides a few built-in functions – 450 in Excel 2013. They are divided into 13 categories: Compatibility, Cube, Database, Date and Time, Engineering, Financial, Information, Lookup and Reference, Math and Trigonometry, Statistical, Text, and Web. To see the list of functions with brief explanations of what each does, open Excel’s Help tool by clicking on the question mark (?) on the right side of the title bar, and then type functions in the search box.

See the documentation from our library for information about using simple formulas in Excel.

11.3. Array Formulas

Almost all Excel formulas produce a result that is saved in a single cell. However, a few Excel function produce a result that is saved to a range of cells. These are called arrary formulas. Select the range of cells where the result will be saved, then type the array formula, which like other formulas begins with the equal sign =. Rather than pressing the ENTER key after typing the formula, press Ctrl + Shift + Enter to execute the formula and save the result.

Functions that operate on matrices are examples of array formulas. Excel only provides four matrix functions. Thus, Excel is not suited to solve most linear algebra problems. However, it can be used to find the solution to a system of linear equations of the form

A\,x = b

Excel does not provide anything equivalent to MATLAB’s left divide operator, so the less efficient matrix inverse is used.

x = A^{-1}\,b

../_images/excel_sys_eq.png

Example of using Excel’s MINVERSE and MMULT to solve a system of linear equations

Excel’s Matrix Functions
MMULT(A, B) Matrix multiplication of A*B
MINVERSE(A) Inverse of matrix A
MUNIT(n) Generate a n-by-n unit matrix
MDETERM(A) Find the determinant of matrix A

11.4. Plotting Data

  1. To produce a data plot, first one must populate a cell for each X and Y data point. Formulas and the fill down operation (Ctrl + d) can help, but it can still be a slow process, especially if the chart needs a level of detail requiring a lot of data points.

  2. Select the correct plot type. Select the data to be plotted, and then under the Insert tab, select the general type of plot desired. Under each general plot type, the user then selects the specific plot type. Moving the mouse over each plot type shows a preview of what the plot will look like. It will likely be required to select “More Charts” to find the desired chart type.

    Warning

    Take care when next selecting the specific plot type because many plot types are fixed to use the index of the data as the x-axis data.

  3. Once the basic plot is created, the remaining steps are easier. The title, x and y axis labels and legend are manually entered using the graphical interface.

Footnotes

[1]My appologies for being less than enthusiastic about using Excel for engineering work. I was requested to cover Excel in this course, but in good consious I can not recommend Excel for general engineering analysis work. Given the salary range of professional engineers. I feel that companies stand to gain more in increased productivity from their engineering staff by using MATLAB instead of Excel than they stand to lose from the higher purchase price of MATLAB compared to Excel.