Excel Adv Class

Brandeis University

ITS Training: Advanced Excel

ITS Training Advanced Excel 17.1 Database Features To use Excel’s database features with a cluster of data, the data must be separated by at least one row and one column, except for a heading, from the rest of the spreadsheet. Each row of the spreadsheet is the same as a record in a database and each column represents a field. Using Filters Filters let you screen the data so that only the entries you want are used. To use filters, open the Data menu, choose Filter, then AutoFilter. Arrows appear in the headings. Click the drop down arrow and choose the value to filter on. A sample work sheet is shown in Figure 17.1. In this worksheet, we have clicked the arrow in the department column to show the drop down menu. If we were to select accounting, only the records of the employees in accounting would be displayed. To view all the records again, click the drop down arrow once more and choose All. Or, in the Data Menu, choose Filter then Show All.

Page 1

Brandeis University

ITS Training: Advanced Excel

Figure 17.1 The arrows next to the column headings indicate that Excel is functioning as a database. Clicking on the drop down arrow next to a heading (Department in our example) shows a list of the choices for filtering.

You can also filter using mathematical and Boolean operators. Suppose you wish to display only those with a salary between $40,000 and $50,000. Click the drop down arrow in the Current Salary column and choose Custom. Choose the operator is greater than or equal to (>=)and the value $40,000. Select And. Choose the operator is less than or equal to (<=) and type the value $50,000. Click OK. The desired records are displayed. You can use also wildcards for filtering. For example, in the LastName field, choose Custom and type P*. This pulls up all names beginning with P. To turn off filters, open the Data Menu, choose Filter and click AutoFilter again. Form View To view one record at a time, click the Data Menu and choose Form. Click the Find Next or Find Prev buttons to move back and forth through the records. The New and Delete buttons let you add and delete records. Clicking the Criteria button lets you enter search criteria and filter records.

Page 2

Brandeis University

ITS Training: Advanced Excel

Sorting You can sort records in alphabetical or numerical order based on any text or numerical field. You can sort in ascending or descending order. For text, ascending means a to z and descending z to a. To sort records, open the Data Menu and choose Sort or use the toolbar buttons. Figure 17.2 shows the Sort dialog box for the worksheet in Figure 17.1. Note you can sort on any of the worksheet fields. Figure 17.2 Clicking the drop down arrows lets you choose the sort criteria in the Sort window.

Subtotals Excel allows you to calculate subtotals based on any field on which you are sorted. On the Data Menu, click Subtotals. In the box At each change in, specify the field you are sorted on. Choose a function (sum, count, average, etc.), and choose a field to display the values. Every time the value in the sort field changes, Excel calculates a subtotal. For example, completing the Subtotal dialog box as in Figure 17.3, would take the data in the spreadsheet in Figure 17.1 and calculate the total current salary by department as well as a grand total. When the subtotals are calculated, you will notice that the numbers 1,2, and 3 appear in the upper left corner of the spreadsheet. Choosing 1 displays the grand total only. Choosing 2 displays the grand total and subtotals. Choosing 3 displays the grand total, the subtotals and the individual records To get rid of the subtotals, click any cell, open the Data Menu, click Subtotals then Remove All.

Figure 17.3 The Subtotal dialog box shown here is completed to display subtotals of current salaries by department. The original spreadsheet is seen in Figure 17.1.

Page 3

Brandeis University

ITS Training: Advanced Excel

17.2 Advanced Charting As we learned in Intermediate Excel, one of Excel’s best features is its ability to quickly and easily convert your data to a wide variety of chart formats. Excel charts are fully customizable, that is you can control every element of the chart from the graph itself to the title, the axis labels, the units, the legend, the background, the colors, etc. To illustrate this, we will create a chart using the data in Section 17.1 above. You will recall that to create a chart you first select the cells that contain the data you want displayed in the chart. Then select the Chart command from the Insert Menu or click the Chart toolbar button. The Chart Wizard starts and Step 1 asks you to choose the type and subtype of chart. We will create a pie chart with a 3-D visual effect. Click Next for Step 2 to preview the chart.

Figure 17.4 The types of charts available include column, bar, line, pie, scatter, area and others.

Click Next for Step 3 and give the chart a title. Depending on the type of chart, you may also be able to provide labels for the x and y axes (see Figure 17.5). Use the Legend tab (Figure 17.5) to locate the legend on the bottom, top or sides or to delete it altogether. A legend identifies the chart components by colors or patterns. Select the Data Labels tab (Figure 17.5) and show the values or percentages in your chart if you so desire. Note that every change you make appears in the preview window so you can decide if you like it and want to keep it.

