Microsoft Excel – Lab 2016-17

Excel lab Session 1

Finding your way within Excel
Students in ACCO1095 and ACCO1098 typically display different level of Excel skills. To start levelling
the playing field, we will aim to explain and understand basic terminology in the most-used
spreadsheet software there is. In this section, you will learn to name the different parts of the Excel
window, as well as how cells and arrays are addressed in Excel.

Explain terminology
In the beginning, we will look at different terms relating to the spreadsheet application and window
itself. You will need to be able to explain the following terms at the end of this section:

• Spreadsheet application;
• Worksheet
• Workbook
• Ribbon - tab/group
• Rows/columns
• Arrays

2. Open “1095_Basics.xlsx”.
3. The whole application is called a "spreadsheet application", and the file you just opened is a
"workbook".
4. A "worksheet" is a single screen within a workbook, showing columns and rows. There can
be more than one worksheet in a workbook.
5. On the bottom of the screen, you can see the worksheet tabs. They are initially named
“Sheet 1”, “Sheet 2” etc. by default, but you can change their names with a double-click.
6. On the top of the screen, we see the "ribbon", which consists of various tabs. On each “tab”,
we can see so-called "groups" of logically related menus and commands.
7. Within a worksheet, the rows are indicated by numbers, the columns by letters. A single cell
can be referenced using this system. For instance, if we wanted to indicate a value in the cell
in column G, row 6, then we would refer to it as "G6". To the cell in column B, row 14, we
would refer to using "B14".
8. An array is a range of cells. For instance, a range of cells starting with cell A1 and ending with
cell A10 can be referred to as "A1:A10". Array references always use the top-left and the
bottom-right cell.
9. For instance, an array starting in cell A2 and ending in cell B16 can be referred to as
"A2:B16".

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

10. There are many more terms in Excel, but these are for now the most important ones to know
when working in and with Excel.

Entering/editing/formatting data
Excel allows you to enter all sorts of data. Predominantly, these are numerical values, text strings,
and formulas. If you are an advanced user, you can enter data in cells using tailor-made forms and
self-programmed macros (however, we won’t cover these in this lab).
1. Select worksheet “Employees” in the same workbook as before. As you can see, there is
already some data in this worksheet. We want to add some additional datasets to it.

2. We need to add the following four datasets to the table, starting in cell A8:

7 Jack Male Helsinki 15000

8 Ted Male Copenhagen 30000
9 Lucy Female Stockholm 30005
10 Michelle Female Stockholm 28000

3. To jump into the next cell on the right-hand side, press the "Tab" key on the keyboard (left of
the key for the letter "Q"). To jump into the cell below, press the "Enter" key; alternatively,
you can use the arrow keys to move in any direction.

4. You can speed up typing in numbers if they follow a logical sequence. For example, if you
wanted to type in the remaining numbers 7 - 10, you can select cells A6 and A7 containing
number values “5” and “6”.

5. Highlight the two cells. Now look for a small black square on the right bottom corner of this
selection. Hover the mouse pointer over it, until it turns into a black cross.

6. Press the left mouse button without releasing it. Drag the selection down to cell A11. Can
you see how Excel now automatically increases the numbers based on the cells before?

