Sparkline Chart Drawings

Sparkline Chart Drawings

Sparkline charts are small and compact charts with high data density and serve to display the trends and variations of associated data areas in figures. SpreadsheetWEB will automatically convert them into the web application as long as they are correctly formed within the spreadsheet. However, one has to put them together in their spreadsheet at the Excel level. Below, you will find the instructions on how to do that.

Basically, there are 8 types of different sparkline charts that SpreadsheetWEB currently supports.

1. Line:

The basic formula setting for a SparklineLine chart is:

=SparklineLine(InputCells, MaxMin, FirstLast, AllPoints)

Input Cells: It is the cell range that will make the sparkline line chart. (e.g. A1:C1, B1:N:5, C1:C22)
MaxMin: This paremeter defines the maximum and minimum points of the line chart. Sample values can be either 'True' or 'False'. If you want to mark the maximum and minimum points in the sparkline, write 'True' into the cell, otherwise write 'False'.

Figure 1. MaxMin = True

FirstLast: This option helps to point out the first and last points of the sparkline. If you enter 'True' into the cell, then the sparkline will be shown with edges at the beginning and end of the line. Write in 'False' for no edges.

Figure 2. FirstLast = True

AllPoints: In order to show multiple points on a sparkline, set this paremeter to 'True'. Otherwise set it to 'False'.

Figure 3. AllPoints = True

Finally the formula should be something like: =SparklineLine(A1:C1, True, False, False)

2. Discrete Bar:

SparklineDiscreet gives a discreet look to the sparklines.

The basic formula setting for SparklineDiscreet is:

=SparklineDiscreet(InputCells, Threshold)

InputCells: It is the cell range you have to select in order to make a chart out of the data in those cells. (e.g. A1:D1, C1:E1, F1:G16)
Threshold: This paremeter changes the linear alignment of a bar by assigning two different colours (black and blue) to different sets of data values.

Figure 4. Threshold = 0

Let us assume that we have a set of numbers including 1, 7, -5, 0, 9, 12, -11 and 2.

Their look when arranged in a row will change according to the threshold. Results for thresholds 0, 10 or -20 will be different.

All in all, the formula should be like: =SparklineDiscreet(A1:D1, 10)

3. Bar:

The basic formula setting for the SparklineBar chart is:

=SparklineBar(InputCells, Threshold)

The configuration of SparklineBar is the same as that of the SparklineDiscreet bar (see above), but the outcome is favorably different. SparklineBar charts look like the smaller copies of Excel bar graphs. Depending on the variance of the threshold value, the appearance of the chart will change.

Figure 5. Threshold = 5

The formula you have entered should consequently be similar to:

=SparklineBar(B1:K1, 10)

4. Area

The basic formula setting for a SparklineArea chart is:

=SparklineArea(InputCells, Border, Points, Color)

InputCells: Select the data range. (e.g. B3:I5)
Border: Default SparklineArea appears with no borders on the web application. If you want borders to be added to the figure, simply write 'True' in the cell next to the cell range. The area will be framed in black. Enter 'False' for no borders.
Points: Just like the AllPoints parameter in SparklineLine, type in 'True' to get several points on the area highlighted. Again, type in 'False' for a plain area.
Color: This parameter gives you the opportunity to change the color of the chart. Write in "Red", "Orange" or "Blue", or use the RGB values like #DEFC11.

Figure 6. Color = "Orange" Figure 7. Color = "Red"

In the end, the formula should look like:

=SparklineArea(C1:K11, True, False, "Yellow")

5. Pie

The basic formula setting for a SparklinePie chart is:

=SparklinePie(InputCells, Color)

InputCells: Select the data set you want to display as a SparklinePie. (e.g. D1:E15)
Color: You can change the color of your charts. Simply write the name of the color or the RGB value into the relevant cell. (e.g. "Red" or #35CFA6)

Figure 8. Color = "Green" Figure 9. Color = "Red"

Note that the formula cell should be something akin to:

=SparklinePie(F3:H6, #35CFA6)

6. Icon

The basic formula to make a SparklineIcon chart is:

=SparklineIcon(Range1,DataPlace,IconType,Range2,SortType)

When it comes to creating an icon set, the variables are:

InputCells: Select the data set you want to apply one or more icons to (e.g. A1:C1, D2:F7). This is the range of values for which this icon will be displayed. For example, if the range for a the red arrow in the "3 Arrows Colored" set is 1-3, then the red arrow will show up whenever the output data is within the range of 1-3.
Index: Since the formula is applicable to a single cell, you need to apply conditional formatting to the other cells as well, and write down the index interval of the other cells to this part of the formula (e.g. A1, B2, C3...). This means that all data ranges should be represented by the icon set. If you have data, for example, with a total range of 1-9, then all numbers from 1-9 should be represented (i.e. 1-3 is the first icon, 4-6 is the second icon, and 7-9 is the third icon). However, these need to be represented by cells. Therefore, if 1-3 is present in cells A1, A2, and A3, the first range should show A1:A3 as the range.
IconType: Choose the icons from the list of icon sets below:

You should add the name of the set (e.g. "3 Arrows Colored") to the formula.

InputCells2: You have to define these cells to adjust the border values for icon types. Since icons feature conditional formatting, those associated cells are very important and need to be supported by InputCells2. Also see the SortType parameter.
SortType: This parameter requires three extra values (the "Percent", "Percentile", and "Number") to distribute the icons successfully. You are empowered to choose one of the three sort types. The formula will look into the InputCells, Index, and the InputCells2 respectively to determine if the sort type you have entered fits the requirements. Please see the examples for SparklineIcon.

Your formula should be similar to the following:

=Sparklinecon(A1:A12, B1:C2, "3 Arrows Colored", "Percentile")

Examples for SparklineIcon:

To understand the implementation of icons more clearly, please read the following examples:

1. "Number"

Assume that there are the names of employees listed in your spreadsheet, and you want to apply an icon to each employee according to their salaries per year.

IconType = "3 Arrows Gray"

InputCells2 = "10000" and "50000"

SortType = "Number"

Then, the distribution of icons will most likely be as follows:

Down Arrow => Employees whose salary per year is lower than 10000.

Horizontal Arrow => Employees whose salary per year is between 10000 and 50000.

Up Arrow => Employees whose salary per year is higher than 50000.

2. "Percent"

Assume that you need to icon 300 numbers with their values ratio.

IconType = “3 Signs”

InputCells2 = “50” and “70”

SortType = “Percent”

The formula detected that the highest number in the InputCells is 3215, so our icons will be distributed like;

Diamonds => Numbers that are smaller than (3215/100)*50

Triangles => Numbers that are between (3215/100)*50 and (3215/100)*70

Circles => Numbers that are higher than (3215/100)*70

3. "Percentile"

Assume that you have 10 numbers (1, 2, 45, 36, 5, 666, 100, 3654, 9, 44) and you want to icon them with respect to their ranks.

IconType = “3 Arrows Grey”

InputCells2 = “32” and “77”

SortType = “Percentile”

Then, the formula will sort the values and distribute the icons respectively to the sorted InputCells;

Down Arrow =>

---Exceeds 32% of the numbers so change the icon

Horizontal Arrow =>

--- Exceeds 77% of the numbers so change the icon

Up Arrow =>

100

666

3654

7. Bullet Graph

The chart formula for SparklineBullet is:

=SparklineBullet(InputCells, InputCells2, Color)

InputCells: Select the cells and write their range to this part. (e.g. A1:A11, B2:B13...)
InputCells2: This part is necessary for indicating the actual and target values of the bullet. It retains two cells: Actual and target values. Actual value means the cell that you want the bullet to capture. Starting from that value, the bullet chart stretches further enclosing the target value. However, it is optional to set a target value.
Color: Write the color name or the RGB value into the formula box. (e.g. "Green", #00FF00)

Figure 10. InputCells without target value and Color = "#00FF00" Figure 11. Basic SparklineBullet

Your formula should be something like this:

=SparklineBullet(B1:B10, C2:C11, "Purple")

Bullet Graph can either be horizontal or vertical depending on the height and width of the target cells.

8. Scale

The chart formula for SparklineScale is:

=SparklineScale(InputCells, TopBottom)

InputCells: The configuration of input cells for SparklineScale is different than the other sparkline charts. Input cells for scale entails three distinct cells: "Start Number", "Finish Number", and "Interval". Arrange them, for example, in such order: "A1:C1, B5:B7, C1:C3"
TopBottom: Set the parameter to 'False' if you want the scale to be upside down. Type in 'True' for an upward look.

Figure 12. TopBottom = True Figure 13. TopBottom = False

Overall formula should look like:

=SparklineScale(A1:A3, True)

9. Icon Bar

The chart formula for the Sparkline Icon Bar chart is:

=SparklineIconBar(DataRange,BarPoint,Color)

DataRange: This is the full range of data points. For instance, if you have 10 data points in the cell range A1 through A10, the DataRange would be A1:A10.
BarPoint: This is the data point that responds to this particular bar. Since the Sparkline Icon Bar is a bar chart that is separated into individual data points to create one finalized bar chart, each formula needs a single reference point. For example, if the data range is from A1:A10, then each data point needs a separate SparklineIconBar formula. An example formula for this example could be =SparklineIconBar(A1:A10,A2,Orange).
Color: The color will be the color of the chart. You can either use names of colors (e.g. Red, Orange, Yellow, etc.) or hexadecimal color codes (e.g. FFFF00). You can select different colors for each bar in the set. You can also use a cell link if you have entered your the color name/hex codes in a range of cells in the workbook.

Figure 14: An example Sparkline icon bar chart.