2015 Certificate IV Applications 1

What is Excel?

Today we will learn about Excel - Microsoft's spreadsheet application. Spreadsheets can be used for heaps of different things, such as a small table of data to complicated budgets and financial reports.

There will be some basic Maths involved - for those who suffer from arithmophobia - please don't stress too much and I will try and keep the calculations simple.

A spreadsheet contains a number of pages or sheets, each made up of a number of Rows and Columns. Each block on the spreadsheet is called a Cell, the name of each is given from the intersection of the Column and Row, so the name of the cell located in Column B row 8 is B8.

Each Cell can contain text, numbers, dates, formulas or functions. By default, text is left justified and numbers are right justified.

The easiest way to move from cell to cell is with your arrow keys.

Let the fun begin!!

Excel Basics

Entering Text

Start Excel and create a Blank Spreadsheet.

We are going to create a simple spreadsheet.

Put your cursor in cell A1 and type in Michele's Chicken Emporium

Put your cursor in cell A3 and type in Breeds

Put your cursor in cell B3 and type in Quantity

Put your cursor in cell A5 and type in Commercial Layers

Put your cursor in cell A6 and type in Sussex - Light and Coronation

Put your cursor in cell A7 and type in Welsummer

Put your cursor in cell A8 and type in Wyandotte - Gold Laced

Put your cursor in cell A9 and type in Orpington - Buff and White

Put your cursor in cell A10 and type in Brahma

Put your cursor in cell A11 and type in Australorp - Black, Blue and Splash

Put your cursor in cell A12 and type in Silkie X

Your spreadsheet should look similar to the following:

Resizing columns

You can resize columns in a number of ways - we will look at the 2 easiest and visual ways.

The first is by placing your cursor between the two columns where the column designators are. To resize Column A, place your cursor on the divider between the letters A and B - your cursor will change shape to a double arrow pointing left and right - and drag the mouse until the column is the correct width.

The second way is to place your mouse in the same position as above, but double-click the left mouse button. This will resize the column to fit the longest text that is in a cell in that column.

Using either of the techniques above, resize column A to a reasonable width.

Entering Numbers

As mentioned above, numbers are automatically right justified.

In the same spreadsheet, enter the following:

B5     7
B6     4
B7     2
B8     2
B9     4
B10   5
B11   12
B12   3

The spreadsheet should now look like this:

Entering Formulas

For our first formula, we will use an inbuilt function. Excel has heaps of these functions that cover all aspects of calculations including statistics and financial analysis. We will use the very simple autoSUM function. The AutoSum feature allows you to automatically add up a list or range of numbers in a spreadsheet. It will allow you to add up columns and/or rows. When you are dealing with more than one cell, it is called a range. A range is referred to by the two diagonal corners separated by a colon.

Put your cursor in B13

Click on the autoSUM function button - this is located on the Home ribbon in the end block titled editing.

Excel will make some assumptions based on your data and assume that you want to add the column up from B5 to B12 (B5:B12). Press ENTER to confirm. Your answer should be 39.

You have now completed your first basic spreadsheet.

Watch the Video on YouTube by clicking here

Or watch it below

Entering Autofill data

Excel has an Autofill feature that will fill ranges of information based on patterns (some that are built in or you can create your own lists).

•    Move to Cell B15
•    Type in January
•    Move to the corner of the cell, so it is on the small square, and you should see a small crosshair appear.
•    Drag the cursor out to column M

There are a range of things that are available through AutoFill. You can even create your own.
•    In B16, type in Jan. Drag out to column M
•    In B17, type in Monday. Drag across to column M
•    In B18, type in Mon. Drag across to column M.
•    In B19, type in the number 1. In C19 type in the number 2. Highlight both cells and drag across to M

Excel will do this with any pattern of numbers, so long as there are enough numbers selected to set the pattern up.

•    In B20, type in the number 12. In C20, type in the number 19. Select both cells and drag across to column M. Numbers will increase by 7.
•    In Cell B21, type in the date 17/3/15. Drag across to column M
•    In Cell B22, type in the date 17/3/15. In cell C222, type in the date 24/3/15. Select both and drag down to row 20 - this will give you dates a week apart.

To see the existing lists or to create your own, Click on the File, and then Options. From the Options, choose Advanced. Scroll down and you will see the Edit Custom Lists button.

To watch the video on Autofill on YouTube you can click here

or watch it below.

Excel Formulas

Formulas are used to do mathematics within your spreadsheet. I know – I can hear the groans already!!

Simple or even complex calculations can be performed with data in your spreadsheet by putting in a formula.

Formulas use the basic maths operators of plus (+), minus (-), divide (/) and multiply (*) as well as brackets.

To indicate to Excel that you are doing a formula, you start with an equals sign (=).

Excel uses the basic maths principle of BODMAS (Brackets, of, division, multiplication, addition, subtraction) to determine the precedence of the operators.
4 + 5 * 6 = 34 The multiplication is done first, 5 * 6, which is 30 then 4 is added.
(4 + 5) * 6 = 54 The brackets are calculated first which is 9, then multiplied by 6.

When you do calculations in Excel, you can type in the formula using numbers or you can refer the formula to cells that contain the numbers. The second option is the best as the formula will recalculate if you change any relevant cell values.

When you enter your formula, you can type in the cell addresses or you can use the mouse/cursor to select them.

In E3 type in 25
In E4 type in 32
In E5 type in 6
In E6 type in =25+32+6 and press ENTER
In E6 type in = then point to E3 and press + then point to E4 and press + and then point to E5 and press ENTER (this will overwrite the formula that is currently there)

Change one of the numbers - what happened???

Yep - Excel is VERY COOL.

