Excel Intro exercises by Sarah Mason

Excel Introduction 2010 Exercises Version 1.0

An introduction to spreadsheets using Microsoft Excel 2010 and Windows XP Professional. This exercise manual is designed to be used in conjunction with training sessions delivered by Avon IM&T Consortium. http://nww.avon.nhs.uk/imtconsortium/Training

Avon IM&T Consortium

1|Page

Avon IM&T Consortium

Table of Contents Table of Contents.................................................................................................................. 2 Exercise 1 ............................................................................................................................. 3 Exercise 2 ............................................................................................................................. 4 Exercise 3 ............................................................................................................................. 5 Exercise 4 ............................................................................................................................. 6 Exercise 5 ............................................................................................................................. 7 Exercise 6 ............................................................................................................................. 8 Exercise 7 ............................................................................................................................. 9 Exercise 8 ........................................................................................................................... 10 Exercise 9 ........................................................................................................................... 11 Exercise 10 ......................................................................................................................... 12 Exercise 11 ......................................................................................................................... 13 Exercise 12 ......................................................................................................................... 14 Exercise 13 ......................................................................................................................... 15 Exercise 14 ......................................................................................................................... 16 Exercise 15 ......................................................................................................................... 17 Exercise 16 ......................................................................................................................... 18

2|Page

Exercise 1 Task

Entering Labels

Comments

1. Select cell A1

The cell shows a thick outline representing the ACTIVE CELL

2. Enter the text Smoking Cessation Conference

The text spills over to B1, however it is only contained in cell A1

3. Select cell A3 4. Enter the column labels: Forename, Surname, Workshop A, Workshop B, Workshop C

Use the to jump between cells or click with the mouse

Explanatory notes This exercise is identifying names of delegates attending a conference and the amounts they have paid for each workshop. Some delegates will pay half price and some are not attending all the workshops. 1

3|Page

Exercise 2 Task

Adjust column width

Comments

Move your mouse over the vertical line that separates the A and the B column label

The mouse will change shape to a cross type shape

Click and drag to the right

The column width is increased

Repeat with all other columns

Complete entering all the data as shown below

4|Page

Exercise 3 Task

Highlighting with formatting

Comments

Highlight cell A1 and make the contents size 24

Use the point size box from the Font group on the Home tab

Highlight the range A3:F8 and change the font colour to Blue

Use the font colour icon from the Font group on the Home tab

Select the heading labels row and make the contents bold

Highlight the row by selecting the number 3 on the left hand edge and choose the bold icon

Highlight the entire spreadsheet and change the font type to Comic Sans

Select the grey rectangle in the top left hand corner and use the Font options from the Font group in the Home tab

Explanatory notes Using the formatting options from the ribbon are identical in the different programs belonging to the Office suite.

5|Page

Exercise 4 Task

Editing cells

Comments

Click in cell A6 and change Eileen to Ben.

One click in the cell to select it, followed by typing the new word will overwrite the previous contents

Select cell B7 and alter the word fish to fishes.

Double click in the cell to keep the existing cell contents. The flashing cursor will appear ready to type.

Explanatory notes To save time when editing cell contents, ensure that you either click once to remove existing cell contents or double click to keep the cell contents and add to it.

6|Page

Exercise 5 Task

Inserting rows and columns

Comments

Select column F and insert a new column with the label Workshop D

From the Cells group choose Insert, then Insert Sheet Columns

Select row 6 and insert a new row for “R T Laughter”

From the Cells group choose Insert, then Insert Sheet Rows

Explanatory notes By inserting new rows and columns, the existing columns will be ‘re-lettered’ or ‘renumbered’. Remember if you can’t remember the location of this function, you can use the right mouse click.

7|Page

Exercise 6 Task 1

Adjusting Alignment Comments Select cells A3:B3 and align to 45o

Select cells A1:G1 and use the merge and centre feature Indent the contents of cell A4

Select cells C3:F3 and wrap text Select cell G3 and align contents of cell to top of cell

There are options in this icon below. It is worth with the various

several sub as detailed experimenting features

This option will join the cells together to become one and centre the text from the first cell across the merged cells. Other text from other cells will be lost

This icon increases the margin between the cell edge and the text. It could be useful if the previous columns‟ contents contained figures that are right aligned This will make all the cell contents visible by dropping the hidden words down onto the line below. Cell contents can be aligned within the cell – top, middle or bottom

Explanatory notes Cell alignment can be used to successfully ‘smarten’ the spreadsheet. Headings can be made to stand out without changing the font format.

8|Page

Exercise 7 Task

Adding borders and shading

Comments

Select the range A3:G9 and give a red dotted outline to the selection

Use the Border tab from the Format Cells dialog box.

Also give the selection a light blue inner lines.

Select B9:G9 and also G4:G9 (hold the CTRL key down) and apply a red check pattern

Using the Control key on the keyboard allows you to select non-adjacent cells.

Create a â&#x20AC;&#x17E;styleâ&#x20AC;&#x; from the formatting applied here called NHS style

Using the Styles group, select Cell Styles, New Cell styles

Open 2 styles.docx and apply the NHS style

Explanatory notes Borders and patterns can be applied to spreadsheet to define and highlight certain areas. Styles can be saved and applied to other spreadsheets.

9|Page

Exercise 8 Task

Formatting numbers

Comments

Select the range C4:F8 and apply the Number formatâ&#x20AC;&#x;

The cell contents will be displayed to 2 decimal places

Select the range C9:G9 and also G4:G9 and apply the Currency format

The numbers when added to the cells will be displayed with a ÂŁ sign and show 2 decimal places

Explanatory notes Adding a ÂŁ sign to every cell can sometimes make the spreadsheet harder to read with unnecessary symbols everywhere so often it is better to only use the Currency format to the totals row.

10 | P a g e

Exercise 9 Task

Using Autosum

Comments

Use Autosum to total each of the columns and rows as shown below.

The cells have been pre-formatted to display as Currency format.

Make the totals display in bold.

Explanatory notes Autosum is an extremely quick and easy method of totalling figures. However, be careful that autosum Is totalling the correct cells. You will have found that G6 autosum will probably have tried to total the range above rather than horizontally.

Tip: If the cell displays with ##### (hash symbols) you will need to widen the column.

11 | P a g e

Exercise 10 Task

Using formulae

Comments

Make some sensible adjustments to the data as described on the task list

Select cell B6 and add a formula to total B4 and B5

=B4+B5

Select cell C6 and add a formula to subtract C5 from C4

=C4-C5

Select cell D6 snd add a formula to multiply D4 by D5

=D4*D5

Select cell E6 and add a formula to divide E4 by E5

=E4/E5

Explanatory notes The cell addresses are not case sensitive so you can type B4 or b4. Instead of typing in the cell reference, you can select the cell with the mouse and it will enter the reference for you.

12 | P a g e

Exercise 11 Task

Using Autofill

Comments

Open the exercise 5 DVDs

Enter the formula to multiply B2 times C2

=B2*C2

Use autofill to populate the cells underneath

Click and drag the autofill handle down

In cell D8, use autosum to total the income

Explanatory notes Autofill is an extremely efficient method of copying cells relatively ie adjusting the formula as it moves down through the cells.

13 | P a g e

Exercise 12 Task

Practising Autofill

Open the exercise 4 autofill

Select the autofill handle for each entry and drag down the page

When you reach the G column, you will need to highlight G1:G2 first, and then drag down the page. This defines the interval required between entries

Comments

Explanatory notes Autofill will recognise keywords as being part of a series eg January and when filled in, will copy the same format.

14 | P a g e

Exercise 13 Task

Using Functions

Comments

Open the worksheet 5 DVDs

Select cell D9 and calculate the average price of DVDâ&#x20AC;&#x;s

=AVERAGE(D2:D7)

Select cell D10 and calculate the number of different types of DVDâ&#x20AC;&#x;s

=COUNT(D2:D7)

Select cell D11 and calculate the maximum price for a DVD

=MAX(D2:D7)

Select cell D12 and calculate the minimum price for a DVD

=MIN(D2:D7)

Explanatory notes The functions on the drop down list are an efficient method of accessing commonly used functions. Care needs to be taken that the correct cells are referenced.

15 | P a g e

Exercise 14 Task

Print Options

Comments

Open the worksheet 7 hotel

Adjust the page orientation to landscape

Adjust the margins to narrow

Add a header with the NHS logo

Add a footer with your name and the filename

Print only the This is found in the backstage view. selection A1:N33 over two pages

This can be found in Page Layout tab.

This will need to be formatted to reduce the size slightly

Explanatory notes It is worth experimenting with all the settings in the backstage view as Excel is extremely versatile in allowing you to print exactly how you wish to.

16 | P a g e

Exercise 15 Task

Print Options

Comments

Open the worksheet 7 hotel

Create a column Select the range A3:C7, choose F11 graph to show just the bedroom income for January and February

Apply one of the Shape Styles to the graph

From the Format tab, choose Shape Styles

Explanatory notes It is worth experimenting with all the settings as the choices for displaying graphs is very large!

17 | P a g e

Exercise 16 Task

Graphical Toys

Comments

Open the worksheet 7 hotel

Insert a new column after March

With the cursor in Column D, right click and choose Insert, Entire Column

Insert sparklines to graphically interpret the data from the first three months of the year

Insert tab > Sparklines > Line

Insert smartart – „Upward Arrow‟ with the text “ Design, Plan, Implement”

Explanatory notes It is worth experimenting with all the settings as the choices for sparklines and smartart is extensive!

18 | P a g e