1. No category

University of Glamorgan
How to...
get started with ICT
ASSESS AND IMPROVE YOUR BASIC
SPREADSHEET SKILLS
This document is one in a series to help you to improve your information,
communication and technology (ICT) skills. It is based on the program
Microsoft Excel®2007 — all the features described also appear in other
spreadsheet software packages but may require slightly different commands.
The guide begins with a self-assessment activity followed by exercises with
useful tips. You may wish to try the self-assessment again after you have
completed the exercises to see if you have improved your skills to a level that
satisfies you.
To develop your ICT skills further, you are welcome to use the other
documents in the series:
l
How to… Assess and Improve Your e-Mail and Internet Skills
l
How to… Assess and Improve Your Word-processing Skills
l
How to… Assess and Improve Your File Management Skills
l
How to… Assess and Improve Your PowerPoint Skills
You can download all these from http://celt.glam.ac.uk/SupportResources/?c=Documents-to-Help-Students
You can also receive support from staff and tutors at the University’s
Education Drop-in Centre (EDiC). Information on EDiC is available at
http://edic.glam.ac.uk/onlineresources/
Contents
enhancing
learning
through
Assess Your Basic Spreadsheet Skills. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
About the new look of Office 2007. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Spreadsheet skills – Basic Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Additional Spreadsheet skills . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
MindLeaders Online Training Courses . . . . . . . . . . . . . . . . . . . . . . . . . . 11
technology
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
1
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
Assess Your Basic Spreadsheet Skills
Can you...
No
Knowledge
Some
Knowledge
Competent
Create a new spreadsheet?
Enter text and numbers
accuracy?
Can you find commands
easily on the ribbon?
Set and adjust paper layout?
Set and adjust paper size?
Use ‘Print Preview’?
Print a spreadsheet?
Search for help successfully?
Select rows, cells, and
columns?
Select an entire spreadsheet?
Save a spreadsheet?
Open an existing
spreadsheet?
Change cell formatting?
Adjust font size?
Adjust font style?
Set text in bold or italics?
Adjust font style?
Underline text?
Align text left or right?
Centre align text?
Justify text?
Use ‘Undo’ and ‘Redo’?
Write simple formulas?
Use cut/ copy/ paste?
Find information unig Help?
Insert basic headers and
footers?
Use the AutoSum feature?
Create a simple chart?
How did you do? We recommend that you should be able to judge yourself as
competent in most of the skills listed, or have at least some knowledge of them.
Try the exercises which follow from the next page if you would like to improve your
basic level of skills.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
2
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
About the new look of Office 2007
This is the new look of Excel 2007. These are the terms that are used for reference
throughout.
Quick Launch Toolbar
Help
Contextual Tab
Office
Button
Example of
command buttons
Open Dialog Box
Example of
a group
Status bar
As Office 2007 looks a bit different to how it has looked in previous versions, here is
a quick look at the new Ribbon, and features of the application window.
The ribbon, has been designed to group
together similar commands, these groups
are sorted into Tabs. The Home Tab
includes the commands that you would
expect to use most within this
application. If you need a larger working
area, use the keyboard command CRTL +
F1 to hide/ unhide the ribbon.
Did you know that pressing the ALT
key will display shortcuts to the
Ribbon Commands? Plus all of hte
keyboard commands you may
already know, still work.
The help icon is located to the right of the tab
area, next to the window control buttons.
An example of another Tab is the Formulas tab, which includes the Function Library
group.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
3
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
The Office Button
has replaced the File Menu, and looks after the Document
Tasks (Opening, saving, printing etc.)
Notice the
pushpins next to
the recent
documents, if the
pin is pushed in,
the document will
remain in the list.
Did you know that you can add
commands to the Quick Launch
toolbar to work more effectiviely?
Click the drop-down button for
options.
The Quick Launch toolbar
is next to the Office Button, and
includes the Save File and Undo, Redo commands.
Contextual tabs are only displayed when
required, (e.g. chart options or pictures), and
these appear when an object is selected.
Finally, at the bottom of the
application window is the
status bar with view option
shortcuts, and zoom.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
4
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
Spreadsheet skills – Basic Exercises
Important point: add specific heading if you want (optional)
Remember that any help or advice is given based on the Microsoft Windows
operating system. Every effort has been made to accommodate the fact that
students can work with a variety of versions of the software.
1.
Open Microsoft Excel 2007 with a new blank spreadsheet.
You should find an icon for Excel 2007 in one of
three places:
Did you know that you can
press CTRL+N to create a
new spreadsheet?
i.
on the Desktop;
ii.
under ‘Start’ > ‘All Programs’; or
iii.
under ‘Start’> ‘All Programs’ > ‘Microsoft Office’.
When Excel opens, it automatically starts a new blank
spreadsheet. To create a new blank document from within Excel,
select the Office button, and select ‘New’.
2.
Enter the data exactly as shown below:
Note that each ‘box’ is referred to as a ‘cell’ and is identified by the intersection of
the relevant letter (above) and number (to the left). so in our example above, the
income for January (1025) is referred to as within cell C5.
3. Save this spreadsheet on your desktop, or location of
your choosing, as ‘Company Costs.xlsx’.
To save a document, select the Office button and select Save .
Did you know that you can also
press CRTL+S to save a document?
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
5
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
4.
Close your spreadsheet and close Microsoft Excel.
To close an individual spreadsheet, you
can use ‘Close’ on the Office button
menu.
To close Excel, you can use ‘Exit’ on the
Office button menu.
Did you know that you can press
ALT+F4 to close programs? You will
be prompted to save any changes
you have made..
Or, you can use the window control
buttons at the top right of the window.
5.
Close application
window
Close document
Reopen Excel and open ‘Company Costs.xlsx’.
Open Excel as explained in point 1 above. An existing spreadsheet can be opened
from the Office button menu by selecting ‘Open’ or you may find the filename on
the Recent Documents list.
6.
Format the text ‘Company Finances’ as Arial Black, font size 14.
Select the cell A2 using the keyboard or mouse. Then
from the Home tab, Font group, change the font type
and font size using the drop-down boxes. Note also the
increase and decrease font size buttons that are in this
group.
7. Format each of the column headings (‘Income’, ‘Outgoings’ and
‘Balance’) in bold.
Select the required cells (C2, E2 and G2)
then select the ‘Bold’ command button
from the Home tab, Font group.
8.
Did you know that if you hold down
the CTRL key you can select cells
that are not adjacent?
Format the text ‘Totals’ as font size 12, italic.
Select the cell containing the text ‘Totals’ then select the ‘Italic’ command button.
9.
Align the entire ‘Income’ column to the right.
Select the entire ‘Income’ column. This can be done quickly
by clicking on the column heading ‘C’. Then select the right
alignment command button, located in the Home tab,
Alignment group.
10. Repeat this for the ‘Outgoings’ and ‘Balance’ columns.
Your spreadsheet should now look like this:
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
6
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
11. Save your spreadsheet.
Remember that the Save command button is on the quick launch toolbar as well as
in the Office button menu.
12. Enter the current date in cell A1 and underline it.
Select cell A1 and type in the current date or use the simple formula =TODAY(), this
will insert the current date every time the spreadsheet is opened (notice the Formula
AutoComplete suggesting functions to use). This formula can also be located in the
Formulas tab, Function Library group, from the Date & Time command button.
Select this cell and click on the ‘Underline’ command button from the home tab,
Font group.
13. Align the months centrally in column A.
Highlight the range of cells for the text to be formatted
using the mouse or keyboard. Select the align centre
command button from the Home tab, Alignment
group.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
7
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
14. Enter a formula in cell G5 that will calculate the incoming budget for
January minus the outgoing budget for January.
Select cell G5 with the mouse or the keyboard. The formula should calculate the
value of C5 minus E5, and should therefore be =C5-E5.
15. Copy this formula to calculate the budget balance for each month.
Select cell G5 and move your cursor over the cell
handle at the bottom-right of the cell. Your cursor
should change to a ‘plus sign’; if you click and hold
the handle and drag downwards the formula will
be copied into the new cells.
Cell Handle
16. Use ‘AutoSum’ to calculate the total figures for the ‘Income’,
‘Outgoings’ and ‘Balance’ columns.
Select the cell where you want the total to appear. Click on
the ‘AutoSum’ command button from the Home tab,
Editing group. You can also find the AutoSum function on
the Formulas tab, Function Library group, with all the other
formulas.
17. Use the ‘Find’ facility to quickly locate the cell containing the
information for June.
Use the ‘Find’ command on the Home tab, Editing group
to search for the word ‘June’. Don’t forget the keyboard
shortcut CTRL+F will also work.
Your spreadsheet should now look like this:
18. Save your spreadsheet.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
8
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
19. Use the ‘Merge and Centre’ feature to spread the text ‘Company
Finances’ across cells A2 to G2.
Select cells A2 to G2 and click on the ‘Merge and
Centre’ command button, from the Home tab,
Alignment group.
20. Remove column D.
Select the entire column by clicking on the column header ‘D’. Note
that the delete key on the keyboard will only delete the contents of
the column, so instead us the Delete command button (Home tab,
Cells group), to remove the entire column (or row) as required.
Right-clicking your selection will also bring up the Context
menu, which will include tasks specific to your selection,
note also the mini format menu which also appears.
21. Move cells F4−F12 to column E.
Select the required cells, using either the keyboard or mouse.
The quickest way to move cells is to highlight the required cells,
move your mouse over any part of the boundary for that
selection until you see the four-headed arrow, hold the left
mouse button, and drag the cells to their new position.
Alternatively you can use the Home tab, Clipboard group commands,
Cut, Copy and Paste. Highlight the required cells and select either
Copy or Cut, move the cursor to the new position and Paste. If you
used Copy, then return to the original selection and Delete.
22. Insert another blank row between ‘June’ and ‘Totals’.
A point to note is that Excel inserts rows above the current position
of the cursor. You should therefore select a cell on either the blank
row, or on the row bearing the word ‘Totals’ before performing this
insertion. To insert the new row, from the Home tab, Cells group,
select ‘Insert’.
23. Change the formatting of all cells containing financial
figures to currency, using the euro symbol.
Select all the cells in which formatting is to be changed. On
the Home tab, Number group, select ‘Currency’, then select
the euro symbol.
24. Change the width of any columns that are too
narrow to display all figures.
The quickest way to change the width of a column is to double-click on the righthand edge of the column header, this will Auto-fit to the cell contents.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
9
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
Alternatively use the ‘Auto-fit’ command, located in the Home
tab, Cells Group, Format button menu. To manually change the
column width, use your mouse to drag the right-hand edge of
the column header, or the ‘column Width…’ command button
also located in the Home tab, Cells group, format button menu.
Your spreadsheet should now look like this:
25. Save your spreadsheet.
26. Select row 13 and change the text colour to blue.
Select the row header ‘13’ to select the entire
row. From the home tab, Font group select the
Font Color command button, and from the
drop-down menu select blue.
Any text that is typed into this row in future
will be formatted in blue.
27. Select the entire spreadsheet and change all text formatting to Times
New Roman.
The entire spreadsheet can be selected by left-clicking with your mouse in the blank
box at the top left-hand corner .
Did you know using CTRL+A will
select the entire spreadsheet?
Formatting can then be changed using the Home tab, Font group.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
10
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
28. Add the file name ‘Company Costs’ to the spreadsheet header.
Headers and footers are accessed through the Insert tab, Text group. Selecting the
‘Header & Footer’ command button will not only open the Header & Footer
Contextual tab, but will also change the View to ‘Page Setup view’. Your screen will
look like this.
Your cursor will be in the Header of the document. Add the file name from the
Header & Footer Elements group.
29. Add ‘Page Number’ to the document footer.
Use the Navigation group button ‘Go to Footer’ to move to the footer.
Your cursor will now be in the Footer of the document. Add the Page Number from
the Header & Footer Elements group.
30. Return to Normal View.
To return to the normal view, either, select ‘Normal’ from the View tab, Workbook
Views group, or the quick view buttons at the bottom of the application window.
31. Change the paper size to A5 and the layout to landscape.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
11
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
From the Page Layout tab, select Size from the Page Setup group, and select A5.
From Orientation, select Landscape.
32. Create a column chart from your monthly data
Select your data (from cell A4 to E10 only). From the Insert tab, Charts group select
the first type of 2-D Column Chart.
It is important to select the headings for
the columns and rows in addition to the
values, when creating a graph.
33. Move to a new sheet
Your chart will appear on the same sheet as your data, use the Move Chart
command in the Chart Tools Design tab, Location group, to move the chart to a new
sheet.
34. Add a chart title ‘Company Finances’
Select the first option in the Chart Layout group(Layout 1), and this will insert the
text Chart title, replace this with ‘Company Finances’.
Your chart should look like this.
A note to remember about charts!
Although there are lots of impressive chart and
graph options to choose from, remember to choose
the simplest to convey your information. This chart
is colourful, but does not convey this information
well.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
12
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
35. View your document in Print preview, when you are satisfied with the
document, print one copy.
‘Print Preview’ can be accessed from the office button menu by selecting ‘Print’ then
from the side menu, ‘Print Preview’.
Notice that you can print directly from the preview window, or you can return to the
Office button menu and select ‘Print’ there.
Your spreadsheet should now look like this:
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
13
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
Additional Spreadsheet skills
Below you will find a list of the more advanced skills that you may find useful. You
are not expected to have knowledge or competence in these areas before you start,
but as you progress through your studies you may wish to improve on your basic
skills.
Can you...
No
Knowledge
Some
Knowledge
Competent
Write a complex formula?
Use the ‘Save As’ function?
Find or replace information?
Use ‘Insert Function’?
Use the suto-fill facility?
Add/ edit borders to cells?
Edit text alignment within
cells?
Merge and split cells?
Use multiple worksheets in
a workbook?
Use formulas across
spreadsheets?
Scale printouts?
print a specific area?
Utilise drawing tools?
Add/ edit cell shading?
Adjust page margins and
layout?
Customise headers and
footers?
Insert different types of
charts and graphs?
Filter data?
Use excel as a simple
database?
Create a PivotTable, or
Report?
Note: In this document, we don’t offer further exercises to support the skills
detailed above, but you can use the ‘Help’ facility (see panel on right) or you could
follow a MindLeaders course (see next page).
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
14
EST034-03 released January 2010 • © University of Glamorgan 2010
How to…
Improve your ICT skills Assess and improve your basic Spreadsheet skills
MindLeaders Online Training Courses
You can build on the exercises in this document with the
MindLeaders online training courses. MindLeaders can be
freely used by staff and students of the University of
Glamorgan as there is a university-wide licence
arrangement.
We suggest the following course to build upon your Excel
skills:
•
Excel 2007
MindLeaders is accessed via the Hot Links, on the My
Institution tab within BlackBoard.
Click the ‘free online courses’ button, then enter your
username and password if prompted.
At the MindLeaders login page, remember that you only need to enter your
username. However you may be prompted to fill in a quick registration form the first
time you log in.
THIS IS ONE OF A SERIES OF ‘HOW TO…’ GUIDES FROM LCSS BLENDED LEARNING SUPPORT
IS Customer Support Services (LCSS) [email protected] +44 (0)1443 482882
15
EST034-03 released January 2010 • © University of Glamorgan 2010