Microsoft Excel

From UCanWiki

Microsoft Excel is a program you can use to create "spreadsheets". A spreadsheet is a grid which contains words and numbers. For example, you might want to create a spreadsheet that showed your monthly incomings and outgoings. In this case, Excel could automatically calculate things for you, such as your total monthly outgoings, or the total amount you spend on your phone bill each year.

Contents 1 Spreadsheet basics 1.1 Cells 2 Formulas 2.1 List of formulas 2.1.1 The "SUM" formula 3 Autofill 3.1 Some notes on autofill 4 Changing the appearance of your spreadsheet 4.1 Changing the width of columns 5 Datatypes

Spreadsheet basics

Cells

Each spreadsheet is made up of lots of small rectangles, known as cells. Each cell is identified by its location in the spreadsheet. For example, the cell which is in column C and row 3 is called cell C3:

Each cell can contain one of three things: text, a number, or a formula.

To enter text or a number into a cell, simply click on the cell and then type in the text or number you want to enter, and then hit enter on the keyboard.

Formulas

One of most useful features of Excel is its ability to do calculations for you. For example, if you wanted to add up numbers that you have typed into cells A4, D3 and F5, and have the result displayed in cell G6, you just have to type the appropriate formula into cell G6 and Excel will do the rest.

In this example, you would need to type

=A4+D3+F5

into cell G6. The answer would then be displayed in cell G6, and would change automatically if you changed any of the numbers in cells A4, D3 or F5.

List of formulas

Here are some example formulas. Note that they all start with the equals sign.

=A6+B3

Gives the sum of A6 and B3

=A6-B3

Gives the result of taking B3 away from A6

=A6/B3

Gives the result of dividing A6 by B3

=A6*B3

Gives the result of multiplying A6 by B3

You can make your formulas as long as you like; for example, =A1+B2+C3+D4+E5 would give the sum of all of those cells.

You can also use numbers rather than cell references in your formulas. For example,

=A6*2

Gives the result of multiplying A6 by 2.

The "SUM" formula

When you want to add up a long list of numbers, you could write a formula such as =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15+A16+A17. However, this would be rather boring and time consuming.

Instead, you could use the "SUM" formula. The equivalent SUM formula for the above addition would be:

=SUM(A1:A17)

Similarly, if you wanted to add up all of the cells between D6 and D42, you could use

=SUM(D6:D42)

The SUM formula also works for adding up rows of numbers. For example, to add up all the cells between B2 and P2, you could use

=SUM(B2:P2)

Autofill

Quite often in Excel you'll want to enter a series of data. For example, you might want to create a column which contains the days of the week, or a row which contains the months of the year. Excel's autofill command can do this for you.

To create a column of data which contains months of the year, simply type the first month you want into the cell you want it to appear in, and press enter. Then click once on the cell. A box will appear round the cell, like this:

At the bottom right-hand corner of the box there is a little black square. Move the mouse over this square, so that the mouse pointer turns into a thin black cross. Click and hold the left mouse button, and then drag the mouse in the direction you want Excel to autofill your series. For example, here we've dragged downwards from January:

While your dragging the mouse, Excel will tell you which month (or whatever) it is creating. When you get to the month (or whatever) you want, simply release the mouse. Your spreadsheet will look like this:

Some notes on autofill

Autofill takes a little while to get used to. Sometimes you might have to select more than one cell to get it work properly. For example, if you want it to produce a series of numbers like 1, 2, 3, 4, ..., you will need to enter the 1 and the 2 yourself, highlight both of these cells, and then drag from the cell containing number 2.

You can also use autofill with formulas. Say you were creating a spreadsheet to calculate monthly income and expenditure. You could enter a monthly total for your income for one of the months, and then use the autofill technique described above to fill the monthly totals for the other months.

Changing the appearance of your spreadsheet

Changing the width of columns

To change the width of column A (for example):

1. Move the mouse pointer so that it is between the headers of column A and column B.
2. If you want to manually change the width of column A, click and drag the mouse pointer left and right to adjust the width. Release the mouse when you're done.
3. If you want to automatically adjust the width of column A, just double-click.

Datatypes

You can enter different types of information (or "data") into cells in an Excel spreadsheet: you could enter text or numbers; those numbers might be whole numbers, or fractions, or negative numbers, or a specific currency.

If you tell Excel what type of data you are entering in to a specific cell (or group of cells) then Excel will display that data correctly. For example, it would add a "£" sign to the start of numbers that represent amounts of pounds and pence.

Often Excel will guess how you want a type of data to be presented. Other times you have to tell Excel how to present your data. To do this:

1. Select the cells which contain (or are going to contain) your data
2. Click the "Format" menu, and then select "Cells"
3. A dialog bow will open. In that dialog box, make sure the "Number" tab is selected. If it isn't, click it to select it.
4. Under the Number tab there's a column marked category. Click on the category of data that best matches what you have in your cells.
5. Once you've picked a category, Excel will give you more options to control your data type. For example, if you picked "Currency", Excel will let you pick the type of currency - pounds, euros or dollars, for example - you want.
6. When you're done, click OK.