1 / 38

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment Chapter 7 Automating Repetitive T

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment Chapter 7 Automating Repetitive Tasks and Customizing Excel. Automating Repetitive Tasks and Customizing Excel. Quick Links to Presentation Contents. Automate Tasks Using Macros

sitara
Download Presentation

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment Chapter 7 Automating Repetitive T

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. Excel 2010 Level 2 Unit 2Managing and Integrating Data and the Excel Environment Chapter 7Automating Repetitive Tasks and Customizing Excel

  2. Automating Repetitive Tasks and Customizing Excel Quick Links to Presentation Contents • Automate Tasks Using Macros • Pin Workbooks to the Recent Workbooks List • Customize the Quick Access Toolbar • CHECKPOINT 1 • Change Display Options to Customize the Work Area • Minimize the Ribbon • Customize the Ribbon • Create and Apply a Custom View • Save a Workbook as a Template • Customize Save Options • CHECKPOINT 2

  3. Automate Tasks Using Macros • A macrois a series of instructions stored in sequence that can be recalled and carried out whenever the need arises. • It is helpful to create a macro when you find yourself frequently and consistently carrying out the same task. • Saving the instructions for a task in amacro not only saves time but also ensures that the steps are carried out consistently every time. This can help prevent errors in data entry and formatting, among others.

  4. Automate Tasks Using Macros…continued To record a macro: • Click the View tab. • Click the down-pointing arrow on the Macros button in the Macros group. • Click the Record Macro option at the drop-down list. continues on next slide… Record Macro option

  5. Automate Tasks Using Macros…continued • At the Record Macro dialog box, type a macro name. • Click in the Description text box. • Type the description text. • Click OK. • Perform the desired actions. continues on next slide… Record Macro dialog box

  6. Automate Tasks Using Macros…continued • Click the Stop Recording button located near the left side of the Status bar. Stop Recording button

  7. Automate Tasks Using Macros…continued • When a macro is created in Excel, the commands are written and saved in Microsoft Visual Basic. The macro recorder converts your actions to Visual Basic statements for you behind the scenes. • A workbook that contains a macro should be saved using the macro-enabled file format. The default XML-based file format (.xlsx) cannot store VBA macro code.

  8. Automate Tasks Using Macros…continued To save a macro-enabled workbook: • Click the File tab. • Click the Save As option. • If necessary, navigate to the desired drive and/or folder. • Type the file name in the File name text box. • Click the Save as type list arrow. • Click the Excel Macro-Enabled Workbook (*.xlsm) option. • Click Save. Excel Macro-Enabled Workbook (*.xlsm) option

  9. Automate Tasks Using Macros…continued To run a macro: • Click the View tab. • Click the Macros button in the Macros group. continues on next slide… Macros button

  10. Automate Tasks Using Macros…continued • At the Macro dialog box, double-click the macro name. Macro dialog box

  11. Automate Tasks Using Macros…continued To assign a macro to a shortcut key: • Click the View tab. • Click the down-pointing arrow on the Macros button in the Macros group. • Click the Record Macro option. • Type a macro name. • Click in the Shortcut key text box. • Type the desired letter. • Click in the Description text box. • Type the description text. • Click OK. • Perform the desired actions. • Click the Stop Recording button. Shortcut key assigned

  12. Automate Tasks Using Macros…continued To edit a macro: • Open the workbook containing the macro. • Click the View tab. • Click the Macros button in the Macros group. • Click the desired macro name. • Click the Edit button. continues on next slide… Edit button

  13. Automate Tasks Using Macros…continued • Make the desired changes in the Visual Basic code window. • Click the Save button. • Click File. • Click the Close and Return to Microsoft Excel option. Visual Basic code window

  14. Automate Tasks Using Macros…continued • If you create macros that you want to use in other workbooks, one solution is to leave the workbook containing the macros open since the Macro dialog box, by default, displays macros from all open workbooks in the Macro name list box. • Consider creating a macros workbook with a set of standard macros that you wish to use in any file.

  15. Pin Workbooks to the Recent Workbooks List • By default, the Recent tab Backstage view displays the twentymost recently opened workbook file names in the Recent Workbooks section in the left pane. • The right pane displays a list of folders to which you have recently navigated in a section titled Recent Places.

  16. Pin Workbooks to the Recent Workbooks List…continued To pin a workbook to the Recent Workbooks list: • Make sure the workbook has been opened recently. • Click the File tab. • If necessary, click the Recent tab. • Click the pin icon next to the workbook name. pin icon

  17. Customize the Quick Access Toolbar To customize the Quick Access toolbar: • Click the Customize Quick Access Toolbar button. • Click the More Commands option. continues on next slide… More Commands option

  18. Customize the Quick Access Toolbar…continued • Click the down-pointing arrow at the right of the Choose commands from option. • Click the desired category. • Double-click the desired command in the commands list box. • Click OK. Choose commands from option

  19. CHECKPOINT 1 • This is a series of instructions stored in sequence that can be recalled and carried out. • mini • maxi • micro • macro • By default, macros are stored within the workbook that is this. • printed • active • saved • closed Answer Answer Next Question Next Question • The Macros button is located in this tab. • View • Review • Data • Formulas • The Recent tab Backstage view displays how many most recently opened file names by default? • 10 • 15 • 20 • 25 Answer Answer Next Question Next Slide

  20. Change Display Options to Customize the Work Area To customize display options: • Click the File tab. • Click the Options button. • Click the Advanced option in the left pane. • At the Excel Options dialog box, change the display options as required. • Click OK. Excel Options dialog box

  21. Minimize the Ribbon To minimize the Ribbon: • Click the Minimize the Ribbon button. Minimize the Ribbon button

  22. Customize the Ribbon To create a new tab: • Click the File tab. • Click the Options button. • Click the Customize Ribbon in the left pane. • Click the tab name to precede the new tab. • Click the New Tab button. New Tab button

  23. Customize the Ribbon…continued To rename a tab or group: • Click the File tab. • Click the Options button. • Click the Customize Ribbon option in the left pane. • Click the tab or group to be renamed. • Click the Rename button. • Type the new name. • Press Enter or click OK. Rename button

  24. Customize the Ribbon…continued To add buttons to a group: • Click the File tab. • Click the Options button. • Click the Customize Ribbon option in the left pane. • Click the group name in which to insert the new button. • Change the Choose commands from option to the desired command list. • Scroll down and click the desired command. • Click the Add button. Add button

  25. Customize the Ribbon…continued To reset the Ribbon: • Click the File tab. • Click the Options button. • Click the Customize Ribbon option in the left pane. • Click the Reset button. • Click the Reset all customizations option at the drop-down list. • Click Yes at the Microsoft Office message box. • Click OK. Reset all customizations option

  26. Create and Apply a Custom View • A custom view saves display and print settings for the active worksheet. • You can create multiple custom views for the same worksheet. Toapply a view, select a stored view name at the Custom Views dialog box.

  27. Create and Apply a Custom View…continued To create a custom view: • Change the display and print settings as desired. • Click the View tab. • Click the Custom Views button in the Workbook Views group. • Click the Add button. • At the Add View dialog box, type a name for the custom view. • Choose the desired Include in view options. • Click OK. Add View dialog box

  28. Create and Apply a Custom View…continued To apply a custom view: • Click the View tab. • Click the Custom Views button in the Workbook Views group. • At the Custom Views dialog box, click the desired view name. • Click the Show button. Show button

  29. Save a Workbook as a Template • Templatesare workbooks with standard text, formulas, and formatting. • Cells are created and formatted for all of the entries that do not change. • Cells are also created for variable information. These cells are formatted but left empty since they will be filled in when the template is used to generate a worksheet. • Several templates were installed on your computer when you installed Microsoft Office. Other templates are available for download from Microsoft Office Online.

  30. Save a Workbook as a Template…continued To save a workbook as a template: • Open the workbook. • Make the desired changes. • Click the File tab. • Click Save As. • Change the Save as type to the Excel Template (*.xltx) option. • Type the desired file name. • Click the Save button. Excel Template (*.xltx) option

  31. Save a Workbook as a Template…continued To use a custom template: • Click the File tab. • Click New. • Click the My templates option. • At the New dialog box, double-click the desired template. New dialog box

  32. Save a Workbook as a Template…continued To delete a custom template: • Click the File tab. • Click New. • Click the My templates option. • At the New dialog box, right-click the desired template name. • Click the Delete option. • Click Yes. • Close the New dialog box. • Click the Home tab. Delete option

  33. Customize Save Options • AutoRecoversaves versions of your work at a specified time interval so that you can restore all or part of your data should you forget to save or otherwise experience a situation (such as a power outage) which causes Excel to close unexpectedly.

  34. Customize Save Options…continued • In conjunction with AutoRecover, Excel includes the AutoSavefeature which will save the last version of a workbook in a temporary file.

  35. Customize Save Options…continued To customize Save options: • Click the File tab. • Click the Options button. • Click Save in the left pane. • Change the save options as required. • Click OK. save options

  36. CHECKPOINT 2 • Press these keys to minimize the Ribbon. • Shift + F1 • Ctrl + F1 • Shift + F3 • Ctrl + F3 • These are workbooks with standard text, formulas, and formatting. • documents • forms • worksheets • templates Answer Answer Next Question Next Question • The Custom Views button is located in this tab. • File • Page Layout • View • Review • In conjunction with AutoRecover, Excel includes this feature. • AutoSave • AutoFile • AutoKeep • AutoOpen Answer Answer Next Question Next Slide

  37. Automating Repetitive Tasks and Customizing Excel Summary of Presentation Concepts • Record, run, and edit a macro • Save a workbook containing macros as a macro-enabled workbook • Create a macro that is run using a shortcut key combination • Pin and unpin a frequently used file to the Recent Workbooks list • Add and remove buttons for frequently used commands to the Quick Access toolbar • Hide the ribbon to increase space in the work area • Customize the display options for Excel • Customize the ribbon by creating a custom tab and adding buttons • Create and apply custom views • Create and use a template • Customize save options for AutoRecover files

More Related