ms excel 2007 manual
TRANSCRIPT
-
8/13/2019 MS Excel 2007 Manual
1/92
-
8/13/2019 MS Excel 2007 Manual
2/92
Excel 2007 Intermediate - Page 2
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
1995-2008 Cheltenham Courseware Pty. Ltd.
All trademarks acknowledged. E&OE.
No part of this document may be copied without written permission from Cheltenham Courseware unlessproduced under the terms of a courseware site license agreement with Cheltenham Courseware.
All reasonable precautions have been taken in the preparation of this document, including both technical andnon-technical proofing. Cheltenham Courseware and all staff assume no responsibility for any errors oromissions. No warranties are made, expressed or implied with regard to these notes. Cheltenham Coursewareshall not be responsible for any direct, incidental or consequential damages arising from the use of any materialcontained in this document. If you find any errors in these training modules, please inform CheltenhamCourseware. Whilst every effort is made to eradicate typing or technical mistakes, we apologise for any errorsyou may detect. All courses are updated on a regular basis, so your feedback is both valued by us and will helpus to maintain the highest possible standards.
Sample versions of courseware from Cheltenham Courseware(Normally supplied in Adobe Acrobat format): If the version of courseware that you are viewing is marked as NOTFOR TRAINING, SAMPLE, or similar, then it cannot be used as part of a training course, and is made availablepurely for content and style review. This is to give you the opportunity to preview our courseware, prior to makinga purchasing decision. Sample versions may not be re-sold to a third party.
For current license informationThis document may only be used under the terms of the license agreement from Cheltenham Courseware.Cheltenham Courseware reserves the right to alter the licensing conditions at any time, without prior notice.Please see the site license agreement available at: www.cheltenhamcourseware.com.au/agreement
Contact Information
Australia / Asia Pacific / Europe (ex. UK) / Rest of the WorldEmail: [email protected] Web: www.cheltenhamcourseware.com.au
USA / CanadaEmail: [email protected]
Web: www.cheltenhamcourseware.com
UKEmail: [email protected] Web: www.cctglobal.com
.
-
8/13/2019 MS Excel 2007 Manual
3/92
-
8/13/2019 MS Excel 2007 Manual
4/92
1995-2007 Cheltenham Courseware Pty. Ltd. Phone: 1300 852 204 Fax: 1300 852 206Email: [email protected] Internet: http://www. cheltenhamcourseware.com.au
Questions to ask our competitors Do they supply a library of training courses, at a price even remotely close to our low prices?
Are complete samples of ALL the IT courses available for download so that you can judge the quality?
If they offer courses on a site license basis, is the full range of courses are included?
Is the site license an annual license or a one-off payment? Are the training materials fully editable and can you add your own name and logos?
Are the manuals easy to edit using standard 'cut and paste' techniques and can the table of contents
be easily updated, or do I have to use proprietary software to edit the materials?
Are upgrades and new courses included free of charge for 12 months?
Are Intranet versions available?
Are PowerPoint slides included in the price?
If you are unhappy with your current computer courseware supplier, please download our samples
now and switch to our quality driven, cost effective courseware solution.
Our commitment to ECDL / ICDL The European Computer Driving Licence (ECDL) is the internationally recognised qualification whichenables people to demonstrate their competence in computer skills. The record breaking ECDL / ICDL
certification is the fastest growing IT user qualification in over 125 countries.
Our ECDL / ICDL training manuals are approved for the UK, Ireland, Australia, Cyprus, Middle East
(UNESCO region), South Africa and the Asia Pacific region.
Cheltenham Courseware was first courseware company to release ECDL courseware.
We were first company to release ECDL Foundation approved Advanced Level courseware.
We were one of the first companies in the world to release courseware for ECDL syllabus 4.
We worked with the United Nations (UNESCO) to produce the first Arabic ICDL courseware.
We were one of the first companies in to release approved courseware for ECDL/ICDL WebStarter
Contact Details:
UK/Ireland: www.cctglobal.comUSA/Canada: www.cheltenhamcourseware.comAustralia/Rest of the world: www.cheltenhamcourseware.com.au
-
8/13/2019 MS Excel 2007 Manual
5/92
Excel 2007 Intermediate - Page 3
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
MANIPULATING DATA & NAMED RANGES ................................................................................................. 5
PASTE SPECIAL TECHNIQUES ........................................................................................................................... 5
Paste Special .............................................................................................................................................. 5
Transposing data ........................................................................................................................................ 6
IMPORTING A TEXT FILE AND DELIMIT BY COMMA,SPACE OR TAB..................................................................... 7
Importing text and delimiting by space, comma or tab......................................................................... 7
NAMED RANGES ............................................................................................................................................... 10
What does naming a cell range mean? ................................................................................................ 10
Rules for naming cells and ranges ........................................................................................................ 10
Naming cell range(s) in a worksheet..................................................................................................... 11
Named ranges within formulas............................................................................................................... 11
Navigating through workbooks using named ranges.......................................................................... 12
Creating named ranges automatically based on cell values.............................................................. 12
Deleting named cells/ranges .................................................................................................................. 13
SUB-TOTALING ................................................................................................................................................. 13
Creating subtotals .................................................................................................................................... 14
Removing subtotals.................................................................................................................................. 15
TEMPLATES ...................................................................................................................................................... 16
Using templates ........................................................................................................................................ 16
Creating templates ................................................................................................................................... 17
Opening and editing templates............................................................................................................... 18
FORMATTING & DISPLAY TECHNIQUES................................................................................................... 20
CELL STYLES ................................................................................................................................................... 20
Formatting tables ...................................................................................................................................... 20
TABLE STYLES ................................................................................................................................................. 21
Formatting tables using table styles ...................................................................................................... 21
CONDITIONAL FORMATTING............................................................................................................................. 21Formatting cell ranges using conditional formatting............................................................................ 22
CUSTOM NUMBER FORMATS............................................................................................................................ 23
Creating custom number formats........................................................................................................... 23
FREEZING ROW AND COLUMN TITLES .............................................................................................................. 25
Freezing the top row ................................................................................................................................ 25
Freezing the first column ......................................................................................................................... 26
Freezing the top row and the first column at the same time.............................................................. 27
HIDING AND UN-HIDING ROWS AND COLUMNS................................................................................................. 27
Hiding and un-hiding rows....................................................................................................................... 27
Hiding and un-hiding columns ................................................................................................................ 29
HIDING /UN-HIDING WORKSHEETS .................................................................................................................. 29
Hiding and un-hiding worksheets........................................................................................................... 29SORTING AND QUERYING DATA ................................................................................................................ 32
SORTING DATA BY MULTIPLE COLUMNS........................................................................................................... 32
Sorting internal Excel databases............................................................................................................ 32
CUSTOM SORTS ............................................................................................................................................... 35
Custom sort options ................................................................................................................................. 35
USINGAUTOFILTER ......................................................................................................................................... 37
Using AutoFilter to query data................................................................................................................ 37
Multiple queries......................................................................................................................................... 39
Removing filters ........................................................................................................................................ 40
Top 10 AutoFilter ...................................................................................................................................... 40
USING ADVANCED QUERY
/FILTER OPTIONS................................................................................................... 42
Filtering unique records ........................................................................................................................... 42
Advanced Filter ......................................................................................................................................... 43
-
8/13/2019 MS Excel 2007 Manual
6/92
Excel 2007 Intermediate - Page 4
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
LINKING & CONSOLIDATING DATA............................................................................................................. 46
LINKING DATA OR A CHART WITHIN A WORKSHEET.......................................................................................... 46
Linking individual cells within a worksheet ........................................................................................... 46
Linking charts to data within a worksheet............................................................................................. 46
LINKING DATA OR A CHART BETWEEN WORKSHEETS [WITHIN A WORKBOOK]................................................. 48
Linking a cell range on one worksheet to another worksheet (within the same workbook).......... 48
Linking data on one worksheet to a chart in another worksheet (within the same workbook)...... 49
LINKING DATA OR A CHART BETWEEN SPREADSHEETS [WORKBOOKS]........................................................... 51
Linking data from one workbook to another......................................................................................... 51
Linking a chart from one workbook to another..................................................................................... 52
LINKING DATA OR A CHART INTO A WORD PROCESSING DOCUMENT ............................................................... 53
Copying data from Excel into a Word document................................................................................. 53
Linking data from Excel into a Word document................................................................................... 53
Copying a chart from Excel into a Word document............................................................................. 54
Linking a chart from Excel into a Word document............................................................................... 55
CONSOLIDATING DATA IN ADJACENT WORKSHEETS USING A 3DSUM FUNCTION........................................... 56
Consolidating data over several worksheets or worksheet pages.................................................... 56
CHARTS FORMATTING TECHNIQUES ....................................................................................................... 58
Changing the angle of pie chart slices .................................................................................................. 58
Formatting the chart axis fonts ............................................................................................................... 59
Formatting the chart axis scales ............................................................................................................ 60
Formatting the chart axis text orientation.............................................................................................. 62
Creating and positioning a chart title..................................................................................................... 63
Re-positioning a chart legend................................................................................................................. 64
Re-positioning chart data labels............................................................................................................. 65
Exploding the segments within a pie chart........................................................................................... 66
Deleting a data series within a chart...................................................................................................... 68
Adding a data series to a chart............................................................................................................... 69
Modifying the chart type for a defined data series............................................................................... 70
Widening the gap between columns / bars within a 2-D chart........................................................... 71
Inserting an image into a 2D chart (as a background)........................................................................ 73
Inserting an image into a 2D chart (to format a column or bar of data)............................................ 76
PROTECTION AND SECURITY ..................................................................................................................... 78
WORKBOOK PASSWORD PROTECTION ............................................................................................................ 78
Specifying a password for opening a workbook .................................................................................. 78
Using the read-only recommended option......................................................................................... 80
Removing a password from an Excel workbook.................................................................................. 83
PROTECTING /UN-PROTECTING A WORKSHEET WITH A PASSWORD............................................................... 85
Protecting a worksheet or worksheet elements................................................................................... 85
Removing workbook protection.............................................................................................................. 87
Allowing selective editing of a protected worksheet............................................................................ 87
-
8/13/2019 MS Excel 2007 Manual
7/92
Excel 2007 Intermediate - Page 5
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Manipulating Data & Named Ranges
Paste Special Techniques
Paste Special
Open a workbook called Paste special 01.
Select the cell(s) containing the data you wish to copy, in this case the range
A2:D6.
Press Ctrl+Cto copy the range.
Select the cell you want to paste the copied data into, in this case cell F2.
Click on the down arrowunder the Pasteicon (on the HomeTab).
This will display a range of Paste options. In this case select Paste Values.
You will notice that just the values are pasted, without any of the formattingthat was applied to the original table.
-
8/13/2019 MS Excel 2007 Manual
8/92
Excel 2007 Intermediate - Page 6
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Save your changes and close the workbook.
Transposing data
Open a workbook called Paste special 02.
Select the cell(s) containing the data you wish to copy, in this case select the
range A2:D6.
Press Ctrl+Cto copy the range.
Select the cell you want to paste the copied data into, in this case click on cellF2.
Click on the down arrowunder the Pasteicon.
Select the Transposecommand. The data should resemble the illustrationbelow.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
9/92
Excel 2007 Intermediate - Page 7
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Importing a text file and delimit by comma, space or tab
Importing text and delimiting by space, comma or tab
Click on the Office Button icon (top-left of your screen) and select the Opencommand. The Opendialog box will be displayed. In the Files of typesection click on the down arrowand select All Files.
Select a text file called Import text.txt. This file is delimited by tab stops.
Click on the Openbutton. You will see the Text Import Wizard (Step 1 of
3)dialog box, as illustrated.
-
8/13/2019 MS Excel 2007 Manual
10/92
Excel 2007 Intermediate - Page 8
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Notice that the dialog box has two areas, the upper referring to choices youcan make, the lower showing the data you are about to import. The wizardlooks at your source data and the original file format and determines whetherit is delimited or not. You can choose to alter this decision if you wish. In
this case Delimitedtext is automatically detected.
Click Nextand the Text Import Wizard (Step 2 of 3)dialog box isdisplayed. The upper half of this box allows you to select the required field
delimiter. The default is Tab.
The delimited text is displayed at the bottom of the dialog box.
Click on the Nextbutton. The Text Import Wizard (Step 3 of 3)dialogbox is displayed.
-
8/13/2019 MS Excel 2007 Manual
11/92
Excel 2007 Intermediate - Page 9
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
You can select the data format for each of the columns. You can also decidewhether you wish to import a column or not. In this case click on the Finishbutton to import the text into your spreadsheet. The data should be neatlyseparated into columns, as illustrated below:
Save your data in Excel Workbookformat. To this click on the OfficeButtonand select the Save Ascommand. When the Save Asdialog box isdisplayed, use the Save as typesection of the dialog box to select ExcelWorkbook. Use My Delimited Fileas the file name.
-
8/13/2019 MS Excel 2007 Manual
12/92
Excel 2007 Intermediate - Page 10
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Close the workbook.
Named ranges
What does naming a cell range mean?
We have seen that cells can be referred to by their location references:
Examples of single cell references would include.C1AA23IV16A1
Examples of referencing a range of contiguous cells would include.
A1:A6B3:X3B16:F20
It is also possible to name cells and ranges so that they can be referred tousing meaningful names, such as sales.
It is much easier to understand a formula such as:
=unit_price*no_of_units
than a formula of the type shown below.
=A9*B84
Rules for naming cells and ranges
Range names normally begin with a letter or underscore character.
Range names must NOT contain hyphens or spaces.
Range names should be limited to 255 characters.
Bear in mind that names of 10-15 characters in length will be visible in most
drop-down menus.
-
8/13/2019 MS Excel 2007 Manual
13/92
Excel 2007 Intermediate - Page 11
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Naming cell range(s) in a worksheet
Open a file called Named ranges 01.
We are going to name a range for the Quantity Sold.
Select the cell(s) to be named, in this case cell B10. Click in the Name Box
with the mouse and enter the name you want to give to the cell(s), in thiscase Quantity_Sold, and press Enter. (If you see an error message, this isbecause you did not place an underscore character between the words
Quantityand Sold):
We are now going to name a range for the Price per Item. Highlight andselect the cell(s) to be named, in this case cell D4. Click in the Name Box
with the mouse and enter the name you want to give to the cell(s), in thiscase Price_per_item, and press Enter.
TIP: If you see an error message, this is because you did not place anunderscore character between the words.
Named ranges within formulas First click on cell B13and enter an equals symbol (=).
-
8/13/2019 MS Excel 2007 Manual
14/92
Excel 2007 Intermediate - Page 12
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on cell B10and enter a multiply symbol (*).
Click on cell D4and then press the Enterkey.
Click on cell B13again and notice the formula in the Formula Bar. As youcan see this makes the formula much easier to understand:
Navigating through workbooks using named ranges
Having defined a set of named cell ranges, click the down arrowto the rightof the Name Boxand select the named cell/range you wish to go to from the
drop down list that appears. Try going to both ranges that you have named.
Save your changes and close the workbook.
Creating named ranges automatically based on cell values
Open a file called Named ranges 02.
Click on cell B2, which we wish to name the cell using the name Tom.
Click on the Formulastab and then click on the DefineName icon.
The New Name dialog box is displayed and as you can see, hasautomatically picked up the name Tom.
-
8/13/2019 MS Excel 2007 Manual
15/92
Excel 2007 Intermediate - Page 13
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton to complete the naming of the cell.
Repeat these actions so that cells B3:B5are also named with the relevantperson.
Deleting named cells/ranges
Click on the Formulastab and then click on the Name Manager icon.
The Name Managerdialog box is displayed
Select the name you wish to delete, in this case Nyahand then click on theDeletebutton. A warning dialog box is displayed, click on the OKbutton toconfirm the deletion.
Save your changes and close the workbook.
Sub-totaling
-
8/13/2019 MS Excel 2007 Manual
16/92
Excel 2007 Intermediate - Page 14
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Creating subtotals
Open a workbook called Subtotals.
First, sort the data in ascendingorder, using the data in the Sales Regionto perform the sort.
To do this click on Datatab. Click on cell A3and then click on the SortAscendingicon.
Click within the data in the Sales Regioncolumn.
Click on the Subtotalicon.
This will display the Subtotaldialog box.
-
8/13/2019 MS Excel 2007 Manual
17/92
Excel 2007 Intermediate - Page 15
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton. You will see the data subtotaled as illustrated:
Removing subtotals
Click on the Subtotalicon and within the dialog box displayed, click on theRemove Allbutton.
Reapply the subtotals once again and the close the workbook, saving your
data.
-
8/13/2019 MS Excel 2007 Manual
18/92
Excel 2007 Intermediate - Page 16
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Templates
Using templates
Click on the Office Buttonand select the Newcommand.
This will display the New Workbookdialog box. In this case select theInstalled Templatescommand (on the left of the dialog box). You shouldsee the following displayed.
Select the Billing Statementand click on the Createbutton and you willsee the following.
-
8/13/2019 MS Excel 2007 Manual
19/92
Excel 2007 Intermediate - Page 17
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Take a little time to examine the workbook that has been created for you.You can click on the various fields within the document. You can enter valuesfor billing items and totals will be calculated automatically. After a short timeexperimenting, save the workbook as My Billing Statement, and close theworkbook.
If you have time take a look at some of the other installed templates.
Creating templates
Open a workbook called Make into template. This is a workbook not atemplate. We will now save this workbook as a template. To do this click onthe Office Button and click on the Save Ascommand. This will display the
Save Asdialog box.
In the File namesection of the dialog box, enter the name Monthly Sales.
Click on the down arrownext to the Save as Typesection of the dialog box
and select Excel Template.
Click on the Savebutton to save the template. Close the template.
-
8/13/2019 MS Excel 2007 Manual
20/92
Excel 2007 Intermediate - Page 18
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
To use the template, click on the Office Buttonand then click on the Newcommand. Select the My templatesoption.
You will see a dialog box displaying your template file.
Click on the Monthly Sales template and then click on the OKbutton. Anew workbook will be created, based on the template.
NOTE: You have not opened the template file, you have created a newworkbook, based on the template file.
You can fill in details on the workbook (just make up some data) and thensave the workbook as My New Sales Report. Then close the workbook.
Opening and editing templates
Click on the Office Button, and then click on the Opencommand, which willdisplay the Opendialog box.
In the Files of typesection of the dialog box, select Templates.
Select your template file and you can now make changes to the actual
template file. Add your name next to the name field and re-save the file.
-
8/13/2019 MS Excel 2007 Manual
21/92
Excel 2007 Intermediate - Page 19
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
NOTE: Any changes that you make to the template file will only affect newworkbooks that are created using the template. Any workbooks that werepreviously created based on the template will remain unaffected.
Create a new document based on your template and this time you will see
that your name is now automatically displayed within the new workbook.Close the workbook without saving your changes.
-
8/13/2019 MS Excel 2007 Manual
22/92
Excel 2007 Intermediate - Page 20
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Formatting & Display Techniques
Cell Styles
Formatting tables
Open a workbook called Cell styles.
Select a cell within the data in the worksheet.
Click on the Hometab and from within the Stylesgroup click on the CellStyles icon.
This will display a drop down from which you can select a style to apply to
the selected cell(s).
Experiment with applying different formatting styles to different cells.Remember that you can select more than one cell at a time by keeping the
Ctrlkey pressed as you click on cells. When you have finishedexperimenting save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
23/92
Excel 2007 Intermediate - Page 21
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Table Styles
Formatting tables using table styles
Open a workbook called Table styles. Select the table within the worksheet.
If necessary select the Hometab and then click on the Format as Tableicon, within the Stylesgroup. This will display a drop down from which youcan select a style to apply to your table. Experiment with applying differentstyles and look at the effect it has on your table.
An example style is shown below.
When you have finished experimenting save your changes and close your
workbook.
Conditional Formatting
-
8/13/2019 MS Excel 2007 Manual
24/92
Excel 2007 Intermediate - Page 22
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Formatting cell ranges using conditional formatting
Open a file called Conditional formatting. This workbook contains detailsfor sales over a 3-month period. We wish to highlight all sales over 50.
Select the range of cells to which you wish to apply conditional formatting. Inthis example, the range would be C6:E13.
Click on the Hometab and then click on the Conditional Formattingicon(within the Styles group).
This will display a drop down list. In this case click on Greater Thanfromthe submenu displayed.
This will display theGreater Thandialog box. We wish to highlight all values
over 50 so we need to enter the number 50, as illustrated.
-
8/13/2019 MS Excel 2007 Manual
25/92
Excel 2007 Intermediate - Page 23
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Next, we need to set the formatting that will be displayed to highlightnumbers over 50. Click on the down arrownext to the withsection of thedialog box. Pick a formatting option, such as Red Border.
When finished click on the OKbutton and you will see that all cells containingvalues over 50,are now formatted, as requested.
If you have time investigate some of the other conditional formatting options.When you have finished experimenting save your changes and close the file.
Custom number formats
Creating custom number formats
Open a workbook called Custom number formats.
Select cells C6and D6, which contain the annual balance information.
-
8/13/2019 MS Excel 2007 Manual
26/92
Excel 2007 Intermediate - Page 24
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Right click on the selected cells and from the popup menu displayed select
the FormatCellscommand, which will display a dialog box.
Select the Numbertab.
From the Categorysection of the dialog box, select Custom.
Within the Typesection of the dialog box, select a format code similar to thatshown below:
#,##0;[Red]-#,##0
Within the format code, change the word Redto Blue:
Click on the OKbutton and the negative balance for the year 2006 will bedisplayed in blue.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
27/92
Excel 2007 Intermediate - Page 25
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Freezing row and column titles
Freezing the top row
Open a workbook called Freeze titles. In this example, we have column titles, which in this case, are the months of
the year, and row titles, which relate to stock numbers. The main data areadisplays the number of each stock type sold in a particular month:
If we scroll down the page we would soon see the following. As you can seethe column titles are no longer visible.
Click on the Viewtab and from within the Windowgroup select the FreezePanes icon.
-
8/13/2019 MS Excel 2007 Manual
28/92
Excel 2007 Intermediate - Page 26
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
You will see a drop down list of options. Click on Freeze Top Row.
Now when you scroll down the page the top row containing the columnheaders remains frozen and visible at all times.
To unfreeze the pane, click on the Freeze Panesbutton and select the
Unfreeze Panes command.
Freezing the first column
If we now scroll to the right, we will see the following. The row titles are no
longer visible:
To freeze the first column, click on the Viewtab and from within the Windowgroup select the Freeze Panes icon. Click on the Freeze First Columncommand.
-
8/13/2019 MS Excel 2007 Manual
29/92
Excel 2007 Intermediate - Page 27
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
If we now scroll to the right, the row titles will remain visible, as illustrated:
To unfreeze the pane, click on the Freeze Panesbutton and select the
Unfreeze Panes command.
Freezing the top row and the first column at the same time
Click on cell B2and then click on the Viewtab and from within the Windowgroup select the Freeze Panes icon. Click on the Freeze Panescommand.
You will find that the top row and the first column have now both beenfrozen.
Save your changes and close the workbook.
Hiding and un-hiding rows and columns
Hiding and un-hiding rows
Open a file called Hide.
Click the row heading to select the row you wish to hide. Highlight other rows
by pressing Ctrlwhile you click on each row heading. In this case, select therow relating to Carla. Right click on the selected row and from the popup
-
8/13/2019 MS Excel 2007 Manual
30/92
Excel 2007 Intermediate - Page 28
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
menu displayed select the Hidecommand.
The selected row is now hidden. As you can see Carlas details are no longerdisplayed.
To unhide the row, select the rows above and below the hidden row, andright click over the selected rows. From the popup menu displayed select the
Unhidecommand. The hidden rows will now be displayed again.
-
8/13/2019 MS Excel 2007 Manual
31/92
Excel 2007 Intermediate - Page 29
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Hiding and un-hiding columns
Select the column you wish to hide, in this case the column relating to Salesfor2005. Right click over the selected column and from the popup menudisplayed, select the Hidecommand.
The column will no longer be displayed, as illustrated.
To unhide the column, select the columns on either side of the hidden column
and right click over the selected column. From the popup menu displayedselect the Unhidecommand.
Experiment a little more with hiding and un-hiding rows and columns. When
you are finished, save your changes and close your workbook.
Hiding / un-hiding worksheets
Hiding and un-hiding worksheets
Open a workbook called Worksheet.
Right click on the sheet tab to select the sheet you wish to hide, in this case
Sheet 2. From the popup menu displayed select the Hidecommand.
-
8/13/2019 MS Excel 2007 Manual
32/92
Excel 2007 Intermediate - Page 30
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Sheet2will now be hidden, as illustrated.
To unhide the worksheet, right click on one of the worksheet tabs and fromthe popup menu displayed, select the Unhidecommand.
You will see a dialog box displayed from which you can select the hidden
sheet you wish to unhide.
-
8/13/2019 MS Excel 2007 Manual
33/92
Excel 2007 Intermediate - Page 31
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton and the hidden sheet will now be displayed again.
Experiment with hiding another worksheet and then save your changes and
close the workbook.
-
8/13/2019 MS Excel 2007 Manual
34/92
Excel 2007 Intermediate - Page 32
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Sorting and Querying Data
Sorting data by multiple columns
Sorting internal Excel databases
Open a file called Sort data 01.
In this example, we wish to sort the data by second name, and then by firstname. As you can see, there are three people with the last name Gutierrez.
Click within the Second_namecolumn of data:
Click on the Datatab and from within the Sort & Filtergroup click on theSorticon.
This will display the Sortdialog box:
-
8/13/2019 MS Excel 2007 Manual
35/92
Excel 2007 Intermediate - Page 33
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the down arrowto the right of the Sort bysection of the dialogbox and select Second_name.
Make sure that the A to Zoption is selected within the Ordersection of thedialog box.
Click on the Add Level button.
You will see a Then bysection added to the dialog box.
-
8/13/2019 MS Excel 2007 Manual
36/92
Excel 2007 Intermediate - Page 34
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the down arrowto the right of the Then bysection of the dialogbox, and select First_Name.
Make sure that the A-Zoption button is marked within the Ordersection ofthe dialog box.
Click on the OKbutton and the data will be sorted by second name and thenby first name, as illustrated:
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
37/92
Excel 2007 Intermediate - Page 35
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Custom sorts
Custom sort options
Open a workbook called Custom sorts. This contains details for a small airtravel company, operating flights to different countries that leave on specificdays of the week. We wish to sort the data by the days of the week, but ifwe do this alphabetically, we would get the following:
What we want is the list sorted so that we see Mondays flights listed first,
then Tuesday's and so on. To do this we need to perform a custom sort.
Click on a cell within the Day flights Availablecolumn.
Click on the Datatab and from within the Sort & Filtergroup click on theSorticon.
This will display the Sortdialog box.
-
8/13/2019 MS Excel 2007 Manual
38/92
Excel 2007 Intermediate - Page 36
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on down arrowwithin the Sort bysection of the dialog box, and select
Day flights Available.
Click on down arrowwithin the Ordersection of the dialog box, and selectCustom List.
The Custom Listdialog box is displayed. Select the option illustrated.
-
8/13/2019 MS Excel 2007 Manual
39/92
Excel 2007 Intermediate - Page 37
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton and close all open dialog boxes. The list will then besorted by weekday, as illustrated.
Save your changes and close the workbook.
Using AutoFilter
Using AutoFilter to query data
Open a workbook called Autofilter 01.
Click on any cell within the list.
-
8/13/2019 MS Excel 2007 Manual
40/92
Excel 2007 Intermediate - Page 38
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Datatab and from within the Sort & Filtergroup click on theFiltericon.
The table will then look like this.
We can click on the down arrowsdisplayed to apply filters to the data. Clickon the down arrow at the top of the Regioncolumn, and you will see the
following.
-
8/13/2019 MS Excel 2007 Manual
41/92
Excel 2007 Intermediate - Page 39
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
De-select Select Alland then select North, as illustrated, and click on the OKbutton.
You will now only see details relating to the Northregion.
Multiple queries
So far, we have filtered the data to show only sales in the Northregion. Wecan now add a second filter to show only sales in the North region, over a
certain amount, say 11.
To do this, click on the down arrowin the Units_Sold columnand select
Number Filters. From the submenu displayed select Greater Than.
-
8/13/2019 MS Excel 2007 Manual
42/92
Excel 2007 Intermediate - Page 40
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
This will display the Custom Autofilterdialog box. Enter the number 11, asillustrated and click on the OKbutton.
The data will now be displayed as illustrated below. Only sales above 11within the Northregion will be displayed.
Removing filters
To remove the filter re-click on the Filtericon.
Save your changes and close the workbook.
Top 10 AutoFilter
Open a workbook called Autofilter 02.
Click on any cell within the list.
Click on the Datatab and from within the Sort & Filtergroup click on theFiltericon. You will see drop down arrows applied to the top of each columnof data.
Click on the down arrowin the Units_Soldcolumn. Click on the Number
Filterscommand and from the submenu displayed select the Top 10command.
-
8/13/2019 MS Excel 2007 Manual
43/92
Excel 2007 Intermediate - Page 41
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
This will display the Top 10 AutoFilterdialog box. Type in the number 5, asillustrated and click on the OKbutton.
You will see the top 5 entries listed, as illustrated:
If you wish you can sort these, by clicking on the A-Z Sorticon within theSort & Filtergroup of the Datatab.
-
8/13/2019 MS Excel 2007 Manual
44/92
Excel 2007 Intermediate - Page 42
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
The data will now look like this.
Save your changes and close the workbook.
Using advanced query / filter options
Filtering unique records
Open a workbook called Advanced filter. This contains a list of names with
company ID numbers. If you look carefully you can see a duplicate, i.e.Carlais listed twice. In this case, the list is so small that looking forduplicates is easy, but just imagine trying to do this for a list of thousands ofnames. We wish to filter this list to avoid duplicates.
First click on a cell in the Name column. Then click on the Datatab andfrom within the Sort & Filtergroup, click on the Advancedbutton.
The Advanced Filterdialog box is displayed.
-
8/13/2019 MS Excel 2007 Manual
45/92
Excel 2007 Intermediate - Page 43
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on Unique records only, and then click on the OKbutton.
We will now see the list displayed without any duplicates. Carlais only listedonce.
Save your changes and close the workbook.
Advanced Filter
Open a workbook called Advanced Filter Criteria. This workbook contains asimple sales list. We wish to filter the list so only sales made by GinatoGermanyare displayed. So it is easier to see, we have highlighted all salesby Gina, who made two sales to Germany and one to South Africa.
Enter the following (i.e. "Gina" and "Germany") into the criteria areabeneath the list:
-
8/13/2019 MS Excel 2007 Manual
46/92
Excel 2007 Intermediate - Page 44
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Select a cell within the Sales Personlist. Click on the Datatab and from within the Sort & Filtergroup click on the
Advancedbutton.
The Advanced Filterdialog is displayed. Excel should have automaticallyentered your list range into the List rangebox.
We wish to sort according to the criteria in cells A25:D26, so click within theCriteria rangebox and enter $A$25:$D$26:
-
8/13/2019 MS Excel 2007 Manual
47/92
Excel 2007 Intermediate - Page 45
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton and Excel will filter the list, showing only records thatmatch your criteria.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
48/92
-
8/13/2019 MS Excel 2007 Manual
49/92
Excel 2007 Intermediate - Page 47
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
This will display a drop down list of chart options. Click on the first 2-Dchartoption illustrated.
The chart will be displayed as illustrated.
Change the sales values for the Northor Southregions and the chart willautomatically update to reflect these changes.
-
8/13/2019 MS Excel 2007 Manual
50/92
Excel 2007 Intermediate - Page 48
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Save your changes and close the workbook.
Linking data or a chart between worksheets [within aworkbook]
Linking a cell range on one worksheet to another worksheet (within thesame workbook)
Open a workbook called Link data between worksheets. This workbook
contains two page tabs. Note that postage and packing value is stored onthe second sheet.
In this example, we will link the Postage and packingvalue on the secondworksheet to a cell within the first worksheet of the workbook.
Click on cell C4on the first worksheet.
Type in the equals sign (=).
Click on the tab of the second worksheet (at the bottom of the workbook).
Click on cell A4(in the second worksheet).
Press Enterand you will return to Sheet 1.
Re-click on cell A4and you examine the formula within this cell:
You have linked data from one worksheet to another worksheet. Notice thesyntax in cell A4. You can use the help within Excel to get more information.Save your changes and close the workbook
-
8/13/2019 MS Excel 2007 Manual
51/92
Excel 2007 Intermediate - Page 49
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Linking data on one worksheet to a chart in another worksheet (withinthe same workbook)
Open a workbook called Link chart between worksheets.
NOTE: Be careful not to open a different file calledLink chart between workBOOKS
Click on the worksheet tab called Chart. You should now be viewing anempty worksheet.
Click on the Inserttab and from within the Chartsgroup click on theColumnbutton. Select the first chart within the 2-Doptions.
You will see an empty chart placeholder displayed within your worksheet.Right click on this empty chart placeholder and you will see a popup menu
displayed. Click on the Select Datacommand.
This will display the Select Data Sourcedialog box.
-
8/13/2019 MS Excel 2007 Manual
52/92
Excel 2007 Intermediate - Page 50
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Dataworksheet tab (bottom-left of your screen).
Select the table of data.
NOTE: You may have to move the dialog box so that you can select the data.
Click on the OKbutton and the chart will be displayed on the secondworksheet, using that data you selected on the first worksheet.
Try altering the data on the first worksheet and if you switch to the second
worksheet, you should find that the chart has changed to reflect yourchanges.
Save and close your workbook.
-
8/13/2019 MS Excel 2007 Manual
53/92
-
8/13/2019 MS Excel 2007 Manual
54/92
Excel 2007 Intermediate - Page 52
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Now click on cell B3, and the correct formula will be displayed in the formulabar, (an example of which is illustrated):
Try altering the data in the first workbook and you will see that the data
within the second workbook also changes.
Close the first workbook without saving any changes that you may havemade.
Save your changes and close the workbook.
Linking a chart from one workbook to another
Open two Excel workbooks called Link chart between workbooks 01
and Link chart between workbooks 02. Select the chart within the first workbook.
Copy the selected chart to the Clipboard (by pressing Ctrl+C).
Switch to the second Excel workbook.
Click at the location where you wish to paste the chart and press Ctrl+Vtopaste the chart into the workbook.
Try altering the data in the first workbook and both charts will change to
reflect these changes.
Saveyour changes and close your workbooks.
-
8/13/2019 MS Excel 2007 Manual
55/92
Excel 2007 Intermediate - Page 53
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Linking data or a chart into a word processing document
Copying data from Excel into a Word document
Start Microsoft Word. If necessary, start Excel and then open a workbook
called Link chart to word.
Within Excel, select the data (in this case the range A4:C6) that you wish tocopy to the Clipboard and press Ctrl+Cto copy the selected data to theClipboard.
Switch to your Word document (or similar program).
Place the insertion point at the location within the document where you wish
to paste the data.
Click on the Pasteicon within the Word program.
The data will be displayed within Word and can be edited within Word, in the
same way that you would edit a normal Word table.
Close Word and Excel without saving your changes.
Linking data from Excel into a Word document
Start Microsoft Word.
If necessary, start Excel and then open a workbook called Link chart toword.
Within Excel, select the data (in this case the range A4:C6) that you wish tocopy to the Clipboard and press Ctrl+Cto copy the data to the Clipboard.
Switch to your Word document (or similar program).
Place the insertion point at the location within the document where you wishto paste the data.
Click on Hometab and within the Clipboardgroup of the ribbon, click on the
down arrow under the Pasteicon. Select the Paste Specialcommand.
-
8/13/2019 MS Excel 2007 Manual
56/92
Excel 2007 Intermediate - Page 54
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
This will display the Paste Specialdialog box. Select the Paste Linkoptionand click on the OKbutton.
The data will be displayed within Word. To edit the data you need to changethe original data within Excel. Try changing the data within Excel and see if
the data changes as expected within the Word document.
Close Word and Excel without saving your changes.
Copying a chart from Excel into a Word document
Start Microsoft Word.
If necessary, start Excel and then open a workbook
called Link chart to word.
Within Excel, select the chart and copy it to the Clipboard, by pressing
Ctrl+C.
Switch to your Word document (or similar program).
Place the insertion point at the location within the document where you wish
to paste the chart. Click on the Pasteicon within the Word program.
-
8/13/2019 MS Excel 2007 Manual
57/92
Excel 2007 Intermediate - Page 55
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
The chart will be displayed within Word.
Close Word and Excel without saving your changes.
Linking a chart from Excel into a Word document
Start Microsoft Word.
If necessary, start Excel and then open a workbookcalled Link chart to word.
Within Excel, select the chart and then copy it to the Clipboard, by pressingCtrl+C.
Switch to your Word document (or similar program).
Place the insertion point at the location within the document where you wishto paste the chart.
Click on the Hometab and from within the Clipboardgroup, click on thedown arrowunder the Pasteicon. From the drop down displayed select
the Paste Specialcommand. The Paste Specialdialog is displayed. Select the Paste linkoption.
Make sure that Microsoft Office Excel Chart Objectis selected.
Click on the OKbutton.
The chart will be displayed within Word. To edit the data you need to changethe original data within Excel. Try changing the original data now and make
sure that the chart within Word is updated as expected.
Close Word and Excel without saving your changes.
-
8/13/2019 MS Excel 2007 Manual
58/92
Excel 2007 Intermediate - Page 56
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Consolidating data in adjacent worksheets using a 3D sumfunction
Consolidating data over several worksheets or worksheet pages Open a workbook called 3-D sum consolidation. Quickly examine the data
on each worksheet. In this example, we have a workbook containing a
number of worksheets. Each worksheet within the workbook contains thetotal annual sales for a salesperson called Elliot. We wish to total the salesfor the last three years and display them on the first page within theworkbook:
Click on the cell B3where we wish the consolidated data to be displayed (onthe first worksheet within the workbook).
Click on the Formulastab and from within the Function Librarygroup,click on the AutoSumicon.
Click on the tabof the second worksheet to display the second worksheet.
Highlight the cell or range to be consolidated, in this case cell B2:
While depressing the Shiftkey, click on the worksheet tabs for the otherworksheets to be consolidated, in this case for years 2005and 2004.
Press the Enterkey and the consolidated value for the three years will bedisplayed within the first worksheet.
If you click on the cell containing the consolidated data (on the firstworksheet) you will notice the following formula in the formula bar:
-
8/13/2019 MS Excel 2007 Manual
59/92
Excel 2007 Intermediate - Page 57
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
60/92
Excel 2007 Intermediate - Page 58
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Charts Formatting Techniques
Changing the angle of pie chart slices
Open a workbook called Pie chart slices. Click on the pie chart, so that the whole pie chart is selected:
Click on a particular slice of the pie chart so that only that slice is selected, asillustrated.
Right click on the selected pie chart and from the popup menu displayedselect the Format Data pointcommand.
The Format Data Pointdialog box is displayed.
-
8/13/2019 MS Excel 2007 Manual
61/92
Excel 2007 Intermediate - Page 59
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
You can use the Angle of first slicecontrol to set the required angle(experiment with changing this value).
Click on the Closebutton to confirm your change.
Save your changes and close the workbook.
Formatting the chart axis fonts
Open a workbook called Chart axis.
Select the value axis of the chart (i.e. the vertical axis currently running from
0-40). Right click on the vertical axis and from the popup menu displayed,
select the Fontcommand.
-
8/13/2019 MS Excel 2007 Manual
62/92
Excel 2007 Intermediate - Page 60
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
The Fontdialog box will be displayed.
Change some of the font attributes (such as colour and font type).Experiment with different attributes.
Click on the OKbutton to view the effects of the changes that you havemade.
Formatting the chart axis scales
Select the vertical axis again, and right click over it. From the popup menudisplay select the Format Axiscommand.
This will display the Format Axisdialog box.
-
8/13/2019 MS Excel 2007 Manual
63/92
Excel 2007 Intermediate - Page 61
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Change the Minimumvalue to 0(you will have to select the Fixedoption).
Change the Maximumvalue to 60 (you will have to select the Fixedoption).
Change the Major unitto 10(you will have to select the Fixedoption).
Click on the Closebutton to view the effects. The chart should resemble theillustration shown:
-
8/13/2019 MS Excel 2007 Manual
64/92
Excel 2007 Intermediate - Page 62
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Formatting the chart axis text orientation
Select the value axis again (i.e. the vertical axis). Right click on the verticalaxis and from the popup menu displayed, select the Format Axiscommand,which will display the Format Axisdialog box. Click on the Alignmenttab,as illustrated.
Change the orientation to 45Degrees by typing 45 into the Custom anglebox.
-
8/13/2019 MS Excel 2007 Manual
65/92
Excel 2007 Intermediate - Page 63
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Closebutton to view the effects. The chart should resemble theillustration shown:
Save your changes and close the workbook.
Creating and positioning a chart title
Open a workbook called Position chart items.
Click on the chart to select it.
Click on the Layouttab and then click on the Chart Titleicon.
-
8/13/2019 MS Excel 2007 Manual
66/92
Excel 2007 Intermediate - Page 64
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Select the Centered Overlay Titleoption. You will see the Chart Titleinserted, as illustrated.
Click on the chart title and edit to as illustrated.
Move the mouse pointer to the edgeof the chart title and drag the chart titleto a new location, as illustrated below.
Re-positioning a chart legend
Click on the chart legend to select it, as illustrated:
-
8/13/2019 MS Excel 2007 Manual
67/92
Excel 2007 Intermediate - Page 65
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Move the mouse pointer to the edgeof the chart legend, drag the legend toa new location, as illustrated below.
Re-positioning chart data labels
Click on the chart data label to select it, as illustrated:
Right click on the horizontal axis and from the popup menu displayed, selectthe Format Axiscommand. The Format Axisdialog box is displayed.
Select the Alignmenttab.
Enter a value of 45 degrees within the Custom anglesection of the dialogbox. Click on the Closebutton to apply the formatting.
-
8/13/2019 MS Excel 2007 Manual
68/92
Excel 2007 Intermediate - Page 66
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Save your changes and close the workbook.
Exploding the segments within a pie chart
Open a workbook called Exploding pie chart.
Click once on the chart itself and the entire pie chart will be selected, as
illustrated:
-
8/13/2019 MS Excel 2007 Manual
69/92
Excel 2007 Intermediate - Page 67
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
You will notice that each pie segment has a handle attached to it.
Click on one of the handles, and drag outwards. The effect is illustrated
below. Experiment with dragging all the segments outwards.
Save your changes and close the workbook
-
8/13/2019 MS Excel 2007 Manual
70/92
Excel 2007 Intermediate - Page 68
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Deleting a data series within a chart
Open a workbook called Delete data series.
To delete a data series from a chart, click on one of the columns(representing the data series that you wish to remove). In the example
illustrated, we clicked on the sales data for the sales from the Westregion,(i.e. the red column):
Press the Delkey. The result will be similar to that illustrated:
-
8/13/2019 MS Excel 2007 Manual
71/92
Excel 2007 Intermediate - Page 69
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Save your changes but leave the workbook open.
Adding a data series to a chart
You can select a data range on your worksheet and drag and drop thisdirectly into a chart; the new data range will be displayed in the chart,
following on from the previous exercise where we deleted the graphicaldisplay of data for the West region. To add this information to the chart firstwe need to select the data relating to the West region, as illustrated:
Press Ctrl+Cto copy the selected data to the Clipboard. Click within themiddle of the chart area and press Ctrl+Vto paste the data into the chart.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
72/92
-
8/13/2019 MS Excel 2007 Manual
73/92
Excel 2007 Intermediate - Page 71
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton to change the chart type, as illustrated:
Save your changes and close the workbook.
Widening the gap between columns / bars within a 2-D chart
Open a workbook called Column gaps. This workbook contains a bar chartand also a column chart, as illustrated.
-
8/13/2019 MS Excel 2007 Manual
74/92
Excel 2007 Intermediate - Page 72
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Right click on one of the columns (or bars), and from the popup menudisplayed select Format Data Series:
This will display a dialog box. If necessary, select the SeriesOptionsbuttonwithin the left of the dialog box.
-
8/13/2019 MS Excel 2007 Manual
75/92
-
8/13/2019 MS Excel 2007 Manual
76/92
Excel 2007 Intermediate - Page 74
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
This will display the Format Chart Areadialog box. Make sure that the Filltab is selected. Click on the Picture or texture filloption.
To insert a clipart picture to be displayed in the background of the chart area,
click on the Clip Artbutton, and you will be able to select an item of clipartinstalled on your computer.
-
8/13/2019 MS Excel 2007 Manual
77/92
Excel 2007 Intermediate - Page 75
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the OKbutton and close any open dialog boxes. Your chart will looksomething like the illustration below (depending on the clipart you inserted).
NOTE: Had you clicked on the Fileoption rather than the Clip Artoption,you could have placed a specified picture onto the chart background.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
78/92
Excel 2007 Intermediate - Page 76
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Inserting an image into a 2D chart (to format a column or bar of data)
Open a workbook called Chart column image.
Right click on one of the columns and from the popup menu displayed select
the Format Data Seriescommand which will display the Format DataSeriesdialog box. Click on the Filltab and select the Picture or texturefilloption. You will see the following options.
Click on the Filebutton and navigate to your samples folder (normally underthe My Documentsfolder). Select a file called Smileand click on the Insertbutton.
Click on the Stackoption and then close the dialog box
Your chart will now look like this.
-
8/13/2019 MS Excel 2007 Manual
79/92
Excel 2007 Intermediate - Page 77
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Closebutton to close the Format Data Seriesdialog box.
Save your changes and close the workbook.
-
8/13/2019 MS Excel 2007 Manual
80/92
Excel 2007 Intermediate - Page 78
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Protection and Security
Workbook password protection
Specifying a password for opening a workbook
Open a workbook called Protect workbook 01. We need to add a passwordto prevent unauthorised access to this file.
Click on the Office Buttonand then click on the right arrownext to theSave Ascommand. Select Excel Workbookfrom the submenu displayed.
The Save Asdialog box is displayed. Click on the Toolsbutton (bottom-left
of the dialog box) and from the menu displayed, select General Options.
This will display the General Optionsdialog box.
-
8/13/2019 MS Excel 2007 Manual
81/92
Excel 2007 Intermediate - Page 79
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click within the Password to opensection and type in a password, in thiscase, use the password cctglobal.
TIP: Passwords are case sensitive.
When you click on the OKbutton, you will see a confirmation dialog box.Renter the password and click on the OKbutton.
Within the Save Asdialog click on the Savebutton (bottom-right). You maysee another warning dialog box. Click on the Yesbutton to overwrite theexisting file.
The workbook is now protected by an opening password. Close the
workbook.
Reopen the workbook and you will see the following dialog box.
-
8/13/2019 MS Excel 2007 Manual
82/92
Excel 2007 Intermediate - Page 80
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Try entering the wrong password and you will see the following.
Click on the OKbutton and then re-open the workbook, this time with thecorrect password. It should open without problems, if it does not check your
spelling and also remember that passwords are case sensitive.
Click on cell C4and change the value from 28647 to 33333. Save thechanges. As you supplied a password you can change the data in the original
workbook without any problems.
Close your workbook.
Using the read-only recommended option
Open a workbook called Protect workbook 02. We wish to save this file sothat when opened it will display a dialog box, explaining that you shouldnormally open this worksheet as a read-only file, i.e. one where modifications
may be made, but where you cannot overwrite the original file. Any changeswould have to be saved in a file with a different name.
Click on the Office Buttonand then click on the right arrownext to theSave Ascommand. Select Excel Workbookfrom the submenu displayed.
-
8/13/2019 MS Excel 2007 Manual
83/92
-
8/13/2019 MS Excel 2007 Manual
84/92
-
8/13/2019 MS Excel 2007 Manual
85/92
Excel 2007 Intermediate - Page 83
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
The workbook will open and if you click on cell C7, you can change the valueto a different value. However if you look at the Title Bar across the top of theworkbook, you will see the following.
The workbook is a read-only document. You have edited the workbook, but ifyou wish to save your changes you will need to use a different file name.
Click on the Saveicon. You will see the following message.
Click on the OKbutton and you will see the Save Asdialog box displayed.Save the file using the file name suggested, i.e.:
Copy of Protect workbook 02.xlsx
Click on the Savebutton and the workbook will be saved using thisalternative name. Close the workbook.
Removing a password from an Excel workbook
Open the workbook containing the password you wish to remove, in this casea file called Protect workbook 03.
You will see a Passworddialog box illustrated. Enter the password (in lowercase) which is:
cctglobal
Click on the OKbutton to open the file.
-
8/13/2019 MS Excel 2007 Manual
86/92
Excel 2007 Intermediate - Page 84
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Office Buttonand then click on the right arrownext to theSave Ascommand. Select Excel Workbookfrom the submenu displayed.
The Save Asdialog box is displayed. Click on the Toolsbutton (bottom-leftof the dialog box) and from the menu displayed, select General Options.
This will display the General Optionsdialog box.
Clear the Password to openfield and click on the OKbutton.
Save the workbook. You may see a warning dialog box, in which case clickon the Yesbutton.
Close your workbook and then re-open the workbook. The workbook should
open without the need for a password.
Close the workbook.
-
8/13/2019 MS Excel 2007 Manual
87/92
-
8/13/2019 MS Excel 2007 Manual
88/92
-
8/13/2019 MS Excel 2007 Manual
89/92
Excel 2007 Intermediate - Page 87
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
a user of the workbook can edit the data in cells C4:C7, but nothing else.
Removing workbook protection
First we need to remove the protection. To do this click on the Hometab
and from within the Cellsgroup click on the Formatbutton. From the dropdown list displayed click on the Unprotect Sheet command.
You will see the Unprotect Sheet dialog box displayed.
Enter the password, and the sheet will now be unprotected.
Allowing selective editing of a protected worksheet We need to select the cells that we will allow to be edited, once protection is
reapplied. Select the cells C4:C7, as illustrated.
-
8/13/2019 MS Excel 2007 Manual
90/92
Excel 2007 Intermediate - Page 88
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Right click on the selected cells and from the popup menu displayed selectthe Format Cellscommand.
This will display the Format Cellsdialog box. Select the Protectiontab.You will notice that the Lockedbox is ticked.
Remove the tick, so that the dialog box looks like this and the click on the OKbutton to continue.
-
8/13/2019 MS Excel 2007 Manual
91/92
Excel 2007 Intermediate - Page 89
FOR USE AT THE LICENSED SITE(S) ONLY
Cheltenham Courseware Pty. Ltd. 1995-2008 www.cheltenhamcourseware.com.au
Click on the Hometab and from within the Cellsgroup click on the Formatbutton. From the drop down list displayed click on the Protect Sheetcommand.
This will display the Protect Sheetdialog box.
Make sure that Protect worksheet and contents of locked cellsisselected within the dialog box, as illustrated. Leave the other default settingsas is:
-
8/13/2019 MS Excel 2007 Manual
92/92
Excel 2007 Intermediate - Page 90
Enter a password, in this case cctglobaland click on the OKbutton. AConfirm Passworddialog box will appear.
Re-enter the password and click OKto proceed.
Now try editing the data in any of the cells within the range C4:C7. As youcan see you can change this data. Try editing some of the data in the other
cells and you will see a dialog box, telling you that those cells are protected. Save your changes and close the workbook.