Follow

Analysing Data in Excel

Data can be analysed more productively if it is presented in an effective manner.  Excel provides many tools that allow for data to be displayed in easy to comprehend formats.

Trendlines:
Definition: A trendline is a graphical representation of trends in a data series. Usually represented as a line, a trendline can be particularly useful in depicting current or future trends.

Trendlines can be added to the data series in a column, line, bar, area, stock, or bubble chart. They can be created using default settings or you can create your own custom settings.

Types of Trendlines
There are a number of different types of trendlines that you can use depending on what you are trying to show.

Exponential
Curved lines that are best used when data values rise or fall at increasingly higher rates.

Linear
Straight lines that are best used with linear data sets.

Logarithmic
Curved lines that are best used when the rate of change in the data increases or decreases quickly and finally levels out.

Polynomial
Curved lines that are best used when the data fluctuates with ups and downs.

Power
Curved lines that are best used with data sets, which compare measurements that increase at a specified rate.

Moving Average
Curved lines that are used to smooth out any fluctuations in data, thereby displaying a pattern in the data.

Sparklines
Provide the simplest way to represent trends in a cell on a worksheet.

Definition: A sparkline is a tiny chart embedded in a cell to represent the trend for a given range, which can be a row or column. Unlike a chart, a sparkline can be used as a cell background.

Moreover, you can create a sparkline for a single range and then extend it to multiple ranges using the fill handle.

Types of Sparklines
There are a number of different types of sparklines that you can use depending on what you are trying to show.

Line
Data trends are displayed in the form of a straight or zigzag line.

Column
Data trends are displayed in the form of column. Each data value is represented by a column whose size is proportional to the data value.

Win/Loss
Data trends are displayed through the high points, the median, and the low points.

Scenarios
Allows you to create and test multiple scenarios to help you choose a particular outcome and plan accordingly.

Definition: A scenario is a set of input values substituted for the primary data in a worksheet. These input values are used to forecast outcomes based on the data that represents the scenario in your worksheet. You can create any number of scenarios in a worksheet and switch between them to view their results.

The What-If Analysis Option
The What-If Analysis option is used for performing analysis using the Scenario Manager, Goal Seek, and Data Table options.

Scenario Manager
Create scenarios.

Goal Seek
Set the value stored in a single cell to a specific value, thereby changing the value stored in another cell.

Data Table
Display the varying results of formulas based on different values given as input.

The Scenario Manager Dialog Box
The options in the Scenario Manager dialog box allow you to create, edit, delete, and merge scenarios.

Scenarios
Lists all scenarios you have created in the worksheet.

Add
Invokes the Add Scenario dialog box that allows you to create a new scenario.

Delete
Deletes the selected scenario.

Edit
Invokes the Edit Scenario dialog box that allows you to edit a scenario.

Merge
Allows you to merge scenarios from other worksheets.

Summary
Displays a summary of the scenario in the Scenario Summary dialog box.

Changing Cells
Display the cell reference for changing cells.

Comment
Displays the comments entered in the Add Scenario dialog box.

Show
Displays the result of the selected scenario on a worksheet

 

Return to Top

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk