Beyond simple pie charts, bar and column charts, you can create an additional layer of information in your Excel charts. Two such techniques are a secondary axis and trendlines.
Secondary axis
As an example, say you have production cost and retail price information in a spreadsheet. The difference between these, divided by your production cost, would represent your margin or profit. However, the dollar values of production cost and retail price are far larger than the <1 size of your profit percentage. Adding a secondary axis on an additional right axis solves this problem. Here’s how to do it:
Also See: Learn how to Organize Data to Make Consistent Pivot Tables & Charts
- Create a basic column chart with your data. You’ll notice that your Margin series is virtually invisible. Go to the Chart Tools contextual tabs called Layout, and choose Margin selection from the dropdown list. This makes it easier to select this series.
- Click on the Format Selection button located below your Margin selection.
- Click on the Secondary Axis radio button.
- Right-click on the Margin series that is still selected and choose Change Series Chart Type to make the appearance more meaningful. Pick one of the line chart types.
Also See: 10 Simple Tips To Make Your Excel Charts Sexier
Trendlines
You can generate the most basic trendline by simply choosing the series you want to base your trend on and then selecting Add Trendline on the right-click menu. This will show your selected series as a trend through all the other series. If you need to create a more predictive trendline into the future, you’ll find some useful tools on the Layout tab of the Chart Tools contextual tabs. Choose the Trendline button to see what preset options are available. Select More Trendline Options to predict future trends on historical data. On this dialog box, you can choose the type of trendline and the number of periods forward (or backward) to forecast the trend. Example: Say you want to predict sales three months into the future based on past sales for one year. Here’s how to do it:
- Create a column chart with your data.
- Select the Trendline button and More Trendline Options.
- Choose your trendline type (such as linear or logarithmic) and enter the number of periods (months) to forecast, in this case three.
Also See: How to Choose the Right Chart Type for your Data
Finishing touches
You can display the equation by checking the box at the bottom of the Format Trendline dialog box. You can also make the appearance stand out with color or a different line style. Select those from the same dialog box. If you want a baseline on the chart as a horizontal line, from the Insert tab choose Shapes and the line shape. Hold down the Shift key to draw the line straight across the chart area. You can format this line further from the Drawing Tools contextual tab buttons.
Courtesy: Business Management Daily
Also See