1. Select the range of cells to be formatted.
2. Choose Home > Styles > Conditional Formatting. You are presented with a range of rule options (see below).
3. Select the appropriate rule and specify the condition for formatting the chosen range. Try to use cell references rather than values in a condition.

4. When done, choose OK.

Rule options

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.

Managing multiple conditional formats

1. Select the range and choose Home > Styles > Conditional Formatting > Manage Rules… to open the Rules Manager dialogue box.

2. If no rules are shown, check the option in the Show formatting rules for dropdown.
3. From the Rules Manager you can add, edit and delete rules, and change the order in which they are applied. Hit OK when you're done.

1. Select the range of cells to be formatted and choose Format > Conditional formatting to open the Conditional formatting rules side panel;
2. Configure the rule and format in the side panel;
3. Select Done when complete.

Managing multiple rules

The side panel is used to add, remove, or edit your conditional formatting rules:

1. First select the cells containing the rules you want to change, then open the side panel as above.
2. Choose a rule to edit it, or add/delete rules using the appropriate controls.

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.

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:

• Don't leave empty rows/columns in your data if avoidable
• Include axis labels, but enter them in just one cell for each row/column

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:

Categorised data

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?).

Numeric data

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?

Implied numeric values

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:

1. Select the range of cells from which you wish to create your chart, including any labels that are required.
2. Go to Insert > Charts and choose the chart type you require — Excel will create a basic chart from your data.
3. You can fine-tune your chart by selecting various parts of it and using the right-click context menu, side-panels, and extra ribbon menus available. You can also move the chart, resize it, or even put it on its own tab (accessed from Chart Design > Location > Move Chart).
4. Be sure to give your chart some suitably descriptive alt text by selecting it and then going to Format > Accessibility > Alt Text.

Customising Excel charts

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:

1. Select the range of cells you wish to create a chart from, including any labels that are required.
2. Choose Insert > Chart, or select the Insert chart button on the toolbar. A ‘provisional’ Chart will appear, with the Chart editor panel on the right.
3. Use the controls on the panel to configure the chart as required: the Setup tab lets you choose how to use the data, and the Customise tab controls the appearance.
4. The chart will initially appear within the current spreadsheet. You can reposition and resize it, and the chart can be moved into its own tab from the 'kebab' menu (⋮) menu.
5. From this menu you can also give your chart some suitably descriptive alt text.

Customising charts in Google Sheets

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:

Excel charts in other Office applications

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‡

∗ - Based on the 16-cell fruit column-chart from earlier.
† - Expect the size of your source spreadsheet to be added to the size of your destination file.
‡ - More complicated charts will tend to have larger file-sizes.

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:

1. Select the chart, right-click and choose Edit Data (or go to Chart Design > Data > Edit Data) — there are sub-menu options to Edit Data using a mini-window in the document or to Edit Data in Excel;


2. After making changes, simply close the spreadsheet window; with linked charts you can continue to work with both open.

Google Sheets charts in other Google Apps

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:

• Link to spreadsheet – inserts the chart as a raster image, but one which can be updated if the original data changes.
• Paste unlinked – inserts the chart as a stand-alone raster image with no link to the original data.

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.

1. In "Design" mode on 'New Sites', from the Insert tab choose Charts
2. Locate the Sheets file that contains the chart you want, select it, and choose INSERT
3. In the "Select a chart" dialogue, select the chart you want and choose ADD


4. The dynamic elements of the chart aren't visible in the "Design" mode — you'll need to Preview your site to see them properly.

Going beyond the native applications

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.

There's no save option on an Excel chart in Excel, but there is on a chart in PowerPoint!

1. Paste your chart into PowerPoint, bring up the right-click menu (make sure you're selecting the chart as a whole), and choose Save as Picture... — this will bring up the standard "Save As" dialogue;
2. The Save as type dropdown lists the available formats you can choose from, including PNG (a raster image but with any transparency preserved), and the aforementioned SVG.

1. From the three-dot menu (⋮) on the chart, choose Download and select the type of file you want:
   • PNG - a raster image, but any transparent parts of your chart will remain transparent;
   • PDF - a print-format document — the chart elements will be scalable and extractable in a program like CorelDRAW;
   • SVG - the aforementioned scalable vector graphic.
2. Go and play with your downloaded image!

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:

1. Go to File > Publish to the web to open the publish dialogue;


2. Choose between Link (for a simple web link) or Embed (for an iframe);
3. From the dropdown, pick the chart you wish to publish. Charts are indented in the list. If your chart doesn't have a title, it may appear as an empty value in the list;
4. In the adjacent dropdown, choose between Interactive or Image;
5. At the bottom of the page, select how widely you wish to publish, and whether the chart should automatically update;
6. Think carefully about whether you should be publishing this to the web;
7. If you're ready to publish, hit Publish.

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).