We’ve all used Excel at some point for labs or presenting data, so this introduction is just about the various tips and tricks to make Excel easier to use for you once you’re in Engineering.

Formatting

One of the bigger annoyances when you open up a new file and start putting your info in is that Excel will try and make life easier for you by automatically changing things to a default format. When you’re entering something like dates or something with decimal points and it keeps changing things on you, just click here on the drop-down menu:

You can also change the cells you’re using to be dollar amounts, and change the number of decimal points.

We can also come over to this “Format” button:

And if you open it up you can see you can that you can hide columns or rows, and change the height of the rows in case you need to move things around to make the data easier to read. This will help when you have 20+ columns of data but don’t want to scroll back and forth. You can also lock cells, and even sheets if you need to keep certain things permanent.

Static Values or “Hard coded values”

Sometimes we want to drag a formula down an entire column so the whole column uses the same formula. This is an easy way to calculate a whole bunch of values, but you’ll see that as you move down the rows the formula will change to pull in the new row number! If we need to pull data from some particular cell that isn’t on every row, we can refer to it using “$A$1” as the template. Normally we would type “=A1” for our cell to make it equal the value stored in cell A1, but if we want to always refer to A1 we can use “=$A$1” to specify it should always equal A1.

Filters

Nothing complicated here – just a quick way for you to filter data down to certain values or types. This will let you easily group things together or find outliers.

PivotTables

PivotTables are rarely used while in university but do come into play in the workforce quite a bit. All a PivotTable does is take all the data that you have in a Table, and repackages it in such a way that the data is summarized. Let’s say you took a bunch of sales data from a fruit stand, and you want to see how many bananas you sold. You could throw it into a table, then filter it down, then manually add the filtered rows together; or, you could take that table, make a PivotTable of it, and have Excel automatically sum the banana sales for you to give you a nice round number.

Goal Seek

Easily the most important thing you will need for working in Excel. You may have to add it in to Excel but it’s a very quick and painless process. Goal Seek allows you to do trial and error without having to manually mess around with the cells a million times. You simply tell Excel that you want to get Cell X to equal a specific value, by changing Cell Y, and it will figure it all out for you.

Under the “Data Tab” at the top of the page you will find this “What-If Analysis” Section. Click on that, and then select “Goal Seek” from the drop down menu.

Macros

Otherwise known as Excel VBA, macros give you the ability to run code on your Excel workbooks. This is helpful when you have loads of data that needs to be processed and can be automated, saving you lots of time.

Excel