Spreadsheet Chapter 5
Steps to create a Static Consolidation
1 Select the cell range 2 Click the consolidate button 3 Click the function arrow and choose the function 4 Click the reference box 5 Click the first worksheet tab name with data to be consolidated 6 Select the cells to be summarized on the source sheet 7 Click add in the consolidate dialog box 8 Click the next worksheet tab name with data to be consolidated (same cell range is selected) 9 Click add (Reference is placed in all references list) 10 Repeat steps 8-9 for each worksheet to be included 11 Click OK
Create a hyperlink
1- Click cell where link should appear 2- Click link button (insert tab) 3- Select "place in this document" in the link to list on the left 4- Select a worksheet name to jump to that sheet 5- Click the "type the cell reference" box 6- type the cell address that should be selected when the hyperlink is clicked 7- click the "text to display" box and enter text for the hyperlink cell 8- Click the "screen tip" button 9- Type an optional screen tip 10- Click ok to close all open dialog boxes
How to group worksheets
1- Click the first sheet tab to be included in the group 2- Press Shift and click the last Tab name for the group to select the adjacent sheets 3-Press Ctrl and click each Tab name to select nonadjacent sheets 4- Right click any sheet tab and choose "Select All Sheets" to group all sheets in a workbook
How to format a smartart graphic
1- Click the smartart graphic 2- click the more button 3- point to a thumbnail style 4- Click a style to apply it 5- Click the change colors button 6- Point to a color thumbnail to preview 7- Click to apply it 8- Click an individual shape in the graphic 9- Click the arrow with the shape fill button 10- Chose a color from the gallery 11- Point to a corner sizing handle 12- Drag to resize 13- Point to the smartart frame 14-drag to desired position 15- click outside to deselect
How to insert a SmartArt Graphic
1- Click the worksheet tab for the SmartArt 2- Click the insert SmartArt Graphic button 3- Choose the category from the list on the left 4- Select the SmartArt Graphic and select OK 5- Click a shape and type the text 6- Click the frame or another SmartArt object
SmartArt graphic
An illustration, usually with text in an excel worksheet
Safeguarding a Workbook
Excel has two commands that provide a simple level of security to protect your work from unwanted changes
Dynamic Data Consolidation
Places formulas on the consolidated sheet in an outline. When data on a source worksheet is edited, the formula recalculates
Password protect a Workbook
Same as mark as final but different
Static Data Consolidation
Summarizes the data and displays a result on the consolidated sheet. The result does not change when a value on any source worksheet is edited.
Consolidating Data by Category
When data in multiple worksheets is arranged differently but has the same row and column labels you can build a consolidation by category. Category refers to row and column labels. Can be static or dynamic.
Create a Dynamic Consolidation
You build a Dynamic consolidation sheet the same way you build a static consolidation sheet. In the Consolidate dialog box however, chose the option to create links to the source data. The resulting outline in the consolidation sheet uses 3D reference formulas. This step comes as the second to last step in the creation.
Grouped Worksheets
You can worksheets that have identical layouts for common editing or formatting. Not all commands work for grouped worksheets
Cell hyperlink
a text shortcut or jump in a cell Shortcut is CTRL+K
Source Workbook
includes data that is referenced in a dependent workbook
Dependent Workbook
includes or refers to data from another workbook
Mark as Final
indicate to readers that a document is read only and cannot be edited
Consolidated Worksheet
summarizes data from multiple sheets with a mathematical or statistical function
Link Workbooks using Consolidate Command
1- Open the dependent Workbook 2- Delete the contents of the cells to be consolidated 3- Open the source workbook 4- Return to the dependent Workbook 5- Select the range of cells to be consolidated 6- Click the consolidate program 7- Click the function arrow and choose the function 8-Select and delete references in the all references list 9- Click the reference box 10- Click the workbook icon on the windows task bar for the first source workbook 11- Select the sheet tab with data to be consolidated 12- Select the cell range and click add 13- Click the next tab name and click add 14- Select the create links to source data box 15- Click OK 16- Switch to the dependent workbook
Link Workbooks with an addition formula
1- Open the dependent Workbook 2- Open all source workbooks 3- Return to the dependent workbook 4- Click the cell for the formula 5- Proceed to build your formula using the cells from other WORKBOOKS like you would from other WORKSHEETS 6- Press Enter
Update links in a workbook
1- Open the dependent workbook 2-Click enable content 3- Click update in the message box 4- Click continue if the links cannot be updated 5- Click the edit Links button 6- Click Change source 7- Navigate to find the file and click to select its name 8- Click OK to update the location 9- Click update values 10- Click close
How to Mark as Final
1- Save workbook 2- Open File tab-Info option- Click Protect workbook 3-Select Mark as final 4- Ok out
How to format a SmartArt Graphic
1- Select the SmartArt Graphic 2- Click the more Button to open the gallery 3- Point to a style thumbnail to see a live preview 4- Click a Style icon to apply it 5 Click the change colors button 6- Point to a color thumbnail 7- Click a color scheme to apply it 8-Click an individual shape in the graphic 9- Click the arrow with the shape fill button 10- Chose a color from the gallery 11- Point to a corner sizing handle 12- Drag the resize arrow to make the graphic larger or smaller 13- Point to the SmartArt frame 14- drag to desired position 15- Click a cell to deselect the Graphic
Creating a Dynamic Data Consolidation by Category
1-Delete the contents of the cells to be summarized including the label cells 2-Select the range of cells to be summarized including the label cells 3- Click the consolidate button 4- Click the function arrow and choose the function 5- Click the first sheet tab name with data to be consolidated. 6- Select the data and label cells to be summarized 7- Click Add in the consolidate dialog box 8- Click the next work sheet tab name 9- Click Add 10- Repeat steps 8-9 for each worksheet to be included 11-select create links to Source Data box 12- Select the box for Top Row in the "Use Labels in" group 13- Click OK
Linking Workbooks
Linking Workbooks is the process of referring to data in another workbook. Linking may display data or reference data for a formula
