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?

1

4 Answers 4

42
+50

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

7
  • 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! Nov 8, 2012 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. Nov 9, 2012 at 12:16
  • I switched my "answered" to your solution. THANKS! This is really, really cool! Nov 9, 2012 at 12:42
  • @Qrrbrbirlbel How can column header row be used with text in it...? Jun 18, 2013 at 10:27
  • @ADP Usually, you can use \multicolumn{1} to escape any column specification (including \collectcell). Jun 18, 2013 at 12:47
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}%
}
%...
9
  • Can this answer be modified so that the 100% bars stretch the full width of the cell (and others proportionally)?
    – Sparr
    Nov 8, 2012 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 8, 2012 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. Nov 8, 2012 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 8, 2012 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 :) Dec 19, 2014 at 10:51
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

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}
3
  • 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, 2019 at 8:07
  • @CarLaTeX you are right! The beauty come first ^^ I think it's acceptable for small tables
    – Black Mild
    Jul 21, 2019 at 8:21
  • Yes, for small tables is surely acceptable.
    – CarLaTeX
    Jul 21, 2019 at 8:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .