1 / 40

Chapter 10 Excel: Data Handling or What do we do with all that data?

Chapter 10 Excel: Data Handling or What do we do with all that data?. BUS169. Topics . Data Handling Techniques Entering Data Importing Data Excel’s Database Capabilities Sort Filter Subtotal Pivot Tables and Charts. Manual Data Entry. Press TAB to enter data horizontally:

hilde
Download Presentation

Chapter 10 Excel: Data Handling or What do we do with all that data?

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. Chapter 10Excel: Data HandlingorWhat do we do with all that data? BUS169

  2. Topics • Data Handling Techniques • Entering Data • Importing Data • Excel’s Database Capabilities • Sort • Filter • Subtotal • Pivot Tables and Charts

  3. Manual Data Entry Press TAB to enter data horizontally: Press ENTER to enter data vertically:

  4. Custom Lists • Dates • Numeric Intervals • Lists

  5. Data Forms To enter data in a form view, click Data>Form

  6. Import Data Import a tab or comma-delimited file that has been saved as “Text Only with Line Breaks”

  7. Excel’s Database Capabilities • Sorting • Filtering • Working with Subtotals Tap the database functions of Excel Click Data . . . Sort, Data . . . Filter, or Data . . .Subgroup

  8. Sorting an Excel Data List Click Data>Sort and choose column to sort by

  9. Filtering an Excel Data List Click Data>Autofilter Choose Column and specifications to filter on

  10. Subtotals and Totals Click Data> Subtotals Choose parameters for subtotals

  11. Pivot Tables and Charts Pivot Table: an interactive worksheet that allows you to summarize large amounts of information. (The graphical representation of the Pivot Table is a Pivot Chart.)

  12. Excel Data List Files Data suitable for Pivot Table analysis: • columns are considered fields, • column headings are field names, and • rows are records. Important: data should have no blank columns or rows

  13. Name the Data Range Highlight the data list. Then enter a name for the range in the Range Name box.

  14. Create Pivot Table Place cursor in data range Choose Pivot Table from Data menu

  15. Pivot Table: Step 1 of 3 Select the data source: Excel list Select the desired result

  16. Pivot Table: Step 2 of 3 If a data range has been defined, the range will automatically be displayed

  17. Pivot Table: Step 3 of 3 Choose pivot table destination Click the Layout Button

  18. Drag the field buttons . . . . . . to the Pivot Table diagram Pivot Table Layout

  19. Sample Pivot Table The data list fields are “pivoted” around the “core data”, gross sales, providing different “views” of the data.

  20. Change Table Layout View > Toolbars > Pivot Table Pivot Chart Wizard Or, drag field names to create new layout

  21. Create a Pivot Chart View > Toolbars > Pivot Table Pivot Chart Button

  22. Sample Pivot Chart

  23. Pivot Table Capabilities “Drill Down” for Detail Automatic Updates Multiple Data Views

  24. “Drill Down” Double click in cell of Pivot Table Results in Detail from Raw Data:

  25. Updates to Core Data When core data is updated . . . . . . use Pivot Table Toolbar to Refresh table

  26. Multiple Consolidation of Ranges Compare Similar Data in Multiple Worksheets

  27. Multiple Consolidation:Step 1 of 3 Select the data source: Multiple Ranges Select the desired result

  28. Multiple Consolidation:Step 2a of 3 Name the page fields, or let Excel assign a default name.

  29. Multiple Consolidation:Step 2b of 3 Highlight ranges, one at a time and add Name the page fields here

  30. Multiple Consolidation:Step 3 of 3

  31. Drag the field buttons . . . . . . to the Pivot Table diagram Multiple Consolidation: Layout

  32. Multiple Consolidation:Pivot Table This Pivot Table consolidates data from the worksheets of all designated products

  33. Consolidate Grouped Data

  34. Grouping Data Fields • Highlight Jan, Feb Mar labels • Data > Group and Outline > Group • Enter Quarter Label

  35. Data Grouped by Quarter

  36. Pivot Table or Chart in Word In Excel, highlight and copy the Pivot Table In Word, click Paste Special. Highlight Microsoft Excel Worksheet Object and Paste Link.

  37. Formatting Options Format Cells Autoformat Conditional Formats

  38. Format Cells Highlight cells and click the format tool bar buttons to format cells. For more options, click Format > Cells

  39. Autoformat Highlight the desired cells. Then click Format > Autoformat to apply a pre-designed style.

  40. Conditional Formats Set cell value criteria with desired formats. Above, all cells with values between 40 and 100 will display in a yellow cell with bold font.

More Related