Page 4

Brandeis University

ITS Training: Advanced Excel

Figure 17.5 The Chart Options window lets you add a chart title, x and y axis labels, a legend and individual data labels.

Click Next again for the final step and you are asked to choose if you want the chart to be on a separate worksheet or if you want it to appear as an object on your current worksheet. The chart we created for the data in Figure 17.1 appears below in the pie chart in Figure 17.6. Salary by Department 20%

15%

9% 21% 12%

3% 7%

13%

Accounting Total

Administration Total

Data Processing Total

Human Resources Total

Inventory Total

Payroll Total

Purchasing Total

Sales Total

Figure 17.6 The salaries from the spreadsheet in Figure 17.1 have been sorted by department, subtotaled and turned into a 3-D pie chart.

You can move the position of your chart or change its size. Click in the white area of the chart. Sizing handles appear around the box. Click again and hold and your pointer turns to a four directional arrow. Move the chart where you want it and release the mouse button. To size the chart, grab one of the sizing handles with your mouse and pull. Note that only the corner handles retain the proportionality of the chart.

Page 5

Brandeis University

ITS Training: Advanced Excel

Double click on the chart itself and the Format Data Series window appears. The Patterns tab lets you change borders, colors, and fill effects including patterns, textures and gradients. The Data Labels tab gives you the opportunity to add data labels as in Step 3 of the Chart Wizard above. The Options tab lets you rotate the chart. Double click on the chart title. The Format Chart Title window appears and gives you the chance to change the font, font size, alignment, color, border and fill effects. Clicking on the legend will give you the same options to modify the legend. Even individual elements of the chart itself are modifiable. To emphasize a particular slice of the pie in our chart, select the individual slice by clicking once on the pie, then once on the slice. Drag the slice away from the others to form an exploded pie chart. You can also use the Chart toolbar or the Chart Menu to make formatting changes or to change the type of chart.

Tip: If you have created a chart and decide that you want to add additional data to it, you can do so three ways: • Open the Chart menu and select Add Data. Select the cells that contain the data you want to add to the chart. If you want the column or row label for the new data to appear in the chart, include that cell in your selection. Click OK. • Select the cells that contain the data you want to add to the chart. If you want the column or row label for the new data to appear in the chart, include that cell in your selection. Click Copy. Click the chart. Click Paste. If you are not satisfied with the appearance of the added data, click Undo then open the Edit menu and choose Paste Special. Specify how you want to plot the data in the chart and select the options you want. • For embedded charts, you can select the data in your spreadsheet, put the mouse on the border of the selection and, when it becomes an arrow, drag it onto the chart. Release the mouse button when a + appears and the data is added to the chart.

17.3 Linked and Embedded Objects We already know how to copy information between programs using Cut and Paste. Items shared this way are static; they cannot be updated if the information in them changes without recreating the item in the original program and then repeating the Cut and Paste. Using linked and embedded objects to share information between programs allows for easier, even automatic, updating of information. Linked and embedded objects can be created in all MS Office programs as well as many others. You can create a new embedded object, or you can create a linked object or an embedded object from an existing file.

Page 6

Brandeis University

ITS Training: Advanced Excel

The main differences between linked objects and embedded objects are where the data is stored and how it is updated after you place it in the destination file. With a linked object, information is updated only if you modify the source file. Linked data is stored in the source file. The destination file stores only the location of the source file and displays a representation of the linked data. Use linked objects if file size is a consideration. With an embedded object, information in the destination file does not change if you modify the source file. Embedded objects become part of the destination file and, once inserted, are no longer part of the source file. Double-clicking on the embedded object, however, opens the source program and allows you to edit the object in the destination file. To create a new embedded object, click in the document where you want to place the embedded object. Open the Insert menu, click Object, and then click the Create New tab. In the Object type box, click the type of object you want to create. To display the embedded object as an icon (rather than see the object in its entirety), select the Display as icon check box. To prevent the embedded object from being displayed as a drawing object that you can position in front of or behind text and other objects, clear the Float over text check box. Note that only programs that are installed on your computer and that support linked and embedded objects appear in the Object type box. Also note that when you click Microsoft Excel Worksheet in the Object type box, an entire workbook is inserted into your document. The document displays only one worksheet at a time. To display a different worksheet, double-click the Excel object, and then click a different worksheet. You can also create a linked object or embedded object from an existing file. Click in the document where you want to place the linked object or embedded object. Open the Insert menu, click Object, and then click the Create from File tab. In the File name box, type the name of the file you want to create a linked object or embedded object from, or click Browse to select from a list. To create a linked object, select the Link to file check box. Otherwise, an embedded object is created. To prevent the linked object or embedded object from being displayed as a drawing object that you can position in front of or behind text and other objects, clear the Float over text check box. To display the linked object or embedded object as an icon, select the Display as icon check box. Example: Open Word. Type: Dear Dean, Here is a breakdown of our applications to date for next year. Insert the Excel file LINKSAMPLE as an embedded object. Double click on it. Change the number of applicants in March to 425. Click in the text portion of the document to return to Word. Open the Excel program. Open the spreadsheet LINKSAMPLE in Excel. The change has not been updated because the data is stored in the destination file.

