CIS*1000*DE
START Double click on
the ICON on
desktop
PROGRAMS OR
Microsoft Office 2007
Microsoft Excel 2007
Saving your
• To save your document, simply click on the MS 2007 logo in the top left?hand corner [ ] and the menu bar that you see on your right here will drop down giving you various options, including saving.
• When you save a workbook in MS Excel 2007, it automatically saves with “.xlsx” as its extension.
• However, you can also save your workbook so that it’s readable by earlier versions of MS Excel (97 – 2003) by simply selecting “Excel 97?2003 workbook (*.xls)” when choosing what file type to save as (please see the screenshot below)
• To get help in Excel 2007, look for the help icon [ ] in the top right?hand corner of the screen and click on it.
• You will be presented with a window that looks like the one here on the right.
• Workbook – An Excel file containing several worksheets
• Worksheet – Rectangular grid of rows & columns that labels and values are inserted into
• Cell ? The intersection of a row and column, identified by an address (ex. A1, F4, Z55)
• Value – Numerical data in a cell
• Label – Non?numerical data in a cell
• Table – A logically distinct group of cells, visually distinguished with borders and shading
Cell Referencing
In addition to the intuitive mouse and arrowkey movement between cells, these shortcuts can make lots of data easier to input:
• | Home: | Move to beginning of row |
• | Ctrl+Home: | Move to “A1” |
• | Enter: | Move one cell down |
• | TAB: | Move one cell to the right |
• | Shift+TAB: | Move one cell to the left |
• | End+ : | Move to last used cell in row |
• | End+ : | Move to last used cell in column |
• | F2: | Edit the the content of a cell |
Renaming Worksheets
rename and choose rename
• To enter simple data into a cell we can either:
– Position the cursor on the cell and then type on the keyboard and press Enter
– Position the cursor on a cell and then click on the
formula bar, type the data, press Enter or click on the to accept or on the d to cancel.
• Click on the row or column header
• Right click and choose insert
• Rows are inserted above the selected row
• Columns are inserted to the left of the selected column
– Note: Affected merged cells will automatically grow
An Example…
• Type a number into any cell in row “8”
• Right?click on a row header “8” & select Delete
• Type a number into any cell in column “B”
• Right?click on the column letter “B”, & select Delete
– The row/column is removed, along with any formulas, data and formatting.
– The adjacent cells are shifted up/left and the row or column is re?labeled to reflect the new change
• Make the first column, A, wide enough to accommodate the width of the text labels.
• Right?click on the column letter and you’ll be given the following options:
• Select “Column Width”
• Shortcut, you can also click on the line the column letters and drag cursor left or right.
• Merging allows one cell to take up multiple rows and/or columns
• To merge 2 or more cells, highlight the desired cells to merge and click on the merge
& center button:
• We can change the format of numerical data to appear as currency.
• Highlight some columns which contain numbers
• Click the Currency button on the Home tab
Note: there are also buttons for Percentage Style , and Comma Style
• Click on the Decrease Decimal button twice to remove decimal places
0.01 = 1%
0.1 = 10%
1 = 100 %
10 = 1000 %
100 = 10000% ¼ = 0.25 = 25%
½ = 0.50 = 50%
¾ = 0.75 = 75%
etc…
• A formula is a special entry in a cell, that calculates its value based on other cells, and/or constants
• By beginning an entry into a cell with an “=” we let Excel know we’re using a formula
• Without the equal sign “=” Microsoft Excel assumes you’ve entered a label or value
• Select any cell on your spreadsheet and type:
• Press <Enter>
• Excel recognizes the ‘=’ sign and calculates the result
• Note that the formula is shown in the formula bar (while that cell is selected) and that the computed value is displayed in the cell
• Also note that Excel respects the order of operations
(BEDMAS)
• You can refer to cells in a formula by simply using the cell’s reference name.
• Enter some data or a label into cell A1
• Select cell A2 and type: =A1
• Now, when the information in A1 is changed, those changes will automatically show up in cell A2 • You can even link cells from different worksheets!
• For example, in Sheet2 select cell A1 and type:
=My First Spreadsheet!A1 =sheet name!cell address
*Note the Exclamation Mark!!!
• In Cell A2 type: =A1*2
• Now, change the value in A1
• Excel will automatically update the value shown in cell A2
• So, this is just like a link
the value in A1 is substituted in before Excel
performs the calculations in cell A2
• This works with cells linked in between different worksheets too! ?
• Excel comes with hundreds of built?in functions which can be used in your formulas.
• However, the majority of functions need data in order to be useful
– For example, in order to use the Sum function, you need to tell Excel which values to sum up.
• These built?in functions can operate by themselves, on a single value, or on multiple values depending on what kind of operation is being performed
– For example, the Pi function simply returns the value of Pi, the Square Root function needs only one number to work while the Z?Test function (from stats) requires three different numbers in order to work.
• Built?In Functions are structured like this: FunctionName(Parameter1,…)
• The FunctionName always comes first and usually describes what the function does.
• The Parameter is the information that the function needs in order to work
• This can be a single value or a set of values and can be represented by either numbers (ex. 1, 2, 3, 5, 8, 13, etc…), cell references (ex. A1, B2, C3:D4, etc…) or text.
• The Parameter(s) always comes second and are always contained within parentheses “ ( ) “.
• | SUM( ) | Adds all numbers in a range of cells |
• | PRODUCT( ) | Multiplies all the numbers in a range of cells |
• | COUNT( ) | Counts all the cells that contain numbers in a range of cells |
• | > AVERAGE( ) | Calculates the average in a range of cells |
• | MEDIAN( ) | Calculates the median in a range of cells |
• | MODE( ) | Calculates the mode in a range of cells |
• | POWER( ) | Calculates a number raised to a power |
• | SQRT( ) | Calculates the square root of a number |
• | MAX( ) | Returns the largest number in a range of cells |
• | MIN( ) | Returns the smallest number in a range of cells |
• | IF( ) | Checks whether a condition is met, and returns one value if true, and a different value if false |
• | COUNTIF( ) | Count the number of cells within a given range that meet the given condition |
• | PI( ) > | Returns the value of Pi, accurate to 15 digits |
• | MOD( ) | Returns the remainder when a number is divided |
by | a divisor | |
• | LEN( ) | Returns the number of characters in some text |
• | ROMAN( ) | Converts an Arabic numeral to Roman, as text |
• | CONCATENATE( ) | Joins several pieces of text to each other |
• | TODAY( ) | Returns the current date |
• | NOW( ) | Returns the current date and time |
• There are two different ways to use functions:
1. The first way is to simply write them out
This will save you time if you know what functions you’ll be using and how to use them
2. The second way is to use the wizard
This is a great way to learn about the many different kinds of functions and operations that excel can perform and how to use them. =SUM(A1,b>A5,A10,A15,A20)
• This formula uses commas and will sum up the values for the five cells A1, A5, A10, A15, A20
=SUM(A1:A20)
• This formula uses a colon and will sum up the values for the range of cells A1 to A20.
Make sure you understand the difference between these two examples!
• Let’s do an example!
• Type numbers into the first 5 rows of column A
• In cell A6 type: =SUM(A1:A5) • A6 now contains the sum of A1 to A5
• To find the average you would use:
• How could you find the difference between the biggest number and the smallest number in a range of cells?
• Some functions in Excel accept a condition as a parameter.
This is the range of cells to searchthis is the condition=countif(A1:A100, “>75”)
• This function will tell us how many cells in the range of A1 to A100 contain a value that is greater than 75.
• We could have also used <, =, <=, >= in our condition
This is the condition If TRUE, return this value If FALSE, return this value
=if(A2 > A3, “A2 is bigger”, “A3 is bigger”)
=if(countif(A1:A20, “>75”) > 10, “More than half over 75”, “At least half under 75”)
• So, what does this thing do?
• Notice how there are 2 different built?in functions.
• The best way to work through this is to start with the inside built?in function, in this case, countif( ), and work our way out.
• The inside function will count the number of cells from A1 to A20 that contain values greater than 75.
• The outside function will check to see if that “counted” number of cells is greater than 10, and if it is, then it’ll return “More than half over 75”, otherwise, it’ll return “At least half under 75”
Using the Wizard for Built?In Functions
• Select any cell on the spreadsheet
• Click the “=” or the “f_{x}” button on the Formula bar
• The drop?down?box to the left of the “=” can be used to select a function.
• A help wizard will then guide you through the entry of your formula
• New to MS Excel 2007 is a tab with many of Excel’s built in functions / formulas neatly organized into different categories
• While some people may prefer to simply type in their desired formula, others may want to explore what Excel has to offer by searching through and experimenting with the options presented in this tab.
• When we copy/cut & paste a formula Excel transforms it, treating all references as Relative.
• For example, type random numbers into the first 5 rows of column A again, and enter the formula:
=A1*2 into cell B1
• Now, copy and paste the formula into cells B2, B3, B4, & B5
• Notice how the formula has changed…
• …we moved down 3 rows and so too has the cell reference (it changed from A1 to A4)
• We can also tell Excel not to transform a reference
(making it an Absolute Reference)
• Let’s do another example
– In A1 write: “Tax Rate”
– And in B1, write: “1.15”
• Next, create a list of items (ex. things that you’d like to sell) in column A and list their prices in their adjacent cells in column B
• To get the price of the mattress After Tax, we use the formula:
=$B$1*B4
Absolute Relative Reference Reference
(1.15) * ($50.00) = $57.50
• Now, copy and paste the formula into the remaining cells
• We do this because we want the formula (or rather Excel) to “remember” the location of the tax rate. That way the tax rate is applied to the prices of all the items.
• If we didn’t add the absolute reference ($B$1), and instead used the relative reference (B1), then we would have ended up with the formulas: C5=B2*B5, C6=B3*B6, C7=B4*B7, C8= B5*B8 and that would produced the following: obviously wrong!
• Cells are identified by their:
Column (a letter) & Row (a number)
– Ex. A1,F3
• Ranges of cells can be referenced using a colon “:” – Ex. A1:B10
• References to other worksheets take the form:
– For example, ‘My First Spreadsheet’!A1:B10
Relative Referencing Review
• Most references are relative.
• For example, when the formula A2*B2 is copied & pasted into cells C3, C4, C5, and C6, Excel automatically assumes that you wish to apply the formula to row 3 by using A3 & B3 instead of A2 & B2
• When you don’t want a part of your formula transformed you can protect it by adding: ‘$’
• Columns and rows can be isolated separately or together, for example:
– $A1 – the column part (A) is locked
– A$1 – the row part (1) is locked
– $A$1 – both the row and column are locked
• Goal seek is a “what?if” analysis tool
• Essentially, goal seek changes the value of one cell used in a formula by changing the value of another cell in the formula.
• Goal seek can be found under “What?If Analysis”
Goal Seek (cont’d)
• With this example I am trying to determine how many apples $5 will buy me.
• Sorting can be done from the Home tab by clicking on the “Sort & Filter” option. Choose from…
– Sort Descending / Sort A to Z
– Sort Ascending / Sort Z to A
• Sorting can also be done from the Data tab by using the sort buttons
• To Sort, just highlight the specific cells that you’d like sorted and then choose to have them sorted in Ascending (Z to A) or Descending (A to Z) order.
• Let’s do an example!
• Let’s sort this table according to Sex, then
according to Age, and lastly according to Name.
• Select the entire table and…
• Home tab “Sort & Filter”
• Data tab “Sort & Filter”
• As can clearly be seen here, this
table has now been sorted on three different levels
?
• Excel offers a variety of chart types.
• Column, Bar, Line, Pie, and Scatter being the most common choices.
• But before we can make a chart, we’ll need data.
• For an example we’ll need some
actual data to chart
so generate a list of monthly expenses such as this one.
• To add a chart title, select the Layout tab and click on “Chart Title”
• Almost everything in the graph can edited by simply right?clicking on the desired component of the graph and selecting the
• Recall: To edit the graph, use the options found under theLayouttab. From there you can add/edit the chart title, axis titles, legend, etc..
• Everything seen on the chart can be selected, formatted, moved, etc.
• For instance, to change stuff Right Clickon:
– The X?Axis, Y?Axis, Plot Area, Chart Area, Gridlines, Data Series, Legend, or Chart Title and select appropriate option that you’d like to edit
– You’ll be able to play around with the colours, fonts, scale, alignment, numbering, etc… so explore and find out what’s possible!
• So what is the difference anyways?
• A Line Graph is almost like a bar graph, except that instead of using bars, it uses points with a line connecting each of the adjacent points together
• An XY Scatter Plot is the familiar type of graph we all do in math where you have and x?axis and a y?axis that are used to represent coordinates.
• As well, on a Line Graph, you can use labels on the xaxis (ex. Toronto, Ottawa, Montreal, etc…) while on a XY Scatter Plot, you can only use numbers (ex. 0, 1, 5.5, ½, ?0.23, etc..)
• Highlight a chart in Microsoft Excel
• Copy it
• Open a Microsoft Word document
• Paste it
– Note, if the chart in Excel changes, the chart in WordWILLchange as well
• To import a data from a text file, go to the Data tab and look for the “Get External Data” section
• Select the “From Text” option
• Highlight the file and click “Import”
• The Text Import Wizard should open up
• Usually you will want to choose “delimited”, as most files come with some sort of separator between the fields
• Next, select the proper type of delimiter
– For Assignment #2, you will be using a comma delimited file.
• Now, select “general” as the Data Column Format
• Click on Finish
• Practise, Practise, Practise!!!
• If you have any problems post in the forum