Learning to Create Basic Excel Spreadsheets
If you haven’t used Excel before you may enjoy this short tutorial to get started and learn the basics.
I have used Excel version 2010 for this tutorial, but Excel has not changed so much in it’s various versions and thus these steps are pretty much the same for all versions.Spreadsheets manipulate numbers of any sort such as maths, dates, mileages etc.
The Excel page is divided into small boxes called ‘cells’.
Columns always refer to those from top to bottom and start with a letter.
Rows always refer to those from left to right and start with a number.
Each cell has its own reference or address which is made up of the column number and the row letter.
The box indicated here always shows the reference of the cell currently being used. Click on any cell to see its reference there.
Use the TAB key on the keyboard to jump to the next cell on the right. Use the Return or Enter key to jump to the next Row.
Columns can be made wider by dragging the small divider bar between the Column letters.
Note the symbol displayed for dragging appears as a line with a cross-hair arrow when held in the right place. The Column to the left of your divider is the one that will be made bigger/smaller.
The same also applies to making Rows larger or smaller.
Text headings can be typed into the cells on the first row to enable set up of lists or to identify the columns function.
These cells can be colour shaded to stand out. In fact you can use any of the icons on the Formatting toolbar, shown here to Fill the background of your cells, the Text colour icon to make the text stand out. Also use Bold or Italics etc., where required.
Much of this is the same as in Word. Text size and Style can also be changed if required.
Before applying the formatting effects, you must highlight the cells that you want to be affected.
Unlike Word there is no cursor so you will simply click to select whole cells. Start with the first cell holding your mouse on it and dragging to include the last cell. Then click the format option(s) you want.
The numeric data can then be entered into the cells, adjusting the widths of the columns as necessary. Type date formats in using the / or the – to separate the days and months, and Excel will make them appear as shown here.
Some quick helpful shortcuts are as follows:-
Double click any column divider to make it automatically adjust the column to the minimum width used by the columns contents.
Double click any row divider to make it automatically adjust the row to the minimum height required as above.
The calculation ability in Excel is it’s real power. This is achieved by using Formulas. The formula is always typed into the cell where the answer is required. In this example it would be in F2 and then downwards for more of the same.
A formula is an instruction on how to do the sums required. A sample of a formula is:
This formula will find out 10% of the value of cell E2. Since E2 can change, the value is simply the amount you type there at any time. If you change it, the answer changes too.
You must click out of the cell before the calculation takes place, so changing the value of 50 in E2 to say 60 will only affect the answer in F2 when you click out of E2.
Formulas use many symbols. The most common sum formulas are:-
/ = Divide
* = Multiply
+ = Add
– = Minus
Use them from the number keypad on the right side of the standard PC keyboard.
The % sign is on the top of the keyboard
Also use the : symbol to mean continue so a formula that looks like this =sum(E2:E50)
would continue adding from E2 to E50. This saves you having to use A2+A3+A4+A5 etc.
The = sign just tells Excel that a formula is being used. Then the SUM word is the name of the formula we are using here. Finally the cell references and the math symbols are always included between sets of brackets ( ).
To save you typing endless formulas, there are 2 ways to perform these quickly.
First, to add a column, you can use the AutoSum icon on the top toolbar.
For example, to add up the numbers down column E we first drag the mouse over the cells from E2 (1stone with numbers) down to the cell where you want the total to appear, say E10 then click the AutoSum icon as shown. The Total is immediately placed in E10 for you.
The second way to perform formulas quickly is when the same formula will follow down a column so instead of having to type the same formula down a column we can force Excel to do the work for us.
In this case, set up the formula for the first cell of that column then, hold the mouse over the right lower corner of the cell where a tiny black square shows. The mouse will change to a cross.
Drag this cross down to include all cells involved. This will repeat the formula, incrementing the cell references accordingly.
If you click on any of the cells afterwards you will see their formulas automatically generated in the box at the top of Excel .
If no data exists in any rows, a zero will be displayed as the last line of data will be displayed –since there is nothing to total. To avoid this, you could just wait and drag down as required each time to extend the totals.
In Excel there are some Auto Format tools to make quick colour and design changes to your spreadsheet. Depending on your version of Excel this tool may be accessed from different places.
The grey grid lines in Excel do not print out, so you may need to add ‘printable’ lines to make a grid appear on your paper copy.
First you must highlight the cells you want to have lines around –shown here in light blue.
This may be a block of cells, a single line or even one cell. Once highlighted, click the Borders icon on its list arrow and select the All Borders option as shown in the example on the right.
Your grid lines will now be enhanced with a darker printable border. This is how the spreadsheet will appear when printed.
You can use the Print Preview icon to see a preview of how your page will print.
There are many more options and formulas to learn in Excel of course.