Presentation Transcript

1. Day 10:Excel Chapter 9

   Cody Cutright CS 101 September 18, 2013
2. Grouped Worksheets Creating worksheets with identical structure and formatting provides consistency and continuity. For example, a yearly budget broke down by each month.
3. Grouping Worksheets Grouping is the process of selecting worksheets to perform the same action at the same time. Table 9.1
4. Selecting Worksheets
5. Grouped Worksheets
6. Things To Do: Data Entry: Enter labels, values, dates, and formulas Structural Changes: Insert rows, widen columns, cut/copy/paste data, delete cells, rows, and columns Formatting: Font formats, alignment, number formats Page Layouts and Printing: Headers, page orientation, print areas, scaling
7. Musing: What if you created and formatted only one worksheet, and now you want to copy the data and formatting to others?
8. Fill Across Worksheets Click the worksheet tab that is to be copied, select the appropriate range of cells. Hold Ctrl, and click on the destination worksheet tabs Home Tab -> Editing Group -> Fill, and Select Across Worksheets Click All to copy data and formats, Contents to copy the data only, or Formats to copy just the formatting. Click OK.
9. Be Ye Warned! Make sure you ungroup worksheets when you only want to perform a task on only one of them! If you are not paying attention, you could ruin multiple worksheets or waste hours of time!
10. 3-D Formulas (Cross Sheet) Excel workbooks often contain data from different time periods, i.e. different months of a yearly budget. It becomes necessary to not only analyze data on one sheet, but to compare or analyze data across multiple sheets. 3-D Formulas consolidate data from several worksheets.
11. Worksheet References 3-D Formulas differ from normal formulas because they reference data on other sheets, using work sheet references. A worksheet reference is a pointer to a cell (or range of cells) in another worksheet. Form:‘Worksheet Name’!RangeOfCells
12. 3-D Convenience ! It could take a LONG time to click cell E7 on each worksheet, to get a sum of their values… so don’t do it!!! =SUM('Qtr1:Qtr4'!E3)
13. 3-D Formula Creation Click the cell where the function will be entered. Type =, the function name (i.e. SUM), then an opening parenthesis ( Hold Shift to select a range of worksheets, or Ctrl to select nonadjacent ones Click the cell or range that contains the values you want to use in the function argument. Example: =SUM(‘First Worksheet:Last Worksheet’!RangeOfCells)

14. Day 10:Excel Chapter 10

    Cody Cutright CS 101 September 18, 2013
15. Cell Styles A Cell style is a set of formatting options applied to worksheet cells
16. Predefined Styles • Good, Bad and Neutral: Use to emphasize bad, good, or neutral results, or click Normal to reset a cell back to its original default setting. • Data and Model: Use to indicate special cell contents, such a calculated result, input cell, output cell, or warning. • Titles and Headings: Use to format titles and headings, such as column and row labels, for emphasis. • Themed Cell Styles: Use Accent styles for visual emphasis. These cell styles are dependent on the currently selected theme. • Number Format: Provide the same formatting as commands in the Number group on the Home tab.
17. Custom Styles Custom Styles can be made and saved in a workbook, and applied to multiple cells. Select the cell with the desired formatting. Home Tab -> Styles Group -> Cell Styles New Cell Style, at the bottom of the gallery Click the check boxes to select the style options you want Click OK
18. Custom Styles Dialog Box After you customize and save the style, it can be applied to any other cells in the workbook.