Excel 2007 Advanced

TABLE OF CONTENTS
TABLE OF CONTENTS ...............................................................................................3
INTRODUCTION.........................................................................................................................7
How To Use This Guide .................................................................................................................. 7
Objectives...................................................................................................................................... 7
Instructions ................................................................................................................................... 7
Appendices.................................................................................................................................... 7
SECTION 1 ADVANCED WORKSHEET FUNCTIONS.....................................................9
NAMES....................................................................................................................................10
Defining Names........................................................................................................................... 10
Creating Names Automatically..................................................................................................... 11
Managing Names ......................................................................................................................... 12
USING NAMES ..........................................................................................................................14
Go To........................................................................................................................................... 14
Names In Formulae ...................................................................................................................... 15
Applying Names ........................................................................................................................... 16
CONDITIONAL & LOGICAL FUNCTIONS.......................................................................................17
If Statements............................................................................................................................... 17
Logical Test.................................................................................................................................. 18
Value If True / False ..................................................................................................................... 18
Nested If...................................................................................................................................... 19
Statistical If Statements ............................................................................................................... 21
AND, OR, NOT...........................................................................................................................22
And.............................................................................................................................................. 22
Or ................................................................................................................................................ 23
Not .............................................................................................................................................. 23
LOOKUP FUNCTIONS.................................................................................................................24
Lookup......................................................................................................................................... 24
Vector Lookup............................................................................................................................. 24
Hlookup....................................................................................................................................... 26
Vlookup ....................................................................................................................................... 27
Nested Lookups........................................................................................................................... 29
OTHER USEFUL FUNCTIONS.......................................................................................................31
ISERROR....................................................................................................................................... 31
IFERROR....................................................................................................................................... 32
COUNTIF...................................................................................................................................... 33
AVERAGEIF .................................................................................................................................. 35
AVERAGEIFS ................................................................................................................................ 36
SUMIFS........................................................................................................................................ 37
COUNTIFS.................................................................................................................................... 39
DATA CONSOLIDATION..........................................................................................41
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER.......................................44
GOAL SEEKING AND SOLVING....................................................................................................45
Goal Seek..................................................................................................................................... 45
Solver .......................................................................................................................................... 47
Solver Parameters....................................................................................................................... 47
Constraints .................................................................................................................................. 48
Solver And Scenario Manager...................................................................................................... 50
Opening Scenario Manager.......................................................................................................... 50
Saving Solver Solutions ................................................................................................................ 51
Reports........................................................................................................................................ 52
VIEWS & SCENARIOS.................................................................................................................53
Custom Views.............................................................................................................................. 53
Typical Custom View Model......................................................................................................... 53
Defining A View........................................................................................................................... 54
Showing A View........................................................................................................................... 55
Scenario Manager ........................................................................................................................ 55
Load The Scenario Manager......................................................................................................... 55
Showing A Scenario...................................................................................................................... 57
Editing A Scenario ........................................................................................................................ 58
Deleting A Scenario ...................................................................................................................... 59
SECTION 3 USING EXCEL TO MANAGE LISTS...........................................................60
EXCEL LISTS,LIST TERMINOLOGY................................................................................................61
Row And Column Content............................................................................................................ 61
Column Labels .............................................................................................................................. 61
List Size And Location................................................................................................................... 61
Miscellaneous .............................................................................................................................. 61
SORTING DATA .........................................................................................................................62
Custom Sorting Options ............................................................................................................... 65
Creating A Custom Sort Order...................................................................................................... 66
ADDING SUBTOTALS TO A LIST ..................................................................................................67
Examining Subtotals..................................................................................................................... 68
FILTERING A LIST.......................................................................................................................69
Custom Criteria ............................................................................................................................ 72
Wildcards .................................................................................................................................... 73
Turning Off Autofilter................................................................................................................... 74
Advanced Filtering ....................................................................................................................... 75
Set Criteria .................................................................................................................................. 75
Entering Search Criteria ............................................................................................................... 77
Wildcards With Text Criteria ........................................................................................................ 77
Multiple Criteria ........................................................................................................................... 77
Checking The Criteria Range ........................................................................................................ 78
Calculated Criteria....................................................................................................................... 79
Calculated Criteria Using Functions ............................................................................................. 80
Copying Filtered Data................................................................................................................... 81
Unique Records........................................................................................................................... 81
LIST STATISTICS........................................................................................................................82
Database Functions...................................................................................................................... 82
PIVOTTABLES...........................................................................................................................85
To Create A PivotChart Report From An Existing PivotTable Report ........................................... 87
Create A Static Chart From The Data In A PivotTable Report ...................................................... 88
Delete A PivotTable Or PivotChart Report ................................................................................... 88
Create Layout For PivotTables ..................................................................................................... 89
Modifying A PivotTable ................................................................................................................ 90
MANAGING PIVOTTABLES.........................................................................................................93
Using Another PivotTable Report................................................................................................. 93
Changing An Existing Report's Source Data ................................................................................. 94
Automatically Refresh Data When A Workbook Is Opened......................................................... 95
Automatically Refresh Data At Regular Time Intervals ................................................................ 95
Require A Password To Refresh An External Data Range ............................................................ 95
Grouping PivotTable Items........................................................................................................... 97
Formatting A PivotTable ............................................................................................................ 100
SECTION 4 CHARTS ..............................................................................................104
INTRODUCTION TO CHARTING ................................................................................................105
Terminology ............................................................................................................................... 105
Embedded Charts....................................................................................................................... 106
Separate Chart Pages ................................................................................................................. 106
Three Methods To Create Charts............................................................................................... 107
Moving And Resizing Embedded Charts .................................................................................... 108
Data Layout ............................................................................................................................... 108
Shortcut Menu (Right Click) ....................................................................................................... 110
Chart Types ............................................................................................................................... 110
Default Chart Type ..................................................................................................................... 114
FORMATTING CHARTS ............................................................................................................115
Design Ribbon ............................................................................................................................ 115
Switch Rows And Columns......................................................................................................... 117
Changing The Chart Layout ........................................................................................................ 117
Chart Styles ............................................................................................................................... 118
Moving Chart Location............................................................................................................... 118
Layout Ribbon ............................................................................................................................ 119
Formatting Chart Elements........................................................................................................ 119
Resetting Custom Formats......................................................................................................... 120
Adding, Removing And Formatting Labels ................................................................................. 120
Axes........................................................................................................................................... 121
Gridlines .................................................................................................................................... 122
Unattached Text......................................................................................................................... 122
Format Dialog............................................................................................................................ 123
Add A Series Manually ............................................................................................................... 125
The Series Function.................................................................................................................... 125
Charting With Blocks Of Data..................................................................................................... 125
SECTION 5 TEMPLATES ........................................................................................126
INTRODUCTION TO TEMPLATES ..............................................................................................127
Standard Templates ................................................................................................................... 127
Custom Templates ..................................................................................................................... 128
AutoTemplates.......................................................................................................................... 130
Opening And Editing Templates................................................................................................. 130
Template Properties................................................................................................................... 131
SECTION 6 AUDITING...........................................................................................133
AUDITING FEATURES ..............................................................................................................134
Tracers....................................................................................................................................... 134
Comments................................................................................................................................. 136
SECTION 7 OTHER FORMATTING .........................................................................139
FORMATTING CELLS................................................................................................................139
Themes...................................................................................................................................... 139
Customising A Theme................................................................................................................. 140
Styles ......................................................................................................................................... 142
Conditional Formatting .............................................................................................................. 145
SECTION 8 OTHER EXCEL FEATURES.....................................................................150
INSERTING, FORMATTING AND DELETING OBJECTS..................................................................150
Inserting A Drawing Object ........................................................................................................ 150
SmartArt.................................................................................................................................... 151
WordArt .................................................................................................................................... 153
Formatting Shapes ..................................................................................................................... 153
QuickStyles................................................................................................................................ 153
Manual Formatting .................................................................................................................... 155
REVIEWING ............................................................................................................................157
Protecting.................................................................................................................................. 157
Use A Shared Workbook To Collaborate.................................................................................... 159
Share A Workbook ..................................................................................................................... 160
PROOFING TOOLS ...................................................................................................................166
Spelling And Grammar ............................................................................................................... 166
Thesaurus.................................................................................................................................. 167
Translation ................................................................................................................................ 167
Show Or Hide ScreenTips ........................................................................................................... 168
EXCEL 2007 SPECIFICATIONS AND LIMITS ............................................................169
Excel 2007 Advanced
© The Mouse Training Company
7
INTRODUCTION
Excel 2007 is a powerful spreadsheet application that allows users to produce tables containing calculations
and graphs. These can range from simple formulae through to complex functions and mathematical
models.
How To Use This Guide
This manual should be used as a point of reference following attendance of the introductory level Excel
2007 training course. It covers all the topics taught and aims to act as a support aid for any tasks carried out
by the user after the course.
The manual is divided into sections, each section covering an aspect of the introductory course. The table
of contents lists the page numbers of each section and the table of figures indicates the pages containing
tables and diagrams.
Objectives
Sections begin with a list of objectives each with its own check box so that you can mark off those topics
that you are familiar with following the training.
Instructions
Those who have already used a spreadsheet before may not need to read explanations on what each
command does, but would rather skip straight to the instructions to find out how to do it. Look out for the
arrow icon which precedes a list of instructions.
Appendices
The Appendices list the Ribbons mentioned within the manual with a breakdown of their functions and
tables of shortcut keys.
Keyboard
Keys are referred to throughout the manual in the following way:
[ENTER] – Denotes the return or enter key, [DELETE] – denotes the Delete key and so on.
Where a command requires two keys to be pressed, the manual displays this as follows:
[CTRL] + [P] – this means press the letter “p” while holding down the Control key.
Commands
When a command is referred to in the manual, the following distinctions have been made:
When Ribbon commands are referred to, the manual will refer you to the Ribbon – E.g. “Choose HOME
from the Ribbons and then B for bold”.
When dialog box options are referred to, the following style has been used for the text – “In the PAGE
RANGE section of the PRINT dialog, click the CURRENT PAGE option”
Dialog box buttons are shaded and boxed – “Click OK to close the PRINT dialog and launch the print.”
Notes
Excel 2007 Advanced
© The Mouse Training Company
8
Within each section, any items that need further explanation or extra attention devoted to them are
denoted by shading. For example:
“Excel will not let you close a file that you have not already saved changes to without prompting
you to save.”
Tips
At the end of each section there is a page for you to make notes on and a “Useful Information” heading
where you will find tips and tricks relating to the topics described within the section.
Excel 2007 Advanced
© The Mouse Training Company
9
SECTION 1 ADVANCED WORKSHEET FUNCTIONS
􀂾 Objectives
By the end of this section you will be able to:
• Create and use NAMES in workbooks
• Understand and use conditional formulae
• Set up LOOKUP tables and use LOOKUP functions
• Use the GOAL SEEK
• Use the SOLVER
Excel 2007 Advanced
© The Mouse Training Company
10
NAMES
When entering formulae or referring to any area on the spreadsheet, it is usual to refer to a "range". For
example, B6 is a range reference; B6:B10 is also a range reference. A problem with this sort of reference is
that it is not always easy to remember what cells to reference. It may be necessary to write down the
range, or select it, which often means wasting time scrolling around the spreadsheet. Instead, Excel offers
the chance to name ranges on the spreadsheet, and to use these names to select cells, refer to them in
formulae or use them in Database, Chart or Macro commands.
Defining Names
There are a number of ways to set up names on a spreadsheet. A common way is to use the Insert, Name,
Define menu. In the example, there is a range of sales figures that could be named “1st_Qtr”;
􀂾 To name cells:
Mouse
i. Select the cells you wish to name.
ii. Click the DEFINE NAME button on the in
the DEFINED NAMES GROUP on the
FORMULAS Ribbon
iii. The NEW NAME dialog box appears
Selection of
cells for
naming
DEFINED NAMES
group on the
FORMULAS ribbon
Excel 2007 Advanced
© The Mouse Training Company
11
iv. To name the cells, simply type a name in the Name box and choose OK.
OR
Keyboard
i. Select the cells you wish to name.
ii. Type directly in the NAME BOX to the left of the FORMULA BAR.
iii. Press RETURN
A range can include any selection of cells, not
necessarily a contiguous row or column. Names
can be up to 255 characters in length, must start
with a letter and cannot include spaces. Names
are not case sensitive.
In the example, these cells would be called
“1st_Qtr”. From now on, any reference to the
range C6:C10 can be made with the name
“1st_Qtr”; Notice that the name box, on the lefthand
side of the formula bar now displays the name “1st_Qtr”; It will do so whenever cells C6:C10 are
selected;
Creating Names Automatically
Alternatively, cells can be named using text already on the spreadsheet. For instance, in a spreadsheet,
column or row headings may have already been entered in the cells. B6 to B10 for example shows the
salesmen’s names and their respective sales quarterly this text can be used to name the cell ranges for their
sales
Excel 2007 Advanced
© The Mouse Training Company
12
􀂾 To create names automatically:
Mouse
i. Select the cells you wish to define names for,
include the data and the data labels in either
the first column or top row
ii. Click the CREATE FROM SELECTION button
on the in the DEFINED NAMES GROUP on the
FORMULAS Ribbon
iii. Select where your labels are. They must be
part of the selection can be in the top row or
left column.
iv. Choose OK and, all the salesmen’s names will appear in the
name box to the left of the formula bar and selecting their
name will select their individual sales figures
This procedure works equally well with text entered to the right of
a row of data. Or labels in the bottom of a column but THEY
MUST BE PART OF THE SELECTION.
Managing Names
The NAME MANAGER option in the group is a useful tool that allows you to create, modify or delete
names within your workbook even if the name refers to cells or ranges outside the present workbook.
Excel 2007 Advanced
© The Mouse Training Company
13
􀂾 To use name manager
Mouse
i. Click the NAME MANAGER BUTTON on the in the DEFINED NAMES GROUP on the FORMULAS
Ribbon.
ii. The NAME MANAGER Dialog box appears.
iii. To create new name use the NEW button.
iv. The NEW NAME dialog appears. The name manager temporarily dissappears until you click OK or
CANCEL
v. When the NEW NAME dialog box is there you must give a name, select a scope and click in the refers
to box.
vi. You may then select any cells in this workbook or ANY open workbook.
vii. You may then click on OK the dialog box will close, the NAME MANAGER will reappear and the named
cells will appear wilthin the large white area.
viii. Selecting any named range will allow you to edit or delete it by clicking on the enabled buttons at the
top of the dialog.
􀂾 Filtering out needed named ranges
Using the filter button allows some basic filtering of the names
within your workbook.
Don’t forget to clear the filter after you have what you want.
Scoping is a function where the names may be used on a
specific sheet or throughout the whole workbook. When
filtering the names you have it may be useful to set a scope if
you have many names on many sheets.
Excel 2007 Advanced
© The Mouse Training Company
14
USING NAMES
Go To
The GOTO feature can be used to go to a specific cell address on the
spreadsheet. It can also be used in conjunction with names.
􀂾 To GO TO a name:
Mouse
i. Click the FIND & SELECT BUTTON on the in the EDITING GROUP
on the HOME Ribbon.
ii. Select GO TO
iii. The following dialog appears
iv. Click on the name required, then choose OK.
Keyboard
i. Press [F5]. The following dialog box appears;
ii. Click on the name required, then choose OK.
Not only does the cell pointer move to the correct range, but it
also selects it. This can be very useful for checking that ranges
have been defined correctly, and also for listing all the names on the spreadsheet.
You can also go to a specific cell that has been used in two range names. The previous example
mentioned cell C6, the intersection of the FEB and Britain ranges.
􀂾 To move to a cell that belongs to two ranges:
Keyboard
i. Press [F5] and type the first range name in the Reference
box, then type a space and the second range name.
ii. Click OK. The pointer immediately jumps to the correct
cell.
􀂾 To go to locations in workbook based on
different criteria than names.
Keyboard
i. Press [F5] and click the SPECIAL button The following
dialog appears
ii. Make a selection and click on OK
Excel 2007 Advanced
© The Mouse Training Company
15
iii. All cells of those criteria will be selected.
iv. Use return or ribbon keys to move around.
Names In Formulae
Names can be used in any simple formula, as well as any of Excel's built‐in functions. Instead of typing cell
references or selecting cells, simply type the name or paste the name into the formula.
=SUM(LONG)
=AVERAGE(_1ST_QTR)
An intersecting name can be used, E.G.;
=LONG _1ST_QTR
= LONG _1ST_QTR + OLSON _1ST_QTR
To avoid typing a name, choose from a list and paste in the required
name.
􀂾 To paste a name into a formula:
Mouse
i. Click the USE IN FORMULA BUTTON on the in the DEFINED
NAMES group on the FORMULAS Ribbon
ii. Select a name
iii. The name is entered within the selected cell prefaced by an equals sign
OR
i. Click the USE IN FORMULA BUTTON on the in the DEFINED NAMES group on the FORMULAS
Ribbon
ii. Click on the paste names option at the bottom of menu
iii. Click on the required name and choose OK
Or
Keyboard
Press [F3]
i. Click on the required name and choose OK
Excel 2007 Advanced
© The Mouse Training Company
16
Applying Names
When a cell has already been referred to in a formula, and is then
named, the name will not automatically appear in the formula.
Similarly, if a cell is referred to by its address rather than its name,
the name will not automatically appear. To replace all references
with names, the names must be applied.
Suppose a formula is written to sum cells C7:F7
=SUM(C7:F7)
The formula makes no reference to the range "OLSON", even though this range has been named.
􀂾 To replace cell references with range
names:
Mouse
i. Click the drop down arrow next to DEFINE NAME
BUTTON on the in the DEFINED NAMES group on the
FORMULAS Ribbon;
ii. Select APPLY NAMES.
iii. Click on the name you want, and choose OK.
To apply other names at the same time, use [Ctrl] and click
on the required names. The formula will now show the
range names instead of the cell references.
The Apply Names command works throughout the
spreadsheet, so wherever the cell reference to the name
you chose appeared, the name is now in its place.
Excel 2007 Advanced
© The Mouse Training Company
17
CONDITIONAL & LOGICAL FUNCTIONS
Excel has a number of logical functions which allow you to set various "conditions" and have data respond
to them. For example, you may only want a certain calculation performed or piece of text displayed if
certain conditions are met. The functions used to produce this type of analysis are found in the Insert,
Function menu, under the heading LOGICAL.
If Statements
The IF function is used to analyse data, test whether or not it meets
certain conditions and then act upon its decision. The formula can be
entered either by typing it or by using the Function Library on the
formula’s ribbon, the section that deals with logical functions Typically,
the IF statement is accompanied by three arguments enclosed in one set
of parentheses; the condition to be met (logical_test); the action to be
performed if that condition is true (value_if_true); the action to be
performed if false (value_if_false). Each of these is separated by a
comma, as shown;
=IF ( logical_test, value_if_true, value_if_false)
􀂾 To view IF function syntax:
Mouse
i. Click the drop down arrow next to the LOGICAL button in the
FUNCTION LIBARY group on the FORMULAS Ribbon;
ii. A dialog box will appear
iii. The three arguments can be seen within the box
Excel 2007 Advanced
© The Mouse Training Company
18
Logical Test
This part of the IF statement is the "condition", or test. You may want to test to see if a cell is a certain
value, or to compare two cells. In these cases, symbols called LOGICAL OPERATORS are useful;
> Greater than
< Less than
> = Greater than or equal to
< = Less than or equal to
= Equal to
< > Not equal to
Therefore, a typical logical test might be B1 > B2, testing whether or not the value contained in cell B1 of
the spreadsheet is greater than the value in cell B2. Names can also be included in the logical test, so if cells
B1 and B2 were respectively named SALES and TARGET, the logical test would read SALES > TARGET.
Another type of logical test could include text strings. If you want to check a cell to see if it contains text,
that text string must be included in quotation marks. For example, cell C5 could be tested for the word YES
as follows; C5="YES".
It should be noted that Excel's logic is, at times, brutally precise. In the above example, the logical test is
that sales should be greater than target. If sales are equal to target, the IF statement will return the false
value. To make the logical test more flexible, it would be advisable to use the operator > = to indicate
"meeting or exceeding".
Value If True / False
Provided that you remember that TRUE value always precedes FALSE value, these two values can be almost
anything. If desired, a simple number could be returned, a calculation performed, or even a piece of text
entered. Also, the type of data entered can vary depending on whether it is a true or false result. You may
want a calculation if the logical test is true, but a message displayed if false. (Remember that text to be
included in functions should be enclosed in quotes).
Taking the same logical test mentioned above, if the sales figure meets or exceeds the target, a BONUS is
calculated (e.g. 2% of sales). If not, no bonus is calculated so a value of zero is returned. The IF statement
in column D of the example reads as follows;
=IF(B2>=C2,B2*2%,0)
Excel 2007 Advanced
© The Mouse Training Company
19
You may, alternatively, want to see a message saying "NO BONUS". In this case, the true value will remain
the same and the false value will be the text string "NO BONUS";
=IF(B2>=C2,B2*2%,"NO BONUS")
A particularly common use of IF statements is to produce "ratings" or "comments" on figures in a
spreadsheet. For this, both the true and false values are text strings. For example, if a sales figure exceeds
a certain amount, a rating of "GOOD" is returned, otherwise the rating is "POOR";
=IF(B2>1000,"GOOD","POOR")
Nested If
When you need to have more than one condition and more than two possible outcomes, a NESTED IF is
required. This is based on the same principle as a normal IF statement, but involves "nesting" a secondary
formula inside the main one. The secondary IF forms the FALSE part of the main statement, as follows;
=IF(1st logic test , 1st true value , IF(2nd logic test , 2nd true value , false value))
Only if both logic tests are found to be false will the false value be returned. Notice that there are two sets
of parentheses, as there are two separate IF statements. This process can be enlarged to include more
conditions and more eventualities ‐ up to seven IF's can be nested within the main statement. However,
care must be taken to ensure that the correct number of parentheses are added.
In the example, sales staff could now receive one of three possible ratings;
=IF(B2>1000,"GOOD",IF(B2<600,"POOR","AVERAGE"))
To make the above IF statement more flexible, the logical tests could be amended to measure sales against
cell references instead of figures. In the example, column E has been used to hold the upper and lower
sales thresholds.
=IF(B2>$E$2,"GOOD",IF(B2<$E$3,"POOR","AVERAGE"))
(If the IF statement is to be copied later, this cell reference should be absolute).
Excel 2007 Advanced
© The Mouse Training Company
20
N.B. The depth of nested IF functions has been increased to 64 as previous
versions of excel only nested 7 deep
Excel 2007 Advanced
© The Mouse Training Company
21
Statistical If Statements
A very useful technique is to display text or perform calculations only if a cell is the maximum or minimum
of a range. In this case the logical test will contain a nested statistical function (such as MAX or MIN). If, for
example, a person's sales cell is the maximum in the sales column, a message stating "Top Performer" could
appear next to his or her name. If the logical test is false, a blank message could appear by simply including
an empty set of quotation marks. When typing the logical test, it should be understood that there are two
types of cell referencing going on. The first is a reference to one person's figure, and is therefore relative.
The second reference represents the RANGE of everyone's figures, and should therefore be absolute.
=IF(relative cell = MAX(absolute range) , "Top Performer" , "")
In this example the IF statement for cell B2 will read;
=IF(C2=MAX($C$2:$C$4),"Top Performer","")
When this is filled down through cells B3 and B4, the first reference to the individual's sales figure changes,
but the reference to all three sales figures ($C$2:$C$4) should remain constant. By doing this, you ensure
that the IF statement is always checking to see if the individual's figure is the biggest out of the three.
A further possibility is to nest another IF statement to display a message if a value is the minimum of a
range. Beware of syntax here ‐ the formula could become quite unwieldy!
Excel 2007 Advanced
© The Mouse Training Company
22
AND, OR, NOT
Rather than create large and unwieldy formulae involving multiple IF statements, the AND, OR and NOT
functions can be used to group logical tests or "conditions" together. These three functions can be used on
their own, but in that case they will only return the values "TRUE" or "FALSE". As these two values are not
particularly meaningful on a spreadsheet, it is much more useful to combine the AND, OR and NOT
functions within an IF statement. This way, you can ask for calculations to be performed or other text
messages to appear as a result.
And
This function is a logical test to see if all conditions are true. If this is the case, the value "TRUE" is returned.
If any of the arguments in the AND statement are found to be false, the whole statement produces the
value "FALSE". This function is particularly useful as a check to make sure that all conditions you set are
met.
Arguments are entered in the AND statement in parentheses, separated by commas, and there is a
maximum of 30 arguments to one AND statement. The following example checks that two cells, B1 and B2,
are both greater than 100.
=AND(B1>100,B2>100)
If either one of these two cells contains a value less than a hundred, the result of the AND statement is
"FALSE.” This can now be wrapped inside an IF function to produce a more meaningful result. You may want
to add the two figures together if they are over 100, or display a message indicating that they are not high
enough.
=IF(AND(B1>100,B2>100),B1+B2,"Figures not high enough")
Another application of AND's is to check that a number is between certain limits. The following example
checks that a number is between 50 and 100. If it is, the value is entered. If not, a message is displayed;
=IF(AND(B1>50,B1<100),B1,"Number is out of range")
Excel 2007 Advanced
© The Mouse Training Company
23
Or
This function is a logical test to see if one or more conditions are true. If this is the case, the value "TRUE" is
returned. If just one of the arguments in the OR statement is found to be true, the whole statement
produces the value "TRUE". Only when all arguments are false will the value "FALSE" be returned. This
function is particularly useful as a check to make sure that at least one of the conditions you set is met.
=IF(OR(B1>100,B2>100),"at least one is OK","Figures not high enough")
In the above formula, only one of the numbers in cells B1 and B2 has to be over 100 in order for them to be
added together. The message only appears if neither figure is high enough.
Not
NOT checks to see if the argument is false. If so, the value "TRUE" is returned. It is best to use NOT as a
"provided this is not the case" function. In other words, so long as the argument is false, the overall
statement is true. In the example, the cell contents of B1 are returned unless the number 13 is
encountered. If B1 is found to contain 13, the message "Unlucky!" is displayed;
=IF(NOT(B1=13),B1,"Unlucky!")
The NOT function can only contain one argument. If it is necessary to check that more than one argument is
false, the OR function should be used and the true and false values of the IF statement reversed. Suppose,
for example, a check is done against the numbers 13 and 666;
=IF(OR(B1=13,B1=666),"Unlucky!",B1)
Excel 2007 Advanced
© The Mouse Training Company
24
LOOKUP FUNCTIONS
As already mentioned, Excel can produce varying results in a cell, depending on conditions set by you. For
example, if numbers are above or below certain limits, different calculations will be performed and text
messages displayed. The usual method for constructing this sort of analysis is using the IF function.
However, as already demonstrated, this can become large and unwieldy when you want multiple conditions
and many possible outcomes. To begin with, Excel can only nest seven IF clauses in a main IF statement,
whereas you may want more than eight logical tests or "scenarios.” To achieve this, Excel provides some
LOOKUP functions. These functions allow you to create formulae which examine large amounts of data and
find information which matches or approximates to certain conditions. They are simpler to construct than
nested IF’s and can produce many more varied results.
Lookup
Before you actually start to use the various LOOKUP functions, it is worth learning the terms that you will
come across, what they mean and the syntax of the function arguments.
Vector Lookup
A vector is a series of data that only occupies one row or column. LOOKUP will look through this row or
column to find a specific value. When the value is found, a corresponding "result" in the adjacent row or
column is returned. For example, column D of a spreadsheet may contain figures, and the adjacent column
E contains corresponding text. LOOKUP will search for the requested figure in column D and return the
corresponding text from column E.
The syntax for LOOKUP is as follows;
=LOOKUP( lookup_value , lookup_vector , result_vector )
The lookup_value represents the number or text entry to look for; the lookup_vector is the area in which to
search for the lookup_value; the result_vector is the adjacent row or column where the corresponding
value or text is to be found.
It is essential that data in the lookup vector is placed in ascending order, i.e. numbers from lowest
to highest, text from A to Z. If this is not done, the LOOKUP function may return the wrong result.
Excel 2007 Advanced
© The Mouse Training Company
25
In the diagram, column D contains varying salaries, against which there is a company car in column E which
corresponds to each salary. For example, a £20030 salary gets a Golf, a £35000 salary gets a Scorpio. A
LOOKUP formula can be used to return whatever car is appropriate to a salary figure that is entered. In this
case, the lookup_value is the cell where the salary is entered (B13), the lookup_vector is the salary column
(D3:D11), and the result_vector is the car column (E3:E11). Hence the formula;
=LOOKUP(B13,D3:D11,E3:E11)
Typing 40000 in cell B13 will set the lookup_value. LOOKUP will search through the lookup_vector to find
the matching salary, and return the appropriate car from the result_vector, which in this case is Mercedes.
Alternatively, the formula could be simplified and cell references avoided by using Formula, Define Name to
give appropriate range names. Call B13 Salary, D3:D11 Salaries and E3:E11 Cars. The LOOKUP formula could
then be simplified to;
=LOOKUP(Salary,Salaries,Cars)
One of the advantages of the LOOKUP function is that if the exact lookup_value is not found, it will
approximate to the nearest figure below the requested value. For instance, if a user enters a Salary of
23000, there is no figure in the Salaries range which matches this. However, the nearest salary below 23000
is 20030, so the corresponding car is returned, which is a Golf. This technique is very useful when the
lookup_vector indicates grades or "bands.” In this case, anyone in the salary "band" between 20030 and
25000 gets a Golf. Only when their salary meets or exceeds 25000 do they get a Sierra.
There may be occasions where the lookup_value is below the lowest
value in the vector. In this case the #N/A message is displayed.
Location of all
REFERENCE and
LOOKUP
functions
Typical layout of
a sheet needing
a LOOKUP
function
Excel 2007 Advanced
© The Mouse Training Company
26
􀂾 To insert a lookup function:
Mouse
i. Click the drop down arrow next to the LOOKUP AND REFENCE button in the FUNCTION LIBARY
group on the FORMULAS Ribbon;
ii. A dialog box appears displaying the two versions of LOOKUP. There are two syntax forms; the first is the
"array" and the second the "vector.”
The second of these forms, the "vector" LOOKUP is by far the most useful, and it is recommended
that you only use this form.
Hlookup
The horizontal LOOKUP function (HLOOKUP) can be used not just on a "vector" (single column or row of
data), but on an "array" (multiple rows and columns). HLOOKUP searches for a specified value horizontally
along the top row of an array. When the value is found, HLOOKUP searches down to a specified row and
enters the value of the cell. This is useful when data is arranged in a large tabular format, and it would be
difficult for you to read across columns and then down to the appropriate cell. HLOOKUP will do this
automatically.
The syntax for HLOOKUP is;
=HLOOKUP( lookup_value , table_array , row_index_number)
The lookup_value is, as before, a number, text string or cell reference which is the value to be found along
the top row of the data; the table_array is the cell references (or range name) of the entire table of data;
the row_index_number represents the row from which the result is required. This must be a number, e.g. 4
instructs HLOOKUP to extract a value from row 4 of the table_array.
It is important to remember that data in the array must be in ascending order. With a simple LOOKUP
function, only one column or row of data, referred to as a vector, is required. HLOOKUP uses an array (i.e.
more than one column or row of data). Therefore, as HLOOKUP searches horizontally (i.e. across the array),
data in the first row must be in ascending order, i.e. numbers from lowest to highest, text from A to Z. As
with LOOKUP, if this rule is ignored, HLOOKUP will return the wrong value.
As an example, a user may have a spreadsheet which displays various different rates of interest for a range
of amounts over different time periods;
Excel 2007 Advanced
© The Mouse Training Company
27
Whatever the amount a customer wants to borrow, he may pay up to five different rates of interest
depending on whether the loan is over 10, 15 or more years. The HLOOKUP function will find a specific
amount, then move down the array to find the appropriate interest rate for the required time period.
Designate cell A51 as the cell to hold the amount, i.e. the lookup_value; cells C43:H48 are the table_array;
the row_index_number will be 2 if a customer wants the loan over 10 years, 3 if he wants the loan over 15
years, and so on. Cell B51 holds this formula;
=HLOOKUP(A51,C43:H48,3)
The above formula looks along the top row of the array for the value in cell A51 (30000). It then moves
down to row 3 and returns the value 15.00%, which is the correct interest rate for a £30000 loan over 15
years. (Range names could be used here to simplify the formula).
As with the LOOKUP function, the advantage of HLOOOKUP is that it does not necessarily have to find the
exact lookup_value. If, for example, you wanted to find out what interest rate is applicable to a £28000
loan, the figure 28000 can be entered in the lookup_value cell (A51) and the rate 14.30% appears. As
before, Excel has looked for the value in the array closest to, but lower than, the lookup_value.
Vlookup
The VLOOKUP function works on the same principle as HLOOKUP, but instead of searching horizontally,
VLOOKUP searches vertically. VLOOKUP searches for a specified value vertically down the first column of an
array. When the value is found, VLOOKUP searches across to a specified column and enters the value of the
cell. The syntax for the VLOOKUP function follows the same pattern as HLOOKUP, except that instead of
specifying a row index number, you would specify a column index number to instruct VLOOKUP to move
across to a specific column in the array where the required value is to be found.
=VLOOKUP( lookup_value , table_array , col_index_number )
In the case of VLOOKUP, data in the first column of the array should be in ascending order, as VLOOKUP
searches down this column for the lookup_value.
In the same spreadsheet as before, a VLOOKUP formula could be used to search for a specific time period,
then return the appropriate rate for a fixed amount. In the following example, a time period is entered in
cell A54 and in B54 the VLOOKUP formula is contained;
Excel 2007 Advanced
© The Mouse Training Company
28
Cell B54 holds this formula;
=VLOOKUP(A54,C43:H48,5)
The cell A54 is the lookup_value (time period), the table_array is as before, and for this example rates are
looked up for a loan of £40000, hence the column_index_number 5. By changing the value of cell A54, the
appropriate rate for that time period is returned. Where the specific lookup_value is not found, VLOOKUP
works in the same way as HLOOKUP. In other words, the nearest value in the array that is less than the
lookup_value will be returned. So, a £40000 loan over 17 years would return an interest rate of 16.00%.
Excel 2007 Advanced
© The Mouse Training Company
29
Nested Lookups
One of the limitations of the horizontal and vertical LOOKUP functions is that for every lookup_value
changed, the column or row index number stays constant. Using our example, the HLOOKUP will search for
any amount, but always for the same time period. Conversely, the VLOOKUP will search for any time period,
but always for the same amount. In both cases, if you want to alter the time period and the amount the
formula must be edited to alter the column or row index number.
There is, however, a technique whereby one LOOKUP function is "nested" within another. This looks up one
value, which will then be used in a second LOOKUP formula as a column or row index number. Using this
technique allows you to, say, enter a time period and an amount and see the correct interest rate.
Because nested LOOKUPs have more than one lookup_value, more than one array is needed. This second
array should consist of the column or row numbers to be used in the LOOKUP formula. The example shows
our main interest rates spreadsheet, with an additional two columns of data;
Column J contains all the same time periods as column C, but alongside this are numbers 2 to 6, indicating
the row_index_number to be returned for the appropriate time period. To look up this value, use a simple
vector LOOKUP formula, where K50 is the required time period, J43:J47 is the lookup_vector and K43:K47 is
the result_vector;
LOOKUP(K50,J43:J47,K43:K47)
Notice there is no equals sign, because this formula is not being entered in a cell of its own. The formula will
return a value between 2 and 6 which will be used as a row_index_number in a HLOOKUP formula. This
HLOOKUP will look in the main interest rate table for an amount typed in by you, and will respond to the
row_index_number returned from the nested LOOKUP formula. The cells J50 and K50 hold the amount and
time period to be typed in by you, and the entire nested HLOOKUP, typed in J52, is as follows;
=HLOOKUP(J50,C43:H48,LOOKUP(K50,J43:J47,K43:K47))
In the example, the time period 25 is vertically looked up in column J and the corresponding value 5 is
returned. Also, the amount 40000 is horizontally looked up in the main table, with a row_index_number of
Excel 2007 Advanced
© The Mouse Training Company
30
5. The end result is an interest rate of 18.50%. Simply by changing cells J50 and K50, the correct interest
rate is always returned for the amount and period typed in.
Excel 2007 Advanced
© The Mouse Training Company
31
OTHER USEFUL FUNCTIONS
ISERROR
ISERROR is a very useful function that tells you if the formula you look at with it gives any error value.
Iserror(Value)
Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
􀂾 To use ISERROR function
In the example below the average functions in the column G is trying to divide empty cells and giving the
error message #DIV/0! The error function checking that cell gives the value true there is an error this could
be nested in an IF function with an AVERAGE function so that the error message does not show in column
G
Mouse
iii. Click on MORE FUNCTIONS in the FORMULAS group on the FORMULAS ribbon
iv. Select ISERROR function
v. The dialog box above will appear
vi. Select cell you wish to check, the cell reference will appear in the VALUE box.
vii. Click OK
For more advanced users try nesting the ISERROR function and the function giving an error message in an IF
function.
Excel 2007 Advanced
© The Mouse Training Company
32
IFERROR
IFERROR(Value, value_if_error)
A common request in the area of functions is something to simplify error checking.
E.G. If a user wants to catch errors in a VLOOKUP and use their own error text opposed to Excel’s error,
they have to do something like this using the IF and ISERROR functions:
=IF(ISERROR(VLOOKUP("Dave", SalesTable, 3, FALSE)), " Value not found",
VLOOKUP("Dave", SalesTable, 3, FALSE))
As you can see, users need to repeat the VLOOKUP formula twice. This has a number of problems.
FIRST, it is hard to read and hard to maintain – if you want to change a formula, you have to do it twice.
SECOND, it can affect performance, because formulas are quite often run twice. The IFERROR function
solves these problems, enabling customers to easily trap and handle formula errors.
Here is an example of how a user could use it in the same situation:
=IFERROR(VLOOKUP(“Bob”, SalesTable, 3, false), “Value not found”)
􀂾 To use IFERROR function
Mouse
viii. Click on LOGICAL in the FORMULAS group on the FORMULAS ribbon
ix. Select IFERROR function
x. The dialog box above will appear
xi. Click in the VALUE text box.
xii. Select cell you wish to check, the cell reference will appear in the VALUE box.
xiii. Type in the VALUE_IF_ERROR text box what value you wish to display if an error is found
xiv. Click OK
Excel 2007 Advanced
© The Mouse Training Company
33
COUNTIF
COUNTIF counts the number of cells in a range based on agiven criteria.
COUNTIF(range,criteria)
RANGE is one or more cells to count, including numbers or names, arrays, or references that contain
numbers. Blank and text values are ignored.
CRITERIA is the criteria in the form of a number, expression, cell reference, or text that defines which cells
will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
􀂾 To use COUNTIF function
Mouse
i. Click on the MORE FUNCTIONS button in the FORMULAS group on the FORMULAS ribbon
ii. Click on STATISTICAL.
iii. Select COUNTIF from the displayed functions. A dialog will be displayed
iv. Click in RANGE text box
v. Select the range of cells you wish to check.
vi. Click in the CRITERIA box, either, type criteria directly in the box or select a cell that contains the value
you wish to count.
Excel 2007 Advanced
© The Mouse Training Company
34
vii. Click OK
Excel 2007 Advanced
© The Mouse Training Company
35
AVERAGEIF
A very common request is for a single function to conditionally average a range of numbers – a complement
to SUMIF and COUNTIF. AVERAGEIF, allows users to easily average a range based on a specific criteria.
AVERAGEIF(Range, Criteria, [Average Range])
RANGE is one or more cells to average, including numbers or names, arrays, or references that contain
numbers.
CRITERIA is the criteria in the form of a number, expression, cell reference, or text that defines which cells
are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
AVERAGE_range is the actual set of cells to average. If omitted, RANGE is used.
Here is an example that returns the average of B2:B5 where the corresponding value in column A is greater
than 250,000:
=AVERAGEIF(A2:A5, “>250000”, B2:B5)
􀂾 To use AVERAGEIF function
Mouse
viii. Click on the MORE FUNCTIONS button in the FORMULAS group on the FORMULAS ribbon and Click
on STATISTICAL.
ix. Select AVERAGEIF from the displayed functions. A dialog will be displayed
x. Click in RANGE text box
xi. Select the range of cells containing the .values you wish checked against the criteria.
xii. Click in the CRITERIA box, either, type criteria directly in the box or select a cell that contains the value
you wish to check the range against
xiii. Click in the AVERAGE_RANGE text box and select the range you wish to average..
xiv. Click OK
Excel 2007 Advanced
© The Mouse Training Company
36
AVERAGEIFS
Average ifs is a new function to excel and does much the same as the AVERAGEIF function but it will
average a range using multiple criteria.
􀂾 To use AVERAGEIFS function
Mouse
xv. Click on the MORE FUNCTIONS button in the FORMULAS group on the FORMULAS ribbon and Click
on STATISTICAL.
xvi. Select AVERAGEIFS from the displayed functions. A dialog will be displayed
xvii. Click in AVERAGE_RANGE text box
xviii. Select the range of cells containing the .values you wish checked against the criteria.
xix. Click in the CRITERIA_RANGE1 box select a range of cells that contains the values you wish to check
the criteria against
Excel 2007 Advanced
© The Mouse Training Company
37
xx. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
xxi. Repeat steps 5 and 6 to enter multiple criteria, range2, range3 etc, use the scroll bar on the right to
scroll down and locate more range and criteria text boxes. Click OK when all ranges and criterias have
been entered.
Some important points about AVERAGEIFS FUNCTION
• If AVERAGE_RANGE is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
• If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
• Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0
(zero).
• Each cell in AVERAGE_RANGE is used in the average calculation only if all of the corresponding
criteria specified are true for that cell.
• Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each
CRITERIA_RANGE must be the same size and shape as SUM_RANGE.
• If cells in AVERAGE_RANGE cannot be translated into numbers, AVERAGEIFS returns the #DIV0!
error value.
• If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark
matches any single character; an asterisk matches any sequence of characters. If you want to find
an actual question mark or asterisk, type a tilde (~) before the character.
SUMIFS
This function adds all the cells in a range that meets multiple criteria.
The order of arguments is different between SUMIFS and SUMIF. In particular, the SUM_RANGE
argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and
editing these similar functions, make sure you put the arguments in the correct order.
SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
SUM_RANGE is one or more cells to sum, including numbers or names, arrays, or references that contain
numbers. Blank and text values are ignored.
CRITERIA_RANGE1, CRITERIA_RANGE2, are 1 to 127 ranges in which to evaluate the associated criteria.
CRITERIA1, CRITERIA2, …are 1 to 127 criteria in the form of a number, expression, cell reference, or text
that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or
B4.
Some important points about SUMIFS
Each cell in SUM_RANGE is summed only if all of the corresponding criteria specified are true for that cell.
Cells in SUM_RANGE that contain TRUE evaluate as 1; cells in SUM_RANGE that contain FALSE evaluate
as 0 (zero).
Excel 2007 Advanced
© The Mouse Training Company
38
Unlike the range and criteria arguments in the SUMIF function, in SUMIFS each CRITERIA_RANGE must
be the same size and shape as SUM_RANGE.
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches
any single character; an asterisk matches any sequence of characters. If you want to find an actual question
mark or asterisk, type a tilde (~) before the character.
􀂾 To use SUMIFS function
Mouse
xxii. Click on the MATH & TRIG button in the FORMULAS group on the FORMULAS ribbon.
xxiii. Select SUMIFS from the displayed functions. A dialog will be displayed
xxiv. Click in SUM_RANGE text box
xxv. Select the range of cells containing the .values you wish to sum up
xxvi. Click in the CRITERIA_RANGE1 box select a range of cells that contains the values you wish to check
the criteria against
xxvii. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
xxviii. Repeat steps 5 and 6 to enter multiple criteria, range2, range3 etc, as you use each CRITERIA_RANGE
and CRITERIA more text boxes will appear for you to use. Click OK when all ranges and criterias have
been entered.
Excel 2007 Advanced
© The Mouse Training Company
39
COUNTIFS
The COUNTIFS function, counts a range based on multiple criteria.
COUNTIFS(range1, criteria1,range2, criteria2…)
RANGE1, RANGE2, … are 1 to 127 ranges in which to evaluate the associated criteria. Cells in each range
must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
CRITERIA1, CRITERIA2, …are 1 to 127 criteria in the form of a number, expression, cell reference, or text
that define which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples",
or B4.
􀂾 To use COUNTIFS function
Mouse
xxix. Click on the MORE FUNCTIONS button in the FORMULAS group on the FORMULAS ribbon and click
on STATISTICAL.
xxx. Select COUNTIFS from the displayed functions. A dialog will be displayed
xxxi. Click in the CRITERIA_RANGE1 box select the range of cells that you wish to count.
xxxii. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
xxxiii. Repeat step 4 to enter multiple criteria, criteria_range2, range3 etc, as you use each CRITERIA_RANGE
and CRITERIA more text boxes will appear for you to use. Click OK when all ranges and criterias have
been entered.
Excel 2007 Advanced
© The Mouse Training Company
40
Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell.
If criteria is an empty cell, COUNTIFS treats it as a 0 value.
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches
any single character; an asterisk matches any sequence of characters. If you want to find an actual question
mark or asterisk, type a tilde (~) before the character.
Excel 2007 Advanced
© The Mouse Training Company
41
DATA CONSOLIDATION
To summarize and report results from separate worksheets, you can consolidate data from each separate
worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet
or in other workbooks. When you consolidate data, you are assembling data so that you can more easily
update and aggregate it on a regular or ad hoc basis.
E.G. If you have a worksheet of sales figures for each of your divisional offices, you might use a
consolidation to roll up these figures into a corporate sales worksheet. This master worksheet (All divisions)
might contain sales totals or averages for the entire enterprise.
􀂾 To consolidate data
Mouse
xxxiv. Name a new sheet to summarise your aggregate data go to the top left hand cell on that sheet where
you would like to start aggregate your data. In example above the cell would possibly be A3 on all
divisions sheet.)
Make sure that you leave enough cells to the right and below this cell for the consolidated data.
The CONSOLIDATE command populates the area as needed
xxxv. On the DATA ribbon in the DATA TOOLS group, click on consolidate the CONSOLIDATE dialog box is
displayed.
Excel 2007 Advanced
© The Mouse Training Company
42
xxxvi. From the FUNCTION drop down box select which function you wish to apply to the consolidated data
ranges (default is sum)
xxxvii. Click in REFERENCE text box. Select one of the sheets you wish to consolidate and select the data on
that sheet the range will appear in the REFERENCE box you will notice it is absolute.
If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close
the Browse dialog box. The file path is entered in the Reference box followed by an exclamation
point.
Alternatively,(and easier) ensure all workbooks that have sheets to be consolidated are already
open. Then just like selecting the range click in the REFERENCE box, then select the workbook
from the TASKBAR, select the sheet then the range and click on ADD.
Be consistent with your selections although the ranges may be in different locations on different
sheets, either, select the labels on ALL THE SHEETS with the data to be consolidated OR NONE AT
ALL.
xxxviii. Click on ADD the range will appear in the ALL REFERENCES box if the wrong range has been selected
select it in the ALL REFERENCES box and click on DELETE.
xxxix. Repeat the previous two steps until all data ranges to be consolidated have been selected and added to
ALL REFERENCES box.
xl. If you selected labels in your data ranges then tick the check boxes in the USE LABELS IN area. Select
TOP ROW and/or LEFT COLUMN.
xli. The data when consolidated can be automatically updateable by selecting the check box CREATE LINKS
TO SOURCE DATA. This ensures as your data changes so will your consolidated data.
You can only select this check box if the worksheet is in another workbook. Once you select this
check box, you won't be able to change which cells and ranges are included in the consolidation.
To set up the consolidation so that you can update the consolidation manually by changing the
included cells and ranges, clear the Create links to source data check box.
xlii. If you are satisfied with all ranges selected and options selected, click OK.
Excel 2007 Advanced
© The Mouse Training Company
43
xliii. The data will be consolidated onto your summary worksheet
xliv. Data is consolidated
If you selected the CREATE LINKS TO SOURCE DATA check box your data will be automatically
outlined (see left and below NAME BOX) use these outlines as you would in SUBTOTALS. The
CREATE LINKS TO SOURCE DATA check box works best when consolidating across workbooks as
you can see in the above picture across sheets enters the sheet name in the B column repeatedly
you would have to change this manually but across workbooks the filename is entered there to
inform you of the source of the data.
If you did not use the CREATE LINKS TO SOURCE DATA check box then the data will be
consolidated and put into your cells as values (averaged or summed as you chose)
Excel 2007 Advanced
© The Mouse Training Company
44
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER
By the end of this section you will be able to:
• Create Views
• Use Goal seek and solver
• Switch between Views
• Create Scenarios
• Switch between Scenarios
Excel 2007 Advanced
© The Mouse Training Company
45
GOAL SEEKING AND SOLVING
Excel has a number of ways of altering conditions on the spreadsheet and making formulae produce
whatever result is requested. Excel can also forecast what conditions on the spreadsheet would be needed
to optimise the result of a formula. For instance, there may be a profits figure that needs to be kept as high
as possible, a costs figure that needs to be kept to a minimum, or a budget constraint that has to equal a
certain figure exactly. Usually, these figures are formulae that depend on a great many other variables on
the spreadsheet. Therefore, you would have to do an awful lot of trial‐and‐error analysis to obtain the
desired result. Excel can, however, perform this analysis very quickly to obtain optimum results. The Goal
Seek command can be used to make a formula achieve a certain value by altering just one variable. The
Solver can be used for more painstaking analysis where many variables could be adjusted to reach a desired
result. The Solver can be used to not only obtain a specific value, but also to maximise or minimise the
result of a formula (e.g. maximise profits or minimise costs).
Goal Seek
The Goal Seek command is used to bring one
formula to a specific value. It does this by changing
one of the cells that is referenced by the formula.
Goal Seek asks for a cell reference that contains a
formula (the Set cell). It also asks for a value, which
is the figure you want the cell to equal. Finally,
Goal Seek asks for a cell to alter in order to take
the Set cell to the required value.
In the example spreadsheet, cell B8 contains a
formula that sums advertising and payroll costs.
Cell B10 contains a profits formula based on the
revenue figure, minus the total costs.
A user may want to see how a profit of 4000 can be
achieved by altering payroll costs.
􀂾 To launch the Goal seeker:
Mouse
i. On the DATA ribbon, DATA TOOLS group, click WHAT‐IF ANALYSIS and then click GOAL SEEK.
ii. In the SET CELL box, enter the reference for the cell that contains the formula result you wish to set to a
specific figure. (In the example, this is cell B4.)
iii. In the TO VALUE box, type the result you want. (In the
example, this is ‐4000.)
iv. In the BY CHANGING CELL box, enter the reference for
the cell that contains the value you want to adjust. (In
the example, this is cell B3.)
The Goal Seek command automatically suggests the active
cell as the Set cell. This can be overtyped with a new cell
reference or you may click on the appropriate cell on the
Excel 2007 Advanced
© The Mouse Training Company
46
spreadsheet.
v. Click the OK button and the spreadsheet will alter the cell to a value sufficient for the formula to reach
your goal. Goal Seek also informs you that the goal was achieved;
vi. You now have the choice of accepting the revised spreadsheet, or returning to the previous values. Click
OK to keep the changes, or CANCEL to restore previous values.
Goal Seek can be used repeatedly in this way to see how revenue or other costs could be used to
influence the final profits. Simply repeat the above process and alter the changing cell reference.
The changing cell must contain a value, not a formula. For example, if you tried to alter profits by
changing total costs, this cell contains a formula and Goal Seek will not accept it as a changing cell.
Only the advertising costs or the payroll cells can be used as changing cells.
Goal Seek will only accept one cell reference as the changing cell, but names are acceptable. For
instance, if a user had named either cells B5 or B6 as "Advert_costs" or "Payroll" respectively,
these names could be typed in the BY CHANGING CELL box. For goal‐seeking with more than one
changing cell, use the Solver.
Excel 2007 Advanced
© The Mouse Training Company
47
Solver
For more complex trial‐and‐error analysis the Excel Solver should be used.
Unlike Goal Seek, the Solver can alter a formula not just to produce a set
value, but also to maximise or minimise the result. More than one changing
cell can be specified, so as to increase the number of possibilities, and
constraints can be built in to restrict the analysis to operate only under
specific conditions.
The basis for using the Solver is usually to alter many figures to produce the
optimum result for a single formula. This could mean, for example, altering
price figures to maximise profits. It could mean adjusting expenditure to minimise costs, etc. Whatever the
case, the variable figures to be adjusted must have an influence, either, directly or indirectly, on the overall
result, that is to say the changing cells must affect the formula to be optimised. Up to 200 changing cells can
be included in the solving process, and up to 100 constraints can be built in to limit the Solver's results.
Solver Parameters
The Solver needs quite a lot of information in order for it to be able to come up with a realistic solution.
These are the Solver parameters
􀂾 To set up the Solver:
Mouse
i. Click SOLVER button on the in the ANALYSIS group on the DATA Ribbon;
Like Goal Seek, the Set Cell is the cell containing the formula whose value is to be optimised. Unlike
Goal Seek, however, the formula can be maximised or minimised as well as set to a specific value.
ii. Decide which cells the Solver should alter in order to produce the Set Cell result. You can either type or
click on the appropriate cells, and [Ctrl] click if non‐adjacent cell references are required.
Excel 2007 Advanced
© The Mouse Training Company
48
When using a complex spreadsheet, or one that was created by someone else, there is an option to
let the Solver guess the changing cells. Usually it will select the cells containing values that have an
immediate effect on the Set Cell, so it may be a good idea to amend this.
Constraints
Constraints prevent the Solver from coming up with unrealistic solutions.
􀂾 To build constraints into your Solver parameters:
Mouse
i. In the Solver dialog, choose ADD
ii. This dialog box asks you to choose a cell whose value will be kept within certain limits. It can be any cell
or cells on the spreadsheet (simply type the reference or select the range).
iii. This cell can be subjected to an upper or lower limit, made to equal a specific value or forced to be a
whole number. Drop down the arrow in the centre of the Constraint box to see the list of choices:‐ To
set an upper limit, click on the <= symbol; for a lower limit, >=; the = sign for a specific value and the INT
option for an integer (whole number).
iv. Once the OK button is chosen, the Solver Parameter dialog box re displays and the constraint appears in
the window at the bottom. This constraint can be amended using the Change button, or removed using
the Delete button.
When maximising or minimising a formula value, it is important to include constraints which set upper or
lower limits on the changing values. For instance, when maximising profits by changing sales figures, the
Solver could conceivably increase sales to infinity. If the sales figures are not limited by an upper constraint,
the Solver will return an error message stating that the cell values do not converge. Similarly, minimising
total costs could be achieved by making one of the contributing costs infinitely less than zero. A constraint
should be included, therefore, to set a minimum level on these values.
The example here shows the number of cases for five London hospitals, split into three types: Elective,
Emergency and Day cases. Below this are the respective costs of each type of case for each hospital, and
finally the total costs (number multiplied by price) for each type in each hospital. All these figures are
totalled in column H, to arrive at a final total costs figure in cell H17.
Excel 2007 Advanced
© The Mouse Training Company
49
Call up SOLVER from the ANALYSIS group on the DATA ribbon. The Set Cell in this case will be H17, the
total costs cell. It can be assumed that the costs of cases cannot be changed, only the number in each
hospital, therefore the changing cells will be B4:F6:
The problem is that, if Solve is
chosen now, the number of cases
could reduce to infinitely below
zero and produce an error message.
Fortunately, constraints can be built
in to force each hospital to have a
minimum number of cases, and for
there to be a total number of cases
overall. Choose the ADD option to
add a constraint, highlight the cells
B4:F6, drop down the arrow and
click on >= to set a minimum limit.
Here, type whatever the minimum
number of cases should be. To
avoid the error message, simply type 1 and choose OK. Also, as hospital cases cannot be fractions, add
another constraint to force these cells to be integers. Finally, a constraint could be added to set a total
number of cases (cell H7). The Solver parameters should now appear as follows:
When Solve is chosen, the Solver
carries out its analysis and finds a
solution. This may be unsatisfactory,
as it has calculated that the best way
to minimise costs is to put the
majority of cases in St George's as
day patients. Further constraints
could now be added to force the
Solver to place minimum numbers of
cases in the other hospitals, or set a
maximum limit on St Georges' day
cases.
Excel 2007 Advanced
© The Mouse Training Company
50
Solver And Scenario Manager
The Solver uses sophisticated numeric
analysis and iterative methods to
perform trial‐and‐error calculations
very quickly. The original values of the
spreadsheet, therefore, have a
profound effect on the result of a Solver
solution. It may be the case that there
is no concrete solution to a spreadsheet
problem, and the Solver may produce a
"best guess" within the specified
constraints. Changing the original
values and running the Solver again
may produce a different result, and it
may therefore by helpful to save the
different "scenarios" that are produced.
It may also be necessary to save
scenarios where constraints have either
been added, removed or amended.
Using Excel's Scenario Manager can
facilitate this, by allowing you to save
each new Solver solution and compare
it to previous ones. For most complex
spreadsheet problems, the Solver and Scenario Manager are used together.
Opening Scenario Manager
􀂾 To open scenario manager
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon; ( the text “what if analysis” will be missing if solver has been added in to Excel)
ii. Scenario manager will appear.
Excel 2007 Advanced
© The Mouse Training Company
51
Saving Solver Solutions
When the Solver finds a solution that seems feasible, you may want to save it.
􀂾 Save a solution as a Scenario:
Mouse
i. Once Solver has found a solution, choose SAVE SCENARIO from the dialog box. The scenario can be
named and either the new values kept or the original values restored.
􀂾 To view a saved Scenario:
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon; ( the text “what if analysis” will be missing if solver has been added in to Excel)
ii. Scenario manager will appear. All named scenarios will be listed.
iii. Click on the appropriate name and choose SHOW to display the results. Typically, a scenario only holds
the results of the changing cells set in any Solver Parameters. Choosing Edit from the Scenario Manager
allows these values to be changed, or the Scenario renamed.
Excel 2007 Advanced
© The Mouse Training Company
52
Reports
Once a Solver solution is obtained, a report can be generated to summarise the changes that have been
made and how accurately the constraints have been satisfied. When changing cells have integer constraints,
the only meaningful type of report is an Answer Report, which gives details of the target cell's original value
and new value, the changes to the adjustable cells as well as all constraints.
􀂾 To create a solver report:
Mouse
i. Click Answer Report from the Solver Solution dialog box. Excel generates the report in a new sheet
behind the current worksheet.
ii. To see the report, choose the answer report ribbon: behind current worksheet
Excel 2007 Advanced
© The Mouse Training Company
53
VIEWS & SCENARIOS
Excel's Custom Views are used to save and recall different display settings and print options. The Scenario
Manager allows you to store changes to spreadsheet data and perform "what if" analysis.
Custom Views
Custom views allow you to save different display and print settings, and impose them quickly and easily on
the worksheet at any time. The settings which can be saved include print settings, row heights and column
widths, display settings, selected cells, window size and positions, settings for panes and frozen titles. This
can be advantageous when dealing with large worksheets where switching from one area to another might
otherwise be awkward. It also allows a number of different print settings ‐ including print area ‐ to be saved
as part of the same worksheet file.
Typical Custom View Model
In the example above, Sales, Costs and Profits data can be seen at the top of the screen, with an embedded
line chart underneath. There is also an embedded pie chart, which, at the moment, can only be seen by
scrolling down the spreadsheet. It would be useful to be able to "swap" between the line chart and the pie
chart while still able to see the spreadsheet figures. It may also be helpful to define different page settings,
depending on whether the line chart or pie chart is being printed. By defining different spreadsheet
"views,” it is possible to toggle between the different charts and keep the data on the screen at all times. It
is also possible to print a different header when a different type of chart is displayed.
Excel 2007 Advanced
© The Mouse Training Company
54
Defining A View
Before defining a view, you should ensure that the display options, zoom percentage, print settings etc. are
as you wish to record them.
􀂾 To create a view:
Mouse
i. Click View from the menu bar and choose Custom Views. The following dialog box will appear:
ii. From the Custom Views dialog box, choose ADD.
iii. Choose whether or not to include print settings or hidden rows and columns as part of the View by
checking the required options.
iv. Enter the name under which this view is to be saved and click on OK.
Once a view has been defined, the display and print settings of the worksheet can be changed (for example
in our typical model you may want a view to display the pie chart next to the data with an appropriate
header when printing). You can then set up a View that would save those settings.
Excel 2007 Advanced
© The Mouse Training Company
55
Showing A View
Having defined as many views as are needed for the current worksheet, you can switch between them.
􀂾 To show a view:
Mouse
i. Click on the VIEW ribbon WORKBOOK VIEWS group, and CUSTOM VIEWS.
ii. Click on the SHOW button. If Print or Print Preview commands are executed, the correct settings
(including the header) will be apply to each view.
􀂾 To delete a view:
Mouse
i. Click on the VIEW ribbon WORKBOOK VIEWS group, and CUSTOM VIEWS.
ii. Click the DELETE button.
Scenario Manager
The purpose of the Scenario Manager is to allow you to save a number of alternative inputs for specific cells
which affect the results in a worksheet. For example, you may want to see the results of changes in costs
figures, and their impact on profits. A variety of different costs figures could be saved as different
"scenarios,” and each one loaded in turn to produce comparisons.
Load The Scenario Manager
Once you have constructed your
worksheet with the appropriate data and
formulae, you are ready to set up
scenarios.
􀂾 To set up scenarios:
Mouse
i. Click SCENARIO MANAGER on the
WHAT IF ANALYSIS button on the
in the DATA TOOLS group on the
DATA Ribbon; (the text “what if
analysis” will be missing if solver has
been added in to Excel)
ii. :Click the ADD button to name your
scenario and define the CHANGING
CELLS (the cells containing the values
you want to vary for each scenario).
The following dialog will appear:
Excel 2007 Advanced
© The Mouse Training Company
56
iii. Type a name in the box marked SCENARIO NAME.
iv. Click the button to the right of the CHANGING CELLS box to collapse the dialog allowing you to view
the worksheet and select the cells containing the variables. Non consecutive cells may be selected using
[Ctrl] and click. Click the button to expand the Add Scenario dialog once more.
v. Click OK to add the Scenario. The following dialog box will appear:
vi. Type the value for the first changing cell that you want to save under the current scenario name. Press
[RIBBON] to move to the next changing cell and type a value for that changing cell. Repeat the process
until all changing cell values have been set for the current scenario.
vii. Click the OK button to return to the Scenario Manager dialog
viii. Click OK again to exit the Scenario Manager.
Or
i. Click the ADD button to define another scenario.
ii. When all scenarios have been added, click OK to return to the Scenario Manager dialog and OK to exit
the Scenario Manager.
Excel 2007 Advanced
© The Mouse Training Company
57
Showing A Scenario
When several scenarios have been created, each one in turn can be shown. The values associated with that
scenario will appear in the designated Changing Cells, and all the dependant formulae on the worksheet will
update. Any charts dependant on the changing values will also update. The Scenario Manager dialog box
will remain on screen, allowing you to click on an alternative scenario name and show it instead.
􀂾 To show scenarios:
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon; (the text “what if analysis” will be missing if solver has been added in to Excel)
ii. Double‐click the scenario name whose values you want on the worksheet. The values will appear in the
changing cells.
iii. The dialog box remains on‐screen allowing you to double‐click other scenario names and see how the c
hanging values affect the data. Click OK to exit the Scenario Manager dialog.
Excel 2007 Advanced
© The Mouse Training Company
58
Editing A Scenario
There are two main ways in which you might wish to change an existing scenario. You might want to amend
the values of the changing cells, or add or delete changing cells. The approach is slightly different for each
of these tasks.
􀂾 To change values in a scenario
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon;
ii. Select the name of the scenario to be edited.
iii. Click on the EDIT button and click OK from the Edit Scenario dialog.
iv. Change the values as required, and click on the OK button. This procedure can be repeated if necessary
to edit other scenarios.
􀂾 To add changing cells:
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon; (
ii. Select the name of the scenario to be edited.
iii. Click on the EDIT button and click the button to the right of the CHANGING CELLS box to collapse the
Edit Scenario dialog.
iv. Hold down the [CTRL] key as you click and drag across the cells that you want to add. Click the button to
expand the dialog. Click OK to confirm the addition.
v. Enter the value for the newly added changing cell in the Changing cells dialog and click OK to confirm.
vi. Click CLOSE to exit the Scenario Manager.
􀂾 To remove changing cells:
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon;
ii. Select the name of the scenario to be edited.
iii. Click on the EDIT button.
iv. Drag across the cell references of the cells you want to remove from the CHANGING CELLS box and
press [DELETE]. Click OK to confirm the deletion and OK again to close the Changing cells dialog.
v. Click CLOSE to exit the Scenario Manager.
Excel 2007 Advanced
© The Mouse Training Company
59
Deleting A Scenario
􀂾 To delete a scenario:
Mouse
i. Click SCENARIO MANAGER on the WHAT IF ANALYSIS button on the in the DATA TOOLS group on
the DATA Ribbon;
ii. Select the name of the scenario to be deleted.
iii. Click DELETE button.
You can’t undo the deletion of a scenario.
Excel 2007 Advanced
© The Mouse Training Company
60
SECTION 3 USING EXCEL TO MANAGE LISTS
􀂾 Objectives
By the end of this section you will be able to:
• Set up an Excel list
• Sort the list
• Create list subtotals
• Add, edit and delete list items using the data form
• Use AutoFilter to find specific list data
• Use the Advanced filtering tools
• Analyse list data with data and PivotTables
Excel 2007 Advanced
© The Mouse Training Company
61
EXCEL LISTS,LIST TERMINOLOGY
Although Excel's primary function is as a Spreadsheet, it can also be used for a number of list operations. It
is possible to store, and manipulate information (customer records, staff records or stock inventories for
example) on an Excel worksheet, organise it in different ways, and "query" the list to extract information
which meets specific, user‐defined criteria. The list is effectively treated as a database.
In order to use Excel's database capacity, information must be laid out in rows and columns subject to
certain constraints. There are some database terms with which the user should become familiar:
Row And Column Content
The information being stored must be divided up into categories. For example, information on staff might
include Firstname, Lastname and Department. In an Excel List, each category must be entered in a separate
column. Do not mix text and numbers in a column – the data must belong to the same category of
information and therefore should be the same data type. Do not use spaces in front of column entries, use
alignment buttons instead if you need to move data away from the column edges.
Column Labels
This is the title at the top of each column, describing the category of information which it contains. Each
label name must be unique and must be made up of text rather than numbers or calculations. The column
names must appear directly above the list information ‐ they may not be separated from the rest of the list
by a blank row. Format your column labels to distinguish them from the list data.
List Size And Location
The List is the whole collection of information, all Fields, Fieldnames and Records and should be laid out as a
regular block of data. (see specifications for list limits)
Do not place more than one list on a worksheet. If you want more than one list in a workbook, place each
list on a separate sheet. (this is only a guide it will not affect functionality but when working with lists
hidden rows can cause severe problems with other lists on same sheet)
Leave at least one blank row and one blank column between the list and other data on the worksheet.
Place additional data diagonally below and to the right of your list. This ensures that data will not be
affected when you filter the list.
Miscellaneous
Excel does not distinguish between upper and lower case characters in a list, unless you use the Casesensitive
sort option.
When you use formulae in lists, Excel uses the results of the formulae.
Excel 2007 Advanced
© The Mouse Training Company
62
SORTING DATA
Although not confined
to database
information, the sorting
facility in Excel is
particularly appropriate
for changing the order
in which records are
listed. Remember to
save the file containing
the database information prior to sorting.
If you will need to restore the original record order, it is a good idea to include a column of record numbers
before sorting the database. This can be achieved simply by adding a column with a suitable heading, and
using the fill handle or the data series command to enter consecutive numbers adjacent to each record.
When using any data handling techniques ensure you have: ‐1. Selected a cell
somewhere in the data list. 2. Have NO MORE than one cell selected
Excel automatically selects the entire list for sorting. It compares the top rows of your list for
formatting differences. If there is a difference in the formatting of the top row, Excel identifies
that row as column labels and excludes it from the sort. This ensures the column labels will not be
sorted with the rest of the data.
􀂾 To perform a single‐level sort: (quick sort)
Mouse
i. Select a cell in the list within the column by which you want to sort.
ii. Click the Sort Ascending or Sort Descending button from the DATA ribbon, SORT &
FILTER group
OR
Mouse
i. Click within the data to be sorted in the column you wish to sort by
ii. Click the Sort A to Z or Sort Z to A option from the Sort & Filter button
on the HOME ribbon in the EDITING group
iii. The data will be sorted alphabetically or numerically by that column
Excel 2007 Advanced
© The Mouse Training Company
63
􀂾 To perform a multi‐level sort:
Mouse
i. Click within the data to be sorted.
ii. Choose Sort, button from the DATA ribbon, SORT & FILTER group The following
dialog box will appear in which from which you may specify the Sort fields and the Sort order.
iii. From the Sort By drop‐down list, select the field you want to use as the main sort order.
iv. Select from the next drop down list what you want to sort on by default this
will be the data (values)
v. Select the Ascending or Descending from the drop down list depending on
which order you wish the data sorted in.
vi. Select add level
vii. Specify any sub‐sorts using the Then By drop‐down lists to pick the subsequent fields to sort by when
duplicates occur in the main sort field.
viii. You may add many levels to your sorting of data. If you wish to reorder your sorting levels
use the reorder buttons by selecting a level and moving it up or down
ix. If you have an incorrect level in your many sort orders. Select it and click on delete level.
x. Click OK. to apply sort orders
OR
Mouse
i. Click the Custom sort option from the Sort & Filter button on the HOME ribbon in the EDITING group
ii. The custom sort dialog will appear.
iii. Continue as previous
iv. Click OK. to apply sort orders
Excel 2007 Advanced
© The Mouse Training Company
64
Excel 2007 Advanced
© The Mouse Training Company
65
Custom Sorting Options
The ascending and descending sort orders rearrange your list by alphabetical, numerical, reverse
alphabetical or reverse numerical order. For some types of data, such as months, this may not be the order
that you need to use. You can use one of the custom sort orders provided with the Excel program to
rearrange your data in chronological order by day of the week or by month.
􀂾 To sort by a custom sort order:
Mouse
i. Place the active cell within the list.
ii. Click the Custom sort option from the Sort & Filter button on the HOME ribbon in the EDITING group
OR
i. Choose SORT, button from the DATA ribbon, SORT & FILTER group.
ii. From the SORT BY drop‐down list, select the column by which you want to sort.
iii. From the SORT ON drop down list select what you want to sort on (Values)
IV. From the ORDER drop down list select CUSTOM LIST
v. The following dialog box will appear
vi. Select a custom list from the left hand box.
vii. Click on OK to close the list dialog and apply sort order to level and click on OK again to perform the
sort.
Excel 2007 Advanced
© The Mouse Training Company
66
Creating A Custom Sort Order
When sorting by ascending, descending or chronological order is not suitable for the data in a list, you can
create a custom sort order. Custom sort orders enable you to give Excel the exact order to rearrange data.
Custom sort orders are helpful for data such as Low, Medium, High, where neither alphabetical nor an
existing custom sort order will provide the desired results.
􀂾 To create a custom sort order:
Mouse
i. Click the Custom sort option from the Sort & Filter button on the HOME ribbon in the EDITING group
OR
i. Choose SORT, button from the DATA ribbon, SORT & FILTER group.
OR
i. Click on the MICROSOFT OFFICE BUTTON and select the EXCEL OPTIONS button. In the POPULAR
section . click on the EDIT CUSTOM LISTS button
ii. In the CUSTOM LISTS box, verify that New List is selected.
iii. In the LIST ENTRIES box, type each unique entry in the order you want to sort the entries. Separate the
entries by pressing [ENTER].
iv. Click ADD the list entries will appear in the left hand box
v. Click OK.
Custom sort orders are saved with the Excel 2007 program settings and are available for use with
all worksheets. You can use a custom list with the AutoFill feature.
Excel 2007 Advanced
© The Mouse Training Company
67
ADDING SUBTOTALS TO A LIST
Automatic subtotals are useful in summarising the data contained in a list.
Subtotals are created by using an Excel summary function such as SUM(),
COUNT() or AVERAGE(). To use the subtotals, your data must be organised in
a properly designed list and sorted according to the column by which you
want to summarise the data.
􀂾 To add subtotals to a list:
Mouse
i. Sort the list according to the column by which you
want to summarise the data.
ii. Choose SUBTOTAL from the OUTLINE group on the
DATA ribbon.
iii. From the At Each Change In drop‐down list, select
the field by which you want to summarise the data.
(the field you have the data sorted by)
iv. From the Use Function drop‐down list, select the
summary function you want to use to generate the
subtotals.
v. In the Add Subtotal To box, check the column or
columns to which you want the function to be
applied.
vi. If desired, check the options for replacing the current
subtotals (if any), inserting a page break for each
summary group and inserting the summary below
each group.
vii. Click OK.
When you use the Data, Subtotals command, it adds its own Grand Total, so you should not use the
SUM() function in your list. If you use the Data, Subtotals command, the SUM() function will be
inaccurate since it includes the subtotals in the calculation. (see working with lists)
􀂾 To remove subtotals from a set of data:
Mouse
i. Select a single cell somewhere within the subtotalled list.
ii. Choose SUBTOTAL from the OUTLINE group on the DATA ribbon
iii. Click REMOVE ALL and then OK.
Excel 2007 Advanced
© The Mouse Training Company
68
Examining Subtotals
When you insert automatic subtotals, Excel creates an outline of your data. The outline enables you to
show or hide certain sections of data by clicking on the outline buttons below the Name box on the formula
bar. Grand total values are derived from the list data, not the subtotal rows.
􀂾 To examine a subtotalled list
Mouse
i. Having applied subtotals to a list, outline numbering can be seen on the far left below the name box.
ii. Select 1,2 or 3 to see all the data with subtotals at intervals, subtotals alone or just the grand total
iii. Clicking on the + or =‐ buttons below the outline numbers enables you to select which outline group to
expand or collapse to allow printing or comparison of required data.
iv. Removing subtotals will remove these automatic outlines
Outline
buttons
Excel 2007 Advanced
© The Mouse Training Company
69
FILTERING A LIST
When you filter a list, you
display only the sets of
data that meet a certain
set of search conditions
called criteria. The
AutoFilter feature enables
you to specify those search
conditions from the list.
When you use the Data, Filter, AutoFilter command, drop‐down list arrows are displayed next to each of the
column labels in the list. When you open a drop‐down list, a list of all the unique entries for that column is
displayed. By selecting one of the entries from the drop‐down list, called a filter criterion you instruct Excel
what to search for. Then Excel filters the list so that only the sets of data that contain the entry you
selected will be displayed. When Filter mode is active, arrows for the columns with filter criterion selected
appear in blue on the worksheet, row numbers appear in blue, and the status bar displays either the
number of rows that meet the criteria, or the text “Filter mode.” The sets of data that do not meet the
criteria remain in the list but they are hidden.
If you select a single cell in the list before choosing Filter drop‐down list arrows are applied to all of
the column labels in your list. If you select multiple column labels before choosing Filter drop‐down
list arrows are displayed only for the selected columns, thus restricting which columns you can
apply filters to. In either case, the entire list is filtered. Also, you can filter only one list at a time
on a worksheet.
􀂾 To filter a list using AutoFilter:
Mouse
i. Place the active cell anywhere within your list.
ii. Click the FILTER option from the SORT & FILTER button on the HOME
ribbon in the EDITING group
OR
i. Choose FILTER, button from the DATA ribbon, SORT &
FILTER group.
ii. Your list column labels will appear with drop‐down list arrows
to the right.
iii. When you select the drop down arrow from the top of a particular column you will have (depending on
the data type) a box at the bottom of the menu with all unique values make sure the values you wish to
be seen are ticked. Select the values you are filtering for.(Following Pictures)
iv. When all values you wish to see are ticked (this creates OR conditions for that column) click OK to apply
the filter for that column
OR
Excel 2007 Advanced
© The Mouse Training Company
70
i. You have sort order options at the top part of the menu which work in the same manner as previously
discussed if you select a sort order this will close the menu and apply the filter.
ii. Repeat step 3 until you have set filter criteria for all columns that you wish to filter by.
iii. The list will show only those rows that match your criteria.
Each time you apply criteria to a column you create AND conditions across columns that reduce the
number of records that will be displayed. Using the simple autofilter OR conditions cannot be
applied across columns. (see advanced filter). More AND conditions = less records
Whilst a filter is active, if you print the worksheet, only visible rows will be output, so you can print
out multiple views of your data from an individual list.
􀂾 Removing a single column filter:
Mouse
Sorting
options
Values to be
filtered by
Filter options for
custom filter.
(see next)
Excel 2007 Advanced
© The Mouse Training Company
71
i. You can see which columns have filter criteria active because the drop‐down list arrows are blue. Click
the drop‐down list arrow for the column whose criteria you wish to remove. And choose the tick box
that says select all.
ii. All values will be selected for display again. Either click OK or select new sort order to show all records.
Excel 2007 Advanced
© The Mouse Training Company
72
􀂾 Removing all column filters:
Mouse
i. Click the Clear option from the SORT & FILTER button on the HOME ribbon in
the EDITING group
OR
i. Choose FILTER, button from the DATA ribbon, SORT &
FILTER group.
ii. All column filters will be cleared
When filters are cleared the SELECT ALL tick box is applied to all columns. Make sure that this
method is the one you really want if you have selected complicated criteria in a particular column.
Custom Criteria
When you specify a filter criterion for a column from unique entries listed in the AutoFilter drop‐down list,
you can only select one filter criterion at a time. The Custom filter criterion enables you to filter a list to
display sets of data that contain This creates an OR condition or complicated options of what text, dates or
numbers you wish to display To meet the filter criteria, a set of data must meet either the first filter
criterion or the second filter criterion or both
You can also use the Custom criterion choice to find values that fall within a range. When you specify
custom criteria, select a comparison operator from the drop‐down list and then either type in a value or
select it from the criteria drop‐down list.
When you use custom criteria, you need to understand the comparison operators that Excel offers you. The
table below outlines these:
Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
􀂾 To specify “either AND/OR”
custom criteria:
Mouse
i. Click on the AUTOFILTER drop‐down
for the desired column.
ii. Depending on the Data type you will
have the data type and type of filter
name. (picture shows number filter)
Excel 2007 Advanced
© The Mouse Training Company
73
iii. You may select one of the options shown to
start your custom filter OR move to the
bottom of the menu and select custom Filter.
iv. The following dialog box will be shown.
v.
vi. In the Custom AutoFilter dialog box from the
first criteria drop‐down list select one of the
filter criteria. (The default operator is =
“equals”).
vii. Click on OR or AND. (this is very important)
viii. From the second operator drop‐down list, select a comparison operator.
ix. From the second criteria drop‐down list select the other filter criterion.
x. Click OK. The filtered list shows the sets of data that meet either the first or the second specified
criterion for the column.
􀂾 Using custom criteria to find a range of values:
Mouse
i. Click the drop‐down arrow for the column label whose range of values you want to filter by. This will
typically be numbers or dates
ii. Click Custom filter. From the resulting dialog box, select the comparison operator to control the lower
limiting value, for example greater than or greater than or equal to.
iii. From the first criteria drop‐down list, select a value or type the value in.
iv. Select AND as the data MUST meet both conditions to display the range
v. From the second criteria drop‐down list select the other filter criterion.
vi. Click OK. The filtered list shows the sets of data that meet BOTH the first and the second specified
criterion for the column.
Wildcards
You can use wildcards to search for text in common within the unique entries, even though the entire entry
might not match. For example, searching for all of the sets of data that have entries in the last‐name
column that begin with “M” might display two Moore’s (where the entire entry matches) but might also
display Madding and Martinez (where the entire entry does not match).
Wildcard Finds Example
* asterisk Any set of characters that are in the same
position as the asterisk
*.xls finds Filter.xls and
sortdata.xls
? question mark Any single character that is in the same position
as the ?
B?t finds Bat, Bit, But and Bet
~ tilde A question mark or an asterisk Who~? Finds the text “Who?”
Excel 2007 Advanced
© The Mouse Training Company
74
􀂾 To filter a list using wildcards:
Mouse
i. With AutoFilter active, select the drop‐down list arrow to the right of the column you want to use to
filter the data.
Wildcards only work when filtering columns containing text
i. Choose CUSTOM.
ii. Ensure that the operator is set to =.
iii. Type the pattern of letters you are filtering by with the asterisks and/or question marks inserted in the
appropriate positions.
iv. Click OK.
Turning Off Autofilter
When you no longer need to filter your data, you can switch the AutoFilter off in the following way:
􀂾 To switch off AutoFilter:
Mouse
i. Choose FILTER, button from the DATA ribbon, SORT & FILTER
group.
OR
i. Click the FILTER option from the SORT & FILTER button on the HOME ribbon
in the EDITING group
ii. The AutoFilter option on the submenu will appear ticked showing that AutoFilter
is currently active. Click AutoFilter to remove the tick and deactivate the
AutoFilter.
Excel 2007 Advanced
© The Mouse Training Company
75
Advanced Filtering
Sometimes, the filter criteria that you specify with AutoFilter will not yield the necessary results. For
example, you cannot use AutoFilter to filter a list to display the more complex criteria of two separate AND
conditions combined with an OR condition. To do this, you must use the Advanced Filter option. This relies
on you setting up and defining a Criteria range on the worksheet where the data to be matched can be
entered.
Set Criteria
The Criteria range usually consists of a copied set of the list column names, and a blank row immediately
beneath into which you can type the data to be matched. It is a good idea to copy the column names from
the top of the database into the area to be used as a criteria range, as this reduces the chance of there
being any discrepancy between the two sets of names.
In fact, not all the column labels need to be included in the criteria range. It could be restricted to only
those labels on which you wished to search, and those labels included could be displayed in a different
order. If you wish and criteria to create a range you may need to copy a particular column label twice.
􀂾 To define the Criteria range:
Mouse
i. Copy across to a new sheet the column labels you wish to create criteria for.
ii. Create the criteria in the cells under the labels.
iii. If criteria are created in the same row, this would create an AND condition and on different rows this
would create an OR condition.
iv. Select the copied set of column labels and the criteria below
and name the cell range if you wish. (not essential) Type the
word Criteria into the name box and press [ENTER].
You do not have to name the cells with the range name
Criteria, but it will ensure that Excel automatically picks the
correct group of cells as the criteria carrying cells whenever you
use the Advanced Filter.
AND
condition
OR
condition
Excel 2007 Advanced
© The Mouse Training Company
76
􀂾 To run an advanced filter:
Mouse
i. Click within your data list
ii. Choose ADVANCED, button from the DATA ribbon, SORT & FILTER group.
iii. The following dialog will be displayed
iv. You should see that your data list is selected completely. If not (due to empty columns or rows.) Delete
the values in the list range box and either type in the range you wish or select the correct range with the
mouse
v. In the criteria range box either type criteria (if you named the range) or delete any values present and
select your criteria from your sheet of criteria.
vi. By default the list will be filtered in place as when using the AutoFilter.
vii. Click OK. You will be returned to your data list with the filter applied.
viii. Sort if needed
􀂾 To remove a filter:
Mouse
i. Click the Clear option from the SORT & FILTER button on the HOME ribbon in
the EDITING group
OR
i. Choose FILTER, button from the DATA ribbon, SORT & FILTER group.
ii. The filter will be cleared
Excel 2007 Advanced
© The Mouse Training Company
77
Entering Search Criteria
At a basic level criteria entered in the Criteria range is subject to the limitations mentioned earlier but
making use of a user defined Criteria range allows more complex searches to be performed.
It is important to remember to clear the old filter and select new criteria and delete old criteria from either
the custom filter or the advanced filter before applying a new filter. Otherwise the true results of a filter
will not be shown. For example, if the first filter is applied with Johnson entered under Surname, and a
subsequent filter is carried out for those who work in Finance, it is essential that the Name specification is
cleared unless you deliberately wish to confine the filter to those people called Johnson who happen to
work in the Finance department.
Excel will find records matching text information entered in the Criteria range, and records where the initial
letters match the specified data. When working with a user defined criteria range, if you wish to confine
filter results to only those records where, for example, the first name is Rob, it would be necessary to enter
the formula ="=Rob" in the Criteria range under the appropriate column label.
Criteria Entered Results Matched
Rob or Rob* Rob; Robert; Robin
="=Rob" Rob
Wildcards With Text Criteria
One variation on searches for text criteria consists of using text Wildcard symbols. The two Wildcard
symbols may be familiar to users of other PC systems.
The Asterisk
The Asterisk (*) may be substituted for any group of characters. Searching for *Banking would find both
Development Banking and Merchant Banking. If no Wildcard symbols are included in the search criteria,
Excel usually assumes that there is an asterisk at the end of the specification, so it will match the data
specified and any records where the initial data is the same.
The Question Mark
The Question Mark (?) may be substituted for any single character. The question mark identifies the
position of the wildcard character within the string of text. T?m would find Tim or Tom. ?a would find all
records where the second letter in the appropriate field was an A. Once again, Excel will assume that there
is an asterisk on the end of the search specification unless otherwise informed. Entering T?m in a Firstname
field would find Tim, Tom and Tommy. Use the syntax ="=T?m" to confine the searches to three characters
in length.
Multiple Criteria
Hitherto, the Criteria range has been described as a copied set of field names into which you may enter
search specifications under the appropriate column names. You may choose to enter criteria in the blank
row under more than one field name. Entering Finance as the department and 7 as the grade for example,
would find only those persons who met both criteria.
Multiple criteria on the same row dictates that the first specification AND all other specifications must be
met in order for Excel to find the record. (See also use of the AND() function under Calculated Criteria).
Excel 2007 Advanced
© The Mouse Training Company
78
Using Multiple Rows in the Criteria Range
There may be situations where you wish to find members of either Division or
Risk. In such an instance the Criteria range can be extended to include a second
row into which you may enter specifications:
􀂾 Extending the criteria range for OR criteria:
Mouse
• If you named your criteria range then you may wish to first delete the current Criteria range name.
choose NAME MANAGER from the FORMULAS ribbon. In the DEFINED NAMES group.
iii. Select CRITERIA from the names list inside the dialog and click DELETE.
iv. Close the dialog box
v. Create your criteria on your criteria worksheet as necessary. Now, entering search specifications in all
rows within the range will allow Excel to identify all those records which meet the specifications in
either, the first OR the second row etc. (See also use of the OR() function under Calculated Criteria).
vi. Highlight the entire region to be redefined as the Criteria range ‐ i.e. the copied set of Column names
and the two rows (or more) immediately below, then, Name the range again if you wish. (if previous
named criteria are still present then ensure a different name is used to identify this criteria.).
vii. Apply advanced filter as previously discussed.
The Criteria range may be extended to include three or more rows of user defined search criteria if
required.
To return to using just one row of user defined information in the Criteria range, select the area to
be included and redefine the Criteria range again. This is important because searching for data
when a row in the Criteria range has been left blank, will result in Excel finding every record in the
database. In effect, you have asked Excel to find all records where the contents of any field can be
anything at all.
Checking The Criteria Range
If you are getting surprising results when you filter your data, it may be because your criteria range contains
unlabelled cells or extra rows that you thought you had removed from the range.
It is easy to double check the currently defined Criteria range at any time by making use of the range name
which Excel applies to it. Using the [F5] function key will result in a dialog box showing all the currently
named ranges on the worksheet. Click on the name Criteria and choose OK. The area covered by that
name will be highlighted. You may choose to alter the selection and redefine the Criteria range again to
adjust it.
Excel 2007 Advanced
© The Mouse Training Company
79
Calculated Criteria
You may also choose to find data subject to calculated criteria rather than exactly matching data or using
comparison operators or wildcard characters. This would let you find data that matches the result of a
formula, rather than a value that you have entered directly
􀂾 To use calculated criteria:
Mouse
i. Include in the Criteria range one column name which is not used in the list ‐ Calc for example.
ii. Delete any named criteria from the NAME MANAGER
iii. Select the column labels (including Calc or whatever you have named it) plus at least one row below
them depending on whether you need use multiple OR conditions to filter your data.
iv. Type Criteria into the Name box and press [ENTER] to name the range
Below the calculated fieldname in the criteria range, you must enter a formula which refers to the
cells contained in the first record of the database. The formula must result in a TRUE or FALSE
answer.
In the example below, in order to find only those records where the value of the gross for Australia would
increase to over 500 a 10% increase was applied, the formula shown could be entered in the Calc column.
When entered, the calculated formula displays on the worksheet as TRUE or FALSE depending on the
figures contained in the first record of the database to which the formula specifically refers. The underlying
formula displays in the formula bar as usual.
You may use calculated fields to refer to and manipulate cells within the first record of the database, and to
refer to cells outside of the database area. For example, the threshold figure of 500 might be held in an
input cell outside the database. If this was the case, that cell reference could be included in the calculated
search criteria, but the reference to it would need to be absolute or fixed.
Excel 2007 Advanced
© The Mouse Training Company
80
Calculated Criteria Using Functions
Some of Excel's Logical Functions are particularly suited to setting Criteria for a list search. Rather than
having to extend the criteria range, you can specify criteria as arguments within the AND(), OR() or NOT()
functions.
=AND()
If there are several specifications, every one of which must be met by all records found, use the AND()
function and refer once again to the cells contained in the first record of the database. Text entries must be
enclosed in double quotes. The AND() function may contain up to 30 comma separated arguments
Referring to the database in the diagram below, for example, if the GROSS (L2) must be greater than or
equal to 400, the DIVISION (E2) Australia and the RISK (G2), medium. The calculated function might be set
as appears on the Formula bar:
=OR()
Searching for two different entries in the same field would necessitate the use of the OR() function. You
may wish to locate all the records where the DEPT (F2) is either shows or water rides. Obviously the AND()
function will be inappropriate, because the customer cannot be both companies simultaneously. Instead,
the calculation might be:
Excel will find any records where any one of the arguments contained in the OR() function is met. The OR()
function may contain up to 30 comma separated logical arguments.
=NOT()
The NOT() function can be used to exclude records meeting certain criteria from the find operation.
Entering a calculation such as:
=NOT(DIVISION="Canada")
will allow Excel to find all those records where the Division is anything other than Canada. The NOT()
function contains only one argument. It can be combined with other functions, for example:
=AND(NOT(DIVISION="Canada"),Hrs<20)
Excel 2007 Advanced
© The Mouse Training Company
81
will find all those records for divisions other than Canada where the Hrs worked was less than 20.
Copying Filtered Data
You can use the Advanced Filter command to copy the sets of data that meet the criteria in the Criteria
range to another location on the worksheet.
􀂾 To copy filtered data to another location:
Mouse
i. Set the Criteria range.
ii. Place the active cell within the list.
i. Choose ADVANCED, button from the DATA ribbon, SORT & FILTER group.
ii. In the resulting dialog box, choose Copy to Another Location.
iii. In the Copy To text box, enter a worksheet cell that represents the top left‐hand corner of where you
would like the results.
iv. Click OK.
If you want to copy only certain columns from the matching sets of data, enter the column labels
exactly as they appear in the list in the location you want to copy to. When you run the filter, set
the Copy To range reference to the cells where you have typed the column labels. You may only use
this on the sheet your data is on you cannot copy to another sheet that will have to be done
manually later.
Unique Records
There is a check box [a]allowing you to select Unique records only. This may be useful if, for example, the
Copy To range does not include all the column labels. There may be several records where the division and
last name are the same. If the Hourly rate, hrs, Date of hire fields etc are not included in the Copy To range,
this could result in several seemingly identical records being extracted. Checking the Unique records only
check box before choosing OK would result in Excel extracting only the first record in each instance.
Data copied
to here
Excel 2007 Advanced
© The Mouse Training Company
82
LIST STATISTICS
There are several Excel functions which are specifically designed to enable you to analyse database
information. A selection of these appears in the table below.
Database Functions
Function Purpose
DCOUNT(Database,Field,Criteria) To count the number of records in a list which meet
specified criteria. This function will only count value cells.
DCOUNTA(Database,Field,Criteria) To count the number of records in a list which meet
specified criteria. This function includes text and value
cells.
DSUM(Database,Field,Criteria) To add the contents of the chosen field in a list, subject to
any specified criteria.
DMIN(Database,Field,Criteria) To find the minimum value in the chosen field in a list,
subject to any specified criteria.
DMAX(Database,Field,Criteria) To find the maximum value in the chosen field in a list,
subject to any specified criteria.
DAVERAGE(Database,Field,Criteria) To find the average value of the chosen field in a list,
subject to any specified criteria.
DGET(Database,Field,Criteria) To return the contents of the chosen field subject to any
specified criteria. This function is only valid where a
single record meets the criteria set.
DPRODUCT(Database,Field,Criteria) To multiply the contents of the chosen field in a list,
subject to any specified criteria.
In all cases, if the Criteria range is blank, these functions will apply to the entire list area. Once data is
entered in the Criteria range, the results of the Database functions will adjust to reflect only those records
meeting the criteria.
The arguments for all of these functions are identical, and the easiest way to incorporate them into a
worksheet is by using the Paste Function dialog.
Excel 2007 Advanced
© The Mouse Training Company
83
􀂾 To enter a database function on the worksheet:
Mouse
i. Click the INSERT FUNCTION button from FORMULA LIBRARY on the FORMULAS ribbon
OR
i. Click the INSERT FUNCTION button from the left hand side of the FORMULA BAR.
ii. The following dialog box will appear
iii. In the function category list, select Database.
iv. From the function name list choose the database function you require: e.g.=DMAX()
v. Click OK another dialog will appear.
Excel 2007 Advanced
© The Mouse Training Company
84
vi. Enter the three arguments, list range, field name and criteria range.
vii. Press [ENTER] or click OK
In the above example, the formula shown on the formula bar above has been entered into the cell to the
right of the label “Max gross pay for Canada.” This formula finds the maximum gross pay for all records
where the division is Canada. All the database functions look at what has been entered in the criteria range
in order to give their results.
If you have assigned the range name DATABASE to your LIST AREA and CRITERIA to your criteria
range you use can [F3] to paste the names.
The field may be entered as a number or as text. Obviously, if the field on which the function is to
operate is the fifth column within the database, you could enter the number 5 as the field
argument. Alternatively, the field name could be entered as text, in which case it would need to be
enclosed in double quotes:
=DMAX(database,"division",criteria)
Excel 2007 Advanced
© The Mouse Training Company
85
PIVOTTABLES
A PivotTable can summarise large amounts of data using specified calculations and formats. It is called a
PivotTable because the headings can be rotated around the data to view or summarise it in different ways.
• The source data can be:
• An Excel worksheet database/list or any range that has labelled columns.
• A collection of ranges to be consolidated. The ranges must contain both labelled rows and columns.
• A database file created in an external application such as Access or Dbase.
• The data in a PivotTable cannot be changed as it is the summary of other data. The data itself can be
changed and the PivotTable recalculated. The PivotTable can be reformatted.
􀂾 To create a PivotTable or PivotTable with pivot chart:
Mouse
i. Select a cell in a range of cells of data, or put the insertion point inside of an
Excel table.
ii. Make sure that the range of cells has column headings.
iii. Do one of the following: To create a PivotTable report, on the Insert ribbon, in
the Tables group, click PivotTable, and from the menu click PivotTable.
iv. The Create PivotTable dialog box is displayed.
OR
i. To create a PivotTable and PivotChart report, on the Insert ribbon, in the Tables group, click PivotTable,
and then from the menu click PivotChart.
ii. The Create PivotTable with PivotChart dialog box is displayed.
Excel 2007 Advanced
© The Mouse Training Company
86
􀂾 To Select a data source. And choose the data that you want to analyze
Mouse
i. Click Select a table or range.
ii. Type the range of cells or table name reference, such as =QuarterlyProfits, in the Table/Range box.
iii. If you selected a cell in a range of cells or if the insertion point was in a table before you started the
wizard, the range of cells or table name reference is displayed in the Table/Range box.
OR
i. To select a range of cells or table, click Collapse Dialog button to temporarily hide the
dialog box, select the range on the worksheet and then press Expand Dialog .
If the range is in another worksheet in the same workbook or another workbook, type the
workbook and worksheet name by using the following syntax:
([workbookname]sheetname!range).
􀂾 Use external data
Mouse
i. Click Use an external data source.
ii. Click Choose Connection.
iii. The Existing Connections dialog box is displayed.
iv. In the SHOW drop‐down list at the
top of the dialog box, select the
category of connections for which
you want to choose a connection or
select ALL CONNECTIONS (which
is the default).
v. Select a connection from the
SELECT A CONNECTION list box,
and then click OPEN.
If you choose a connection from the
Connections in this Workbook
category, you will be reusing or
sharing an existing connection. If you
choose a connection from the
Connection files on the network or
Connection files on this computer the
connection file is copied into the
workbook as a new workbook
connection, and then used as the new
connection for the PivotTable report.
Excel 2007 Advanced
© The Mouse Training Company
87
􀂾 To Enter a location.
Mouse
i. To place the PivotTable report in a new worksheet starting at cell A1, click NEW WORKSHEET.
ii. To place the PivotTable report in an existing worksheet, select EXISTING WORKSHEET, and then type
the first cell in the range of cells where you want to locate the PivotTable report.
OR
i. click COLLAPSE DIALOG to temporarily hide the dialog box, select the beginning cell on
the worksheet and then press EXPAND DIALOG .
ii. Click OK.
An empty PivotTable report is added to the location that you entered with the PivotTable Field List
displayed so that you can start adding fields, creating a layout, and customizing the PivotTable report.
If you are creating a PivotChart report, an associated PivotTable report is created directly underneath the
PivotChart report for the location that you enter. This PivotTable report must be in the same workbook as
the PivotChart report. If you specify a location in another workbook, the PivotChart report will also be
created in that workbook.
To Create A PivotChart Report From An Existing PivotTable Report
Mouse
i. Click the PivotTable report.
ii. On the INSERT ribbon, in the CHARTS
group, click a chart type.
You can use any chart type except xy (scatter), bubble or stock.
􀂾 Convert a PivotChart report to a static chart
Find the associated PivotTable report that has the same name as the PivotChart report by doing the
following: (The PivotTable report that supplies the source data to the PivotChart report. It is created
automatically when you create a new PivotChart report. When you change the layout of either report, they
both change.)
Mouse
i. Click the PivotChart report to find the associated PivotTable report name, In the DATA group, on the
DESIGN ribbon, click SELECT DATA to display the EDIT DATA SOURCE dialog box, and then note the
associated PivotTable name, which is the text that follows the (!) exclamation point, in the Chart data
range text box and then click OK.
ii. To identify the associated PivotTable report, click each PivotTable report in the workbook, and then on
the OPTIONS ribbon, in the PIVOTTABLE group, click OPTIONS until you find the same name in the
NAME text box. then Click OK.
iii. On the OPTIONS ribbon, ACTIONS group, click SELECT, then click ENTIRE PIVOTTABLE. Press
DELETE.
Excel 2007 Advanced
© The Mouse Training Company
88
iv. The chart is now static and not associated with the PivotTable.
Create A Static Chart From The Data In A PivotTable Report
This procedure creates a regular, noninteractive chart rather than a PivotChart report (PivotChart report: A
chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see
different levels of detail or reorganize the chart layout by dragging fields and by showing or hiding items in
fields.).
􀂾 To create static chart from data
Mouse
i. Select the data in the PivotTable report that you want to use in your chart. To include field buttons(A
field button is a Button that identifies a field in a PivotTable or PivotChart report. You can drag the field
buttons to change the layout of the report, or click the arrows next to the buttons to change the level of
detail displayed in the report.) and data in the first row and column of the report, start dragging from
the lower‐right corner of the data that you're selecting.
ii. On the HOME ribbon, in the CLIPBOARD group, click COPY .
iii. Click a blank cell outside of the PivotTable report.
iv. On the HOME ribbon, in the CLIPBOARD group, click the arrow next to PASTE, and then click PASTE
SPECIAL.
v. Click VALUES, and then click OK.
vi. On the INSERT ribbon, in the CHARTS group, click a chart type.
Delete A PivotTable Or PivotChart Report
􀂾 To Delete a PivotTable report
Mouse
i. Click the PivotTable report.
ii. On the OPTIONS ribbon, in the ACTIONS group, click SELECT, and then click ENTIRE PIVOTTABLE.
iii. Press DELETE.
Deleting the associated PivotTable report (associated PivotTable report: The PivotTable report that
supplies the source data to the PivotChart report. It is created automatically when you create a
new PivotChart report. When you change the layout of either report, the other also changes.) for a
PivotChart report creates a static chart that you can no longer change.
􀂾 To Delete a PivotChart report
Mouse
i. Select the PivotChart report.
ii. Press DELETE.
Deleting the PivotChart report does not automatically delete the associated PivotTable report.
Excel 2007 Advanced
© The Mouse Training Company
89
Create Layout For PivotTables
Once the PivotTable has been created a layout has to be
created to view your data in the empty PivotTable we do this
through the PivotTable Field list which appears in a pane to the
right of your PivotTable
An Option button will allow you to change the way your
PivotTable field list looks
􀂾 To create a layout
Mouse
i. Drag and drop the fields from the fields section at the top to the bottom areas of the Pane
ii. If Month of Hire is used as a row label the PivotTable will look at your data and pick out the unique
values to make up the row headings within your report.
iii. Choose a field for the column labels
iv. Choose a field you wish to use as your values.
Numerical data will use SUM as the default method of calculating your data. If Textual it will use
count as default. You may use more than one field in any area but it is important to place them
correctly. You may drag them around as much as you wish until your report looks as you wish it to
look.
v. You may filter on one or more fields if you wish. To do this drag a field to the report filter box
Any of these areas can be filtered.
Excel 2007 Advanced
© The Mouse Training Company
90
Modifying A PivotTable
• All of the following are options for modifying your PivotTable
• Adding or deleting fields
• filtering and sorting
• Format the colour scheme
• Changing how the pivot chart calculates
􀂾 To Add or delete fields:
Mouse
vi. Drag and drop the fields between the various areas and the field list section field info will disappear or
appear in different locations.
vii. Dragging a field from one of the areas to the field list will remove that data from the report.( this will not
change the data in your Data list merely leave it absent from the report.
Excel 2007 Advanced
© The Mouse Training Company
91
􀂾 To sort a report:
Mouse
i. Move your mouse over a field that is ticked in the field list section
ii. Click on the drop down arrow to the right of the selected field
i. Untick any values you don’t wish to see this will remove those values as column labels within the report.
ii. SORT your data Ascending or descending
iii. Click OK
OR
i. Click on MORE SORT OPTIONS
ii. The following dialog appears.
iii. By default you may drag labels on your
report to be in any order you wish.
iv. Make an ascending or descending
choice and by what field (this may be
the field you originally started this
process from or the Value field (E.G.
Sum Of Gross)
v. Click OK.
OR
Excel 2007 Advanced
© The Mouse Training Company
92
i. click on MORE OPTIONS
ii. The following dialog appears
By default the report will sorted each time you update the report. If this box is unticked then you are
allowed to sort by a specific key sort order as when we created custom sort orders previously. (see
following picture.)
Excel 2007 Advanced
© The Mouse Training Company
93
􀂾 To filter a report
Mouse
i. Move your mouse to LABEL or VALUE FILTERS to see certain options
to filter out your data
ii. Selecting a filter brings up the following dialog box the value is
obviously SUM OF GROSS you may decide whether it is supposed to
be equal to or greater than.
iii. Type in the value (or values) you will compare the condition against
and Click OK
You may decide to filter your data more thoroughly there are two kinds of filters VALUE and LABEL
filters Label filters will remove labels based on criteria rather than the tickboxes just discussed. And
the Value filters will hide the data values and leave the labels showing. Both are completed the
same way as Previously discussed in the filtering section.
MANAGING PIVOTTABLES
When data is changed in the PivotTable source list, the PivotTable does not automatically recalculate.
Using Another PivotTable Report
The PivotTable cache.
Each time that you create a new PivotTable report or PivotChart report, Excel stores a copy of the data for
the report in memory, and saves this storage area as part of the workbook file. Thus, each new report
requires additional memory and disk space. However, when you use an existing PivotTable report as the
source for a new report in the same workbook, both reports share the same copy of the data. Because you
reuse the same storage area, the size of the workbook file is reduced and less data is kept in memory.
Location requirements
To use a PivotTable report as the source for another report, both reports must be in the same workbook. If
the source PivotTable report is in a different workbook, copy the source report to the workbook location
where you want the new report to appear. PivotTable reports and PivotChart reports in different
workbooks are separate, each with its own copy of the data in memory and in the workbook files.
Excel 2007 Advanced
© The Mouse Training Company
94
Changes affect both reports
When you refresh the data in the new report, Excel also updates the data in the source report, and vice
versa. When you group or ungroup items in one report, both reports are affected. When you create
calculated fields or calculated items in one report, both reports are affected.
PivotChart reports
You can base a new PivotTable report or PivotChart report on another PivotTable report, but you cannot
base it directly on another PivotChart report. However, Excel creates an associated PivotTable report from
the same data whenever you create a PivotChart report, so you can base a new report on the associated
report. Changes to a PivotChart report affect the associated PivotTable report, and vice versa.
Changing An Existing Report's Source Data
Changes in the source data can result in different data being available for analysis. For example, you may
want to conveniently switch from a test database to a production database. You can update a PivotTable
report or a PivotChart report with new data that is similar to the original data connection information by
refreshing the report.
To include additional data or different data, you can redefine the source data for the report. If the data is
substantially different with many new or additional fields, it may be easier to create a new report.
Displaying new data brought in by refresh
Refreshing a report can also change the data that is available for display.
For reports based on worksheet lists, Excel retrieves new fields within the
source range or named range that you specified. For reports based on
external data, Excel retrieves new data that meets the criteria for the
underlying query (query: In Query or Access, a means of finding the records
that answer a particular question you ask about the data stored in a
database.) or data that becomes available in an OLAP cube. You can view
any new fields in the Field List and add the fields to the report. (OLAP cube:
An OLAP data structure. A cube contains dimensions, like
Country/Region/City, and data fields, like Sales Amount. Dimensions
organize types of data into hierarchies with levels of detail, and data fields
measure quantities.)
􀂾 To refresh a PivotTable:
Mouse
Click in PivotTable.
i. Choose REFRESH DATA in the DATA group on the OPTIONS ribbon.
ii. Choose to REFRESH ALL or just REFRESH
iii. The data is is now refreshed and updated new information, field names and changed data is now
displayed
Refresh will refresh just the report you are clicked on. Refresh all will refresh all reports in the
workbook.
Excel 2007 Advanced
© The Mouse Training Company
95
Automatically Refresh Data When A Workbook Is Opened
You can refresh an external data range automatically when you open the workbook, and optionally save the
workbook without saving the external data, so that the workbook file size is reduced.
􀂾 To automatically refresh data
Mouse
i. Click a cell in the external data range.
ii. On the DATA ribbon, in the MANAGE CONNECTIONS group, click the arrow next to REFRESH, and
then click CONNECTION PROPERTIES.
iii. Click the USAGE tab and Select the REFRESH DATA ON FILE OPEN check box.
iv. If you want to save the workbook with the query definition but without the external data, select the
REMOVE EXTERNAL DATA FROM QUERY TABLE BEFORE SAVING WORKSHEET check box.
To refresh data when the workbook is opened for a PivotTable report, you can also use the Refresh
data when opening the file check box under the PivotTable Data section on the Data tab of the
PivotTable Options dialog box.
Automatically Refresh Data At Regular Time Intervals
􀂾 To refresh at Time intervals
Mouse
i. Click a cell in the external data range.
ii. On the DATA ribbon, in the CONNECTIONS group, click the arrow next to REFRESH, and then click
CONNECTION PROPERTIES.
iii. Click the USAGE tab and Select the REFRESH EVERY check box, and then enter the number of minutes
between each refresh operation.
Require A Password To Refresh An External Data Range
Stored passwords are not encrypted and not recommended. If your data source requires a password to
connect to it, you can require that the password is entered before the external data range can be refreshed.
This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy).
􀂾 To set a password.
Mouse
i. Click a cell in the external data range.
ii. On the DATA ribbon, in the CONNECTIONS group, click the arrow next to REFRESH, and then click
CONNECTION PROPERTIES.
iii. Click the DEFINITION tab and clear the SAVE PASSWORD check box.
Excel 2007 Advanced
© The Mouse Training Company
96
Excel prompts for the password only the first time that the external data range is refreshed in each
Excel session. The next time that you start Excel, you will be prompted for the password again if
you open the workbook that contains the query and attempt a refresh operation.
Excel 2007 Advanced
© The Mouse Training Company
97
Grouping PivotTable Items
• Data can be summarised into higher level categories by grouping items within PivotTable fields.
Depending on the data in the field there are three ways to group items:
• Group selected items into specified categories.
• Automatically group numeric items
• Automatically group dates and times
􀂾 To group selected items:
Mouse
i. Select the items to group. Select adjacent items by clicking and dragging or non‐adjacent items by
selecting each item whilst holding [CTRL].
ii. Choose GROUP from the OUTLINE group on the DATA ribbon.
iii. A group is created:
iv. Repeat procedure until grouping is complete
v. Click on a group name (E.G. Group1) Then type in the name you wish to call this group in the example
above the months have been grouped and named as quarters
vi. The + and – buttons in front of the group names allow the collapse and expansion of the groups to see
the data for the subgroup.
You may even group several groups together
􀂾 To rename a group.
Mouse
i. The row labels now have a duplicate field name with a number
following the name. Click on the drop down arrow to the right of the
Excel 2007 Advanced
© The Mouse Training Company
98
field name. and select FIELD SETTINGS.
ii. the following dialog will appear
iii. Enter a new CUSTOM NAME that would best describe the group. Such as QUARTERS.
iv. Click OK
v. The field has changed names not just within the Row label area but also in the field list section where it
can be used within this report until it is ungrouped.
Excel 2007 Advanced
© The Mouse Training Company
99
􀂾 To group numerically
Mouse
i. Select a single item.
ii. Choose GROUP from the OUTLINE group on the DATA ribbon
iii. Excel displays a dialog box in which to enter starting, ending and
interval values. Enter appropriate values
iv. Click OK.
􀂾 To group a date or time in a range:
Mouse
i. Select a single item.
ii. Choose GROUP from the OUTLINE group on the DATA
ribbon
iii. Excel displays a dialog box in which to enter starting, ending
and interval values.
iv. Enter appropriate values and click OK.
If you are experiencing problems analysing list data check the following:‐
Your list is correctly set up with the first row containing the column labels identifying data in each
of the columns and no blank rows between the headings and the first row of data.
Your column headings are not ambiguous ‐ i.e. they cannot be confused with function names or
range names.
Your column headings are formatted to make them stand out from the data.
Your column headings ideally should not contain spaces ‐ you can remove the spaces completely or
replace them with an underscore (_) character.
Your criteria range should only contain a row of headings and blank rows below. The headings
must exactly match the headings at the top of your list.
Problems sometimes occur if the criteria range looks blank but perhaps has a space in it.
Excel 2007 Advanced
© The Mouse Training Company
100
Formatting A PivotTable
After you have added the fields, displayed the appropriate level of details, created calculations, and sorted,
filtered, and grouped data the way that you want in a PivotTable report, you often want to enhance the
layout and format of the report to improve readability and to make it more attractive. There are a number
of ways to change the layout and format of a PivotTable report as described in the following sections.
You can manually format a cell or cell range in PivotTable report by right‐clicking the cell or cell range, by
clicking FORMAT CELLS, and by using the FORMAT CELLS dialog box. However, you cannot use the
MERGE CELLS check box on the ALIGNMENT group in a PivotTable report.
You can also conditionally format a PivotTable report
􀂾 To apply a PivotTable style
i. Click the PivotTable report.
ii. On the DESIGN ribbon, in the PIVOTTABLE STYLES group
iii. Click a visible style, scroll through the gallery, or to see all of the available styles, click the MORE button,
at the bottom of the scroll bar.
OR
Excel 2007 Advanced
© The Mouse Training Company
101
iv. If you have displayed all of the available styles and you want to create your own custom PivotTable
style, click New PivotTable Style at the bottom of the gallery to display the New PivotTable Style dialog
box.
􀂾 to apply Banding
Mouse
v. Click the PivotTable report.
vi. On the DESIGN ribbon, in the PIVOTTABLE STYLE
OPTIONS group, either:
• To alternate each row with a lighter and darker colour format, click BANDED ROWS.
• To alternate each column with a lighter and darker colour format, click BANDED COLUMNS.
• To include row headers in the banding style, click ROW HEADERS.
• To include column headers in the banding style, click COLUMN HEADERS.
􀂾 To Change the number format for a field
Mouse
vii. In the PivotTable report, select the field for which you want to change the
number format.
viii. On the OPTIONS ribbon in the ACTIVE FIELD group, click FIELD SETTINGS.
The FIELD SETTINGS dialog box is displayed for labels and report filters, and
the VALUES FIELD SETTINGS dialog box is displayed for values.
ix. Click NUMBER FORMAT at the bottom of the dialog box. The FORMAT
CELLS dialog box is displayed.
x. In the CATEGORY list, click the format category that you want.
xi. Select the options that you want for the format, and then click OK twice.
Excel 2007 Advanced
© The Mouse Training Company
102
􀂾 Preserve or discard formatting
Mouse
i. Click the PivotTable report.
ii. On the OPTIONS ribbon, in the PIVOTTABLE group, click OPTIONS. The
PIVOTTABLE OPTIONS dialog box is displayed.
iii. Click the LAYOUT & FORMAT tab, in and look at the FORMAT section
iv. To save the PivotTable report layout and format so that it is used each time that you perform an
operation on the PivotTable, select the PRESERVE CELL FORMATTING ON UPDATE check box.
OR
v. To discard the PivotTable report layout and format and resort to the default layout and format each
time that you perform an operation on the PivotTable, clear the PRESERVE CELL FORMATTING ON
UPDATE check box.
Excel 2007 Advanced
© The Mouse Training Company
103
Excel 2007 Advanced
© The Mouse Training Company
104
SECTION 4 CHARTS
􀂾 Objectives
By the end of this section you will be able to:
• Create embedded charts
• Create separate page charts
• Change chart types and formats
• Add and remove chart data
• Add trend lines to charts
• Create picture charts
Excel 2007 Advanced
© The Mouse Training Company
105
INTRODUCTION TO CHARTING
One of the most impressive aspects of Excel is its charting ability. There are endless variations available,
allowing you to produce a chart, edit and format it, include notes, arrows, titles and various other extras as
desired. This manual will look at many of the issues involved in producing and formatting Excel charts.
Charts are based on data contained in Excel Worksheets. It is necessary to understand how Excel picks up
the data to be used in a chart because the way in which the data is laid out will influence how the chart is
presented.
Excel offers a wide range of types and formats from which you can choose when producing charts.
However, the charts themselves can exist in different forms and it is important to understand the difference
between them. The first form is an embedded chart, the second is a separate chart page.
Terminology
As a starting point, there are some terms used in charting which should be understood by you. The terms
defined below relate to the example car sales worksheet and column chart which appear beneath the table:
Data Point An individual figure on the spreadsheet which is reflected in the chart e.g.
Fred's Orion sales figure
Data Series A collection of related data points, e.g. all of Fred's figures, which will appear
on a chart as markers (bars, for example) of the same colour
Legend The "key" to the chart, identifying which patterns/colours relate to which
data series
Marker A bar, column, or slice of pie for example, representing a data point
Category The category axis appears across the bottom of a graph (pie charts excepted)
and the categories are listed here. Points within the different data series are
grouped by category
Excel 2007 Advanced
© The Mouse Training Company
106
Embedded Charts
An embedded chart appears on the worksheet where it was created. It
is an embedded object, which does not normally appear in its own
window, and has no separate existence apart from the worksheet. The
chart is saved only when the worksheet file itself is saved, and will be
printed with the worksheet in which it is embedded. You may choose
to have an embedded or separate chart at any time. All charts whether
embedded or separate are created from the INSERT ribbon in the CHARTS group.
Separate Chart Pages
A chart sheet, although linked to the worksheet whose figures it represents, exists as a separate page in a
workbook. The F11 key is very useful for creating a default chart from selected data as a new sheet within
the workbook
Some chart elements to be aware of.
Chart Element Description
Titles This is the area where you can specify the titles to have on the chart (i.e. X‐axis
“1998”, Z‐axis “GBP”
Axes Here you specify whether you want a Y/Z axis and whether you are using
timescales to plot your data
Gridlines The gridline ribbon allows you to switch on and off horizontal and vertical gridlines
Legend Use this ribbon to switch the legend on and off or reposition it
Data Labels The Data Labels ribbon allows you to display the amount each point represents or
display the label (i.e. in the example above, each cylinder would have Qtr1, Qtr2
Excel 2007 Advanced
© The Mouse Training Company
107
displayed as appropriate at the top of each data marker)
Data Table The Data Table ribbon will display a grid underneath the chart that will show the
information that is being plotted.
Three Methods To Create Charts
􀂾 To create a chart
Mouse
i. Select data for chart.
ii. Go to the CHARTS group on the INSERT ribbon. Select
a chart type and click
iii. The menu on the left appears.
iv. Hovering your mouse over a chart type will bring up an
explanation of that chart type
v. When you have chosen click once to select a chart type
vi. The chart is now created based on the selected data as
an embedded chart.
OR
Keyboard
i. Select Data for chart
ii. Press the F11 Key
iii. Default chart will created as chart on a separate sheet.
OR
i. Select data for chart.
ii. Click on the DIALOG BOX LAUNCHER on the CHARTS
group on the INSERT ribbon.
iii. The following dialog will appear
iv. Select a type from
the left hand
section and a sub
type from the right
hand section.
v. Click OK to create
the chart.
vi. This will be created
as an embedded
chart
Excel 2007 Advanced
© The Mouse Training Company
108
Moving And Resizing Embedded Charts
Once the chart object has been created and stored as an embedded object, you can move and resize it.
􀂾 To move an embedded chart:
Mouse
i. Move mouse over the chart frame border your mouse cursor should
have a four pointed black arrow
ii. Click on the chart frame border and hold the mouse button down as
you drag. Release the mouse when the chart is in the desired
location.
􀂾 To resize an embedded chart:
Mouse
i. Move your mouse over the dotted handles on the Chart frame
border.
ii. The mouse cursor should change to a double arrow.
iii. Click and Drag up, down, left or right.
Hold down the [ALT] key if you wish the chart to resize by snapping to the cell gridlines
Data Layout
Depending on the "shape" of the selected data, Excel will assign categories and data series to either the
rows or columns of information. Usually it will be assumed that there are more categories than data series,
therefore, if there are more rows than columns of selected information, the data series will be based on
columns, with the legend labels being picked up from the row across the top of the selected area and the
category labels being picked up from the leftmost column:
Excel 2007 Advanced
© The Mouse Training Company
109
If there are more columns than rows in the selected area, the data series will be based on rows, with the
legend labels being picked up from the leftmost column and the category labels taken from the top row of
the selected area:
If the number of rows and columns is the same, Excel will opt for data series in rows. It is possible to
override the choice made by Excel in how the data series and categories are decided. Details of this
procedure will be found under the section on manipulating data.
Excel 2007 Advanced
© The Mouse Training Company
110
Shortcut Menu (Right Click)
You may be familiar with the Shortcut menus associated with the selected
cell(s) on the Excel worksheet. When working on a chart ‐ embedded either
on a worksheet or in its own window, clicking on the chart with the secondary
mouse button will call up a Charting Shortcut menu.
The Shortcut menu will contain a selection of choices from some of the
Standard Menu bar options mostly relating to the chart as an embedded
object ‐ almost like a graphic on the worksheet.
Chart Types
There are several different types of chart available within Excel. The type to choose will vary depending on
the data involved and what information the chart is intended to convey or highlight. Practice will improve
your instinct on which type of chart to use in each instance. Initially it may be useful to try different types
until the result is reasonably close to your requirements, and then add custom formats and elements as
desired. Some chart types are very specialised and may only be of use to particular business sectors.
􀂾 Available Types Of Chart
Selecting any of the types listed will apply a given chart type to the active chart. The most useful types
available and some of their applications have been summarised below:
Area
Area charts can be 2 or 3‐dimensional. They are used to compare the change in volume of a data
series over time, emphasising the amount of change rather than the rate of change. Area charts
show clearly how individual data series contribute to make up the whole volume of information
represented in the graph.
Bar
Bar charts can be 2 or 3‐Dimensional. They are used to show individual figures at a specific time
or to compare different items. Categories are listed vertically, so that bars appear on the
horizontal, thus there is less emphasis on time flow. Bars extending to the right represent
positive values while those extending left represent negative values.
Column
Column charts can be 2 or 3‐Dimensional. They are frequently used to show variation of different
items over a period of time. Categories (often days or months for example, representing a
progression of time) are listed horizontally and columns are displayed side by side, making for easy
comparisons. Two variations on the theme of Column charts are represented by further tools on the Chart
toolbar. The Stacked Column chart can be used to show variations over a period of time, but also shows
how each data series contributes to the whole. A further variation on the 3‐D column chart produces 3‐D
columns in a 3‐D plot area, receding away from the viewer.
Line
Line charts can be 2 or 3‐Dimensional. Line charts are used to compare trends over time. There
are similarities with Area charts, but line charts tend to emphasise the rate of change rather than
Excel 2007 Advanced
© The Mouse Training Company
111
volume of change over time. 3‐D lines appear as "ribbons" which can be easier to see on the chart.
Excel 2007 Advanced
© The Mouse Training Company
112
Pie
Pie charts can be 2 or 3‐Dimensional. They are used to compare the size of the parts with the
whole. Only one data series can be plotted, making up 100%. Pie charts within their own
window can be made to "explode" by dragging one or more pieces of pie away from the centre.
Radar
Each category in a radar chart has its own axis radiating from the centre point. Data points are
plotted along each spoke, and data points belonging to the same series are connected by lines.
Xy Scatter Charts
XY charts are used to compare two different numeric data series, and can be useful in determining whether
one set of figures might be dependent on the other. They are also useful if the data on the X axis
represents uneven intervals of time or increments of measurement.
3‐D Surface
3‐D Surface charts present information in an almost topographical layout. They can be used to pinpoint the
high and low points resulting from two changing variables. It can be helpful to think of a 3‐D surface chart
as a 3‐D Column chart which has had a rubber sheet stretched over the tops of the columns.
Combination
A combination chart allows you to overlay one 2‐Dimensional chart type on top of another. This can be
useful for comparing different types of data, or for charting data requiring two different axis scales. Once
the combination chart has been set up, the actual type of the main or overlay chart can be changed by you.
􀂾 To change the chart type:
Mouse
i. Click on chart to be changed..
ii. Go to the CHARTS group on the INSERT ribbon. Select a chart type and click
iii. Hovering your mouse over a chart type in the menu will bring up an explanation of that chart type
iv. When you have chosen click once to select a chart type
v. Your chart will have changed
OR
i. Click on the DIALOG BOX LAUNCHER on the CHARTS group on the INSERT ribbon. The INSERT
CHART dialog will appear
ii. Select a type from the left hand section and a sub type from the right hand section. Click OK to change
the chart type
OR
i. Right click on the chart to call up the shortcut menu Click on CHANGE CHART TYPE
ii. The INSERT CHART dialog will appear Select a type from the left hand section and a sub type from the
right hand section. Click OK to change the chart type
Excel 2007 Advanced
© The Mouse Training Company
113
Excel 2007 Advanced
© The Mouse Training Company
114
OR
i. Click on CHANGE CHART TYPE on the TYPE group on the DESIGN ribbon. The CHANGE CHART
TYPE Dialog box will appear
ii. Select a new chart type
iii. Click OK
Default Chart Type
The default graph setting in Excel is set
to a simple 2‐dimensional column
chart, however you can change the
default to any of the types offered
within the chart type dialog.
􀂾 To set the default chart
type:
Mouse
i. Click on the DIALOG BOX
LAUNCHER on the CHARTS group
on the INSERT ribbon.
ii. The following dialog will appear
iii. Select a type from the left hand section and click on the specific format that you want the chart to have
from the gallery of pictures on the right.
iv. Click the SET AS DEFAULT CHART button.
v. New charts created from now on will use the default format as defined by you when pressing F11
Excel 2007 Advanced
© The Mouse Training Company
115
FORMATTING CHARTS
There are several different ways of formatting the various elements in a chart. Some formats, such as
adding a legend can be applied to a chart using the following sections
Calling up the Shortcut menu on a Chart will also allow you to access the dialog boxes which can be used to
change formatting on the entire chart.
Design Ribbon
The DESIGN ribbon is to change some very basic aspects of your chart globally for the chart we have
already looked at changing the chart type. We will look at creating some of our own later on
􀂾 To change data source
Mouse
i. Click on Chart.
ii. Click on SELECT DATA in the DATA group the following dialog will appear
iii. In the CHART DATA RANGE box a highlighted range will be seen.
iv. If you need a completely new range then delete the values in this box and select a different range for
your chart.
v. Click on OK.
Be sure to include the row and column labels in this range. If you wish you may select more than
one range by holding down the [CTRL] key down after you have selected your first range and then
select another range.
Excel 2007 Advanced
© The Mouse Training Company
116
􀂾 To add or remove a series or category.
Mouse
i. Click on Chart.
ii. Click on SELECT DATA in the DATA group the SELECT DATA SOURCE dialog will appear
i. In the LEGEND ENTRIES (SERIES) box click on ADD the EDIT SERIES dialog will appear.
ii. In the series name box select the cell that holds the
series Label
iii. In the series values box select the range of cells that will
make up the data for that series.
iv. Click OK
v. In the HORIZONTAL (CATEGORY) AXIS LABELS box click on EDIT
vi. The AXIS LABELS dialog will appear
vii. Reselect the range that will include any new category
labels.
viii. Click OK. and OK again to apply the new data to your chart.
􀂾 To delete a series
Mouse
i. Click on Chart.
ii. Click on SELECT DATA in the DATA group the SELECT DATA SOURCE dialog will appear
iii. Select the series you wish to delete.
iv. Click on DELETE the series will be removed.
Excel 2007 Advanced
© The Mouse Training Company
117
􀂾 To delete a category
i. Click on Chart.
ii. Click on SELECT DATA in the DATA group the SELECT DATA SOURCE dialog will
appear
iii. Click on the SWITCH ROW/COLUMN button. What was a category has now become a series
iv. Now delete series as previously explained.
v. Click on the SWITCH ROW/COLUMN button on the dialog box. What were series have now become
categories with the category you wished, removed.
Switch Rows And Columns
􀂾 To switch between rows and columns
Mouse
i. Click on Chart.
ii. Click on SELECT DATA in the DATA group the SELECT DATA SOURCE dialog will appear
iii. Click on the SWITCH ROW/COLUMN button. What was a category has now become a series
OR
Mouse
i. Click on chart.
ii. Click on Chart.
iii. Click on SWITCH ROW/COLUMN in the DATA group
iv. Your data has now switched rows to columns
This facility may not be available if multiple data ranges have been selected for your chart
especially if they are different sizes from different locations.
Changing The Chart Layout
As discussed earlier a chart is made up from many elements that can be turned on or off depending on the
type of chart or arranged in different places on the chart. To change the layout swiftly instead of laboriously
changing each element the change layout tool allows some quick global options.
􀂾 To change the chart layout
Mouse
i. Click on the drop down arrow on the CHART LAYOUT
group
ii. Select a chart Layout
Excel 2007 Advanced
© The Mouse Training Company
118
iii. The layout is applied
Chart Styles
A chart style is mainly a theme of formatting for your chart using the existing elements of your chart There
are some very exciting colour schemes in this feature
􀂾 To apply a chart style
Mouse
i. Select chart
ii. Click on the drop down arrow to the right of the CHART STYLES group.
iii. Select a chart style
iv. Your chart should now adopt the style chosen.
If the chart style is not to your liking apply another style following the same method until you have
a style close to what you wish. We will look at formatting the various elements in a later chapter
to achieve exactly what you want
Moving Chart Location
If you usually use one method to create a chart you will regularly get either a separate sheet
chart or an embedded one and you may wish to switch between the two types. Or move your
embedded chart to a different sheet within your workbook.
􀂾 To move embedded chart between sheets
Mouse
i. Click on MOVE CHART on the LOCATION
group. A dialog will appear.
ii. Click on drop down arrow to the right of
OBJECT IN select the sheet you wish to
move it to
iii. Click OK
􀂾 To switch between embedded and separate sheet
Mouse
i. Click on MOVE CHART on the LOCATION group. A dialog will appear.
ii. Click on New sheet
iii. Name sheet in the text box
iv. Click on OK
v. Embedded chart will now be on a separate sheet with the given name.
To create an embedded chart from a separate sheet chart simply CHOOSE AS OBJECT in the dialog
box and select a sheet to place it as an embedded object.
Excel 2007 Advanced
© The Mouse Training Company
119
Layout Ribbon
The Layout ribbon allows us to format, add or remove various elements of a chart. Some tools are only
available, however for certain types of chart. E.G. you cannot apply 3D rotation to a 2D chart.
Formatting Chart Elements
􀂾 To select and format a chart element.
Mouse
vi. Go to the LAYOUT ribbon.
vii. To select an element of your chart, click on the drop down arrow
to the right of top box in the CURRENT SELECTION group.
viii. Click on a chart element. That element will be selected
ix. Click on FORMAT SELECTION in the CURRENT SELECTION
group, the following dialog will appear.
x. The left hand section of the dialog will give the various categories
of how you may format your selection (These options may vary
depending on the selection.)
xi. The right hand section contains the available formats for that
category.
xii. Clicking on each category and setting your format choices will
immediately affect you chart.
xiii. When you are satisfied with your formatting choices, click CLOSE.
Many of the options displayed in the category options section may
involve other drop down boxes to make a selection. (see picture) if
you move your mouse over these possible selections a help tip should
appear to give you a description of that choice BEFORE actually
making a selection. Any choice already applied will already be
selected and have a different colour.
Excel 2007 Advanced
© The Mouse Training Company
120
Resetting Custom Formats
When experimenting with various formats you may find it difficult to
remember exactly what settings were applied to a specific element and
therefore you would find it difficult to make it appear as it once was.
Resetting the format of specific chart elements can be very useful.
􀂾 To reset an element
Mouse
i. Make a selection of element to be reset
ii. Click on RESET TO MATCH STYLE button on the CURRENT
SELECTION group.
iii. The selected element will revert back to the original format settings of the applied chart style.
Adding, Removing And Formatting Labels
Information labels on your chart are very important on your chart
especially if it is on a separate sheet. The LABELS group offers a
selection of labels you may wish to show or hide on your chart. The
chart layout choices previously explained uses a mixture of labels in
different locations on your chart but you may wish to put specific labels
on your chart and format them yourself and place them where you wish.
􀂾 To add or remove labels.
Mouse
i. Select chart if embedded
ii. Click on drop down arrow of type of label you wish to add or remove
from the LABELS group.
iii. Make a selection from choices present.
iv. Label will appear or disappear dependant on choice
e.g.
v. Use the Data Labels button on the LABELS to write the values or the labels on the data markers.
vi. Use the Data Table button on the LABELS to add the plot data so that it is visible on the chart itself.
􀂾 To format labels
Mouse
i. Select label element from drop down box in the CURRENT SELECTION group as mentioned previously.
ii. Click on FORMAT SELECTION in the CURRENT SELECTION group as mentioned previously.
Excel 2007 Advanced
© The Mouse Training Company
121
􀂾 To edit label text
Mouse
i. Select label as previously discussed.
ii. Click within the label and delete and retype with the text you require.
iii. Click off label
􀂾 To move or resize chart elements
Mouse
i. Make a selection of a chart element. (E.G. a label)
ii. Handles will appear at each corner to show selection.
iii. Moving mouse over label border should show a 4 pointed Black arrow. This appears to indicate that you
are in the right position to click and drag to move the selected element.
iv. Clicking within the label to edit the text the label will automatically resize to the size of the text entered
Selecting an element like the PLOT AREA will allow a double black arrow when moving over a
handle. Clicking and dragging will resize that element.
Axes
For various types of charts you may not wish to see both axes on the chart you are
able to hide or show these axes dependant on your needs.
􀂾 To add or remove axes from chart
Mouse
i. Select chart if embedded.
ii. Click on drop down button on AXES button on AXES group
iii. Select PRIMARY HORIZONTAL or PRIMARY VERTICAL
AXIS.
iv. Make a selection from choices shown.
Selecting more primary horizontal or vertical options opens the
formatting dialog which would appear if you selected the axis
and formatted it. Using the axes menu is best for turning it off or
on.
􀂾 To format the Category (X) Axis:
Mouse
i. Under TICK MARK TYPE, you may click on the appropriate option button to specify that tick marks on
the axis will appear on the inside or outside of the axis line, cross the axis line, or not appear at all.
Minor tick marks can also be included (click on the Scale... button to set the intervals for major and
minor tick marks).
Excel 2007 Advanced
© The Mouse Training Company
122
ii. The TICK LABELS section allows you to dictate where the Labels associated with the selected axis will
display. This can be at the High Values end of the axis, the low values end of the axis, next to the axis, or
completely suppressed.
iii. Use the SCALE ribbon to specify at where the value axis will appear, which categories are labelled and
how many categories will appear between each pair of tick marks.
iv. A series of boxes [a]use automatic positioning. The default setting is to have this box checked, which
produces a Value Axis at the edge of a given category. Putting a value in the next box down will result in
a Value Axis which cuts down the middle of a category. This will also affect the location of tick marks on
the axis. Categories may be displayed in reverse order if desired, and the Value Axis may be required to
cross at the last plotted category on the chart.
v. Use the ALIGNMENT category to specify the orientation of the category labels.
􀂾 To format the Value (Y) axis:
Mouse
i. Follow steps described above for the category axis.
ii. The SCALE ribbon will have different options relating to the values on the axis.
iii. From the SCALE ribbon, you may specify the Minimum and Maximum values to appear on the axis. The
intervals to be used as Major and Minor units on the axis may also be set. You may dictate the point at
which the value and category axes cross, whether or not the axes are plotted on a Logarithmic Scale, or
whether to have the values plotted in Reverse order.
Gridlines
Gridlines are the indicator lines that run across your chart to either divide up your categories or give visual
help when deciding on a value for a data point more distant from the value axis. You may need more, or
less of these, dependant upon your needs for accuracy or visual impact.
􀂾 To change gridline options
Mouse
iv. Select chart if it is embedded.
v. Click on drop down arrow on GRIDLINES on the AXES
group.
vi. Choose PRIMARY HORIZONTAL or PRIMARY VERTICAL
GRIDLINES and make a selection from the choices given.
Unattached Text
Floating text may be typed directly onto the Chart, then dragged to the desired position.
􀂾 To add floating text to a chart:
Mouse
i. Go to the INSERT ribbon and select the TEXT BOX button click on your chart and a text box will appear.
Type required text, resize and format text box and drag to required location.
Excel 2007 Advanced
© The Mouse Training Company
123
Format Dialog
􀂾 Element options
This category varies dependent on what is selected it may
show AXIS, CATEGORY OR SERIES OPTIONS. For
SERIES OPTIONS it allows you to change the width of the
column or gap between the series. AXIS OPTIONS allows
you to specify widths and separation options where the
axis begins and ends (if available)
􀂾 Fill
Use the FILL category to specify background colours or
designs.
􀂾 Shape
Use this category to set the shape for a selected element
(series or data point if available)
􀂾 Borders
Select the BORDER colour to change the border colour
and set a border
Set the BORDER STYLES category to add a border around the outside of the selected element
􀂾 Shadow
This option allows you to set the shadow depth , colour and direction for the selected element.
􀂾 3‐D Format
If you have a chart that has a 3‐D format this category will allow you to change many aspects of the 3‐D
appearance such as the material, lighting, contour, depth and bevel.
Depending on the data being displayed, some data markers on a 3‐D chart may be obscured. It is possible
to adjust the view so that your data may be seen to its best advantage. You may influence the degree of
elevation, perspective or rotation of your chart. A sample chart within the 3‐D view dialog box reflects the
new views as you change these factors.
Elevation and Rotation can be adjusted either by typing values into the appropriate sections within the
dialog box, or by clicking on the arrow buttons displayed around the sample chart. The latter technique is
obviously easier.
Elevation dictates the height from which you view the data. Ranging from 90°(above the plot area) to ‐
90°(below the plot area), where 0° represents a view level with the centre of the plot area. With 3‐D Pie
Charts, the range varies from 10°, almost level with the edge of the pie, to 80°, looking down on the
surface of the pie.
Excel 2007 Advanced
© The Mouse Training Company
124
􀂾 3‐D Rotation
Selecting the plot or chart area will allow you to rotate your chart in any direction or change the perspective
of your chart.
Rotation
Rotation allows you to turn the graph on its vertical axis. The range goes from 0° to 360°, where zero
views the chart from the front, 90°would view it from the side, and 180° would allow you to see it from the
back ‐ effectively reversing the order of the data series for the chart display.
Perspective
Perspective can be changed to make the data at the back of a 3‐D chart appear more distant. A perspective
of zero means that the farthest edge of the chart will appear as equal in width to the nearest edge.
Increasing perspective (up to a maximum of 100) will make the farthest edge appear proportionally smaller.
You may also affect the height of the graph in relation to its width and whether or not you want the axes to
remain at right angles. This latter setting would preclude the use of perspective in 3‐D charts. Auto‐scaling
allows Excel to scale a 3‐D chart so that, where possible, it is similar in size to its 2‐D equivalent.
􀂾 Font
The font for any selected textual element can be set on the HOME ribbon from the FONT group or right
clicking on the highlighted text and using the mini toolbar.
􀂾 Formatting The Legend
The Legend can be selected and formatting like the other chart elements The legend can be positioned
manually simply by pointing and dragging it to a new position on the chart, but there are some preset
positions which can be selected from legend button in the LABEL group
Note that the legend cannot actually be resized. Changing the font size will cause the size of the
overall legend to adjust, but it cannot be resized by dragging on the selection handles. No chart
element which shows white selection handles (rather than the usual white) can be resized by
dragging. Dragging the legend to a new position on the chart will sometimes affect the shape of
the legend and the size of the chart. The legend may be placed overlapping the chart. Note that
the text appearing in the Legend box is picked up from the worksheet data. Edit the text on the
worksheet in order to change the legend text (The legend may be deleted(hidden) by selecting it
and pressing the Delete key on the keyboard.
Excel 2007 Advanced
© The Mouse Training Company
125
Add A Series Manually
􀂾 Other methods To add a new data series to a chart:
Mouse
i. Select the worksheet cells containing the relevant data (including the label to be used if labels were
included in the original data).
ii. Copy this data to the clipboard in the usual way.
iii. Activate the chart by clicking on it and choose Edit, Paste. The data series will appear in the chart.
Or
i. Select the worksheet cells containing the relevant data
ii. If Chart is an embedded chart on current sheet. Drag and drop selected data onto chart.
The added series will invariably come in as the final data series, but the order can be changed by
you as outlined later in this document.
The Series Function
If a data series on a chart is selected, the reference area will display the underlying formula. It can be useful
to know what elements go to make up the Series function, as you may edit it manually if desired. The Series
function includes four arguments:
=SERIES(Series_Name,Categories_Ref,Values_Ref,Plot_Order)
The Series Name can be a reference (Worksheet!Cell) to the cell where the name of this particular data
series is being held, or it may consist of text typed in by you and enclosed in quotation marks. The Series
Name will be picked up in the legend to describe the data series. The Categories Reference refers to the
worksheet name and range of cells where the Category (or x‐axis) labels are to be found. If the data series
are in rows, the category references will refer to the labels at the top of each column and vice versa. The
Values Reference refers to the worksheet name and the range of cells containing the actual values for this
data series which are to be plotted on the y‐axis (or z‐axis on a 3‐D chart). The Plot Order number dictates
the order in which the selected data series is plotted on the chart and listed on the legend. Often, instead
of amending the Series function manually, you may find it easier to edit a data series using the dialog option
covered in the earlier section.
Charting With Blocks Of Data
As it is possible to select separate ranges in Excel, it is possible to produce charts based on non‐contiguous
data. This is vital if some of the information on the worksheet is to be omitted. There are some guidelines
to be aware of however. The layout of data is important as was demonstrated at the beginning of this
document. The selected ranges must amount to a regular block with consistent height and width
measurements so that Excel can interpret it correctly, with categories and data series matching up. Once
the data has been successfully selected, choose File, New and click on Chart before clicking on OK, or tap
[F11]. An extension of this idea leads to the fact that ranges from separate worksheet files can be included
in a single chart. Simply select the worksheet data to be included (subject to the layout provisos above),
copy to the clipboard then paste them into the chart.
Excel 2007 Advanced
© The Mouse Training Company
126
SECTION 5 TEMPLATES
􀂾 Objectives
By the end of this section you will be able to:
• Use the standard Excel templates
• Create Custom templates
• Open and edit templates
• Set template properties
Excel 2007 Advanced
© The Mouse Training Company
127
INTRODUCTION TO TEMPLATES
A template is a file used as a form to create other workbooks, sheets and charts. New workbooks created
from the template contain the same layout, data, formulae, formats and styles as those of the template.
New sheets and charts inserted into a workbook are a copy of the sheet or chart template.
Standard Templates
Excel comes supplied with a selection of templates designed to help in the production of common business
and home financial tasks. These templates can be modified for personal use.
􀂾 To use a standard template:
Mouse
i. Click on the MICROSOFT OFFICE BUTTON and select the NEW button, the dialog above will appear
ii. Click the INSTALLED TEMPLATES button on the left to see a list of Excel templates in the centre
section of the dialog
iii. Click on the Picture for the template you wish to base the new workbook on to see a preview on the
right.
iv. When you have located the template you wish to use click CREATE.
Excel 2007 Advanced
© The Mouse Training Company
128
OR
i. Click on the MICROSOFT OFFICE BUTTON and select the NEW button, the dialog above will appear
ii. Click under the MICROSOFT OFFICE ONLINE button on the left on a section pertaining to your need to
see a list of appropriate Excel templates in the centre section of the dialog.
iii. Click on the Picture for the template you wish to base the new workbook on to see a preview on the
right.
iv. When you have located the template you wish to use click CREATE.
N.B. You must be connected to the internet to use templates from Office online.
Custom Templates
You can create your own workbook and sheet templates.
􀂾 To create a workbook template:
Mouse
i. Open or create the workbook to be used as the basis for the template.
ii. Click on the MICROSOFT OFFICE BUTTON and select the SAVE AS button, click on EXCEL
WORKBOOK, the save as dialog above will appear
iii. Type the template name in the FILE NAME text box.
iv. Select EXCEL TEMPLATE from the SAVE AS TYPE list. An .XLTX extension will be added to the
template name.
v. The folder where the file will be stored will automatically change to Templates. By storing all .XLTX files
in the same folder Excel recognises and keeps track of templates.
Excel 2007 Advanced
© The Mouse Training Company
129
vi. Click SAVE to save the template.
􀂾 To create a worksheet template:
Mouse
i. In a new or existing workbook delete all the sheets except the one to be used as the template.
ii. Click on the MICROSOFT OFFICE BUTTON and select the SAVE AS button, click on EXCEL
WORKBOOK, the save as dialog will appear
iii. Type the template name in the FILE NAME text box.
iv. Select EXCEL TEMPLATE from the SAVE AS TYPE list. An .XLTX extension will be added to the
template name.
v. The folder where the file will be stored will automatically change to Templates. By storing all .XLTX files
in the same folder Excel recognises and keeps track of templates.
vi. Click SAVE to save the template.
􀂾 To base a new workbook on a template:
Mouse
i. Click on the MICROSOFT OFFICE BUTTON and select the NEW button, the NEW WORKBOOK dialog
will appear
ii. Click the MY TEMPLATES button on the left a dialog will appear.
iii. Click on the Picture for the template you wish to base the new workbook on to see a preview on the
right.
iv. When you have located the template you wish to use click CREATE.
􀂾 To add a worksheet based on a sheet template:
Mouse
v. Click the right mouse button over a sheet tab and choose INSERT.
vi. click the icon for the worksheet template you want to base the new sheet on.
Excel 2007 Advanced
© The Mouse Training Company
130
vii. Click OK
AutoTemplates
An autotemplate is a workbook saved as a template in the XLSTART folder or alternate Startup folder using
the specific filename Book.xltx, sheet.xltx, dialog.xltx or macro.xltx. AutoTemplates if they exist will act as
the basis for all new items you create in the Excel environment.
The Book.xltx template becomes the default workbook. The Sheet.xltx template becomes the default
worksheet.
Opening And Editing Templates
Templates are files just like workbooks. If you need to change a template in any way, simply open, edit and
save in the normal way.
􀂾 To open a template:
Mouse
i. Click on the MICROSOFT OFFICE BUTTON and select the OPEN button
ii. Change the LOOK IN location to the Templates folder.
iii. Select the name of the template you want to open. click OPEN
􀂾 To edit a template:
Mouse
i. Make changes in the open template.
ii. Click on the MICROSOFT OFFICE BUTTON and select the SAVE button
Excel 2007 Advanced
© The Mouse Training Company
131
Template Properties
Files have various properties that are saved with them. Some properties, such as date created and last
saved date are included and updated automatically by Excel. Others, such as title and subject must be
entered manually by the user. Properties are helpful when trying to locate files as you can use the
properties to search.
􀂾 To set template properties:
Mouse
i. With the template open Click on the MICROSOFT OFFICE BUTTON and select the PREPARE button
and select PROPERTIES.
ii. .A section (above) will appear under the formula bar.
iii. Set the properties that apply to the template.
iv. Click X.in right hand corner of this bar to close
v. If you have further properties to set. Click on document
properties on the bar and choose ADVANCED
PROPERTIES the dialog on the right will appear
vi. the 5 separate tabs allow the input of many more pieces of
information.
vii. When complete click on OK
Excel 2007 Advanced
© The Mouse Training Company
132
Templates can contain custom number formats and predefined styles.
Excel 2007 Advanced
© The Mouse Training Company
133
SECTION 6 AUDITING
􀂾 Objectives
By the end of this section you will be able to:
• Use and understand tracers
• Insert and use cell comments
• Use Watch window
• Use go to special
Excel 2007 Advanced
© The Mouse Training Company
134
AUDITING FEATURES
Tracers
The precedent, dependent and error tracers display arrows on the worksheet to represent the flow of
computations: The can be found The FORMULA AUDITING group and on the FORMULAS ribbon.
Tracer Type Arrow type
Formula Solid blue
Error Solid red
External reference or reference to other worksheet Dashed black with an icon
COMMENTS
Comments are text or audio messages attached to cells usually
giving a more detailed explanation of a cells content.
GO TO SPECIAL
A method of selecting cells with particular contents or properties.
PRECENDENTS AND DEPENDANTS
Precedents are cells referred to by other formulae. Dependants are cells containing formulae that refer to
other cells.
A direct precedent is a cell referred to by the formula in the active cell. An indirect precedent is a cell
referred to by a formula in a direct precedent cell or another indirect precedent cell.
A direct dependant is a cell containing a formula that refers to the active cell. An indirect dependent is a
cell that contains a formula that refers to a direct dependant cell or another indirect dependant cell.
You use the Auditing toolbar to set auditing options.
WATCH WINDOW
Is a useful tool which allows you to watch the values of calculated cells on other sheets while you change
values that they are dependant on, on a completely different sheet.
􀂾 To set a watch
Mouse
i. Select a cell to which you want to add a Watch
ii. Go to the FORMULAS ribbon an click on WATCH WINDOW in the FORMULA AUDITING group
iii. Click on ADD WATCH
iv. Select cell you wish to monitor
Excel 2007 Advanced
© The Mouse Training Company
135
v. Click on ADD
vi. You may now switch windows or scroll and the watch window will monitor that cell for changes in result
as you enter or manipulate figures elsewhere.
􀂾 To delete a watch
Mouse
i. Go to the FORMULAS ribbon an click on WATCH WINDOW in the FORMULA AUDITING group
ii. Select watch to delete
i. Click on DELETE WATCH on watch window
􀂾 To audit for direct and indirect precedents:
Mouse
i. Click on the cell you want to audit.
ii. Click the trace precedents button on the Auditing toolbar.
􀂾 To audit for direct and indirect dependants:
Mouse
i. Click on the cell you want to audit.
ii. Click the trace dependant’s button on the Auditing toolbar.
Click either button a second time to view indirect precedents or dependants of the active cell.
􀂾 To remove the tracer arrows:
Mouse
i. Click the remove precedent arrows to remove precedent tracers or
remove dependant arrows to remove dependant tracers.
Or
ii. Click the remove all arrows button to remove both precedent and dependant tracers.
Excel 2007 Advanced
© The Mouse Training Company
136
Comments
When a cell has a comment attached, CellTips automatically
display the comments added when the mouse pointer rests
over the cell. You can tell which cells have c comments
attached as they have a comment indicator (a small red corner
triangle).
􀂾 To add a comment:
Mouse
i. Click in the cell where you want to insert a comment.
ii. Choose COMMENT from the REVIEW ribbon
iii. In the text box that appears attached to the cell, type your comment.
iv. Click outside the text box to confirm the entry and close the text box.
􀂾 To view comments:
Mouse
i. Position your mouse over the top of the cell that contains the comment. Shown with a tiny coloured
triangle in top of the cell corner.
ii. Your comment will be displayed as a CellTip.
􀂾 To edit comments:
Mouse
i. Click in the cell where you want to edit a comment.
ii. Choose EDIT COMMENT from the REVIEW ribbon
iii. In the text box edit your comment.
iv. Click outside the text box to confirm the entry and close the text box.
􀂾 Hiding and redisplaying comments:
Mouse
i. You can hide an individual comment by clicking the right mouse button over
the cell that contains the comment and choosing SHOW/HIDE
COMMENT from the shortcut menu.
OR
ii. Click on SHOW/HIDE COMMENTS in the COMMENTS group on the REVIEW ribbon
iii. When you want to redisplay all individually hidden COMMENTS click on SHOW ALL COMMENTS in
the COMMENTS group on the REVIEW ribbon
Excel 2007 Advanced
© The Mouse Training Company
137
􀂾 To print comments:
Mouse
i. On the PAGE LAYOUT ribbon, in the PAGE SETUP
group, click the dialog box launcher next to PAGE
SETUP.
ii. On the SHEET tab, in the COMMENTS box, click AS DISPLAYED ON SHEET or AT END OF SHEET.
iii. Click PRINT.
To see how comments are printed, you can click Print Preview before you click Print.
􀂾 Reviewing comments:
Mouse
i. Go to the COMMENTS group on the REVIEW ribbon.
ii. To view each comment in sequence click the NEXT COMMENT button.
iii. To view the comments in reverse order, click the PREVIOUS COMMENT button.
Excel 2007 Advanced
© The Mouse Training Company
138
Excel 2007 Advanced
© The Mouse Training Company
139
SECTION 7 OTHER FORMATTING
FORMATTING CELLS
Themes
You can quickly and easily format an entire document to give it a professional and modern look by applying
a document theme (A set of unified design elements that provides a look for your document by using
colour, fonts and graphics.). A document theme is a set of formatting choices that include a set of theme
colours, a set of theme fonts (including heading and body text fonts), and a set of theme effects (including
lines and fill effects).
Excel provides several predefined document themes, but you can also create your own by customizing an
existing document theme, and then by saving it as a custom document theme. Document themes are
shared across Office programs so that all of your Office documents can have the same, uniform look.
􀂾 To Apply a document theme
You can change the document theme that is applied by default in Office programs, such as Word, Excel and
PowerPoint, by selecting another predefined document theme or a custom document theme. Document
themes that you apply immediately affect the styles (A combination of formatting characteristics, such as
font, font size, and indentation, that you name and store as a set. When you apply a style, all of the
formatting instructions in that style are applied at one time.) that you can use in your document.
Mouse
i. On the PAGE LAYOUT ribbon, in the THEMES group, click
THEMES.
ii. To apply a predefined document theme, click the document
theme that you want to use under BUILT‐IN.
iii. To apply a custom document theme, click the document theme that you want to use under CUSTOM.
Custom is available only if you created one or more custom document themes
iv. If a document theme that you want to use is not listed, click BROWSE FOR THEMES to find it on your
computer or a network location.
v. To search for other document themes on Office Online, click SEARCH OFFICE ONLINE.
Excel 2007 Advanced
© The Mouse Training Company
140
Customising A Theme
The collection of fonts, colours and effects that make up a theme can all be customised and then saved as a
custom theme
􀂾 To customise a theme
i. Click on the drop down arrow next to the aspect of the theme you wish to change colours, fonts or
effects.
ii. Make a selection from the choices present.
iii. Click on themes and save current theme
Excel 2007 Advanced
© The Mouse Training Company
141
iv. Clicking on the THEMES button in future will display CUSTOM, to allow you to use this theme in future
workbooks.
􀂾 To set custom colour schemes for a theme
Mouse
i. Click on the drop down arrow next to the COLOURS button.
ii. Select CREATE NEW THEME COLOURS at the bottom of the menu
iii. The dialog above is displayed
iv. Select new colours for the various parts of your theme. A sample is shown on the right to show you how
the colours appear together
v. Enter a name for your colour scheme in the NAME box
vi. Click on SAVE.
vii. Next time you click on the colours button it will display CUSTOM and the
sets of colours you have created.
􀂾 To create custom font schemes for a theme.
Mouse
i. Click on the drop down arrow next to the FONTS button.
Excel 2007 Advanced
© The Mouse Training Company
142
ii. Select CREATE NEW THEME FONTS at the bottom of the menu
iii. The dialog above is displayed
iv. Select Fonts for the heading and a font for the body text see
sample on right of dialog.
v. Enter a name for your font scheme in the NAME box
vi. Click on SAVE.
vii. Next time you click on the FONTS button it will display
CUSTOM and the sets of fonts you have created.
Styles
To apply several formats in one step, and to ensure that cells have consistent formatting, you can use a cell
style. A cell style is a defined set of formatting characteristics, such as fonts and font sizes, number formats,
cell borders and cell shading. To prevent anyone from making changes to specific cells, you can also use a
cell style that locks cells. Microsoft Office Excel has several built‐in cell styles that you can apply or modify.
You can also modify or duplicate a cell style to create your own, custom cell style.
Cell styles are based on the document theme that is applied to the entire workbook. When you
switch to another document theme, the cell styles are updated to match the new document theme.
How to select cells, ranges, rows, or columns
To select Do this
A single cell Click the cell, or press the arrow keys to move to the cell.
A range of cells Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while
you press the arrow keys to extend the selection.
You can also select the first cell in the range, and then press F8 to extend the selection by
using the arrow keys. To stop extending the selection, press F8 again.
A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in
the range. You can scroll to make the last cell visible.
All cells on a
worksheet
Click the SELECT ALL button. To select the entire worksheet, you
can also press CTRL+A. If the worksheet contains data, CTRL+A
selects the current region. Pressing CTRL+A a second time
selects the entire worksheet.
Nonadjacent cells or
cell ranges
Select the first cell or range of cells, and then hold down CTRL while you select the other
cells or ranges. You can also select the first cell or range of cells, and then press SHIFT+F8
to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to
the selection, press SHIFT+F8 again. You cannot cancel the selection of a cell or range of
cells in a nonadjacent selection without cancelling the entire selection.
An entire row or
column
Click the row or column heading.
Row heading
Column heading
You can also select cells in a row or column by selecting
the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).If the row or column
contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell.
Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.
Excel 2007 Advanced
© The Mouse Training Company
143
Adjacent rows or
columns
Drag across the row or column headings. Or select the first row or column; then hold
down SHIFT while you select the last row or column.
Nonadjacent rows or
columns
Click the column or row heading of the first row or column in your selection; then hold
down CTRL while you click the column or row headings of other rows or columns that you
want to add to the selection.
The first or last cell in
a row or column
Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or
LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).
The first or last cell
on a worksheet or in
a Microsoft Office
Excel table
Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains
data or formatting.
Cells to the last used
cell on the worksheet
(lower‐right corner)
Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to
the last used cell on the worksheet (lower‐right corner).
Cells to the beginning
of the worksheet
Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to
the beginning of the worksheet.
More or fewer cells
than the active
selection
Hold down SHIFT while you click the last cell that you want to include in the new
selection. The rectangular range between the active cell and the cell that you click
becomes the new selection.
To cancel a selection of cells, click any cell on the worksheet.
􀂾 To Apply a cell style
Mouse
i. Select the cells that you want to format.
ii. On the HOME ribbon, in the STYLES group, click CELL STYLES.
iii. Click the cell style that you want to apply.
􀂾 To create custom cell style
Mouse
i. On the HOME ribbon, in the STYLES group, click CELL STYLES.
ii. Click NEW CELL STYLE.
iii. In the STYLE NAME box, type an appropriate name for the new cell style.
iv. Click FORMAT.
v. On the various tabs in the FORMAT CELLS dialog box, select the formatting that you want, and then
click OK.
vi. In the STYLE dialog box, under STYLE INCLUDES (BY EXAMPLE), clear the check boxes for any
formatting that you don't want to include in the cell style.
vii. To remove a cell style from selected cells without deleting the cell style, select the cells that are
formatted with that cell style.
Excel 2007 Advanced
© The Mouse Training Company
144
􀂾 To remove a cell style
Mouse
i. On the HOME ribbon, in the STYLES group, click CELL STYLES.
ii. To remove the cell style from the selected cells without deleting the cell style, under GOOD, BAD, AND
NEUTRAL, click NORMAL.
OR
iii. To delete the cell style and remove it from all cells that are formatted with it, right‐click the cell style,
and then click DELETE.
You cannot delete the NORMAL cell style.
Excel 2007 Advanced
© The Mouse Training Company
145
Conditional Formatting
Whenever you analyze data, you often ask yourself questions, such as:
• Where are the exceptions in a summary of profits over the past five years?
• What are the trends in a marketing opinion poll over the past two years?
• Who has sold more than £50,000 this month?
• What is the overall age distribution of employees?
• Which products have greater than 10% revenue increases from year to year?
• Who are the highest performing and lowest performing students in the new student class?
Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or
ranges of cells, emphasize unusual values, and visualize data by using data bars, colour scales and icon sets.
A conditional format changes the appearance of a cell range based on a condition (or criteria). If the
condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell
range is not formatted based on that condition.
When creating a conditional format, you can reference other cells in a worksheet, such as
=FY2006!A5, but you cannot use external references to another workbook.
􀂾 To Format all cells by using a two‐colour scale
Colour scales are visual guides that help you understand data distribution and variation. A two‐colour scale
helps you compare a range of cells by using a gradation of two colours. The shade of the colour represents
higher or lower values. For example, in a green and red colour scale, you can specify higher value cells have
a greener colour and lower value cells have a redder colour.
Mouse
i. Select a range of cells, or make sure that the active cell is in a table or PivotTable
report.
ii. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL
FORMATTING, and then click COLOUR SCALES.
iii. Select a two‐colour scale.
Hover over the colour scale icons to see which one is a
two‐colour scale. The top colour represents higher
values and the bottom colour represents lower values.
Excel 2007 Advanced
© The Mouse Training Company
146
􀂾 To manage rules for advanced formatting.
Mouse
i. Select a range of cells, or make sure that the active cell is in a table or PivotTable report.
ii. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL FORMATTING, and
then click MANAGE RULES.
iii. The CONDITIONAL FORMATTING RULES MANAGER dialog box is displayed.
􀂾 To add a conditional format (new rule).
Mouse
i. click New Rule. The New Formatting Rule dialog box is displayed.
ii. Select a rule type from top section of dialog.
iii. Edit the rule description in bottom section. Rule descriptions will change dependent on the rule type.
iv. Select the colours you wish to apply if the conditions you have set are met.
v. Click OK to create the rule the rule will then appear in the RULE MANAGER.
vi. Click ok to apply the rule and close the RULE MANAGER
􀂾 To conditionally format to two colour scale (advanced)
Mouse
i. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL FORMATTING, and
then click MANAGE RULES.
ii. The CONDITIONAL FORMATTING RULES MANAGER dialog box is displayed.
iii. Make sure that the appropriate worksheet or table is
selected in the SHOW FORMATTING RULES FOR list
Excel 2007 Advanced
© The Mouse Training Company
147
box.
iv. Optionally, change the range of cells by clicking COLLAPSE DIALOG
in the APPLIES TO box to temporarily hide the dialog box, selecting
the new range of cells on the worksheet, and then selecting
EXPAND DIALOG.
v. Select the rule, and then click EDIT RULE. The EDIT FORMATTING RULE dialog box is displayed.
vi. Under SELECT A RULE TYPE, click FORMAT ALL CELLS BASED ON THEIR VALUES.
vii. Under EDIT THE RULE DESCRIPTION, in the FORMAT STYLE list box, select 2‐COLOR SCALE.
viii. Select a MINIMUM and MAXIMUM TYPE
• FORMAT LOWEST AND HIGHEST VALUES Select Lowest Value and Highest Value. In this case,
you do not enter a Minimum and Maximum Value.
• FORMAT A NUMBER, date, or time value Select Number, and then enter a Minimum and
Maximum Value.
• FORMAT A PERCENTAGE Select Percent, and then enter a Minimum and Maximum Value. Valid
values are from 0 to 100. Do not enter a percent sign. Use a percentage when you want to visualize
all values proportionally because the distribution of values is proportional.
• FORMAT A PERCENTILE Select Percentile and then enter a Minimum and Maximum Value. Valid
percentiles are from 0 to 100. You cannot use a percentile if the range of cells contains more than
8,191 data points. Use a percentile when you want to visualize a group of high values (such as the
top 20thpercentile) in one colour grade proportion and low values (such as the bottom 20th
percentile) in another colour grade proportion, because they represent extreme values that might
skew the visualization of your data.
• FORMAT A FORMULA RESULT Select Formula, and then enter a Minimum and Maximum Value.
The formula must return a number, date or time value. Start the formula with an equal sign (=).
Invalid formulas result in no formatting applied. It's a good idea to test the formula in the
worksheet to make sure that it doesn't return an error value.
Minimum and Maximum values are the minimum and maximum values for the range of cells.
Make sure that the Minimum value is less than the Maximum value.
You can choose a different Minimum and Maximum Type. For example, you can choose a Minimum
Number and Maximum Percent.
ix. To choose a MINIMUM and MAXIMUM colour scale, click COLOUR for each, and then select a
colour. If you want to choose additional colours or create a custom colour, click MORE COLOURS.
x. The colour scale that you select is displayed in the PREVIEW box.
xi. Click OK to return to the rule manager
xii. Click OK to apply the new rule to selected cells and close rule manager.
Excel 2007 Advanced
© The Mouse Training Company
148
􀂾 To Format all cells by using data bars quick formatting
A data bar helps you see the value of a cell relative to other cells. The
length of the data bar represents the value in the cell. A longer bar
represents a higher value and a shorter bar represents a lower value.
Data bars are useful in spotting higher and lower numbers especially
with large amounts of data, such as top and bottom selling toys in a
holiday sales report.
Mouse
i. Select a range of cells, or make sure that the active cell is in a table or PivotTable report.
ii. On the HOME ribbon, in the STYLE group, click the arrow next to CONDITIONAL FORMATTING, click
DATA BARS and then select a data bar icon.
􀂾 To Format all cells by using data bars advanced formatting
Mouse
i. Select a range of cells, or make sure that the active cell is in a table or PivotTable report.
ii. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL FORMATTING, and
then click MANAGE RULES. The Conditional Formatting RULES MANAGER dialog box is displayed.
Either
iii. To add a conditional format, click NEW RULE. The NEW FORMATTING RULE dialog box is displayed.
OR
iv. To change a conditional format, Make sure that the appropriate worksheet or table is selected in the
SHOW FORMATTING RULES FOR list box.
v. Optionally, change the range of cells by clicking COLLAPSE DIALOG in the APPLIES TO box to
temporarily hide the dialog box, selecting the new range of cells on the worksheet, and then selecting
EXPAND DIALOG .
vi. Select the rule, and then click EDIT RULE. The EDIT FORMATTING RULE dialog box is displayed.
vii. Under SELECT A RULE TYPE, click FORMAT ALL CELLS BASED ON THEIR VALUES.
viii. Under EDIT THE RULE DESCRIPTION, in the FORMAT STYLE list box, select DATA BAR.
ix. Select a Shortest Bar and Longest Bar Type.
• FORMAT LOWEST AND HIGHEST VALUES Select Lowest Value and Highest Value. In this case,
you do not enter a Shortest Bar and Longest Bar Value.
• FORMAT A NUMBER, DATE, OR TIME VALUE Select Number, and then enter a Shortest Bar
and Longest Bar Value.
• FORMAT A PERCENTAGE Select Percent, and then enter a Shortest Bar and Longest Bar Value.
Valid values are from 0 to 100. Do not enter a percent sign. Use a percentage when you want to
visualize all values proportionally because the distribution of values is proportional.
Excel 2007 Advanced
© The Mouse Training Company
149
• FORMAT A PERCENTILE Select Percentile and then enter a Shortest Bar and Longest Bar Value.
Valid percentiles are from 0 to 100. You cannot use a percentile if the range of cells contains more
than 8,191 data points. Use a percentile when you want to visualize a group of high values (such as
the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th
percentile) in another data bar proportion, because they represent extreme values that might
skew the visualization of your data.
• FORMAT A FORMULA result Select Formula, and then enter a Shortest Bar and Longest Bar
Value. The formula must return a number, date or time value. Start the formula with an equal sign
(=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the
worksheet to make sure that it doesn't return an error value.
Make sure that the Shortest Bar value is less than the Longest Bar value.
You can choose a different Shortest Bar and Longest Bar Type. For example, you can choose a
Shortest Bar Number and Longest Bar Percent. To choose a Shortest Bar and Longest Bar colour
scale, click Bar Colour. If you want to choose additional colours or create a custom colour, click
More Colours. The bar colour that you select is displayed in the Preview box. To show only the
data bar and not the value in the cell, select Show Bar Only.
􀂾 To Clear conditional formats (worksheet)
Mouse
i. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL FORMATTING, and
then click CLEAR RULES.
ii. Click ENTIRE SHEET.
􀂾 To Clear conditional formats (A range of cells, table, or PivotTable)
Mouse
i. Select the range of cells, table or PivotTable for which you want to clear conditional formats.
ii. On the HOME ribbon, in the STYLES group, click the arrow next to CONDITIONAL FORMATTING, and
then click CLEAR RULES.
iii. Depending on what you have selected, click SELECTED CELLS, THIS TABLE or THIS PIVOTTABLE.
Excel 2007 Advanced
© The Mouse Training Company
150
SECTION 8 OTHER EXCEL FEATURES
INSERTING, FORMATTING AND DELETING OBJECTS
Inserting A Drawing Object
Inserting pictures, text boxes, callouts, scanned images etc onto a worksheet
can greatly enhance your overall spreadsheet appearance. The subject of
dealing with these objects will be looked at more thoroughly in PowerPoint as
that application deals primarily with inserted objects and how to deal with
them as word works primarily with text and excel with figures, however here is
a brief rundown of some items you may insert and how to deal with them.
􀂾 To insert a shape onto worksheet.
Mouse
i. On the INSERT ribbon in the ILLUSTRATIONS group, click
on the drop down arrow to the right of SHAPES.
ii. Make a selection by clicking with the left mouse button on
the desired shape. If you cannot immediately find what you
want, scroll down using the scroll bar to the right to locate
other shapes.
iii. The menu will dissappear but your mouse cursor will appear
as a small black cross. Click and drag diagaonally to place the
shape on the worksheet.
􀂾 To move or resize shape
Mouse
i. Select shape by clicking on it selection will be shown by
handles appearing around the shape.
ii. Moving mouse cursor over shape should give a four pointed
arrow clicking and dragging with this cursor will move the
shape to desired position.
OR
iii. Use cursor keys for small adjustments in moving shape
around worksheet.
iv. Moving mouse cursor over a handle will give a black two
pointed arrow. Clicking and dragging will resize the shape to
appropriate size.
􀂾 To delete a shape
Mouse
i. Select shape by clicking on it selection will be shown by handles appearing around the shape.
ii. Press DELETE on the keyboard to remove shape.
Excel 2007 Advanced
© The Mouse Training Company
151
SmartArt
A SmartArt graphic is a visual representation of your information and ideas. You can create SmartArt
graphics by choosing from among many different layouts to quickly, easily and effectively communicate
your message.
Most people create content that contains only text, even though illustrations and graphics help audiences
understand and recall information better than text. Creating designer‐quality illustrations can be
challenging, especially if you are not a professional designer or you cannot afford to hire a professional
designer. If you use earlier versions of Microsoft Office, you can spend a lot of time making shapes the same
size and aligning them properly, getting your text to look right, and manually formatting the shapes to
match the document's overall style, instead of focusing on your content. With SmartArt graphics and other
new features such as themes, you can create designer‐quality illustrations with only a few clicks of your
mouse.
When you create a SmartArt graphic, you are prompted to choose a type such as PROCESS, HIERARCHY,
CYCLE, or RELATIONSHIP. A type is similar to a category of SmartArt graphic, and each type contains
several different layouts.
When you choose a layout for your SmartArt graphic, ask yourself what you want to convey and whether
you want your information to appear a certain way. Because you can quickly and easily switch layouts, try
different layouts (across types) until you find the one that best illustrates your message. Experiment with
different types and layouts by using the table below as a starting point.
When you switch layouts, most of your text and other content, colours, styles, effects, and text formatting
are automatically carried over to the new layout.
􀂾 To insert a SmartArt graphic
Mouse
i. On the INSERT ribbon in the ILLUSTRATIONS group, click on the drop down arrow to the right of
SMARTART the SMARTART dialog above will appear
ii. Choose a category from the left thena graphic from the centre to see a preview on the right.
Excel 2007 Advanced
© The Mouse Training Company
152
iii. When you have the selection you desire click ok to insert the smartart on your worksheet
iv. The smart art will appear on your worksheet as in picture below.
About the Text pane
The Text pane is the pane that you can use to enter and edit the text that appears in your SmartArt graphic.
The Text pane appears to the left of your SmartArt graphic. As you add and edit your content in the Text
pane, your SmartArt graphic is automatically updated— shapes are added or removed as needed.
When you create a SmartArt graphic, the SmartArt graphic and its Text pane are populated with
placeholder text that you can replace with your information. At the top of the Text pane, you can edit the
text that will appear in your SmartArt graphic. At the bottom of the Text pane, you can view additional
information about the SmartArt graphic.
􀂾 To enter text into SmartArt
Mouse
i. Click on placeholder in the TEXT PANE.
ii. Placeholder text will disappear.
iii. Type required text and press ENTER
iv. Focus will create a new placeholder and new shape waiting for text
v. Press TAB to move the text lower in the hierarchy in the example above or SHIFT + TAB to move it
higher.
􀂾 To remove a SmartArt shape
Mouse
i. Select the text or placeholder text of the shape you wish to remove in the TEXT PANE.
ii. Press DELETE key on keyboard
iii. Both text and shape will be removed from graphic
Excel 2007 Advanced
© The Mouse Training Company
153
WordArt
WordArt is a gallery of text styles that you can add to your 2007 Microsoft Office system documents to
create decorative effects, such as shadowed or mirrored (reflected) text. You can change WordArt text, as
you can change any other text in a shape.
Mouse
iv. On the INSERT ribbon, in the TEXT group,
click WORDART, and then click the WordArt
style that you want.
v. Enter your text.#
vi. A WordArt graphic can be resized, rotated, deleted and moved like any other shape.
Formatting Shapes
Any shape inserted on the spreadsheet, when selected, causes a new ribbon to appear called the FORMAT
ribbon seen below, it offers the options of inserting further shapes. Formatting all aspects of the selected
shape and fixing a specific size. The easiest method of formatting your shape is to use the QuickStyle option
in the SHAPE STYLES group
QuickStyles
Quick Styles are combinations of different formatting options and are displayed in a thumbnail in the
various Quick Style galleries. When you place your pointer over a Quick Style thumbnail, you can see how
the Quick Style affects your SmartArt graphic or shape.
Quick Styles for SmartArt graphics (SmartArt Styles) include edges, shadows, line styles, gradients and
three‐dimensional (3‐D) perspectives. Try different combinations of SmartArt Styles and colours until you
find one that matches the message that you want to communicate. You can pick a layout, a SmartArt Style,
and a colour variation that you like, and then change the layout again — your SmartArt Style and colours
will stay with your SmartArt graphic, so that you do not need to re‐do them.
SmartArt Styles map the theme effects (theme effects: A set of visual attributes that is applied to elements
in a file. Theme effects, theme colours and theme fonts compose a theme.) of the document theme to the
shapes within the SmartArt graphic. For example, shapes might have thick lines or edges, while arrows
might have a more subtle style applied to them. You can also apply colours from the theme colours of the
document in different ways, such as changing the colour of the shape border. If you create multiple
SmartArt graphics and want them to look alike, you can apply the same colours and SmartArt Style to
achieve a consistent, professional look.
You can have shapes that display with edges, depth, and rotate in 3‐D space. To make a SmartArt graphic
three‐dimensional, apply a 3‐D SmartArt Style or manually apply a 3‐D rotation to each shape. If the entire
SmartArt graphic is three‐dimensional (called scene coherent 3D), you can continue to edit the text and
formatting of each of the individual shapes, but the shapes cannot be repositioned or resized. You can only
Excel 2007 Advanced
© The Mouse Training Company
154
reposition or resize shapes in a two‐dimensional scene. To switch between 2‐D and 3‐D, under SMARTART
TOOLS, on the FORMAT ribbon, in the SHAPES group, click EDIT IN 2‐D. The EDIT IN 2‐D button temporarily
unlocks your SmartArt graphic for editing so that you can move and resize shapes— but the 3‐D SmartArt
Style is still applied to your SmartArt graphic and reappears when you click EDIT IN 2‐D again. When your
SmartArt graphic is displayed in a 3‐D scene, you can rotate it as a whole and position light sources and the
"camera" such that the entire SmartArt graphic appears to pop out of the screen.
SmartArt Styles affect an entire SmartArt graphic, while Quick Styles for Shapes (Shape Styles) affect only
the selected shape. You can manually customize a shape by changing the colour, effects or border, or by
replacing it with another shape. It is recommended that you customize your SmartArt graphic only after you
settle on its content and layout, as some customizations are not transferred because they might not look
good in the new layout. For more information about switching layouts for SmartArt graphics, see Switch the
layout or type of a SmartArt graphic.
􀂾 To apply a QuickStyle
Mouse
i. Select the shape or shapes you wish to apply a QuickStyle to.
ii. As you move your mouse over the various options your shape on the worksheet will temporarily take on
that format as a preview. See below.
iii. If you prefer to look at the other them fills option at the bottom you will be given the above options
iv. When you locate the style you want click on it to apply it to your shape.
Other theme fills
Excel 2007 Advanced
© The Mouse Training Company
155
Manual Formatting
Manual formatting can be applied as well. Fill, border, 3D, rotation etc using various tools from
the format ribbon. There are also tools for aligning, layering and sizing your shape as in a
desktop publishing programme.
􀂾 To change fill colour
Mouse
i. Select shape to be formatted select FILL COLOUR from the SHAPE STYLES
group.
ii. Select a THEME COLOUR, STANDARD COLOUR, GRADIENT, TEXTURE,
PICTURE or MORE FILL COLOURS.
more fill colours gives a palette with any possible colour you may require. You
may match a colour if you know the RGB or CYMK numbers)
􀂾 To change a border
Mouse
i. Select shape to be formatted select SHAPE OUTLINE from the shape styles
group.
ii. Select a THEME COLOUR, STANDARD COLOUR or MORE OUTLINE
COLOURS.
iii. when you have selected a colour for your shapes outline you may wish to
make it thicker or to have a dashed style. Repeat step one and select weight
or dashes and make a selection to apply to your shape.
The arrows option is available if your shape happens to be any kind of line you
may choose an arrow style as well as a weight and line style
􀂾 To apply shape effects
Mouse
i. Select shape to be formatted. Click on
shape effects to see menu on right
ii. The preset menu shows popular styles
made up of the other menu choices. Select
a preset to apply.
iii. You may alter aspects of the preset by
repeating and selecting a different menu
choice.
Using all formatting choices shown gives a
very professional finish to any object placed
Excel 2007 Advanced
© The Mouse Training Company
156
on the worksheet many of these options are available for charts and pictures.
Excel 2007 Advanced
© The Mouse Training Company
157
REVIEWING
Protecting
When sending your work to someone else to check,
make corrections or comments it is necessary to track
the changes that others may make to your work to see
what changes they have made as they review your work.
You may also want to restrict what they are allowed to
do to your work so they do not inadvertently damage formulae and functions that make the workbook
produce valid figures. For these reasons we may have to protect the workbook in various ways.
􀂾 To protect a workbook
Protecting a workbook ensures individuals cannot, insert, delete, move or
otherwise tamper with the sheets in your work book. Hidden sheets will
not be able to be unhidden if valuable tables or data is stored on them.
Mouse
i. Click on PROTECT WORKBOOK in the CHANGES group on the
REVIEW ribbon
ii. Select PROTECT STRUCTURE AND WINDOWS the following dialog will appear.
iii. For security (not essential) enter a password and click on ok. Workbook structure is now protected.
􀂾 To unprotect a workbook
Mouse
iv. Click on PROTECT WORKBOOK in the CHANGES group on the REVIEW ribbon
v. Select UNPROTECT STRUCTURE AND WINDOWS a dialog will appear asking for password
vi. Enter password, click on OK, workbook is now unprotected
Excel 2007 Advanced
© The Mouse Training Company
158
􀂾 Protect worksheet data
Mouse
i. Select all cells you would like individuals to be allowed to
change.
ii. On the HOME ribbon, in the CELLS group, click FORMAT, and
then click FORMAT CELLS.
iii. Click on the protection tab
iv. Untick LOCK CELLS.
v. Click on PROTECT WORKSHEET in the CHANGES group on
the REVIEW ribbon.
vi. Tick what you wish users to be allowed to do in the locked
cells.
vii. Enter a password if you wish
viii. Click on OK.
ix. Sheet is now protected any cell that was locked is now uneditable by anyone.
􀂾 To unprotect worksheet data
Mouse
i. Click on PROTECT WORKSHEET in the CHANGES group on the REVIEW ribbon
ii. Enter password to unprotect sheet
iii. Click OK
􀂾 To protect for tracked changes
Mouse
i. Click on TRACK CHANGES and then HIGHLIGHT CHANGES in the
CHANGES group on the REVIEW ribbon
ii. The HIGHLIGHT CHANGES dialog will appear.
iii. Tick the TRACK CHANGES WHILE EDITING option
iv. Click on OK.
V. Any changes made to the workbook by anyone now
will leave a mark in the cell to show it has been
changed by who, when and what the change is.
Excel 2007 Advanced
© The Mouse Training Company
159
􀂾 To Accept/reject changes
When changes have been made to your workbook you may wish to
check those changes and see what has been altered. You may not be
happy with some of the changes and wish to reject them for what
was previously within a cell.
Mouse
i. Click on TRACK CHANGES and then ACCEPT/REJECT CHANGES in the CHANGES group on the
REVIEW ribbon.
ii. The SELECT CHANGES TO ACCEPT OR REJECT dialog will appear.
iii. Click OK. The ACCEPT OR REJECT CHANGES dialog will appear
iv. As you accept or reject each change the dialog will automatically move on to the next change. When you
have finished click close to close the dialog and finish reviewing.
Use A Shared Workbook To Collaborate
You can create a shared workbook and place it on a network location
where several people can edit the contents simultaneously. For example, if
the people in your work group each handle several projects and need to
know the status of each other's projects, the group can use a shared
workbook to track the status of the projects. All persons involved can then
enter the information for their projects in the same workbook.
Excel 2007 Advanced
© The Mouse Training Company
160
As the owner of the shared workbook, you can manage it by removing users from the shared workbook and
resolving conflicting changes. When all changes have been incorporated, you can stop sharing the
workbook.
Share A Workbook
Create a new workbook and enter any data that you want to provide, or open an existing workbook that
you want to make available for multi‐user editing.
Not all features are supported in a shared workbook. If you want to include any of the following features,
you should add them before you save the workbook as a shared workbook: merged conditional formats
data validation, charts, pictures, objects including drawing objects, hyperlinks scenarios, outlines subtotals,
data tables PivotTable reports workbook and worksheet protection, and macros. You cannot make changes
to these features after you share the workbook.
Features that are not supported in a shared workbook
In a shared workbook, you cannot But you may be able to do the following
Create an Excel table None
Insert or delete blocks of cells You can insert entire rows and columns.
Delete worksheets None
Merge cells or split merged cells None
Add or change conditional formats Existing conditional formats continue to appear as cell
values change, but you can't change these formats or
redefine the conditions.
Add or change data validation Cells continue to be validated when you type new values,
but you can't change existing data validation settings.
Create or change charts or PivotChart
reports
You can view existing charts and reports.
Insert or change pictures or other objects You can view existing pictures and objects.
Insert or change hyperlinks Existing hyperlinks continue to work.
Use drawing tools You can view existing drawings and graphics.
Assign, change, or remove passwords Existing passwords remain in effect.
Protect or unprotect worksheets or the
workbook
Existing protection remains in effect.
Create, change, or view scenarios None
Group or outline data You can continue to use existing outlines.
Insert automatic subtotals You can view existing subtotals.
Create data tables You can view existing data tables.
Create or change PivotTable reports You can view existing reports.
Write, record, change, view, or assign
macros
You can run existing macros that don't access unavailable
features. You can record shared workbook operations
into a macro stored in another nonshared workbook.
Add or change Microsoft Excel 4 dialog
sheets
None
Change or delete array formulas Existing array formulas continue to calculate correctly.
Use a data form to add new data You can use a data form to find a record.
Work with XML data, including:
• Import, refresh, and export XML data
• Add, rename, or delete XML maps
• Map cells to XML elements
• Use the XML Source task pane, XML
None
Excel 2007 Advanced
© The Mouse Training Company
161
toolbar, or XML commands on the Data
menu
􀂾 To share a workbook
Mouse
i. On the REVIEW tab, in the CHANGES group, click SHARE WORKBOOK.
ii. On the EDITING tab, select the ALLOW CHANGES BY MORE THAN ONE USER AT
THE SAME TIME. THIS ALSO ALLOWS WORKBOOK MERGING check box.
iii. On the ADVANCED tab, select the options that you want to use for tracking and updating changes, and
then click OK.
iv. If this is a new workbook, type a name in the FILE NAME box.
OR
v. If this is an existing workbook, click OK to save the workbook, click MICROSOFT OFFICE BUTTON and
then click SAVE AS.
vi. In the SAVE IN box, select a network location that is accessible to the intended users, and then click
SAVE.
You should use a shared network folder, not a Web server.
vii. If the workbook contains links to other workbooks or documents, verify the links and update any links
that are broken, and then click SAVE on the QUICK ACCESS TOOLBAR, or press CTRL+S.
􀂾 To verify and update links to other workbooks or documents
i. On the DATA tab, in the CONNECTIONS group, click EDIT LINKS.
The Edit Links to Files command is unavailable if your file does not
contain linked information.
ii. Click CHECK STATUS to verify the status for all links in the list.
This may take a while if there are many links, or if the source workbook for the links is on a
network location, and the network is slow.
iii. Check the status in the STATUS column, click the link and then take the action that is needed.
If the status is Take this action
OK No action is required. The link is working and up to date.
Unknown Click CHECK STATUS to update the status for all links in the list.
N/A The link uses Object Linking and Embedding (OLE) or Dynamic Data
Exchange (DDE)Microsoft Office Excel cannot check the status of these
types of links.
Error: Source not
found
Click CHANGE SOURCE, and then select another workbook.
Error: Worksheet not
found
Click CHANGE SOURCE, and then select another worksheet. The source
may have been moved or renamed.
Warning: Values not
updated
Click UPDATE VALUES. The link was not updated when the workbook was
opened.
Excel 2007 Advanced
© The Mouse Training Company
162
Warning: Click Open
Source, and calculate
the workbook by
pressing F9
The workbook may be set to manual calculation. To set the workbook to
automatic calculation, click MICROSOFT OFFICE BUTTON , and then click
EXCEL OPTIONS. In the FORMULAS category, under CALCULATION
OPTIONS, click AUTOMATICALLY.
Warning: Some names
cannot be resolved
until the source
workbook is opened
Click OPEN SOURCE, switch back to the destination workbook and then
click CHECK STATUS. If this does not resolve the problem, make sure that
the name is not misspelled or missing. Switch to the source workbook, and
then on the FORMULAS tab, in the NAMED CELLS group, click NAME
MANAGER, and look for the name.
Warning: Click Open
Source
The link cannot be updated until the source is open.
Source is open The status of a link cannot be checked.
Values updated from
file name
No action is required. The values have been updated.
Warning: Excel cannot
determine the status
of the link
The source may contain no worksheets or may be saved in an unsupported
file format. Click UPDATE VALUES.
All users with access to the network share have full access to the shared workbook unless you lock cells and
protect the worksheet to restrict access. To protect a shared workbook, click PROTECT AND SHARE
WORKBOOK in the CHANGES group on the REVIEW tab. When you protect a shared workbook, you can
set a password that all users must enter to open the workbook.
To edit the shared workbook, all users must have one of the following installed on their computers:
Microsoft Office Excel, Microsoft Excel 97 or later or Microsoft Excel 98 or later for Macintosh.
Not all Excel features are supported in a shared workbook. For more information, see the table "Features
that are not supported in a shared workbook" above.
􀂾 To Edit a shared workbook
After you open a shared workbook, you can enter and change data as you do in a regular workbook.
i. Open the shared workbook.
ii. Click the MICROSOFT OFFICE BUTTON , and then click EXCEL OPTIONS.
iii. In the POPULAR category, under PERSONALIZE YOUR COPY OF OFFICE, in the USER NAME box,
enter the user name that you want to use to identify your work in the shared workbook, and then click
OK.
iv. Enter and edit data on the worksheets as usual.
You won't be able to add or change the following: merged cells, conditional formats, data
validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines,
subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros.
Make any filter and print settings that you want for your personal use. Each user's settings are
saved individually by default.
You can also use the filter or print settings that were made by the owner of the workbook
whenever you open the workbook.
v. To save your changes to the workbook and see the changes that other users have saved since your last
save, click SAVE on the QUICK ACCESS TOOLBAR, or press CTRL+S. If the RESOLVE CONFLICTS dialog box
appears, resolve the conflicts.
Excel 2007 Advanced
© The Mouse Training Company
163
To resolve conflicts, see Resolve conflicting changes in a shared workbook in this topic.
You can see who else has the workbook open on the Editing tab of the Share Workbook dialog box
(Review tab, Changes group, Share Workbook button).
You can choose to get automatic updates of the other users' changes periodically, with or without
saving, under Update changes on the Advanced tab of the Shared Workbook dialog box.
􀂾 To Remove a user from a shared workbook
If needed, you can disconnect users from a shared workbook.
Before disconnecting users, make sure that they have completed their work on the workbook. If
you remove an active user, any unsaved work will be lost.
Mouse
i. On the REVIEW tab, in the CHANGES group, click SHARE WORKBOOK.
ii. On the EDITING tab, in the WHO HAS THIS WORKBOOK OPEN NOW list, review the names of
users.
iii. Select the name of the user who you want to disconnect, and then click REMOVE USER.
Although this action disconnects the user from the shared workbook, it does not prevent that user
from editing the shared workbook again.
􀂾 To delete any personal view settings of the removed user, do the following:
Mouse
i. On the View tab, in the Workbook Views group,
click Custom Views.
ii. n the Views list, select the view of another user,
and then click Delete.
􀂾 Resolve conflicting changes in a shared workbook
A conflict happens when two users are both editing the same shared workbook and try to save changes that
affect the same cell. Excel can keep only one of the changes in that cell. When the second user saves the
workbook, Excel displays the RESOLVE CONFLICTS dialog box.
Mouse
i. In the RESOLVE CONFLICTS dialog box, read the information about each change and the conflicting
changes made by the other user.
ii. To keep your change or the other person's change and to advance to the next conflicting change, click
ACCEPT MINE or ACCEPT OTHER. To keep all of your remaining changes or all of the other user's
changes, click ACCEPT ALL MINE or ACCEPT ALL OTHERS.
Excel 2007 Advanced
© The Mouse Training Company
164
􀂾 To override resolve conflicts dialog
To have your changes override all other changes without displaying the RESOLVE CONFLICTS dialog box
again,:
Mouse
i. On the REVIEW tab, in the CHANGES group, click SHARE WORKBOOK.
ii. On the ADVANCED tab, under CONFLICTING CHANGES BETWEEN USERS, click THE CHANGES
BEING SAVED WIN, and then click OK.
􀂾 To view how you or others resolved past conflicts
i. On the REVIEW tab, in the CHANGES group, click TRACK CHANGES, and then click HIGHLIGHT
CHANGES.
ii. In the WHEN list, select ALL.
iii. Clear the WHO and WHERE check boxes.
iv. Select the LIST CHANGES ON A NEW SHEET check box, and then click OK.
v. On the History worksheet, scroll to the right to view the ACTION TYPE and LOSING ACTION columns.
Conflicting changes that were kept have Won for Action Type. The row numbers in the Losing
Action column identify the rows with information about the conflicting changes that were not
kept, including any deleted data. History worksheet is A separate worksheet that lists changes
being tracked in a shared workbook, including the name of the person who made the change,
when and where it was made, what data was deleted or replaced, and how conflicts were
resolved.)
To save a copy of the workbook with all your changes, click Cancel in the Resolve Conflicts dialog
box, click Microsoft Office Button, click SAVE AS, and then type a new name for the file.
􀂾 To Stop sharing a workbook
Before you stop sharing the workbook, make sure that all other users have completed their work. Any
unsaved changes will be lost. Because the change history will also be deleted, you may want to start by
printing the History worksheet or by copying it to another workbook.
􀂾 To keep a copy of the change history information.
i. On the REVIEW tab, in the CHANGES group, click TRACK CHANGES, and then click HIGHLIGHT
CHANGES.
ii. In the WHEN list, select ALL.
iii. Clear the WHO and WHERE check boxes.
iv. Select the LIST CHANGES ON A NEW SHEET check box, and then click OK.
• To print the History worksheet, click MICROSOFT OFFICE BUTTON , and then click PRINT.
Excel 2007 Advanced
© The Mouse Training Company
165
• To copy the history to another workbook, select the cells that you want to copy, click COPY on the
HOME ribbon in the CLIPBOARD group, switch to another workbook, click where you want to
place the copied data, and then click PASTE on the HOME tab in the CLIPBOARD group.
You may also want to save or print the current version of the workbook, because this history data
might not apply to later versions of the workbook. For example, cell locations, including row
numbers, in the copied history may no longer be current.
v. In the shared workbook, on the REVIEW tab, in the CHANGES group, click SHARE WORKBOOK.
vi. On the EDITING tab, make sure that you are the only person listed in the WHO HAS THIS
WORKBOOK OPEN NOW list.
vii. Clear the ALLOW CHANGES BY MORE THAN ONE USER AT THE SAME TIME. THIS ALSO ALLOWS
WORKBOOK MERGING check box.
viii. When you are prompted about the effects on other users, click YES
If this check box is not available, you must first unprotect the workbook. To remove shared
workbook protection,
􀂾 To unprotect shared workbook
Mouse
i. Click OK to close the SHARE WORKBOOK dialog box.
ii. On the REVIEW tab, in the CHANGES group, click UNPROTECT SHARED WORKBOOK.
iii. If you are prompted, enter the password (A way to restrict access to a workbook, worksheet or part of a
worksheet. Excel passwords can be up to 255 letters, numbers, spaces and symbols. You must type
uppercase and lowercase letters correctly when you set and enter passwords.), and then click OK.
iv. On the REVIEW tab, in the CHANGES group, click SHARE WORKBOOK.
v. On the EDITING tab, clear the ALLOW CHANGES BY MORE THAN ONE USER AT THE SAME TIME.
THIS ALSO ALLOWS WORKBOOK MERGING check box.
vi. When you are prompted about the effects on other users, click YES.
Excel 2007 Advanced
© The Mouse Training Company
166
PROOFING TOOLS
Spelling And Grammar
As a deadline approaches, often there is not enough time to check a document for spelling and
grammar mistakes. Your Microsoft Office program provides tools that can help you correct
these mistakes faster. You decide if you want to set up the Microsoft Office program so that you
can easily see potential mistakes while you work. Or, if you find the wavy red and green lines
distracting, you can just check your document when you are ready to finish it.
Maybe you are looking for a way to find and fix spelling mistakes in your document more quickly and easily?
Or maybe you don't want to see the wavy red lines that your Microsoft Office program displays in your
document? This section explains how automatic spelling and grammar checking works and how to turn it on
or off.
There is no option to check spelling while you type in Microsoft Office Access, Microsoft Office Excel
or Microsoft Office Project.
Grammar checking is available only in Microsoft Office Outlook and Microsoft Office Word.
􀂾 To check spelling
Mouse
i. Click inside a worksheet that you are editing to check the entire active worksheet, including cell values,
cell comments, embedded charts, text boxes, buttons, headers, and footers. Excel does not check
protected worksheets, formulas or text that results from a formula. Also, if the formula bar is active
when you check spelling, Excel checks only the contents of the formula bar.
ii. Optionally, to check only a specific piece of text, select the text you want to check.
iii. Click on SPELLCHECKER in the PROOFING group on the REVIEW ribbon.
iv. If the program finds spelling mistakes, a dialog box or task pane is displayed, and the first misspelled
word found by the spelling checker is selected. You decide how you want to resolve each error that the
program finds.
v. After you resolve each misspelled word, the program flags the next misspelled word, so that you can
decide what you want to do.
Excel 2007 Advanced
© The Mouse Training Company
167
vi. When all spelling has been corrected a dialog appears telling you that the spell check is complete
Thesaurus
􀂾 To use thesaurus
Mouse
i. On the REVIEW tab, click THESAURUS.
ii. Press ALT and click the word that you want to look up. Results
appear in the RESEARCH task pane.
You can type a word or phrase in the SEARCH FOR box, and then click
Start Searching .
iii. To use one of the words in the list of results or to search for more
words, do one of the following:
iv. To use one of the words, point to it, click the down arrow and then
click INSERT or COPY.
v. To look up additional related words, click a word in the list of results.
You can also look up words in the thesaurus of another language. If, for
example, your document is in French and you want synonyms, click
RESEARCH OPTIONS in the RESEARCH task pane, and then under
REFERENCE BOOKS, select the thesaurus options that you want.
Translation
Using the Research feature, you can translate single words or short phrases by using
bilingual dictionaries or translate your entire document by using Web‐based machine
translation services. To translate text, you may also need to satisfy the operating system
requirements for specific languages.
i. On the REVIEW tab, click TRANSLATE.
ii. The translation service appears in the RESEARCH task pane.
iii. To change the languages that are used for translation, in the
RESEARCH task pane, under TRANSLATION, select the languages
that you want to translate from and to. For example, to translate
English to French, click ENGLISH (U.S.) in the FROM list and
FRENCH (FRANCE) in the TO list.
iv. To translate a specific word, press ALT and click a word. The results
appear in the RESEARCH task pane under TRANSLATION.
v. To translate a short phrase, select the words, press ALT and click the
selection. The results appear in the RESEARCH task pane under
TRANSLATION.
You can type a word or phrase in the SEARCH FOR box, and then click
Start Searching .
Excel 2007 Advanced
© The Mouse Training Company
168
Show Or Hide ScreenTips
ScreenTips are small windows that display descriptive text when you rest the pointer on a command or
control.
Enhanced ScreenTips are larger windows
that display more descriptive text than a
ScreenTip and can have a link to a Help
topic. Enhanced ScreenTips are available
in the following 2007 Microsoft Office
system programs: Access, Excel,
PowerPoint and Word.
i. Click the MICROSOFT OFFICE BUTTON , and then click
PROGRAM NAME OPTIONS, where Program Name is the
name of the program you are in, for example, WORD
OPTIONS.
ii. Click POPULAR.
iii. Under TOP OPTIONS FOR WORKING WITH PROGRAM NAME, in the SCREENTIP STYLE list, click
the option that you want:
SHOW FEATURE DESCRIPTIONS IN SCREENTIPS This option turns on ScreenTips and Enhanced
ScreenTips. This is the default setting.
DON'T SHOW FEATURE DESCRIPTIONS IN SCREENTIPS This option turns off Enhanced ScreenTips. You
still see ScreenTips.
DON'T SHOW SCREENTIPS This option turns off ScreenTips and Enhanced ScreenTips.
Excel 2007 Advanced
© The Mouse Training Company
169
EXCEL 2007 SPECIFICATIONS AND LIMITS
􀂾 Worksheet and workbook specifications and limits
Feature Maximum limit
Open workbooks Limited by available memory and system
resources
Worksheet size 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3
sheets)
Colours in a workbook 16 million colours (32 bit with full access
to 24 bit colour spectrum)
Named views (view: A set of display and print settings that you can name and
apply to a workbook. You can create more than one view of the same
workbook without saving separate copies of the workbook.) in a workbook
Limited by available memory
Unique cell formats/cell styles 64,000
Fill styles 32
Line weight and styles 16
Unique font types 1,024 global fonts available for use; 512
per workbook
Number formats in a workbook Between 200 and 250, depending on the
language version of Excel that you have
installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Panes in a window 4
Linked sheets Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a
worksheet model.)
Limited by available memory; a summary
report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 64 in a single sort; unlimited when using
sequential sorts
Undo levels 100
Fields in a data form 32
Workbook parameters 255 parameters per workbook
Filter drop‐down lists 10,000
􀂾 Calculation specifications and limits
Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E+307
Largest allowed positive number 1.79769313486231E+308
Smallest allowed negative number ‐2.2251E‐308
Smallest allowed positive number 2.229E‐308
Largest allowed negative number ‐1E‐307
Excel 2007 Advanced
© The Mouse Training Company
170
Length of formula contents 8,192 characters
Internal length of formula 16,384 bytes
Iterations 32,767
Worksheet arrays Limited by available memory
Selected ranges 2,048
Arguments in a function 255
Nested levels of functions 64
User defined function categories 255
Number of available worksheet functions 341
Size of the operand stack 1,024
Cross‐worksheet dependency 64,000 worksheets that can refer to other
sheets
Cross‐worksheet array formula dependency Limited by available memory
Area dependency Limited by available memory
Area dependency per worksheet Limited by available memory
Dependency on a single cell 4 billion formulas that can depend on a
single cell
Linked cell content length from closed workbooks 32,767
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904
date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:59:59
􀂾 Charting specifications and limits
Feature Maximum limit
Charts linked to a worksheet Limited by available memory
Worksheets referred to by a chart 255
Data series(Related data points that are plotted in a chart. Each data series in a
chart has a unique colour or pattern and is represented in the chart legend.
You can plot one or more data series in a chart. Pie charts have only one data
series.) in one chart
255
Data points (Individual values that are plotted in a chart. Related data points
make up a data series. Data points are represented by bars, columns, lines,
slices, dots, and other shapes. These shapes are called data markers.) in a data
series for 2‐D charts
32,000
Data points in a data series for 3‐D charts 4,000
Data points for all data series in one chart 256,000
􀂾 PivotTable and PivotChart report specifications and limits
Feature Maximum limit
PivotTable reports (An interactive, cross tabulated Excel report that summarizes
and analyzes data, such as database records, from various sources, including
ones that are external to Excel.) on a sheet
Limited by available memory
Unique items per field 1,048,576
Row (A field that's assigned a row orientation in a PivotTable report. Items
associated with a row field are displayed as row labels.) or column fields(A field
that's assigned a column orientation in a PivotTable report. Items associated
with a column field are displayed as column labels.) in a PivotTable report
Limited by available memory
Report filters in a PivotTable report 256 (may be limited by available
memory)
Value fields in a PivotTable report 256
Calculated item (An item within a PivotTable field or PivotChart field that uses a
formula you create. Calculated items can perform calculations by using the
contents of other items within the same field of the PivotTable report or
PivotChart report.) formulas in a PivotTable report
Limited by available memory
Excel 2007 Advanced
© The Mouse Training Company
171
Report filters in a PivotChart report(A chart that provides interactive analysis of
data, like a PivotTable report. You can change views of data, see different levels
of detail or reorganize the chart layout by dragging fields and by showing or
hiding items in fields.)
256 (may be limited by available
memory)
Value fields in a PivotChart report 256
Calculated item formulas in a PivotChart report Limited by available memory
Length of the MDX name for a PivotTable item 32,767
Length for a relational PivotTable string 32,767
􀂾 Shared workbook specifications and limits
Feature Maximum limit
Users who can open and share a shared workbook (shared workbook: A
workbook set up to allow multiple users on a network to view and make
changes at the same time. Each user who saves the workbook sees the changes
made by other users.) at the same time
256
Personal views (view: A set of display and print settings that you can name and
apply to a workbook. You can create more than one view of the same
workbook without saving separate copies of the workbook.) in a shared
workbook
Limited by available memory
Days that change history (In a shared workbook, information that is maintained
about changes made in past editing sessions. The information includes the
name of the person who made each change, when the change was made, and
what data was changed.) is maintained
32,767 (default is 30 days)
Workbooks that can be merged at one time Limited by available memory
Cells that can be highlighted in a shared workbook 32,767
Colours used to identify changes made by different users when change
highlighting is turned on
32 (each user is identified by a separate
colour; changes made by the current
user are highlighted with navy blue)
Excel 2007 Advanced
© The Mouse Training Company
172
Prepared by Stephen Moffat on the 12th September 2007