In this lesson I will show you how to use the Sparklines function, how to enrich your tables with charts that are so small that they can fit in every cell.

As an example, I have already chosen a table that has enough empty space on the right side for new elements. Although it doesn’t look like it at first glance, a whole series of charts can be placed in this column. Here’s how it’s done:

To begin with, I will click on the cell in which the first numerical value is located and, holding the Shift key, select the last cell of the table. Since I want to insert a new element, I will click on the Insert option and immediately go to the section called Sparklines. If I then click on the Line option, a new frame will appear on the screen. As you can see, the Data Range parameter is automatically set to match the selected range, so I just need to specify the location where the new elements will be placed. That is why I will first click on the field called Location Range and then select one of the cells belonging to the selected column. If I then press the Shift key and select the last cell in the row, Excel will automatically set this parameter as well. In the end, I just need to click on the OK button and see what I have achieved. As you can see, the entire column will be filled with broken lines corresponding to the selected data. If you want them to be a bit longer, simply change the width of the entire column. Of course, these graphs can be very small, but it seems to me that this dimension is quite appropriate.

Since we are talking about elements whose appearance depends on the input data, the only way to change their shape is to select another group of input data. This can be achieved by selecting the Edit Data button or the Edit Group Location & Data option. At that moment, the parameters frame will appear on the screen again, so it will not be difficult for you to change them. I will click the Cancel button because this layout of the chart suits me just fine.

In a similar way, you can change just one of these charts. Click on the Edit Data button and select the Edit Single Sparkline’s Data option, and a frame containing only one parameter will appear on the screen. It is a scope that includes exactly that part of the table that is responsible for the appearance of the selected chart. I will cancel this function again with Cancel and return to the table.

You can use the following procedure to remove all these charts. To start, select one of the cells that contain Sparkline graphs, and since the corresponding section of the toolbar is already displayed on the screen, it will not be difficult for you to click on the Clear button and select the Clear Selected Sparkline Group option from the menu. At that moment, all the elements you created using the Sparklines function will disappear from the screen.

In the rest of this lesson, I will show you that such charts can be created in a slightly different way. Namely, if I first select the space where they should be placed and then switch to Insert and start the Line function, a familiar frame will appear on the screen. As you can see, Excel has already filled in the field called Location Range, which means that I am expected to select the part of the table that contains the input data. The difference is only in the order in which I determined the parameters, but it is not bad to know that both ways are completely correct.

In the second part of the lesson, I will show you how you can change the appearance of these charts. Since they all belong to the same group, it is enough to select only one element and use one of the functions found on this segment of the ribbon. I will take this opportunity to highlight the beginning and end of each line by selecting the First Point and Last Point options. As you can see, small dots appeared at the ends, and in a similar way, the places representing the highest and lowest values (High Point and Low Point) can be marked. If you want to emphasize negative values, activate the Negative Points option. By selecting the Markers option, you can automatically mark all the characteristic points and thus highlight individual values even more.

To change the color, you can select one of these options or click the Sparkline Color button. At that moment, a small palette will appear on the screen, so it will not be difficult for you to choose the desired shade. If you think that the line with which they are drawn is too thin, click on the Weight option and choose a new width from the menu. The same applies to these small squares that we call markers. You can influence their appearance by clicking on the option of the same name and choosing one of the offered colors.

If you hate to adjust all these parameters individually, you can achieve the same effect by choosing one of the offered examples. For that, it is enough to open this menu and click on the combination you like the most.

I will use this opportunity to highlight only negative values on the graphs. To this end, I will turn off all options except Negative Points so that the appropriate markers appear on the charts. Unfortunately, this is not clear enough either, so instead of line graphs, I will apply those with columns. This can be achieved by selecting the Column button and the result will be immediately visible on the screen. Now I can play with these options again, but even that won’t help me to highlight the places where the losses appear. In such situations, it is best to apply the third type of diagram, which can be achieved by selecting the Win/Loss button. As you can see, now those parts of the graph that represent negative values will be displayed below the base line, so you won’t have a hard time spotting them. If even that is not enough for you, click on the Marker Color option and for Negative Points choose a color that is drastically different from the basic one. Of course, for all other cases, the easiest way is to select the appropriate example from this menu and change all parameters with one stroke.