Library Subject Guides
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.
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!
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.
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:
| fx | Formula bar (calculations shown here) | ||||
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Numbers | Text | column header ⮥ | ||
| 2 | 11.25 | ← value formatted as number | |||
| 3 | 2.42% | ← value formatted as % | I'm cell D3 | ||
| 4 | 09/10/1963 | ← value formatted as date | |||
| 5 | £ 9.99 | ← value formatted as currency | I'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.
| fx | |||||
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Spreadsheets project - Expenses record | ||||
| 2 | |||||
| 3 | Item | Date | Colour | Cost | |
| 4 | Large coffee | 09/10/1963 | Brown | 3.25 | |
| 5 | Cheese and pickle sandwich | 10/10/1963 | White, yellow, brown | 4.5 | |
| 6 | Extra large blueberry muffin | 12/10/1963 | Brown, blue | 0.6 | |
| 7 | |||||
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.
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.
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.
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:
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:
Double-clicking the row/column divide will auto-adjust the row/column to fit the height/width of the data it contains.
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.
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).
A cell can contain one of three things:
Text |
|
|---|---|
Number |
|
Formula |
|
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:
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.
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:
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.
Cells or ranges can be moved using Cut/Copy & Paste as with other applications, but spreadsheets behave slightly differently:
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:
In Excel, copied content only remains on the clipboard for as long as the dotted line is displayed.
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.
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.
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.
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 | Thu | Fri | 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 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!).
Auto-fill can also be used with formulae — we'll explore this more later...
Spreadsheet cells contain, in effect, two layers of formatting:
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.
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:
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.
For fuller control, choose Format > Number..., or select from the More formats drop-down on the formatting tools bar.
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:
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.
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).
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 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 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 | ||||
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full price | £109.88 | ||
| 2 | Discount rate | 10% | 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 | |
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 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!
Dates are formatted automatically if entered in a recognisable format. For instance, 1st February 2034 could be entered...
01/02/34
1/2/34
01-02-34
01-Feb-2034
01.02.34 ← not recognised as a date (the 'dot' is used exclusively for decimals)
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 > 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.
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).
Times can be entered in the following ways:
17:16 ← time only (24hr clock)
5:16 PM ← time only (12hr clock)
17:16:20 ← time including seconds
17-16 ← not recognised
17.16 ← not recognised: it's just a decimal number
23/11/1963 17:16 ← date and time together
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).
As spreadsheets always store dates and times as numbers, you can achieve a wide range of display presentation by applying customised formatting codes.
You can then create your own custom date and/or time format with the following elements:
| d | day of the month |
|---|---|
| m | month or minute (depending on context) |
| y | year |
| h | hour (use [h] when expressing duration rather than time) |
| s | seconds |
| AM/PM | add 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-yy | 01-02-34 |
|---|---|
| ddd, d mmm yyyy | Wed, 1 Feb 2034 |
| dd mmmm, h:mm | 01 February, 17:16 |
| dddd d° mmmm, h.mm AM/PM | Wednesday 8° February, 5.16 PM |
Currency can be entered in two different ways:
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:
Home > Number > More Accounting Formats
Format > Number > Custom currency
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').
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.
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:
When a cell contains text that is wider than the cell:
In either case, the text is still stored entirely in one cell.
If you want to make all of the content visible, do one of these:
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.
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:
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.
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.
