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
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.
EXERCISE 2
If you need some more practice, here is another exercise!! Save this as we will use this later.
Complete the spreadsheet using the following criteria:
- Use Autofill to fill in the months across the page
- Expenses are $1500 in July
- Profit is equal to Sales less expenses (=b6-b8)
- Sales are expected to increase by 3% each month
- Expenses will increase by $150 each month
- Format for numbers to be currency
- Create a column at the end for TOTAL
- Use Autosum to get grand totals for the year.
NOW Wasn't that FUN FUN FUN????
