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.

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.

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

Straight lines that are best used with linear data sets.

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

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

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.

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.

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

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.

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

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.

Lists all scenarios you have created in the worksheet.

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

Deletes the selected scenario.

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

Allows you to merge scenarios from other worksheets.

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

Changing Cells
Display the cell reference for changing cells.

Displays the comments entered in the Add Scenario dialog box.

Displays the result of the selected scenario on a worksheet


Return to Top


Was this article helpful?
0 out of 0 found this helpful