M
ICROSOFT
E
XCEL
2010
Basic analyze data... 4
Use diagram to audit formulas ... 4
Use Error Checking feature ... 4
Use Evaluate Formula feature ... 5
Work with Goal Seek to get a desired result ... 6
Use Data Table to see changes affecting on a formula ... 7
Use Scenario Manager to consider many different variables ... 7
Create a scenario ... 7
Use scenarios ... 9
Explore The Analysis ToolPak Feature ... 10
Enable Analysis ToolPak ... 10
Use Analysis ToolPak feature ... 11
Analyze with PivotTable and PivotChart ... 12
Work with PivotTable reports ... 12
Create a PivotTable ... 12
Create a PivotTable from worksheet data ... 12
Create a PivotTable from an external data source ... 13
Modify fields in a PivotTable report ... 16
Turn on PivotTable Field List pane ... 16
Insert Slicers in an existing PivotTable report ... 17
Utilize a PivotTable report ... 19
Work with a PivotChart ... 20
Insert a PivotChart from an existing PivotTable report ... 20
Convert a PivotChart report to a standard chart ... 21
Find the name of the associated PivotTable report ... 21
Find the associated PivotTable report ... 22
Convert to a standard chart ... 23
Basic analyze data
Use diagram to audit formulas
Select the cell in the data range to auditGo to Formulas tab Formulas Auditing group select one of the following:
If the selected cell contains data that feeds into a formula, select Trace Precedents
If the selected cell contains formula that a cell feeds into, select Trace Dependents
To use shortcut to show precedents and dependents without drawing arrows, do the following
Select direct dependent cells: CTRL + ]
Select direct and indirect dependent cells: CTRL + SHIFT + ]
Select direct precedent cells: CTRL + [
Select direct and indirect precedent cells: CTRL + SHIFT + [
To remove arrows created by tracing precedents and dependents, go to Formulas tab
Formulas Auditing group click on Remove Arrows
Use Error Checking feature
Error Checking feature helps users to recognize and correct common errors in formulas Select the cell with a formula error
To remove arrows created by tracing errors, go to Formulas tab Formulas Auditing group
click on Remove Arrows
Use Evaluate Formula feature
Evaluate Formula feature helps users to understand different parts of a nested formula Select the cell contains formula
Go to Formulas tab Formulas Auditing group click Evaluate Formula
In the Evaluation Formula dialog box, do any of the following:
Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics
If the underlined part of the formula is a reference to another formula, click Step In button to display the other formula in the Evaluation box.
Click Step Out button to go back to the previous cell and formula.
To see the evaluation again, click Restart.
To end the evaluation, click Close
Work with Goal Seek to get a desired result
Goal Seek feature helps to find the input data for current output Select the cell contains formula
Go to Data tab Data Tools group click What-If Analysis click Goal Seek
In the Goal Seek dialog box, do the following:
In the Set cell box, enter the reference for the cell that contains the formula to be resolved
In the To value box, type the desired result
Use Data Table to see changes affecting on a formula
Data Tables feature helps users to substitute one or possibly two entire range of inputs to produce outputs corresponding with the substituted inputs
Select the range of cells contained formula and substitute inputs
Go to Data tab Data Tools group click What-If Analysis click Data Tables
In the Data Table dialog box, select one of the following:
If the variable value is arranged by row, select Row input cell to enter the cell reference
If the variable value is arranged by column, select Column input cell to enter the cell reference
Use Scenario Manager to consider many different variables
Scenario Manager feature is used to change more than two variables at a time or create a professional report
Create a scenario
Select any cell in the worksheet
In the Scenario Manager dialog box, click Add
In the Add Scenario dialog box, do the following:
In the Scenarios name box , enter scenario name
In the Changing cells box , select what inputs will be changed (Do not select the cell that contains data calculated by the function)
In the Comment box, enter description for the scenario (optional)
In the Scenario values text box, change the input values in the text box click OK
Use scenarios
In the Scenarios Manager dialog box, do any of the following:
To edit an existing scenario, under Scenarios box, select a desired scenarios name click Edit
To show changes in the worksheet, under Scenarios box, double-click any scenario name To set the scenario permanently in the worksheet, click Close
Explore The Analysis ToolPak Feature
Analysis ToolPak feature provides data analysis tools for statistical and engineering analysis
Enable Analysis ToolPak
Go to File tab select Options
In the Excel Options dialog box, do the following:
Click Add-Ins
In the Add-Ins dialog box, do the following:
Under Add-Ins available box, select Analysis ToolPak check box
Click OK button
Use Analysis ToolPak feature
Go to Data tab Analysis group click Data Analysis
In the Data Analysis dialog box, select a desired common analysis such as:
Correlation
Moving Average
Analyze with PivotTable and PivotChart
Work with PivotTable reports
A PivotTable report is useful when: A lot of data needs to be analyzed
Unable to see how they relate to each other A PivotTable report summarizes your data:
Turn data into knowledge
Anatomy of a PivotTable
Row labels
Column labels
Summary area The “pivoting” part:
Flexibility: just one or two clicks swaps columns, rows and filters
Filter PivotTable interactively using Slicers
Create a PivotTable
Create a PivotTable from worksheet data
Go to Insert tab Tables group click the PivotTable down arrow choose PivotTable
In the Create PivotTable dialog box, do the following:
Under Choose the data that you want to analyze section , do the following:
Select Select a table or range option
Under Choose where you want the PivotTable report to be placed, do one of the following:
To place the PivotTable report in a new worksheet starting at cell A1, select New Worksheet option
To place the PivotTable report in an existing worksheet, do the following:
Select Existing Worksheet option
In the Location box, specify the first cell in the range of cells to position the
PivotTable report.
Click OK
Create a PivotTable from an external data source
In the Create PivotTable dialog box, under Choose the data that you want to analyze section, do the following:
Select Use an external data source option
Click Choose Connection.
In the Existing Connections dialog box, do the following:
Click the Show down arrow select either the category of connections to specify a connection or All Connections (which is the default)
Under Select a Connection, select a connection
To reuse or share an existing connection, select a connection under the Connection in this Workbook
To copy the connection file into the workbook as a new workbook connection and use the file (an .odc file) as a new connection for the PivotTable report, select a connection from either under Connection files on the network or Connection files on this computer
In the Create PivotTable dialog box, under Choose where you want the PivotTable report to be placed section, do any of the following:
To place the PivotTable report in a new worksheet starting at cell A1, select New Worksheet
To place the PivotTable report in an existing worksheet, do the following:
Select Existing Worksheet option
In the Location box, specify the first cell in the range of cells to position the PivotTable
report.
Modify fields in a PivotTable report
Turn on PivotTable Field List pane
Click any cell in the PivotTable report
Go to PivotTable Tools tab Options tab Show group click Field List
Add fields to the report
By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the
Values area, and date and time hierarchies are added to the Column Labels area. To place a field in the default area of the layout section, select the check box next to the field name under
Choose fields to add to report box
To place a field in a specific area of the layout section, do the following:
Under Choose fields to add to report box, right-click the field name in the field section
Select either Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values.
To drag a field to the desired area, do the following:
Click the field name in the field section
Drag and drop it to an area in the layout section
To create multiple fields in an area, click and drag fields to the same area in the layout sections To rearrange the fields at any time, do the following
Right-clicking the fields in the layout section
Do one of the following:
Select the desired area
Insert Slicers in an existing PivotTable report
Slicers are easy-to-use filtering components that contain a set of buttons to quickly filter the data in a
PivotTable report, without the need to open drop-down lists to find the items to filter Click anywhere in the PivotTable report
Go to PivotTable Tools tab Options tab Sort & Filter group click Insert Slicer
In the Insert Slicers dialog box, do the following:
Select desired fields check boxes to create a slicer
In each slicer, do any of the following:
Click the items on which to filter.
Utilize a PivotTable report
To filter data in a PivotTable report, click the down arrow in each column header
To create a brand new worksheet for a underlying data for particular cell in the report, double click the cell
To update changes from the source data on the report, do the following:
Select any cell in the PivotTable report
Work with a PivotChart
A PivotChart report provides a graphical representation of the data in the PivotTable report. A PivotChart report is interactive which means that you can sort and filter it to show subsets of the PivotTable data
Insert a PivotChart from an existing PivotTable report
Click the PivotTable report.
Go to PivotTable Tools tab Options tab Tools group click PivotChart
In the Insert Chart dialog box, do the following:
Click the desired chart type and chart subtype except an xy (scatter), bubble, or stock chart
Convert a PivotChart report to a standard chart
Converting a PivotChart report to a standard chart will delete its connection associated to
PivotTable report.
You can convert a PivotChart report to a standard chart by deleting its associated PivotTable report. If you have multiple PivotTable reports and PivotChart reports in your workbook, you may first have to find the associated PivotTable report that has the same name as the PivotChart report
Find the name of the associated PivotTable report
Click on the PivotChart report
Go to PivotChart Tools tab Design tab Data group click SelectData
In the SelectDataSource dialog box, in the Chart data range box, note the associated
Find the associated PivotTable report
Click any cell in the PivotTable report
Go to the Options tab PivotTable group click Options
In the PivotTable Options dialog box, in the Name box, note whether the name matches the name of the associated PivotTable report.
Convert to a standard chart
Select the associated PivotTable report that has the same name as the PivotChart report that you want to change
