Welcome to Helptips.org
Adding data and basic formulas to spreadsheets
This document will show you the very basics of Excel. If you have never used Excel before, this is the place to start. I will show you what Sheets, Cells, Rows and Columns are, and how to add basic text or numbers into an Excel spreadsheet. I will also show you how to use the four basic formulas of add, subtract, multiply and division. We will also go through some simple formatting to make the spreadsheet look tidy.
Terminology used in Excel
When you first open Excel you will have a blank spreadsheet.
You will notice at the bottom three tabs labelled Sheet1, Sheet2 and Sheet3. These are 3 pages of the spreadsheet. Unlike Word though you will not move onto another sheet when you reach the bottom of Sheet1, they are treated as separate pages but are all part of the same overall spreadsheet. The idea being you may have data like a person home address on one sheet, then their job etc on another sheet and so on.
Each of the squares you see is a cell. It is each of these cells where we will enter out text or data. Here I have highlighted one cell. It has cell reference of B2 as it crosses the point of column B and Row 2.
Rows are horizontal lines of cells. Rows are numbered from 1 to 65536. Here I have highlighted Row 2.
Columns are vertical lines of cells and are labelled from A to IV. I have highlighted column B below.
Adding Data / Text
Adding text to a cell is just the same as writing in Word. First you need to select the cell you are going to type into. Here I have selected A1.
I am going to create a spreadsheet where I can keep track of my monthly budget. For this I need 3 headings of Payment to, Day of Month and Amount. In cell A1 I will enter Payment to, then in cell B1 Day of Month and Cell C1 Amount. You can move to the next cell by clicking on it, or by using the arrow keys.
As you can see the text in cell A1 and B1 doesn't quite fit so a quick way of making the cell resize to the longest text is to double click on the dividing line between the cell and the one to the right. So to adjust A1 we need to click on the line between A and B and for cell B1 it is the line between B and C.
It will then resize the cell.
Now do the same for B1 so your spreadsheet looks like this.
As these are my headings, I am going to enter some data under them in row 2 downwards. As before you can click on the cell to select it, or use the arrow keys to move around.
As you can see anything you entered as text was automatically aligned to the left, but the numbers were aligned to the right. This is the normal way Excel handles text and numbers.
If you tried to enter the amount as 64.00 for pounds and pence, Excel would as default remove the .00 and just enter 64. This is because Excel sees this as a General number and therefore will not include any 0 at the end or any 0 at the start of the number. So if you entered 064.00 it would only show 64 in the cell.
We need the Amount to be entered as pounds and pence, so we need to change the cells formatting from General to Currency. As we know the entire column is going to be currency we can select the C column and change all the cells in one go. To do this, click on the letter C above Amount.
Now right click anywhere over the highlighted area and a new menu will pop up.
Click Format Cells.
In the new window make sure you are on the Number Tab, where you will see the Category: is showing General selected. We need Currency so click on Currency.
You now have a few options, but the defaults are fine to use so click OK.
As you can now see our Amounts have now changed to currency with the pound symbol and to two decimal places.
We are now going to make a total of our outgoing amount. I am going to show you two ways of doing this. The first is to use the + sign, the second to use the SUM formula. I have added a Total at cell A6 and then selected cell C6 for my formula.
In my first example you can add a number of cells just by using the + sign. All formulas start with =, this left Excel know you are going to use a formula. So press =. Now click on the first cell you want added, in our case its cell C2. Then press + and click on the second cell C3, press + then click on cell C4 so you have the same as below.
Notice how Excel colour codes your cell with the formula. To confirm the formula press the Enter key on your keyboard, or click away from the cell. The spreadsheet will now show the result of your formula, but notice in the formula bar at the top, it shows your formula instead of the result.
I am now going to add some more details to the spreadsheet in the form of a Total Planned and an amount I will put aside each month to cover these costs.
Using the subtraction formula I can take my Planned total from the actual total to find if I have enough money or not.
I have added a new Total and then clicked on cell C8 to enter my formula. As always start with = then click on cell C7 for my total amount of money, then use the â€“ sign and click on cell C6. You formula should look like this.
Press enter to confirm your formula.
We have entered an Amount based on per month, but we can easily see what a yearly total would be by using the multiply formula.
I am going to start a new heading in cell D1 call Yearly Amount.
Starting in cell D2 we will enter our formula. Start with = then click on cell C2. Now click the * button on your keyboard. This is what Excel uses as a multiply symbol. It is normally found above the number 8 key. Now type 12, for the number of month in a year. Your formulas should look like this.
Press enter or click away from the cell to confirm your formula.
To save time writing the same formula for the next 2 rows, we can copy the formula at cell D2 into D3 and D4. The easiest way to do this is click on cell D2, and you will notice a small square in the bottom right of the cell. Move the mouse over this square so it changes to a + sign.
Now hold down the mouse button and drag the box down to cell D4.
Then let go of the mouse button.
I am going to add some new data to the spreadsheet. In this example I have added a new row and entered Road Tax with a yearly amount of £180.00
To find out how much per month this yearly total will be, we can use a formula to divide the yearly amount by 12.
First, select the cell where you want your formula to be. In our example it will be in cell C5.
As always, start your formula with = then click on cell D5 to select the yearly total, then press the / key and type 12. Excel use the / symbol as a divide sign. Your formula should look like this.
Now press Enter, or click away from the cell to confirm your formula. The result should then be shown.
© Copyright 2012. All Rights Reserved.