Page 7

Brandeis University

ITS Training: Advanced Excel

Figure 17.7 The Excel file LINKSAMPLE has been inserted as an embedded object into a Word document. Note that the Excel toolbars are available within the Word program.

Delete the embedded object. Insert the Excel file LINKSAMPLE as an linked object. Save the Word file as LINKLETTER and close it. In Excel, open the spreadsheet LINKSAMPLE. Change the number of applicants in March to 425. Save the file and close it. Switch to Word and open LINKLETTER. The spreadsheet has been updated. Delete the linked object. Now insert the object and check Display as Icon. Tips: When using linked and embedded objects be careful that: • You only use linked objects if you want the information to be updated every time the original data changes in the source file. • If you use linked objects, the source file must remain available on your computer or network. Use embedded objects if you or others will be using the destination file on another computer or one without needed network access. • You have sufficient disk space. Remember that an embedded object becomes part of the destination file and thus increases its file size.

Page 8

Brandeis University

ITS Training: Advanced Excel

17.4 Pivot Tables A pivot table is used to quickly summarize a large amount of data in a format you choose. You create pivot tables from existing data. You specify what data is to be included and how to organize that data by choosing from fields (column labels) and items. Open the spreadsheet PIVOTSAMPLE. In the sample, region is a field and west is an item. The items become the row and column headings in the pivot table. To use pivot tables successfully, the row, column and page fields usually contain a limited set of values. The data fields contain the data you want summarized. In PIVOTSAMPLE, month, type, salesperson and region would make good row or column headings. Sales and Units would be good data fields. Excel uses the sum finction for numerical data and the count function for text data. You can change the function (e.g., to average) through the Data menu, Pivot Table Field command.

Figure 17.8 Parts of a Pivot Table

Page 9

Brandeis University

ITS Training: Advanced Excel

To filter data in a pivot table, use a page field. A page field breaks data into separate pages so you can see data for one item at a time. To see data for a second item, use the drop down arrow next to the page field. To create a pivot table, use the PivotTable Wizard. If you are basing the PivotTable on a Microsoft Excel list or database, first click a cell in the list or database. Open the Data menu and select PivotTable Report. The wizard takes you through four steps. Step 1 - Select the type of data source. Your choices are: An Excel list or database External data sources including Access, FoxPro, DBase, Oracle and SQL Server You can combine and summarize data from multiple Excel worksheets An existing pivot table Step 2 - Specify the location of the data. If you have already clicked inside the Excel spreadsheet or database, this is done for you. Step 3 – Construct the layout of your pivot table by dragging the field buttons on the right to the diagram on the left. Drag the fields you want to use as row and column field labels into the row and column areas. Drag the fields you want summarized into the data area. You can include more than one field in row or column areas for greater detail. If you move a field into the page area, the pivot table displays data for only one item in that field at a time. Let’s create a table in PIVOTSAMPLE that sumarizes the type of sales (that is, Meat, Dairy, Beverages, Produce) by region. Our data field is sales. Step 4 - Specify where you want the pivot table to appear. It can appear in the worksheet containing the data or in a separate worksheet. Choose Finish. The Query and Pivot toolbar appears to let you customize the pivot table. You can rearrange fields, display or delete totals, change field names, change the format, etc. When you save the pivot table, Excel stores a copy of the data source as hidden data along with the pivot table layout. If you are using a large amount of data and don’t want to store it, clear the Save Data with Table Layout box in step 4. Next time you change or reefresh the table, it will be updated from the data source. More examples: 1. a. Create a pivot table that sumarizes the type of sales (column) by the year and salesperson (rows). Once again, the data field is sales. b. Move the year field from a row to a column and observe the change.

Page 10

Brandeis University

ITS Training: Advanced Excel

2. a. Create a pivot table that plots year versus region using the sales field as the data field. b. Add a page field for the salesperson.

17.5 Importing Data from a Database to Excel To import data from a database into Excel, you must: • • •

