Templates • Templates can be produced for the following elements: • Text and Graphics • Formatting Information – Layouts, Styles • Headers and Footers • Formulas • Macros
Templates • Creating Templates • Create the Workbook you wish to save as a template • File | Save As • Type the name for the template in the name box • From the Save as type drop down list box, select Template • The extension .XLT is added to the filename and the template is saved in the Template folder
Templates • Using a Template • File | New • Select template from the template tab in the New dialog box • Select O.K. to open a copy of the template
Styles • Creating Styles • Select a cell formatted with the attributes you require • Format | Style • Type the Style name in the Style Name drop down list
Styles • Creating Styles • Select the Modify button to change any of the attributes • The Format Cells dialog box will be displayed • Click on the Number, Alignment, Font, Border, Pattern and Protection Tab
Macros • Macros can speed up any common editing sequence you may execute in an Excel Spreadsheet • Macros are small programs that perform a specific function, designed by the user
Macros (Creating One) • Click Tools|Macro|Record New Macro • Name the Macro in the Macro Name Field • Name must not begin with a number and cannot contain spaces • If you would like a shortcut key, enter the letter under Shortcut key • Enter a lower case letter to make a CTRL+number shortcut key • Enter a upper case letter to make a CTRL+SHIFT+number shortcut key • Note: If Excel already has that key combination assigned, your macro will overwrite it.
Macros (Creating One) • Select an option from the Store macro in drop-down menu • Personal Macro Workbook • This Workbook • New Workbook • Enter a description of the macro in the Description field • This is a reference for you • Click O.K. when you’re ready to start recording • Click Stop when the macro is completed
Macros (Creating One) • If the Stop Recording toolbar does not appear when you begin recording a macro, display it (as the last steps) • On the View menu, point to Toolbars, and then click Stop Recording. The Stop Recording toolbar appears. • Perform any additional actions you want to record in the macro. • On the Stop Recording toolbar, click the Stop Recording button.
Macros (Creating One) • On the Tools menu, point to Macro, and then click Macros. • Click the macro name that you recorded and click Edit. • Delete the following line from the macro: • Application.CommandBars("Stop Recording").Visible = True
Macros (Window) • To run/edit a macro you have created, select Tools|Macro|Macros from the menu bar • From the Macros window, highlight the Macro name in the list and click Run/Edit • If the want to stop a macro while it is running, press BREAK (hold CTRL + PAUSE)
Protection • To protect an entire workbook with a password do the following: • Open the workbook • On the Tools Menu, point to Protection, and then click protect Workbook • Ensure that the Structure checkbox is selected, type a password in the Password box, then press O.K. • Retype that password again for verification
Web Query(Pre-made) • Follow the following steps: • Be sure you have access and are connected to the World Wide Web. • Open a new workbook in Excel. • On the Data menu, click Get External Data, and then click Run Saved Query. • In the file list in the Run Query dialog box, click an iqy file • Microsoft Investor Stock Quotes.iqy. • Click Get Data. • In the Returning Data to Microsoft Excel dialog box, click OK
Web Query • Create Your Own Web Query • On the Data menu in Excel, point to Get External Data, and then click New Web Query. • In the first section of the New Web Query dialog box, type or paste an address for the Web page. Or, if you don't have the address, click the Browse button to start your browser • In the second section of the New Web Query dialog box, choose the data you want returned
Web Query • The entire page returns text, tables and other data on a page (does not include graphic images). • Only the tables returns all tables or preformatted (<PRE>) sections on the page. • One or more specific tables on a page returns only the table(s) that you specify. Use this option when you want data out of a specific table or tables only.
Web Query • In the third section of the New Web Query dialog box, choose the type of formatting you want returned: • None returns plain text without formatting such as font face or color. • Rich text formatting returns most Web page formatting such as font face and color, but does not return hyperlinks or other types of advanced formatting. • Full HTML formatting returns all of the Web page formatting that Excel supports, such as hyperlinks. • Click O.K.
Web Query • In the Returning Data to Microsoft Excel dialog box, specify where you want to put the data (starting in a cell you specify on an existing worksheet or, in a new worksheet).