 # Technology Tutorial: An Introduction to Using Microsoft Excel

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

• Sub-totals
• Pivot tables
• Validation
• Filters