Basic Excel:

There are 5 important areas in the screen.

1. Quick Access Toolbar: This is a place where all the important tools can be placed. When you start
Excel for the very first time, it has only 3 icons (Save, Undo, Redo). But you can add any feature of
Excel to to Quick Access Toolbar so that you can easily access it from anywhere (hence the name).
2. Ribbon: Ribbon is like an expanded menu. It depicts all the features of Excel in easy to understand
form. Since Excel has 1000s of features, they are grouped in to several ribbons. The most
important ribbons are – Home, Insert, Formulas, Page Layout & Data.
3. Formula Bar: This is where any calculations or formulas you write will appear. You will understand
the relevance of it once you start building formulas.
4. Spreadsheet Grid: This is where all your numbers, data, charts & drawings will go. Each Excel file
can contain several sheets. But the spreadsheet grid shows few rows & columns of active
spreadsheet. To see more rows or columns you can use the scroll bars to the left or at bottom. If
you want to access other sheets, just click on the sheet name (or use the shortcut CTRL+Page Up
or CTRL+Page Down).
5. Status bar: This tells us what is going on with Excel at any time. You can tell if Excel is busy
calculating a formula, creating a pivot report or recording a macro by just looking at the status
bar. The status bar also shows quick summaries of selected cells (count, sum, average, minimum
or maximum values). You can change this by right clicking on it and choosing which summaries to
show.

Typing & Formatting Data in Excel

 Using fonts
 Using colors
 Applying Cell Borders
 Aligning Text, wrapping text
 Applying cell formats – $, 0,00, Dates, Times etc.
Basic Formulas:
o MDAS – Multiplication, Division, Addition, Subtraction */+-
o AutoSum
o Min
o Max
Basic Logical
IF :
=IF(LOGICAL TEST,TRUE,FALSE)
VLOOKUP:
=VLOOKUP(Look up value, Table Array, Column Index Number)

CELL ADDRESS – the intersection of Rows and Columns

Excel Data Input

GCOTC 205 Page 1

 Although it may be considered to be one of the most basic Excel actions, there are actually several
different ways of entering data into a spreadsheet. Many of these are shortcuts that will help you to work
more efficiently. Click on the links below for details.

The most basic way to input data into Excel is to simply select a cell and type directly into it. However, if
you want to go back and alter the contents of that cell (without deleting the existing cell contents), you
need to put the cell into edit mode. To do this, you can either :

 Double click on the cell, or

 Select the cell you want to edit and then either:
 Click in the formula bar
 Press F2
When a cell is in edit mode, a cursor appears (either in the cell or in the formula bar), and when you type
or paste data into the cell, it appears alongside the existing cell contents. Also, when a cell is in edit mode,
you can MOVEyour cursor through the cell contents, using the left, right, up and down keys on your
keyboard.

Entering a Value Into a Range of Cells

If you want to enter the same value into several cells, you can quickly do this by highlighting all the cells
you want to populate, typing in the cell contents and then pressing <CTRL>-Enter (ie. press the Ctrl key,
and while this is depressed, press Enter).

This copies the text you have typed into all of the cells in the selected range.

Importing Data Into Excel

If you have data in a different file format, you can import this into Excel. For example, to import data from
a text file:
 In Excel 2003: Select the Data tab, and from within this menu, select Import External Data and
then Import Data...
or
 In Excel 2007 and later versions of Excel: Select the Data tab and from within this menu, select
the From Text option

Enter a New Line

If you want to insert a new line in an Excel cell, the simplest way is to press:
<ALT> Enter
I.e. press the ALT key and while holding this down, press the Enter key.

Use Formulas to Insert a New Line

You can insert a new line in an Excel cell using the Excel Char function. The new line character is
represented by character number 10. Therefore, a new line can be inserted into a cell using the function:
CHAR( 10 )
An example is shown below:

Formula: Result:
A A
1 ="John Smith" & CHAR( 10 ) & "3 Carlton Road" John Smith
1 3 Carlton Road

Formatting Cells to Display a New Line

GCOTC 205 Page 2

 If you have entered a new line in a cell but this is not displayed,
this may be because the Wrap Text option is not enabled. To
enable the Wrap Text option:

 Right click on your cell and select Format Cells...

 From the Format Cells window, select the Alignment tab
 Tick the Wrap Text option and click OK

The Excel Autofill feature can be used to populate a range of

cells with either a repeat value, or with a series of numerical values
(eg, 1, 2, 3, ...). To use the Excel Autofill :
 Enter a value into the start cell
 Use the mouse to drag the 'fill handle' (the small black square at the
bottom right of the start cell) across the range of cells to be filled
 When you drag the 'fill handle' across the range of cells to be filled,
Excel will fill the selected cells, by either repeating the value in the
first cell or by inserting a sequence from the first cell value (eg. 1, 2,
3, ...)
 Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will
give you the following different options :
 'Copy Cells' - copy the initial cell across the selected range
 'Fill Series' - fill the selected range with a series, starting with the initial cell value
 'Fill Formatting Only' - fill the selected range with the formatting, but not the values of the initial
cell
 'Fill Without Formatting' - fill the selected range with values, but do not copy the formatting from
the initial cell
Note that the Fill series option also works with dates and times, as these are stored as numbers in Excel.

Autofill Using More Than One Starting Cell Value

The easiest way to start off a series which you want to extend using the Auto
Fill feature, is to type the first two values of your series into the first and
second cells of a range. Select both of these cells and again, drag the fill handle
across the range to be filled. Excel will automatically recognize the pattern
from the two initial cells and continue this across the selected range. Using this method, you can get
Excel to fill cells by increments of any number (eg. 2, 4, 6, 8, ...).

Using the Function Inputting Tool

The Excel function inputting tool automatically starts up when you insert a function, using the function
menu. This can be done by either:

 Selecting the Insert Function button

(denoted by the ƒxsymbol) from the
left side of the formula bar (see right);
or
 In Excel 2007 and later versions of Excel: selecting theFormulas menu tab and then selecting the
ƒx button, or selecting a function from the grouped buttons.
 In Excel 2003 and earlier versions of Excel: selecting the menu option Insert→Function...
When you select a function using one of the methods above, Excel automatically pops up a window to
assist you in inputting your selected function. This window tells you what the function does, and what
arguments the function takes.

GCOTC 205 Page 3

 The image on the right shows the inputting
tool for the Excel If function. In this example,
the cursor is currently positioned in the entry
field for the first function
argument, Logical_test, and so the text in the
middle of the window tells you what is
required for the this argument.
As you MOVE the cursor to the other entry fields, the text in the middle of the window will tell you what
should be input for the corresponding arguments.

If, as a part of your argument, you wish to specify a range in the current Excel spreadsheet, or in another
open spreadsheet, this can be done by simply using the mouse to select the required range. As you do
this, a Function Arguments window will pop up, and the addresses of any cells or ranges that you then
select with the mouse are automatically inserted into this window. Once you have selected your required
range these addresses are automatically inserted into the current argument field.

Typing Functions Directly Into a Cell

As you become more used to inputting Excel functions and formulas, you might find it quicker to insert
functions by typing them directly into a cell or into the formula bar. In this case, Excel still provides you
with useful prompts. This is shown in the example below, in which the user has started to type the Excel If
function directly into the formula bar. It can be seen that, once the user has typed in the function name
and the opening bracket, Excel pops up a mini prompt, which shows you the format of the function, and
indicates which arguments should be inserted.

You can also use the mouse to select ranges that you want to form a part of any Excel function or formula.
Whilst inputting into a cell or formula bar, ensure your cursor is in the position where you want to insert
a reference to a cell or range of cells, then simply use the mouse to select the required range. Excel will
then automatically insert a reference to the cell's address, into your function, as shown in the image
below.

The above range selection method can be used to select cells in any Worksheet of any Workbook that is
open in your current Excel browser. This is particularly useful if you are selecting a range in a separate
workbook, as the address needs to contain the workbook name, the worksheet name, and the reference
to the cell range, which can be cumbersome to type, and prone to typing errors.

Simple Excel Copy and Paste

The most simple Excel copy & paste is done by the following steps:
 Select an Excel cell, or range of cells
 Copy the cell(s) by either:
o Selecting 'Copy' from the menu which appears on the right mouse click
o Selecting 'Copy' from the home tab (or the Edit menu in older versions of Excel)
o Using the keyboard shortcut, CTRL-C (i.e. select the CTRL key and while holding this down, press C)
 Click on the location where you want to paste the copied cell(s)
 Paste the copied cell(s) by either:
o Selecting 'Paste' from the menu which appears on the right mouse click
o Selecting 'Paste' from the home tab (or the Edit menu in older versions of Excel)

GCOTC 205 Page 4

 o Using the keyboard shortcut, CTRL-V (i.e. select the CTRL key and while holding this down, press V)

Note that there are a few Excel copy and paste rules that you should be aware of:
 When you copy cells containing formulas, the cell references within the formulas will be altered, unless
they are made absolute by placing the $ symbol before the column or row reference - see the pages
on Absolute and Relative Cell Referencesfor more details of this
 If you copy more than one cell, and then select a paste location that would result in the copied cells 'falling
off' the edge or end of the spreadsheet, Excel will flag up an error and will not paste the copied data. This
will occur if you copy a whole row or column and then attempt to paste this into an internal cell in the
spreadsheet.

Excel Keyboard Shortcuts

Most people know some Excel keyboard shortcuts, and therefore recognise that they can be a great time-
saver and can help you to work much more smoothly and efficiently.
Llsts of the most popular Excel shortcuts. The shortcuts have been organised into categories, to help you
to find those that are of particular interest to you. It is recommended that you try to remember a few at
a time, and make use of these in your day-to-day work, until they become second nature to you.

Copying and Pasting

CTRL-C - Copies the current selected cell or range of cells

CTRL-V - Pastes from the clipboard, into the current selected cell or range of cells

CTRL-D - Fills down :

If a single cell or cells in a single row are selected:
 copies the contents from the row above the selected range, into the
selected range
if cells in more than one row are selected:
 copies the contents from the top row of the selected range, into all other
rows of the selected range

CTRL-R - Fills to the right :

If a single cell or cells in a single column are selected:
 copies the contents from the column to the left of the selected range, into
the selected range
If cells in more than one column are selected:
 copies the contents from the first column of the selected range, into all
other columns of the selected range

CTRL-Z - Undo the last action

Selecting Cells
CTRL - Left Mouse Button - Selects multiple cells
i.e. to select more than one cell (or range), first use the mouse to
select on the first cell (or range), then press the CTRL key and use
the mouse to select further cells or ranges

SHIFT - Left Mouse Button - Selects all cells between (and including) the previous active cell
and the cell that is currently being clicked in
i.e. to select a range, first use the left mouse key to click on a cell
(or row or column) at the start of the range, then press the Shift
key and select the cell at the end of the range

SHIFT ↓ - MOVES the current selected range down a row

GCOTC 205 Page 5

 SHIFT ↑ - MOVES the current selected range up a row

SHIFT → - Moves the current selected range right by one column

SHIFT ← - Moves the current selected range left by one column

CTRL-SHIFT ↓ - Selects all cells below the current selection, up to the edge of the
current data region

CTRL-SHIFT ↑ - Selects all cells above the current selection, up to the edge of the
current data region

CTRL-SHIFT → - Selects all cells to the right of the current selection, up to the
edge of the current data region

CTRL-SHIFT ← - Selects all cells to the left of the current selection, up to the edge
of the current data region

CTRL-Spacebar - Selects the whole of the current column(s)

SHIFT-Spacebar - Selects the whole of the current row(s)

CTRL-a - Selects all cells in the current worksheet

Selecting & Inserting Worksheets

CTRL-PageDown - MOVE to the next worksheet in the workbook

CTRL-PageUp - MOVE to the previous worksheet in the workbook

SHIFT-F11 - Insert a new worksheet into the current workbook

ALT-SHIFT-F1 - Insert a new worksheet into the current workbook

SHIFT-CTRL-PageDown - Select the current worksheet and the next worksheet

(if repeated, causes further sheets to be selected also)

SHIFT-CTRL-PageUp - Select the current worksheet and the previous worksheet

(if repeated, causes further sheets to be selected also)

Inputting Data
CTRL - ; - Inserts the current date into a cell

CTRL - : - Inserts the current time into a cell

F4 - While in edit mode within a cell, cycles through the 4 different combinations of
absolute and relative references :
A1
$A$1
A$1
$A1

Show Formulas In Excel

GCOTC 205 Page 6

The following decribes how to show formulas in Excel, for a whole worksheet, or how to just show the
formula in a single Excel cell.

Show All Formulas in a Spreadsheet

If you want to show the formulas in all of the cells in your
spreadsheet, the easiest way to do this is through the keyboard
combination:
CTRL-`

CTRL-` to show formulas in Excel

I.e. press the control key, and while this is depressed, press the ` key.
Note that the ` key is at the top left of your keyboard, next to the number 1.
To hide the formulas again, simply repeat the same key combination (i.e. press CTRL-`)

Show Formulas in a Single Cell

If you want to show a formula in a single Excel cell, you can do this by simply inserting an apostrophe at
the start of the cell.
I.e. for a cell with the simple formula =TODAY() you would insert the apostrophe as follows:
'=TODAY()
This forces the cell to display the formula, rather than the result.
Excel Formatting

As well as giving your spreadsheet a professional look, the use of Excel formatting can provide essential
information that determines the way a user interprets the data in the spreadsheet.
This is particularly the case with Excel numbers.

As dates, time, percentages and CURRENCY values are all stored as numbers in Excel, it is only the
formatting of these numbers that tells the user of the spreadsheet what they represent.

For example, a cell containing the data value 0.5 could represent any of the following:
 The time 12:00 hrs
 The date and time 01-Jan-1900 12:00
 The currency value $0.50
 The percentage value 50%
 The simple number 0.5
The spreadsheet below shows different ways of formatting Excel cells containing numeric values.
A B C
1 Formatted Value Underlying Value
2 A number formatted as a percentage: 59% 0.59
3 A number formatted as a currency: $54.27 54.27
4 A number formatted as a date: 01 August 2008 39661
5 A number formatted as a time: 12:27 PM 0.51875
6 A number formatted as a fraction: 1 3/4 1.75

How To Change Formatting in Excel

To format data in Excel cells, you first need to select the cells to be formatted. Some of the more popular
formatting options can then be accessed directly from the Home tab of the ribbon (or in older versions of
Excel, from the formatting toolbar).
However, for the full menu of formatting options, you will need to use the Format Cells control box, which
can be accessed by any of the following methods :

GCOTC 205 Page 7

  Right-click on the selected cell or range and
select the Format Cells ... option from the
drop-down menu
or
 Press CTRL-1 (ie. Select the CONTROL key and
while this is depressed, select the "1" (one)
key)
or
 Use the menu at the top of Excel. In recent
versions of Excel (2007 or later), this is the
option Format→Format Cells..., which is found
in the Home tab of the ribbon.

The Format Cells control box is shown on the left.

Make sure that the Number tab at the top of the

control box is selected. Within this tab, you will see a window with the heading Category, which has
several data types listed.

Select the Excel formatting Category that you want to apply to your cell (e.g. Number, Percentage, Date,
etc). For most of the categories, this will cause further options to appear on the right hand side of the
control box, which you can use to tailor your formatting style.

Once you have selected the formatting style that you want, click OK.
Note that the listed formats will only work with numbers, so if you apply a date format to a cell
containing text, the appearance of the cell will remain unchanged.

How to Access the Custom Formatting Menu

The Excel custom formatting menu is found in the Number tab of the Format Cells control box, which can
be accessed by either :

 right-clicking on the selected cell or range and selecting

the Format Cells ... option from the drop-down menu
 Pressing CTRL-1 (ie. Selecting the CONTROL key and while
this is depressed, pressing the "1" (one) key)
 Using the menu at the top of the spreadsheet (i.e. in recent
versions of Excel, select the Home tab, and from this,
select Format→Format Cells..., or in Excel 2003,
select Format→Cells).
From within the Number tab of the Format Cells control box,
select theCustom option from the Category list. A list of pre-
defined formatting styles will then appear to the right of the Format Cells control box (see right). You can
either select and use these pre-defined formats as they are, or you can edit them to define your own
formatting style.

Wrap Text in Excel

There are many situations in which you may want to wrap text in Excel cells, so that you can improve the
appearance of, or easily view long text strings.

Without the wrap text feature, if the contents of an Excel cell are too long to be displayed, one of two
things will happen instead. Either the text string will be displayed over the top of the adjacent cells, or, if

GCOTC 205 Page 8

the adjacent cells contain values, the displayed text will be cut off at the cell boundary. These cases are
shown in cells A1 - A2 of the spreadsheet below, while cell A3 shows the same text string with the wrap
text option enabled.

Switching on the Wrap Text Option

To switch on Wrap Text in an Excel cell:

 Use the mouse to right click on the cell and

select Format Cells...
 From the Format Cells window, select Alignment tab
 Tick the Wrap Text option and click OK

Forcing Line Breaks

You can also cause the Wrap Text option to be switched on, by
forcing a line break in a cell.

To do this:
 Put the cell into write mode (i.e. double click on the cell
or select the cell and then either press F2 or click on the formula bar)
 Place your curser at the point where you want a line break and press ALT-ENTER (ie. press the ALT
key and while holding this down, press the Enter/Return key)

Resizing Cells with Wrapped Text

If you wrap text in a single Excel cell, depending on your

settings, the height of the cell may or may not automatically
adjust, to show the whole of the wrapped text. If the cell
height does not automatically adjust, you can force it to do
this by double clicking on the row labels at the left hand side
of the spreadsheet.

For example, to auto-adjust the height of row 1, double click

on the line between rows 1 and 2 of the spreadsheet, in the
greyed label area at the left of the spreadsheet.

Resizing Merged Cells with Wrapped Text

Note that the auto-sizing of cells does not work with merged cells. If you have wrapped text in a merged
cell, you will need to resize the cell manually, by dragging the bar between the row titles to the desired
height.

Convert Excel To PDF

Many users wish to convert Excel files to PDF ("Portable Document Format"). PDF files present your
spreadsheet to others in a way that preserves the document's formatting and prevents the files from
being easily altered.

GCOTC 205 Page 9

 In order to view a PDF file, you will need the Adobe Reader, which can be downloaded for free from
the Adobe website.

In Excel 2010 or 2013

In Excel 2010 or Excel 2013, you can save your Excel spreadsheet to a PDF file using the 'Save As' menu:

 From the 'File' menu, select Save As option

 From the Save as type drop-down menu, select the option PDF (*.pdf)
 Type a filename into the File name area and click Save

In Excel 2007
If you have Excel 2007 and you want to save your Excel to a PDF
file, you may need to download an add-in (a program that
interacts with Excel) to do this. Microsoft provide a free add-in
for this purpose, which can be downloaded from the Microsoft
Download Center.
Once the add-in is installed, this runs whenever you start up
Excel. You can then save an Excel file as a PDF document as
follows:

 From the main Excel menu, click on the right arrow next to
the Save Asoption
 Select the option PDF or XPS (see the image on the right)
 In the box that pops up, type the PDF filename, choose the file type "PDF", and click on
the Publish button

GCOTC 205 Page 10