Skip to Main Content
University of York Library
Subject Guides

Essential Spreadsheets: a Practical Guide

X1: Visualising data

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

Data is just numbers and text on a screen. To help you visualise what those numbers mean, there are a number of features built into spreadsheets, including conditional formatting, images, sparklines, and charts. We also look at how to get your data into other visualisation tools.

Conditional formatting

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.

ExcelConditional formatting in Excel

  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. Setting a Greater Than conditional format: referencing the value in a cell (e.g. G6) rather than a hardcoded number will save you pain should you want to tweak the rule
  5. When done, choose OK.

Rule options


With Highlight Cells Rules, cells matching the specified criteria are coloured in in the specified way

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.

Icon Sets and Data Bars can be combined for extra gaudiness
Icon Sets work in a similar way to Highlight Cells Rules, but with icons instead of colouring
With Data Bars, bars corresponding to the magnitude of the value are overlayed over the cells

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.


Tip

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. Rules are applied in the order in which they are listed in the Rules Manager
  3. If no rules are shown, check the option in the Show formatting rules for dropdown.
  4. 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.

Google SheetsConditional formatting in Google Sheets

In the Conditional format rules side panel you can choose between a single colour or a gradient scale, and apply the conditions you require.
  1. Select the range of cells to be formatted and choose Format > Conditional format 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 Conditional formal rules side-panel lets you select a rule to be edited, delete a rule, or add a new rule

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.


Tip

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.


Images

Google Sheets

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.

Google Sheets'Image in cell' method

  1. Select the cell where you want the image
  2. Go to Insert > Image > Image in cell — a file picker dialogue will open;
  3. Use the dialogue to select the image you want, and hit INSERT — the image will be inserted into the cell, and resized so that it fits the cell at its original aspect ratio;
  4. Resizing the cell will resize the image accordingly.

Tip

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.


Google SheetsThe IMAGE function

=IMAGE(URL,[size-mode],[height],[width])

IMAGE fetches an image from a specified URL. The size-mode argument determines how the image is displayed:

  1. — resizes the image to fit the cell, maintaining the aspect ratio (as per the Image in cell approach)
  2. — stretches the image to fit the height and width of the cell
  3. — retains the original pixel size of the image, but crops to the confines of the cell
  4. — allows the specification of height and width but otherwise works as 3

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

Sparklines provide little mini-graphs within a single cell. They're useful for giving a quick visual summary of a range of data.

Sparklines as line plots for ranges of monthly data, with hight and low points indicated

Excel
  1. Select the cell(s) where you want the sparkline(s), and go to Insert > Sparklines where you can choose from three chart styles: Line, Column orWin/Loss — the Create Sparklines dialogue will launch.
  2. Select the Data Range for the sparkline(s): if you selected just one cell, select the range to chart in that cell; if you selected a range, select a corresponding range of data.
  3. Hit OK — the sparkine(s) will be created. You can modify them from the Sparkline context ribbon.
Google Sheets

Google Sheets generates sparklines from a function:

=SPARKLINE(data,[options])

...where data is the data range being plotted, and options is an optional array of configuration settings for chart type, axis settings and styling. Theses settings are quite involved, but Google's SPARKLINE help page gives a good summary.

Because Google Sheets uses a function for its sparklines, you can build in conditions to create progress bars and Gantt charts.

Charts

Data in a spreadsheet can be quickly turned into charts. Several types of chart are supported, with options to control the appearance.

From spreadsheet to chart

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:

Column A contains month names. These become x-axis labels. Column B contains the header 'Apples' and values for each month. The header becomes the Series 1 label, and the values are plotted as Series 1 against a y-axis derived from the values therein

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:

fx
ABCD
1 ApplesOrangesPears
2 Jan26820
3 Feb311215
4 Mar28912
5 Apr191423

Effective charts

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?

fx
AB
1 DaySpent (£)
2 Mon45
3 Tue67
4 Thu34
5 Fri78
6 Sun38

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.

ExcelCreating charts in Excel

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:

Right-click context menu, context ribbons, and side-panel

It's worth going through the options carefully: there's some really cool stuff in there but it can be hard to find!

Google SheetsCreating charts in Google Sheets

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 three dots ( ) menu.
  5. From the three dots 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:

The three dot menu is in the top right of the chart

Using your charts in other places

Excel

Excel charts in other Office applications

There are essentially three ways by which you can paste an Excel chart into Word or PowerPoint:

Paste optionDescriptionSize impact (kb)*
EmbedA 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
LinkThe 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
PicturePastes 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.

Options for pasting appear on the right-click context menu, or from a dropdown that appears at the bottom right-hand corner of an item immediately after it's been pasted

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.

In the Import chart dialogue, the option for preserving the link to the spreadsheet is a checkbox at the bottom-right
In the Paste chart dialogue, the options are presented as radio buttons
Google Sheets

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.

Options for updating and unlinking are in the top right of the linked chart. Update only appears when the source data is changed

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.


Excel

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.
Google Sheets
  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

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. Previously published items are also listed here and can can be un-published as needed

  3. Choose between Link (for a simple web link) or Embed (for an iframe);
  4. 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;
  5. In the adjacent dropdown, choose between Interactive or Image;
  6. At the bottom of the page, select how widely you wish to publish, and whether the chart should automatically update;
  7. Think carefully about whether you should be publishing this to the web;
  8. 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).

Using your data with other visualisation tools

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.

Preparing 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:

Excel

File > Save As, and choose the filetype CSV (Comma delimited) (*.csv)

Google Sheets

File > Download > Comma-separated values (.csv, current sheet)


For data visualisation tools and inspiration, take a look at: