Skip to Main Content
University of York Library
Subject Guides

Essential Spreadsheets: a Practical Guide

P1: Spreadsheet basics

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

In this section we familiarise ourselves with the basics of spreadsheets: we'll explore the anatomy of a spreadsheet, we'll start to manage our workspace, and we'll learn how to enter, edit and format data. We'll also summarise some accessibility principles.

Creating & saving

Excel

New Excel spreadsheets are created from Excel's File tab and must be saved using File > Save As…, at which point the location of the file is also chosen. Remember to save changes to your spreadsheet!

Google Sheets

You're best off creating Google Sheets from within Google Drive: first locate the Google Drive folder where you want the Sheet to live, then go to New > Google Sheets. Google Sheets are saved repeatedly and automatically without any action by the user.

Anatomy of a spreadsheet

A spreadsheet workbook may consist of one or more worksheets (arranged in tabs at the bottom left of the workbook): these individual worksheets are made up of a series of cells arranged in a grid.

Here's an example spreadsheet layout:

fxFormula bar (calculations shown here)
ABCDE
1NumbersTextcolumn header ⮥
211.25← value formatted as number
32.42%← value formatted as %I'm cell D3
409/10/1963← value formatted as date
5£ 9.99← value formatted as currencyI'm the active cell!
6 

At the top of the spreadsheet there's the formula bar where the underlying calculations of a cell are displayed; below that is the sheet of cells. A cell's coordinates, based on the column header above that cell, and the row header to its left, make up that cell's cell reference. This cell reference can be used to refer to the contents of that cell in a calculation. The currently selected cell (the active cell) is in column D and on row 5, so is therefore cell D5.

In the above layout there's a discernible table structure from cells A1 to B5. These cells are bound by empty cells to the bottom and right, and a header row has been created by making the first row of this table bold. We'll see later how such things can infer special properties on a table of data.

A group of adjacent cells is called a range. The table occupying cells A1 to B5 could be expressed as a range. A range is defined by its top-left and bottom-right cells, with a colon character between the two: in this case it would be written A1:B5.

Find the cellExercise

fx
ABCDE
1Spreadsheets project - Expenses record
2 
3ItemDateColourCost
4Large coffee09/10/1963Brown3.25
5Cheese and pickle sandwich10/10/1963White, yellow, brown4.5
6Extra large blueberry muffin12/10/1963Brown, blue0.6
7 

What is the cell reference for the date when the large coffee was bought?

C1
B4
A4
D8

Managing the workspace

A new spreadsheet (or new worksheet) will contain a large number of cells of a standard width and height. You will often need to make adjustments to these.

Managing worksheets

To the bottom left of the spreadsheet window are the tabs for any worksheets within the spreadsheet workbook. Right-click selecting these tabs will bring up additional options, and you can add new sheets with the "+" icon.


Excel
Excel worksheet tabs with right-click context menu and 'New worksheet' button
Google Sheets
Google Sheets worksheet tabs with right-click context menu and 'New worksheet' button

Options include the ability to rename a sheet, colour-code its tab, duplicate it (either within the workbook, or in a new workbook), or move it to a different position.

Give your sheets good names: that way you'll have a better idea what's on them, and you'll make it easier for people to navigate your workbook.

Working with rows and columns

There are menu options for working with rows and columns, but right-clicking on a row or column header (the number to the left of the row, or the letter at the top of the column) will bring up a context menu containing a range of useful options:

Excel
Excel row options - the context menu from right-click selecting a row header
Google Sheets
Google Sheets row options - the context menu from right-click selecting a row header

You can insert multiple rows or columns at once by first selecting a number of rows or columns (equal to the amount you're wanting to add) and then right-clicking and selecting Insert.

Row height and column width can be adjusted from the context menus too. Alternatively, you can drag the row/column boundary to resize manually:

The pointer becomes a double-headed arrow when you're over a row/column boundary

Tip

Double-clicking the row/column divide will auto-adjust the row/column to fit the height/width of the data it contains.

Text wrap occurring in a cell: text is spread over multiple lines within the cell

As well as adjusting column widths and row heights to accommodate longer content in a cell, you can also enable text wrapping for a cell. This will automatically adapt the height of a row to fit the content of cells within it.

Google Sheets 'Text wrapping' options are on the icon menu

In Excel, the text wrapping button is on the menu ribbon at Home > Alignment. In Google Sheets it can be found on the Format menu, or via the icon menu (left).

Entering and editing data

A cell can contain one of three things:

Text

  • Left-aligned by default
  • The formula bar displays actual content
  • Content preceded by a single quote (') is always regarded as text

Number

  • Right-aligned by default
  • Can be displayed as different formats including dates, times, percentages and currency
  • Leading zeroes are removed and rounding may be applied

Formula

  • Aligned according to the type of result (text or number)
  • The cell displays the result of the formula, while the formula bar shows the formula itself
  • Formulae are preceded by an equals sign (=)

Data entry

When constructing or editing a spreadsheet you will need to be able to navigate between cells and enter content. It’s mostly intuitive, but there are some useful points to be aware of:

  • Click or use the navigation (arrow) keys to move to a specific cell
  • Typing in a cell overwrites existing content if it is not blank
  • To edit a cell that already contains data, double-click or select it and edit the content in the formula bar
  • Once data has been entered or edited, you must press Enter or Tab. At this point the spreadsheet will:
    • Check your data makes sense (and report errors)
    • Decide how numerical values should be formatted
    • Update all calculated values
    Resist the urge to click in another cell or use the cursor keys as they have a different effect when editing cell content.
  • Pressing the Escape key (Esc) at any point during data entry will abort the process and escape the formula without saving any changes you've made
  • Pressing the Delete key (Del) will clear the contents of a cell, though any formatting will remain in place
Tip

Pressing Enter after data entry moves to the next cell down.
Pressing Tab will move to the right.
Holding Shift when pressing either Enter or Tab will move the cursor in the opposite direction.

Selecting cells

Actions that can be applied to several cells (eg formatting changes or clearing content) require you to select a range of cells. Use whichever method is most appropriate:

Rectangular block of cells

  • Drag from top left to bottom right of area to be selected
  • Select the first cell; hold down Shift and select the opposite corner
  • Select the first cell; hold down Shift and use the cursor keys to extend the selection

Several non-continuous blocks of cells

  • Select the first block, then hold down Ctrl as you drag over another non-adjacent cell / block of cells

Whole row or column

  • Click on the row number/column letter

Several continuous rows or columns

  • Drag over the row numbers/column letters

Several non-continuous rows and/or columns

  • Select the first column or row, then hold down Ctrl as you select further rows/columns

Whole worksheet

  • Choose the ‘button’ at the very top left of the worksheet (above the row numbers and to the left of column letters)
  • Press Ctrl and A together (Ctrl-A) twice so that all cells are selected
Tip

Pressing Ctrl-A once will select the current continuous block of cells. The selection will stop at any continuous selection of empty cells in your sheet.

Copying and pasting

Cells or ranges can be moved using Cut/Copy & Paste as with other applications, but spreadsheets behave slightly differently:

A copied selection

After choosing either cut or copy, the selected range is surrounded by a dotted line - cut data stays where it is until pasted elsewhere.

When pasting a range, select only the top left cell of the new area before choosing paste:

A pasted selection

In Excel, copied content only remains on the clipboard for as long as the dotted line is displayed.

Dragging selected data

Selecting the cell border allows you to drag

Cells or ranges can be dragged to a different location. Positioning the cursor at the cell border will change to the 'move' cursor (crosshair arrows in Excel; a hand in Google Sheets) at which point the cell or range can be dragged.

Any references to the cell or range will be updated to the new location.

Auto-complete & auto-fill

Auto-complete

After typing 'Ap', the auto-completion 'Apples' is offered

When entering text, auto-complete may suggest using other text already present higher up the column. Press Enter to accept the suggestion; continue typing or hit backspace to reject it.


Auto-fill

Dragging the fill handle extends the contents into neighbouring cells

A series of values (e.g. 1 to 10, Mon-Fri) can be entered quickly using auto-fill.

Auto-fill is carried out using the fill handle: a little box at the bottom-right-hand-corner of the selected cell.

To use auto-fill, enter the start of the sequence, then drag the fill handle in the direction you wish to fill. The fill handle is quite small, but your pointer will become a fine crosshair when it's directly over it.

As you drag, the sequence will be continued until the point you release the fill handle.

Tip

You can also double-click the fill handle to auto-fill down to the same length as a left-neighbouring column. This will save you a lot of time when working against large data-sets.

Here's some examples of what data needs to be entered and selected in order to produce a particular series using auto-fill:

Numbers Days / Months Other sequences
Repeat Count Up Count Up in 10s Count Down Years Leap Years Days Every Other Day Mon, Wed, Fri Letters The Beatles
1 1 10 50 2012 2012 Mon Mon Mon A John
1 2 20 49 2013 2016 Tue Wed Wed B Paul
1 3 30 48 2014 2020 Wed Fri Fri C George
1 4 40 47 2015 2024 Thu Sun Mon D Ringo
1 5 50 46 2016 2028 Fri Tue Wed E John
1 6 60 45 2017 2032 Sat ThuFri A Paul
1 7 70 44 2018 2036 Sun Sat Mon B George
1 8 80 43 2019 2040 Mon Mon Wed C Ringo
1 9 90 42 2020 2044 Tue Wed Fri D John
1 10 100 41 2021 2048 Wed Fri Mon E Paul
1 11 110 40 2022 2052 Thu Sun Wed A George

Excel's auto-fill options toggle lets you fine-tune the auto-fill, for instance by only filling in weekdays and skipping weekends

Excel has some additional Fill tools: an auto-fill tab may appear when auto-filling, providing fine-tuning options where appropriate (right); and there's Home > Editing > Fill which lets you auto-fill content through a selected range of cells (and in different directions!).

Tip

Auto-fill can also be used with formulae — we'll explore this more later...

Number formats & text styles

Spreadsheet cells contain, in effect, two layers of formatting:

  • style (e.g. font, colour, border style, background colour)
  • number format

Decimals, dates, percentages, and currency are all treated as numbers, and for each one there are various options. For example, currency includes a symbol; dates can be shown in different ways, e.g.01/01/2000 or 01‑Jan‑2000.

When you enter or calculate values, the spreadsheet will try to detect the number format required, but you can change it if you wish.

Applying number formats

In Excel: Home > Number; In Google Sheets: Format > Number

There are several buttons to control number format directly. These will apply that format to the select cell or range.

Both Excel and Google Sheets include buttons for:

  • Currency
  • Percentage
  • Increase / decrease decimal places
  • Other formats

Excel

For fuller control, right-click on the selected cell(s), choose Format Cells... and select an appropriate format from the Number tab.You can also get to these options from the Number Format "dialogue box launcher" on the ribbon.

Google Sheets

For fuller control, choose Format > Number, or select from the More formats drop-down on the formatting tools bar.


Tip

Number formats remain in a cell when clearing values. This can lead to confusion when entering data — if a cell previously contained a date, entering the number 4 will produce the result 04/01/1900.
This is easily fixed by re-formatting the cell(s) (choose General if no special format is required).


Here's some useful information about numbers in spreadsheets:


Decimal places

Numbers are stored internally to 15 digits (e.g. 3.14159265358979) and the full value is used in calculations. The value displayed, however, will depend on the formatting chosen, and the usual rules of rounding will be applied.

Leading zeroes

Numbers are displayed without leading zeroes, even if entered that way. This can cause a problem with data such as phone numbers. However, phone numbers can safely be stored as text (since they are not used in calculations). To force a spreadsheet to treat cell content as text, enter a single quote (') first, e.g. '018118055 (the quote doesn’t display).

Separators

Large numbers can be easier to read if separators are used to group the thousands (e.g. 1,000,000 instead of 1000000). In Google sheets, commas are included in the standard "Number" format, while in Excel there's a Use 1000 Separator (,) checkbox in the "Format Cells" dialogue box mentioned above.

Be aware that some countries use the comma as a decimal separator; try not to confuse people!

Other formats

Other options are available, including scientific notation and fractions. Dates, times, percentages, and currency are numbers with special formatting applied.

######

If a number is too wide for the column it is shown as a series of hashes, since truncating the value could be very misleading!

Percentages

Percentages are recognised as such when entered with the % symbol (e.g. 10%). Percentages entered this way are stored in their decimal form (in this case 0.1) but displayed as a percentage:

fx
ABCD
1 Full price£109.88
2 Discount rate10%0.1← Using % format, cell B2 contains 0.1 but displays as 10%
3 Discount amount£10.99=B1*B2← Cell B3 correctly calculates 10% of the price
4 Discounted price£98.89=B1-B3

Tip

Avoid expressing a percentage as a 'normal' number, e.g. writing 10 rather than 10%. A percentage is a fractional value (a hundredth) so always enter percentages with % or format decimal values as %. Don't go multiplying and dividing things by 100 — you don't need to do that in spreadsheets.

Dates & times

Dates are stored as whole numbers – the number increases by 1 every day.

Times are stored as decimal values (where ‘0.5’ is midday).

So 17:16:20 on 23rd November 1963 is 23338.7196759259 to a spreadsheet. But the spreadsheet can then display this number in a range of different ways using special formatting.

The fact that dates and times are stored as numbers makes calculating with them a lot easier!


Entering dates

Dates are formatted automatically if entered in a recognisable format. For instance, 1st February 2034 could be entered...

Tick

01/02/34

Tick

1/2/34

Tick

01-02-34

Tick

01-Feb-2034

Cross

01.02.34  ← not recognised as a date (the 'dot' is used exclusively for decimals)


Tip

Localisation settings affect dates. For instance, if your spreadsheet is set to the US locale, 01-02-34 is interpreted as 2nd January 2034. It is very important you know which date format is being used!

In Google Sheets you can check your locale in File > Spreadsheet Settings.... The default value is set from your Google account. Excel uses the region settings in your operating system. You can test the locale settings in Excel by entering the date 02/01 (without a year) and seeing how it renders it.


Entering historical dates

Excel can only really cope with dates after 1st March 1900. It gets dates from January and February 1900 a day out owing to a programming error which included the non-existent 29th February 1900, and it can't handle dates from before 1900 at all, which is a problem when working with historical data.

Google Sheets doesn't have the 1900 leap-year error, and can work easily with any dates from 1st January 100 onwards. With calculations it can even reach as far back as 1st January 1 BCE (styled 00-1), beyond which it gives up. Google Sheets is therefore a potentially more useful tool for historians.

When entering a year from a previous century, be sure to enter all the digits, otherwise it will resolve to the 21st century (hence why Google Sheets can only accept dates after 1st January 100).


Entering times

Times can be entered in the following ways:

Tick

17:16  ← time only (24hr clock)

Tick

5:16 PM  ← time only (12hr clock)

Tick

17:16:20  ← time including seconds

Cross

17-16  ← not recognised

Cross

17.16  ← not recognised: it's just a decimal number

Tick

23/11/1963 17:16  ← date and time together


Tip

17:16 means just gone quarter past five in the afternoon, not 17 hours and 16 minutes.
Don't confuse time of the day with elapsed duration (which we'll look at later).


Custom date and time formatting

As spreadsheets always store dates and times as numbers, you can achieve a wide range of display presentation by applying customised formatting codes.

Excel
Custom formatting options in the Format Cells dialogue

  1. Select the cells you need to format
  2. Right-click and select Format cells..., or go to Home > Number > Number format (dialogue toggle)
  3. Select "Custom" from the Category list
Google Sheets
The Custom number formats dialogue

  1. Select the cells you need to format
  2. Go to Format > Number > More Formats > Custom number format

You can then create your own custom date and/or time format with the following elements:

dday of the month
mmonth or minute (depending on context)
yyear
hhour (use [h] when expressing duration rather than time)
sseconds
AM/PMadd this after a time to specify 12-hour clock with AM or PM

Repeating a letter code generates longer formats, and separators may be included. Below are some examples and the result they produce for 01/02/2034 at 17:16:20...

dd-mm-yy01-02-34
ddd, d mmm yyyyWed, 1 Feb 2034
dd mmmm, h:mm01 February, 17:16
dddd d° mmmm, h.mm AM/PMWednesday 8° February, 5.16 PM

Currency

Currency can be entered in two different ways:

  • Enter the amount, including the currency symbol, e.g. £1.50. This will be recognised as currency and formatted appropriately;
  • Enter the value only and then select the currency format.

Other currency symbols are recognised too. Users of UK keyboards with both $ and € on the 4 key can enter € by pressing Alt Gr + 4 (Alt Gr is to the right of the space bar). Further currency symbols are available from:

Excel

Home > Number > More Accounting Formats

Google Sheets

Format > Number > More Formats > More currencies


Tip

Spreadsheets aren't clever enough to automatically convert currency values using number formatting — the currency sign is basically just decoration. So £1.50 + €1.50 will give a result of 3.00. If you did want to work with exchange rates, you could try the GOOGLEFINANCE function...


There are two currency-formatting styles: Currency, and Accounting (or Financial) which displays negative values in brackets (along with other subtle differences to 'Currency').

Text style formatting

Standard text formatting tools are available as per other Office / Google Suite applications: different fonts, colours, sizes, and styles (e.g. bold, italic) can be used to change the appearance of cell content.

Tip

Never encode meaning into your data purely with colour: it might make sense to you to flag certain rows red, but it won't mean as much to everybody else. Add a new column for your flagging — you could always use conditional formatting to colour in the row based on a text flag.

As well as font and colour, the position of content within a cell (alignment) can be modified. Text content can also be ‘wrapped’ within a cell:

Excel
Excel row alignment options: Home > Alignment
Google Sheets
Google Sheets alignment options are also at Format > Align

When a cell contains text that is wider than the cell:

  • if the adjacent cell is empty, the text will overflow over the next cell
  • if there is no adjacent space, overflowing text will not be shown but will still be stored in the cell

In either case, the text is still stored entirely in one cell.

The contents of both A3 and A4 are wider than column A. In the case of A3, only part of the content is visible as there is a value in B3. In the case of A4, all content can be seen because B4 is empty and therefore A4 can spill over it.

If you want to make all of the content visible, do one of these:

  • resize the column width
  • wrap text for a better fit and increase the row height
  • Adjust the orientation of the content using the text rotation options.

Tip

The appearance of a cell can also be made dependent on the number or text it contains.
This is called conditional formatting and we'll deal with it later.

Accessibility principles

By their very nature, spreadsheets aren't especially accessible: big lists of data full of long numbers are nobody's friends at the best of times, and table structures are bad enough to get your head around when you can see them, let alone if you're having them read to you by a screen reader. However, there are certain principles that can help make spreadsheets as accessible as possible:

  • Use list structure principles for laying out your data — in particular, a bold header row with no merged cells or missing values;
  • Use the alt text — be it images, charts, or other objects, be sure to give them an appropriate description;
  • Consider your use of colours — never encode meaning solely through cell colour, and never assume that your conditional formatting will be as helpful to others as it might be to you;
  • Give your sheets helpful names so that people can navigate to the right sheet without having to read everything else that's in there.
Exercises
Google Sheets

Google Sheets versions of the files are listed below. In each case you will be prompted to make your own copy of the spreadsheet files, for which you'll need to be signed into a Google account.



Excel

You can download the above Google Sheets for use in Excel by going to File > Download

Excel versions of the exercise files can also be found on university-managed machines at T:\IT Training\Essential Spreadsheets

You can map to that drive at home if you use the University's VPN.