Excel

Fundamentals

Microsoft Excel

Contents

Understanding Workbooks ................................................................................................................................ 1

Navigating in a File ............................................................................................................................................ 2

Typing Text or Numbers Into A Worksheet ....................................................................................................... 3

Typing Simple Formulas In A Worksheet .......................................................................................................... 4

Filling A Series ................................................................................................................................................... 5

Inserting And Deleting Worksheets ................................................................................................................... 6

Copying A Worksheet ........................................................................................................................................ 7

Renaming A Worksheet ..................................................................................................................................... 8

Moving or Copying A Sheet To Another Workbook ........................................................................................... 9

Changing Worksheet Tab Colours .................................................................................................................. 10

Grouping Worksheets ...................................................................................................................................... 11

Freezing Rows And Columns .......................................................................................................................... 12

Selecting Ranges .............................................................................................................................................................. 13

Selecting Rows ................................................................................................................................................ 14

Selecting Columns ........................................................................................................................................... 15

Understanding Formatting ................................................................................................................................................. 16

Applying General Formatting ........................................................................................................................... 17

Changing Fonts ............................................................................................................................................... 18

Changing Font Size ......................................................................................................................................... 19

Understanding Borders .................................................................................................................................... 20

Applying A Border To A Range ....................................................................................................................... 21

Wrapping And Merging Text ............................................................................................................................ 22

PRACTICE EXERCISE .......................................................................................................................................... 23

PRACTICE EXERCISE .......................................................................................................................................... 24

PRACTICE EXERCISE .......................................................................................................................................... 25

Understanding Functions .................................................................................................................................................. 26

Using The SUM Function To Add .................................................................................................................... 27

Calculating An Average ................................................................................................................................... 28

Finding A Minimum Value ................................................................................................................................ 29

Common Error Messages ................................................................................................................................ 30

PRACTICE EXERCISE .......................................................................................................................................... 31

Understanding Quick Analysis .......................................................................................................................................... 32

Quick Formatting ............................................................................................................................................. 33

Quick Charting ................................................................................................................................................. 34

Quick Totals ..................................................................................................................................................... 35

Quick Sparklines .............................................................................................................................................. 36

Quick Tables .................................................................................................................................................... 37

Practice Exercise ............................................................................................................................................. 38

Printing A Worksheet ........................................................................................................................................................ 39

St. George’s Information Services

Microsoft Excel

The Charting Process ....................................................................................................................................................... 40

Choosing The Right Chart ............................................................................................................................... 41

Using A Recommended Chart ......................................................................................................................... 42

Creating A New Chart From Scratch ............................................................................................................... 43

Working With An Embedded Chart .................................................................................................................. 44

Resizing A Chart .............................................................................................................................................. 45

Repositioning A Chart ...................................................................................................................................... 46

Printing An Embedded Chart ........................................................................................................................... 47

Creating A Chart Sheet.................................................................................................................................... 48

Changing The Chart Type ............................................................................................................................... 49

Changing The Chart Layout ............................................................................................................................ 50

Changing The Chart Style ............................................................................................................................... 51

Printing A Chart Sheet ..................................................................................................................................... 52

Embedding A Chart Into A Worksheet ............................................................................................................. 53

Deleting A Chart .............................................................................................................................................. 54

PRACTICE EXERCISE .......................................................................................................................................... 55

PRACTICE EXERCISE SAMPLE ............................................................................................................................ 56

St. George’s Information Services

Microsoft Excel

St. George’s Information Services

Microsoft Excel

UNDERSTANDING WORKBOOKS

In Microsoft Excel the data you enter, whether it sheets) that have been ruled into columns and consists of numbers, text, or formulas, is stored rows. Before using Excel it is helpful to know what

in a file known as a workbook. Workbooks are the various parts and elements that make up a

just like huge electronic books with pages (or workbook are.

1

2

4

3

5 6

 A worksheet (or page) in a workbook contains 16,384 columns that are labelled using letters of the alphabet. The first column in a worksheet is labelled column A , while the last is labelled XFD

 A worksheet (or page) in a workbook contains 1,048,576 rows that are labelled using numbers from 1 to 1,048,576

 Where a column and row intersect we get what is known as a cell. You enter your data into these cells. Each cell in a worksheet can hold up to 32,767 characters – although it would be

unrealistic to ever push it this far. Cells are referred to by their column and row labels. For

example, in the screen above the cell we are pointing to is C11 – this reference is known as the

cell address and is most important as it is frequently used in commands and formulas

 When you start typing something, you want it to appear somewhere in the worksheet. As a consequence when the Status Bar shows Ready mode, at least one cell in the worksheet will be

highlighted – this is known as the active cell. In the screen above, the active cell is cell A1 –

notice that the column label and the row label also appears coloured to indicate the active cell.

You can have more than one active cell – when this occurs you have what is known as a range

 A workbook (as you would expect) is made up of pages known as worksheets. You can have as many sheets in a workbook as your computer resources can accommodate. As a default, a new

blank workbook normally has 3 worksheets labelled Sheet1, Sheet2, and Sheet3. Of course

these labels are pretty boring and meaningless and can be changed to something more relevant

 The Insert Worksheet button here will insert another worksheet into the current workbook should you need it

ITTraining@sgul.ac.uk Page 1 St. George’s Information Services

Microsoft Excel

NAVIGATING IN A FILE

Arrow Move one cell to the right, left, up or down

Keys

Tab Move once cell to the right

Ctrl+Home To beginning file

Ctrl+End To end of typed information

Home Beginning of a line

End End of a line

Page Down Down one screen

Page Up Up one screen

F5 To a specific page

Appear at the right and on the bottom of the screen. You may click

Scroll bars

the scroll arrows, drag the scroll box or click the scroll bar to move

through the document.

ITTraining@sgul.ac.uk Page 2 St. George’s Information Services

Microsoft Excel

TYPING TEXT OR NUMBERS INTO A WORKSHEET

Generally when you start a new spreadsheet you wish to enter the data active. This can be done

project, the first task is to enter some headings in a number of ways but the most common is to

into rows and columns. To type anything into a click in it first before typing. worksheet you need to make the cell into which

Try This Yourself:

that there is a blank Before you begin ensure

workbook on the screen…

 Click in cell A3 to make this the active cell, type 1 2

Garden Settings and

press

3

When you press the

next cell down

automatically becomes

the active cell. By the

way, even though the text

looks like it is in cells A3

and B3 it really only is in

cell A3 – since there is

nothing in B3, Excel

allows the spill over to be

displayed giving the 4

illusion it is in 2 cells…

 Type Pool Covers and press

 Repeat the above steps and enter the remaining

text in column A as shown

 Click in cell B2 to make this the active cell, type

UK 5 and press

When you press the

cell to the right becomes

the active cell…

 Enter the remaining text in row 2 as shown

For Your Reference… For Your Reference… Handy to Know… Handy to Know…

To To enter text save a new document : :   You don’t have to use In the exercise above we have named the or to make

1. Click on the adjacent cells active. You can simply use the workbook Garden Department Sales and 1. Click the cell pointer on the desired cell and File Tab and select Save As

type the required information mouse and click in the cells if you want or filed it in C:\Course Files for Excel 2010 .

2. Locate the storage folder in the Navigation even press the arrow keys to move up, Each time you start Excel it will most likely 2. Press pane , an arrow key or to down, left, or right.

assume you want to file your workbooks in a

3. Type a confirm the data entry and to move the cell File name and click on [Save] folder called Documents which is associated

pointer to another cell with the user name you use on the computer.

ITTraining@sgul.ac.uk Page 3 St. George’s Information Services

Microsoft Excel

TYPING SIMPLE FORMULAS IN A WORKSHEET

The whole idea behind Excel is to get it to even other formulas, already in the worksheet

perform calculations. In order for it to do this you using the cell addresses of these numbers rather

need to type formulas in the worksheet. Usually than the actual value in them. Formulas must be

these formulas reference existing numbers, or typed beginning with an equal sign (=).

Try This Yourself: 2

Continue using the previous

file with this exercise...

 Click in cell B8 to make this the active cell

 Type =B3+B4+B5+B6+B7 and examine what is happening on

the screen

3

 Press to enter the formula and move to the next cell

Notice that a calculation has

now been performed. We have

entered a formula in B8 that

says “add the values in B3, B4,

B5, B6, and B7 and show them

here”…

 Ensure that C8 is the active 5 cell, type =SUM(C3:C7) and

press

This is an alternative type of

formula known as a “function”.

Again a calculation will appear

in the cell…

 Click in cell B8 and notice that the formula you typed appears

in the Formula Bar, while the

result of the calculation 6 appears in the worksheet

 Repeat step 5 with cell C8

 Click on the File Tab and select Save to save the

additions that have been made

For Your Reference… Handy to Know…

To enter a formula: Operators

1. Click the cell pointer on the desired cell and + Addition

type the formula commencing with = - Subtraction

2. Press , an arrow key or to * Multiplication

confirm the data entry and to move the cell / Division

pointer to another cell

ITTraining@sgul.ac.uk Page 4 St. George’s Information Services

Microsoft Excel

FILLING A SERIES

A series refers to a sequence of ordered entries reducing the amount of time taken for data entry,

in adjacent cells, such as the days of the week or and ensuring that the spelling is correct. Excel

months of the year. The fill technique can be provides days and months as special built-in series used to create these in a worksheet for you, that you can access.

2

Try This Yourself:

n Before starting this exercise

pe you MUST open the file E707 File O Filling_1.xlsx...

 Click on cell A4

 Move the mouse pointer to the small square (the fill handle )

at the bottom right corner of

the cell until the mouse pointer 3

appears as a thin, black cross

 Drag the mouse pointer to column F

Excel will fill the range with the

first six months of the year…

 Click on cell A5 and repeat steps 2 and 3 to create the

series of months with their full

names

You can also fill more than one 6

row at a time…

 Select the range A6:A12

 Repeat steps 2 and 3 to fill across to column F

 Examine each of the series created by the filling process

For Your Reference… Handy to Know…

To fill a series:  As you drag the fill handle across, a tool tip

1. Click on the first cell in the series appears below the fill pointer displaying the

current value in the series. This is really

2. Drag from the fill handle across as many

handy when you want to end on a particular

columns as required

month, day or value.

ITTraining@sgul.ac.uk Page 5 St. George’s Information Services

Microsoft Excel

INSERTING AND DELETING WORKSHEETS

Once you’ve decided on a structure for your worksheets inserted. However, remember that workbook, you may find that there are some deletion of worksheets is permanent and can’t be

worksheets that can be deleted. Alternatively, undone using Undo, so always save your you may find that you need additional blank workbook before making these changes.

1

Try This Yourself:

n you MUST open the file

Before starting this exercise

pe File O E1324 Worksheet

Techniques_1.xlsx…

 Examine the workbook – it 2 currently contains one

worksheet named Sheet1

 Click on the New Sheet icon at the end of the worksheet

tabs

A new worksheet named

Sheet2 will be inserted. You 3

can also use the keyboard

shortcut...

 Press + to insert another new worksheet

This sheet is named Sheet3

and is inserted before the 4

currently selected sheet.

Now let’s delete a sheet...

 Right-click on the Sheet3 worksheet tab to display the

shortcut menu

 Select Delete to remove the worksheet

As the worksheet contains no

data, the sheet will be

deleted immediately. If a

worksheet contains data, 5

Excel will ask you to confirm

your actions...

 Repeat steps 4 and 5 to delete Sheet2

For Your Reference… Handy to Know…

To insert a new worksheet into a workbook:  To insert a worksheet between existing

 worksheets, right-click on the worksheet tab Click on the New Sheet icon to the right of

the worksheet tabs before which you want to insert a new sheet,

then click on Insert to display the Insert

To delete a worksheet from a workbook:

dialog box. Select Worksheet and click on

 Right click on the worksheet tab, then select [OK].

Delete

ITTraining@sgul.ac.uk Page 6 St. George’s Information Services

Microsoft Excel

COPYING A WORKSHEET

Just as you can copy the contents of cells and have a budget workbook that contains data for

ranges within a worksheet, you can duplicate several departments, you can create a worksheet

worksheets within a workbook. This technique is for the first department and then copy it to create

ideal for replicating layouts. For example, if you identical worksheets for other departments.

1

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E1324 Worksheet

Techniques_1.xlsx...

 Right-click on Sheet1 to display the worksheet shortcut

menu

 Select Move or Copy to display the Move or Copy

dialog box 2

 Click on Create a copy so it appears ticked, then click on

[OK]

The new worksheet is named

Sheet1 (2). Let’s create a

“template” from this worksheet

by deleting unwanted data...

 Select the range B7:E9, then press to clear it

 Repeat step 4 to clear the ranges B14:E23 , G7:J9 and 3 G14:J23 , then press +

to return to cell A1

Now we can copy this

“template” to create additional

worksheets...

 Repeat steps 1 to 3 three times to create three copies of 6 the template worksheet – this

time without data

The final worksheet should be

named Sheet1 (5)

For Your Reference… Handy to Know…

To copy a worksheet:  You can copy the current worksheet using

1. Right-click on the worksheet to copy, then the HOME tab by clicking on Format in the

select Cells group, then clicking on Move or Copy Move or Copy

Sheet.

3. Click on  The Before sheet options in the Move or [OK] 2. Click on Create a copy so it appears ticked

Copy dialog box allow you to position the copied worksheet where you want.

ITTraining@sgul.ac.uk Page 7 St. George’s Information Services

Microsoft Excel

RENAMING A WORKSHEET

By default, Excel names worksheets as Sheet1, makes it much easier to understand the purpose of

Sheet2, Sheet3, etc. These names are fine if you a worksheet. You can also adjust the horizontal

are not planning to share the workbook, but scroll bar to make room for longer, more changing these to something more relevant meaningful worksheet names.

1

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E1324 Worksheet

Techniques_2.xlsx...

 Point to the vertical dots 3 between the sheet names and

the horizontal scroll bar, as

shown

The pointer will change to a

double-headed arrow...

 Click and drag the bar across 4 to the right, to the end of

column L, then release the

mouse button

 Double-click on Sheet1 (5) to select the worksheet tab name

This will also place it into edit

mode… 5

 Type Comms, then press

 Repeat steps 3 and 4 to rename the other worksheets:

Sheet1 (4) Admin

Sheet1 (3) Shop

Sheet1 (2) IT

Sheet1 Maintenance

For Your Reference… Handy to Know…

To rename a worksheet:  You can rename a worksheet by

1. Double click on the current name on the right-clicking on the worksheet tab to display

worksheet tab the shortcut menu and clicking on Rename.

2. Type the new name and press  A worksheet tab name can contain up to 31

characters including spaces, but it is better to

keep it short and succinct.

ITTraining@sgul.ac.uk Page 8 St. George’s Information Services

Microsoft Excel

MOVING OR COPYING A SHEET TO ANOTHER WORKBOOK

You can copy worksheets to other workbooks as can copy their worksheet to another workbook and

required. For example, you might need to keep send them their data only. If worksheets exist in the

records for six different divisions – rather than other workbook, you will need to determine the

send each division the entire set of records, you order in which to place the copied worksheet.

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the file

m E1324 Worksheet

Sa Techniques_6.xlsx...

 1

Click on the Maintenance

worksheet tab

We’ll copy this completed

data to another workbook...

 Right-click on the worksheet tab to display

the shortcut menu, then

click on Move or Copy to

display the Move or Copy

dialog box

 Click on the drop arrow for To book , then select (new

book) 2 4

 Click on Create a copy so it appears ticked

This will create a new

workbook as well as

making a copy of the

worksheet...

 Click on [OK] A new workbook will be 5 created and Maintenance

will be the only worksheet

in the workbook…

 Save the new workbook as Maintenance.xlsx , then

close it

For Your Reference… Handy to Know…

To copy a sheet to another workbook:  To copy a worksheet into an existing

1. Right click on the worksheet tab, then click workbook, make sure that you open the

on destination workbook first to ensure that it is Move or Copy

listed in To book in the Move or Copy

2. Select either (new book) or the name of

dialog box.

another workbook in To book

3. Tick Create a copy, then click on [OK]

ITTraining@sgul.ac.uk Page 9 St. George’s Information Services

Microsoft Excel

CHANGING WORKSHEET TAB COLOURS

To make it easier for you to distinguish between financial years, departments or months. The active worksheets, Excel enables you to change the sheet appears as underlined in a gradient version colours of worksheet tabs. This allows you, for of the selected colour, while inactive tabs will example, to quickly distinguish between different display a solid colour background.

2

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E1324 Worksheet

Techniques_7.xlsx...

 Click on the Admin worksheet tab to select the

worksheet

 Right-click on the worksheet tab to display the shortcut

menu, then point to Tab

colour

3

This will display a palette of

colour options…

 Click on Red under Standard colours to apply

the colour to the tab

 Right-click on the 4 Maintenance worksheet tab

to display the shortcut menu,

click on Tab colour, then

click on Blue under

Standard colours

Notice how the Admin

worksheet tab colour is now 5

a solid rather than a

gradient…

 Repeat either technique to apply the following colours:

Shop Yellow

IT Green 6

 Click on the Admin worksheet tab to view the

results

For Your Reference… Handy to Know…

To change the colour of a worksheet tab:  To apply the same colour to two or more

1. Right-click on the worksheet tab to display sheets at once, select them first. Hold down

the shortcut menu to select consecutive worksheets or

hold down to select non-consecutive

2. Point to Tab colour to display a palette of

colour options worksheets.

3. Click on the desired colour

ITTraining@sgul.ac.uk Page 10 St. George’s Information Services

Microsoft Excel

GROUPING WORKSHEETS

Worksheet grouping enables you to make the example, if you want to format the heading for

same change at once to all selected worksheets. multiple worksheets, you simply group the This feature is useful in situations where your worksheets, make a change to one worksheet and

worksheets have identical layouts or text. For the other worksheets will reflect the change also.

1

Try This Yourself:

Continue using the previous

ile file with this exercise, or

F

e open the file E1324

m Worksheet

Sa Techniques_8.xlsx...

 2 Click on the Admin

worksheet tab, hold down

, then click on the Shop

worksheet tab to select the

first three worksheets

 Click in cell A1 to select the cell

 Click on the HOME tab, then 3 click on Italics in the Font

group

This will italicise the text in

cell A1 on this and all other

worksheets in the group…

 Click on the Maintenance worksheet tab, then the

Shop 4 worksheet tab to see

that the changes have been

applied here

 Click on the IT worksheet tab to see that the changes

have not been applied to

this worksheet

Since this was not part of

5

the grouped sheets the

changes have not been

applied here. Notice too that

clicking on a tab deselects

the previous grouping

For Your Reference… Handy to Know…

To group worksheet tabs:  To deselect a group, either click on the tab of

1. Click on the first worksheet tab a worksheet that is not in the group, or right-

click on a tab and select Ungroup Sheets.

2. Hold down , then click on the last

worksheet tab  Most formatting and text changes done on a

worksheet in a group will be applied to other

sheets in that grouping.

ITTraining@sgul.ac.uk Page 11 St. George’s Information Services

Microsoft Excel

FREEZING ROWS AND COLUMNS

When you lay out your data in rows and columns, scroll across or down to particular cells, the it is most likely that your headings end up at the headings scroll out of view. This problem can be

top or to the left of your data. If you have a large resolved by freezing the rows and/or columns that amount of data, you may find that when you hold the headings.

Try This Yourself:

e Continue using the previous file

m with this exercise, or open the file

Sa File E1324 Worksheet

Techniques_11.xlsx...

 Click on the Maintenance worksheet tab, then spend a few

moments examining the worksheet

Depending on your screen, it is

possible 3 that you won’t be able to

see all of the figures on the screen

at once...

 Click in cell B6 to select the cell

 Click on the VIEW tab, click on Freeze Panes in the Window

group, then select Freeze Panes

Thin black lines appear above and

to the left of the selected cell. This

indicates that the areas above and

to the left are frozen...

 Scroll to the right until Yearly 4 Average in column L appears next

to column A

 Scroll down until Overheads in row 25 is below row 5

 Press + to move to cell B6 – this is our temporary home

cell, as the cells above and to the

left are frozen

 On the VIEW tab, click on Freeze Panes in the Freeze Panes group,

then click on Unfreeze Panes to

unfreeze the rows and columns

5

For Your Reference… Handy to Know…

To freeze panes in a worksheet:  If you want to freeze only the rows above the

1. Click in the cell below and to the right of the selected cell (leaving all columns unfrozen),

area you want to freeze/unfreeze select the cell in column A of that row – e.g.

to freeze rows 1 to 6, click in cell A7. The

2. Click on the VIEW tab

same applies to freezing only columns and

3. Click on Freeze Panes in the Window leaving the rows unfrozen: select the cell in

group, then select Freeze Panes row 1.

ITTraining@sgul.ac.uk Page 12 St. George’s Information Services

Microsoft Excel

SELECTING RANGES

A contiguous range is any group of selected mouse, the keyboard or a combination of the two.

cells that form either a square or a rectangle. A Once selected, you can use the range for input, or

single cell that is selected is also considered to apply formatting, or copy the cells as required.

be a range. Ranges can be selected using the

Try This Yourself: 1

n Before starting this exercise you

pe MUST open the file E705 File O Ranges_1.xlsx...

 Click on cell B7 to select it Because it is the only cell

selected it is the active cell…

 Hold down the key and click in cell E10

Even though a range has been

selected, the active cell is B7 2 – it

appears in a different colour and

its contents appear in the

formula bar. You can keep the

range selected and change the

active cell within the range using

the keyboard…

 Press several times and 3 watch the various cells become

active through the selection

 Click in cell B7, hold down the mouse button, and drag down to

cell C10 before releasing the

mouse

The previous selection has

disappeared and the range B7 4 to C10 is now selected…

 Press and to deselect the selected cells and

return the cell pointer to cell A1

For Your Reference… Handy to Know…

To select ranges with the mouse:  When a range has been selected it can be

1. Click in the left-most cell of the range used as an input range. You can then enter

data into the active cell and move the active

2. Hold down the key and click in the

cell to either the cell below by pressing ,

last cell, Or

or the adjacent cell by pressing .

3. Drag the mouse pointer to the bottom right

corner of the range

ITTraining@sgul.ac.uk Page 13 St. George’s Information Services

Microsoft Excel

SELECTING ROWS

If you want to make changes to an entire row, row header to the left of the row. Remember that

such as bolding all of the headings in a row or any changes you make will apply to every cell in

changing the font of all the cell entries, you must the row all the way across to column XFD, so be

first select the row. This is done by clicking on the careful!

2

Try This Yourself:

e

Continue using the previous file

m with this exercise, or open the file File Sa E705 Ranges_1.xlsx...

 Press + A1 to make cell the active cell

 Move the mouse pointer to the row heading for row 5

3

Notice that the mouse pointer

changes to a black arrow that

points towards the row…

 Click once on row heading 5 to select the entire row

 Click in cell B7and press + 4

This is the key combination for

selecting an entire row…

 Click on the row header for row 7 to select this row

 Hold down 6 and click on the row header for row 10

All rows from 7 to 10 will be

selected…

 Click in the row header for row 5, then hold down the left mouse

button and drag down the row 7 headers to row 10

This is another technique for

selecting rows, but it does require

a steady hand!

For Your Reference… Handy to Know…

To select an entire row:  When every cell in a row or column is

1. Click on the row header of the row that you selected, the corresponding row or column

want to select header is filled in dark blue. When only some

of the cells are selected, the row or column

OR

header is filled in orange. These indicators

1. Click in any cell in the row and press help you locate the active cell(s) on the

+ worksheet.



ITTraining@sgul.ac.uk Page 14 St. George’s Information Services

Microsoft Excel

SELECTING COLUMNS

If you want to make changes to an entire clicking on the column header directly above the

column, such as bolding all of the headings in a column. Remember that any changes you make

column or changing the font of all the cell entries, will apply to every cell in the column all the way

you must first select the column. This is done by down to row 1,048,576!

Try This Yourself: 2

e

Continue using the previous

m file with this exercise, or open File Sa the file E705 Ranges_1.xlsx...

 Press + to make 3 cell A1 the active cell

 Move the mouse pointer to the column heading for column B

Notice that the mouse pointer

changes to a black arrow

pointing down the column…

 Click once to select the column This time the row headers

change to orange to indicate

that at least one cell (but not 4

all) in each row is selected…

 Click in cell D6 and press +

This key combination also

selects an entire co lumn…

 Click on the column header for column B to select it

 Hold down and click on the 6 column header for column D

This time, columns B, C, and D

are all selected…

 Click in the column header for column A , then hold down the

left mouse button and drag the

mouse pointer across the

column headings to column E

For Your Reference… Handy to Know…

To select an entire column:  Make sure that you check your worksheet

1. Click on the column heading of the column carefully after you’ve made changes to entire

that you want to select columns. Remember that all of the cells in

OR that column are affected – even those in

rows below the visible area.

1. Click in any cell in the column and press

+

ITTraining@sgul.ac.uk Page 15 St. George’s Information Services

Microsoft Excel

UNDERSTANDING FORMATTING

In Excel there are always two aspects to a screen it is formatted to appear as a number with a

number: how the number presents on the screen percentage sign, whereas the real value in the cell

(known as formatting) and the underlying value is .02. of the number. Take 2% as an example – on the

Number Formatting – The Veil Placed Over Numbers

All calculations in Excel are performed using numbers – this is only logical. So, when you want to perform

a calculation, you type the numbers in various cells, then create formulas to reference those numbers.

How do you show what those numbers represent? For example, how do you show you are working with

currency, or percentages, or even dates (which in Excel are really numbers)?

Excel allows you to show these representations using number formatting. With number formatting you

change the way a number looks so that it makes immediate sense to the reader of your worksheet. The

underlying value of number, however, remains unchanged. For example, instead of showing sales tax in

a worksheet as .1 you show it as 10%, to show 12889.95 as currency it would appears $12,889.95 or

€12,889.95 (depending upon the currency you are working with), and to show 44104 as a date you show

it as 30-Sep-2020 (remember, dates are actually numbers representing the number of days from January

1, 1900).

The following worksheet contains formatted numbers:

With the formatting removed from the numbers the worksheet looks as follows:

Formatting can also be applied as you type. For example, if you type 30/9/2020 Excel will place the

number 44104 in the cell but will format this number as a date and show it as you typed it. There are also

a range of number formatting options on the ribbon that allow you to apply formatting to numbers after

they have been entered into a worksheet.

ITTraining@sgul.ac.uk Page 16 St. George’s Information Services

Microsoft Excel

APPLYING GENERAL FORMATTING

The Number Format command in the Number formats easily and quickly to a selected cell or

group on the HOME tab contains a drop arrow range of cells in the worksheet. that provides a gallery of the more commonly

used number formats. You can apply these

Try This Yourself:

n Before starting this exercise

pe you MUST open the file E1315 File O Number Formatting_1.xlsx…

 Click in cell D4, hold down , then click in cell D13 to

select the range containing

dates

 Click on the HOME tab, then click on the drop arrow for

Number Format in the

Number group to see a gallery

of number formats

 Click on Long Date to make the short dates in the selected

range appear as long dates

 Click in cell E4, hold down , then click in cell E13 to 2

select the range containing

units of measure

 Click on the drop arrow for Number Format , then select

Number to display these as

numbers with 2 decimal places

 Repeat the above steps to change G4:G13 to Currency

 Repeat the above steps and change the following ranges

as shown:

H4:H14 Percentage

I4:I4 Accounting

G15:I15 Currency

7

For Your Reference… Handy to Know…

To apply general formatting to numbers:  Excel may appear to round values up or

1. Select the range to format down as necessary – however, the value in

2. Click on the the cell does not change. Sometimes you’ll HOME tab, then click on the see minor rounding discrepancies. drop arrow for Number Format in the  Number group The Currency format shows the currency

format and symbol appropriate to the country

3. Click on the desired number format

your computer is configured for.

ITTraining@sgul.ac.uk Page 17 St. George’s Information Services

Microsoft Excel

CHANGING FONTS

The appearance that you choose for your text is In Excel 2007, font just refers to the typeface or referred to as the font or typeface. Font shape of the letters. Typical classic fonts include

traditionally refers to a combination of typeface, Times New Roman , Arial, Century Gothic and style and size in points (e.g. Arial Bold 12 pt). Copperplate.

1

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E722 Font

Formatting_1.xls...

 Click in cell A1 to make the cell with the main heading the

active cell

 Click on the drop arrow next to the Font command in

the Font group on the Home

4

tab to display a gallery of

available fonts

 Point to Arial Narrow, then Book Antiqua , Garamond and Gill

Sans MT

If you don’t have these fonts,

try different ones. As you point

to each font, the preview will

change...

 Scroll to and click on Comics Sans MS , or another font of

your choice if you don’t have

this one

This time the font formatting

has changed in the cell and is

no longer just a preview – it

won’t change again unless you

make another font selection.

For Your Reference… Handy to Know…

To apply font formatting:  You can jump directly to a font. For example,

1. Select the text if you want to preview Garamond, click on the

name of the font in the Font command and

2. Click on the drop arrow for Font

press . Excel will jump to the fonts that

3. Point to a font to preview it start with G and Live Preview will display the 4. Click on the font to apply it text temporarily. Keep typing the name until

you reach the required font.



ITTraining@sgul.ac.uk Page 18 St. George’s Information Services

Microsoft Excel

CHANGING FONT SIZE

One way that text can be emphasised is by be changed for small detailed items, such as

changing the size of the font. For example, if comments or a caption. Main headings in a your normal text is 11 pt, you may like to make worksheet usually appear in a slightly larger font

the headings 13 pt or larger. Font size may also size compared to the rest of the data.

1

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E722 Font

Formatting_2.xlsx...

 Click in cell A1 to make the 2 cell with the main heading the

active cell

 Click on the drop arrow next to the Font Size command

in the Font group on

the Home tab to display a

gallery of available sizes

 Point to various sizes and notice how Live Preview

shows you how the heading

will look

 Click on 16 to change the heading to 16 pt

You can also change the font

size of parts of a document,

and you can use the Mini

8

toolbar...

 Click in cell A2

 Click with the right-mouse button to display the mini-

toolbar and the shortcut menu

 Click on the drop arrow next to Font Size and

click on 14

 Click in cell A3 to hide the toolbar

For Your Reference… Handy to Know…

To change font size:  You may have noticed that the text didn’t

1. Select the cell or range that you want to change size when you used the mini toolbar

2. Click on the drop arrow of size. This is because Live Preview doesn’t Font Size work with the mini toolbar. change until you actually clicked on a different font

3. Click on the required font size

ITTraining@sgul.ac.uk Page 19 St. George’s Information Services

Microsoft Excel

UNDERSTANDING BORDERS

Borders are lines that are placed around the is that the lines can be used to group together data

edges of individual cells or ranges. The lines may or indicate totals, or to draw the user’s attention to

be thin, thick, solid, dashed, black or coloured, or critical cells that may need special data entry. Here

even double lines. The reason for using borders are some examples.

A Worksheet without and with Borders

Borders can be used to apply a structure. Here’s the same worksheet shown without borders and

then with borders applied. The use of borders helps to highlight the totals and separate them from

the other data.

Border Variations

Borders can be applied to all four sides of a cell, or to individual sides of a cell. The following examples show a cell without a border, with an outside border and a top and double bottom border.

ITTraining@sgul.ac.uk Page 20 St. George’s Information Services

Microsoft Excel

APPLYING A BORDER TO A RANGE

You can apply a border to a range of cells. This indicate that they are in separate groups. Borders

allows you to place an outline around them to can be used in ranges of cells to create a more

indicate that the cells are somehow related to form-like appearance. The borders available for

each other, or to place borders between cells to single cells can also be applied to ranges.

1

Try This Yourself:

e Continue using the previous

m file with this exercise, or open

Sa File the file E730 Applying

Borders_2.xlsx...

 Select the range A5:A11

 Click on the drop arrow for 3 Borders and select

Outside Borders

 Click away from the range to see the border

An outline has been placed

around the cells...

 Repeat steps 1 and 2 to apply an outline border to each of

the following ranges in the

order that they are listed:

B5:B11, C5:C11, D5:D11,

E5:E11, F5:F11, G5:G11,

H5:H11, I5:I11, A5:I5,

A11:I11

B13:B19, C13:C19,

D13:D19, E13:E19, F13:F19,

G13:G19, H13:H19, I13:I19,

A13:I13, A19:I19

You can hold down

and select several of these

ranges at once before

applying the border...

 Click away from the last selected range to see the

result

5

For Your Reference… Handy to Know…

To apply a border to a range:  You can copy a border between cells, for

1. Select the range example, from one table to another, using

Paste Special. Select the cells, click on

2. Click on the drop arrow for Borders

Copy , click on the first cell of the

in the Font group on the Home tab

second range and click on the drop arrow for

3. Click on the border option of your choice Paste . Select Paste Special, click on

Formats and then click on [OK].

ITTraining@sgul.ac.uk Page 21 St. George’s Information Services

Microsoft Excel

WRAPPING AND MERGING TEXT

Microsoft Excel will allow long cell entries to spill to place long text entries in a cell you can arrange

across to other adjacent cells to the right as long for Microsoft Excel to wrap the text within the cell

as those cells are empty. If those cells contain and also merge that cell with others to data the spill-over will be chopped off. If you need accommodate the longer text entry.

1

Try This Yourself:

n Before starting this exercise

pe you MUST open the file E723 File O Cell Alignment_9.xlsx...

 Click in cell A5 This cell contains a long text 3

entry that spills across several

columns…

 Click on the Expand Formula Bar tool to the right of the

formula bar to see all of the

text

 Click on the Wrap Text command in the

Alignment group on the

Home tab to wrap the text in

cell A5

Notice how the row height has 5 now increased…

 Hold down the key and click in cell E5 to select the

range A5:E5

 Click on the drop arrow for Merge & Centre in the

Alignment group and select

Merge Cells to merge the cells

in the range

 Move the mouse pointer to the bottom of the row 5 heading

border and drag the row height

up until you reach 30 points

For Your Reference… Handy to Know…

 To wrap text - click in the cell to merge and  In the example above, wrapping forced the

click on the text into one cell and Excel expanded the Wrap Text command in the

row height so that all of the text was

Alignment group on the Home tab

 To merge text - click on the drop arrow across several horizontal cells in the exercise accommodated. We then merged the text

for above so that we could reduce the row Merge & Centre in the Alignment

group and select height to a more acceptable level. Merge Cells



ITTraining@sgul.ac.uk Page 22 St. George’s Information Services

Microsoft Excel

PRACTICE EXERCISE

Font Formatting

Tasks:

Before starting this exercise you MUST have completed all of the topics in

the chapter Font Formatting…

 Open the workbook called PE_Font Formatting.xlsx (it can be found in the same folder as the student files)

 Format the heading in cell A1 as Cambria, 36 pt, bold, Orange Accent 2

 Format the other headings as bold, italic or underline as shown on the following page

 Use Orange, Accent 2, Lighter 80% to fill the area behind the headings as shown on the following page

 1 Add the superscript in cell H3 and in cell B27 with the following comment 1 Fee may be reduced as the result of Government Assistance

Your completed worksheet should appear as shown on the following page...

 Use the Save As command to save the workbook as PE_Font Formatting (Completed).xlsx

ITTraining@sgul.ac.uk Page 23 St. George’s Information Services

Microsoft Excel

PRACTICE EXERCISE

Cell Alignment

Tasks: Completed:

Before starting this exercise you MUST have completed all of the topics in

the chapter Cell Alignment…

 Open the workbook called PE_Cell Alignment1.xlsx (it can be found in  the same folder as the student files)

 Right-align the fees 

 Left align the range B6:B21 

 Centre align cells B23, B25 and B27 

 Use the Save As command to save the workbook as PE_Cell Alignment1  (Completed).xlsx

ITTraining@sgul.ac.uk Page 24 St. George’s Information Services

Microsoft Excel

PRACTICE EXERCISE

Number Formatting

Tasks: Completed:

Before starting this exercise you MUST have completed all of the topics in

the chapter Number Formatting…

 Open the workbook called PE_Number Formatting.xlsx (it can be found  in the same folder as the student files)

 On the Cargo worksheet, apply formatting to the dates and figures so that  they appear as shown in sample A on the next page

This will involve applying a date format, thousands separator, setting the number of decimals and applying the currency format...

 On the Purchases worksheet, apply formatting so that the figures appear  as shown in sample B on the following page

The currency formats should be $, € Euro (€ 123), R English (South Africa) and ETB Amharic (Ethiopia) respectively. You’ll need to widen the columns a little to make room for the characters added by the formatting...

 Use the Save As command to save the workbook as PE_Number  Formatting (Completed).xlsx

ITTraining@sgul.ac.uk Page 25 St. George’s Information Services

Microsoft Excel

UNDERSTANDING FUNCTIONS

Imagine having to create a formula that time consuming to develop. This is the role of

calculated the monthly payments on a loan, or hundreds of arithmetic functions that have been

the average of over 100 cells – these would pre-programmed in Excel for you. require complex or long formulas that would be

Functions Overview

Functions are simply pre-programmed formulas already provided for you in Excel which can perform calculations covering a wide range of categories including statistics, date and time arithmetic, financial calculations, lists, engineering, and more.

Just like normal formulas that you create, functions must start with an equal sign. The equal sign is then followed by the name of the function (usually a descriptive name which indicates the purpose of the function). Most functions also require additional information known as arguments which are supplied to the function in brackets after the function name. Functions are therefore written as follows:

=name(arguments)

The arguments are quite often cell or range references that contain values that can be used in the function. For example, the commonest function is the SUM function which, as its name suggests, is used to sum or add values together. If you wanted to add all of the values in the cells from B10 to D15 you would write this function as:

=SUM(B10:D15)

As you can see this is much simpler than writing your own referential formula which would look like: =B10+B11+B12+B13+B14+B15+D10+D11+D12+D13+D14+D15

Imagine writing and proofing a formula where you had to add 200 cells!

Typing Functions

If you are familiar with the function that you need you can type it into a cell exactly the same way you type any other formula. If you are not sure if Excel has a function or you can’t quite remember how it is written you can use the Insert Function tool on the Formula Bar to assist you. When you click on

this tool the Insert Function dialog box will be presented to you which lists the most recently used or common functions and also allows you to search for other functions that you might need.

The Insert Function dialog box will also type the function out for you and then provide you with a further dialog box to guide you through the process of specifying the arguments that the function needs to perform its calculation.

ITTraining@sgul.ac.uk Page 26 St. George’s Information Services

Microsoft Excel

USING THE SUM FUNCTION TO ADD

One of the most used functions is the SUM type the function, and then use the pointing function. This function allows you to add the technique to fill in the arguments. Excel then paints

values in a range of cells. The function is written marquees around the cells involved helping you to

as: =SUM(range or ranges to add). You can track your progress.

Try This Yourself: 1

n Before starting this exercise

pe you MUST open the file E710 File O Formulas_4.xlsx...

 Click on B9 then type =sum( to start the formula

 Click on B6 to point to this cell as the start, hold down the

key and click on B8

2

Notice the relative addressing

details, 3R x 1C, that appear in

the tool tip…

 Type ) and press to complete the function

 Click on B9, then move the mouse pointer to the fill handle

on the lower right corner of the

cell and drag across to E9 to

fill the selected range with the 7

equivalent functions

 Click on the Copy command Clipboard group on on the

the Home tab

 Click on B14, hold down and then click on cells B19

and B24

 Release and press to paste equivalent functions

into the worksheet

For Your Reference… Handy to Know…

To type a sum function for a contiguous range:  You can also use the Sum command in the

1. Type Editing group on the Home tab of the =sum(

Ribbon to have Excel automatically enter a

2. Select the range of cells

sum function based on a range of cells.

3. Type )  You can also type the name of a function in 4. Press upper or lowercase – it is not case sensitive.

ITTraining@sgul.ac.uk Page 27 St. George’s Information Services

Microsoft Excel

CALCULATING AN AVERAGE

The AVERAGE function allows you to average average function can be applied using the the values in a range of cells. It is written in much Functions Wizard, a part of Excel that steps you the same way as the SUM function, for example, through the process of creating a function or you

=AVERAGE(range of cells to average). The can type it in yourself if you are comfortable with it.

1

Try This Yourself:

e

Continue using the previous file

m with this exercise, or open the File Sa file E710 Formulas_6.xlsx...

 Click on B29 then click on the Insert Function tool to

display the Insert Function

dialog box

 Click on AVERAGE in Select a function then click on [OK] to

display the Function

Arguments dialog box

 Click on the Range Selector tool for Number1 to roll up

the wizard, then hold down

and select the following ranges

B6:B8

B11:B13

B16:B18

B21:B23

 Press to complete the range specifications, then click

on [OK] to complete the process 3

Let’s use the AutoSum

function…

 Click on B34, then click on the drop arrow for the Sum 5

command on the Editing

group, then select Average

 Click on B9, hold down and click on B14 , B19 and B24 , then

press to complete the

formula

For Your Reference… Handy to Know…

To insert an average function:  You can type queries like “How do I work out

1. Click in the cell then click on the the monthly payment for a car loan?” into the Insert

Function Search box in the Insert Function dialog tool

box. Once you have selected a function from

2. Click on AVERAGE in Select a function the Select a function list, the Function 3. Insert the required ranges then click on Arguments dialog box will help you to enter

[OK] the values into the function.



ITTraining@sgul.ac.uk Page 28 St. George’s Information Services

Microsoft Excel

FINDING A MINIMUM VALUE

The Minimum or MIN function allows you to The function can be applied using the Function extract the lowest value from a range of values. It Wizard, or by typing the function in detail directly is written in much the same way as the SUM into the cell. function. For example, =MIN(range of cells).

Try This Yourself:

e Continue using the previous

m file with this exercise, or

Sa File open the file E710

Formulas_8.xlsx...

 Click on B31 then click on the Insert Function tool

to display the Insert

Function dialog box

 Click on the drop arrow for the Or select a category

box and click on Statistical

 Scroll down and click on MIN in Select a function then

click on [OK] to display the

Function Arguments dialog

box 4

 Click on the Range Selector tool to roll up the wizard,

then hold down and

select the following ranges:

B6:B8 B16:B18

B11:B13 B21:B23

 Press to complete the 6 range specifications, then

click on [OK] to complete the

process

Let’s simply type the function

this time…

 Click on B36 and type =MIN(B9,B14,B19,B24)

 Press to complete the 7 formula

For Your Reference… Handy to Know…

To insert a minimum function:  You might use a Minimum function in real

1. Click in the cell then click on the life to find the lowest value in a large range of Insert

Function numbers. For example, in a large inventory it tool

can be used to work out which product is the

2. Click on MIN in Select a function slowest seller. 3. Insert the required ranges then click on

[OK]

ITTraining@sgul.ac.uk Page 29 St. George’s Information Services

Microsoft Excel

COMMON ERROR MESSAGES

Microsoft Excel has some in-built messages that other formula cells that depend upon it. The can assist you when something goes wrong with messages are always prefixed with a hash sign (#)

a formula. These messages appear in the cell and appear with a code. The more common error

that contains the formula, and sometimes also messages are listed below.

A Line of Hash (#) Signs

Sometimes referred to as “tramlines”, a line of hash

signs usually occurs because a column is not wide

enough to display the numbers in the cell or

formula. Widening the column will correct this

problem – you can drag the column heading until the value in the cell appears as it should.

#DIV/0!

This message means you are trying to divide a value by

zero – this is mathematically impossible. In the example at the left we are trying to find the average number of

persons per household. All is fine as long as there is a

value greater than zero in cell B3 (Houses). As soon as

we change this to a zero an error message appears in

the formula cell (B5).

To prevent the error you will need to enter a value

greater than zero into cell B3, the divisor cell.

#VALUE!

In this message Excel is advising that something in the

formula is not a value and therefore a calculation can’t be made.

A close examination of the example at the left shows cell

B3 contains the word “three”. Therefore the formula in

cell B5 is trying to divide 192,664 (in cell B2) with a word,

which doesn’t make sense.

To fix the error, a value (a number) will need to be

entered in cell B3.

#NAME?

This message appears when text is found in a

formula that can’t be matched to either a

legitimate function or range name.

In the example to the left, the formula has been

entered as =SOME(B3:B7) – there is no such function as SOME, and presumably the author should have typed =SUM(B3:B7).

ITTraining@sgul.ac.uk Page 30 St. George’s Information Services

Microsoft Excel

PRACTICE EXERCISE

Formulas And Functions

Tasks: Completed:

Before starting this exercise you MUST have completed all of the topics in

the chapter Formulas And Functions…

 Open the workbook called PE_Formulas And Functions.xlsx (it can be  found in the same folder as the student files)

 Create a formula that calculates the gross pay for each employee, then use  a function to calculate the total of the gross pay

The total for Gross Pay should appear in E14...

 Create a formula that calculates the tax as being 20% of the gross pay for  each employee, then create a total for the tax

  Create a formula to calculate the net pay for each employee and then a total of the net pay

  Create a formula that calculates the superannuation as being 8% of the gross pay for each employee, then create a total for superannuation

  Use functions to determine the average, maximum and minimum values for each column, setting the number of decimal places to 2

Your worksheet should appear as shown on the following page...

 Use the Save As command to save the workbook as PE_Formulas And  Functions (Completed).xlsx

ITTraining@sgul.ac.uk Page 31 St. George’s Information Services

Microsoft Excel

UNDERSTANDING QUICK ANALYSIS

The Quick Analysis tools were developed in Live Preview with some of these tools to create response to the fact that users weren’t using or the Quick Analysis tools. even aware of the more powerful analytical tools

found in Excel. So Excel decided to combine

The Quick Analysis Button

The Quick Analysis button

appears when a range is

selected in a worksheet.

Clicking on the button displays

the Quick Analysis gallery

which contains quick analysis

tools that can be applied to the

selected data.

The tools have been organised

along tabs at the top –

FORMATTING, CHARTS,

TOTALS, TABLES, and

SPARKLINES.

When you click on a tab,

options specific to that tab are

presented.

Using Quick Analysis Tools With Live

Preview

Most of the Quick Analysis tools in the

Quick Analysis gallery provide a Live

Preview of the changes in the worksheet

when you point to an option.

This is very useful if you are not sure of the

formatting or type of analysis you require as it

provides you with a preview of what the data

would look like if you selected that specific

option.

At the right we have selected only the totals

from the worksheet shown above. We have

pointed to options from the TOTALS tab (% Total and Average) and from the

FORMATTING tab (Data Bars).

Live Preview has either presented another

row of analysed data or has formatted the

selection accordingly.

All of these tools are also available on the

ribbon but using the Quick Analysis tools is much quicker.

ITTraining@sgul.ac.uk Page 32 St. George’s Information Services

Microsoft Excel

QUICK FORMATTING

The first tab in the Quick Analysis gallery is colouring it or presenting it in a slightly different

FORMATTING. This tab provides access to the way. In the Quick Analysis gallery you can apply conditional formatting tools of Excel. These are data bars, colour high and low values, values over

the tools that allow you to analyse data by or below a value, and more.

2

Try This Yourself:

n exercise you MUST open

Before starting this

pe File the file E1355 Quick O

Analysis_1.xlsx…

 Click in cell B5, hold down , then click in

cell E9 to select the range

B5:E9

 Point to the bottom of the selected range so that the

Quick Analysis button

appears, as shown, then

click on it to see the

Quick Analysis gallery

3

 On the FORMATTING tab, point to Data Bars to

see data bars

representing the size of

the selected values

 Point to Colour Scale to see colours used to

signify the scale of values

(from red for low to green

for high)

 Point to Top 10% to see the top 10% of values

 Click on Greater Than to see the Greater Than

dialog box

 6

Type 200000 in Format

cells that are GREATER

THAN, then click in cell

A1 to see the changes

For Your Reference… Handy to Know…

To apply Quick Formatting in a worksheet:  Quick Formatting applies conditional

1. Select the range to be formatted, then click formatting, not the standard formatting.

on the Quick Analysis button  The Clear Format option in the Quick

2. Choose the desired formatting from the Analysis gallery will clear any conditional

FORMATTING tab formatting that has been applied.

ITTraining@sgul.ac.uk Page 33 St. George’s Information Services

Microsoft Excel

QUICK CHARTING

Charts aren’t all that difficult to create in Excel, tools provide a way of seeing what the different

especially with the Recommended Charts charts will look like without having to first create the

feature. However, deciding what style and type of chart. chart can be daunting. Fortunately the Charts

3

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the file

m

E1355 Quick

Sa Analysis_2.xlsx...

 Click in cell A3, hold down , then click in

cell E9 to select the

range A3:E9

 Click on the Quick Analysis button, then

click on the CHARTS tab

to see a range of

recommended chart

types for this range

 Point to Clustered Column to see a Live 6

Preview of the chart with

the Week as the legend

 Point to Line, then Stacked Area , then

Stacked Column to see

how these options appear

in Live Preview

 Point to the second Clustered Column to

see a preview of the chart

with the Days as the

legend

 Click on the second Clustered Column to

create a chart in the

worksheet

For Your Reference… Handy to Know…

To use the Quick Charting tools:  When creating a chart you’ll need to ensure

1. Select the range to be charted, then click on that the range you select includes the labels

the to be used on the chart. Quick Analysis button

2. Choose the desired option from the

CHARTS tab

ITTraining@sgul.ac.uk Page 34 St. George’s Information Services

Microsoft Excel

QUICK TOTALS

The TOTALS tab in the Quick Analysis gallery operations (e.g. AVERAGE instead of SUM) or use has some useful tools and options to help you the options to create the totals and calculations in

build your worksheet. You can use the options to the first place. analyse data and perform alternate arithmetic

3

Try This Yourself:

n exercise you MUST

Before starting this

pe File open the file E1355 O

Quick Analysis_3.xlsx…

 Click in cell B5, hold down , then click in

cell E9 to select the

range B5:E9

 Click on the Quick Analysis button, then

click on the TOTALS tab

to see the calculation

options for this range

 Point to Vertical Sum to see a preview of the 6 totals for each column

 Point to Horizontal Sum to see a preview of

the totals for each row

 Point to the other options and study the

results – do not click on

any at this stage

 Click on Vertical Sum to create column totals

 7

Click on the Quick

Analysis button again,

click on the TOTALS

tab, then click on

Horizontal % to see the

percentages for each

day of the week

For Your Reference… Handy to Know…

To create Quick Totals in a worksheet:  Always check any operation that performs

1. Select the range to be totalled/calculated and calculations and embeds formulas for you to

click on the ensure that the correct cells and ranges are Quick Analysis button

included in totals.

2. Choose the desired calculation methodology

from the TOTALS tab

ITTraining@sgul.ac.uk Page 35 St. George’s Information Services

Microsoft Excel

QUICK SPARKLINES

Sparklines are mini charts that are embedded acceptance or understanding that Microsoft would

into a worksheet, usually immediately adjacent to like. So, you’ll now find them in the Quick Analysis the data. Sparklines are only relatively new in tools where you can easily implement them without

Excel and probably haven’t gained the too much head scratching.

Try This Yourself:

n exercise you MUST open

Before starting this

pe File the file E1355 Quick O

Analysis_4.xlsx…

 Click in cell B5, hold down , then click in cell E9

to select the range B5:E9

 Click on the Quick Analysis button, then

click on the

SPARKLINES tab

 Point to Line to display a line drawing showing

trends for each row

across the four weeks 3

 Point to Column to display the trend as

columns rather than a

continuous line

 Click on Column to add Sparklines in column F

Notice that after the

Sparklines have been

created the SPARKLINE

TOOLS tab on the ribbon

is now available so that

you can further enhance

or modify the Sparklines

5

For Your Reference… Handy to Know…

To use Quick Sparklines in a worksheet:  The Win/Loss is a special type of Sparkline

1. Select the range to be analysed, then click that shows positives above an imaginary line

on the and negatives below it. You need to have Quick Analysis button

values range from the negative to the

2. Choose the desired Sparkline from the

positive to make any good use of it.

SPARKLINES tab

ITTraining@sgul.ac.uk Page 36 St. George’s Information Services

Microsoft Excel

QUICK TABLES

In computer terminology a table is created when does have columns and rows of continuous data.

data is organ ised into rows and columns. You’d But it must also have headings which provide filter

think then that a worksheet would be a table – buttons. Creating a table is not hard, but it is much

but it is not in the Excel definition. In Excel a table easier using Quick Tables.

Try This Yourself:

Before starting this

le exercise you MUST

Fi

n open the file E1355

pe Quick

O Analysis_5.xlsx…

 Click in any cell containing data

 Hold down + , then press to select

all of the non-empty

cells around the current 3

cell

 Using the scroll bars, scroll to the bottom

right corner of the

selection, click on the

Quick Analysis button,

then click on the

TABLES tab

 Click on Table to turn the selected range into

a table

 Scroll across and on the drop arrow for

Position to see sorting

and filtering options

6

 Click on Select All to remove the tick, then

click on Effective

People Leader so it

appears ticked

 Click on [OK] to see only those people with

this position title 7

For Your Reference… Handy to Know…

To use Quick Tables to create a table:  A drawback of using Quick Tables is that all

1. Select the entire data to be used as a table of the data must be selected first. Using the

normal operation to create a table (the Table

2. Click on the Quick Analysis button

command on the INSERT tab of the ribbon)

3. Click on the TABLES table, then click on only one cell in the table needs to be

Table selected.

ITTraining@sgul.ac.uk Page 37 St. George’s Information Services

Microsoft Excel

PRACTICE EXERCISE

The Quick Analysis Tools

Tasks: Completed:

Before starting this exercise you MUST have completed all of the topics in

the chapter The Quick Analysis Tools…

 Open the workbook PE_Quick Analysis.xlsx (it can be found in the same  folder as the student files)

 Use the Quick Analysis tools to apply a colour scale to the data in the  worksheet

 Use the Quick Analysis tools to create a chart for the Overheads data.  This chart should be a clustered column chart that has the column

headings as the x axis, and displays the legend at the bottom of the chart.

Make the chart title Cost of Overheads.

  Reposition the chart below the data

 Use the Quick Analysis tools to create Sparklines for the Qtr1 to Qtr4  and Total columns for Overheads

Your worksheet should appear as shown on the following page…

 Use the Save As command to save the workbook as PE_Quick Analysis  (Completed).xlsx

ITTraining@sgul.ac.uk Page 38 St. George’s Information Services

Microsoft Excel

PRINTING A WORKSHEET

Traditionally, printing means producing your over what and how much to print, as well as document on paper, but in today’s Web and enabling you to select the printer to use. You can

online world it might mean printing to the Web or print one or multiple copies of a document, one or

to another file. Excel gives you a lot of control multiple pages and even collate copies.

Try This Yourself: 1

Continue using the previous

file with this exercise...

 Click on File Tab then select Print to display the

Print dialog box

Your dialog box may appear a

little different to the one

shown, as the available

options will depend on the

make and model of printer that

you are using...

 Click on Print to print the pages



For Your Reference… Handy to Know… For Your Reference… Handy to Know…

To close a workbook:  To print a document :  If you save your workbook using the close You can also print a document directly,

command, the workbook will be closed without opening the Print dialog box. Click

1. Click on the File Tab and select Close 1. Click on the File Tab without the prompting message above. the arrow on the Quick Access toolbar, 2. Click on Print  Click Excel allows you to have a number of Quick Print. This will send one copy of

the document directly to the printer. workbooks open at the same time. When you close a workbook when others are still open

one of the others will then appear.



ITTraining@sgul.ac.uk Page 39 St. George’s Information Services

Microsoft Excel

THE CHARTING PROCESS

Charts provide a way of seeing trends in the data any of the Insert commands in the Charts group in your worksheet. The charting feature in Excel on the is extremely flexible and powerful and allows you

to create a wide range of charts from

Inserting Charts

The first step when creating a chart is to select the data from the worksheet that you want to chart. It is important to remember that the selected range (which can be either contiguous or non-contiguous), should include headings (e.g. names of months, countries, departments, etc). These become labels on the chart. Secondly, the selected range should not (normally) include totals as these are inserted automatically when a chart is created.

The second step is to create a chart using the INSERT tab on the ribbon. You can choose a Recommended Chart where Excel analyses the selected data and suggests several possible chart layouts.

Alternatively you can create the chart yourself from scratch by choosing one of the Insert commands in the Charts group. Charts that you create in Excel can be either embedded into a worksheet, or they can exist on their own sheets, known as chart sheets.

Embedded Charts

Charts that appear within a

worksheet are known as

embedded charts. A chart is

really an object that sits on

top of the worksheet – unlike

numbers and letters, charts

are not actually placed into

worksheet cells.

Chart Sheets

If you want to keep your

chart separate from the data

you can move the chart to

its own sheet. Chart sheets

make it easier and more

convenient to work with your

chart because you’ll see

more of it on the screen –

since the data is not there!

ITTraining@sgul.ac.uk Page 40 St. George’s Information Services

Microsoft Excel

CHOOSING THE RIGHT CHART

A chart is far more effective at communicating communicate different types of information. Some

results, outcomes or trends than a table of figures charts show simple relationships between values,

displaying the same information. Different chart while others are designed for quite technical types have been created to purposes. Here is a summary of the use of different

chart types.

Column, Bar These chart types, either in 2D or 3D, are used to

compare values across categories. For example,

they could compare the populations of different

countries.

Line, Area Lines in 2D or 3D are useful for showing trends

such as sales or employment figures. An area

chart is a line chart with the area below the line

filled in.

Surface The surface chart plots trends in two dimensions.

You could use this to plot departmental sales

figures over time. The chart then shows you the

trends between departments, as well as the sales

trends over time.

Pie, Doughnut If you want to show proportion, such as the sales

figures from different departments that make up a

total, then the pie and doughnut charts are for

you. The only variation between the doughnut

chart and the pie chart is that the doughnut chart

can display more than one series of values.

Stock The stock chart type has been designed to show

the stock figures for a day, and the trend over

time. At its simplest, you can plot the high, low

and close figures, and at its most complex, the

volume, open, high, low, and close. It can be

adapted to show the relationships between any

five sets of values.

XY (Scatter) Scatter diagrams are used to display the

relationship between two variables. For example,

you could research the age and price of a series

of cars, and plot the values you find. You could

also investigate the height and weight

relationship of a group of people.

Radar A radar diagram is designed to show the change

in values from a central point. For example, it can

be used to show mobile telephone coverage,

including multiple networks and multiple

measurements.

ITTraining@sgul.ac.uk Page 41 St. George’s Information Services

Microsoft Excel

USING A RECOMMENDED CHART

If you are undecided about the best type of chart selected data and presents you with what it for the data you have selected to graph, then you considers to be the best way to chart that data.

may wish to use Excel’s Recommended Charts Several alternatives are presented and you simply

feature. This feature analyses your choose the one you like most.

Try This Yourself:

n Before starting this exercise

pe you MUST open the file File O E1 317 Charting_1.xlsx…

 Click in cell A3, hold down , then click in cell G7 to 1 You can also use the Quick Analysis tool that appears at the select the range A3:G7 bottom right corner of a selected range to create a quick chart.

 Click on the However, this method will not allow you to preview a wide INSERT tab, variety of charts. then click on

Recommended Charts in

the Charts group

The Insert Chart dialog box

will display with a number

of recommended chart

options…

 Click on each of the alternatives in the left pane

to see a preview of how the

chart will appear in the right

pane and spend a few

moments reading the

descriptions

 Click on Line chart (the second alternative in the

left pane), then click on

[OK] to embed the chart in

the worksheet

 Point to the top border of the chart, then click and

drag the chart immediately

below the data

 Click in cell A1 to deselect 2 the chart

For Your Reference… Handy to Know…

To use the Recommended Charts feature:  When selecting data for a chart you should

1. Select the data to be charted include headings (e.g. names of the month,

regions, etc.) but not the totals derived from

2. Click on the INSERT tab, then click on

the data. In the example above the names of

Recommended Charts in the Charts group

the months and the cities are selected but

3. Click on the desired chart and click on [OK] the total revenue and the regional totals are

not.

ITTraining@sgul.ac.uk Page 42 St. George’s Information Services

Microsoft Excel

CREATING A NEW CHART FROM SCRATCH

The easiest way to create a chart is by using the INSERT tab of the ribbon. This may be faster if you Recommended Chart feature. However, you have a specific style of chart in mind. can create a chart yourself from scratch using

Try This Yourself:

n exercise you MUST open

Before starting this

pe File the file E1317 O

Charting_1.xlsx…

 Click in cell A3, hold down , then click in cell G7

to select the range A3:G7

Note that we have

selected the data

including headings but 2

excluding the totalling…

 Click on the INSERT tab, then click on Insert

Column Chart in the

Charts group to see a

gallery of Column chart

types

 Under 2-D Column, click on Clustered Column

The chart will be

embedded in the

worksheet. The chart will

be active (selected) and

you’ll see additional tabs

on the ribbon for working

with the chart…

 Point to the chart, then click to select it and drag

the chart so that it is

underneath the data, as 4

shown

 Click in cell A1 to deselect the chart

For Your Reference… Handy to Know…

To create a chart from scratch:  When a chart gallery appears after you’ve

1. Select the range to chart used the Insert chart command, you can

point over each image in the gallery to see a

2. Click on the INSERT tab, then click on the

Live Preview of the chart in the worksheet.

appropriate Insert command in the Charts

This will help you to select the right chart for

group

your needs.

3. Click on the desired chart type

ITTraining@sgul.ac.uk Page 43 St. George’s Information Services

Microsoft Excel

WORKING WITH AN EMBEDDED CHART

By default, new charts are placed in the active want to work with a chart you must select it – this worksheet, which is usually the one that contains can be done by clicking on the chart. The chart

the data. Charts are placed over the top of the itself is made up of many objects and these too can

worksheet, embedded as objects. When you be selected by clicking on them.

Try This Yourself:

e Continue using the

m previous file with this

Sa File exercise, or open the file

E1317 Charting_2.xlsx...

 Point to the border of the chart and click once to

select the chart as an

object

The border of the chart will

thicken to indicate that the

chart is selected, the

range of data used for the

chart will be coloured, the

ribbon will show

chart-specific tabs and

commands, and additional 2

tools will appear to the

right of the chart…

 Click on the chart legend to make it the active object

in the chart

 Click on the vertical axis to make it the active object

 Click on the horizontal axis to make it the active

object

 Click on the border of the chart to make the overall

chart the active object

again – notice that the

range of data has been

coloured again

 Click in cell A1 to deselect the chart

3

For Your Reference… Handy to Know…

To select a chart and its objects:  Once an object is selected, be it a chart, a

1. Click on the border of the chart to select an legend on the chart, or the like, you can right-

embedded chart click on the object to see a shortcut menu

specific to the selected object.

2. Click on the various objects of a chart to

select them

ITTraining@sgul.ac.uk Page 44 St. George’s Information Services

Microsoft Excel

RESIZING A CHART

There are two main ways to resize a chart if you These handles appear with dots in them. You can

are not satisfied with its current size. A chart that also resize a chart using commands in the Size has been selected can be resized by dragging group on the CHART TOOLS: FORMAT tab that one of the sizing handles around its border. appears when the chart is selected.

3

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m file E1317

Sa Charting_3.xlsx...

 Click on the chart to select it

 Point to the sizing handle on the left

border of the chart until

the mouse pointer

changes to a double

arrow

 Hold down the left mouse button and drag

left until the chart

appears as shown

You can also resize a 4

chart from the ribbon…

 Click on the CHART TOOLS: FORMAT tab

 Click on the up spinner arrow for Shape

Height in the Size

group until it shows 8.5

cm

 Click on the up spinner arrow for Shape Width

in the Size group until it

shows 17 cm

 Click in cell A1 to deselect the chart

For Your Reference… Handy to Know…

To resize a chart:  If you wish to change the size of a chart

1. Select the chart, then click on and drag a quickly and easily, clicking on and dragging

sizing handle on the border of the chart, or the resize handles is the best option whereas

Click on the if you want to resize a chart to a specific size CHART TOOLS: FORMAT tab,

then click on up/down spinner arrows for it is best to resize the chart using the tools in

Shape the Size group on the CHART TOOLS: Height and Shape Width in the Size group FORMAT tab.

ITTraining@sgul.ac.uk Page 45 St. George’s Information Services

Microsoft Excel

REPOSITIONING A CHART

It’s unlikely that a chart embedded in the on and dragging the border of the chart to the

worksheet by Excel will be exactly where you desired location. The chart obviously must be

would like it to be. You can easily relocate a chart selected before it can be dragged to a new to a more appropriate position by clicking position.

2

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m

file E1317

Sa Charting_4.xlsx...

 Click on the chart to select it

 Point to the border of the chart until the

mouse pointer

changes to a four-

headed arrow

 Hold down the left mouse button and

drag the chart below

the data so that the

Total Revenue row 3

in the worksheet is

visible

 Click in cell A1 to deselect the chart

For Your Reference… Handy to Know…

To move a chart:  You can use the standard cut and paste

1. Click on the chart to select it commands to move a chart. Select the chart,

click on the HOME tab, then click on Cut in

2. Move the mouse pointer to the border of the

the Clipboard group to copy it to the

chart until the mouse pointer changes to a

clipboard. Click in a new location and, on the

four-headed arrow

HOME tab, click on Paste in the Clipboard

3. Drag the chart to a new location group to paste the chart.

ITTraining@sgul.ac.uk Page 46 St. George’s Information Services

Microsoft Excel

PRINTING AN EMBEDDED CHART

When you print a worksheet, Excel will print underlying data. All you need to do is to position

whatever is in or embedded in that worksheet the chart in the appropriate location then access

(including charts). This makes it easy and the print commands in the usual way. convenient to print both the chart and its

1

Try This Yourself:

n exercise you MUST

Before starting this

pe File open the file E1317 O

Charting_5.xlsx…

 Click on the FILE tab, then click on

Print to see a

preview of the data

and the chart

Not all of the chart or

data may be visible

so we’ll change the

orientation to

landscape…

 Click on Portrait Orientation in

Settings then select

2

Landscape

Orientation

 Click on [Print] to print the chart

If you don’t have a

printer connected or

you don’t wish to

print, click on the

Back arrow to

display the workbook

again

For Your Reference… Handy to Know…

To print an embedded chart:  If you only want to print the chart and not the

1. Click on the data, click on the chart to select it, click on FILE tab, then click on Print

the FILE tab, then click on Print. You will

2. Click on [Print]

notice that only the chart will appear in the

preview.

ITTraining@sgul.ac.uk Page 47 St. George’s Information Services

Microsoft Excel

CREATING A CHART SHEET

Charts can either be stored in a worksheet or in a are interested in printing the chart on its own page.

separate sheet of their own known as a chart Charts can be shifted back and forth between a

sheet. Chart sheets separate the chart from the worksheet and a chart sheet. underlying data and are useful especially if you

2

Try This Yourself:

e Continue using the

m previous file with this

Sa File exercise, or open the file

E1317 Charting_6.xlsx...

 Click on the chart to select it and display the

CHART TOOLS:DESIGN

and CHART TOOLS: 3

FORMAT tabs

 Click on the CHART TOOLS: DESIGN tab,

then click on Move Chart

in the Location group to

display the Move Chart

dialog box

 Click on New Sheet, then type Revenue Chart

This will become the

sheet name for the

chart…

 Click on [OK] to move the embedded chart to its

own sheet

 Click on the Chart Data worksheet tab to see the

data again

Notice that the chart is no

longer embedded on this

worksheet

4

For Your Reference… Handy to Know…

To create a chart sheet:  Keeping charts on their own sheets makes

1. Click on the them easier to work with as they do not CHART TOOLS: DESIGN tab,

then click on obstruct the data. Move Chart in the Location

group

2. Click on New Sheet, type a name for the

sheet and click on [OK]

ITTraining@sgul.ac.uk Page 48 St. George’s Information Services

Microsoft Excel

CHANGING THE CHART TYPE

When you create a chart, you may not always of what each chart type is designed for and to

achieve the result that you desire. Fortunately, select the format that best suits your purpose. Just

the process for changing a chart type is quite be aware that some chart types are designed for

simple. You just need to have an understanding specialised applications.

3

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m file E1317

Sa Charting_7.xlsx...

 Click on the Revenue Chart worksheet tab to

see the chart, then click

anywhere on the chart

to select it and display

the chart commands on

the ribbon

 Click on the CHART TOOLS: DESIGN tab,

then click on Change

Chart Type in the Type

group to display the

Change Chart Type

dialog box

 Click on 3-D Column, as shown

 Click on [OK] to apply the change to the chart

 Click on the Chart Data worksheet tab to return

to the worksheet

4

For Your Reference… Handy to Know…

To change the chart type:  You can use Change Chart Type in the

1. Ensure the chart or chart sheet is selected Type group on the CHART TOOLS:

DESIGN tab for either embedded charts or

2. Click on the CHART TOOLS: DESIGN tab,

charts that have their own worksheet tabs.

then click on Change Chart Type in the Type group

3. Click on the desired chart and click on [OK]

ITTraining@sgul.ac.uk Page 49 St. George’s Information Services

Microsoft Excel

CHANGING THE CHART LAYOUT

Excel has a gallery of chart layouts that can be elements of the chart are placed within the chart.

applied to an existing and selected chart that is Different layout options can therefore change the

either in its own worksheet or embedded into the appearance of your chart and its readability.

data worksheet. Chart layouts are the way

Try This Yourself:

e Continue using the

m previous file with this

Sa File exercise, or open the file

E1317 Charting_8.xlsx...

 Click on the Revenue Chart worksheet tab to

see the chart, then click

anywhere on the chart to

select it and see the

2

CHART TOOLS:

DESIGN and CHART

TOOLS: FORMAT tabs

 Click on the CHART TOOLS: DESIGN tab,

then click on Quick

Layout in the Chart

Layouts group to display

a gallery of layout

options

 Click on Layout 3 to apply this chart layout to

the chart

 Repeat steps 2 and 3 to select other chart

layouts and see how

they appear when

applied to the chart

 5

Click on Quick Layout in

the Chart Layouts group

and click on Layout 5

 Click on the Chart Data worksheet tab to display

this worksheet

For Your Reference… Handy to Know…

To change the chart layout:  Chart layouts are predefined themes

1. Ensure the chart or chart sheet is selected created by Microsoft. Even if you choose one

of these layouts you can still make your own

2. Click on the CHART TOOLS: DESIGN tab,

modifications to the way the elements and

then click on Quick Layout in the Chart

objects are positioned and how they appear.

Layouts group

3. Select the desired layout

ITTraining@sgul.ac.uk Page 50 St. George’s Information Services

Microsoft Excel

CHANGING THE CHART STYLE

The style of a chart refers to its colour scheme and effort. Excel also makes it easy to change

and overall appearance and can impact the chart styles if you decide the style you have chosen

clarity of the content of the chart. Choosing a is not appropriate. predefined chart style can save valuable time

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m file E1317

Sa Charting_9.xlsx...

 Click on the Revenue Chart worksheet tab to

see the chart, then click

anywhere on the chart

to select it

 Click on the 2 Chart Styles tool to the right

of the chart to see a

gallery of style options,

as shown

 Scroll through the gallery and point to

each style to see how

your chart will look in

Live Preview

 Scroll to and click on Style 9

 Click on the Chart Styles tool to the right

of the chart to close the

gallery

 Click on the Chart Data worksheet tab

4

For Your Reference… Handy to Know…

To change the chart style:  Instead of using the Chart Styles tool to the

1. Ensure the chart or chart sheet is selected right of the chart, you can also choose chart

styles from the CHART TOOLS: DESIGN

2. Click on the Chart Styles tool to the right of

tab on the ribbon when a chart is selected.

the chart

3. Click on the desired style

ITTraining@sgul.ac.uk Page 51 St. George’s Information Services

Microsoft Excel

PRINTING A CHART SHEET

You can print an embedded chart simply by simply ensure that the chart sheet is active, then

printing the worksheet as if it is a standard click on the FILE tab, click on Print, apply the print worksheet. You can also print a chart sheet in settings as desired and click on [Print]. exactly the same way. To print a chart sheet, the

worksheet data. But the real benefit of inserting

Try This Yourself:

e Continue using the previous

m file with this exercise, or

Sa File open the file E1317

Charting_10.xlsx...

 Click on the Revenue Chart worksheet tab

 Click on the Chart Title text box, select the text, then

type Revenue Chart to

change the title

 Repeat step 2 to change the Axis Title to Euros

 Click on the 2 FILE tab, then click on Print to see the

print options and a preview

of the chart

No further adjustment is

required here so we can go

ahead and print it…

 If you wish to print the chart, click on [Print]

If you don’t have a printer

connected or wish to save

paper, click on the Back

arrow to return to the

worksheet…

 Click on the Chart Data worksheet tab

4

For Your Reference… Handy to Know…

To print a chart sheet:  When you preview a chart prior to printing, it

1. Click on the chart sheet tab may not appear as clearly as you would like.

This is due to the screen resolution, not the

2. Click on the FILE tab, then click on Print

chart itself. The printed version of the chart

3. Click on [Print] will appear clearer than the preview.

ITTraining@sgul.ac.uk Page 52 St. George’s Information Services

Microsoft Excel

EMBEDDING A CHART INTO A WORKSHEET

Charts can either be presented in their own sheet and a worksheet as often as you wish without

sheets or they can be embedded into a impacting at all on the chart. Sometimes it is easier

worksheet that contains data. In fact, you can to work with a chart in its own sheet, but it may be

move a chart back and forth between its own necessary to print the chart with its data.

3

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m file E1317

Sa Charting_11.xlsx...

 Click on the Revenue 4 Chart worksheet tab

 Click on the CHART TOOLS: DESIGN tab,

then click on the Move

Chart tool in the

Location group to

display the Move Chart

dialog box

 Click on Object in, then click on the drop arrow

and click on Sheet 2

 Click on [OK] to move the chart to the

worksheet

5

 Reposition the chart by dragging it to the top

left of the sheet, then

drag the resizing

handles to resize it as

shown

 Click on the Chart Data worksheet tab

For Your Reference… Handy to Know…

To embed a chart in a worksheet:  Embedding is normally only done when it is

1. Click on the necessary to print the worksheet and the CHART TOOLS: DESIGN tab,

then click on data together. Move Chart in the Location group

2. Click on the drop arrow, select the sheet to

embed it into, then click on [OK]

ITTraining@sgul.ac.uk Page 53 St. George’s Information Services

Microsoft Excel

DELETING A CHART

If you no longer require a chart you can easily chart sheets you can delete the sheet by right

delete it. With embedded charts you must first clicking on the chart sheet tab and choosing the

select the chart in the worksheet and then press deletion option.

the key to delete the chart. With charts in

1

Try This Yourself:

Continue using the

ile previous file with this

F

e exercise, or open the

m

file E1317

Sa Charting_12.xlsx...

 Click on Sheet 2 to see the chart in the

worksheet, then click

on the chart to select it

 Press to delete the chart

2

For Your Reference… Handy to Know…

To delete a chart:  Because it is so easy to delete a chart object

1. Click on the worksheet to see the chart, then it is also easy to delete it by accident!

click on the chart to select it Remember, you can use the Undo feature in

Excel to restore accidental deletions.

2. Press

ITTraining@sgul.ac.uk Page 54 St. George’s Information Services

Microsoft Excel 2013 - Level 1

PRACTICE EXERCISE

Creating Charts

Tasks: Completed:

Before starting this exercise you MUST have completed all of the topics in

the chapter Creating Charts…

 Open the workbook called PE_Creating Charts.xlsx (it can be found in the  same folder as the student files)

  Create a Clustered Column chart showing the sales of products for the months of January through to June

  Drag the chart down below the data and resize it so that it is the same width as the data, keeping the proportions as far as possible

  Change the chart type to 3-D Stacked Column and change the chart title to Sales

The chart should appear as shown in sample A on the following page...

 Create a Pie in 3-D chart of the products and their totals then place it on its  own chart sheet called Product Sales

 Change the Chart Title to Product Sales 

 Change the layout to Layout 6  The chart should appear as shown in sample B on the following page...

 Print the pie chart 

 Use the Save As command to save the workbook as PE_Creating Charts  (Completed).xlsx

Files required for PE_Creating Charts.xlsx exercise:

Files/work created by PE_Creating Charts (Completed).xlsx, 1 printed copy of the Product Sales

student: chart

Exercise Completed: 

© Watsonia Publishing Page 55 Chapter 10 - Practice Exercise

Microsoft Excel 2013 - Level 1

PRACTICE EXERCISE SAMPLE

Creating Charts

A

B

© Watsonia Publishing Page 56 Chapter 10 - Practice Exercise

Table of Contents

Understanding Workbooks ............................................................................

Navigating in a File ...............................................................................

Typing Text or Numbers Into A Worksheet ............................................................

Typing Simple Formulas In A Worksheet ..............................................................

Filling A Series ...................................................................................

Inserting And Deleting Worksheets ..................................................................

Copying A Worksheet ................................................................................

Renaming A Worksheet ...............................................................................

Moving or Copying A Sheet To Another Workbook ......................................................

Changing Worksheet Tab Colours .....................................................................

Grouping Worksheets ................................................................................

Freezing Rows And Columns ..........................................................................

Selecting Ranges ...................................................................................

Selecting Rows .....................................................................................

Selecting Columns ..................................................................................

Understanding Formatting ...........................................................................

Applying General Formatting ........................................................................

Changing Fonts .....................................................................................

Changing Font Size .................................................................................

Understanding Borders ..............................................................................

Applying A Border To A Range .......................................................................

Wrapping And Merging Text ..........................................................................

PRACTICE EXERCISE ..................................................................................

Understanding Functions ............................................................................

Using The SUM Function To Add ......................................................................

Calculating An Average .............................................................................

Finding A Minimum Value ............................................................................

Common Error Messages ..............................................................................

Understanding Quick Analysis .......................................................................

Quick Formatting ...................................................................................

Quick Charting .....................................................................................

Quick Totals .......................................................................................

Quick Sparklines ...................................................................................

Quick Tables .......................................................................................

Printing A Worksheet ...............................................................................

The Charting Process ...............................................................................

Choosing The Right Chart ...........................................................................

Using A Recommended Chart ..........................................................................

Creating A New Chart From Scratch ..................................................................

Working With An Embedded Chart .....................................................................

Resizing A Chart ...................................................................................

Repositioning A Chart ..............................................................................

Printing An Embedded Chart .........................................................................

Creating A Chart Sheet..............................................................................

Changing The Chart Type ............................................................................

Changing The Chart Layout ..........................................................................

Changing The Chart Style ...........................................................................

Printing A Chart Sheet .............................................................................

Embedding A Chart Into A Worksheet .................................................................

Deleting A Chart ...................................................................................