44

Excel has a cool feature called "data bar" conditional formatting that allows the user to partially fill a cell background to create histograms based on cell contents. Here is a screen clip example from a workbook:

example

I'm looking for a TiKZ, minipage, or some other solution that will allow me to only partially color the background of a cell. I've played around with a few options without success.

Here is a minimum working example using \cellcolor from the xcolor package.

\documentclass{article}
\usepackage[table]{xcolor}% http://ctan.org/pkg/xcolor
\begin{document}
\begin{tabular}{l|c|r}
  \hline
  91.41 & \cellcolor{green!25}90.81 & 38.76 \\
  \cellcolor{green!25}98.75 & 13.82 & 94.62 \\
  57.11 & \cellcolor{green!25}51.21 & \cellcolor{green!25}42.84 \\
  \hline
\end{tabular}
\end{document}

The code above produces:

mwe-example

In a perfect world, I'd have a macro like \partialcellcolor{green!25}{0.41\linewidth} or whatever to specify that the color extends across 41% of the cell. I use "R" to generate the table, so I'll let it do the math as it scripts the latex code.

Any suggestions?

barbara beeton
  • 88,848
John Leonard
  • 1,294

4 Answers4

42

I've rewritten this whole answer. For the previous version, see the history of this answer.

For this, the columntype H is introduced that takes one possibly empty argument.

For positive numbers the bar is filled from left to right, for negative nmbers the bar is filled from right to left.

They values min, max, min neg, max neg need to be set to scale the bar. The max neg value is initialized with 0 as it is used to determine the width of the node which is needed for align=right to work. (There are other ways to do this but I'm keeping it simple.)

I don't think it makes much sense to set min and min neg to something other than 0 but the option is there.

Various styles can be used to change the appearence. Hopefully, these are self-explanatory.

This is how the table is ultimately specified:

\begin{tabular}{
  | H{} | H{min=2.00,max=105.00}
  | H{max neg=-10.00, max=15.00, add format={precision=0}}|}
  \hline
   10    &   2    & -10 \\
   15.49 &  13.82 &  -5 \\
  100    & 105    &   5 \\
  \hline
\end{tabular}

Code

\documentclass{article}
\usepackage{tikz,array,collcell}
\newcommand*\tikzcellbgset{\pgfqkeys{/tikz/cellbg}}
\tikzcellbgset{
  /pgf/number format/.code=\pgfqkeys{/pgf/number format}{#1},
  node/.style={
    /pgf/number format={/tikz/cellbg/number format},
    node contents={\strut\pgfmathprintnumber{#1}},
    anchor=base, outer sep=+0pt, inner ysep=+0pt, align=right,
    inner xsep=\tabcolsep,
    /utils/exec=%
      \pgfmathprintnumberto{\pgfkeysvalueof{/tikz/cellbg/max}}{\tempa}%
      \pgfmathprintnumberto{\pgfkeysvalueof{/tikz/cellbg/max neg}}{\tempb},
    text width/.expanded={max(width("\tempa"),width("\tempb"))},
    path picture={
      \def\tcbgvo########1{\pgfkeysvalueof{/tikz/cellbg/########1}}% Eugh!
      \pgftransformshift
        {\pgfpointanchor{path picture bounding box}{south west}}
      \pgfsetxvec{%
        \pgfpointdiff
          {\pgfpointanchor{path picture bounding box}{south west}}
          {\pgfpointanchor{path picture bounding box}{south east}}}
      \pgfsetyvec{%
        \pgfpointdiff
          {\pgfpointanchor{path picture bounding box}{south west}}
          {\pgfpointanchor{path picture bounding box}{north west}}}
      \pgfmathifthenelse{#1<0}{"0"}{"1"}
      \ifnum\pgfmathresult=0
        \path[cellbg/fill neg](1,0) rectangle
          ++({-(#1-\tcbgvo{min neg})/(\tcbgvo{max neg}-\tcbgvo{min neg})},1);
      \else
        \path[cellbg/fill pos](0,0) rectangle
          ({(#1-\tcbgvo{min})/(\tcbgvo{max}-\tcbgvo{min})},1);
      \fi
  }},
  max/.initial=100, min/.initial=0,
  max neg/.initial=0, min neg/.initial=0,
  pos color/.code=\colorlet{@tikz@cellbg@pos}{#1},
  neg color/.code=\colorlet{@tikz@cellbg@neg}{#1},
  fill pos/.style={fill=@tikz@cellbg@pos}, pos color=green!25,
  fill neg/.style={fill=@tikz@cellbg@neg}, neg color=red!50,
  number format/.style={fixed zerofill},
  add format/.style={/tikz/cellbg/number format/.append style={#1}}
}
\newcommand{\tikzMe}[1]{\tikz[baseline,cellbg/pic/.try]\node[cellbg/node={#1}];}
\newcolumntype{H}[1]{%
  @{}>{\tikzcellbgset{#1}\collectcell\tikzMe}c<{\endcollectcell}@{}}
\begin{document}
\begin{tabular}{
  | H{} | H{min=2.00,max=105.00}
  | H{max neg=-10.00, max=15.00, add format={precision=0}}|}
  \hline
   10    &   2    & -10 \\
   15.49 &  13.82 &  -5 \\
  100    & 105    &   5 \\
  \hline
\end{tabular}
\end{document}

Output

enter image description here

Qrrbrbirlbel
  • 119,821
  • 3
    This is a great example of how to keep the data section of the table clean by using the array package embed all the logic in the column types. Thanks! – John Leonard Nov 08 '12 at 22:44
  • I've been thinking about how to encode \maxValue on a column-by-column basis. For example, \begin{tabular}{|L|C|R|} might look like: `\begin{tabular}{|L{0}{100}|C{-20}{120}|R{0}{100}|} where 2 arguments (min and max column range) are included on a column-by-column basis. – John Leonard Nov 09 '12 at 12:16
  • I switched my "answered" to your solution. THANKS! This is really, really cool! – John Leonard Nov 09 '12 at 12:42
  • @Qrrbrbirlbel How can column header row be used with text in it...? – Nicholas Hamilton Jun 18 '13 at 10:27
  • @ADP Usually, you can use \multicolumn{1} to escape any column specification (including \collectcell). – Qrrbrbirlbel Jun 18 '13 at 12:47
  • This example does not work for me. I get "! Undefined control sequence. \minWidth ->\widthof {$\ifnegativeValues -\fi 100.00$} l.40 10.0 & 2.00 & 0.00 \" – nobody Dec 29 '16 at 13:43
  • @nobody TikZ brings its own width measuring function that can be used. \widthof can just be replaced with width. However, I've updated the answer to a more user-friendly and expandable approach. – Qrrbrbirlbel Sep 10 '22 at 14:45
30

Another sans-TikZ alterantive:

enter image description here

\documentclass{article}
\usepackage[table]{xcolor}% http://ctan.org/pkg/xcolor
\usepackage[nomessages]{fp}% http://ctan.org/pkg/fp
\newcommand{\maxnum}{100.00}
\newlength{\maxlen}
\newcommand{\databar}[2][green!25]{%
  \settowidth{\maxlen}{\maxnum}%
  \addtolength{\maxlen}{\tabcolsep}%
  \FPeval\result{round(#2/\maxnum:4)}%
  \rlap{\color{green!25}\hspace*{-.5\tabcolsep}\rule[-.05\ht\strutbox]{\result\maxlen}{.95\ht\strutbox}}%
  \makebox[\dimexpr\maxlen-\tabcolsep][r]{#2}%
}
\begin{document}

\begin{tabular}{*{3}{|l}|}
  \hline
  \databar{91.41} & \databar {90.81} & \databar{38.76} \\
  \databar{98.75} & \databar {13.82} & \databar{94.62} \\
  \databar{57.11} & \databar {51.21} & \databar{42.84} \\
  \databar{20.00} & \databar{100.00} & \databar{80.00} \\
  \hline
\end{tabular}
\end{document}

You provide the maximum number (as a macro \maxnum) that is used to calculate the percentage fill. Calculations are performed using fp.

Using this variation of \databar provides a fill that leaves a (barely visible) .5\arrayrulewidth on either side of the table cell. This is just to avoid any kind of artefacts caused by the viewer. The TikZ solution doesn't show this since it draws the coloured boxes in the background (almost as an underlay) of the entire table, so the rules overdraw any overlap:

enter image description here

%...
\newcommand{\databar}[2][green!25]{%
  \settowidth{\maxlen}{\maxnum}%
  \addtolength{\maxlen}{\dimexpr2\tabcolsep-\arrayrulewidth}%
  \FPeval\result{round(#2/\maxnum:4)}%
  \rlap{\color{green!25}\hspace*{\dimexpr-\tabcolsep+.5\arrayrulewidth}\rule[-.05\ht\strutbox]{\result\maxlen}{.95\ht\strutbox}}%
  \makebox[\dimexpr\maxlen-2\tabcolsep+\arrayrulewidth][r]{#2}%
}
%...
Moriambar
  • 11,466
Werner
  • 603,163
  • Can this answer be modified so that the 100% bars stretch the full width of the cell (and others proportionally)? – Sparr Nov 08 '12 at 21:07
  • @Sparr: I've added that to my answer. I've still kept a small (.5\arrayrulewidth) gap between the box and the tabular rules, since a reader may show an "overlap", although this is actually not the case. If you really don't want that, you can remove any reference to \arrayrulewidth in the updated version of \databar. – Werner Nov 08 '12 at 21:19
  • @Werner: It was hard to pick, but I have to go with your non-Tikz option. Thanks!!!! I'm going to play with this base code so that the maximum value in each column is used to scale the histograms. This version hard codes \maxnum to 100, but in my working code I'll need to use \maxnum over all the values in a given column. – John Leonard Nov 08 '12 at 21:20
  • @JohnLeonard: That can be achieved using the array package and specifying a new column type that takes the "maximum value" as an argument. – Werner Nov 08 '12 at 21:22
  • I have adapted this solution to use a custom maximum width as my column header was quite a bit wider than the maximum number; works like a charm :) – O. R. Mapper Dec 19 '14 at 10:51
  • You have "green!25" as the default, but "#1" is not used. Instead, you repeat "green!25", which ignores the parameter. – nobody Dec 29 '16 at 13:11
  • @O. R. Mapper post your solution. – nobody Dec 29 '16 at 13:13
  • I've tried to adapt your code in R, thank you for posting such a great example https://stackoverflow.com/questions/46595296/r-markdown-pdf-partially-color-cell-background-data-bar/47085376#47085376 – Cedric Nov 02 '17 at 22:09
  • And here is another different one https://stackoverflow.com/questions/19623866/creating-r-tables-with-embedded-graphics/47119715#47119715 – Cedric Nov 05 '17 at 08:57
1

A solution with {NiceTabular} of nicematrix.

\documentclass{article}
\usepackage{nicematrix,tikz}
\usetikzlibrary{calc}
\usepackage{collcell}

\begin{document}


\newcolumntype{B}{>{\collectcell\BarValue}c<{\endcollectcell}}


\ExplSyntaxOn


\NewDocumentCommand{\BarValue}{m}
  {
    #1
    \tl_gput_right:Nx \g_nicematrix_code_before_tl
      { 
        __pantigny_bar_value:nnn 
          { \int_use:c { c@iRow } } 
          { \int_use:c { c@jCol } } 
          { \fp_eval:n { #1 / 100 } } 
      }
  }


\cs_new_protected:Nn __pantigny_bar_value:nnn 
  {
    \tikz \fill [blue!15] 
          (#1-|#2) 
          rectangle 
          ( $ ( \int_eval:n { #1 + 1 } -| #2 )
              ! #3 !
              ( \int_eval:n { #1 + 1 } -| \int_eval:n { #2 + 1 } ) $ )
    ; 
  }


\ExplSyntaxOff


\begin{NiceTabular}{BBB}[hvlines,first-col]
A & 40.89 & 80.5 & 38.76 \
B & 90.89 & 70 & 15.76 \
C & 57.11 & 21.21 & 92.84 
\end{NiceTabular}


\end{document}

You need several compilations (because nicematrix uses PGF/Tikz nodes under the hood).

Output of the above code

F. Pantigny
  • 40,250
0

I ultilize TikZ's ability for this: simple and flexible!

enter image description here

\documentclass[tikz,border=5mm]{standalone}
\begin{document}
\newcommand\tab[2]{%
\begin{scope}[shift={(-.5,.5)}]
\fill[green!20] #1 rectangle +(#2/100,-1); 
\draw #1 rectangle +(1,-1);
\end{scope}
\path #1 node{#2};
}% end of tab

\begin{tikzpicture}[yscale=.5,xscale=1.2]
\tab{(0,0)}{40.89}  \tab{(1,0)}{80.5}   \tab{(2,0)}{38.76}
\tab{(0,-1)}{90.89} \tab{(1,-1)}{70}    \tab{(2,-1)}{15.76}
\tab{(0,-2)}{57.11} \tab{(1,-2)}{21.21} \tab{(2,-2)}{92.84}
\end{tikzpicture}

\end{document}
Black Mild
  • 17,569
  • 1
    Interesting solution, but if you have to add a column between two existing columns, you have to manually modify the absolute position of all the columns on the right (or on the left) of the one you're adding. – CarLaTeX Jul 21 '19 at 08:07
  • @CarLaTeX you are right! The beauty come first ^^ I think it's acceptable for small tables – Black Mild Jul 21 '19 at 08:21
  • Yes, for small tables is surely acceptable. – CarLaTeX Jul 21 '19 at 08:26