Chapter 2 A more complex spreadsheet in Excel

Introduction to Chapter 2

Now that you've gotten a feel for how Excel works, we'll build a more complex spreadsheet. The skills you'll learn in this section are:

• How to use AutoFill
• How to insert a row or a column
• How to add up numbers in a column
• And how to enter simple formulas for your calculations

The spreadsheet you'll construct looks like this, when it's finished:

Not much has been done in the way of formatting here, as we'll concentrate on how to add up in Excel. To make a start, follow along with the instructions below.

• Click inside of cell A1 on a new spreadsheet
• Type the text "My Chocolate Addiction", then press the Enter key on your keyboard
• Highlight the cells A1, B1 and C1, and Merge the cells, just like you did for Review One
• Your spreadsheet will look like this in Excel 2007:

Or this in Excel 2010 and 2013:

Now that you have a heading for your spreadsheet, we'll fill in the days of the week using something called AutoFill. This allows you to quickly fill in things like days of the week, months, and consecutive numbers.

How to use AutoFill

Your spreadsheet from the previous section should look like this one:

You have a title in cell A1, but nothing else. You'll now see how to use the AutoFill feature of Excell to quickly enter the days of the week. Off we go, then.

Excel AutoFill

Click inside cell B3 of your spreadsheet, and type Monday, as in the image below:

The days of the week are going to be entered on Row 3 of our spreadsheet, from cell B3 to cell H3. Fortunately, you don't have to type them all out. You can use something called AutoFill to complete a known sequence like days of the week. In other words, Excel will do it all for us.

• Position your mouse pointer to the bottom right of the B3 cell
• The mouse pointer will change to a black cross, as in the images below. The image on the left shows the normal white cross; the image on the right, the black cross, tells you AutoFill is available:

      

• When you can see the AutoFill cursor, hold down your left mouse button and drag to the right
• Drag your mouse all the way to cell H3, as in the following image:

• When your cursor is in the H3 cell, let go of the left mouse button
• Excel will now complete the days of the week:

And that's all there is too it! AutoFill can be a handy tool to use, when you want to complete a known sequence like days of the week, months, and even formulas. We'll use AutoFill on a column of numbers, shortly. But let's crack on with our spreadsheet

Now that we've got a heading for the spreadsheet, as well as the days of the week, we can enter a few chocolate bars.

• Click inside cell A4 and enter the name of a chocolate bar. You can enter anything you like, but we've gone for Mars Bars. In cell A5 we chose Twix, and in cell A6 Bounty. In cell A7 we typed Other
• In cell A9 of you spreadsheet enter the words Day Totals. Leave cell A8 blank. Your spreadsheet should then look something like ours below:

Time to enter some numbers.

• Click inside cell B4, and enter the number 1. Press the enter key on your keyboard, and the active cell will jump down to cell B5
• In cell B5 type the number 7. Press the Enter key again to jump down to cell B6
• In cell B6 type 8
• In cell B7 type 1
• Your spreadsheet will then look like this one:

To complete the numbers for the rest of the week, enter the following under each heading:

Tuesday: 2, 5, 3, 2
Wednesday: 1, 3, 2, 2
Thursday: 3, 2, 3, 2
Friday: 3, 4, 4, 2
Saturday: 2, 2, 1, 1
Sunday: 5, 4, 4, 1

When you're done, your spreadsheet will look like this:

Now that you have all those numbers typed out, we can move on to formulas, as you learn how to add up in Excel.

Entering Simple Addition Formula

The first thing we'll do to our spreadsheet from the previous section is to add up all those numbers, the ones going down under the days of the week headings. The total for each day of the week will be placed on Row 9. So Monday's total will go in cell B9, Tuesday's total will go in cell C9, and so on.

Here's our spreadsheet again:

Our first total will go in cell B9.

Adding up in Excel

Excel needs to know which cells you want to add up. Look at the numbers for the Monday column. We have a 1 in cell B4, a 7 in cell B5, an 8 in cell B6, and a 1 in cell B7. So we want the answer to this:

B4 + B5 + B6 + B7

To let Excel know that this is what we want, try this:

• Click inside cell B9, which is where we want the answer to appear
• Once you've clicked on cell B9, click into the formula bar at the top
• Type this:

B4 + B5 + B6 + B7

When you have entered the formula in the formula bar, press the enter key on your keyboard. Your spreadsheet should look like ours below:

Something has gone wrong! This is not quite what we were expecting. We wanted Excel to add up the numbers for us, but it hasn't done anything except enter the cells we typed.

What went wrong was that we didn't "tell" excel to add up. Excel needs you to type an equals (=) sign first, and then those cell references. If you don't include the equals sign, Excel thinks it's just plain text, and so doesn't do any calculating.

So enter this inside of your formula bar instead:

= B4 + B5 + B6 + B7

In other words, put an equals sign ( = ) before B4. Press your enter key and you should have the correct answer in cell B9.

Now click back inside the formula bar, and delete the equals sign. Press the enter key again. You should then just have the same text as in the image above. We're doing this to show you an easier way to add up - with the SUM function.

When your spreadsheet looks like ours in the image at the top of the page, you can nove on to the next part.

The Sum Function

You saw a simple way to add up in the previous section. Enter an equals sign, followed by the cells you want Excel to add up:

= B4 + B5 + B6 + B7

But this is not a good way to add up in Excel: it could get very tedious indeed if you had to type out say 50 cell references by hand. The easy way is to get Excel to do the work for you. That's where SUM comes in.

The Excel SUM function

The SUM function is used to add things up, and saves you the bother of typing out lots of cell names and numbers. It looks like this:

=SUM( )

In between the round brackets, you type what you want Excel to add up. Look at our spreadsheet again. Here it is in Excel 2007:

In Excel 2010 and 2013 you'll have this less colourful version:

We want to add up the numbers under the Monday heading, and place the answer in cell B9.

So with cell B9 selected again, click into your formula bar. If you're following along fromthe previous lesson, you should have this in cell B9:

If you have an equals sign before B4, delete it and press the enter key. Now position your cursor at the start of the line, before the "B" of B4.

Type an equals sign first, then the letter SU of SUM.

As soon as you start typing, Excel will present you with a drop down list of available functions. Click once with the left mouse button on SUM to highlight it:

Now double click on SUM. Excel will add the "M" for you, and the left bracket. It will also highlight the cells in your formula:

Now press the Enter key on your keyboard. Excel will add the right bracket, and work out the SUM for you:

Now click back on cell B9, and look at the Name box (just above the A column, in our image). It has B9 in it. The formula bar to the right shows you which formula you have in the active cell (B9).

An easier way to add up number with the SUM function is to use a colon (:) The colon is a shorthand way of adding up consecutive cells. Instead of typing out all those cell references like this:

=SUM(B4 + B5 + B6 + B7)

You can just type out the first cell reference, then a colon, then the last cell reference. Like this:

=Sum(B4: B7)

Excel will then add up the numbers in cells B4 to B7. It knows what the colon means!

• Click into cell B9, if it's not already active
• Now click on the cell with your right mouse button
• You'll see a menu appear:

• From the menu, select Clear Contents by clicking the item with your left mouse button
• This will clear the formula from the formula bar
• Now click back inside of the formula bar and type the following:

=Sum(B4:B7)

Your spreadsheet should look like ours:

When you have the formula typed out, hit the Enter key on your keyboard. Excel will add up the numbers for you, and place the correct answer in cell B9.

If everything went well, you should have an answer of 17 in cell B9. Fortunately, we can use AutoFill for the rest of the answers.

• Place your mouse pointer to the bottom right of cell B9
• The pointer will turn into a thin black cross:

• Hold down your left mouse button
• Keep it held down, and drag your mouse to cell H9:

With your mouse pointer over cell H9, let go of the left button. Excel will AutoFill the rest of the formulas. It uses the same formula from cell B9 to get the answers, and just alters all the cell references. Without AutoFill, you'd have to type it all out yourself!

The answers on Row 9 of your spreadsheet should be the same as ours in the image below:

Notice the formula bar in the image. It shows the formula in cell H9. This is:

=Sum(H4:H7)

The formula we started with was:

=Sum(B4:B7)

Excel has changed the letters for us, but not the numbers. In other words, it's adding up the columns.

If you think of the colon as the word TO, it should make sense:

Add up the cells B4 TO B7
Add up the cells H4 TO H7

In the next section, you'll get some more practice with this spreadsheet, and with the SUM Function. So don't forget to save the work you've done so far!

The SUM Function Continued

Using the same spreadsheet you've been working on in the previous section, you'll now get some more practice with the SUM function in Excel, in order to add up values in cells. Our spreadsheet now looks like this, though:

