Excel Spreadsheet Calculations 46134 | Excel Tutorial Version 7

Partial file snippet.

Sheet 1: Readme

	
	
		Earth, Environmental and Geographic Sciences Excel Tutorial
		

		

		

		

		

		

		

	
	
		Author Dr. Craig Nichol UBC Okanagan Creative Commons 4.0 BY NC SA
		

		

		

		

		

		

		Version 7  July 7, 2021 for Earth Educators Rendezvous
	
	
		Please send any comments, edits, or suggestions for content to include to: craig.nichol@ubc.ca
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Welcome
		

		

		

		

		

		

		

	
	
		This Excel Workbook has been created to provide tutorials on how to use spreadsheet software to conduct basic data storage, calculations and graphing.
		

		

		

		

		

		

		

	
	
		These tutorials are not intended to be all done at once, but to be done on an as-needed basis.
		

		

		

		

		

		

		

	
	
		This workbook was originally created to accompany various courses in the Earth and Environmental Sciences program at UBC Okanagan.
		

		

		

		

		

		

		

	
	
		The content and the division of content into the tutorials was designed to match the needs of different courses.
		

		

		

		

		

		

		

	
	
		This tutorial has also been shared with anyone who is interested in learning more about Excel.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Those who have encountered this tutorial in another way - welcome.  Please feel free to rework and modify this tutorial to suit your needs, or to share the tutorial with others. 
		

		

		

		

		

		

		

	
	
		Please send an email to craig.nichol@ubc.ca indicating where you are sharing it and why so that I can improve the tutorial to suit how it is being used. 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		General Instructions
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Menu options or keyboard commands are contained within <> marks.  
		

		

		

		

		

		

		

	
	
		For example, <doubleclick> indicates that the mouse button should be double clicked.
		

		

		

		

		

		

		

	
	
		<Tools> <Options> would mean that on the upper menu you should use the left mouse button to click on the Tools menu,
		

		

		

		

		

		

		

	
	
		and then select the Options sub-menu
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Cell B23 is highlighted in green.  In this spreadsheet tutorial, green or white cells are used to indicate places where you should be entering data.  
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Cell B26 is highlighted in red.  In this spreadsheet tutorial, red cells are cells in which you should not change the values otherwise the tutorial may not function.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		A large space will be shown to separate the text you should write.
		

		

		

		

		

		

		

	
	
		If this includes single quotes ' or double quotes “ these characters should be entered
		

		

		

		

		

		

		

	
	
		For example, if you are asked to enter the number twelve into a cell, the text will say     
		

		

		

		

		

		

		

	
	
		

		Type              12             and press <Enter> will enter the number 12.
		

		

		

		

		

		

	
	
		

		Type              '12            and press <Enter> will enter the text characters 1 and 2.
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Software and Hardware
		

		

		

		

		

		

		

	
	
		These tutorials have been written for Microsoft Excel on a computer running Windows 10 equipped with a mouse.
		

		

		

		

		

		

		

	
	
		The tutorials can be opened in OpenOffice Calc, Google Sheets, or other common spreadsheet software.
		

		

		

		

		

		

		

	
	
		However, some of the functions, and the screenshots will not be the same in other software.
		

		

		

		

		

		

		

	
	
		These tutorials are not intended to be opened in mobile operating systems versions of Excel, or on a touchscreen based phone or tablet.
		

		

		

		

		

		

		

	
	
		A version for OpenOffice Calc is in preparation (July 2021).
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		PC vs Mac
		

		

		

		

		

		

		

	
	
		In some cases, some guidance has been given in the tutorials on how those using Mac versions of Excel may find the experience to be different.
		

		

		

		

		

		

		

	
	
		The <leftclick> and <rightclick> instructions for PC can translate as <Click> and <CTRL-click> in most cases.
		

		

		

		

		

		

		

	
	
		The <F2> instruction to reveal the target cells of formulas on a PC can be achieved by a <doubleclick> on either a Mac or PC.
		

		

		

		

		

		

		

	
	
		Please email craig.nichol@ubc.ca with suggestions for PC vs Mac instructions.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Edits, additions and Updates
		

		

		

		

		

		

		

	
	
		Please email craig.nichol@ubc.ca if you have suggestions for edits or improvements.
		

		

		

		

		

		

		

	
	
		Please note that each tutorial has been written with active cells for calculations to be carried out.
		

		

		

		

		

		

		

	
	
		This choice was made in order that the tutorial is "live".
		

		

		

		

		

		

		

	
	
		This does mean that the instructions in each sheet are specific to certain cells.  If you move content by inserting rows, or deleting rows, then the
		

		

		

		

		

		

		

	
	
		instructions and the active cells will not be properly matched.   
		

		

		

		

		

		

		

	
	
		This version of the spreadsheet for the Earth Educators Rendevous uses American spelling (E.G.: COLOR rather than Canadian spelling color) . 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Instructors: 
		

		

		

		

		

		

		

	
	
		Version 7 is the second public release of this spreadsheet.
		

		

		

		

		

		

		

	
	
		Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course.
		

		

		

		

		

		

		

	
	
		Please send suggestions of how the tutorials can be best arranged to suit the types of spreadsheet skills you expect in your course.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Licensing and Attribution
		

		

		

		

		

		

		

	
	
		This work by Dr. Craig Nichol is licensed under CC BY-NC-SA 4.0
		

		

		

		

		

		

		

	
	
		This spreadsheet may be modifed or adapted. 
		

		

		

		

		

		

		

	
	
		An attribution must be made to the the original spreadsheet:
		

		

		

		

		

		

		

	
	
		This spreadsheet may not be used for commercial purposes.
		

		

		

		

		

		

		

	
	
		Any derivatives must be released under the same licence.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Author:
		

		Dr. Craig Nichol, Earth, Environmental and Geographic Sciences, Univerity of British Columbia Okanagan.
		

		

		

		

		

	
	
		

		

		Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course, or with any suggestions or edits.
		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Acknowledgements:
		

		

		

		

		

		

		

	
	
		Peer Reviewers:
		

		Stuart MacKinnon, UBC (June 2020)
		

		

		

		

		

	
	
		

		

		Nina Hewitt, UBC (June 2020)
		

		

		

		

		

	
	
		

		

		Crystal Huscroft, TRU (June 2020)
		

		

		

		

		

	



Sheet 2: 1 Number Text Data

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Tutorial 1: Basic Data Entry, Data Types and Data Display
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Learning Outcomes
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		1. Enter simple numerical data into cells
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		2. Understand the display formats of simple numerical data
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		3. Changing the display format of simple numerical data.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		4. Using scientific notation to display very large or very small numbers
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		5. Change the format of scientifc notation numbers
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		6. Enter text data into cells
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		7 Convert text data to numerial data.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		8. Set the font and font settings, the background color, and cell outlines
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		9. Basic copying and pasting, and how formats are pasted.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		1 Entering Data
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Selecting a cell, entering a number or text, and pressing <Enter> assigns data to a cell location
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Spreadsheets are a common software program used to store data, perform calculations, and present data.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Data in a spreadsheet is stored in a cell.  Each cell has a column which has a letter, starting at A and going to Z.  After Z, the columns are identified with two letters (AA, AB, AC...AZ, BA, BB... )
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Each cell can contain a variety of data in different formats.  This can be numbers, dates, single characters, or longer strings of characters that make text.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cell B24 is highlighted in green.  In this spreadsheet tutorial, green or white cells are used to indicate places where  you should be entering data.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cell B26 is highlighted in red.  In this spreadsheet tutorial, red cells are cells in which you should not change the values otherwise part of the tutorial may not work.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Hover your mouse over cell B31 and click to highlight the cell.  When selected, a cell will have a bold box around the cell, and this is now termed the active cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		With B31 as the active cell, type         12        and press <ENTER>.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		In this tutorial a large space will be shown to separate the text you should write. 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		If this includes single quotes ' or double quotes “ these characters should be entered. Otherwise do not include quotes.
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cell B31 now contains a numerical value of 12.  It will display with no decimal point or digits, as the cell is interpreted to be an INTEGER value (a whole number).
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		2 Number Formats
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The contents of a cell for mathematical purposes, and the format used to display that number are separate.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Examine cell B41.   It displays the value 12.6     
		

		

		

		

		

		
 
		
		

		

		

		

		

		

	
	
		Click on this cell to make it the active cell.
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		12.6
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Look to the top of the screen where there is the Input Line.
		

		

		

		

		

		

		

		

		

		

		

	
	
		The currently selected cell is shown:      B41
		

		

		

		

		

		

		

		

		

		

		

	
	
		The contents are shown:            12.57234
		

		

		

		

		

		

		

		

		

		

		

	
	
		Spreadsheets can store numbers in a variety of formats.
		

		

		

		

		

		

		

		

		

		

		

	
	
		Integers are whole numbers.  Floating Point numbers have fractions after the decimal point, and are stored to many decimal places accuracy
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Right now, Cell B41 only displays the number to one digit after the decimal point.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		However, if you were to do any calculations using this data, the full number      12.57234     would be used.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		3 Changing the Number Display Format
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You can choose from a range of formats to display numbers
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Make cell B57 the active cell.    
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		15.3
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Option 1: On the <Home Tab>, look across the menu for the Cells area, and select the down arrrow beside <Format>.  Click on <Format Cells>.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Option 2:  <rightclick> on the cell and choose <Format Cell> from the pop up menu.
		

		

		

		

		

		

		
 
		
		

		

		

		

	
	
		The dialog box to the right should open.
		

		

		

		

		

		

		

		

		

		

	
	
		Across the top of the dialog box are tabs: Numbers, Alignment, Font, etc
		

		

		

		

		

		

		

		

		

		

	
	
		On the <Numbers> tab, you should see <Options>, and <Decimal Places>
		

		

		

		

		

		

		

		

		

		

	
	
		Use the arrow keys to increase the number of decimal places.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		What value is actually located in cell B57?
		

		

		

		

		

		

		

		

		

		

	
	
		Type the value of this number into cell B69.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		4 Scientific Notation
		

		

		

		

		

		

		

		

		

		

	
	
		Scientific notation is used to make very large or very small numbers easier to use.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A number in a cell is a numerical value which can be an INTEGER (no decimal places), or a FLOATING POINT number (with decimal places).
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Numbers can also be expressed using SCIENTFIC NOTATION.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		B83 and C83 contain the same floating point number with one digit after the decimal point.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		1863.4
		1863.4
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		
 
		
		

		

		

	
	
		Choose C83 as the active cell.  <Rightclick> and choose <Format Cells>.
		

		

		

		

		

		

		

		

		

		

	
	
		On the <Numbers> tab, under <Category>, choose <Scientific>.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		You will see a sample of the display shown in a <Sample> box.
		

		

		

		

		

		

		

		

		

		

	
	
		This indicates that scientific notation will be displayed as shown
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Click on <Ok>.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		The number 1863.4 has now been displayed as:
		

		

		

		

		

		

		

		

		

		

	
	
		                 1.86 x 103
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		This is equal to 1.86 multiplied by 10 raised to the power of 3.
		

		

		

		

		

		

		

		

		

		

	
	
		10 Raised to the power of three is equal to 10 multiplied by itself 3 times, or 10*10*10 = 1000.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Thus 1.86E+03 expressed in scientific notation is equal to
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		1.86 x 1000 = 1860
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		What happened to the other 3.4?   
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The format that was selected only displays the first 3 digits of the number.  The others are hidden.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Keep in mind that the value of the actual data stored in Cell C83 did not change.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The computer is still storing 1863.4, it is just displaying fewer digits.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		5 Changing the Displayed Digits in Scientific Notation
		

		

		

		

		

		
 
		
		

		

		

		

		

	
	
		You can choose to display more digits or fewer digits.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		 Make cell B117 the active cell
		

		

		

		

		

		

		

		

		

		

	
	
		<Rightclick> and change the format to scientific notation.
		

		

		

		

		

		

		

		

		

		

	
	
		

		1863.4
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Use the <Decimal Places> drop down box to change the number of digits.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Can you now see the whole number?
		

		

		

		

		

		

		

		

		

		

	
	
		You can use this to change the number of digits displayed.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		6 Entering Text
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		TEXT can be a written CHARACTER, or multiple characters that make up a STRING.  TEXT has no numerical value for calculations
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Examine cell B137  It contains the number twelve and one half.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Using what you have learned above, try to change the number of displayed digits.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Did it work?
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		12.5
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The data entered into cell B137 is not a number.  It is entered as TEXT.  This means is not the value 12.5, but rather the typed text “12.5”.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This means what occupies this cell is not the value 12.5, but rather the text character 1, the text character 2, the text character period and the text character 5.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Together, there four characters make up a STRING of text that looks like              12.5   
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		While cell B137 is active, look in the input line at the top of the screen.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You may need to squint hard to see it, depending on the size of your screen, but just before the digits, you should see a single quote      '
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This is the symbol used in spreadsheets to indicate that what follows is to be treated as text, not as a numerical value.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Information that carries obvious text characters like these instructions lines are automatically interpreted to be text, and no    '    single quote is needed.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		If you wish to force something to be entered and interpreted as text then add a single quotation mark BEFORE the item to become text.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		For example, typing                 12.5               and hitting enter would create the numerical value 12.5.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Typing                                       '12.5              and hitting enter will tell the spreadsheet that what you want to create is the text “12.5”, not the numerical value 12.5.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		7 Converting Text to Numbers
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Copying and pasting data into a spreadsheet may lead to number values becoming text.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Copying and pasting data into a spreadsheet is often assisted by automatic formatting.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Copied items that seem to be in the form of a table, or to have a format of rows and columns, will often paste into a spreadsheet in the right format.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A common problem is that data copied and pasted that should be numbers is entered into the cells as character strings instead.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		When you try to perform a mathematical operation on the “number” in the cell, nothing happens or an error occurs, because it is text data, not value data.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Examine cell B163 and B164, which both contain            '12.5         or the text characters one two decimal five.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Examine Cell F163 and F164.  They contain the numerical value 12.5.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		12.5
		

		

		

		12.5
		

		

		

		

		

		

		

		

		

		

	
	
		

		12.5
		

		

		

		12.5
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Make Cell B164 the active cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You could click your cursor into the Input Line at the top of the screen, and carefully delete the      '      character before the 12.5.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		For a lot of data, this would be tiresome.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Instead, with Cell B164 active, choose <Data> and select <Text to Columns> in the <Data Tools> area.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		On each screen, accept the default settings, and click <OK> and then <Finish>.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Screen captures of the screens are shown below.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This data conversion tool will recognize that you want this text to be converted to a value
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		What happened to the format?
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You will have noticed that the      12.5   moved from being on the left of the cell, to the right.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This is one hint that a lot of spreadsheets use as a default presentation style which can help you to separate text and values.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		By default, text is usually formatted to start on the left of the cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		By default, numerical values are usually formatted to be aligned to the right of the cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This can sometimes help you spot format issue.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		
 
		
		
 
		
		
 
		
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		For practice, try formatting F164 back to being text.  How would you do that?   What character needs to be entered as the start of the number 12.5?  What happens to the display format?
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		8 Basic Formats
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Data in cells can be formatted to help you distinguish material from other material.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cell B206 contains the text Title.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Title
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		
 
		
		

	
	
		

		

		

		

		

		

		

	
	
		Make it the active Cell.
		

		

		

		

		

		

	
	
		Pressing <CTRL> and <B> at the same time will make the text bold.
		

		

		

		

		

		

	
	
		Pressing <CTRL> and <i> at the same time will make the text italics
		

		

		

		

		

		

	
	
		Pressing <CTRL> and <U> at the same time will make the text underlined
		

		

		

		

		

		

	
	
		The same format changes can be found on the main menu bar.
		

		

		

		

		

		

	
	
		In the <Home> tab  <Font> area, you can change Bold, Italics, Underline, Font color.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The dropdown arrow next to the red A wil show you the colors you can assign for text
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The Paint Can icon will change background color.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cells can also have a background color, and borders.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Try the various buttons contained in the Font and Aligment areas to determine their effect.
		

		

		

		

		

		

		
 
		
		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		Cells can be given borders.
		

		

		

		

		

		

		

		

		

	
	
		<Rightclick> and choose <Format Cells>.
		

		

		

		

		

		

		

		

		

	
	
		Click on the <Border> tab.
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		On the left, you can select the line style you wish.  
		

		

		

		

		

		

		

		

		

	
	
		On the right, you can select which line locations around the cells you want to change.
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		Try experimenting with changing background, fonts, and borders.
		

		

		

		

		

		

		

		

		

	
	
		See if you can make cells B232 to C234 match those in cells E232 to F234.
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		Parameter
		Value
		

		Parameter
		Value
		

		

		

		

	
	
		

		Length
		12.6
		

		Length
		12.6
		

		

		

		

	
	
		

		Width
		14
		

		Width
		14
		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		9 Copying, Pasting, and Cell Formats
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Copying and pasting data preserves the type of data being stored
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Value
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Parameter
		12.5
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The example above contains two cells that contain Text  - B247 and C246
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The    12.5           is a numerical value.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		To copy a cell's contents, make that cell the active cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Make Cell C247 the active cell.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		To copy an item, choose  <Copy> from the <Clipboard> area of the <Home> main menu, 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Make C258 the active cell.
		

		

		

		

		

		
 
		
		

		

		

		

		

		

	
	
		Chose  <Paste> from the <Clipboard> area of the main <Home > menu.
		

		

		

		

		

		

		

		

		

		

		

	
	
		What happened?
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You will notice that the value of 12.5 now appears in Cell C258.
		

		

		

		

		

		

		

		

		

		

		

	
	
		The color also changed from a green background to a white one.
		

		

		

		

		

		

		

		

		

		

		

	
	
		This is because the cell contains not only the data 12.5, but also information about how to display that number.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		In this example, the text is not bold, not underlined, not italics, is a certain size and font, and is displayed on a white background.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The value, and all the associated formatting, was copied from C247 and pasted into C258.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Return to Cell C247, and  <Copy> the cell contents.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This time, click on cell C269.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		With cell C269 highlighted, click on the arrow located under  <Paste> and choose <Paste Special>.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		You will see the pop up menu to the right.
		

		

		

		

		

		
 
		
		

		

		

		

		

		

		

	
	
		This lists all of the characteristics of the cell C247 that you can choose
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		to copy to a new cell.
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		All
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Formulas
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Values
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Formats
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Comments
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		etc.
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		In this case, click on the small box so that only <Values> is selected
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The, click on <OK>
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This time, Cell 256 should now contain the value 12.5, but the background to the cell should remain green.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Only the numerical data, the number 12.5, was copied, but not any formats, formulas or other features of cell C247.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Tutorial 1: Summary
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		This worksheet has provided some instruction and some practice in entering basic data into a spreadsheet.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		As these worksheets progress, the user is expected to be proficient in the skills from previous worksheets.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		For example, in this worksheets, you are given explicit instructions on how to make the text in a call bold, or underlined.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		In worksheets that come, these instructions may not be repeated, but you may simply be asked to “...make the text bold”.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Typically, instructions will be repeated several times before they are simply assumed to be part of your existing skill set.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	



Sheet 3: 2 Date Time Data

	
	
	
	
	
	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Tutorial 2: Dates and Date Formats
		

		

		

		

		

		

		

		

		

		

	
	
		Learning outcomes
		

		

		

		

		

		

		

		

		

		

	
	
		1. Understand how to enter date and time data into a cell.
		

		

		

		

		

		

		

		

		

		

	
	
		2. Be able to set date and time data to different display formats
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		1. Enter Data Using Date and Time Formats
		

		

		

		

		

		

		

		

		

		

	
	
		A cell can contain numbers, characters, strings, formulas, as well as time or date data.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Tutorial one introduced numbers and text as possible data formats.
		

		

		

		

		

		

		

		

		

		

	
	
		In this tutorial, we will work with date and time data, which are special cases.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Time
		

		

		

		

		

		

		

		

		

	
	
		YYYY-MM-DD
		HH:MM:SS
		

		

		

		

		

		

		

		

		

	
	
		2020/06/11
		10:30:23 AM
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		The two cells above contain date (A25) and time (B25) data.
		

		

		

		

		

		

		

		

		

		

	
	
		The date is June 11th 2020 and the time is 10:30:23 AM.
		

		

		

		

		

		

		

		

		

		

	
	
		Click on each cell in turn, and examine the nature of the data displayed in the Input Line.
		

		

		

		

		

		

		

		

		

		

	
	
		You will see that the displayed format within the cell matches to how the date and time are displayed.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Time and date data use standard shorthand notations to describe how data are entered or displayed.
		

		

		

		

		

		

		

		

		

		

	
	
		Year is Y, Month is M, Day is D, Hour is H, Minutes is M, Seconds is S.  
		

		

		

		

		

		

		

		

		

		

	
	
		These can be combined using some standard conventions.
		

		

		

		

		

		

		

		

		

		

	
	
		YYYY
		This means to display the full year including all digits
		

		

		

		

		

		

		

		

		

	
	
		YY 
		This means to just display the last two digits of the year.  2018 would be 18.
		

		

		

		

		

		

		

		

		

	
	
		MM
		Will display the month of the year as a number from 1 to 12
		

		

		

		

		

		

		

		

		

	
	
		MMM
		Will display the month of the year as 3 characters of text   Jan Feb Mar Apr.....
		

		

		

		

		

		

		

		

		

	
	
		DD
		Displays the day (1 to 31)
		

		

		

		

		

		

		

		

		

	
	
		HH:
		Displays the hour
		

		

		

		

		

		

		

		

		

	
	
		MM
		Displays the minutes
		

		

		

		

		

		

		

		

		

	
	
		SS
		Displays the seconds
		

		

		

		

		

		

		

		

		

	
	
		AM / PM
		Determines the time of the day as before noon or after noon.
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		To enter DATE data, type in numbers in the format:          YYYY-MM-DD     such as        2020-07-27   to enter  July 27th, 2020.
		

		

		

		

		

		

		

		

		

		

	
	
		When numbers are typed in separated by a      -        the numbers will be interpreted as dates.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		To enter TIME data, type in number in the format: HH:MM or HH:MM:SS
		

		

		

		

		

		

		

		

		

		

	
	
		When numbers are separated by a colon      :        the spreadsheet will interpret the data as times.
		

		

		

		

		

		

		

		

		

		

	
	
		A time is inherently ambigous as to whether is is AM or PM.
		

		

		

		

		

		

		

		

		

		

	
	
		You can resolve this by typing in the AM or PM.  Type                 2:14 PM       to enter PM data.
		

		

		

		

		

		

		

		

		

		

	
	
		Another method is to use a 24 hours clock, so that  2:14 PM is entered as 14:14
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		2. Setting the display format for date data
		

		

		

		

		

		

		

		

		

		

	
	
		The data that is stored for date or time, and the manner in which it is displayed, are separate.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Date
		

		

		

		

		
 
		
		
	
		YYYY-MM-DD
		YYYY-MM-DD
		

		

		

		

		
	
		2020/06/11
		2020/06/11
		

		

		

		

		
	
		

		

		

		

		

		

		
	
		The data stored in cell A61 and B61 above are known by Excel to be date formated.  
		

		

		

		

		

		
	
		What is displayed is date in YYYY-MM-DD format.
		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		Click on B61 and <Rightclick> and <Format Cells>.
		

		

		

		

		

		
	
		Choose <Number> and you will see that the format chosen is <Date>.
		

		

		

		

		

		
	
		You will see the various date formats listed.
		

		

		

		

		

		
	
		Try changing the format to different formats to see what happens.
		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		Month Day Year vs Day Month Year
		

		

		

		

		

		
	
		One of the most common points of confusion for entering and displaying 
		

		

		

		

		

		
	
		dates are differences in convention over writing dates.
		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		11/06/2020
		What date is this?
		

		

		

		

		
	
		

		The 11th of June, 2020
		

		

		

		

		
	
		

		or
		

		

		

		

		

		

		

		

		

	
	
		

		November 6th, 2020
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Different countries in the worlld have different conventions for writing dates.
		

		

		

		

		

		

		

		

		

		

	
	
		A common issue in spreadsheets is for people to not be clear on what format is used for date.
		

		

		

		

		

		

		

		

		

		

	
	
		The default way to enter dates in Excel is YYYY-MM-DD.
		

		

		

		

		

		

		

		

		

		

	
	
		This is the safest way to enter dates and to be sure the correct date is entered.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		However, dates on forms, field notes, or other forms of data used for data entry may vary.
		

		

		

		

		

		

		

		

		

		

	
	
		The most common way of writing dates across Earth is Day-Month-Year, which is what Canada uses.
		

		

		

		

		

		

		

		

		

		

	
	
		The United States uses Month-Day-Year. Other countries use other formats.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		If you click on A75 above, and look at the input line, you will see      2020-06-11  indicating it is June 11th of 2020.
		

		

		

		

		

		

		

		

		

		

	
	
		The input line shows the raw data being stored in a common format.  
		

		

		

		

		

		

		

		

		

		

	
	
		The cell has been formatted to display that data in a DD-MM-YYYY format instead of the default  YYYY-MM-DD format.
		

		

		

		

		

		

		

		

		

		

	
	
		This is like a cell displaying      13      or an integer (no decimals) in a cell, but the input line showing the true value
		

		

		

		

		

		

		

		

		

		

	
	
		stored in the cell of 12.856743
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Try setting the cell A75 back to a YYYY-MM-DD format.
		

		

		

		

		

		

		

		

		

		

	
	
		If there is ever any potential for confusion between spreadsheet users, make sure to either include 
		

		

		

		

		

		

		

		

		

		

	
	
		a key such as          DD-MMM-YYYY in the data header to clearly tell users the format.
		

		

		

		

		

		

		

		

		

		

	
	
		Or, choose a format including the MMM form so that month is written as text, not a number.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		3. Working with Date Data
		

		

		

		

		

		

		

		

		

		

	
	
		The actual number stored for dates is a single number that counts time forwards from Jan 1, 1900.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Date
		Date
		

		

		

		

		

		

		

		

	
	
		YYYY-MM-DD
		YYYY-MM-DD
		YYYY-MM-DD
		

		

		

		

		

		

		

		

	
	
		2020/06/11
		43993.00
		11/06/2020
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Click on cell C109
		

		

		

		

		

		

		

		

		

		

	
	
		<Rightclick> and <Format Cells>.
		

		

		

		

		

		

		

		

		

		

	
	
		Choose <Number> and <Number>
		

		

		

		

		

		

		

		

		

		

	
	
		The format of Cell C109 will swap to match B109
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Windows Excel spreadsheets use a convention for determining dates, which is to track 
		

		

		

		

		

		

		

		

		

		

	
	
		the start of the day Jan 1, 1900 as date = 0
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		The number 43993 is the number of days since Jan 1, 1900 to reach Jun 11th, 2020.
		

		

		

		

		

		

		

		

		

		

	
	
		The actual data stored in the background of a date formatted cell is actually this number.
		

		

		

		

		

		

		

		

		

		

	
	
		Having it be a date formated cell simply allows Excel to interpret a number of 43993 as a date in 2020.
		

		

		

		

		

		

		

		

		

		

	
	
		Note: If you are doing this tutorial on a MAC, the you may have noticed a different number.
		

		

		

		

		

		

		

		

		

		

	
	
		Excel for MAC and some other spreadsheets use Jan 1, 1904 as day 1.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		The lesson: You must be very careful when swapping files between MAC and Windows users when dates are involved
		

		

		

		

		

		

		

		

		

		

	
	
		The same for changing between Excel and OpenOffice Calc.
		

		

		

		

		

		

		

		

		

		

	
	
		Always double check that all dates have been interpreted correctly.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		We will see this again in later tutorials when we look at graphs with DATE on the x-axis.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		4. Time Data
		

		

		

		

		

		

		

		

		

		

	
	
		Time data is actually stored as a number between 0 and 1, or the fraction of a day.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Time
		Time
		

		

		

		

		

		

		

		

	
	
		YYYY-MM-DD
		HH:MM:SS
		HH:MM:SS
		

		

		

		

		

		

		

		

	
	
		2020/06/11
		10:30:23 AM
		10:30:23 AM
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Click on C139.  <rightclick> and <Format Cells>.
		

		

		

		

		

		

		

		

		

		

	
	
		Choose <Time> as a format, and then try experimenting with different formats.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Time
		Time
		

		

		

		

		

		

		

		

	
	
		YYYY-MM-DD
		HH:MM:SS
		HH:MM:SS
		

		

		

		

		

		

		

		

	
	
		2020/06/11
		0.437766
		10:30:23 AM
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Next, click on C148. Change the format to <Number> and increase the digits displayed.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		You should see the same number as B148.
		

		

		

		

		

		

		

		

		

		

	
	
		Time is actually calculated across a day as a number between 0 and 1 as the fraction of a day.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		5. Date and Time together
		

		

		

		

		

		

		

		

		

		

	
	
		Days and times can be added or subtracted as numbers.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date
		Time
		

		

		

		

		

		

		

		

		

	
	
		YYYY-MM-DD
		HH:MM:SS
		

		

		

		

		

		

		

		

		

	
	
		2020/06/11
		10:30:23 AM
		2020/Jun/11 10:30:23 AM
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Click on cell C162.
		

		

		

		

		

		

		

		

		

		

	
	
		What is present in that cell is actually a formula that adds together the data
		

		

		

		

		

		

		

		

		

		

	
	
		in cells A162 and B162.
		

		

		

		

		

		

		

		

		

		

	
	
		This is adding together at "DATE" and a "TIME".  One has dashes, the other colons.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Remember, what is really in those two cells is 
		

		

		

		

		

		

		

		

		

		

	
	
		43993.00
		0.437766
		43993.4377662037
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Date is number of days since Jan 1, 1900 (or sometimes Jan 1 1904).
		

		

		

		

		

		

		

		

		

		

	
	
		Time is fraction of a day between 0 and 1.
		

		

		

		

		

		

		

		

		

		

	
	
		Adding the two together has given us a number that contains
		

		

		

		

		

		

		

		

		

		

	
	
		both the calendar date and time.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Rightclick on C162 and <Fomat Cells> to see how that is displayed.
		

		

		

		

		

		

		

		

		

		

	
	
		You will see that the cell format is <Custom>, as YYYY-MMM-DD HH:MM:SS AM/PM
		

		

		

		

		

		

		

		

		

		

	
	
		By choosing a <Custom> type, you can create your own date and time display using YMDHMS shorthands.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Trying changing the date and time displayed in C170 to get it to match C162.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Tutorial 2 Summary
		

		

		

		

		

		

		

		

		

		

	
	
		Date and Time are actually stored as days since 1900 and fractions of a day.
		

		

		

		

		

		

		

		

		

		

	
	
		How they are displayed and worked with in cells, and in the input line are slightly different.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		Always make sure to double check any date data, both on the data source, 
		

		

		

		

		

		

		

		

		

		

	
	
		and in the spreadsheet, to make sure that you are entering and displaying the data
		

		

		

		

		

		

		

		

		

		

	
	
		the right way.
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		The most common error is confusion between the Canadian  day month year system and the 
		

		

		

		

		

		

		

		

		

		

	
	
		United States  month day year system of writing dates.

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet readme earth environmental and geographic sciences excel tutorial author dr craig nichol ubc okanagan creative commons by nc sa version july for educators rendezvous please send any comments edits or suggestions content to include craignichol ubcca welcome this workbook has been created provide tutorials on how use spreadsheet software conduct basic data storage calculations graphing these are not intended be all done at once but an asneeded basis was originally accompany various courses in the program division of into designed match needs different also shared with anyone who is interested learning more about those have encountered another way feel free rework modify suit your share others email indicating where you sharing it why so that i can improve being used general instructions menu options keyboard commands contained within lt gt marks example doubleclick indicates mouse button should double clicked tools would mean upper left click then select submenu cell b highlighted ...

Related files

Share

Help

Earth, Environmental and Geographic Sciences Excel Tutorial
Author Dr. Craig Nichol UBC Okanagan Creative Commons 4.0 BY NC SA			Version 7 July 7, 2021 for Earth Educators Rendezvous
Please send any comments, edits, or suggestions for content to include to: craig.nichol@ubc.ca

Welcome
This Excel Workbook has been created to provide tutorials on how to use spreadsheet software to conduct basic data storage, calculations and graphing.
These tutorials are not intended to be all done at once, but to be done on an as-needed basis.
This workbook was originally created to accompany various courses in the Earth and Environmental Sciences program at UBC Okanagan.
The content and the division of content into the tutorials was designed to match the needs of different courses.
This tutorial has also been shared with anyone who is interested in learning more about Excel.

Those who have encountered this tutorial in another way - welcome. Please feel free to rework and modify this tutorial to suit your needs, or to share the tutorial with others.
Please send an email to craig.nichol@ubc.ca indicating where you are sharing it and why so that I can improve the tutorial to suit how it is being used.

General Instructions

Menu options or keyboard commands are contained within <> marks.
For example, <doubleclick> indicates that the mouse button should be double clicked.
<Tools> <Options> would mean that on the upper menu you should use the left mouse button to click on the Tools menu,
and then select the Options sub-menu

