EngBAM

If you do a Chemical Engineering degree you are likely to encounter a series of different types of software and coding languages including Matlab, COMSOL, Python, SIMULINK, Pro II and most importantly for this page, Excel.

Excel is incredibly useful to Chemical Engineers as it is a great tool to process large amounts of raw data quickly. After one simple experiment you could need to process thousands of data points. Using software such as Excel, this can be achieved quickly and easily.

To hit the ground running at University you might find this quick guide to the basics of Excel useful! This resource is not just specific for those with an interest in Chemical Engineering, if you have any need for Excel in your current / future studies or employment, then this guide is for you!

Practice is key when learning how to use a new piece of software. You will feel a lot more comfortable using Excel when you have built up some familiarity. Therefore, to aid this try to use Excel as much as possible, even if it is for a maths question you can solve on paper. The more you use Excel, the more comfortable you will be and the better you will become!

So let’s get started:

Excel Layout

Excel is organised into a series of worksheets. To get started you want to create a new workbook, within this you can create and work on multiple worksheets. A worksheet is split into a series of rows and columns. The rows are numbered in ascending order, with the columns increasing in alphabetical order. This system allows us to reference an individual cell. For example, the third cell down in the second column is B3.

Simple Calculations

Excel can be used to perform simple calculations such as addition, subtraction, multiplication and division. You might think, what is the point of using Excel for such basic operations? Well, it may be easy to do one or two calculations in your head. However, when this needs to be applied to thousands of data points, it is much easier to complete using Excel instead of pen and paper.

To start writing a formula, pick a cell and type an equals sign. The equals will assign a value to that cell. To complete a formula something always needs to be written on the right hand side of the equals. E.g. = 5+3, would assign a value of 8 to that cell. Open up a new excel sheet and try a few basic calculations.

If you enter words into a cell, then Excel will align the text with the left hand side of the cell, however, a number will be aligned to the right of the cell. This is a useful tool, as it aids presentation as well as allowing for data to be easily distinguishable.

Basic operators:

= Assigns a value to a cell

+ Addition

– Subtraction

* Multiplication

/ Division

Cell Referencing

As useful as being able to do basic operations with numbers is, this won’t be much use when we need to do calculations on large data sets. Introducing cell referencing, potentially one of the most useful habits you can get into when using Excel. Cell referencing is the practice of linking one cell into another cells formula. For example, if you wanted to multiply the values in cell A2 by 3, in a new cell you would write =A2*3. There are two key uses for linking calculations this way:

1. This calculation can be dragged down and applied to an entire column of data (more on this later).
2. If the value in A2 changes, then the calculated value will also automatically update. This is INCREDIBLY useful as an engineer. It is common practice for values to change during analysis, for example if a different constant would be more appropriate, or if a mistake was made. Referencing cells means that you do not need to trawl through all of your data to change every single cell that is affected. Referencing cells will reduce both the time taken for analysis as well as reducing the number of mistakes.

You can even reference cells from another sheet within the same workbook. This is very useful if you are flowsheeting a process and need to link together different stages within the same workbook.

Always reference a cell in a formula if you can

Decimal Places

Depending on the level of accuracy you are working to within your data you may wish to adjust the number of decimal places you want Excel to display. This can be done in two ways, the first of which is easy to use when adjusting individual cells. Highlight the cell you would like to edit, then using the number section on the home tab, use the decimal increase and decrease buttons to adjust the number of decimal places required. If you wish to apply this to all cells within the sheet, first select all cells using Ctrl+A, then right click and choose format cells from the menu. On the number section of the pop up window you can then choose the number of decimal places that will be displayed. Within this window you are also able to choose whether to display negative numbers or the absolute values of the cell.

Drag Down

