This tutorial has been written by NCBI IT Trainer Roisin Mulligan.
MICROSOFT EXCEL
What is excel?
Microsoft Excel is part of the Microsoft Office Suite and is used for:
- Data storage and analysis
- Calculation and sorting of data
- Chart generation
Each Excel file has the extension .xlsx and is also called a WORKBOOK.
Each workbook consists of three WORKSHEETS by default. These are named sheet1, sheet2 and sheet3. These can be deleted or extra sheets can be added to a workbook.
To navigate between worksheets without a mouse use the shortcut key CTRL + PG up and PG DOWN.
Each workbook consists of vertical columns and horizontal rows.
Each column is denoted by a letter and each row is denoted by a number.
A cell is the name given to a single box, which is the intersection of a row and column. E.g. A1 S9 or AA23
A range is the name given to a rectangular group of cells. Each range is named from the top left cell to the bottom right cell with a : between, e.g.
A1:a5
B3 : h3
C9: f12
BASIC NAVIGATION WITH SHORTCUT KEYS
| HOME | Moves to the A column in th ecurrent row |
| CTRL + HOME | Moves to the first cell (A1) |
| CTRL + arrow key | Will move to the last cell with content in that direction |
SIMPLE CALCULATIONS
After typing the = key any simple calculation can be carried out using numbers or the contents of cells:
= 231*7
=A1*3
=B2/B3
=c1^2 (the contents of C2 will be squared)
To edit the formula in any cell press F2 key when the cell is active.
SIMPLE FUNCTIONS
After the = sign the function name is given and in () the cell or range to be used is given
=sum(a1:a5)
=average(b2:e2)
=max(d1:d6)
=min(f7:l7)
=count(a1:a4)
The IF function
=if(condition,then,else)
=if(a1>10,”Above ten”,”below ten”)
=if(a2=5,b2*3,b2*9)
OPERATORS
| OPERATOR | MEANS |
| = | Equals |
| < | Less than |
| > | Greater than |
| <= | Les than or equals |
| >= | Greater than or equals |
| Not equal to |
NESTED IF FUNCTIONS
B2 contains a mark. Depending on this mark a Grade will be given as follows…
| >= 85 | A |
| >=70 | B |
| >=55 | C |
| >=40 | D |
| Otherwise | Fail
  |
The nestedif function which will cator for this is
=if(b2>=85,”A”,if(b2>=70,”B”,if(b2>=55,”C”,if(b2>=40,”D”,”Fail))))
CHARTS
TO CREATE A CHART:
- Highlight the columns which are to feature in the chart
- ALT to access ribbon
- Arrow right to INSERT
- Arrow down and then right to CHART
- Press ENTER
- Select chart type
- Click NEXT
- Select data source
- Click NEXT
- Customise additional information such as legend and axis titles
- Click NEXT
- Choose to either
- Insert the chart in the worksheet or
- Insert the chart in a separate chart sheet
- Click FINISH
SORTING DATA
Can sort data…
- A-Z
- Z-A
- 1-N
- N-1
TO SORT A LIST OF DATA:
- Highlight all data to participate
- ALT to access menu bar
- Arrow right to DATA
- Arrow down to Sort
- Choose sort type
- Can sort by x then by Y
- Like SORT BY County THEN BY Town
ADDITIONAL DATA FEATURES
- Sub-totals
- Pivot tables
- Validation
- Filters