Microsoft Excel

This Presentation Contains Narration and NotesTo hear the narration, turn on your speakers or plug in headphones.If you would like to view this slideshow WITHOUT narration please do the following:Click on the Slide Show tab in the ribbon.

In the Set Up section click the Set Up Slide Show button.

Under Show Options check the box next to Show without narration.Click Ok and view the slideshow as normal.The notes at the bottom are a transcription of the narration or extra information.

1

Microsoft Office Excel 2010

Fall 2011short course SECTION I

OFFERED BYINSTRUCTIONAL COMPUTING UNIVERSITY OF MISSOURI ST.LOUIS

Welcome to the Instructional Computer short course on Excel session 1.2AccessibilityStart button Microsoft ExcelOr Start button All Programs Office Applications Microsoft Excel 2010

If you are using one of ICs computers on campus, follow these directions to access excel.3

What is a worksheet?

Excel, as you probably know, is the worlds most widely used spreadsheet program, and is part of the Microsoft Office suite. A workbook is the Excel file in which you enter and store related data. A worksheet (also known as a spreadsheet) is a collection of cells on a single sheet where you actually keep and manipulate the data.

4Entering and Formatting your dataMove the cell pointer to the cell you want to insert data, type data, and press enter.

To format the cell, right click on the cell you wish to format, then click on Format Cells...

5Insert data: To insert data into cells, move the cell pointer to the cell you want to insert data, then click on it and start typing. You can press enter to finish entering data.Format Cell To format the cell, right click on the cell you wish to format, then click on Format Cells. A small window will pop up.

Formatting your data

6Similar to MS Word, you can make changes of font size, color, etc. within the cells.

Working with rows and columns [1]Inserting rows and columns:Click on Home TabOpen Insert box in the Top, Right hand corner.

Or Right click at the cell and then click insert

Inserting rows and columns: Click on Home Tab, then open Insert box in the Top, Right hand corner. A small window will pop up so you can select what you want to insert.You can also right click at the cell and click on Insert to open the window.

7Working with rows and columns [2]Deleting rows and columns by right click at the row/column then click at Delete..

Creating and Using FormulasOperator such as + (for addition) and * (for multiplication)Cell references Values or textWorksheet function (such as SUM or AVERAGE)A formula entered into a cell can consist of any of these elements:Example: = SQRT(225) + B2Worksheet functionValueCell referenceOperator

Formulas are used in Excel to calculate results from the data stored in the worksheet. When data changes, the formulas calculate updated results automatically.

9

Entering FormulasAt the cell, simply type an equal sign (=) followed by a formula. E.g. A6 = A1 + A5. OR In order to enter a formula, simply choose the cell and click on the Formula tab on top Different formulae can be entered depending on the operation. Sum (under AutoSum)Rate (under Financial)Search (under Text)And / Or (under Logical)

To enter a formula into a cell, you can select the Formula tab on top and choose the formula you want. You can also enter formula directly by typing equals and the function name

10

Inserting a function into a formulaSimilar to creating a formula, to insert a Function, click on Formulas, and then onFunction CategoriesFunction Description

11Similar to creating formulas, to insert a Function, click on Formulas tab, and then click on Insert Function button.Using References in Formulas [1]Relative: The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.

Absolute: The row and column references do not change when you copy the formula because the reference is to an actual cell address.

Mixed: Ether the row or column reference is relative or absolute

There are 3 types of references you can use:

12Referencing allows users to copy and paste to move a formula to a different cell and the cells in that formula will automatically update to the position that it moved to. E.g. if your formula in cell A3 is A1+A2 and you copy the formula to B3, the formula will update to B1+B2.Using References in Formulas [2]ExampleRelative Reference: =(A17+A18)/A19Absolute Reference: =($A$17+$A$18)/A19Mixed Reference: =(A$17+$A18)/A19$ ARE IMPORTANT

Relative looks normal, absolute has dollar signs in from of the row and column (dollar signs are important), and mixed is a combination of both.13Referencing cells outside the worksheets [1]To use a reference to a cell in another worksheet in the same workbook, use this format: SheetName!CellAddress

Example: =A1 * Sheet2!A1To use a reference to a cell in a worksheet in a different workbook, simply insert its file name: [WorkbookName]SheetName!CellAddress

Example: =[Budget.xls]Sheet1!A1

This is how you reference another worksheet in excel.14Referencing cells outside the worksheets [2]If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks.= A1 * [Budget For 2003]Sheet1!A1Here is a formula that refers to a cell on Sheet1 in a workbook name Budget for 2003

This is an example on how to reference to another workbook.15

Making Charts [1]In order to insert a chart, simply click on Insert, and then choose which chart to be utilized.

Depending on how the graph should look like, different graph icons can be used.

Column GraphLine Graph Pie Graph Bar Graph Area GraphScatter GraphOther Graphs

1. 2. 3. 4. 5. 6. 7.

In order to insert a chart, simply click on Insert Tab, and then choose which chart you want to use. Depending on how the graph should look like, different graph icons can be used: Column, Line, Pie, Bar, Area, Scattler, and other Graph.

16

Making Charts [2]In the second step, the data that is to be used has to be specified.In order to do that, click on Design after inserting the chart, and then on the Select data icon.

Make sure that you have your data already before creating a chart. Click on Design after inserting the chart, and then on the Select data icon. A window will pop up for you to add new series, edit or delete old series, switch graphs between rows to columns or vice versa, etc.

17

Making Charts [2 cont] Once the below window opens up, New series can be added. Existing series can be deleted or edited.Graphs can be switched from Rows to Columns. Adds New Series to the GraphSwitches between Rows and Columns Edits existing SeriesRemoves Series

Make sure that you have your data already before creating a chart. Click on Design after inserting the chart, and then on the Select data icon. A window will pop up for you to add new series, edit or delete old series, switch graphs between rows to columns or vice versa, etc.

18

Making Charts [3]The last step of creating a chart is filling the graph with labels such as Chart title, Axes titles, Legend, Data labels etc. To do that, click on Layout under Chart Tools. From there, each icon represents a different title or label. To Insert a Chart TitleTo Insert Axes TitleTo Modify Location of LegendTo Create Data LabelsTo Show or Hide Data Table

If you go to the chart layout tools, you can change the chart title, axis, legend, and labels.19

Making Charts [4]It can be specified where to place the New Created Chart, by clicking on Design under Chart Tools, and then by clicking on Move Chart Location. The Chart can be either saved Onto a New sheet OR Onto an existing Sheet

20If you go to the chart tools design ribbon, you can change the style of the chart. You can also move the chart to a new sheet if you want to.SavingClick on File tab.Click Save or Save As > Excel Workbook (.xlsx) or other types.

If you save your document, it will be different that in 2007. You will be prompted with a dialog box as shown on this this and you can select the file format you need. If you need backwards compatibility then select .xls.21Printing (1)Select the area you would like to printGo to the File tab located on the top right hand corner. And then go to Print.

The 2010 office has a new print screen. It shows all the options and the print button on one screen rather than a popup. 22

Printing (2)If you would like to fit all the worksheet into one page (in the case that the worksheet is bigger than A1 paper), simply go to Page Layout, and change the Width and the Height to 1 page rather than Automatic, under Scale to Fit. Change both the Width and the Height to 1 page

Using scale to fit allows users to have a worksheet fit to a certain physical dimension. If you want it to be 1page you can set the height and width to 1 page so all the data will fit. However, if you have too much data it will make the text very small and not readable. 23Printing (3)FormulasHold CTRL + ~

You may need to show your formulas so if you do ctrl+~ you will be able to see your formulas.24Questions?If any further questions arise, please contact a lab consultant in any of the following Instructional Computing Labs.

SSB 103 SSB 452Benton Hall 232 Math TLC (UC 050) Ward E. Barnes Library Thomas Jefferson Research Commons

25Additional Informationhttp://www.cetc.umsl.eduThe Microcomputer Program at the University of Missouri-St. Louis offers day and evening computer courses as part of the Chancellor's Certificate series. http://www.umsl.edu/stg The Online Student Technology Guide has answers to many computing questions.http://help.umsl.eduOnline self-service solutions for UMSL Technology Issues