You've just used the easy way to add up values in consecutive cells for a column. Just do this:

=SUM(B4:B7)

Using that formula gave us the answer to how many chocolate bars we ate from Monday to Sunday. You can use this same colon ( : ) shorthand to add up numbers in a Row.

• Click inside cell J3 of your Chocolate Addiction spreadsheet
• Type the text Individual Totals (you may have to widen the column a bit, as you did for a previous section)
• Your spreadsheet will then look like this:

We'll use a SUM formula to add up the values in each Row. This will tell us how many of a particular chocolate bar we ate in one week: how many Mars Bars, how many Twix, etc.

The first answer we'll try is how many Mars Bars we ate in one week. We'll place this answer in cell J4. The cells we're going to be adding up are these:

B4 + C4 + D4 + E4 + F4 + G4 + H4

Because we have consecutive cells, we can use the colon shorthand again.

• Click into cell J4 of your spreadsheet
• Then click into the formula bar at the top
• Enter the following formula:

=Sum(B4:H4)

Press the enter key on your keyboard, and you'll see the answer appear in J4. To complete the rest of the rows, we can use AutoFill again.

• Click back in cell J4 to make it the active cell
• Move your mouse pointer to the bottom right of cell J4
• You'll see the pointer change to a thin black cross:

• Now hold down your left mouse button
• Keep the left button held down and drag down to cell J7

When your mouse pointer gets to cell J7, let go of the left button. Excel will use AutoFill to get the answers for the other three cells. Hopefully, your spreadsheet now looks like ours:

Select any of the cells J4, J5, J6 and J7. Then examine the formula in the formula bar. You should be able to understand what is being added up, and what all the formulas mean.

Now that we have totals for each individual chocolate bar, we can work out how many chocolate bars we ate for the whole week. We'll put the Grand Total in cell F11. First, we'll enter some text to explain what is being added up

• Click inside cell A11 on your spreadsheet
• Type the following text: Number of Chocolate bars consumed in a week
• Hit the Enter key on your keyboard
• You should see the text you just typed. But it will all be in individual cells. Highlight the cells A11 to E11, and merge them together (You learned how to merge cells in aprevious section.)
• This is what your spreadsheet should now look like:

There are two ways we can calculate the Grand Total. You can just add up the Individual totals in the J column, or ... Well, how else could you get the number of chocolate bars consumed in one week?

• Click into cell F11 on your spreadsheet
• Enter your formula to calculate the number of chocolate bars consumed in one week
• Hit the Enter key when you think you have the correct formula

The correct answer is 80. If you got a different answer, or are struggling in any way to come up with the correct formula, then it's a good idea to go over the previous section. But don't just type 80 into cell F11 and move on!

How to Copy and Paste

From the previous section, you now have a spreadsheet that looks like this:

If we're eating that many chocolate bars in one week, we'd like to know how much this habit is costing us! And what about the yearly cost of the addiction? Excel makes sums like this quite easy to calculate. First let's have some new headings.

Copy and Paste

To create your new headings, do this:

• Locate cell A13 on your spreadsheet and click on it
• Type Cost of Addiction
• Merge the cells, in the same way you learned previously
• Add a bit of formatting to the text, if you like

We're going to be needing the names of the chocolate bars again. These will go in cells A15 to A18. Instead of typing them all out by hand, Excel 2007 and Excel 2010 make it easy to copy and paste the names. Try this:

• Highlight the cells A4, A5, A6 and A7
• Locate the Clipboard panel in the Ribbon at the top of the page (on the Home menu)
• From the Clipboard panel, click Copy

You'll see some moving lines surrounding your highlighted cells - the so-called marching ants!

• Once you see the marching ants, click into cell A15
• To paste the copied text over, simply press the Enter key on your keyboard
• You spreadsheet should now look something like ours below:

We need some new headings on the spreadsheet. Under these headings will be the price of each chocolate bar, how many of a particular chocolate bar we're eating each week, and how much this is costing us each week. So, do the following:

• In cell B14 enter the word Price
• In cell C14 enter the word Number
• In cell D14 enter the word Cost

Your spreadsheet should now look like this:

We're going to put the price of each chocolate bar in cells B15, B16, B17 and B18. We'll have the following for the prices:

Mars Bars £0.35
Twix £0.29
Bounty £0.32
Others £0.40

So go ahead and enter those prices in cells B15, B16, B17 and B18 of your spreadsheet. (You can have your own currency symbol, instead of the UK pound.)

