Excel Chart Trendlines

Trendlines can be used to show the relationship between x and y values on a chart. A trendline will run through data points, in such a way that the values above and below the line will more or less cancel each other out. Six different types of trendlines can be added to Excel charts.

1. Linear

2. Exponential

3. Logarithmic

4. Power

5. Polynomial

6. Moving Average.

To add a trendline select the specific data series you're working with. On the PivotChart Tools menu, select the Design submenu, and on the left pick 'Add Chart Element . . . Trendline'.

A linear trendline will be a straight line that is based on the average variation between the dependent (y axis) and independent (x axis) factors.

Selecting 'More Trendline Options' will bring up a pane on the right that will allow you to select between all six options. The exponential trendline will rise or fall at an increasingly higher rate.

A logarithmic trendline is used to show values that rise or fall at first, then level off. It's the opposite of an exponential trendline.

A power trendline will curve steadily in one direction.

A polynomial trendline is based on an equation. These trendlines are used when the overall trend does not go in one direction. The higher the option number set for a polynomial trendline on the right, the more it will curve in a serpentine fashion.

A moving average trendline will smooth a trendline by basing it on the average of a specified number of periods. In this example, the period for a moving average can be set to between 2 and 19, since the chart tracks sales in 20 different weeks.

Add-in To Merge Multiple Workbooks

Ron de Bruin has posted a free Add-in for Excel here, which can be used to merge multiple Excel workbooks. Download the file RDBMerge.xlam, and then in Excel go to File . . . Options . . . Add-ins. In the Manage drop down menu at the bottom, select 'Excel Add-ins', and then browse to the downloaded file, and add it.

RDB Merge will be added as an option on the Data tab on your Excel ribbon. You will be able to select a folder containing multiple Excel workbooks. Select the format of those workbooks (.xls; .xlsx; or .csv). Ron's add-in givens you the option to select specific files, or merge all of the files in the folder. Specific worksheet names can also be targeted. The 'Use the sheet index' option gives you the ability to select one of the sequential worksheets in each workbook - the first, second, third, and so on.

A specific range of cells must be designated. By default, RDB Merge will add the path to each source workbook in column A.

Unfortunately the add-in doesn't line up columns which have matching headings.

Timeline Slicers

Last night's tip explained how to apply slicers to multiple pivot tables on an Excel worksheet. It's also possible to use slicers on Pivot tables to apply timelines on pivot tables which include a date field. You can slide a date range back and forth to change the data calculated in the pivot table.

Begin simply by selecting any cell in a pivot table. In the PivotTable Tools menu, select Analyze. Next to Insert Slicer, click on Insert Timeline. You will be prompted to select any date fields in the table.

A sliding scale will appear that you can move in increments of years, quarters, months, or days.