Excel Meetings  2012

Other Years  2011



Tuesday 13 November 2012

Attendance :

7

News :

 XLIG meetings will be in recess until further notice.
 Geoff Turton will be available to assist members with their Excel projects.

Q & A :

 How do you create a chart for data in nonadjacent cells?
 Remove any rows that contain blank cells in the columns to be charted.
 Use Ctrl + Leftclick and
Shift + Leftclick to select the ranges of data to be charted.
 Press F11 to create a chart based on the selected data.
 This link shows an example
of a chart created in this way.
 How do you apply conditional formatting to highlight the differences
between cells in the same row of two different columns?
Example: Apply conditional formatting to highlight when the values
in the cells in column J are less than the values in the cells
in the corresponding rows in column F.
 See the example worksheet,
using Excel 2007/2010.
 Select the first cell value in column J (J4).
 Create the conditional formatting rule for cell J4 as follows:
 Home ribbon >
Styles group >
Conditional Formatting icon >
Highlight Cells Rules option >
Less Than… >
 Format cells that are LESS THAN:
cell reference F4 >
Apply required highlighting format, then
click OK.
Note: Be sure to change the default absolute
cell reference $F$4 to a relative reference F4.
 Copy cell J4, then select the cells in the range J5:J15.
 Rightclick anywhere in the selected range and select
Paste Special… from the contextsensitive menu.
 Under the Paste options, select
Formats, then click
OK.
 Cells in column J that are less than the corresponding values
in the same rows in column F will now be highlighted.

Presentation :


....................................
GRT

Tuesday 9 October 2012

Attendance :

8

Q & A :

 How do you retain headings at the top and/or on the left of the screen
when scrolling down or to the right?
 Select the cell in the row below the row(s) you want to remain in view while scrolling down and
in the column to the right of the column(s) you want to remain in view while scrolling to the right,
and then select Freeze Panes.
Excel 2003  Windows menu >
Freeze Panes option.
Excel 2007/10  View tab >
Window group > Freeze Panes icon.
 How do you copy part of an Excel worksheet into a Word document?
 Select the cells to be copied.
 Open Word document and position cursor where you want to paste worksheet extract.
 Select Paste Special… and then either:
 Select Paste: and paste as an image.
An image can be resized to fit available space but the pasted worksheet contents
will not reflect any subsequent changes made in the source Excel worksheet, or
 Select Paste link: and click OK.
Paste Link creates a shortcut to the source file.
Changes to the source Excel worksheet will be reflected in your document.

Presentation :

 An informative video tutorial from the August 2011 edition of PC User
magazine, entitled "Mistakes to Avoid in Excel" by Helen Bradley, was shown.
The mistakes to avoid are:
 Don't skip columns when creating a worksheet.
 Blank columns can lead to complications when creating a chart.
 Don't use numbers as column headings.
 This can lead to problems when using the AutoSum function.
 Don't use the Space Bar to delete a cell's contents.
 The Space Bar leaves a character in a cell that cannot be seen.
This may lead to printing unwanted blank sheets.
 Don't waste paper when printing.
 Use Page Setup… > Page > Scaling options
to manage the number of sheets required to print your worksheet.
 Format cells to indicate value units.
 For example, if the value in a cell represents 6 kg, it can be shown as
6 kg rather than just 6.

Select Format Cells… > Number > Custom.

After displaying the required number format in the Type: box,
add "kg" (include the inverted commas).

The letters "kg" will appear after values in cells formatted in this way but
Excel will still treat the contents of the cells as numbers when performing calculations.
 Be aware of the order of precedence when creating formulas.
 Excel performs mathematical operations in the following order:

Brackets (or parentheses)

Exponentiation (eg: 3^2)

Multiplication (eg: 3*2)

Division (eg: 3/2)

Addition (eg: 3+2)

Subtraction (eg: 32)
 So, Excel will evaluate the formula
"= 6 + 4 * 3 / 2  1" as 11.
 If you wanted to add the 6 and 4 before multiplying the sum by 3,
you would need to place 6 + 4 in brackets.
 Excel will evaluate the formula
"= (6 + 4) * 3 / 2  1" as 14.
 Brackets are used to change the order of precedence for
mathematical operations in formulas.

....................................
GRT

Tuesday 11 September 2012

Attendance :

8

Presentation :

 Geoff Turton presented the topic
"Using Excel to Extract Information from a Set of Numbers" 
Charts.
 What is Information?
 The father of Information Theory, Claude Shannon's definition is:
"That which resolves uncertainty".
 A short YouTube video entitled
Information
makes the following main point 
A text document, a drawing or a set of numbers is not information but rather,
information is the message(s) contained in the text document, drawing or set of numbers.
 In the case of a set of numbers in an Excel worksheet, charts provide
an effective means of extracting any message(s) contained therein.

Members' Projects :

 Various HSCC financial and membership worksheets
were used to generate charts to reveal useful information.

....................................
GRT

Tuesday 14 August 2012

Attendance :

6

Presentation :


Members' Projects :

 The group examined a clever use of the VLOOKUP function
to record Bowls Competition scores in a workbook
supplied by Les Gersback.

....................................
GRT

Tuesday 10 July 2012

Attendance :

10

Q & A :

 Why do I receive Circular Reference messages?
 From Excel 2007 Help:
"When a formula refers back to its own cell, either directly or indirectly,
it is called a circular reference. Microsoft Office Excel cannot automatically calculate
all open workbooks when one of them contains a circular reference.
You can remove a circular reference, or you can have Excel calculate
each cell involved in the circular reference once by using the results of the previous iteration
Unless you change the default settings for iteration, Excel stops calculating after 100 iterations
or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first."

Presentation :

 Graeme Gavin demonstrated options for using and customising the Quick Access Toolbar in Excel 2007/10.
 Geoff Turton demonstrated the useful options available to manage club membership records when a data
range is converted to a List (Excel 2003) or a Table (Excel 2007/10).

Discussion :

 Compatibility Mode
 From Excel 2007 Help:
"To ensure that a Microsoft Office Excel 2007 workbook does not have compatibility
issues that cause a significant loss of functionality or a minor loss of fidelity
in an earlier version of Excel, you can run the Compatibility Checker.
The Compatibility Checker finds any potential compatibility issues and
helps you create a report so that you can resolve them.
When you work on a workbook in Compatibility Mode, where the workbook is in Excel 972003
Binary file format (BIFF8) instead of the new Office Excel 2007 XMLbased file format (.xlsx),
the Compatibility Checker is automatically run when you save a workbook."
 IF Function

....................................
GRT

Tuesday 12 June 2012

Attendance :

7

Presentation :


Excel on the Internet :


Members' Projects :

 Geoff Turton demonstrated how sorting, filtering and data tables
could be used to analyse responses from HSCC members on their
ownership of and interest in using tablet computers.

....................................
GRT

Tuesday 8 May 2012

Attendance :

8

Presentation :


Excel on the Internet :

 Using Google to Search a Specific Website

To search a specific website, enter your search requirements into Google as follows:
Required topic site:required site
For example:
Singleuse dropdown list site:excel.tips.net
 The following Excel tips were discussed:

Singleuse Dropdown Lists

Additional Ways of Displaying a Hidden First Column

Members' Projects :

 The group examined a worksheet Les Gersback uses to record
Pennant Bowls Teams scores.
Discussion included potential applications of the
excel.tips.net topics covered above.

....................................
GRT

Tuesday 10 April 2012

Attendance :

9

Presentation :


....................................
GRT

Tuesday 13 March 2012

Attendance :

7

Presentation :


....................................
GRT

Tuesday 14 February 2012

Attendance :

8

Excel on the Internet :

 Excel.tips.net provides a wide range of useful tips for creating Excel worksheets.
There are two versions of the site:
excel.tips.net
for Excel versions 2003 and earlier, and
excelribbon.tips.net
for Excel versions 2007 and later.

Presentation :

 Geoff Turton conducted a tutorial on
Formatting Cells (Alignment, Borders and Fill)

....................................
GRT