Successful ICT Projects In Excel (3rd Edition)

Pat Heathcote has worked as a programmer and systems analyst, and has many years of experience in teaching Computing and Information Technology. She has served as an Assistant Examiner with AQA and is on the Subject Committee for Computing. She is now a full-time author and publisher.

Chapter 1 - Workbooks and Worksheets: Introduction; The Excel environment; Opening a new workbook; Anatomy of a worksheet; Opening a new workbook and saving it; Adding, deleting and naming sheets; Inserting the same information in several worksheets at once; Moving cell contents; Copying and pasting cells between worksheets; Copying a worksheet; Selecting cells in a worksheet; Entering and editing data and comments; Freezing panes to keep column labels visible; Viewing several worksheets simultaneously; Entering simple formulae; Printing a worksheet. Chapter 2 - Editing and Formatting: Enhancing the appearance of a worksheet; Adding and deleting rows and columns; Copying series; Adjusting the system date settings; Formatting dates; Copying formats to other cells; Editing a formula; Formatting numbers; Conditional formatting; Formatting headings; Adding borders and colours; Headers and footers; Printing the worksheet. Chapter 3 - Writing Formulae: Formula basics; Entering formulae; Using labels and editing formulae; Naming individual cells; Using names on different sheets; Creating duplicate names in a workbook; Writing formulae which refer to other sheets; Creating row and column headings; consisting of static values; Using relative addresses; Using absolute addresses; Using mixed references in a formula; Checking your formulae; The Auditing toolbar; Detecting whether cells contain values or formulae. Chapter 4 - Using Functions: Introduction; Setting up the Chemistry worksheet; Generating random numbers as test data; Converting formulae to values; Counting the number of values in a range; Naming cells and ranges; Rules for valid names; Calculating the total and percentage marks; Creating a Lookup table; Using the Lookup table; Creating effective test data; The IF function; Nested IF statements; Using Goal Seek. Chapter 5 - "What if" Scenarios: "What if.."; Creating and naming input cells; Creating a scenario of the current situation; Creating additional scenarios; Viewing the scenarios; Creating a scenario summary; Using Solver. Chapter 6 - Linking Workbooks: Introduction; Organising data in different workbooks; Creating the Customer, Product and Sales workbooks; Opening a group of files that share a common characteristic; Naming columns as cell ranges; Copying sheets into a single workbook; Looking up values from other workbooks; Testing the application; Using a built-in data form; Problems with links; Updating links to other workbooks; Adding a new product. Chapter 7 - Filtering, Sorting; & Subtotalling Lists: Introduction; Filtering to display a subset of records; Finding the top six values; Sorting data; Summarising data with subtotals; Multiple levels of subtotals; Showing and hiding levels of detail; Pasting subtotals into a Word document; Removing subtotals and filters; Creating a PivotTable; Editing the PivotTable; Showing more detail in a PivotTable; Showing average sales quantities. Chapter 8 - Creating Charts: Charts and graphs; Using Edit, Replace; Summarising using a PivotTable; Creating a monthly summary; Creating a PivotChart report; Customising the chart; Printing the chart; Creating different charts by changing the PivotTable; Making the embedded chart into a chart sheet; Forecasting a sales trend. Chapter 9 - Printing: Introduction; Previewing the worksheet; Setting print titles; Printing a worksheet on a specified number of pages; Centering data on the page; Printing headers and footers; Printing selected areas of the worksheet; Printing an embedded chart; Printing a sheet and the embedded chart together. Chapter 10 - Forms & Templates: Introduction; Creating the basic form; Data validation; Selecting valid data from a list; Protecting the worksheet; Editing a protected sheet; Hiding gridlines; Hiding columns; Saving the workbook as a template; Using the template; Testing the template; Viewing the database; Displaying the formulae in a worksheet; Adding a combo box control; Retrieving the price from the list; Using a spinner button; Inserting a scroll bar for interest rates; Enhancing the form. Chapter 11 - Basic Macro: The macro recorder; Recording a macro to insert a new line in a table; Changing the shortcut key; Examining the macro code; Syntax of Visual Basic statements; Adding a message to the user; Stepping through a macro; Using the Visual Basic toolbar; Using relative references in a macro; Using the Object Browser to get help; Making a macro run other macros; Attaching the macro to a command button. Chapter 12 - Advanced Macros: Data processing; Recording the macro; Trying out your macro; Debugging a macro; If..Then..Else..Endif statements; Hiding operations from the user; Performing a loop in a macro; Auto_Open and Auto_Close macros. Chapter 13 - The User Interface: Creating a full-screen menu; Creating custom buttons and toolbars; Testing the custom button; Adding a custom menu; Deleting and restoring menus; Hiding, displaying and positioning toolbars. Chapter 14 - Project Ideas: Introduction; Input-Process-Output; Using advanced features of Excel; Preparing budgets; Expenses claims forms; Keeping accounts; Invoicing and sales summaries; Quotation systems; Student grades system; Inappropriate projects. Chapter 15 - The Systems: Life Cycle; Introduction; Problem definition; Analysis; Design; Development; Installation; Evaluation. Chapter 16 - The Project: Report; Introduction; The mark scheme; Creating an outline for your project; Reordering topics; Adding numbers to the headings; Turning the outline into a document; Adding a header and footer; Inserting a Table of Contents; Analysis/Specification; Design; Implementation; Testing; Evaluation; User documentation; Technical documentation; Handing it in. Appendix A - Sample Project; Appendix B - AQA Specification. Index