Have access to the database. If the data you want access to is not on your local computer, you may need to see the administrator of the database for a password, user permission or other information about how to connect to the database. Install Microsoft Query. Microsoft Query is an optional feature of Excel and includes the Query Wizard. Instructions for installing Microsoft Query are at the end of this section. Install an ODBC driver for your data source. An ODBC driver is required to retrieve data, including data in Excel. An ODBC driver is a dynamic-link library (DLL) file that Microsoft Query and Excel can use to connect to a particular database. Each database program, such as Access or dBASE, requires a different driver.

Unless you change the options when you install Microsoft Query, the setup program automatically installs three ODBC drivers that allow you to retrieve data from dBASE, Access and Excel. The program gives you the option to install other ODBC drivers as well. ODBC drivers are available for Access, dBASE, Excel, FoxPro, Paradox, SQL Server, text and HTML databases. It is possible to use other ODBC drivers that do not come with Excel; these should be investigated on a case-by-case basis. Other ways to import data into Excel include: • Data can be imported from a Microsoft Exchange or Lotus 1-2-3 data source by using DAO in Visual Basic. • You can retrieve data from external sources that you want to summarize in a PivotTable using the PivotTable Wizard. • A Visual Basic macro can be used to gain access to an external data source. • External data from a World Wide Web source or intranet source can be retrieved with a Web query (see below). Getting External Data Using the Query Wizard 1. Click the cell where you want to start the external data range. 2. On the Data menu, point to Get External Data, and then click Create New Query. 3. On the Databases tab, select the database from which you want to retrieve the data. If you need to set up a new data source, double-click New Data Source then specify the data source.

Page 11

Brandeis University

4. 5. 6.

ITS Training: Advanced Excel

Make sure the Use the Query Wizard to create/edit queries check box is selected then click OK. Follow the instructions in the Query Wizard. To return the data directly to Excel, choose Return Data to Microsoft Excel, click Finish, then select where you want to put the external data.

To refine the query further, click View Data or Edit Query in Microsoft Query, click Finish, then make the changes you want. If you choose to refine the query further in Microsoft Query, the result set is displayed in Microsoft Query. If you want, you can return the result set to Microsoft Excel at a later time. You can check the status of a query that's running in the background and taking a long time to return data. While the query is executing, click Refresh Status on the External Data toolbar. To cancel the query, click Stop Refresh. Importing Data from the World Wide Web To retrieve external data from a Web source, you do not need to install Microsoft Query. However, you must have access to the Internet either through the Brandeis network or through a modem and an Internet service provider on your home computer. In addition, you must have a Web query (.iqy file) to run. If you know how to create forms in HTML, you can create a Web query to retrieve external data from a World Wide Web or intranet source. Excel includes sample Web queries that you can modify by changing the HTML code using Notepad or another text editor. You can find these samples in the Queries folder in the Microsoft Office folder. To run a web query: 1. Open the Data menu, choose Get External Data and then click Run Web Query. 2. Select the Web query you want to run. A Web query has an .iqy file name extension. 3. Click the Get Data button. The Returning External Data to Microsoft Excel dialog box is displayed. 4. Click Properties to specify whether you want to return only the data from tables in the HTML data source or from all of the information on the Web page. 5. To return the external data range to the selected worksheet, click Existing worksheet. On your worksheet, click the cell where you want to place the upper-left corner of the external data range, and then click OK. To return the external data range to a new worksheet, click New worksheet then click OK. Excel adds a new worksheet to your workbook and automatically starts the external data range in the upper-left corner of the new worksheet. 6. If prompted, enter parameters for the query. If you do not know what the valid parameters are, you may have to ask the person who created the query. You can tell the query is running because Excel displays a spinning icon on the status bar. You can check the status of a query that's running in the background and taking a

Page 12

Brandeis University

ITS Training: Advanced Excel

long time to return data. While the query is running, click Refresh Status on the External Data toolbar. To cancel the query, click Stop Refresh. Instructions for Installing Microsoft Query and ODBC Drivers Microsoft Query, which includes the Query Wizard, is an optional feature for Microsoft Excel. If you chose the Typical installation option during Setup, Microsoft Query is not installed on your computer. 1. 2. 3. 4.

Close all open programs. On the Start menu, point to Settings and then click Control Panel. Double-click Add/Remove Programs. Click the Install/Uninstall tab, click Microsoft Office or Microsoft Excel, and then click Add/Remove. The setup program starts. 5. When the Setup dialog box is displayed, click Add/Remove. 6. Click Data Access (or Data Access and ActiveX Controls), then click Change Option. 7. Click Database Drivers and then click Change Option. 8. Select the ODBC drivers you want to install, then click OK. 9. Select the Microsoft Query check box, then click OK. 10. Click Continue and follow the directions to finish the Setup.