The drag down function is useful when applying one formula to multiple cells in a column or row. If you need to apply one calculation to an entire column of data, for example multiplying the measured mass of an object by it’s acceleration to find the force applied (F=ma). It becomes tiresome to have to repeat this calculation for each of potentially hundreds of data points in a table. Therefore, ensuring you reference the cells within a formula instead of typing in the raw data you only need to write out the formula once. When you highlight this cell and hover over the bottom right corner, a small black cross will appear. If you click the cross and drag down over the cells, the formula will be applied to each of the cells in the column that you drag down over. If you reference the cells instead of typing in the raw data, the formula will automatically update to use the data in that row. You can also drag horizontally to achieve the same result by repeating the formula across a row.

Absolute Cell Referencing

If you want to only vary one thing in a calculation, for example W=mg, the gravitational constant will reference one cell within the sheet. You can enter the gravitational constant (9.81 m/s^2) in cell A2. When you enter our formula to calculate the mass, =B3*A2, this will calculate the weight for that specific mass. However, if you want to repeat this operation in column B, then as you drag the formula down the next calculation would be =B4*A3. However, cell A3 is empty as your gravitational constant is in A2. To avoid this you can use what is called absolute cell referencing. To use absolute referencing in a formula you need to add a $ sign before the letter and the number in a cell reference. If this is done then even if you drag down a formula, that specific cell will always be referenced. Therefore, in our example the formula =B3*$A$2 can be dragged down and applied to the data in column B.

Sorting and Filtering Data

If you have large amounts of experimental data it can be helpful to sort the data before data analysis. For example, imagine you have an experiment focussing on increasing the velocity of water flowing through a pipe with three repeats for each value. It might be helpful for you to be able to have this data ordered with increasing velocity. To achieve this, you want to highlight the data in the velocity column, then on the home tab under the editing section select the Sort & Filter button. From here you can select how you want to sort the data, in this case select smallest to largest. Depending on the data you have selected, a window asking whether you want to expand data will appear, if you click yes, then the other data in the table will be rearranged along with the velocity. This means that even if the order of the velocities in the table change, the associated experimental number will remain the same.

Freeze Panes

When you are working on large tables, it is common that you will no longer be able to see the table titles if you need to scroll down. This can make it very difficult to interpret the data. To overcome this issue Excel has a function that allows you to freeze panes. Freezing a pane will keep the selected panes in place, so for example the column titles will remain locked in place when you scroll down. To freeze a section select the cell that you want to be the top corner of your unfrozen cells. For example if you choose B3, then all of the A column and rows 1 and 2 will be frozen in place. Then to freeze the planes, go to the view tab then select the freeze pane dropdown and choose the freeze pane button. Now if you scroll up and down you should see that your frozen panes will not move.

Excel Figures

Being able to represent data in an easy to interpret manner is crucial in Engineering. An easy way to do this is to represent the data in a graph or figure. For example we may want to explore the effect that the velocity of a fluid in a pipe may have on a property called the Reynolds Number. The Reynolds number is a dimensionless value used in fluid mechanics to determine the flow regime, this value is dependent on the velocity of the fluid. In engineering settings a scatter graph is often employed as it easily allows for a data trend to be identified.

On the x-axis we want our independent variable, in this case this is the velocity of the fluid through the pipe. Therefore, our dependent variable is the Reynolds number and this belongs on the y-axis.

Firstly, we want to select a scatter graph from the charts section of the insert tab. Once you have a blank chart you can right click on the chart and head to the select data section. The data can then be added to the chart by creating a new legend entry and highlighting the cells that contain your data within the sheet. This will create a chart from your data. You can use the chart formatting section to add a chart title, axis labels, a legend as well as trend lines if they are appropriate for the plotted data.

Excel Worksheet

Hopefully now you have a better understanding on how to use Excel as well as an insight into how Excel may be used by a Chemical Engineer. It’s now time to put those skills into practice. Linked below there is a PDF document and an excel workbook. The word document contains instructions on how to complete some basic analysis on the example data found in the excel sheet. If you get stuck or want to check your answers, there is a possible solution shown in the video below.

Worksheet Links:

EngBAM by University of Birmingham School of Chemical Engineering is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
Based on a work at engbam.com.