1. Formulas and Advanced Features R003

2. AO1: Use Formulas & features in your spreadsheet • Invoice sheet • Absolute cell reference • Macros • Conditional Formatting • Protecting worksheets

3. Invoice sheet • To create the invoice sheet you will be using a series of features listed below • Data validation • Spinners • User Comments

4. Data validation • Data validation • Used to create lists, error messages and other restrictions on the data that can be entered • To use this feature click on: the Data tab  data validation • The data validation menu will appear. Have a look at all of the options available

5. Spinners • A spinner will allow you to move the value in a cell up or down • To create a spinner go to the office but and click on Excel options • Select the ‘Developer tab’ box and click OK

6. Spinners continued • Select the Developer Tab at the top • Click on Insert and choose the spin button Icon • Draw the button next to the cell you want to link it to • Right click in the button and choose format control

7. Spinners continued • This menu will appear • Choose your minimum and maximum values • In the cell link box write the cell reference that you want to control. Look at my example

8. User Comments • It is very easy to add a comment to a cell • Comments are used to give guidance and instructions to the user • Most commonly they are used to tell the user what kind of data should be entered into a particular cell • To do a comment you go to the Review tab and choose New comment

9. Absolute cell reference • This is a special type of cell reference that is used when replicating a formula in which one of the cell reference must stay the same • The to make a normal cell reference absolute you simple add the dollar sign in front of the letter and the number • E.g.$A$1

10. Macros • To create a macro you will have to record the actions you want to repeat often • Go to • Developer Tab • Record Macro • Give it a sensible name e.gMacroPrint (no spaces) • Record your actions • Go to the Developer tab stop recording • Insert a button (using the developer tab) and assign the macro to it

11. Conditional Formatting • This is used to highlight cells with specific information • To apply a conditional format you • Highlight the cells you want to use • Click on the conditional formatting tool on the tool bar • Click on New Rule • Set your parameters and click on the Format button • Choose the formatting you want and click ok

12. Password Protecting • To password protect a work book do the following • Click on the Review tab • Choose protect worksheet • Enter the password twice • Be sure to record the password somewhere where you can easily find it