1 / 10

Efficient Data Collection and Storage Using Excel: A Guide for Level 3 Problem Solving

This resource provides an essential guide for Level 3 learners on how to efficiently collect and store data using spreadsheets. It emphasizes the importance of design features that enhance speed, safety, and accuracy, including input messages, macros, and data validation. Students will learn to create user-friendly interfaces, apply validation techniques, and link worksheets seamlessly. With clear instructions and suggestions for evidence, this guide supports learners in mastering basic Excel functionalities to improve data handling skills.

analu
Télécharger la présentation

Efficient Data Collection and Storage Using Excel: A Guide for Level 3 Problem Solving

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OCR Nationals Level 3 Unit 3 – Problem Solving AO3 Collect data and store it using a spreadsheet D.Garey

  2. Important Note: • THIS IS THE SPREADSHEET JUST FOR COLLECTING/COMPILING YOUR RAW DATA!!!!! • Don’t worry about any scary calculation, graphs etc yet, its not on this sheet!!! D.Garey

  3. Safety, Security, Efficiency • Conduct some brief research into different interfaces and identify design features that will improve speed, safety and accuracy • Think input messages, macros, protection and validation!!!!!! • Screen shots for evidence • For distinction- show understanding of security measures through typed document D.Garey

  4. How to create an interface in Excel Write your questions in the cells Write the relevant answers elsewhere on the worksheet D.Garey

  5. Validation… • Click on the cell where you want the answer to be, go to DATA then VALIDATION Ensure “LIST” is selected under “ALLOW” Click on Source icon and highlight the possible answers on the spreadsheet Click back on the icon to return to the validation menu D.Garey

  6. Validation… • If you’ve done it properly, you will see a drop down menu next to your question You can also specify numbers i.e. age range D.Garey

  7. Rename the Sheets • Right click on the sheet tabs to rename the questionaire “HOME” and sheet 2 as “RESULTS” • You also need to hide those values on the home sheet D.Garey

  8. Linking the Two Sheets • Go to TOOLS, MACRO and RECORD NEW Fill in the fields, click record and simply switch from HOME to RESULTS tab and click STOP Enable the FORMS toolbar setting and create a button to link the pages using your macro D.Garey

  9. Recording a Record • Create a new macro on the results page, called individual record • Click on RECORD, ensuring you are on RESULTS sheet • Insert a new row • Copy the first cell answer on the home sheet • Paste the cell into the RESULTS sheet • Continue copying and pasting the answers • Press STOP • Create a new command button on the home sheet called SUBMIT • Try it out!!!! • Now write a macro to clear the form, create a button and link it up D.Garey

  10. Video Tutorials • formatting spreadsheet • Validation • protecting cells D.Garey

More Related