Now comes the fun part…..or for some the confusing part. I like to think of it as the fun part because if you can understand the concept, creating large spreadsheets will be oh so eeeeasy.

Absolute versus Relative Addresses

Formulas and Functions created in Excel are by default “relative”. This means that the cell references used in formulas will change if you copy the formula to a different place.

Next to the cells that have numbers, enter the following:

In F3, type in 43
In F4, type in 59
In F5, type in 120

Copy the formula from E6 to F6 - you can copy/paste or you can drag across as the cell we are copying to is adjacent.

The formula that we placed in E6, which was =E3+E4+E5 is not specifically referring to E4, 5 and 6 – the formula is actually saying from where the formula is go up 3 rows in the same column + up 2 rows, plus up 1 row which in this case happen to be E3, E4 and E5. When we move or copy the formula, the same rules apply, up 3 rows from current plus up 2 rows plus up 1 row - in the new position, this becomes =F3+F4+F5.

There are major advantages in the way Excel uses relative addressing in most circumstances, but there are occasions where it will not work. We will look at an example of where it works and where it doesn't.

Start with a new blank spreadsheet and using the techniques that you have learnt so far, create the following spreadsheet:

Make column A larger, Font for heading is 18pt, Sales and Expenses are underline, TOTAL SALES, TOTAL EXPENSES and TOTAL PROFIT are all bolded.

Here's where the fun starts!!

Our first formula will go in cell B8. Move to that cell and use AutoSum to add up the sales items.
Move to B16 and do the same
TOTAL PROFIT is equal to TOTAL SALES minus TOTAL EXPENSES. Move to cell B18. The formula will be =B8-B16

It is expected that our monthly figures will grow 2% each month as we start to build up our business. In cell A20, type in Monthly Growth. In cell B20, type in 2%.

The formula that you need to calculate the new figure for each month is: Previous Month multiply by 1 plus percentage growth. For February Pens Sales, this equates to =B5*(1+B20)
Copy this across all the way to December.

DID YOU GET THE ANSWER YOU WERE EXPECTING???? I don’t think so. The value that has the percentage change is only in one cell. If you look at the formula in each cell, you can clearly see that only the first formula is correct. The others refer to a cell that does not contain a value. This is one of those circumstances that the relative addressing default does not work. In this formula, we really need the first part to be relative (so that it refers to the previous cell) but we want to refer to an ABSOLUTE cell that contains the percentage growth.

Try changing the formula slightly in C5 to =B5*(1+$B$20) - The dollar signs allow us to refer to an ABSOLUTE cell. You can use the F4 key to change a cell reference to absolute.
Copy this across all the way to December.
Have a look at the values for each cell. This formula is now correct. We can actually copy this formula to all of the sales and expenses for every month.
Now copy the formula from B8 across to all of the cells. Do the same with B18. Your spreadsheet is now complete except for some prettying up!!

Although Excel can be quite scary to begin with, once you get the hang of it you can see how easy and quick it is to create fully functional spreadsheets.

Add a total Column after December
Use the Sum formula to add up all the month’s data and copy it down the spreadsheet.
After doing some research, you have decided that 2% is being too conservative and that the actual monthly growth will be closer to 2.5%. Modify the spreadsheet for the new Percentage Growth.

Watch the video on YouTube on how to complete the spreadsheet

Or watch it here:

** hope this video works - had problems uploading to YouTube - wouldn't complete the process **

Prettying it Up - Basic Tools

Making your spreadsheet look a little bit more attractive is very similar to Word. You can use the Font and Alignment tools, but there are also the number tools that can be used to change number formatting such as decimal places and $ signs.

The basic font style formatting is found in the Font section on the Home Ribbon. You will notice that this looks very similar to that found in Word.

You can use these tools to change the font style, size and colour. You can also use the borders tool to display grids (as the spreadsheet grid is not printed). The process is the same as with Word, select the cells that you want to format and then choose what attributes to apply.

Because we are dealing with a Spreadsheet, and not a normal document, there are other formatting tools that can be used. These deal with the actual data – numbers, dates, percentages etc.

There is a range of different ways that data can be formatted. This includes $ signs, %, different date formats and the number of decimal places that you want the spreadsheet to be formatted to. These are in the Number section of the Home Ribbon. The top allows you to choose from a range of format descriptions, or you can use the quick buttons to format quickly to % or $ as well as increase or decrease the number of decimal places. My spreadsheet is about quantity of sales and should not include decimals.

I will make my spreadsheet numbers, but with no decimals.

A couple of more things that I can do with my spreadsheet. The heading, Monthly sales by Product, should be centred across the months, rather than left justified. To do this, highlight the heading plus all the cells across the months and choose, Merge and Centre. You could also add a box around the heading to enhance it.

Wrap Text will allow text to expand down the cell instead of to the adjacent cell. This is useful if you have a lot of text in the cell like a large product description.

There are a few more formatting things that are specific to Excel.

The alignment, as well as the usual left, right and centred, also has the option of top, middle and bottom. This is for when you increase the height of a cell or row of cells. Another really cool formatting tool is the text rotation. Instead of just having text across the cell, you can change the direction to be down or even on an angle.

Format as table, will allow you to apply a variety of styles to your spreadsheet to make it pretty.

If you don’t want to convert your spreadsheet to a table, just use the styles and apply them where appropriate.

To practice some of the techniques, complete the following spreadsheet.

EXERCISE 1

Create the following Spreadsheet:

Enter the formula to calculate the Total Value for each item
Enter the formula to calculate the Total Value for all stationery
Apply formatting to the spreadsheet that will enhance its appearance. Both Price and Total Value should be formatted in currency format with 2 decimal places.
Apply formatting to enhance the appearance of the spreadsheet.