Excel2010Advanced

1、Excel 2010 Advanced Corporate Training Materials Module One: Getting Started With Excel 2010, you can manage more data than ever, with increased worksheet and workbook sizes. Excel also makes your job easier by providing an easy to use interface, and an array of powerful tools to help you turn your

2、data into useable information and better information leads to better decision making! The wisest mind has something yet to learn. George Santayana Workshop Objectives Insert SmartArt Add text to a diagram Resize and move a diagram Reset a diagram Understand the contextual tabs Add pictures from your

3、 computer Add Clip Art Add text boxes Draw shapes Trace precedent cells Trace dependents of a cell Display formulas within the sheet Work with comments Insert a chart Use the Chart Tools tab Understand the parts of a chart Change the chart style Resize and move a chart Insert a Pivot Table Use the P

4、ivot Table Tools tab Choose fields and group data in a Pivot Table Change Pivot Table data and refresh the view Apply a Style to a Pivot table Use Real-life examples in a Pivot Table Create a Pivot Chart from a Pivot Table Create a Pivot Chart from Data Display the Developer tab Record and run macro

5、s Change the security level Customize and Change the Quick Access Toolbar Use named ranges in formulas Understand formula errors Trace dependents and precedents in formulas Use the Trace Errors Commands Evaluate formulas Use Goal Seek Use the Scenario Manager Use a One Input Data Table Use a Two Inp

6、ut Data Table Transposing Data from rows to columns Use the Text to Columns feature Check for duplicates Create data validation rules Consolidate Data Group Data Add Subtotals Outline Data view Grouped and Outlined Data Module Two: SmartArt and Objects In this module, well look at how to add these o

7、bjects to your spreadsheets. Well also look at how to add text to a SmartArt diagram. Youll learn how to resize and move SmartArt diagrams, as well as how to reset them. Finally, youll learn about the contextual Tools tabs that appear in Excel 2010 when you are working with different types of object

8、s. Whenever you are asked if you can do a job, tell em, Certainly I can! Then get busy and find out how to do it. Theodore Roosevelt Inserting SmartArt Select the Insert tab from the Ribbon. Select SmartArt. In the Choose a SmartArt Graphic dialog box, select the category on the left. Adding Text to

9、 the Diagram Click the arrow to open the Text Pane. Click on the first line and begin typing. Click anywhere on the spreadsheet, and the Text Pane will close automatically. Or you can click the X in the top right corner. Resizing and Moving the Diagram Click on the SmartArt graphic to select it. Sel

10、ect one of the corners and drag the picture. Release the mouse when the graphic is the desired size. To Move The Diagram Select the diagram border. The cursor changes to a cross with four arrows. Drag the mouse to move the diagram. Release the mouse when the diagram is in the desired location. Reset

11、ting the Diagram Right-click on the diagram. Select Reset Graphic from the context menu. Adding Pictures from Your Computer Select the Insert tab from the Ribbon. Select Picture. Navigate to the location of the file on your computer or other media and highlight the file you want to insert. Select In

12、sert. Adding Clip Art Select the Insert tab from the Ribbon. Select Clip Art. In the Search for field, enter a key word. Select Go. Click on an image to add it to the current worksheet. Adding Text Boxes Select the Insert tab from the Ribbon. Select Text Box. Click on the worksheet and drag the mous

13、e to draw the text box. When you release the mouse, Excel inserts the text box. Begin typing to enter text into the text box. Drawing Shapes Select the Insert tab from the Ribbon. Select Shapes. Select a shape tool. Drag the mouse in the desired location to create the selected shape. Release the mou

14、se to complete the shape. Module Three: Auditing Precedent cells are cells whose contents are used in the active cell. Dependent cells are used in other cells contents or formulas. This module explains how to show these relationships. It also explains how to display the formulas, instead of the resu

15、lts, in a worksheet. Youll also learn how to work with comments in this module. The most erroneous stories are those we think we know best - and therefore never scrutinize or question. Stephen Jay Gould Tracing Precedent Cells Select the cell that contains the formula you want to trace. Select the F

16、ormulas tab from the Ribbon. Select Trace Precedents. Excel adds a tracer arrow from each cell that provides data to the active cell. Tracing the Dependents of a Cell Select the cell that you want to trace. Select the Formulas tab from the Ribbon. Select Trace Dependents. Excel adds a tracer arrow t

17、o each cell that uses the active cells data. Displaying Formulas Within the Sheet Select the Formulas tab from the Ribbon. Select Show Formulas. Adding, Displaying, Editing, and Removing Comments Select the cell where you want to add a comment. Select the Review tab from the Ribbon. Select New Comme

18、nt. Begin typing your comment. Show Or Hide Comments Select the cell with the comment. Select Show/Hide Comment or Show All Comments. To Edit A Comment Select the cell with the comment. Select Edit Comment. To Remove A Comment Select the cell with the comment. Select Delete. Module Four: Creating Ch

19、arts Charts provide a visual way of relating information. This module will explain how to insert a chart. Youll learn about the chart tools tab and gain an overview of the parts of a chart. Youll learn how to change the chart style, as well as how to resize and move a chart. We can chart our future

20、clearly and wisely only when we know the path which has led to the present. Adlai E. Stevenson Inserting a Chart Select the cells, including the labels to include in the chart. Select the Insert tab from the Ribbon. Select the type of chart you would like to use. Excel displays the chart. Understand

21、ing the Parts of a Chart The Chart area includes all other parts of the chart that appear inside the chart window. A data marker represents a single value in the worksheet. Depending on the type of chart, this may be a bar, a pie slice, or another shape or pattern. A group of related values make up

22、the chart data series. Charts usually have more than one data series, except pie charts, which only represents one data series. An axis is a reference line for plotting data. A two-dimensional chart has an X-axis and a y-axis. For many charts, the label is on the X-axis and the values are on the y-a

23、xis. Three dimensional charts also have a Z-axis. A pie chart does not have an axis of any type. Understanding the Parts of a Chart A tick mark intersects an axis as a small line. It may have a label and can indicate a category, scale, or chart data series. The Plot area includes all axes and data p

24、oint markers. Gridlines can make it easier to view data values by extending tick marks across the whole plot area. You can add chart text to include a label or title. The chart text can be attached to the chart or axis, which cannot be moved independently of the chart. Unattached text is a text box

25、simply shown with the chart. The legend defines the patterns, colors, or symbols used in the data markers. Changing the Chart Style Select the chart you want to format. Select the Chart Tools Design tab. Select the arrow in the Chart Styles area. Resizing and Moving the Chart Click on the chart to s

26、elect it. Select one of the corners and drag the picture. Release the mouse when the chart is the desired size. To Move The Chart To A New Worksheet In The Workbook Select the chart. Select the Chart Tools Design tab. Select the Move Chart tool. Select New Sheet. Give the new worksheet a new name, i

27、f desired. Select OK. Module Five: Creating Pivot Tables In this module, youll learn how to insert a chart. Youll gain an understanding of the PivotTable Tools tab. Youll also learn how to choose fields for your table and group data. Quick Tip: You can access the PivotTable Wizard by pressing ALT, D

28、, P. Inserting a PivotTable Place your cursor somewhere in the data you want to analyze. Select the Insert tab from the Ribbon. Select PivotTable. Excel displays the Create PivotTable dialog box. Select a location for the PivotTable. Select OK. Choosing Fields and Grouping Data Check the box next to

29、 a field listed in the PivotTable Field list to include it in the report. The default location where fields are added are as follows: Nonnumeric fields are added to the Row Labels. Numerical fields are added to the Values area. Date and time values are added to the Column Labels. The bottom of the P

30、ivotTable Field List pane includes four areas: Report Filter Axis Fields (categories or row labels) Legend Fields (column labels) Values Module Six: Working with Pivot Tables and Pivot Charts In this module, youll learn more about PivotTables and Pivot Charts. Youll learn how to change the data disp

31、layed and refresh the chart. Finally, well look at some real-life examples of using PivotTables and Pivot Charts. Quick Tip: When working with PivotTables and Pivot Charts, remember that you are not changing the structure of your original table or data. Feel free to experiment! Changing the Data Dis

32、played and Refreshing the PivotTable Return to the worksheet containing the PivotTable. Click somewhere on the PivotTable. Select the Options tab from the Ribbon. Select Refresh. Select the Options tab from the Ribbon. Select Change Data Source. Highlight the new data area on the worksheet. Select O

33、K. Excel opens the new PivotTable. Select and group the fields as desired. Applying a Style to Your Pivot Table Select the Design tab of the Ribbon. Select the small arrow in the PivotTable Styles area to see the PivotTable Style gallery. Select an option to apply the style. Creating a Pivot Chart f

34、rom a Pivot Table Select the Options tab from the Ribbon. Select PivotChart. Excel displays the Insert Chart dialog box. Select the desired type of chart and select OK. Creating a Pivot Chart from Data Place your cursor somewhere in the data you want to analyze. Select the Insert tab from the Ribbon

35、. Select the arrow under PivotTable. Select PivotChart. Excel automatically provides a range of cells based on your selection. Select a location for the PivotChart. Select OK. Add fields to view the chart. Module Seven: Macros This module will explain how to save time with macros. Youll learn how to

36、 display the Developer tab, which contains the tools youll need to record macros. Youll learn how to record and run macros. This module also explains macro security levels to avoid allowing malicious content to damage your computer with macros. Quick Tip: You can automate any Office task involving m

37、enus with a macro. Displaying the Developer Tab Select the File tab from the Ribbon. Select Options. Select Customize the Ribbon. In the Customize the Ribbon area on the right, check the Developer box. Select OK. Recording and Running Macros Select the Developer tab from the Ribbon. Select Use Relat

38、ive References. Select Record Macro. In the Record macro dialog box, give your macro a name. To make the macro available to other worksheets, select Personal Macro Workbook from the Store Macro In drop down list. Select OK to begin recording. Perform the actions you want to record. Select the Develo

39、per tab. Select Stop Recording. To Run A Macro Place your cursor in the cell where you want to perform the macro. Select the Developer tab. Select Macros. In the Macro dialog box, select your macro name from the list. Select Run. Changing the Security Level Select the Developer tab. Select Macro Sec

