Working for People with Sight Loss

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

ADDITIONAL DATA FEATURES

  • Sub-totals
  • Pivot tables
  • Validation
  • Filters