Cell B23 is highlighted in green. In this spreadsheet tutorial, green or white cells are used to indicate places where you should be entering data.

Cell B26 is highlighted in red. In this spreadsheet tutorial, red cells are cells in which you should not change the values otherwise the tutorial may not function.


A large space will be shown to separate the text you should write.
If this includes single quotes ' or double quotes “ these characters should be entered
For example, if you are asked to enter the number twelve into a cell, the text will say
	Type 12 and press <Enter> will enter the number 12.
	Type '12 and press <Enter> will enter the text characters 1 and 2.


Software and Hardware
These tutorials have been written for Microsoft Excel on a computer running Windows 10 equipped with a mouse.
The tutorials can be opened in OpenOffice Calc, Google Sheets, or other common spreadsheet software.
However, some of the functions, and the screenshots will not be the same in other software.
These tutorials are not intended to be opened in mobile operating systems versions of Excel, or on a touchscreen based phone or tablet.
A version for OpenOffice Calc is in preparation (July 2021).

PC vs Mac
In some cases, some guidance has been given in the tutorials on how those using Mac versions of Excel may find the experience to be different.
The <leftclick> and <rightclick> instructions for PC can translate as <Click> and <CTRL-click> in most cases.
The <F2> instruction to reveal the target cells of formulas on a PC can be achieved by a <doubleclick> on either a Mac or PC.
Please email craig.nichol@ubc.ca with suggestions for PC vs Mac instructions.