40、urity. Select one of the following options: Disable all macros without notification this option only runs macros in documents in trusted locations. Disable all macros with notification this option disables macros that are not in trusted locations, but it provides notification, so that you can choose

41、 to enable those macros on a case by case basis. Disable all macros except digitally signed macros this option allows not only macros in trusted locations, but also macros that are digitally signed by a trusted publisher. Other macros are disabled with notification to allow you to choose to enable t

42、hose macros on a case by case basis. Enable all macros this option allows all macros to run, which is potentially dangerous since virus authors often use macros to distribute malicious code on computers. Microsoft does not advise using this setting. Customizing and Changing the Quick Access Toolbar

43、Select the arrow to the right of the Quick Access Toolbar. Select More Commands. In the Choose Command From drop down list, select Macros. The macro you recorded should be listed. Select it and select Add. If you would like to modify the name of the macro, select Modify. In the Modify Button dialog

44、box, you can choose an icon to show in the Quick Access Toolbar. You can also modify the name. Select OK. Select OK in the Excel Options window. Module Eight: Solving Formula Errors This module will teach you how to prevent formula errors by using named ranges. Youll gain an understanding of formula

45、 errors and take a closer look at tracing dependents and precedents. This module explains how to use the trace errors commands. Youll also learn how to evaluate formulas. Quick Tip: Use named ranges to help understand your formulas. You can keep a list of named ranges in a worksheet by using the Def

46、ined Names group on the Formulas tab. Using Named Ranges Highlight the cell references you want to name. Select the Formulas tab. Select Define Names. Enter a name for the cell reference range. Select a different scope for the reference, if desired, from the Scope drop down list. Enter a Comment, if

47、 desired, Change the Refers to area, if desired. Select OK. To Use A Named Range In A Formula Begin entering your formula. When you are ready to enter the range, select the Formula tab. Select Use in Formula. Select the named range from the list. Understanding Formula Errors Start every function wit

48、h the equal sign (=) Excel will display the formula contents as text or a date if you do not use the equal sign. Match all open and close parentheses Every parenthesis needs a pair. Parenthesis must be in the correct position for the formula to work correctly. Use a colon to indicate a range When wo

49、rking with a range of cells, you must use a colon between the first and last cell reference. Enter all required arguments Some functions require arguments and some do not. If the function requires arguments, make sure you have the right number. Enter the correct type of arguments For functions that

50、require arguments, make sure you have the right ones. Nest no more than 64 functions The top limit of nested functions, or functions within a function, is 64. Enclose other sheet names in single quotation marks If your worksheet names contain non-alphabetical characters, you must enclose the sheet n

51、ame within single quotation marks when using the name in a formula. Place an exclamation point (!) after a worksheet name when you refer to it in a formula If you are using a worksheet name in a formula, the name must be followed by an exclamation point. Include the path to external workbooks If you

52、 are referencing cells from another workbook, make sure the formula includes both the workbook name and the path to the workbook. Enter numbers without formatting Excel treats commas as separator characters. Format the formula result after you enter the numbers in the formula. Avoid dividing by zero

53、 If you divide a cell by another that is zero or no value can result in a #DIV/0! Error. Using the Trace Errors Commands A formula with an error displays a green triangle in the upper left corner, along with an error icon next to the cell. Click on the arrow next to the icon to see the options. Help

54、 on this Error opens the Excel help files directly to an article related to the type of error Excel detected. Show Calculation steps opens the Evaluate Formula dialog box (discussed later in this module). Ignore Error allows you to keep the error and removes the error icon and green triangle. Edit i

55、n Formula Bar moves your cursor to the Formula bar to allow you to correct the formula. Error Checking Options opens the Options window to allow you to adjust the error checking options (discussed later in this module). Using Error Checking From anywhere on the worksheet, select the Formulas tab. Se

56、lect Error Checking. IN THE ERROR CHECKING RULES AREA, YOU CAN CHECK OR CLEAR THE FOLLOWING CHECKBOXES: Cells containing Formulas that result in an error When checked, Excel checks for formulas that do not use expected syntax, arguments, or data types. Inconsistent calculated column formula in table

57、s When checked, Excel checks for inconsistencies in calculated columns, such as when you enter data other than a formula in a column that has all calculated cells. Cells containing years represented in 2 digits When checked, Excel will create an error if you enter a date with a year represented as t

58、wo digits. Numbers formatted in text or preceded by an apostrophe When checked, Excel will create an error if you enter or import numbers preceded by an apostrophe or text. Formulas inconsistent with other formulas in the region When checked, Excel looks for formulas that are different from formulas

59、 near it. Often these formulas should be the same, except for the cell references used. Formulas which omit cells in a region When checked, Excel compares the reference in a formula against the actual range of cells adjacent to it. Unlocked cells containing formulas Formulas are locked for protectio

60、n by default and must be unlocked before editing. If you have unlocked cells with formulas, Excel marks it as an error when this box is checked. Formulas referring to empty cells When checked, Excel creates an error if a formula includes a reference to an empty cell. Data entered in a table is inval