Bar chart within a cell

Bar charts in Excel are very useful when graphically representing our data because they allow us to adequately visualize their trend. Excel 2010, and later versions, include functionality to create sparklines but this time we will do an exercise where we will create our own bar sparklines.

The technique that I will use to create the bar sparklines will be very similar to the one I have shown in the article Pie chart inside a cell, so it would be a good idea to also refer to that post to understand the possibilities of this method.

Our goal of creating bar charts within a cell will be achieved in four steps: the first will be to download a font that will represent each of the bars within the cell. The second step will be to normalize the data, later we will concatenate these values ​​and finally we will apply the appropriate font style to the cells.

Font for sparkline bars

The first step is download source which will help us show the bar sparkline. Once you have downloaded it, you must install it on your computer by right clicking on the file and select the option .

The fountain is a collection of ten characters that represent different fill levels of a bar. The characters go from 0 to 9 where each one of them increases the total of the bar by 10% until it reaches 90% and it will be the period character (.) That will represent a bar at 100%. The following image shows an example of the bar sparkline that we can achieve with the font just installed on our computer.

Bar chart inside a cell in Excel

Normalize data for sparkline

For our example we will assume the sales data of different products for each of the days of the week. What we need to graph is the behavior of daily sales for each product, but these numbers exceed the limit of 10 positions that our source has, so it is necessary to adjust the data to the appropriate scale.

Sparklines of data bars in Excel

Before proposing the formula that will help us to normalize the data, I want to clarify the use of the following formula:

=REDONDEAR(C3/MAX($C3:$I3) * 10,0)

Pay special attention to the denominator of the division which is MAX ($ C3: $ I3) * 10. The MAX function helps us to obtain the maximum value of the range C3: I3, which is the sales of Product A and we will multiply this value by 10 so we will force all values ​​to be within the scale from 0 to 10. The ROUND function is present to avoid any decimal number in the division result.

We will place this formula inside a IF function so that its execution is conditioned to those cases in which we have a value other than the maximum value of the range, in which case we need to obtain the point character (.) which is equivalent to having a 100% bar. In this way, the formula that I will use to normalize the data will be the following:

=SI(C3=MAX($C3:$I3), ".", REDONDEAR(C3/MAX($C3:$I3) * 10,0))

I will put this formula in cell J3 to normalize the value of cell C3 and the result will be as follows:

Use sparklines to show data trends

To obtain the normalized value of every day for all products, it will be enough to copy the formula in cell J3 to the right and down to obtain the following result:

Bar charts within cells in Excel

Note that the normalized value that corresponds to the day with the highest amount of sales of the week, will have the point character (.) Which will later help us to represent it properly by a 100% bar.

Concatenate the normalized values

Now that the data is normalized, we must concatenate all the values ​​for the same product and place it in column Q. We will achieve this with the CONCATENATE function and indicating each of the cells of the normalized values. For example, to concatenate the values ​​of the I will use the following formula in cell Q3:

=CONCATENAR(J3,K3,L3,M3,N3,O3,P3)

I can copy this formula downwards and I will have all the values ​​concatenated in the Q column as you can see in the following image:

Sparklines and data bars in Excel

Notice that the values ​​in column Q are aligned to the left, which indicates that they are text strings and not numeric values. Each character in this text string will be equivalent to a data bar when making the font change.

Change font type

To finish with our exercise, we only have to change the font type of the cells that have the concatenated values, so it will be necessary to select those cells and then go to the Home tab and select the font type :

Bar chart within a cell

When making the change of font, we will obtain the bar graphs within the selected cells.

How to Create Sparklines in Excel

Optionally you can hide the columns with the normalized values, and even hide the columns with the original data to leave only the cells with the sparklines which will show the trend in the sales of each of the products during the week.

With this we have finished creating our own bar graphs within a cell. Although this method is excellent for practicing our use of formulas in Excel, it will be up to you whether you decide to use it for sparkline generation or you decide to use native Excel commands for create sparklines that are already available from version 2010 and that you can find in the file .

Leave a Comment