1 / 12

Formulas and Advanced Features

Formulas and Advanced Features. R003. AO1: Use Formulas & features in your spreadsheet. Invoice sheet Absolute cell reference Macros Conditional Formatting Protecting worksheets. Invoice sheet. To create the invoice sheet you will be using a series of features listed below

tuwa
Download Presentation

Formulas and Advanced Features

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. 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

More Related