Edits, additions and Updates
Please email craig.nichol@ubc.ca if you have suggestions for edits or improvements.
Please note that each tutorial has been written with active cells for calculations to be carried out.
This choice was made in order that the tutorial is "live".
This does mean that the instructions in each sheet are specific to certain cells. If you move content by inserting rows, or deleting rows, then the
instructions and the active cells will not be properly matched.
This version of the spreadsheet for the Earth Educators Rendevous uses American spelling (E.G.: COLOR rather than Canadian spelling color) .



Instructors:
Version 7 is the second public release of this spreadsheet.
Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course.
Please send suggestions of how the tutorials can be best arranged to suit the types of spreadsheet skills you expect in your course.

Licensing and Attribution
This work by Dr. Craig Nichol is licensed under CC BY-NC-SA 4.0
This spreadsheet may be modifed or adapted.
An attribution must be made to the the original spreadsheet:
This spreadsheet may not be used for commercial purposes.
Any derivatives must be released under the same licence.

Author:		Dr. Craig Nichol, Earth, Environmental and Geographic Sciences, Univerity of British Columbia Okanagan.
		Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course, or with any suggestions or edits.

Acknowledgements:
Peer Reviewers:		Stuart MacKinnon, UBC (June 2020)
		Nina Hewitt, UBC (June 2020)
		Crystal Huscroft, TRU (June 2020)



