To create a win-loss chart in Google Sheets, we use the SPARKLINE function.
Syntax of the SPARKLINE function=SPARKLINE(data, [options])data – The range of data to represent in a chart.options – A group of optional settings and their related properties for customizing the chart.
The “charttype” option allows us to indicate the type of chart to make. The various sparkline charts include line graphs, stacked bar charts, column charts, and win-loss charts.
Example Usage=SPARKLINE(D5:H5, {"charttype","winloss"})
//The SPARKLINE function takes in a data range D5:H5 and displays it in the form of a win-loss chart specified in the charttype property.
Clink HERE to make a copy of the spreadsheet with formula and example.
Introduction
Google Sheets through Sparklines enable us to conveniently present visual data using charts that don’t take much real estate in our sheets. We can create miniature charts in cells to visualize the data sample in corresponding cells. The win-loss chart is an example of such a chart.
A win-loss chart is a simple tool that is ideal for presenting data with binary outcomes—yes or no, 0 or 1, win or loss. For instance, let us say we want to tabulate the results of a chess tournament. We want to do this in such a way that each participant in the tournament sees the number of games they have won or lost. We can use the table below
Now, imagine if there were 20 rounds played, wouldn’t it be hard making any visual sense of the table? While there are alternatives to present this data, the win-loss chart in Google Sheets can help us do this easily by presenting charts for each participant in the data set. It aids us to—at a glance—get a sketch of a participant’s overall performance and win/loss rates.
In this tutorial, we will create a win-loss chart in Google Sheets and customize the chart so that it is easier to read and understand.
How to create a win-loss chart in Google Sheets
Unlike normal charts, we won’t be inserting the win-loss chart using the insert tab. The sparkline charts are added using the SPARKLINE function.
Using the example from above, we will consider the simple steps involved to create a win-loss chart in Google Sheets.
Step 1: Set up the data for win-loss chart in Google Sheets
We are going to modify our table which contains the total number of games that 10 players participated in. We need to include an extra column to display the win-loss charts for each of the participants.
To add a new column:
- Select column C, and right-click on it to open an options menu
- Select Insert 1 column to the left
- Name the column Summary
Step 2: Insert a win-loss chart in Google Sheets
To add a win-loss chart to our table, we enter the function below into the first cell of the Summary column
- Begin to type =SPARKLINE as shown below
Recall that the first parameter is the data range to implement in the chart, therefore:
- Enter D5:H5 as the data parameter in the Sparkline function.
- The second parameter is where we are going to indicate the type of chart we want.
- Enter the type of chart in between curly braces as shown in the image below. The option here is “charttype” and we are setting this to a win-loss chart using the “winloss” property.
- Press the enter key.
From the above screenshot, we can see a basic chart that represents wins with black squares and leaves a blank space for each loss.
Step 3: Set up the win-loss chart for the remaining players
Simply change the range i.e data parameter to correspond to the appropriate player’s info. For instance, player 2, Brown, will have D6:H6 as data.
Tip: To generate win-loss charts for the remaining players quickly, click on the dot in the right corner of the cell and drag it down.
Google Sheets will automatically display the win-loss chart for each player.
Customizing the win-loss chart in Google Sheets
Though the sparkline charts are somewhat limited relative to normal charts in terms of customization options, we can still customize them by changing some of the default features.
Changing the chart colour using a colour name
To change the colour of the win-loss chart we need to modify our formula.
- Enter the formula below into a cell
=SPARKLINE(D5:H5, {"charttype","winloss";"color","red"})What this does is add another property to our chart–a colour property that we have set to red. Notice how we used a semicolon to separate the colour property from the chart-type property.
- Press the enter key.
The colour successfully changes to red. We can do the same for the remaining charts by simply dragging down the blue dot in the right corner of the cell.
Changing the colour of the win-loss chart in Google Sheets using the hex code
Alternatively, we could use hex codes to set the colour of our chart. While using colour names could be a much simpler approach, hex codes enable us to achieve different colour shades that may be otherwise difficult to achieve.
- Enter the formula below into a cell
=SPARKLINE(D5:H5, {"charttype","winloss";"color","#FF0000"})- Press the enter key.
We have changed the colour property by providing the hex code for red instead.
Changing the colour of negative values in the win-loss chart in Google Sheets
Another way to use the win-loss chart is to represent losses using negative values instead. With this approach, you can easily set a colour to represent wins and another to represent losses using the negcolor property. This makes the chart easier to read.
- In our table, change all the losses from 0 to -1
- Enter the formula below
=SPARKLINE(D5:H5, {"charttype","winloss";"color","blue"; "negcolor","red"})We have set the color property to blue for the wins and the negcolor to red for the losses.
- Press Enter.
Win-loss charts are very useful for economically visualizing binary data. It is a miniature chart that we can further customize to look even better.
Commonly asked questions
How do you make a win-loss chart?
To make a win-loss chart in Google Sheets use the SPARKLINE function. A win-loss chart displays binary data inside cells in the form of miniature charts.
E.g.
=SPARKLINE(A5:H5, {“charttype”,”winloss”;”color”,”blue”})
How do I graph results in Google Sheets?
Graphing results in Google Sheets involves making use of charts to visualize data in your sheet.
To use normal charts:
- Click on the Insert tab.
- Select Chart, this opens the chart editor.
- Set the chart type e.g histogram chart or column chart.
What is a win-loss chart?
A win-loss chart is a simple way to present binary data. If the outcome of an event varies between two distinct states e.g on and off, head or tail, profit or loss, etc, then it could be useful to create a win loss chart in Google Sheets to visualize this data.
See also
Here are some similar articles on our blog, check them out:
Sparkline in Google Sheets| Visualize Data in 2 mins