microsoft excel 2010 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft Excel 2010 PowerPoint Presentation
Download Presentation
Microsoft Excel 2010

Loading in 2 Seconds...

play fullscreen
1 / 47

Microsoft Excel 2010 - PowerPoint PPT Presentation


  • 107 Views
  • Uploaded on

Microsoft Excel 2010. Chapter 6 Working with Multiple Worksheets and Workbooks. Objectives. Use the ROUND function Use custom format codes Define , apply, and remove a style Add a worksheet to a workbook Create formulas that use 3-D cell references

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Microsoft Excel 2010' - azure


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft excel 2010

MicrosoftExcel 2010

Chapter 6

Working with Multiple Worksheets and Workbooks

objectives
Objectives
  • Use the ROUND function
  • Use custom format codes
  • Define, apply, and remove a style
  • Add a worksheet to a workbook
  • Create formulas that use 3-D cell references
  • Add data to multiple worksheets at the same time

Working with Multiple Worksheets and Workbooks

objectives1
Objectives
  • Add a header or footer and change margins
  • Insert and move a page break
  • Save a workbook as a PDF or XPS file
  • Create a workspace file
  • Consolidate data by linking workbooks

Working with Multiple Worksheets and Workbooks

project consolidated expenditures worksheet
Project – Consolidated Expenditures Worksheet

Working with Multiple Worksheets and Workbooks

project consolidated expenditures worksheet1
Project – Consolidated Expenditures Worksheet

Working with Multiple Worksheets and Workbooks

general project guidelines
General Project Guidelines
  • Design the consolidated worksheet and plan the formatting
  • Identify additional worksheets needed in the workbook
  • Plan the layout and location of the required custom format codes
  • Examine the options, including headers, margins, and page breaks, that you have for printing worksheets
  • Identify workbooks to be consolidated into a workspace and then linked to create a consolidated workbook of the initial workbooks

Working with Multiple Worksheets and Workbooks

entering sample data using the fill handle
Entering Sample Data Using the Fill Handle
  • Enter the values from which to create a series
  • Select the cells with the values
  • Drag the fill handle through the cells to fill

Working with Multiple Worksheets and Workbooks

the round function and entering formulas
The ROUND Function and Entering Formulas
  • The ROUND function can decrease the number of decimal places that can appear in a cell

=ROUND (number, number of digits)

The number argument can be a number, cell reference, or formula

Working with Multiple Worksheets and Workbooks

assigning a currency style using the format cells dialog box
Assigning a Currency Style Using the Format Cells Dialog Box
  • Select the cell(s) to format with Currency style
  • Right click a selected cell
  • Click Format Cells on the shortcut menu to display the Format Cells dialog box
  • If necessary, click the Number tab to display the Number tab, click Currency in the Category list to select the type of format to apply, and then click the desired currency format
  • Click the OK button

Working with Multiple Worksheets and Workbooks

assigning a currency style using the format cells dialog box1
Assigning a Currency Style Using the Format Cells Dialog Box

Working with Multiple Worksheets and Workbooks

format codes
Format Codes

Working with Multiple Worksheets and Workbooks

creating and assigning a custom format code and a comma style format
Creating and Assigning a Custom Format Code and a Comma Style Format
  • Select the range of cells to format, and then click Format Cells on the shortcut menu to display the Format Cells dialog box
  • If necessary, click the Number tab to display the Number tab and then click Custom in the Category list to begin creating a custom format code
  • Delete the word General in the Type box, and then enter the desired format code
  • Click the OK button
  • Click the Comma Style button to display the numbers in the selected ranges using the Comma style format

Working with Multiple Worksheets and Workbooks

creating and assigning a custom format code and a comma style format1
Creating and Assigning a Custom Format Code and a Comma Style Format

Working with Multiple Worksheets and Workbooks

cell styles
Cell Styles

Working with Multiple Worksheets and Workbooks

creating a new style
Creating a New Style
  • Click the Cell Styles button (Home tab | Styles group) to display the Cell Styles gallery
  • Click the New Cell Style button in the Cell Styles gallery to display the Style dialog box
  • Type the desired name for the new style
  • Click the Format button to display the Format Cells dialog box
  • Select the desired category and type
  • Click the Alignment tab, and then select the desired alignment

Working with Multiple Worksheets and Workbooks

creating a new style1
Creating a New Style
  • Click the OK button
  • When the Style dialog box becomes active, click Font, Border, Fill, and Protection to clear the check boxes, indicating that the new style does not use these characteristics
  • Click the OK button to add the new style

Working with Multiple Worksheets and Workbooks

creating a new style2
Creating a New Style

Working with Multiple Worksheets and Workbooks

applying a new style
Applying a New Style
  • Select the desired cell(s) and then click the Cell Styles button (Home tab | Styles group) to display the Cell Styles gallery
  • Click the name of the new style to assign the style to the selected cell(s)

Working with Multiple Worksheets and Workbooks

adding a worksheet to a workbook
Adding a Worksheet to a Workbook
  • Click the Sheet2 tab at the bottom of the window and then click the Insert Cells button arrow (Home tab | Cells group) to display the Insert Cells menu
  • Click Insert Sheet on the Insert Cells menu to add a new worksheet to a workbook, in this case a sheet named Sheet 4 between Sheet 1 and Sheet 2

Working with Multiple Worksheets and Workbooks

adding a worksheet to a workbook1
Adding a Worksheet to a Workbook

Working with Multiple Worksheets and Workbooks

copying the contents of a worksheet to other worksheets in a workbook
Copying the Contents of a Worksheet to Other Worksheets in a Workbook
  • Click the tab for the sheet containing the data to copy
  • Click the Select All button to select the entire worksheet and then click the Copy button (Home tab | Clipboard group) to copy the contents of the worksheet
  • Click the tab(s) for the sheet(s) to paste the data
  • Click the Paste button (Home tab | Clipboard group) to copy the data on the Office Clipboard to the selected sheet(s)

Working with Multiple Worksheets and Workbooks

copying the contents of a worksheet to other worksheets in a workbook1
Copying the Contents of a Worksheet to Other Worksheets in a Workbook

Working with Multiple Worksheets and Workbooks

drilling an entry through worksheets
Drilling an Entry through Worksheets
  • With Sheet1 active, hold down the SHIFT key and then click the remaining tab(s) for the worksheet(s) to contain the entry
  • Type the entry in the desired cell

Working with Multiple Worksheets and Workbooks

drilling an entry through worksheets1
Drilling an Entry through Worksheets

Working with Multiple Worksheets and Workbooks

referencing cells in other sheets in a workbook
Referencing Cells in Other Sheets in a Workbook
  • To reference cells in other sheets in a workbook, you use the sheet name, which serves as the sheet reference, and the cell reference

=‘Public Work’s!B5’

=‘Public Works’!B5 + ‘Police and Fire’!B5 + ‘Parks and Recreation’!B5

  • You can enter a sheet reference in a cell by typing the sheet reference or by clicking the appropriate sheet tab while in Point mode

Working with Multiple Worksheets and Workbooks

referencing cells in other sheets in a workbook1
Referencing Cells in Other Sheets in a Workbook

Working with Multiple Worksheets and Workbooks

more about pasting
More About Pasting

Working with Multiple Worksheets and Workbooks

changing margins and centering the printout horizontally
Changing Margins and Centering the Printout Horizontally
  • Select the worksheet tab(s)
  • Click the Page Setup Dialog Box Launcher (Page Layout tab | Page Setup group) to display the Page Setup dialog box
  • When Excel displays the Page Setup dialog box, if necessary, click the Margins tab
  • Set the top, bottom, left, and right margins as desired
  • Click the Horizontally check box in the ‘Center on page’ area to center the worksheet on the printed page horizontally
  • Click the OK button

Working with Multiple Worksheets and Workbooks

changing margins and centering the printout horizontally1
Changing Margins and Centering the Printout Horizontally

Working with Multiple Worksheets and Workbooks

