BulletProofing your Excel Spreadsheets Tech Day 2013 Presentation

Bullet-Proofing your Excel Spreadsheets Tech Day 2013 Presentation Margaret Gill mjgill@noctrl. edu Chris Kardaras cjkardaras@noctrl. edu Marco V. Martinez mvmartinez@noctrl. edu

OUTLINE • • Overview Data Protection Why Use Macros? Enabling Macros, and a Warning Recording a Macro Capturing a Formula in a Macro Resources Tech Day 11/2013 2

PROTECTING DATA FROM ERRORS • Data errors can occur in many ways: – manual entry – data from external sources – data transmission errors • We can make better decisions if we can detect and prevent invalid data • In Excel, this is called Data Validation Tech Day 12/2013 3

WHERE IS IT FOUND? Select the cells/rows/columns that you want to validate, then Tech Day 12/2013 4

HOW TO CONFIGURE Tech Day 12/2013 5

WHAT IS THE PURPOSE OF MACROS? • Manipulate data • Apply style and formatting Tech Day 12/2013 6

HOW WOULD I EVER USE MACROS? • Manipulating research data Tech Day 11/2013 7

HOW WOULD I EVER USE MACROS? • Classroom gradebook Tech Day 12/2013 8

HOW WOULD I EVER USE MACROS? IF YOU ARE REPETITIVELY PERFORMING THE SAME ACTION IN EXCEL, THEN IT IS WISE TO USE A MACRO Tech Day 12/2013 9

ENABLING MACRO CREATION • To execute or work with macros, you must enable the Developer tab: , then Tech Day 12/2013 10

ENABLING MACRO EXECUTION • Most Excel installations have macro execution disabled by default • Be careful if you enable macro execution – you are allowing code to run on your computer Tech Day 12/2013 11

HOW DO I MAKE A MACRO? • Open sample excel file Tech Day 11/2013 12

HOW DO I MAKE A MACRO? • Click on the “Developer” tab or Tech Day 12/2013 13

HOW DO I MAKE A MACRO? • Do you need your macro to be dependent upon coordinates, or relative? – Dependent upon coordinates – only works in coordinates recorded – Relative – dependent upon where cursor is Dependent on coordinates Tech Day 12/2013 Relative Reference 14

HOW DO I MAKE A MACRO? • If want relative reference click “Relative Reference” • Click “Record” Tech Day 12/2013 15

HOW DO I MAKE A MACRO? Name your Macro Click “OK” Create shortcut for macro Tech Day 12/2013 16

HOW DO I MAKE A MACRO? RECORD YOUR MACRO! Tech Day 12/2013 17

HOW DO I MAKE A MACRO? • Click “Stop” when finished Tech Day 12/2013 18

HOW DO I RUN MY MACRO? • When open Excel click either – “Macros” – Macros command “Run” • (eg. Option + Command + A) Tech Day 12/2013 19

HOW WOULD I EVER USE MACROS? IF YOU ARE REPETITIVELY PERFORMING THE SAME ACTION IN EXCEL, THEN IT IS WISE TO USE A MACRO FORMULAS!!!! Tech Day 11/2013 20

HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 21

HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 22

HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 23

HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 24

HOW TO CREATE A NEW FORMULA? Tech Day 11/2013 25

HOW TO CREATE A NEW FORMULA? DEMONSTRATION Tech Day 11/2013 26

HOW TO CREATE A NEW FORMULA? Function Area(Length As Double, Width As Double) Area = Length * Width End Function Tech Day 11/2013 27

RESOURCES • Tips on Data Validation – http: //office. microsoft. com/en-us/excel-help/applydata-validation-to-cells-HP 010072600. aspx • Microsoft Excel Recording Macros – http: //office. microsoft. com/en-us/excelhelp/overview-RZ 102337714. aspx? CTT=1&section=1 • Excel for Dummies – http: //www. dummies. com/how-to/computerssoftware/ms-office/Excel-2013/Macros. Programming. html Tech Day 12/2013 28

RESOURCES • Writing Your First VBA Function in Excel – http: //www. fontstuff. com/vbatut 01. htm • Writing Your Own Functions In VBA – http: //www. cpearson. com/excel/writingfunctions invba. aspx Tech Day 11/2013 29