Conditional formatting in LibreOffice spreadsheets
Sparklines
Integrate graphical information alongside the data it represents with conditional formatting and sparklines.
Spreadsheets are all about detailed data and its manipulation. Sometimes, though, you need a summary for gathering general impressions and discerning trends. That is where conditional formatting becomes useful in LibreOffice Calc – as a graphical summary for interpreting general trends in data at a glance, rather than studying the figures closely.
Contrary to first impressions, Conditional Formatting in Calc shares only part of its name with Conditional Fields or Conditional Styles in Writer. All the three features have in common is that their appearance changes depending on context. In the case of conditional formatting, for instance, the format changes when the content of the involved cells changes.
Conditional formatting is an extension of the idea of sparklines, a concept named and popularized by data visualization theorist Edward Tufte, who devoted 20 pages to the concept in his book, Beautiful Evidence [1]. Sparklines are small line graphs that fit on a line of text or in a single spreadsheet cell. They contain no figures, but from their shape, you can see the general trends in the data they map.
According to Tufte, the advantages of sparklines [2] are that they:
… vastly increase the amount of data within our eyespan and intensify statistical graphics up to the everyday routine capabilities of the human eye-brain system for reasoning about visual evidence, seeing distinctions, and making comparisons. And data graphics are no longer a special occasion in a separate place with a frame on some slide with a label "Fig. 17-B" …. Providing a straightforward and contextual look at intense evidence, sparkline graphics give us some chance to be approximately right rather than exactly wrong.
Excel supports sparklines, but Calc does not, although some proof-of-concept macros were circulating a few years ago. However, conditional formatting offers several alternatives to sparklines that provide the same visual advantages while adding some complexity to the basic idea.
How Conditional Formatting Works
Conditional formatting in Calc automatically changes the appearance of selected cells. The automatic format can be the application of a cell style or of an indicator that visually presents data in much the same way as a graph or a chart.
To use Calc's conditional formatting, select the cells to work with, then click Format | Conditional Formatting | Condition, and select an item from the submenu. All types of conditional formatting can be configured from Condition. The Color Scale and Data Bar menu items are shortcuts to options available under Condition. If necessary, you can edit the range of cells affected at the bottom of the Conditions window (Figure 1).
The Conditions pane has three fields. From left to right, they are:
- The general condition.
- The filter to refine the condition.
- The numerical value that must be present to activate the conditional formatting.
Below these three fields, you have the option to select a cell style.
By changing the general condition, you can choose from the following types of conditional formatting:
- Cell value is: Applies the selected cell style when the numerical value is met. This type of formatting is useful for emphasizing target values in a range of cells. It cannot be used for cells formatted for text.
- Formula is: Applies the selected cell style to cells in which the designated formula is used. The formula is typically one that viewers of the sheet want to find easily.
- Date is: Applies the selected cell style to cells in which the designated filter is used, from Today to Last Week. This type is especially useful for locating recent information.
- All Cells | Color Scale (2 entries): Creates a gradient of two colors. The fields refer not to formulas, but to target values. The color scale is especially useful for showing high and low values in a range of cells at a glance (Figure 2).
- All Cells | Color Scale (3 entries): Like a color scale for two entries, except that a third target value is added, often a midpoint using the value Percent.
- All Cells | Data Bar: A gradient that creates a graph-like representation, typically showing how far a cell value is above or below a designated norm (Figure 3).
- All Cells | Icon Set: Adds a set of icons to summarize the contents of cells (Figure 4). Available icons include arrows, flags, checkmarks, bar graphs, emoticons, and quartered circles. Each icon set has three or four icons, with each depicting a different state. For example, traffic light icons or emoticons might designate if the results were above, below, or equal to projections.
Once you have set conditional formatting, you can select Manage from the submenu to see a summary of the instances of Conditional Formats and Edit the selections (Figure 5). You might also want to add a caption to the formatted cells to explain what is being displayed.
Adding a Sparkline
If you want to use a sparkline, you can create one manually with the following procedure:
- Highlight the range of cells for the sparkline.
- Select Insert | Object | Chart.
- Create a Line graph. Use the Lines Only subtype or, if you want to emphasize the points on the graph, the Points and Lines subtype.
- Remove each element of the chart except the line by deleting it or setting it to the same color as the background (usually white).
- Select the chart and drag it while pressing the Shift key, so it changes size without distortion (Figure 6).
Position the line graph by the range of cells it represents.
Figure 6: You can edit a chart, then shrink it down to create a sparkline.
The sparkline will update if you click it or save and reopen the spreadsheet after changing one or more figures in the range of cells.
New Dimensions to Spreadsheets
Both conditional formatting and sparklines are relatively new concepts. So far as anyone can tell, they appear to be less than 20 years old. As the workaround for sparklines in Calc indicates, they are extensions of charts and graphs, except at a higher level and with less detail. Also, unlike charts and graphs, they are integrated with the data they represent.
Think of them as the graphical equivalent of captions – an annotation that helps you make sense of your data. Even if you do not use sparklines or conditional formatting when you first design a spreadsheet, you might decide to add them later, especially in a spreadsheet that has a long, active lifetime.
Infos
- Tufte, Edward. Beautiful Evidence. Graphics Pr, 2006, http://www.edwardtufte.com/tufte/books_be
- Sparklines: http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001OR
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Fedora 41 Released with New Features
If you're a Fedora fan or just looking for a Linux distribution to help you migrate from Windows, Fedora 41 might be just the ticket.
-
AlmaLinux OS Kitten 10 Gives Power Users a Sneak Preview
If you're looking to kick the tires of AlmaLinux's upstream version, the developers have a purrfect solution.
-
Gnome 47.1 Released with a Few Fixes
The latest release of the Gnome desktop is all about fixing a few nagging issues and not about bringing new features into the mix.
-
System76 Unveils an Ampere-Powered Thelio Desktop
If you're looking for a new desktop system for developing autonomous driving and software-defined vehicle solutions. System76 has you covered.
-
VirtualBox 7.1.4 Includes Initial Support for Linux kernel 6.12
The latest version of VirtualBox has arrived and it not only adds initial support for kernel 6.12 but another feature that will make using the virtual machine tool much easier.
-
New Slimbook EVO with Raw AMD Ryzen Power
If you're looking for serious power in a 14" ultrabook that is powered by Linux, Slimbook has just the thing for you.
-
The Gnome Foundation Struggling to Stay Afloat
The foundation behind the Gnome desktop environment is having to go through some serious belt-tightening due to continued financial problems.
-
Thousands of Linux Servers Infected with Stealth Malware Since 2021
Perfctl is capable of remaining undetected, which makes it dangerous and hard to mitigate.
-
Halcyon Creates Anti-Ransomware Protection for Linux
As more Linux systems are targeted by ransomware, Halcyon is stepping up its protection.
-
Valve and Arch Linux Announce Collaboration
Valve and Arch have come together for two projects that will have a serious impact on the Linux distribution.