adding a header and footer
Adding a Header and Footer
  • Click the Page Layout button on the status bar to display the worksheet in Page Layout view
  • If necessary, scroll the worksheet up until the Header area is displayed

Working with Multiple Worksheets and Workbooks

adding a header and footer1
Adding a Header and Footer
  • Scroll the workbook down to view the Footer area

Working with Multiple Worksheets and Workbooks

selecting and deselecting sheets
Selecting and Deselecting Sheets

Working with Multiple Worksheets and Workbooks

inserting and removing a page break
Inserting and Removing a Page Break
  • Select the cell where the page break should be inserted
  • Click the Breaks button (Page Layout tab | Page Setup group) to display the Breaks menu and then click Insert Page Break on the Breaks menu to insert a page break

Working with Multiple Worksheets and Workbooks

inserting and removing a page break1
Inserting and Removing a Page Break
  • To remove a page break, click the cell containing the page break, and then click the Breaks button (Page Layout tab | Page Setup group) to display the Breaks menu
  • Click Remove Page Break on the Breaks menu to remove the page breaks

Working with Multiple Worksheets and Workbooks

hiding page breaks
Hiding Page Breaks
  • Open the Backstage view
  • Click the Options button in the Backstage view to display the Excel Options dialog box
  • Click the Advanced button to display Advanced Excel options
  • Scroll the window until the ‘Display options for this worksheet’ area appears
  • Click the ‘Show page breaks’ check box to clear the check box
  • Click the OK button to close the Excel Options dialog box and hide the page breaks

Working with Multiple Worksheets and Workbooks

hiding page breaks1
Hiding Page Breaks

Working with Multiple Worksheets and Workbooks

searching for and opening workbooks
Searching for and Opening Workbooks
  • Start Excel
  • Open the Backstage view
  • Click the Open button to display the Open dialog box and then select the location to search
  • Type the search text in the Search box
  • In the File list, click the workbook(s) to open
  • Click the Open button to open the selected workbook(s)

Working with Multiple Worksheets and Workbooks

searching for and opening workbooks1
Searching for and Opening Workbooks

Working with Multiple Worksheets and Workbooks

searching for and opening workbooks2
Searching for and Opening Workbooks
  • Click the Arrange All button (View tab | Window group) to display the Arrange Windows dialog box
  • Click Vertical (Arrange Windows dialog box) to arrange the windows vertically
  • Click the OK button to display the opened workbooks arranged vertically

Working with Multiple Worksheets and Workbooks

searching for and opening workbooks3
Searching for and Opening Workbooks

Working with Multiple Worksheets and Workbooks

creating a workspace file
Creating a Workspace File
  • With the desired workbooks opened, click the Save Workspace button (View tab | Window group) to display the Save Workspace dialog box
  • Navigate to the location to save the workspace
  • Type the desired workspace name in the File name text box
  • Click the Save button to save the file names of the workbooks open, of the workbooks displaying, and other display settings

Working with Multiple Worksheets and Workbooks

creating a workspace file1
Creating a Workspace File

Working with Multiple Worksheets and Workbooks

consolidating data by linking workbooks
Consolidating Data by Linking Workbooks
  • Open the desired workspace
  • While entering formulas, you can reference cells in other workbooks by clicking those cells
  • Delete the dollar signs ($) in the formulas so that the references are not absolute

Working with Multiple Worksheets and Workbooks

consolidating data by linking workbooks1
Consolidating Data by Linking Workbooks

Working with Multiple Worksheets and Workbooks

chapter summary
Chapter Summary
  • Use the ROUND function
  • Use custom format codes
  • Define, apply, and remove a style
  • Add a worksheet to a workbook
  • Create formulas that use 3-D cell references
  • Add data to multiple worksheets at the same time

Working with Multiple Worksheets and Workbooks

chapter summary1
Chapter Summary
  • Add a header or footer and change margins
  • Insert and move a page break
  • Save a workbook as a PDF or XPS file
  • Create a workspace file
  • Consolidate data by linking workbooks

Working with Multiple Worksheets and Workbooks

microsoft excel 20101

MicrosoftExcel 2010

Chapter 6 Complete