7. If some data may be too large for the cells (then showing ####), the easiest way to adjust the
cell width is to double-click on the gap between the highlighted column and the adjacent
one. In the right spot, the cursor will look like a strip with two arrows.

8. The salary numbers look ok, but we want to give it a currency format.

9. To achieve that, highlight all the cells you want to format. Now, click on the "Home" tab and
look for the "Number" group.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

10. Click on the drop-down list, and look for "Currency". Click on it. Depending on the preferred
currency, you will most likely get "$", "€" or "£". Select More Accounting Formats, and select
“SEK”. It will add the SEK currency format as well as two decimals to each monthly salary
value.

Customise data tables (fonts, colours)

Although we have calculated all the values required, the table does not look very appealing. We can
make this table look nicer for our manager.
1. Select the "Home" tab, look for the "Font" group.
2. You can experiment a little here. Try different font types, font sizes, bold, italic or underlined
data, or colour the cell or the text/numbers. Investigate and try the various options here.
3. For now, we will just make the table headers bold, give it a nice yellow background colour,
and add some borders around your data. Keep saving your work.
4. You can also apply a pre-defined table style to an existing table. To do so, select the table you
want to apply a table style. This displays the Table Tools, adding a Design tab.
5. On the Design tab, in the Table Styles group, do one of the following:
a. Click the table style that you want to use.
b. Use the arrow buttons to scroll through the available table styles.
c. Click the More button, and then under Light, Medium, or Dark, click the table style
that you want to use.
6. Save your work.

Entering and editing basic formulas

Basic formulas can be created by any user from scratch, such as sums, subtractions, multiplications or
divisions. We will continue using the employee-data in 1095_Basics.xlsx to demonstrate this. The
question we want to answer is: What if we wanted to know how much our employees earn annually
with only the monthly salary stated?

1. On paper, you would multiply the monthly salary by 12 months. In Excel, we can get this
done by using formulas.
2. Let's add another column header called "Annual salary". Then, select cell F2.
3. You trigger a formula by typing the "=" sign in the cell you want something calculated for you.
4. For the annual salary to be calculated, we first need the monthly salary which is stated in
column E. Click the respective cell for the first employee, Charles.
University of Greenwich, Business School
Microsoft Excel – Lab 2016-17

5. Do you see the way Excel highlights the border? This indicates that it has been selected for a
formula. Don't click anywhere else, otherwise the then selected cell would be used for the
formula.
6. Next, enter the "*" symbol for multiplication. Now the border has stopped moving, but has
taken on a pre-assigned colour – the same colour as the cell reference in the formula itself.
7. To calculate the annual salary, we now enter "12", and press "Enter". This formula has now
successfully calculated the annual salary for Charles.
8. To save time, we can just copy down the formula to calculate the annual salary for all other
employees. Use the same approach to copy the content of a cell to another as before.
9. You can see that Excel has replicated the formula in all remaining cells, and the annual salary
of all employees has been calculated accordingly.
10. Let's do some more formulas. How about calculating the total monthly and annual salary of
all employees? How would you do that?
11. Obviously, you would add up the individual values. You could now either select every cell,
type "+", select the next cell, type another "+" - but that would take quite long in large
datasets. Of course, Excel provides a solution for such an occurrence with pre-designed
formulas. One of the most used ones is "SUM".
12. Enter "=" in cell E12. On the left-hand side of the formula box, you can see a drop-down list.
Open it and click “SUM”.
13. A box pops up requiring you to type in the "function arguments". These are just the
components of the formula, so for “Sum” the numbers you want added up.
14. Click in the field next to "Number1", and delete the pre-selected cells if they are incorrect.
15. Select the cells you want added up. You can see a preview of the result in the dialog box
itself.
16. Press "OK".
17. As an alternative to steps 12 – 15 above, start typing "SUM" after the "=" (it does not matter
whether you use capital letters here or not). Excel will start suggesting formulas whilst you
are typing.
18. The result of both approaches will calculate the total monthly salary of all employees. Copy
the cell with the formula to cell F12 to calculate the total annual salary of all employees.

Organising data & structuring worksheets

It is good practice to keep worksheets simple. This also allows other users to work with the data. It
allows for simple operations like sorting or filtering.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

Sorting data
Sorting data is quite helpful to bring order to an otherwise jumbled list of data entries. For instance,
we might want to display the list of employees starting with the staff member that receives the
highest annual salary in descending order to the lowest-paid person.

1. Select the values in the “Annual salary” column first, then expand the selection the other
columns. We need to highlight the cells in this order to tell Excel by which values to sort the
table (if you select the values starting from column A, Excel will sort the table by ID instead of
annual salary).
2. On the “Home” tab, look for the “Editing” group. If we want to sort the salary from highest to
lowest, we need to choose the “Sort from Z to A” command. If you’ve done it correctly, the
table will be sorted showing the employee with the highest annual (and monthly) salary on
top.
3. Save the file as “1095_Basics_solved.xlsx” (whenever you save a local copy of the
spreadsheets in our sessions, add the _solved suffix to it). Leave it open for the next exercise.

Filtering data
If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can
be used to narrow down the data in your worksheet, allowing you to view only the information you
need.
1. Use the same workbook as in the previous exercise. For filtering to work correctly, your
worksheet should include a header row, which is used to identify the name of each column.
In our example, our worksheet is organized into different columns identified by the header
cells in row 1.
2. Select the Home tab, then click the Filter command. A drop-down arrow will appear in the
header cell for each column.

3. Click the drop-down arrow for the column you want to filter. In our example, we will filter
column C to view only the female employees.
4. The Filter menu will appear. Uncheck the box next to Select All to quickly deselect all data.
5. Check the boxes next to the data you want to filter, then click OK. In this example, we will
check “Female”.
6. The data will be filtered, temporarily hiding any content that doesn't match the criteria. In
our example, only the female employees are visible.
7. Filters are cumulative, which means you can apply multiple filters to help narrow down your
results. In this example, we've already filtered our worksheet to show female employees,
and we'd like to narrow it down further to only show female employees that work in the
Stockholm office.
8. Select the header cell for the office locations. Deselect all, then select Stockholm as office
location. The table now shows the female employees in the Stockholm office.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

9. To clear all filters, click the drop-down arrow for the filter you want to clear. Choose Clear
Filter From [COLUMN NAME] from the Filter menu. The previously hidden data will be
displayed.
10. To remove all filters from your worksheet at once, click the Filter command on the Data tab.
11. Save your work.

Referencing
Working with formulas can be a time-consuming task, especially in large datasets. However, we often
do not need to write the same formula repeatedly if we understand how cell references work.
Knowing their mechanism can help us to create useful spreadsheets in a short amount of time – as
such, references are a cornerstone of understanding how Excel works.

Relative, absolute & mixed references

1. Let’s open the file “1095_Referencing.xlsx” and fill it with some simple data on the first,
empty worksheet.
2. Enter the values "20" and “25” into cells A1 and A2, and add them up in cell B2 using “+”.
3. The result should be "45". Now, what do you think would happen if we copied the formula in
cell B2 to cell B3?
4. Select cell B2, and then copy the selection down to cell B3. Oddly enough, the result is not 45
anymore, although the formula worked the first time. After having copied it, the result is
clearly wrong. So what happened?
5. Selecting cell B3 and looking at the formula definition reveals that instead of “A1 + A2” as we
initially stated, it states “A2 + A3”. This happened because of how Excel applies cell
referencing in formulas.
6. When you copied the cell, Excel did not only copy the formula, but also determined the
location of the cells used in the formula, relative to cell B2, and copied this as well. Look
where the referred cells are relative to B2.
7. The first cell, A1, is one cell to the left and one cell up. The second cell, A2, is one column to
the left.
8. When you copied the formula to cell B3, Excel copied exactly these cell references to B3. One
cell to the left and one up from B3 is cell A2. One column to the left of B3 is cell A3. In other
words, the formula in cell B3 added “25” and “0”, as there is no value stated in cell A3.
9. Since Excel determines the location of the cell references in a formula relative to the cell
where the formula is defined in, we call this type of reference a relative reference.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

10. So what do we need to do in order to get the correct answer in cell B3? We need a way to
keep the references pointing to the same cells, no matter where in our worksheet or
workbook we copy it to.
11. We need to make the reference absolute. We can do this in two ways.
12. First, select the cell containing the formula we want to change, in our case cell B2.
13. Move the cursor to the formula bar. We can tell Excel to keep the references absolute by
adding a dollar-sign ($) in front of the column letter and the row number in the formula.
14. Change the formula to this: =$A$1 + $A$2, and press "Enter". This notation indicates an
absolute reference, and it keeps the formula components locked to specific cells. You need
to type the “$”-sign twice (once for the column, once for the row) to keep it locked to that
particular cell.
15. Now copy the formula to cell B3.
16. There you go! The result remains "45". Try copying it wherever you want in your workbook,
the absolute cell references keep it locked onto cells A1 and A2.
17. As an alternative to steps 13 – 14, you can click (or select) the relative cell reference in the
formula input box, and press the F4 key on your keyboard once. The “$”-sign will be added to
the cell reference accordingly.
18. Apart from relative and absolute cell references, there is a third type called mixed references
which mixes absolute and relative references to one cell.
19. Select the sheet called "Mixed". You can see some pre-filled data there: four products called
A, B, C, and D; their unit prices, as well as a table with the number of units sold for each
product in the months of May to August.
20. Below is sales table which we want to fill in quite quickly without having to repeat typing the
formula over and over. We can do that using mixed references. But first, let’s try to
understand why using “purely” relative or absolute references won’t work in this case.
21. Select cell B10. We want to multiply the price for product A with the number of units of A
sold in May. Enter "=", then select cell B2 and multiply the price of product A with the
number of units sold in May in cell B4. Press "Enter".
22. As expected, the formula calculates £120 as sales income from product A in May.
23. Try what happens when we copy this formula over to cell C10.
24. So far, it seems as if it worked quite well. The total income for product B in May is correctly
calculated. Let's repeat this step for cells D10 and E10. Don't forget to hit "Enter" first when
your cursor was in the formula bar as in our example just now, otherwise you will change the
formula.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

25. Let's recapitulate: when we copied the formula in cell B10 across, we didn't need to modify
the references in the formula to absolute. But what happens if we select cells B10:D10, and
copied them down to row 13?
26. The results do not look right at all. There is obviously something off with the formulas we just
copied.
27. Let's check. Select cell B11, and click into the formula bar. Do you see what happened? Excel
has copied the relative location of the cells in the formula one row down. This would be ok
for the reference to the sales of product A in June, but it lost the reference to the price of
product A.
28. Press "Enter" to leave the formula highlight mode, then select cell B10 again. Let's consider
the following for a moment.
29. We want to keep the reference to the cell containing the price per product, no matter where
we copy the formula. On the other hand, we want the reference to the sales in units to move
with the formula. It already does that, so we don't need to modify the cell reference to cell
B4.
30. Let's make the reference to cell B2 absolute and see what happens.
31. Copy the formula across to cell E10 (you know by now how to do that). Now that doesn't
look right, either.
32. Select cell C10, and click with the cursor in the formula bar to investigate the references. The
formula kept the absolute cell reference; however, it kept it locked to the price of product A
when we needed the price for product B in this cell.
33. The answer how to solve this is to use a mixed reference, since neither a relative nor an
absolute reference did the job for us.
34. We need to decide now whether we want to keep the column or the row absolute. This is
quite easy to tell now after all our deliberations.
35. Looking at the formula in cell C10, we want the column to change once we copy the formula,
but when copying the formula down, we don't want the reference to the row to change.
36. Therefore, it is the row we need to keep absolute, and the column relative. Press "Enter" to
leave the highlight mode.
37. Select cell B10, then move the cursor to the formula bar. There, delete the dollar-sign in front
of "B", but leave it in front of "2". Alternatively (again), you can use the F4-key, but you need
to press it twice now. As you can see, you can cycle through the various possible references
using the F4-key repeatedly. Press "Enter."
38. Now copy the formula across to cell E10, then down to row 13.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

39. That looks much better now. Let's test the formula with one cell, let's say cell D12. Click the
cursor in the formula bar to highlight the references.
40. As you can see, it is correct. The total sales for product C in July are the 21 units sold for C in
July times the price of 34, resulting in a sales revenue of £714.
41. Save your work.

Working with multiple worksheets

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to
reference a specific value from one worksheet to another. To do this, you’ll simply need to begin the
cell reference with the worksheet name followed by an exclamation mark (!). For example, if you
wanted to reference cell A1 on Sheet1, its cell reference would be Sheet1!A1.

1. Open workbook 1095_MultipleWorksheets.xlsx, and go to the Menu Order worksheet.

2. Locate cell E14 that contains the total amount for this order.
3. Navigate to the Catering Invoice worksheet. As you can see, the amount for the menu order is
missing in cell C4 there.
4. Enter the “=” sign, the sheet name (in this case between inverted commas because it is a text
string with a blank space), an exclamation mark (!), and the cell address. In our example, this
would read =’Menu Order!E14.
5. Alternatively, you enter the “=” sign, then select the Menu Order worksheet, and select cell
E14, which will then create the reference.
6. For both steps 4 or 5, press the Enter key. As you can see on the Catering Invoice worksheet,
the value from the Menu Order worksheet is now filled in. That also means that any changes
to the total amount for the Menu Order will subsequently be reflected on the Catering Invoice.
7. This reference will be kept even if you chose to change the name of the worksheet at a later
point.
8. Save your work.

Further practice with multiple worksheets

When working with a large amount of data, you can create and use multiple worksheets to help
organise your workbook and make it easier to find content. You can also group worksheets to quickly
add information to multiple worksheets at the same time.

1. Open workbook 1095_MoreMultiSheets.xlsx. You can see monthly budgets for 2016, one
worksheet per month (except December).
2. To insert a worksheet, locate and select the New sheet button near the bottom-right corner
of the Excel window. Click on it, and a new blank worksheet will appear.
3. To duplicate the content of a worksheet, right-click the worksheet you want to copy (e.g.
November), then select Move or Copy from the worksheet menu.
4. The Move or Copy dialog box will appear. Choose where the sheet will appear in the Before
sheet: field. In our example, we’ll choose (move to end) to place the worksheet to the right
of the existing worksheet. Check the box next to Create a copy, then click OK.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

5. The worksheet will be copied. It will have the same title as the original worksheet, as well as
a version number. In our example, it is named November (2).
6. Let’s rename the worksheet to December. Either right-click the new worksheet to select
Rename from the worksheet menu, or double-click the name and type in December directly.
7. Drag and drop the December worksheet, so it will be moved to right after the November
one.
8. Finally, we will assign different colours to the worksheets. Right-click the January worksheet
tab, then hover the mouse over Tab Color. Various colours will be displayed.
9. Select the desired colour.
10. To delete the Sheet1 worksheet we created earlier, right-click the Sheet1 tab and select
Delete.
11. Finally, to protect a worksheet, right-click on the desired tab and select Protect Sheet.
12. Save your work, but leave the workbook open.

Grouping and ungrouping worksheets

You can work with each worksheet individually, or you can work with multiple worksheets at the
same time. Worksheets can be combined into a group. Any changes made to one worksheet in a
group will be made to every worksheet in the group. In our next exercise, we want to combine the
months in the last quarter of 2016 into one group.

1. Select the first worksheet (October) you want to include in the worksheet group.
2. Press and hold the Ctrl key on your keyboard. Select the November and December
worksheets. Once all three tabs are selected, release the Ctrl key.
3. The worksheets are now grouped.
4. Highlight cell array H9:J13 on the October tab, and change the colour to yellow. If you check
the November and December tabs now, you will see that the same cell array has been
coloured in yellow now.
5. To ungroup the tabs again, right-click any worksheet in the group, and select Ungroup Sheets
from the worksheet menu.
6. Save your work.

Working with named ranges

Working with ranges in Excel is a fast and simple way to identify, define, or refer to a single cell, a
range (or group) of cells, a specific or constant value, or a formula. Then you can use those range
names in your formulas or macros to replace values or cell references, or to quickly and more easily
navigate through your spreadsheets and workbooks. Ranges are often named after the field (or
column) names that define their contents.

1. Open 1095_MultipleWorksheets.xlsx. We will change the formulas on the Menu Order

worksheet using name ranges instead of cells.
2. Highlight cell array B4:B13, then go to the Formulas tab and click Define Name.
3. Notice that Excel has already entered the column/field name of the range you highlighted
(even though that cell was not included in your range). Change the suggested name to
Unit_Price. Note the Scope field setting that you could use to determine whether the name
range is valid for the entire workbook, or only one sheet.
4. Highlight cell array C4:C13, and allocate the name Quantity to this range.
University of Greenwich, Business School
Microsoft Excel – Lab 2016-17

5. Ignore the sales tax column. Now highlight cell E4. Enter the “=” sign and type
UNIT_PRICE*QUANTITY. Press the Enter key. Navigating back to cell E4 shows you the
formula using the newly defined name ranges.
6. Copy down cell E4 to E13. As you can see, using name ranges may help understanding
formulas quicker, and make a spreadsheet easier to work on for other users as well.
7. If you want to change the name of a range, navigate to the Formulas tab and click the Name
Manager. By selecting any of the existing name ranges, you can edit them to allocate a new
name, or delete the range (which does not delete the data). Excel updates wherever that
name range has been used before (such as a formula).
8. Let’s assume we want the average unit price of this order. Locate and select cell B15, enter
“=” and type average(u.
9. As you can see in the list that now appears, the unit price range is suggested as a potential
argument variable. Double-click to select it, and close the bracket.
10. The average unit price is now displayed. Save your work.

Working with predefined formulas

In Excel, you do not need to define every formula from scratch. Such an approach might be very
time-consuming, especially for more complex formulas. In this section, we will see how we can use
predefined formulas (such as “SUM”) in Excel – as you will see, there is a myriad of formulas that you
could potentially use in your spreadsheets. (Note: From here on in, you will be provided with less
detailed steps – please take notes accordingly, and ask questions if something becomes unclear).

1. Open 1095_Referencing.xlsx again, and select worksheet “NPV IRR”. You can see some
prefilled data here for a simple investment appraisal calculation. Some of the data has been
entered manually, and others by using a formula (such as the payback period).

2. In class, we want to calculate the NPV and the IRR of the simple project in this worksheet. To
start, select cell B19.

3. Go to the “Formulas”-tab on the ribbon, and look for the “Function Library” group. There,
open the drop-down list for “Financial” formulas.

4. The functions for NPV and IRR are in this category. First, look for the NPV function. As you
can see, by using this route to enter a formula, you do not need to enter the “=”-sign. In the
pop-up box, select the rate of return for “Rate”, and the cashflows in cells C5:G5 for “Value1”.
Be careful not to select the initial investment of £15,000 here, as we do not need to discount
this value (we hope you remember this from ACCO1095/1098!). Press “Enter”.

5. The result is the sum of the discounted cashflows in years 1 – 5 including the terminal value.
All that is left is to include the initial investment. Highlight cell B19 again and add the
corresponding value to the formula (no need to subtract it – the value is already stated as
negative).

6. The result should be an NPV of £5,063.78. Now let’s do the IRR.

University of Greenwich, Business School

Microsoft Excel – Lab 2016-17

7. The IRR-function is in the “Financial” category as well. Enter the formula in cell B21. In the
following pop-up box, choose all values in cells B5:G5 this time. Ignore the “Guess” argument
of this function.

8. The result should be 0.164319312. Format this cell as percentage with two decimals, so it
shows up as 16.43%.

9. Save your work.

University of Greenwich, Business School