PowerPoint Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1e Chapter 4

1. PowerPoint Presentation to Accompany GO! with Microsoft Excel ® 2007 Comprehensive 1e Chapter 4 Creating Charts and Tables; Sorting and Filtering Data

2. Objectives • Use Text Orientation • Create a Column Chart • Create a Chart Sheet and Edit the Chart • Create and Modify a Pie Chart • Apply a Theme to a Chart • Print Charts

3. Objectives • Sort Data • Convert Text into Columns • Apply Conditional Formatting Using Data Bars and Color Scales • Insert a Table and Filter Data

4. Use Text Orientation • Information in cells can be displayed at an angle or vertically. • These formats save space. • They also add interest and a professional look to the worksheet. Column titles displayed at an angle on two lines

5. Use Text Orientation • To change text orientation: • With the cells selected, from the Home tab, in the Alignment group, point to the Orientation button. • Click the Orientation button arrow and make a selection.

6. Use Text Orientation Orientation button Orientation menu

7. Create a Column Chart • A chart type determines the way the data is presented—as a column, line, bar, or pie chart. • Chart styles determine the colors used in the chart.

8. Create a Column Chart • Determining which chart type to use: • Pie chart: To show the proportion of each item to the sum of the whole. • All data points must be positive values and only one data range—row or column—may be charted. • Bar chart: To illustrate comparisons among individual items.

9. Create a Column Chart • Determining which chart type to use: • Column chart: To show data changes over a period of time or to illustrate comparisons among items. • Line chart: To display continuous data over time. Use for showing trends in data at equal intervals.

10. Create a Column Chart • A chart is a visual representation of numeric data. • To create a chart: • - Select the source data range. • - Click on the Insert tab. • - In the Charts group, select the preferred chart style.

11. Create a Column Chart 3D Clustered Column chart Chart legend

12. Create a Column Chart • By default, charts are embedded in a worksheet. • The default displays both the chart and the worksheet data. • A chart may be displayed on a separate sheet in a workbook, called a chart sheet.

13. Create a Chart Sheet and Edit the Chart • Determining which chart type to use: • Column chart: To show data changes over a period of time or to illustrate comparisons among items. • Line chart: To display continuous data over time. Use to show trends in data at equal intervals. • Bar chart: To illustrate comparisons among individual items.

14. Create a Chart Sheet and Edit the Chart • Pie chart: To show the proportion of each data item to the sum of the whole. • All data points must be positive values and only one data range—row or column—may be charted. Sample pie chart

15. Create and Modify a Pie Chart • To create a Pie Chart: Select the data range. • Click on the Insert tab, and in the Charts group, click Pie to display the Pie chart gallery. • Select the desired style of chart.

16. Apply a Theme to a Chart • When a theme is applied, it is applied to the entire workbook, including the chart. • To change a theme: • Select the Page Layout tab. • In the Themes group, click the Themes button. • The Themes gallery is displayed.

17. Print Charts • A chart sheet: • Can include a header and a footer. • Can be centered on the page. • Can be printed in portrait or landscape orientation. • A chart embedded on the same sheet as a worksheet can be printed with the worksheet or as a separate document.

18. Print Charts • To print a chart as a separate sheet apart from the worksheet data: • Click the chart to make it active. • Click the Office button. • Point to Print and then click Print Preview. • Only the chart displays in Print Preview.

19. Sort Data • A table—also called a list or a database—is a collection of data. Sort & Filter button Column titles identify field categories. Sort & Filter list A row or record contains all of the data about one employee.

20. Sort Data • Data can be sorted by: • Text • A to Z • Z to A • Numbers • Largest to smallest • Smallest to largest

21. Sort Data • When the primary sort results match, a secondary sort field is used. Sort dialog box Sort order options Primary level sort Then by level—secondary level

22. Convert Text into Columns • Data, such as first and last name, that is entered into one cell can be split into two or more cells. • Use the Text to Columns feature. Text to Columns button Data tab

23. Convert Text into Columns • Two choices for the file type—delimited and fixed width. • Delimited width sets the limits of the fields by a symbol (e.g., comma, tab, or semicolon), which is called a delimiter. • Fixed width sets the limits of the fields by the specified width for the column break.

24. Convert Text into Columns Delimited—sets the limits of the fields by characters. Fixed width—sets the limits of the fields by a specific number of spaces.

25. Apply Conditional Formatting Using Data Bars and Color Scales • Cells that meet a specific condition, such as greater than, less than, or equal to, can be highlighted using conditional formatting.

26. Apply Conditional Formatting Using Data Bars and Color Scales • With conditional formatting, data that falls within a specific range will be highlighted. • To create, click the Home tab, and in the Styles group, click the Conditional Formatting button. • From the displayed list, point to Highlight Cells Rules, and from the submenu, click Between.

27. Apply Conditional Formatting Using Data Bars and Color Scales Type largest number of range Results Between dialog box Format to apply Type smallest number of range

28. Apply Conditional Formatting Using Data Bars and Color Scales • A data bar is a colored bar that displays in a worksheet cell. • The data bar represents the value in the cell. • A longer bar represents a higher value. • A shorter bar represents a lower value.

29. Apply Conditional Formatting Using Data Bars and Color Scales Data Bars Gallery Conditional Formatting button Data Bar ScreenTip Live Preview displays data bars in the worksheet.

30. Apply Conditional Formatting Using Data Bars and Color Scales • Color scales use gradients of the color to visually compare values. • A two-color template assigns one color to the lowest value and another to the highest value. • Gradients of color are used for the values in between.

31. Apply Conditional Formatting Using Data Bars and Color Scales Edit Formatting Rule dialog box Color assigned to maximum Color assigned to minimum

32. Insert a Table and Filter Data • Filtering data enables you to display a specific set of data. • When a filter is applied to a list, only the records that match the criteria will display in the worksheet. • Data is not lost.

33. Insert a Table and Filter Data • To apply a filter: • Click in a cell within the worksheet data. • Click the Data tab. • In the Sort & Filter group, click the Filter button.

34. Insert a Table and Filter Data Filter button Sort & Filter group Sorting and filtering arrow

35. Insert a Table and Filter Data • Click the sorting and filtering arrow to display the menu. • A list of sorting and filtering options displays.

36. Insert a Table and Filter Data Sorting instructions Text Filters list displays

37. Insert a Table and Filter Data • A filter button displays when the column is filtered. Filter button—indicates column is filtered.

38. Insert a Table and Filter Data • A filter can be removed from a table individually by field. • To remove all filters: • On the Data tab, in the Sort & Filter group, click Clear.

39. Covered Objectives • Use Text Orientation • Create a Column Chart • Create a Chart Sheet and Edit the Chart • Create and Modify a Pie Chart • Apply a Theme to a Chart • Print Charts

40. Covered Objectives • Sort Data • Convert Text into Columns • Apply Conditional Formatting Using Data Bars and Color Scales • Insert a Table and Filter Data