Conditional formatting modifies the visual appearance of cells based on the values they contain. It can be particularly useful for identifying values that need you attention: very high, very low, outliers etc.
Highlight Cells Rules identify cells that are greater than, less than, between, etc., specified criteria.
Top/Bottom Rules operate in a similar manner to Highlight Cells Rules, but identify cells that are in the top ten, bottom ten, etc., of a range.
Data Bars represent the comparative magnitude of a value in a cell by overlaying a corresponding bar. The result is something like a bar chart. Similar results can be achieved with sparklines (more of which soon!).
Icon Sets work a bit like Highlight Cells Rules, but used sets of icons instead of blocks of colour.
For a quick effect, Color Scales shade cells on a gradient based on their comparative values. The gradient points can be configured in different ways in the settings, allowing you to incorporate elements of the Highlight Cells Rules.
With any colour-based formatting rules, be sure to consider colour-blind users. Choose colours with a strong contrast. The underlying information should still be visible to enable interpretation without relying on colour identification.
The side panel is used to add, remove, or edit your conditional formatting rules:
The rules are applied in the order they appear in the side panel, from bottom to top. This means a rule higher on the list can override one lower down.
Google Sheets does not have conditional format options for icons or data bars. However, similar effects can be achieved in Google Sheets using images and sparklines.
While images can be inserted into an Excel spreadsheet, they exist on a 'canvas' layer and don't really interact with the underlying data (unless you configure the image as a button for a macro). In Google Sheets, however, images can be employed as a cell value and called within a formula.
A reference to the image's cell will bring that image through. So if the image is in cell A1, a formula in B1 of =A1 will render the image in cell B1. However, the image cell has no inherent value, so cannot be counted by COUNTIFs etc.
IMAGE fetches an image from a specified URL. The size-mode argument determines how the image is displayed:
Regardless of which method you use, the images can be used in formulae, such as an IF condition to display a particular image when particular criteria are met. In this way, images can be used like the conditional icon sets in Excel.
Sparklines provide little mini-graphs within a single cell. They're useful for giving a quick visual summary of a range of data.
Data in a spreadsheet can be quickly turned into charts. Several types of chart are supported, with options to control the appearance.
In the chart below, a single series shows the number of apples consumed in the months January-March. Values and labels can both be used in the graph:
Before inserting a chart, ensure your data is laid out appropriately:
Here's a nicely laid-out example:
You need to choose the right sort of chart for your data: the final arbiter is not how pretty it looks, but how effectively it tells the story of your data. In part this will depend on whether your data is categorised or purely numeric.
In particular, you need to be clear about the types of data you are working with. Some graphs plot numerical values for categorised data, whereas some plot two sets of related numerical data.
Categorised data is often plotted using bar, column or pie charts, but related numerical data usually requires a scatter graph.
I think we'd better look at some examples:
The number of fruit items eaten per month:
Fruit are categorically different items. So a bar chart or column chart like this makes sense. We've grouped the columns together by month which makes comparison a bit easier. Maybe we could use stacked bars instead, to give a better indication of the proportions of each fruit in our monthly diet. And maybe our choice of colour could better match the fruit in question (perhaps red for apple, orange for, well, orange, and green for pear?).
The mass of a dog and the number of biscuits eaten:
This traditional scatter plot pits one numeric value against another to see if there's a correlation. There seems to be. Maybe we'd want to join the dots to hammer this home, but a line graph tends to imply a continuum (usually a temporal one) and that's not really the case here — it's just a lot of dogs. Maybe a trend line would help though?
Some data may appear to be categorised, but is better understood as a special case of numeric data. The most common case of this is when a value is plotted over time, be that days of the week, months, or years. In this case, the time/date may need to be recorded in a format that provides a numeric value (as spreadsheets generally does with dates) so as to achieve a linear scale.
In this example, if the days are used as categories, a non-linear scale for the week is generated, but treating the days as dates includes the missing days and provides a linear scale for the horizontal axis:
Which of these is the more appropriate plot for your data? Should Wednesday have had a zero value rather than an implied value of about 50? Would point markers help in that regard? Should this be a line graph at all?
Think about what it is your chart is meant to be conveying. Don't rely on what chart comes 'out of the box'; instead, modify the chart to make it communicative and (perhaps most importantly) honest. A misleading chart is a bad chart.
To create a chart:
There are several tools for modifying the behaviour and appearance of a chart:
It's worth going through the options carefully: there's some really cool stuff in there but it can be hard to find!
To create a chart:
Double-click selecting a chart re-opens the Chart editor panel. When a chart is selected, the ⋮ menu allows other common actions, including moving the chart to its own sheet, downloading the chart, or applying alt text:
There are essentially three ways by which you can paste an Excel chart into Word or PowerPoint:
|Paste option||Description||Size impact (kb)*|
|Embed||A copy of the chart and its underlying data are attached to your file: so your PowerPoint or whatever gets its own little spreadsheet bundled within it. The advantage is that wherever your file goes, the data goes with it. The disadvantage is the toll on filesize: the whole of the source spreadsheet will be embedded, not just the data feeding the chart.||21†|
|Link||The pasted chart gets its data from your original spreadsheet. If you make changes to the data in your spreadsheet they will be reflected in the pasted chart. But if the file loses its connection to your spreadsheet (as it might if you forward it to someone) then the data won't update and can sometimes even disappear altogether.||5|
|Picture||Pastes the chart as a raster image. Any interactivity is lost, and, since the image is of a set resolution, any attempt to make the image bigger will also make it look crumbier – because it's a raster image now.||19‡|
If you are using the "Linked" option, any edits to the linked Excel file will automatically be reflected in the document. But a linked or embedded chart can also be edited from within Word or PowerPoint:
A chart can be inserted into a Doc or Slides deck from Insert > Chart > From Sheets or by simply copying and pasting. Either way, there are two options available:
In a rare example of Microsoft being more dynamic in its linking than Google, changes to linked data are not applied to the linked chart automatically (which can be something or a relief if you're presenting a chart and you don't want it changing mid-talk): instead, an update button appears in the chart, which you can click to apply the changes. The chart also includes controls for unlinking itself from the source file.
Charts from Google Sheets can also be embedded in Google Sites. Rather than raster images, these charts are dynamic and are always linked to the source data.
Charts can be downloaded as images for use elsewhere. For applications that support them, you're probably best saving your charts as scalable vector graphics (SVGs): SVGs are images made up of lines and geometry (rather than pixels) and so are generally well suited for charts (which also tend to be made up of lines and geometry). SVG files have two advantages over raster images: they're usually smaller in terms of file size, and, as the name suggests, they're scalable — so you can resize your chart without loss of quality.
Google Sheets charts can be published to the web, either as a stand-alone web-page, or embedded within an iframe (as you might a YouTube video). Most of the charts on this page are using this feature. Such charts can either be interactive or static images. In either case, the image is updated if the underlying data changes (although this connection can be turned off.
The charts use fixed widths and heights which makes them awkward to use in responsive websites. But the fixed sizes are based on the size of the chart as it appears in the spreadsheet: if you wanted, you could create a number of differently sized charts for use at different screen sizes.
To publish a chart for use online:
Only the chart you specify will be published, but that inevitably includes the data being used to generate the chart. If you are concerned as to the extent of what you're publishing, you could always pull your data to a stand-alone spreadsheet first (perhaps using IMPORTRANGE).
The visualisation options built into Excel and Google Sheets only go so far. But there are plenty of other tools that can be used to create attractive and (more importantly) informative visualisations of your data.
Generally speaking you will need your data to be in a pivotable format. Some tools will let you copy and paste data, or import as Google Sheets or Excel files, but often you will need to save your data as a CSV (comma-separated values) file.
CSV files are pure text files with no real formatting. Cells are generally separated by commas, with new rows indicated by line breaks. They consist of only one sheet of data (not multiple worksheets) and don't include underlying calculations. They are purely a surface record of a single sheet of data in text form.
To save the current sheet, go to:
File > Save As, and choose the filetype CSV (Comma delimited) (*.csv)
File > Download > Comma-separated values (.csv, current sheet)
For data visualisation tools and inspiration, take a look at: