Hurstville Seniors Computer Club
Excel Meetings - 2012
Other Years - 2011

Home > Excel > Meetings - 2012

  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 :
  1. How do you create a chart for data in non-adjacent cells?
    • Remove any rows that contain blank cells in the columns to be charted.
    • Use Ctrl + Left-click and Shift + Left-click 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.
  2. 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.
      • Right-click anywhere in the selected range and select Paste Special… from the context-sensitive 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 :
  1. 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.
  2. 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: 3-2)
      • 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 :
  1. 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 :
  1. 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 97-2003 Binary file format (BIFF8) instead of the new Office Excel 2007 XML-based file format (.xlsx), the Compatibility Checker is automatically run when you save a workbook."
  2. IF Function
    • An example of the use of the very powerful IF function was discussed. This function has the following format:

      IF(logical_test,value_if_true, value_if_false)

      IF checks whether a condition is met, and returns one value if TRUE, and another valuenif FALSE.

.................................... 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:
      Single-use drop-down list site:excel.tips.net
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     


Return to Top