When you're finished, your spreadsheet should look like this one.

For the Number column, we'll use a different form of Paste called Paste Special. 

Paste Special

In the previous section, you created new areas of your spreadsheet that look like this:

We have prices in the B column. Under the Number heading, we're going to put how many of each chocolate bar we ate in one week: how many Mars Bars we ate will go in cell C15, how many Twix will go in cell C16, how many Bounty bars will go in cell C17, and how many other chocolate bars we ate will go in cell C18.

But we already have the weekly totals elsewhere in the spreadsheet, so we don't need to calculate them all over again. We can Copy and Paste the formula over to cells C15, C16, C17 and C18.

Paste Special in Excel 2007 to 2013

We have the weekly totals for each chocolate bar in the J column, under the Individual Totals heading.

• So highlight your four totals in the J column of your spreadsheet
• From the Clipboard panel, click Copy
• You'll see the marching ants again:

• Now, under the Numbers heading, click into cell C15
• Press the enter key on your keyboard to paste the numbers across

What you should notice is that something has gone wrong!

So what happened? Why have all those strange #REF comments appeared in the cells?

If you hold your mouse over the exclamation mark in the yellow diamond, you'll see this:

That complex error message means that Excel tried to paste the formulas over. But the cell references it has are all for the J column.

To solve the problem, we can paste the values over and not the formula.

• Click the left curved arrow at the very top of Excel to Undo (or press CTRL + Z on your keyboard)

• Highlight the four cells in the J column again
• From the Clipboard panel, click copy

• Highlight the cells C15 to C18
• Using your right mouse button, click anywhere in the highlighted area. You'll see the following menu in Excel 2007:

• From the menu, click Paste Special with your left mouse button
• The Paste Special dialogue box will appear:

The item that is selected by default is All, under the Paste option at the top. Select the Values option instead. Then click the OK button.

In Excel 2010 and 2013, however, the right-click menu looks like this:

Select Paste Special to see the submenu above. From the submenu select theValues option, which is circled in red in our image.

What you've just done is to tell Excel to paste only the Values (the numbers) across, and not the formulas we used to get these values.

If you did it correctly, your spreadsheet should look ours below:

Of course, it would have been easy just to type out the values again, since we only have 4. But if you have a lot of values to paste over then the Paste Special dialogue box or menu can save you a lot of time.

Now that we have a price for each chocolate bar, and how many we are eating each week, we can calculate how much our addiction is costing us. For that, we'll need to multiply.

How to Multiply in Excel

In the previous parts of this Excel tutorial, you have been working on a spreadsheet that now looks like this:

To get the weekly cost of each chocolate bar, we need to multiply the Number of bars eaten in one week by the Price. This can then go in the Cost column. The standard way to multiply things is like this:

12 x 10 = 120

The "x" means multiply. In a spreadsheet, however, the letter "x" is not used to multiply things. Spreadsheets use the asterisk symbol instead (the one above the number 8 on your keyboard, in the UK). The previous sum would then look like this:

12 * 10 = 120

Actually, in Excel, you don't need much more than that to multiply. The only other thing you need is an equals sign before the formula. So to get the answer 120, you'd just enter this into any cell:

= 12 * 10

Instead of entering numbers directly, though, we'll enter a cell reference instead. To multiply, then, try this:

• Click into cell D15 on your spreadsheet, just below your Cost heading
• Now click into the Formula Bar at the top of Excel
• Type the following formula:

= B15 * C15

• Hit the enter key on your keyboard, and you should get an answer of 5.95
• Your spreadsheet will look like this (we've formatted the cell as a currency):

So to multiply in Excel , you do this:

= Cell Reference * Cell Reference

You type the equals sign first ( = ), followed by the first cell. Type an asterisk ( * ), and then the second cell. Hit the enter key, and Excel will multiply the two cells for you:

= B15 * C15

Once you have that first formula in place, you can use AutoFill for the others: 

After you're done, you should have the same figures that we have in the Cost column.

Finishing the Spreadsheet

To finish off the Excel spreadsheet you have been working on in this section, we'll add figures for the weekly cost and yearly costs of the chocolate addiction. We'll use AutoFill and SUM.

The bottom of our spreadsheet looks like this:

We now have how much each individual chocolate bar is costing us each week. The next things to do is to add them all up to arrive at a weekly figure for all chocolate bars.

To calculate the weekly cost of the chocolate addiction, you can use the Excel SUM function. But there's an even easier way - use Auto Fill and SUM. Try this.

• Click inside cell F20
• Click inside the Formula bar at the top and enter = SU
• When you see the drop down list of functions, double click SUM
• Now click inside D15 of you spreadsheet
• Excel will enter the Cell for you in the formula bar:

• Notice the marching ants around Cell D15, and that there is a blue border with blue squares
• Hold your mouse over the bottom right blue square until your cursor changes to a double-headed arrow:

• Now hold your left mouse button down and drag down to cell D18
• Let go and Excel will enter the rest of the formula for you:

Press the enter key on your keyboard to finish off the rest of the formula:

If you did that correctly, you should have a figure of 26.18 in cell F20. That's how much our chocolate bar addiction is costing each week.

To work out how much the addiction is costing every year, we can multiply the weekly cost of the addiction by 52 (the number of weeks in a year). First, enter some suitable text in cell A21, something like "Annual Cost of Chocolate addiction". The answer can then go in cell F21, under the weekly cost.

• Click into cell F21 on your spreadsheet
• Then click into the formula bar at the top
• Enter the following:

= F20 * 52

Hit the enter key on your keyboard, and the correct answer should appear.

Cell F20 is where the weekly total is. Excel already knows what formula is inside of this cell, so only the cell reference is needed. After the multiply symbol, we then only need to enter the number of weeks in a year.

The answer you should have in cell F21 is 1, 361.36. You spreadsheet should look like ours below:

The formula we just used mixes a cell reference with a number. Excel doesn't mind you doing it this way, just as long as there's something to multiply. So you can do things this way:

= 26.18 * 52

Or this way:

= F20 * 52

If you have the number 52 typed into say cell H20, you could just do this:

= F20 * H20

Whichever way you choose, though, just remember to use the asterisk to multiply things.

Add a Comment to a Cell

A comment can be added to any cell on your spreadsheet. When you hover your mouse pointer over a cell that contains a comment, you'll see the comment appear in a sort of Sticky-Note. To see how they work, study the spreadsheet below:

The formula in cell B1 above gives you a random number from 1 to 49. A new number can be had by clicking the "Calculate Now" button on the Formula menu.

To let users know what to do, we'll add a comment to cell B1.

First, create the spreadsheet above. In cell B2, enter the following formula:

=RANDBETWEEN(1, 49)

The formula will generate a Random number between 1 and 49. Once you have the above spreadsheet up and running, click inside B1 and try it out:

• From the menu bars on the Ribbon at the top of Excel, click on Formula
• Locate the Calculation panel, and then click on Calculate Now:

Excel 2007 will refresh the calculation and enter a new random number for you. To let people know about this, you can add your comment to the cell. To add a comment to cell B1, do the following:

• Click inside cell B1 on your spreadsheet
• From the tabs on the Ribbon at the top of Excel, click on Review
• Click on New Comment

A greenish textbox will appear to the right of cell B1, as in the image below:

The word "user" in the image above is placed there by Excel. This is the name of the user account that was set up in Windows. Press the backspace on your keyboard to delete this.

To add your comment, just start typing. The size of the comment area can be increased or decreased by moving your mouse over the white circles. Hold down the left mouse button and drag.

When you have finished typing your comment, click on any other cell. The comment will disappear. Notice that the cell now has a red triangle in the top right. This indicates that it contains a comment:

If you move your mouse pointer over cell B1 the comment will appear:

To get rid of a comment, right click the cell that contains the comment. Then, from the menu that appears, select Delete Comment.

If you have been following along with the tutorials in this section, try the Review and see how you get on. It will give you more practice in building a spreadsheet.

Review Two

You've seen a spreadsheet on chocolate addiction. The finished version looked like this:

Time now to reveal your addiction! Create a spreadsheet like the one above, but substitute Chocolate Addiction for something else. Examples might be: smoking, drinking, eating out, clothes, makeup - in fact, anything that someone might be spending too much money on. (It doesn't have to be you doing the spending: it can be entirely made up.)

Your spreadsheet should include the following:

• Daily totals
• Individual totals
• Weekly total
• Columns for Prices
• Columns for Number and Cost
• Weekly cost
• Annual cost

You can format the spreadsheet any way you like. The colour scheme is entirely up to you. Just make sure that your spreadsheet is easy to follow.

In the next section, we'll make start on Charts in Excel. Before that, you'll learn how to sort data.