There’s no doubting the usefulness of charts to spot trends and patterns, but they are impractically large for illustrating individual records. However, Excel 2013’s sparklines supports charting within cells themselves, so you can have hundreds of tiny charts on a single spreadsheet.
This approach differs from previously discussed options of data bars and icon sets in that sparklines don’t just illustrate a single data point in a cell; they represent multiple data points, as long as the data resides in the same row.
As an example, say you have a list of employees’ quarterly sales and want to compare sales trends. Constructing full-size individual charts would be tedious and take up too much screen real estate, but sparklines enable you to quickly plot this sales data and even copy/paste your efforts to other cells.
1. Select the Insert tab and choose one of the charting options in the Sparklines group. If Excel is maximized, the charting options appear on the ribbon itself, but if you have a smaller window, the options appear under the Sparklines drop-down list, as the screenshot below illustrates. You can choose between Line, Column and Win/Loss charts.
In this example, a line chart is selected to see trends across time more easily.
2. Enter the range of cells that contain the data in the Data Range field and enter the cell reference where you want the data to appear in the Location Range. This data should be all on in the same row. Click OK to accept your selections.
In this example, the data in B2 through E2 will be charted to cell G2 to display the four quarterly sales figures together.
3. Adjust the Chart as necessary. You can edit data, chart types, points, styles and axis options on the Design tab, which is automatically selected after inserting sparklines.
In this example, Markers are checked in the Show group to add dots to each charted data point. Checking other options in the Show group uses a different color for various points, such as the high, low, negative, first or last point.
4. Click Axis and choose any desired options.
The Axis drop-down menu enables you to select a date axis type, show a horizontal zero line, reverse the data or change minimum and maximum vertical axis values.
Selecting the Same for All Sparklines options won’t have an effort on a single chart, but if you later copy the chart, this option ensures all charts use the same minimum and/or maximum vertical values. This is helpful to compare the magnitude of values between records. However, if you only wish to see trends without worrying about scaling, keep the Automatic for Each Sparkline options checked so changes in direction are easier to spot because each record has its own catered range.
As an example of the Same for All Sparklines and Automatic for Each Sparklines options, say one record contains the values 1, 200, 100 and 50, and another contains 50, 52, 51 and 50. If the charts used the same minimum and maximum values, then the range of both records would accommodate 1 to 200, even though the data in the second record is only 50 to 52. Therefore, the second record’s chart would look like a straight line and seeing the change between values would be virtually impossible. However, if each chart used automatic minimum and maximum values, the second record would clearly show the change, because the range only accommodates values from 50 to 52. That’s perfect to illustrate changes, but it won’t allow you to compare the data magnitude between rows.
5. Drag the lower right Fill Handle down to the last record before releasing your mouse button.
This screenshot shows dragging from G2 to G6 as it appears before releasing the mouse button.
After releasing the mouse button, you can see the chart is recreated in each cell of the dragged range, relative to the row number.
Because this example opted for automatic minimum and maximum vertical values, the movement of each record is clearly defined, but not scaled for comparing data between rows.
If, however, you opted to keep the minimum and maximum values the same for all sparklines, the movement is less defined, but the vertical position is scaled for comparison between rows.
Also, note that these sparklines are background effects, so you can still enter data in the cells without deleting the charts, which appear behind any entered values.