How to color tables by categories (conditional formatting)

In Datawrapper, you can format individual cells from the customize column section in Step 3 - but manually formatting each cell one-by-one can take a very long time! 

Good news - there is a way to conditionally format your cells, using an option called color cells based on categories in Step 3: Visualize > Customize Column: 

For example, here we have a table showing the monthly unemployment rate of selected countries (2020): 

Like all other charts on Academy, you can copy this chart to your account with one click! (Hover-over > click "Edit this chart") 

Index

1. Color columns based on categories in one column
   
   • Add a category column in your dataset
   • Select column and assign colors
   • Color entire rows
   • Hide the category column
2. More complex color-coding in tables
   
   • Use your spreadsheet to categorize your data
   • Re-assign category columns

Color columns based on categories in one column 

Add a category column in your dataset

Let's say we want to color each country based on the continent it belongs to. In order to color based on categories, we would need to include a column in my dataset with the names of the corresponding continents: 

Here's what my spreadsheet looks like:

It's best if we do this before we upload the data in Datawrapper. But, you can also use our added columns feature to add an extra column in Step 2. 

Select column and assign colors

Once the category column (in this case "continent") is in our data, we can move to Step 3: Visualize. Let's use this column to color cells based on categories. 

1. Go to Customize Colors and select the column you want to change the background color for (in this case "country")
   
2. Click on Color cells based on categories > Background
   
3. Choose a column you want the categorize your colors by (in this case, "continent")
4. Then assign individual colors to each category (continent)! 
   
   Here's a summary: 

Color entire rows

Right now, we only colored in the "country" column. If we want to color the entire row, then we can simply select multiple columns.  

Hold down  Ctrl key (or Cmd key on Mac) while selecting, or simply select "all". 

Hide the category column

Now, we can (or not) hide the column we used for categorizing the rows by simply un-checking 'Show on'  desktop and mobile options: 

More complex color-coding in tables

Now, what if we wanted to conditionally color the numbers according to whether their unemployment rates are above or below OECD average?

This is done with the same principle. 

Use your spreadsheet to categorize your data

We'll go into our spreadsheet (you can take a look at the google sheet here) and created new columns for categories. 

In these new columns, we'll use this IFS function to display "above", "below" or "average" depending on whether the unemployment rate was higher, lower, or equal to the OECD average. Let's call them <Month - color > (e.g. Jan - color, Feb color ... etc.)  

Then upload the data into Datawrapper. 

First, let's hide the <Month - color> columns: 

Multi-select columns Jan to Aug: 

Then select one of the <Month - color> columns (any one of them is good but we'll got with the color category for January).

Assign colors to the three categories ("average", "above" and "below) 

Now, we have a table that have color-coded text. 

BUT! All columns are color-coded according to whether January's unemployment rate was higher/lower than the OECD average. What we actually want is to compare on a monthly basis. 

Re-assign category columns 

To do this, simply select one month column at a time and reselect the corresponding <Month - color> columns: 

And that's it! 

We hope you found this helpful and if you have any questions, reach out to us at  support@datawrapper.de.