17.6 Customizing Toolbars You can customize the toolbars that come with Excel or even create your own new ones. Open the Tools menu, select Customize and click the Toolbar tab. Make sure there is a check mark next to the toolbar you want to modify so that it is visible. Click the Commands tab. Click on the icon you want to add and drag it to the toolbar you want it to appear on and drop it in its desired location. You can also access this dialog box by right clicking on any blank area in any toolbar and then choosing Customize. To create a custom toolbar, open the Tools menu, select Customize and chooses the Toolbars tab. Click the New button and give the new toolbar a name. Choose whether the toolbar will be available just to this document or to your other templates. Figure 17.9 The Customize dialog box.

Page 13

Brandeis University

ITS Training: Advanced Excel

Click the Commands tab and drag the icons you want onto the new toolbar. You can further modify the toolbar choices by clicking the Modify Selection button. Excel allows you to edit or change the images that appear on the buttons. To delete an icon you no longer want on your toolbar, hold down the ALT key and drag the button off the toolbar. Note that when you delete a built-in toolbar button, the button is still available in the Customize dialog box. However, when you delete a custom toolbar button, it is permanently deleted. To remove and save a custom toolbar button for later use, create a toolbar for storing unused buttons, move the button to this storage toolbar, and then hide it.

17.7 Using Macros If you perform a task repeatedly in Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. You record a macro just as you record music with a tape recorder. You then run the macro to "play back" the commands. Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake while recording the macro, the mistakes and corrections you make will also be recorded. Each time you record a macro, the macro is stored in a new module attached to a workbook. With the Visual Basic Editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, rename the modules that store the macros, or rename the macros. Recording a Macro Open the Tools menu, select Macro, and then click Record New Macro. Type a name for the macro in the Macro name box. The first character of the macro name must be a letter. Other characters can be letters, numbers or underscore characters. Spaces are not allowed in a macro name; use underscore characters as word separators. If you want to be able to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL + a lowercase letter or CTRL + SHIFT + an uppercase letter. The Figure 17.10 You can create a shortcut key for your macro in the Record Macro dialog box.

Page 14

Brandeis University

ITS Training: Advanced Excel

shortcut key letter you use cannot be a number or special character. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open. In the Store macro in box, click the location where you want to store the macro. If you want a macro to be available whenever you use Microsoft Excel, store the macro in the Personal Macro Workbook in the XLStart folder. Type a description of the macro in the Description box. Click OK. Carry out the actions you want to record. Note that, if you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell references. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again. When finished, click Stop Recording on the Stop Recording toolbar. Tip: If you want a macro to select a specific cell, perform an action, and then select another cell relative to the active cell, you can mix the use of relative and absolute references when you record the macro. To record a macro by using relative references, make sure that Relative Reference is pressed in. To record with absolute references, make sure Relative Reference is not pressed in. Editing a Macro Before you edit a macro, you should be familiar with the Visual Basic Editor. The Visual Basic Editor can be used to write and edit macros attached to Excel workbooks. On the Tools menu, select Macro and then click Macros. In the Macro name box, enter the name of the macro. Click Edit. Running a Macro Open the workbook that contains the macro. Open the Tools menu, select Macro and then click Macros. In the Macro name box, enter the name of the macro you want to run. Click Run. To interrupt a macro before it completes its actions, press ESC. Running A Macro From A Toolbar Button You can run a macro from a button on a toolbar. 1) On the Tools menu, click Customize. 2) If the toolbar that contains the button is not visible, click the Toolbars tab then click

Page 15

Brandeis University

ITS Training: Advanced Excel

the check box next to the toolbar name. 3) If the button you want to run the macro from is not on the toolbar, click the Commands tab, then click Macros in the Categories list. Drag the Custom button from the Commands list onto the toolbar. 4) Right-click the toolbar button, and then click Assign Macro on the shortcut menu. 5) In the Macro name box, enter the name of the macro. Now when you click the new button on the toolbar, the macro will run. Copying Part Of A Macro To Create Another Macro Open the workbook that contains the macro you want to copy. On the Tools menu, point to Macro, and then click Macros. In the Macro name box, enter the name of the macro that you want to copy. Click Edit. Now you can use copy and paste as you would elsewhere in Excel. Select the lines of the macro you want to copy. To copy the entire macro, make sure to include the Sub and End Sub lines in the selection. Click Copy. Switch to the module where you want to place the code. Click Paste.

Page 16