1 / 52

Microsoft Excel 2010

Microsoft Excel 2010. Chapter 7 Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots. Objectives. Create and use a template Import data from a text file , an Access database, a Web page, and a Word document Transpose data while pasting it

rollin
Download Presentation

Microsoft Excel 2010

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. MicrosoftExcel 2010 Chapter 7 Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  2. Objectives • Create and use a template • Import data from a text file, an Access database, a Web page, and a Word document • Transpose data while pasting it • Convert text to columns • Use Find and Replace commands Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  3. Objectives • Draw a Clustered Cone chart • Use WordArt to create a title and to create and modify shapes • Insert and modify an image • Insert and modify a SmartArt graphic • Insert a screen shot Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  4. Project – YouBuild Computers Sales Analysis Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  5. General Project Guidelines • Design the template and plan the formatting • Analyze the existing workbook and the formats of the data to be imported • Determine whether data you import needs to be standardized • Plan the layout and location of the required chart • Choose the type of SmartArt graphics to add • Obtain the images to be used in the worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  6. Saving the Template • Display the Save As dialog box • Type the desired file name in the File name text box • Click the ‘Save as type’ box arrow and then click Excel Template in the list to specify that this workbook is saved as a template • Navigate to the desired save location • Click the Save button Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  7. Saving the Template Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  8. Using Templates Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  9. Opening a Template and Saving It as a Workbook • With Excel active, click File on the Ribbon to open the Backstage view • Click the Open command to display the Open dialog box • Navigate to the location of the file to be opened • Click the template file to select it • Click the Open button Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  10. Opening a Template and Saving It as a Workbook • Click File on the Ribbon to open the Backstage view, and then click the Save As command to display the Save As dialog box • Type the desired file name in the File name text box • Click the ‘Save as type’ box arrow and then click Excel Workbook to save the file as a workbook • Click the Save button to save the workbook Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  11. Opening a Template and Saving It as a Workbook Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  12. Importing Data from a Text File into a Worksheet • Click the first cell on the worksheet to contain the imported data • Click the From Text button (Data tab | Get External Data group) to display the Import Text File dialog box • If necessary, navigate to the location of the file containing text to insert • Click the name of the file to import • Click the Import button to start the Text Import Wizard and display the Text Import Wizard – Step 1 of 3 dialog box Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  13. Importing Data from a Text File into a Worksheet • Click the Next button to display the Text Import Wizard Step 2 of 3 dialog box • Select the delimiter • Click the Next button to display the Text Import Wizard – Step 3 of 3 dialog box • Click the Finish button to finish importing the selected text file and display the Import Data dialog box • Click the Properties button to display the External Data Range Properties dialog box • Set the desired settings, and then click the OK button • Click the OK button to import the data from the text file into the worksheet, beginning in the selected cell Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  14. Importing Data from a Text File into a Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  15. Importing Data from an Access Table into a Worksheet • Click the first cell on the worksheet to contain the imported data • Click the From Access button (Data tab | Get External Data group) to display the Select Data Source dialog box • Navigate to the location of the Access database fi le containing the table to insert • Click the name of the file to import • Click the Open button to display the Import Data dialog box • Click the OK button to import the data in the database to a table Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  16. Importing Data from an Access Table into a Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  17. Importing Data from a Web Page into a Worksheet • Click the first cell on the worksheet to contain the imported data • Click the From Web button (Data tab | Get External Data group) to display the New Web Query dialog box • Type the Web page address in the Address bar, and then click the Go button to display the Web page in the preview area • Click the ‘Click to select this table’ arrow to select the HTML table to import • Click the Properties button to display the External Data Range Properties dialog box • Set the desired settings, and then click the OK button • Click the OK button to import the data from the Web page into the worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  18. Importing Data from a Web Page into a Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  19. Copying and Transposing Data from a Word Document to a Worksheet • Open the Word document containing the data to copy • Drag through all the cells in the table to copy • Click the Copy button (Home tab | Clipboard group) to copy the contents of the table to the Office Clipboard • Quit Word and, if necessary, display the Excel workbook • Select a cell several rows below where the transposed content eventually will be pasted • Click the Paste button arrow (Home tab | Clipboard group) to display the Paste options • Click Paste Special on the Paste menu to display the Paste Special dialog box • Click Text in the As list Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  20. Copying and Transposing Data from a Word Document to a Worksheet • Click the OK button (Paste Special dialog box) to paste the contents of the Office Clipboard • Click the Copy button (Home tab | Clipboard group) to copy the range to the Office Clipboard • Select the first cell where the pasted, transposed content will appear • Click the Paste button arrow (Home tab | Clipboard group) and then point to Transpose on the Paste menu • Click Transpose on the Paste menu to transpose and paste the copied cells to the range beginning with the selected cell • Delete the cells from where the data was originally copied so that only the transposed data displays in the worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  21. Copying and Transposing Data from a Word Document to a Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  22. Converting Text to Columns • Select the cells containing delimited text to convert to columns • Click the Text to Columns button (Data tab | Data Tools group) to display the Convert Text to Columns Wizard – Step 1 of 3 dialog box • Click the Next button to accept Delimited as the file type and to display the Convert Text to Columns Wizard – Step 2 of 3 dialog box • Place a check mark next to the appropriated delimiter and to display the data fields correctly in the Data preview area • Click the Finish button to close the dialog box and separate the data Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  23. Converting Text to Columns Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  24. Replicating Formulas • Click the formula(s) to replicate • Drag the fill handle down through the desired row to replicate the formula Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  25. Replicating Formulas Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  26. Finding a String • Click the Find & Select button (Home tab | Editing group) to display the Find & Select menu • Click Find to display the Find and Replace dialog box • Click the Options button to expand the dialog box • Type the text to find in the Find what box to enter the search text • If desired, click Match case and then click ‘Match entire cell contents’ to insert check marks in those check boxes • Click the Find Next button • Continue clicking the Find Next button to find additional occurrences of the string • Click the Close button to stop searching and close the Find and Replace dialog box Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  27. Finding a String Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  28. Replacing a String with Another String • Click the Find & Select button (Home tab | Editing group) to display the Find & Select menu • Click Replace on the Find & Select menu to display the Find and Replace dialog box • Type the text to find in the Find what box, and the text to replace with in the Replace with box • If desired, click Match case and then click ‘Match entire cell contents’ to insert check marks in those check boxes • Click the Replace All button to replace the string • Click the OK button • Click the Close button Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  29. Replacing a String with Another String Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  30. Inserting a Clustered Cone Chart • Select the data to include in the chart • Click the Column button (Insert tab | Charts group) and then click Clustered Cone (column 1, row 4) in the Column gallery to insert a Clustered Cone chart • Click the Move Chard button to display the Move Chart dialog box • Click the New sheet option button, and then type the desired sheet name in the New sheet text box • Click the OK button to move the chart to the new sheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  31. Inserting a Clustered Cone Chart • Click the 3-D Rotation button (Chart Tools Layout tab | Background group) to display the Format Chart Area dialog box • Type the desired values in the X and Y text boxes to rotate the chart • Click the Close button to finish formatting the chart • Click the Legend button (Chart Tools Layout tab | Labels group) and then click None to remove the legend from the right side of the chart Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  32. Inserting a Clustered Cone Chart • Click the Axis Titles button (Chart Tools Layout tab | Labels group) to display the Axis Titles menu • Point to Primary Horizontal Axis Title on the Axis Titles menu and then click Title Below Axis in the Primary Horizontal Axis Title gallery to add a title to the horizontal axis • Select the Axis Title text and then type the new title Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  33. Inserting a Clustered Cone Chart Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  34. Adding a Chart Title Using the WordArt Tool • Click anywhere on the chart • Click the WordArt button (Insert tab | Text group) to display the WordArt gallery • Click the desired WordArt style • Type the desired WordArt text • Select the text in the WordArt object to display the Mini toolbar • Click the Font Size box arrow and then click the desired font size • Drag the top edge of the WordArt object to the desired location Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  35. Adding a Chart Title Using the WordArt Tool Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  36. Inserting a Picture Strips SmartArt Graphic • If desired, click the Gridlines check box (View tab | Show group) to turn off gridlines on the worksheet • Click the SmartArt button (Insert tab | Illustrations group) to display the Choose a SmartArt Graphic dialog box • Click Picture in the Type list in the left pane of the Choose a SmartArt Graphic dialog box • Click Picture Strips (column 2, row 4) in the layout list to see a preview of the chart in the preview area Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  37. Inserting a Picture Strips SmartArt Graphic • Click the OK button to insert a Picture Strips SmartArt graphic in the worksheet • If necessary, click the Text Pane button (SmartArt Tools Design tab | Create Graphic group) to display the Text pane • Drag the SmartArt graphic to the desired location • Use the sizing handles to resize the graph as desired Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  38. Inserting a Picture Strips SmartArt Graphic Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  39. Adding Shapes in the Picture Strips Smart Art Graphic • Right-click the top shape in the Picture Strips SmartArt graphic to display the shortcut menu • Point to Add Shape on the shortcut menu to display the Add Shape submenu • Click Add Shape Before to add a new shape to the Picture Strips SmartArt graphic Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  40. Adding Shapes in the Picture Strips Smart Art Graphic Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  41. Adding Text to Shapes in the Picture Strips SmartArt Graphic • Click the shape’s text box, and then type the desired text or • Type the desired text next to the corresponding bullet in the Text pane Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  42. Adding Text to Shapes in the Picture Strips SmartArt Graphic Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  43. Adding Pictures to Shapes in the Picture Strips SmartArt Graphic • Click the shape’s Insert Picture icon to display the Insert Picture dialog box • Navigate to the location of the picture to insert, and then click the picture to insert • Click the Insert button to insert the picture in the shape Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  44. Adding an Effect to the Picture Strips SmartArt Graphic • Select the SmartArt graphic • Click the SmartArt Styles More button (SmartArt Tools Design tab | SmartArt Styles group) to display the SmartArt Styles gallery • Click the desired style Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  45. Adding an Effect to the Picture Strips SmartArt Graphic Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  46. Inserting and Modifying an Image in the Worksheet • Click the Insert Picture from File button (Insert tab | Illustrations group) to display the Insert Picture dialog box • Navigate to the location of the picture to insert and then click the picture • Click the Insert button to insert the picture in the worksheet • Move and resize the image as desired • Click the Picture Styles More button (Format tab | Picture Styles group) to display the Picture Styles gallery • Click the desired picture style to apply the style to the image Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  47. Inserting and Modifying an Image in the Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  48. Inserting a Screen Shot on a Worksheet • Display the screen you wish to insert • Display the Excel window • Click the Screenshot button (Insert tab | Illustrations group) to display the Screenshot menu • Click the live preview corresponding to the screen you wish to insert in the worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  49. Inserting a Screen Shot on a Worksheet Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

  50. Chapter Summary • Create and use a template • Import data from a text file, an Access database, a Web page, and a Word document • Transpose data while pasting it • Convert text to columns • Use Find and Replace commands Creating Templates, Importing Data, and Working with SmartArt, Images, and Screen Shots

More Related