• alignment: left | right | center
• best-fit
• border
• cell edit
• center a label over columns (merge cells)
• chart: create | edit
• column: best-fit | delete | hide | insert
• comma style
• copy and paste: formulas relatively / absolutely | values | format
• decimal places 
• font and font size
• format: bold | italic | underline
• formula: edit | show formulas | show values | update | user-defined | percentage

• functions: average | max | min | sum (autosum) | sumif | today | lookup | vlookup | if | count | countblank | countif
• labels
• numbers
• organise data in suitable layout
• pictogram: creating a pictogram
• print: preview | page break preview | page setup | gridlines | row and column headings | header & footer
• query (filter using complex criteria)
• range: definition | more about ranges
• range names: create | display 
• row: best-fit | delete | insert
• save: autosave
• sheet: copy | insert | preview | print | rename | zoom
• sort data
• workbook: create | open | save

how to:

• select the range
• click the button

note: labels (text) are most often left-aligned

how to:

• select the range
• click the button

note: numbers are always right-aligned

how to:

• type '=AVERAGE('
• select the range with the mouse
• press Enter

or

• type '=AVERAGE(A1:A3)' (if the range is A1:A3)
• press Enter

or

• give the range a name
• type '=AVERAGE(range_name)'
• press Enter

how to:

• on the Tools menu, click Options
• click the Save tab
• choose the number of minutes you want your workbook to be saved automatically

how to:

select the range and cells for the sum(s)

click the button

the sums are put below and to the right of the cells added

how to:

• select the range
• click the button

how to:

• select the range
• click the correct border button:

how to:

• type in the edit window
• press Enter

how to:

• select the range
• press the button

• type the label in the leftmost column
• select the columns with the mouse
• click the button

how to:

• 1. select the data 
  
  - if the data is not contiguous, select some data, keep the ctrl-button down while you select some more, etc: 
  (note that the columns have to be of equal height)
  
  
  - or you may copy the data for the chart to another part of the sheet:
  
  
  
  to keep the data always up-to-date, this is the best way to copy:
  
  
  
  
• 2. click the chart wizard button for four easy steps:
  
  Step 1: Decide chart type
  
  
  
  Step 2: Check the source data
  
  
  
  Hint: If you don't like what you see, click Rows or Columns. 
  (In a few cases you have to press the Series tab to manage the data source).
  
  Step 3: Choose options for titles, axis, gridlines, legend, data labels and data table.
  
  
  
  Step 4: Decide if the chart should be placed on its own sheet
  
  

purpose: to edit a chart you have made

how:

• 1. Use the chart toolbar.
• 2. Click the chart wizard button again
  
• 3. Each part of the chart is an object that you can click on and edit. For instance, here we are editing the title:
  
  
• 4. You can right-click a part of the chart and choose from a pop-up menu. Here we have right-clicked on the left axis:
  
  
  
  
  
  

before:

after:

how to give one column best-fit:

• move the mouse between two column headers till it changes to a black plus with arrows going left and right

• double-click the left mouse button to best-fit column B

how to give all the columns best-fit:

• click the Select All Button:
• move the mouse between two column headers till it changes to a black plus with arrows going left and right
• double-click the left mouse button

how to:

• right-click the column header:

• click Delete in the menu that appears

how to:

• right-click the column header:

• click Hide in the menu that appears

how to:

• right-click the column header to the right of where you want the new column

• click Insert in the menu that appears

how to:

• select the numbers
• click the button

note: never type commas in numbers, use the comma style button instead

click here for more details

example:

• The 'Total' is defined as the result for 1995 minus the result for 1996 plus three times the result for 1997

• Instead of typing similar formulas for B and C, just copy the formula for A. When the cursor changes to a black plus, drag the lower right corner of the box to the right. You get this:

click here for more details

example:

• We would like to find the amount for each month multiplied by the rate in B1:

• To copy the relative formula =B3*B1 will not work, so we change B1 to $B$1. When the cursor changes to a black plus, drag the lower right corner of the box down. You get this:

• 1. to change a cells content from formula to the value the formula gave (needed if the formula's arguments will change and we want to conserve the old value)
• 2. to copy the values of a range and not the formulas that produce the values (often needed when a range is copied to make up a table for a chart)

how to:

• select the range
• click the copy button
• select the range to copy to
• click Edit on the menu and choose Paste Special and Values and OK

note: to change a cells content from formula to value (1.above) simply copy the cell to itself

= countblank(range) counts the number of empty cells in the range

=countif(range, condition) counts the number of cells in the range that makes the condition true

how to:

• select the range
• click repeatedly the Decrease Decimal or Increase Decimal button

note: It is impossible to type 1.00 in a new sheet. You have to type 1 and press the Increase Decimal button twice.

how to:

• select the range
• choose the font and the size

note: If nothing else is said, use Arial 10.

how to:

• on the menu click File and then Page Setup
• click the Header/Footer tab
• click Custom Footer
• type or use the buttons to insert your name, today's date, name of sheet etc. in the three sections

note: A convention is to put your name and tutor group in the left section, the date in the center section and the sheet's name in the right section.

how to:

select the cell with the formula click inside the formula bar edit the formula press Enter

note: When a column is inserted at the border of a range a formula uses, the formula has to be edited to make it correct after the insertion. If the insertion is inside the range, the formula is updated automatically by Excel. (see illustrations on the left)

how to:

• on the menu click Tools and Options
• on the View tab, check Formulas, then click OK
• give the entire sheet column best-fit so the formulas show with no wasted space

note: Before you print you may like to change the orientation to landscape and force everything onto one page using the Fit to option.

note: Excel changes the alignment when formulas are shown. Don't worry! Be happy!

before the insertion at the end of the range:

the formula is now wrong and has to be edited:

how to:

select the cell with the formula click inside the formula bar edit the formula press Enter

note: When a column is inserted at the border of a range a formula uses, the formula has to be edited to make it correct after the insertion. If the insertion is inside the range, the formula is updated automatically by Excel. (see illustrations on the left)

user-defined formulas for perimeter and area of rectangles:

how to:

• type = in the cell where you want the result to appear
• type the formula using cell addresses or range names
• press Enter

or

• type = in the cell where you want the result to appear
• type numbers and operations, but click on the cells you need instead of typing their addresses
• press Enter

where are the gridlines here?

how to:

• to show: Tools + Options + check Gridlines on the View sheet + OK
• to print: Files + Page Setup + check Gridlines on the Sheet tab

how to:

• on the menu click File and then Page Setup
• click the Header/Footer tab
• click Custom Header
• type or use the buttons to insert your name, today's date, name of sheet etc. in the three sections

note: A convention is to put your name and tutor group in the left section, the date in the center section and the sheet's name in the right section.

purpose: To let a value of a cell depend on a statement about a cell or a range of cells.

how to: 

• In a cell type "=IF(statement,value1,value2)"
  If the statement is true, the cell will be filled with value1, 
  if it is false it will be filled with value2.

example:

• If the amount falls below 10, the action should be "Reorder", otherwise "OK".
    (see the illustrations below)

• select the range
• click the button

how to:

• select a cell and type along

note: When you have finished typing, check the spelling, alignment and case of the text (uppercase/lowercase). Row labels are usually left-aligned and

how to:

• type '=MAX('
• select the range with the mouse
• press Enter

or

• type '=MAX(A1:A3)' (if the range is A1:A3)
• press Enter

or

• give the range a name
• type '=MAX(range_name)'
• press Enter

how to:

• type '=MIN('
• select the range with the mouse
• press Enter

or

• type '=MIN(A1:A3)' (if the range is A1:A3)
• press Enter

or

• give the range a name
• type '=MIN(range_name)'
• press Enter

how to:

• select a cell and type along

note: When you have finished typing, check the spelling, alignment and case of the text (uppercase/lowercase).

how to:

• Often a two dimensional table is a good layout (see illustration at the left)

100% of a number equals the number

50% of a number equals half the number

10% of a number is 1/10th of the number

0% of a number is zero.

To find 12% of a number multiply it by 0.12 
=F3*0.12 or =F3*12%

To increase a number by 32% multiply it by 1.32
=F3*1.32 or =F3 + F3*32%

To decrease a number by 20% multiply it by 0.80.
=F3*0.80 or =F3 - F3*20%

Click this button to see a preview of what the printout will look like.

In the lower-right corner you can see how many pages will print. Do not print if it says or another unreasonable high number.

Page break preview can be used to insert and move page breaks for a nice printout.

To control the way your page will print, click File - Page Setup or click the Print Preview button followed by Setup.

	If your work does not fit on one page, try first to change the Orientation to Landscape. If that does not work, try to set the Scaling to Fit to 1 page(s) wide by 1 tall. If the size is 60% or more it can be read comfortably.
Header/Footer options:	Click Custom Header to set the header.
This window opens when you click Custom Header:
Use the buttons for changing the font, setting the page number, setting the total number of pages, setting the date and time, and to set the file name and the sheet name.
Sheet options:	Check Gridlines and Row and column headings. if not, it will look like this:

The list must have column labels.

1. Select the column labels from the list for the columns that contain the values you want to filter, and click Copy 
2. Select the first blank row of the criteria range, and click Paste 
3. In the rows below the criteria labels, type the criteria you want to match. Make sure there is at least one blank row between the criteria values and the list. If you type criteria in the same row they are combined with 'AND', if on separate rows with 'OR' (as in Access).
4. Click a cell in the list.
5. On the Data menu, point to Filter, and then click Advanced Filter.
6. To filter the list by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
7. In the Criteria range box, enter the reference for the criteria range, including the column labels.
   To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse.

how to:

• ranges like (B3:F5) address the cells in the rectangle with B3 in the upper-left
  corner and F5 in the lower-right corner
• ranges may be listed separated by commas
  example: =AVERAGE (A2,A4) gives the average of the cells A2 and A4, 
  while =AVERAGE(A2:A4) gives the average of A2, A3 and A4.

how to:

• select the range
• type the range name in the Name Box

note: Certain range names are illegal.

note: To delete a range name: Insert + Name + Define.

how to:

• click in the upper-left corner of an area with two empty columns
• on the Insert menu chose Name - Paste - Paste list

purpose: to show the cell addresses

with row and column headings	without row and column headings

  how to:

• in Print Preview click Setup and then Sheet
• or click Sheet in Page Setup

how to give one row best-fit:

• move the mouse between two row headers till it changes to a black plus with arrows going left and right

• double-click the left mouse button

how to give all the rows best-fit:

• click the Select All Button:
• move the mouse between two row headers till it changes to a black plus with arrows going left and right
• double-click the left mouse button

how to:

• right-click the row header:

• click Delete in the menu that appears

how to:

• right-click the row header below where you want the new row

• click Insert in the menu that appears

how to:

• on the menu click Edit + Move or Copy sheet... + check Copy + OK
• when the new sheet is made, rename it
  

how to:

• on the menu click Insert + Worksheet

how to:

• click the button
• check that you have a suitable header or footer
• check how many pages you will print
• look at all the pages to see if they are OK
• click Setup to change orientation, header/footer, margins, etc.

• click the button

note: for more control use File + Print

how to:

• right-click on the sheet tab
• type a new name
• press Enter

First, select the data you want to sort. (A common error is to just select one column of the data.)

Secondly, press Data - Sort on the menu:

Thirdly, choose Header row if your data table has a header row, and the columns/fields you want to sort by.

Notice: This sheet will not sort correctly. Can you think why? (Hint: it has to do with the reference to B5)

Notice: Don't use these buttons to sort data in a table. They can only sort data in a single column.

example: =SUM(A2:G45)

example: =SUM(GRADES)

how to:

• type =SUM( in the cell where you want the result to appear
• select the range of numbers with the mouse
• press Enter

note: instead of selecting the range, you may also write the range address or range name

example: =SUMIF(dcode,B7,duration) where dcode and duration are range names

how to:

SUMIF(range,criteria,sum_range)
Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum.

how to:

• type =TODAY() in the cell and press Enter
• to format it: click Format-Cells-Date-Type and choose the format you like. Then click OK.

• select the data
• click the button

VLOOKUP(lookup_value,table_

   array,col_index_num,range_lookup)

Table_array   is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
  
  
• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
  
  
• The values in the first column of table_array can be text, numbers, or logical values.
  
  
• Uppercase and lowercase text are equivalent.

Col_index_num   is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup   is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

LOOKUP(lookup_value, array)

Array   is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

how to:

how to: