# 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

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

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¶

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.

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.

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