Tutorial 2: Dates and Date Formats
Learning outcomes
1. Understand how to enter date and time data into a cell.
2. Be able to set date and time data to different display formats









1. Enter Data Using Date and Time Formats
A cell can contain numbers, characters, strings, formulas, as well as time or date data.


Tutorial one introduced numbers and text as possible data formats.
In this tutorial, we will work with date and time data, which are special cases.

Date	Time
YYYY-MM-DD	HH:MM:SS
2020/06/11	10:30:23 AM


The two cells above contain date (A25) and time (B25) data.
The date is June 11th 2020 and the time is 10:30:23 AM.
Click on each cell in turn, and examine the nature of the data displayed in the Input Line.
You will see that the displayed format within the cell matches to how the date and time are displayed.

Time and date data use standard shorthand notations to describe how data are entered or displayed.
Year is Y, Month is M, Day is D, Hour is H, Minutes is M, Seconds is S.
These can be combined using some standard conventions.
YYYY	This means to display the full year including all digits
YY	This means to just display the last two digits of the year. 2018 would be 18.
MM	Will display the month of the year as a number from 1 to 12
MMM	Will display the month of the year as 3 characters of text Jan Feb Mar Apr.....
DD	Displays the day (1 to 31)
HH:	Displays the hour
MM	Displays the minutes
SS	Displays the seconds
AM / PM	Determines the time of the day as before noon or after noon.

To enter DATE data, type in numbers in the format: YYYY-MM-DD such as 2020-07-27 to enter July 27th, 2020.
When numbers are typed in separated by a - the numbers will be interpreted as dates.

