Spreadsheets For Beginners
What is a Spreadsheet? When would I use one?
Spreadsheet programs are a type of computer software that can be used for storing and organizing information. The information stored is called data. Spreadsheets can also be used for working with (known as manipulating) the data, for example, a household budgeting spreadsheet could add up how much you spend on household expenses, such as food, electricity or other items, help you to compare it with your income, and so help you to manage your budget.
On this page, you can try some spreadsheet exercises for beginners. A well-known example of a spreadsheet program is Microsoft Excel. The information and exercises here are designed for use in Excel, but your spreadsheet skills can also be applied to other spreadsheet programs like Google Docs Spreadsheets.
All the images on this page are my own. They are copyright to me, and cannot be used without my written permission and a link this page.
Some Important Facts about Spreadsheets before we begin - Cells and Cell References in Excel
When you first open Microsoft Excel, you will see a blank WORKSHEET. Don't worry about the name, a worksheet is just a grid made of small rectangles. Each of these rectangles is called a CELL. You will enter DATA into the cells.
The cells are arranged in COLUMNS and ROWS.
Columns have an identification letter at the top, starting with A. It is possible to use up to 256 columns in a WORKSHEET.
Rows have an identification number on the left-hand side. It is possible to use up to 65536 rows in a worksheet.
To keep track of the cells, each has a cell reference, e.g. A1, B32, Z5
If you click on a cell, the cell reference is shown in the NAME BOX above column A.
Amazing fact: There are over 16 million cells in a worksheet!
256 x 65536 = 16777216
Don't be put off by this, it just shows the enormous potential available. In a simple worksheet, you will probably use only one or two hundred of these cells.
First, Plan Your Spreadsheet
Spreadsheets Software to download now
Before you begin to enter data into a blank workbook, you have to think about the purpose of the spreadsheet, and the type of information you want to record in it. Spending your time wisely now will save a lot of time later!
So, ask yourself:
What am I trying to achieve? Will a spreadsheet allow me to achieve what I want?
What information do I need to include?
How should I layout the information using rows or columns? What headings will I use to explain the information in the spreadsheet?
Here's an example
Jane runs a clothes shop. Her accountant has told her she should keep better records of stock in the shop. She suggested that Jane should start by making a spreadsheet, listing types of clothing and quantities. Later, when she is more confident with spreadsheets, Jane could also add in sizes and values for the items.
So, where are we:
We have a purpose: stock records, for tax purposes
We have our column headings: Item, Item (reference) number, Quantity, Location (of item). The Item (reference) number will help us distinguish, for example, red t-shirts of two different designs.
How to Enter Data into your Spreadsheet
There are three steps to entering data into a spreadsheet:
1) Click on the cell you want to complete
2) Type your data
3) Then either use your mouse to click on another cell, or press ENTER on your keyboard
Normally, it is best to enter your data in columns, with a title at the top of the column. If there are several sets of data (data series), put them one after the other in columns. Here, in our stock-check spreadsheet, we have entered the column headings: Item, Item Number, Quantity, Location
It is important not to leave blank rows and blank columns in the spreadsheet, as this can affect some of Excel's built-in features like functions and graphs.
If you look at column B, in the image, you will see that the title "Item Number" is partially obscured. This is easy to correct. Click on the cell to the left of column A (the one with the triangle) to select the table. Then move your cursor to the line that separates column A from column B, and double-click. The table will "auto-adjust" the column widths.
Column Width in Excel
The blue colour indicates that the table has been selected. When you click into another cell, it will deselect, and the colour will return to normal.
Using the Mouse
When entering data into a spreadsheet, you can use the mouse to move around the spreadsheet, but this is quite slow. If you have a lot of data to enter, you can use keyboard shortcuts:
The ENTER key will enter the data and move you down to the column to next cell.
The TAB key will enter the data and move you along the row to the next cell.
Moving around the worksheet
Looking at the pictures above, work out how many times would you need to click the ENTER key and the TAB key to move from the words Stock List to the word Location.
How many clicks?
The Active Cell
or current cell
The active cell is sometimes called the current cell. When you enter data, change formatting or delete data, it is the active cell that is affected by the change. You can identify the active cell in two ways: it has a black border or outline surrounding the cell and you can look in the NAME BOX that we mentioned earlier.
Types of Data in Excel
Ok, that's all fine, but what kind of data can we put into a worksheet cell.
In this tutorial, we will look at two data types:
Text data can contain letters, numbers, and special characters, like & and !, so it is used for naming rows and columns of data, and making notes. By default, text data is aligned to the left of the cell, but you can change this. Numbers formatted as "text" cannot be used in calculations, so beware!
Numbers like 1, 23, 10200 can be entered in cells, where they will be right aligned, by default. Excel can also store times, currency amounts and dates as numbers.
Once you get into using spreadsheets, you are also very likely to use FORMULAS. A formula is a mathematical equation such as adding or multiplying numbers. Formulas are usually considered to be number data, although there are exceptions to this.
Editing the Data in a Cell
Updating data or fixing mistakes in Excel
It is quite straightforward to change the data in a cell. Click on the cell, type over the existing data, and press enter.
Making a partial change, such as changing 2012 to 2013, is also straightforward. There are two methods:
1) Click on the cell, so that it becomes the active cell. Then, click on the data in the formula bar, delete the numbers/text that needs to be changed, and press ENTER or click on the green tick (to the left of the data).
2) Double click the cell, edit the data directly within the cell, then click ENTER or click on the green tick.
Changing the data in a cell
Is it possible to change the data in a cell after you've entered it?
You will need a spreadsheet program, or Google Docs, for the next part of this tutorial. If you don't have a spreadsheet program, see the link list below.
You are going to make a simple spreadsheet to record the number of tins of food in your store cupboard.
Enter the following information into these cells:
A2 tomato soup
A3 pea soup
A4 chilli beans
A6 fruit cocktail
A7 mandarine segments
B1 Number of cans
Now for an example of WHY we use a spreadsheet to record this type of information:
Click on the cell B8. Then look along the Home tab, and locate the Editing group. You will see a symbol like a zigzag, followed by the word Autosum.
Click on this Autosum button, and check to see that the cell B8 now contains the following data string =SUM(B2:B7)
Click the tick
The spreadsheet will add up the numbers in the cells B2 through to B7, and put the result into cell B8. The answer should be 12. If you change the number of cans in a row, the total in B8 will also change. Try it!
That’s just the beginning. Get a little practice, and come back for more Excel tutorials when you are ready.
Save Your Work
This is very important!
After a few minutes working on your spreadsheet, you should save your file using the SAVE AS command. This allows you to give the file an appropriate name, for example: Stock List
Remember to save your work regularly after that, either using SAVE or SAVE AS. Using the SAVE AS command allows you to make back-up copies of the file, with slightly different names. These can be useful if you make a mistake and want to go back a few steps, or if your computer crashes.
What is you favorite way of accessing email and internet?
The Excel Bible is a great reference book. It's written in a clear and understandable way.
Very helpful quick reference sheet for beginners and improvers. Can be used easily at the computer.