2 Introduction to Excel

Author: E.W. Hans

Almost all businesses and organizations use MS Excel for a wide array of managerial or administrative functions. Excel has a huge amount of useful spreadsheet functionalities, and even comes with an integrated programming language (Visual Basic for Applications – not covered in this manual). It makes Excel an indispensable tool, which will be of use to you during and after your studies. Excel is not ideal for ‘big data’ (millions of data points), and tends to get slow. For this purpose, more advanced/dedicated software is recommended.

This tutorial introduces Excel’s basic functionalities. You may be familiar with Excel to some extent. In that case, this manual probably still contains functionalities and tricks you did not know about. In any case, this manual is compiled from all functionalities that are deemed ‘useful’ by students and staff from the BSc and MSc programs that use it. Your feedback and ideas for additions are therefore much appreciated!

Any (experienced) Excel user will regularly use the Excel help-documentation or use online search engines to look up how certain Excel functionality works. This is completely normal, as there are simply too many things to remember by heart. Nevertheless, looking up and reading a ‘how to…?’ is time intensive, so it pays off to be able to work fast with Excel and to remember as many functionalities and shortcuts as possible. As this manual focuses on the basic elements of Excel, it is recommended to know most of these by heart.

2.1 Excel Interface and Settings

2.1.1 Fine Tuning the Interface and the Ribbon

Start Excel via the Windows Start menu (press the windows key or click the windows menu button, and type “excel” to quickly find the Excel icon). After Excel has started, you see the following window, which allows you to create a new Excel data file (which is called a workbook), open an existing one, or open the Excel options menu (see below).

Once you have opened a data file, you enter the Excel working environment. Many of Excel’s commands are grouped together in tabs. The tabs together form the so-called Ribbon (see below).

You can customize the Ribbon via the Excel options menu. Within the options menu, select the Customize Ribbon submenu:

For example, the “Developer” tab is disabled by default. Enabling it gives access to the Visual Basic for Applications (VBA) programming environment.

Another submenu is called Add-ins. Open it. As you can see in the screenshot, this submenu allows you to view and manage plugins for Excel.

Click on the “Go” button to manage your add-ins.

Make sure all add-ins are checked like in the screenshot, and press OK. Now, these add-ins are available to you. You will use the Analysis Toolpak in this manual. Other add-ins are likely useful at a later time. For example, the Solver add-in allows you to solve mathematical programming models.

Excel has a status bar at the bottom of the screen, which can contain useful information. To illustrate this, put some data in cells A1, A2, and A3, like this:

Once you have selected the three cells (as in the screenshot above), some statistics for these cells will appear in the status bar, like Average, Count (= number of selected cells), and Sum:

By right-clicking on these statistics, you can customize the status bar. Make sure that Minimum and Maximum are checked. The status bar will then look like this:

This is very useful to quickly get some statistics, without actually having to use formulas, by simply selecting the cells.

2.1.2 Language Settings

Excel can be used in multiple languages. Different languages may use different names. This manual assumes that the English “display language” is used. You can enable these settings under the tab “File”, followed by “Options”, and then the “Language” item (see below).

If you do not see the language of your preference in this list, you may add languages via the Microsoft Office Language Preferences (In Dutch: ‘Taalvoorkeuren voor Office’), which you can find in the Windows start menu. In some cases, your Office license only allows you to use one language.

You may use your own language setting; translation tables for Excel functions can be easily found via Google, like e.g. https://www.excel-function-translation.com.

2.1.3 Excel on a MacBook (MS Office for Mac)

The most recent versions of Excel for Mac contain largely the same features as the MS Windows version of Excel. We highlight some differences here, which may change in future versions, or may already have improved in the version you have.

Keyboard shortcuts on PC and Mac are different. For example, for “Page Up” or “Page Down”, you need to click “FN + Up/Down arrow” on Mac. Some of the shortcuts that work on PC would not work on Mac (for example, “Paste only formulas” or “Paste Link” and others).
Excel for Mac does not support Pivot Charts. On Mac, the Pivot Charts are not interactive and behave like static screenshot-like graphs. They thus do not change simultaneously with the source Pivot Table.
VBA has less extensive features on Mac, which only becomes a problem when you are a more advanced programmer.

2.2 Assignments: Excel Basics

To practice with Excel we will use anonymized data from an operating room department and exercise through various assignments.

To complete the assignments you can use the Excel workbook “Patients.xlsx”. This file contains some data regarding surgeries carried out in an OR department, such as the surgery type, expected duration, and actual duration. Open the file Patients.xlsx. If you have downloaded the file from a browser, make sure you do not open it within the browser, as the file will be “read-only” and you hardly have any Excel functionality. So, first save the file to your hard drive, and open it from there. Excel will then start automatically.

2.2.1 Quick Navigation

An Excel .xlsx document is called a workbook. Within the workbook are several worksheets called: “Sheet1”, “Sheet2”, etc. A worksheet is a table with numbered rows and alphabetically designated columns. This way, every cell within a table is denoted with a letter-number combination. For example, B3 is the 3rd row, 2nd column.

First, we will edit the screen so that the first row with titles will always be visible when scrolling down. Go to the tab “view”, and press “Freeze panes”. Then press “Freeze top row”. The first row now has a dark underlining and will always stay on the screen even when you scroll down (try this).
When you want to construct a formula, select an empty cell, and type =. When you continue typing, for example, AVE, then a list of commands (these are functions) will pop up starting with AVE.

Function Selection

Choose AVERAGE, and press tab. The remainder of the word will be completed by Excel, including the first parenthesis. Now select two cells with numbers, and type ) to close the formula. For example, you now have =AVERAGE(A1:A2). Press enter; the cell now contains the average value of the two selected cells.

HINT: if you forgot to type the closing parenthesis ), Excel will autocomplete it for you, without asking. If parentheses are missing in more complex formulas, Excel will ask you how to complete the missing parentheses.

Note: Excel formulas are not case sensitive. Excel will automatically format formula names with capitals.
When “creating” formulas, it is a huge time saver to be able to maneuver quickly through a worksheet. Repeat the following steps and observe what happens on screen.
- Select cell A2.
- Press CTRL + ARRO W DOWN (to jump to the last cell in the column).
- Press CTRL + ARROW RIGHT (to jump to the last cell in the row).
- Press CTRL + HOME (to jump back to cell A2).
- Press CTRL + SHIFT + ARROW DOWN (to select the entire column A2:A2101).
- Press CTRL + SHIFT + ARROW RIGHT (to select all data cells).
- Go to cell L2 (CTRL + HOME, CTRL + ARROW RIGHT, to quickly go to cell J2, then move to the right).
- Here, type: =SUM(.
- Now press CTRL + HOME. The cell content of cell L2 is now =SUM(A2.
- Now press CTRL + SHIFT + ARROW DOWN. The cell content of cell L2 now is: =SUM(A2:A2101.
- Close the formula: ), and press Enter. Cell L2 has now summed up A2:A2101.
HINT: You can quickly jump to a cell with the “Go To” functionality. Press F5 to use this. For example, jump to cell A1000.

If you were not familiar with these navigation key combinations, train yourself to use them ‘blindly’ by maneuvering through the data as fast as you can (with or without selecting data). Exercise this, as you will experience later that this makes you a lot more efficient.
You can use the mouse to select rows, columns, or ranges of cells.
- Try clicking on a column letter to select that column.
- Try clicking on a row number to select that row.
- Hold CTRL while clicking on column A and then column D. Now you have selected two non-contiguous columns. Of course, this also works on rows.
- Hold SHIFT while clicking on column B and then column E. Now you have selected all (contiguous) columns from B to E.
- Click cell B2. Hold the mouse down, and drag it towards cell E5. Now you have selected all cells {B2,…B5,C2,…,C5,D2,…,D5,E2,…,E5}. In formulas, this is written as B2:E5.

2.2.2 Range and Name

A Range is a collection of cells, for example, A1:A10 (The first 10 cells of column A), or A1:E1 (the first 5 cells in row 1). A range is used in formulas such as: =SUM(A1:A100). When you reference a range on a different worksheet, e.g., Sheet2, the range is denoted as follows: Sheet2!A1:A100.

You can also name a range, in the white space above column A, see the figure. In a new worksheet, enter some numbers in the cells A1 to A3. Select these three cells, and enter in the white space above column A the word UTWENTE. Now place in cell B1 the formula: =SUM(UTWENTE). If you type =SUM( and then press F3, you will see a list with all known ranges to Excel. You should only see the name UTWENTE. Select it, and close the formula with ). Check the result.

Named Range

2.2.3 Formatting Cells

In Excel, data in cells is treated differently depending on the format assigned to the cell. You can assign this using the Format Cells menu (see figure), which you can reach by right-clicking the cell and choosing Format Cells from the menu that appears. Alternatively, press the CTRL + 1 hotkey.

Type the number 3 in a cell and go to the Format Cells menu. When you now select the option Currency from the list on the left, the number 3 is automatically changed to € 3.00. For example, by choosing other options from the list, you can change the number 0.3 to 30% or the numbers 3/1 in 3-1-2010.

In addition, there is also an option Special, which allows data to be formatted as an account number, telephone number, social security number, or place.

Adapting the formatting of the data to the type of data has many advantages. For example, you can avoid unnecessary decimals, make long series of numbers (e.g., telephone numbers) more readable, or automatically show negative amounts of money in red. This increases the readability and clarity of large amounts of data. However, in some situations, there is no standard format for the type of data that you want to process. In that case, you can create your own custom layout (see screenshot).

Type the number 123456789 in an empty cell and press enter. Select the cell and go to the Format Cells menu. Select Custom from the list. In the box to the right, some standard possibilities are given by Excel. When you choose an option from the right-hand box, you will see above it how this option changes the format of your number (try this!). In some cases, however, none of these options is suitable. Suppose a number (serial number, patient ID, process code) starts with a 0, Excel automatically removes this 0. To prevent this, you can create a layout yourself and add it to the Excel options.
Type 0123 in a cell. As soon as you press enter, you see that Excel removes the first 0. Now right-click on the cell and go to the menu Format Cells (shortcut CTRL + 1), the option Custom. The word ‘standard’ appears in the text box under Type. Remove it and replace it with 0000 (four zeros) and click OK. This tells Excel to always fill the number in the cell with zeros up to 4 digits. The number 123 is now supplemented with zeros to 0123, when you type the number 12 this is added to 0012. However, when you type the number 12345, there is no zero because the number already consists of more than 4 numbers. This approach only works if you want all numbers to consist of the same number of digits (for example, with serial numbers).
Suppose you want to let every number start with two zeroes. In this case, you choose (in the text bar under Type) as a format 00###. The #-sign indicates that the original data may hold a digit. In this case, Excel will place two zeroes before every number input that contains at most 3 digits. When you type 1234 in a cell, Excel will change this to 01234. Input data with more than 5 digits will not be changed.
It is also possible to add letters to serial numbers. Suppose all serial numbers consist of an A followed by a maximum of 4 digits. Then use as formatting A000#, and all entered numbers will be completed by zeros up to four digits after the letter A.
In the list of Excel formatting options, you will see that it is also possible to add colors and parentheses. For example, type ** , click OK**, and type 5 in the cell. You will see that 5 is changed by Excel in (005) in blue.

It is not expected that you memorize all formatting options. However, you are expected to be able to change the formatting, and if necessary, use online resources to find the desired formatting options (e.g., Google “Excel custom number formats”).

2.2.4 Copy and Paste; Freezing Cell References

Calculate for the expected and the actual operating time the mean and the standard deviation (or the functions AVERAGE and STDEV.P), and place them somewhere at the top of column M or N. Make sure that Excel shows only 1 decimal (decimal). Use the Format Cells menu (shortcut CTRL + 1).

Note: We do not place the mean and standard deviation below the data columns but in a separate column, for a reason. Namely, when there is only data in a column (and no other calculations) you can calculate the average of a column as follows: =AVERAGE(G:G). You do not have to indicate the row numbers! When data is added, you do not have to adjust the formula either, because it applies to the entire column G. However, if you would place the average below the data (e.g., in row 2103) (as follows: =AVERAGE(G2:G2101)), and there are e.g., four rows of data, you first have to insert 4 rows to

add the data, and you have to change the formula to =AVERAGE(G2:G2105). That does work, but is not very useful.

With “copy and paste” you can have Excel do many calculations very quickly. It is important here to occasionally make use of the “freezing” of (a part of) the cell range in the formula. We will illustrate that in the next steps:
- Select cell L2, in which we have just calculated the sum of the cells A2:A2101.
- Copy cell L2 (using CTRL + C), and paste it (CTRL + V) to cell M2. Look at the formula in cell M2. This is =SUM(B2:B2101). The formula in L2 was =SUM(A2:A2101). By copying this formula one column to the right (from column L to M), Excel has increased the column-reference to column A by one to B. In other words: in cell M2 the data of column B is added.
- Copy cell L2 (with CTRL + C), and paste it (CTRL + V) in cell L3. Look at the formula in cell L3. This is =SUM(A3:A2102). The formula in L2 was =SUM(A2:A2101). By copying the formula one row down, (from row 2 to 3), Excel has increased the row-references to both row 2 and row 2101 by one, to resp. 3 and 2102. So, in cell L3 we are no longer including cell A2 in the sum, and are now including the (empty) cell A2102! How do we prevent this? This is done by freezing the row references in cell L2 with a $-sign, as follows.
- Change the contents of cell L2 to: =SUM(A$2:A$2101). When you now copy cell L2 to L3, the row references are ‘frozen’, i.e., are no longer changed (try this!).
- Similarly, column references are frozen by placing a $-sign before the column letter in a reference.
- You can quickly freeze references using the F4 button. Select cell L2, and select the cell-reference A2:A2101 as in the picture:
  
  Freeze References
- Press F4 a number of times, and observe every time what it does with the reference. All variants of freezing cell references with and without $-signs can be obtained this way.
- You can also press F4 immediately after having typed a cell reference in a formula. For example, type the following in cell L2: =SQRT(F2.

Now press F4, and close the formula with a )-sign, and press Enter.

Copy cell L2 to L3. The result is the same in both cells: =SQRT($F$2).

2.2.5 Conditional Format

To emphasize important data in large spreadsheets you may use the Conditional Format functionality. It determines the layout of cells based on standard or self-entered rules. To set a formatting rule for a collection of data, first select the data. Then go to the function Conditional Format in the Home-menu:

Click on the New Rule option. The New Formatting Rule menu is opened (see figure). Here you can select various options for formatting the data depending on the value. For example, numbers above or below a certain value can be colored. You can also provide all values from the set that are below the average with a specific layout. Even when text is used in the data, you can attach a layout to it. For example, you can color all cells with ‘yes’ green.

Use Conditional Format to change the font of all cells in column F with ages “65 and higher” into red and bold.

2.2.6 Autofill

Autofill is a function that allows you to fill cells quickly and automatically with data so that you do not have to do this time-consuming task. Autofill can for example be used to complete a series of numbers, days, or even formulas (like 1, 2, 3, ….). Excel will need at least two starting cells to predict a series of numbers. If you start with 1 and 2, then Excel will predict that the next one should be 3, etc. In the following example, one input is already sufficient.

Open an empty worksheet and write “Monday” in cell B3. (Note: you might have to use the same language as your Excel is in, so in Dutch “maandag”.) We want to place the days of the week next to each other in columns. You can do this by using Autofill:
- Position your cursor on the bottom right corner of cell B3. It will change into a small black + sign.
Autofill Handle
- After that, you can click (keep the mouse button pressed down) and drag your cursor to cell H3. Excel will now automatically fill in the correct sequence of days and you can even go beyond cell H3.
  
  Autofill Example
  
  Autofill Results
- Instead of dragging the cells, you can also double-click on the black + sign that appears when you hover your mouse over the bottom right corner of a selected cell. This will autofill all the cells positioned under the original cell up to the row that the previous column was filled.
Fill cells A1:A50 with the numbers 1 to 50 on a new worksheet. Place in cell B1 the formula =A1/2. Double-click the black cross, like in the screenshot. You will see that Excel applies autofill to the cells B2 to B50.
Delete the contents of cell A4 and B4. Put the formula =B1*2 in cell C1. Double-click on the black cross of cell C1. Now you can see that Excel stops to autofill cells when the previous column has an empty cell.

Autofill can also be used for months, years, dates, number series (like 1, 2, 3… or 2, 4, 6…. Or even Patient1, Patient2, ….).

2.2.7 Adjusting Column Width and Row Height

When words or large numbers are put into a single cell, it might occur that the cell is too small resulting in overlapping contents of cells or partly invisible contents. To fix this you can adjust the column width or the row heights of the cells, such that all contents are fully shown. There are two ways to achieve this:

First, select an entire column by clicking on the letter of that column. Then click with your right mouse button on the selected column and choose the option Column Width from the menu. Specify a number to increase the column’s width.
Move your cursor to the top row with the letters of the columns. Position your cursor on the border that two columns share, like in the screenshot. When your cursor changes into a plus sign with horizontal arrow signs, you can click and drag the column to enlarge it. You can also double-click on this special plus sign to “autosize” the column to the minimum required width to show all contents of its cells.

Column Width Adjustment

The exact same principles apply to changing the height of the rows.

If you want to enlarge multiple columns and rows, then you can select them all. When you then click and drag a border to the desirable size, all selected columns and rows will then get that same size. Again, you can also double-click and then the size of all cells will be the same and as large as is needed to show the contents of the largest cell.

TIP: By clicking on the empty cell in the upper left corner (left to column A, and above row 1) you can select the entire worksheet.

2.2.8 Graphs

To create an overview of the frequency of the various surgical surgeries, we want to create a graph that displays how often each surgical surgery has been performed. Excel can display data in various types of graphs. To do so, select the data and choose a graph type in the tab Insert.

After that, several graph layout options will be shown per graph type. The graph is displayed in a window and the taskbar Chart Tools appears at the top of the screen. The Chart Tools taskbar consists of the tabs Design, Layout, and Format, which can all be used to adjust a graph.

In the taskbar Design, you can switch the axes of your graph. To change the layout of the graph, select the specific part of the graph that should be adjusted (e.g., the axis or the legend). Next, right-click the selection and choose “Format [selected element]” from the menu.

In the taskbar Layout, you can change the graph title, axis, and legend, both in terms of content and layout.

For more information on charts in Excel, you can watch the following movie:

http://www.youtube.com/watch?v=-btUxQi76qI>

If you want to practice using the examples shown in the movie, you can download the corresponding files using the following link:

https://people.highline.edu/mgirvin/AllClasses/210Excel2010/Content/Ch00/Busn210Excel2010Chapter00ExcelBasics.xlsm

Microsoft also offers some nice tips on how to make charts:

https://templates.office.com/en-US/Ten-tips-for-charts-TM22516108

TIP: In the example above, we first selected the data and then chose a graph type. You can also do it the other way around (first select the graph type, then the data), but this way is usually perceived as much more complex.

Create a histogram that shows the frequency graphically. To do so, first calculate the frequencies per surgery type in column Q. You can do this using the COUNTIF function. Fill in the formula in cell Q2 and then use AutoFill to apply the formula to the other rows (up until row 28). To use autofill correctly, you must “lock” parts of the formula as it was explained before.

To be able to estimate the surgery duration, we want to know if there is a relation between the age of the patient and the duration of the surgery.

In a graph (scatter plot/dissemination graph), plot the two different variables against each other. Set the X-axis (horizontal axis) in such a way that only ages between 15 and 85 years are visible. Can a relation be recognized? (hint: add a trend line using the right mouse button menu)? On average, do the surgeries with older people require more time?

2.2.9 Data Analysis ToolPak, Histogram Function

In the following assignments, we will use the Data Analysis ToolPak. This functionality is installed in Excel, however, it is not yet activated (for unknown reasons) and as a result is not yet visible. Our first task is to activate the Data Analysis ToolPak add-in, if you have not done this when reading Section 2.1. The following movie contains instructions on how to do this (we recommend also selecting all other add-ins that have not been activated yet!):

http://www.youtube.com/watch?v=nfv1z2ko6jk

The previous movie is part of the YouTube channel “ExcelIsfun”. This channel provides you with numerous Excel tips and tricks. The channel itself can be viewed using the following link:

http://www.youtube.com/channel/UCkndrGoNpUDV-uia6a9jwVg

In the following assignment, we will use the Histogram function of the Data Analysis ToolPak. Watch the following movie (starting at 2:00) to get acquainted with the histogram function:

http://www.youtube.com/watch?v=-P5h45EDOm0&t=2m0s

We want to analyze the duration of surgeries in a graph. Make a histogram where in an interval of 10 minutes the frequency is displayed (use the Analysis ToolPak histogram function). When selecting the data (Input and Bin range), do not select the titles, only the data.

2.2.10 IF-Function

The IF-function is one of the most used functions. It allows you to link a condition to the value of a cell. To this end, use the following formula:
= IF(logical test; value if True; value if False)

Fill the cells A1 to A10 with arbitrary numbers between 1 and 10. In B1 enter the formula:
=IF($A1>=6;"pass";"fail")

In the first part of the formula, the condition is tested, namely whether the value of A1 larger or equal is to 6. The second part will be returned when the condition is met, in this case with the text ‘pass’. Because this is text, you must put it between quotation marks. When the condition is not met, the output in cell B1 will be the text ‘fail’. Copy this formula to the cells B1 to B10.

If you want an empty cell as output, enter "" into the formula (note: these are two double quotes, not four single quotes!).

IF-functions may also be used within other IF-functions if multiple conditions must be tested. Imagine you have a list of people with 3 columns, name, gender, and age. From this, you want to select all women older than 35. This can be done with the use of a so-called ‘nested’ IF-function (‘nested’ means a function within a function):

=IF($B2="woman";IF($C2>=35;"woman above 35";"woman below 35");"man")

An AND-function gives as output “TRUE” when all the conditions are met. When one or more conditions are not met, the output is”FALSE”.

An OR-function returns the text “TRUE” when one of the conditions is met and”FALSE” when none of the conditions is met. The AND-function and OR-function can also be used within an IF-function and combined with other functions.

People from age 65 and above need an extra inspection when they have surgery. We want to add this to our patient data.

Let Excel automatically add the text “extra inspection” in the column notes for these people.

We also want to quickly see the surgeries that took longer than 2 hours, with this we can see whether this happens a lot and for which type of surgeries.

Let Excel automatically set the background color of the cells where the surgery took more than 2 hours to red (use the previously explained conditional format functionality).

When you made a mistake in a formula and you want to adjust that specific cell, click on the cell and press the F2 key. With the arrow keys, you can then navigate within the formula and correct the mistake.

Try this by going to a cell with a formula and selecting it with a mouse click, then press the F2 key. You will notice that this is a quick and easy way to adjust the cell.

2.2.11 Excel Table

The Excel version of 2007 introduced a new concept: the Table. Check the following link for a video about the usability of the Table:

http://www.youtube.com/watch?v=FlVCocmicUo

Convert the patient-data to a Table (with CTRL + T). Name this table “Patients”.

In the following assignment, we will sort the data. First, study this video, where the Table function is also used (at the end):

http://www.youtube.com/watch?v=XC7l39bLegg

Practice sorting data with the patient-data, according to the methods mentioned in the video. For example, check the last assignment by sorting the data of the surgery time (increasing).

Approximately a quarter of the patients who had a surgery will be approached for a survey, in connection with research about their pain experience with this organization.

Add a column where automatically and randomly a quarter of the patients will be selected for this survey. You need to do this with the RAND() function, this will select a random number between 0 and 1. Hint: You can do it like this: if RAND() is smaller than 0.25, then select the patients (use the IF() function).

Note: Because you transformed the patient data into a table, the formula you put on the first row in a column will automatically be copied to all the rows below.

The RAND() function recalculates itself every time a cell in a worksheet changes (select an empty cell, press delete; you can see that the cell has another value). Make sure the column does not change by copying it. With the “Paste Special” function, you only select the “values”. You replace the formulas with the calculated values.

The financial administration requires a unique code, which consists of the “Patient ID” and the “Surgery type”.

Add a column that compiles this code (a text field), using the fields “Patient ID” and “Surgery type”. Use the auto-complete functionality.

2.2.12 Pivot Table

The pivot table (Dutch: “draaitabel”) is a valuable function of Excel. By using pivot tables, data can be analyzed quickly, dynamically, and flexibly. This data can be shown in a table or in a standard graph. First, take a look at this instruction video (the link will jump to 4:45, where the instruction about the pivot table begins):

http://www.youtube.com/watch?v=h_Mr4sb2BIs&t=4m45s

The associated Excel file is here:

https://people.highline.edu/mgirvin/AllClasses/210Excel2010/Content/Ch00/Busn210Excel2010Chapter00ExcelBasics.xlsm

Now we would like to analyze the patient data. For this, we will use a pivot table. In the following questions, you have to place each pivot table in a new worksheet, and make sure your calculations are shown with a maximum of 1 decimal. Excel will automatically add rows for subtotals: remove them using the pivot table’s options.

What is the average surgery duration of surgery type NI799 for women? Important: As explained in the instruction video, start by envisioning the pivot table’s rows and columns (i.e., what data fields are needed?), then design it. Here, the data fields needed are Surgery Type, Gender, and Registered Duration. The first two of these should be used for the rows and columns, and the last one to calculate the contents of the table.

Important note: If you change any source data, the pivot tables will not be up-to-date automatically (in contrast to all the other graphics and tables in excel).

Take a look at the average surgery duration of women and the surgery type “AF241” in your pivot table. Search in the source data for a woman with surgery type “AF241”, and change the duration of the surgery to 1000 minutes. Note: the average of the pivot table will not change. Right-click on the pivot table for the option “Refresh” to update the average.
What is the average surgery duration for surgery type KG242 for men above 56?
How many hours were spent in total on surgery type WP174 for women?
How many surgeries were done of type IB847 for women?
Which surgery type has the largest average duration? How long is it?
Which surgery has the largest surgery duration standard deviation? And how large is it?
Create a pivot chart from your pivot table, which should show the average surgery duration for each gender and surgery type. Notice that the pivot chart has dropdown items to select a gender or surgery type.

2.2.13 VLOOKUP

Suppose you want to look up some data in column ‘Y’ of a large table, in the row where there is a particular value in column ‘X’. For example, say you want the ‘Membership number’ for ‘Harry Carter’ from the table below.

You can use the AutoFilter for this purpose, however, if you want to find the data with a formula, you can use the search function VLOOKUP. Let’s first consult the Excel-help function, so type in an empty cell =VLOOKUP and then press F1. Also, watch this instruction video for more information:

http://www.youtube.com/watch?v=GZX2iXfqALs

The function call in the example should be =VLOOKUP("Harry Carter",$A$2:$D$9,4,FALSE)

To find the surgery time of patient number 543 using VLOOKUP, you need to type: =VLOOKUP(543,A2:J2101,10). There are 3 fields in this formula. The first field is the value you are looking for (543). This function will only work if the value is a number in a sorted column. The second field is the table where you are looking in A2:J2101 (Note: the top header row is not included, as we do not want to look through that row!). The first column of the table is the column where you search for the value 543. The last field is 10: if you found 543 in the first column, then the related row in table A2:J2101 needs to give the element in the 10th column. The result needs to be: 101.38. Check this.

If you use the VLOOKUP function in a table, and you copy/pull down the formula to the rows below, make sure to add the $-signs to the table reference argument (i.e., the second argument).

VLOOKUP has an optional fourth argument (TRUE/FALSE), called range_lookup. If set to TRUE, then VLOOKUP will do an approximate search, i.e., try to find the value that is closest to the value you are looking for. This only works with numbers. If set to FALSE, VLOOKUP will do an exact search.

Give the data of the patient who is 50 years old with AX939, using the AutoFilter-function.

Next to VLOOKUP there are also HLOOKUP and LOOKUP. Recently, also XLOOKUP was added to Excel. XLOOKUP is a simplified version of VLOOKUP, for when you need to find things in a table or a range by row. Like VLOOKUP, with XLOOKUP you can look in one column for a value, and return a value from the same row in another column. However, with XLOOKUP the return column may be on either side of the lookup column.

2.2.14 Various Functions

Check the following website with some explanation formulas, working with operators, and the use of brackets.

http://office.microsoft.com/en-us/excel-help/overview-of-formulas-HA102748997.aspx?CTT=1#BMcomponents

To get an idea of how the functions will work within Excel, you will regularly use the following functions.

AVERAGE determines the average of the values of the range
AVERAGEIF the average of the values that meet the condition
SUM adds the values of the range
SUMIF performs conditional addition
SUMIFS performs conditional addition, with multiple conditions
SUMPRODUCT sums the matrix- or vector-product of two ranges.
COUNTIF counts the number of times that a condition is met
AND tests whether two conditions are both met
OR tests whether (at least) one of two conditions is met
RAND draws a random (floating) number from [0,1 ]
RANDBETWEEN draws a random integer number between two values
ROUND, ROUNDDOWN, ROUNDUP rounds floating numbers to a given number of decimals
ABS determines the absolute value of the argument
SQRT takes the square root of the argument
RANK gives the rank (position) of a value in a list of values

For a more complete overview of mathematical functions, see:

http://office.microsoft.com/en-us/excel-help/math-and-trigonometry-functions-reference-HA102752944.aspx?CTT=1

The following functions are useful for using in formulas:

ROW for retrieving the row number of a cell
COLUMN for retrieving a column number of a cellINDEX returns the value of a cell in a table, based on a specified row and column
OFFSET gives a dynamic range that you can use in formulas

The following functions are very useful for editing or creating cells with text:

LEFT allows you to get a number of characters at the beginning of a text
RIGHT allows you to get a number of characters at the end of a text
LEN indicates the length of the text in a cell
& use this in the formula to link text to each other
CONCATENATE compiles texts into a sentence
TEXT converts a numeric value to a text
FIND looks for a text or character in another text
REPT repeats a character a number of times

Microsoft provides an excellent tutorial for the use of formulas:

https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173

https://templates.office.com/en-us/Formula-tutorial-TM16400656

Use these functions for the following exercises.

Go to worksheet2, which has a long column with (complete) patient names. Add two columns, and put the first name of the patients in the first, and the last name in the second. HINT: you need the LEN, RIGHT, LEFT, and FIND functions for this.
Add a column with “surname, first name” (hint: use the “&” sign to connect two strings together).
Add a column with an arbitrary age of each patient, between 0 and 100 years. Use the RANDBETWEEN function.

Every time you press the F9 button, the ages are re-drawn.

Select the column with ages, and copy them (CTRL + C). Right-click on the same column, and choose “Paste special”, and check the option “values”. The column is now overwritten with the age values. The formulas with RANDBETWEEN in it are now gone, and with F9 no ages are drawn again.
Add a column where the age is indicated as “child” (<18 years), or “adult” (18-64 years), or “65+” (for 65 and over).
Make a table in which you count “child”, “adult”, and “65+”. Use the COUNTIF function for this.
Do the same, but with a pivot table.
A tricky one: calculate the sum of the ages of patients younger than 10 years, but older than 5 years. Do this with the SUMIFS function.

2.2.15 Trace Precedents and Trace Dependents

The “Formulas” tab contains functionality to build and analyze formulas. Very useful are the “trace precedents” and “trace dependents” buttons.

Select a cell that is used in one of the formulas you have just built. Press the Trace Dependents button in the Formulas-tab. You see that arrows appear to all cells using this cell. This allows you to determine whether you can safely erase a cell without changing things elsewhere in the worksheet. Press the Remove Arrows button to remove the arrows again.
Select a cell that contains a formula. Press the Trace Precedents button in the Formulas tab. “Precedent” means predecessor. You see that arrows appear to all cells used in the formula. Press the Remove Arrows button to remove the arrows again.

2.2.16 Data Table

The data table functionality allows you to quickly analyze what happens to the outcome of a calculation when one or two variables you have used in the formula assume different values. As an example, we take the calculation of your Body Mass Index (BMI).

Create a new worksheet, which holds the following data:

In cell B3 there is a formula in which the BMI is calculated as the ratio of Weight and the squared Size (= B2/B1^2). Fill cell E1 with a reference to the cell B3 with the BMI, so with =B3. Fill cells D2:D27 with the numbers 65, 66, ..., 90.

Select the range D1:E27. Go to the Data tab, and under “What-If Analysis” select the “Data Table” option, as shown in the figure:

Data Table

Enter a reference to cell $B$2 (the cell with the Weight) in the “Column input cell” option, and press OK. You see that you have made a table that calculates the BMI for all variants of Weight, with a fixed Size (cell B1).

Write in cell H1 the formula =B3 (or a reference to the BMI formula). In cells H2 to H27, put the numbers 65, 66, ..., 90 again, and in the cells I1 to S1 the numbers 1.70, 1.71, ..., 1.80. Select the range H1:S27, and under “What-If Analysis” select the option Data Table. For Row Input Cell you choose $B$1** (the cell with the Size), and for Column Input Cell you choose **$B$2 (the cell with the Weight). Press OK, and view the result.

For a comprehensive explanation of the Data Table with 1 or 2 variables, see:

http://www.youtube.com/watch?v=hxm-mb_kZrQ

http://www.youtube.com/watch?v=kfznvWGzt0Y

2.2.17 Goal Seek

Goal seek allows you to determine what the value for one variable in a formula must be, to retrieve a desired outcome of the formula.

Imagine you want to know how much you should weigh to have a BMI of exactly 25. Click Goal Seek, in the Data tab, under What-If Analysis. You will get this screen:

Goal Seek

Goal Seek Screen

In the first box, you find an icon. When you click on this icon, you can select the cell in your worksheet where the formula is placed of which you want a certain outcome. Thus, select the cell with the BMI-formula. Type in the second box (“To value”) the number 25, and choose in the last box the cell where the weight is placed (see figure). Click OK. The weight that is calculated is 77.44 (with a size of 1.76). In other words: A weight of 77.44 and a size of 1.76 gives a BMI of exactly 25.

This video gives a more extensive explanation of Goal Seek:

http://www.youtube.com/watch?v=sPj6z8_TsAM

Printing huge worksheets

Watch this video: http://www.youtube.com/watch?v=vSEyIDGtfEs

Or these two: http://www.youtube.com/watch?v=NxfYESUnKoM

http://www.youtube.com/watch?v=d8Tc5rlGE5o

2.3 For Those Who Are Interested

The following is not mandatory, but useful for those of you who want to go a step further with Excel.

2.3.1 Data Validation

With data validation, you can set conditions for the values allowed in a cell. For example, you can create a dropdown list in each cell containing the appropriate value for that cell. See:

http://www.youtube.com/watch?v=Ko68RF89JVk

2.3.2 Array Formulas

Working with array formulas facilitates, for example, multiplying ranges (a range is a series of cells, e.g., A1:A100):

http://www.youtube.com/watch?v=FaLvuyeVen8

2.3.3 Dynamic Range

Excel functions like SUM and AVERAGE often have a range as an argument, e.g., A1:A10. What if this range should not be fixed, but depends on the number of (at that moment) filled cells? In that case, you speak of a dynamic range. You can use the function OFFSET() for this. This function has 4 arguments; see:

http://office.microsoft.com/en-001/excel-help/offset-function-HA102752910.aspx?CTT=1

The result is a range. So, you must use the OFFSET function within another function (e.g., SUM). A more complete explanation can be found here (video): http://www.youtube.com/watch?v=7le-m8YRP6M

In this video, the OFFSET function is used to print a dynamic range (in which only the cells with data are printed): http://www.youtube.com/watch?v=aLkNIVMnAZY

2.3.4 Overview of Useful Shortcuts

F1: Excel help file
F2: Start cell editor
F3: Call list with the names you defined
F4: Freeze (put $-signs in) a range or cell reference
F5: (just like CTRL + G) Show the “Go To” menu
F9: Calculate the results again in all worksheets and all workbooks
SHIFT + F9: Just like F9, but works only in the current worksheet
F12: Save as
CTRL + 1: Format cell
CTRL + HOME: Jump to the first cell with data top left in the worksheet
CTRL + A: (first time) Select current table, (second time) select the whole worksheet
CTRL + C: Copy selected cells
CTRL + F: Find and replace (the Find tab is selected)
CTRL + G: (just like F5) Show the “Go To” menu
CTRL + H: Find and replace (the Replace tab is selected)
CTRL + N: Start a new workbook
CTRL + O: Open an existing workbook
CTRL + S: Save the current workbook
CTRL + V: Paste the earlier copied cells
CTRL + ALT + V: Call the “Paste Special” menu (paste with extra options)
CTRL + X: Copy content of selected cells to memory, and erase the cells
CTRL + Z: Undo the last action
CTRL + Page Down/Up: Go to the previous/next tab
CTRL + ARROW DOWN/UP: Jump to the last/first value in column (add SHIFT to select the cells you are jumping)
CTRL + ARROW RIGHT/LEFT: Jump to the last/first value in row (add SHIFT to select the cells you are jumping)

2.3.5 Recommended Reading and Reference

https://people.highline.edu/mgirvin/ (various manuals, examples, videos)
http://www.youtube.com/channel/UCkndrGoNpUDV-uia6a9jwVg (YouTube channel “ExcelIsFun”)
https://support.microsoft.com/en-us/excel (the online Excel support of Microsoft)

Finally, because millions of people use Excel, you can usually quickly find a solution to almost any Excel question through Google. There are often multiple ways to get something done. It’s the result that matters!