To enter TIME data, type in number in the format: HH:MM or HH:MM:SS
When numbers are separated by a colon : the spreadsheet will interpret the data as times.
A time is inherently ambigous as to whether is is AM or PM.
You can resolve this by typing in the AM or PM. Type 2:14 PM to enter PM data.
Another method is to use a 24 hours clock, so that 2:14 PM is entered as 14:14

2. Setting the display format for date data
The data that is stored for date or time, and the manner in which it is displayed, are separate.


Date	Date
YYYY-MM-DD	YYYY-MM-DD
2020/06/11	2020/06/11

The data stored in cell A61 and B61 above are known by Excel to be date formated.
What is displayed is date in YYYY-MM-DD format.

Click on B61 and <Rightclick> and <Format Cells>.
Choose <Number> and you will see that the format chosen is <Date>.
You will see the various date formats listed.
Try changing the format to different formats to see what happens.

Month Day Year vs Day Month Year
One of the most common points of confusion for entering and displaying
dates are differences in convention over writing dates.

11/06/2020	What date is this?
	The 11th of June, 2020
	or
	November 6th, 2020



Different countries in the worlld have different conventions for writing dates.
A common issue in spreadsheets is for people to not be clear on what format is used for date.
The default way to enter dates in Excel is YYYY-MM-DD.
This is the safest way to enter dates and to be sure the correct date is entered.

However, dates on forms, field notes, or other forms of data used for data entry may vary.
The most common way of writing dates across Earth is Day-Month-Year, which is what Canada uses.
The United States uses Month-Day-Year. Other countries use other formats.

If you click on A75 above, and look at the input line, you will see 2020-06-11 indicating it is June 11th of 2020.
The input line shows the raw data being stored in a common format.
The cell has been formatted to display that data in a DD-MM-YYYY format instead of the default YYYY-MM-DD format.
This is like a cell displaying 13 or an integer (no decimals) in a cell, but the input line showing the true value
stored in the cell of 12.856743


Try setting the cell A75 back to a YYYY-MM-DD format.
If there is ever any potential for confusion between spreadsheet users, make sure to either include
a key such as DD-MMM-YYYY in the data header to clearly tell users the format.
Or, choose a format including the MMM form so that month is written as text, not a number.

3. Working with Date Data
The actual number stored for dates is a single number that counts time forwards from Jan 1, 1900.


Date	Date	Date
YYYY-MM-DD	YYYY-MM-DD	YYYY-MM-DD
2020/06/11	43993.00	11/06/2020


Click on cell C109
<Rightclick> and <Format Cells>.
Choose <Number> and <Number>
The format of Cell C109 will swap to match B109

Windows Excel spreadsheets use a convention for determining dates, which is to track
the start of the day Jan 1, 1900 as date = 0

The number 43993 is the number of days since Jan 1, 1900 to reach Jun 11th, 2020.
The actual data stored in the background of a date formatted cell is actually this number.
Having it be a date formated cell simply allows Excel to interpret a number of 43993 as a date in 2020.
Note: If you are doing this tutorial on a MAC, the you may have noticed a different number.
Excel for MAC and some other spreadsheets use Jan 1, 1904 as day 1.


The lesson: You must be very careful when swapping files between MAC and Windows users when dates are involved
The same for changing between Excel and OpenOffice Calc.
Always double check that all dates have been interpreted correctly.

We will see this again in later tutorials when we look at graphs with DATE on the x-axis.

4. Time Data
Time data is actually stored as a number between 0 and 1, or the fraction of a day.


Date	Time	Time
YYYY-MM-DD	HH:MM:SS	HH:MM:SS
2020/06/11	10:30:23 AM	10:30:23 AM


Click on C139. <rightclick> and <Format Cells>.
Choose <Time> as a format, and then try experimenting with different formats.


Date	Time	Time
YYYY-MM-DD	HH:MM:SS	HH:MM:SS
2020/06/11	0.437766	10:30:23 AM

Next, click on C148. Change the format to <Number> and increase the digits displayed.

You should see the same number as B148.
Time is actually calculated across a day as a number between 0 and 1 as the fraction of a day.



5. Date and Time together
Days and times can be added or subtracted as numbers.

Date	Time
YYYY-MM-DD	HH:MM:SS
2020/06/11	10:30:23 AM	2020/Jun/11 10:30:23 AM

Click on cell C162.
What is present in that cell is actually a formula that adds together the data
in cells A162 and B162.
This is adding together at "DATE" and a "TIME". One has dashes, the other colons.

Remember, what is really in those two cells is
43993.00	0.437766	43993.4377662037

Date is number of days since Jan 1, 1900 (or sometimes Jan 1 1904).
Time is fraction of a day between 0 and 1.
Adding the two together has given us a number that contains
both the calendar date and time.

Rightclick on C162 and <Fomat Cells> to see how that is displayed.
You will see that the cell format is <Custom>, as YYYY-MMM-DD HH:MM:SS AM/PM
By choosing a <Custom> type, you can create your own date and time display using YMDHMS shorthands.

Trying changing the date and time displayed in C170 to get it to match C162.


Tutorial 2 Summary
Date and Time are actually stored as days since 1900 and fractions of a day.
How they are displayed and worked with in cells, and in the input line are slightly different.

Always make sure to double check any date data, both on the data source,
and in the spreadsheet, to make sure that you are entering and displaying the data
the right way.

The most common error is confusion between the Canadian day month year system and the
United States month day year system of writing dates.

Related files

Share

Share to social media

Help

Login Area