Lecture 7 Desktop Publishing IV – Spreadsheet Software

1. Lecture 7Desktop Publishing IV – Spreadsheet Software Introduction to Information Technology Dr. Ken Tsang 曾镜涛 Email:kentsang@uic.edu.hk http://www.uic.edu.hk/~kentsang/IT/IT3.htm Room E408 R9 With thanks to Dr. A. Zhang, Dr. Haipeng Guo, and Dr. David Chen

2. Outline • What is Spreadsheet Software? • OpenOffice Calc Basics • Performing Calculations • Creating Chart

3. Spreadsheet Software • Another widely used application software • Allows users to organize data in rows and columns and perform calculations on the data • Rows and columns collectively are called worksheets • Features from word processing • Spreadsheet organization • Up to 255 worksheets • 256 columns, and 65,536 rows • Letters identify columns (A … IV) • Numbers identify rows (1 …65536)

4. Spreadsheet Software • Cells • A cell is the intersection of a column and a row • 256 * 65,536 cells • Identifies cells by the column and row, e.g. B6 • Contains three types of data: labels, values, and formulas • Calculations • Value – a number used in a calculation • Formula – performs calculation to generate values • Function – predefined formula

5. Spreadsheet Software • Recalculation • One of the powerful features • Making manual changes can be time-consuming and may result in new errors • Making changes in an electronic worksheet is much easier and faster, and more accurate • Charting • Depicts data in graphical form • Line chart, shows a trend during a period of time • Column chart, displays bars of various lengths to show the relationship of data • Pie chart,shows the relationship of parts to a whole

6. OpenOffice Calc • Tool to create professional spreadsheets and charts http://www.openoffice.org/product/calc.html • Calc window • Many elements - Title bar, Menu bar, Tool bars, Formula bar, Worksheets, and Status bar • Similar to OpenOffice Writer

8. Calc Window • Formula bar • Name box • Function Wizard, Sum, Function • Cell entries (data) • View > Formula bar • Worksheets • Workbook • 3 worksheets by default • Cell address: A1, B6, E10, ...

9. Calc Window • Status bar • View > Status bar • Statistics

10. Working with Worksheets • Start a workbook • Click New icon or choose File > New • Enter text • Simply click a cell and type • Enter numbers • As values – right-aligned • As labels – left-aligned • Automatic increase • Drag the black square in the bottom-right corner of a cell

11. Working with Worksheets • Formatting numbers • Add commas to separate thousands • Specify number of decimal places • Place a dollar sign ($) in front of the number • Display as a percent • Display as Date, time • Several other options • Format > Cells > Number

12. Working with Worksheets • Selecting cells • F8 function key click the up-left first, press F8, click the lower-right • mouse to drag

13. Working with Worksheets • Moving quickly between cells • Name Box • F5 • Resizing rows and columns • Dragging the side line • Format > Row/Column > Height/Width • Adding and renaming worksheets • Worksheet tabs • Add – Insert > Worksheet • Rename – right-click the tab and select Rename

14. Performing Calculation • A distinguishing feature • Otherwise it is not more than a large table • Formula calculations • Must begin with equal sign “=” • Performs calculations and displays the result • Includes cell addresses • Visible in the cell entries of the formula bar after execution • Point mode • Enter a formula without typing cell addresses • By clicking cells or using arrow keys

15. Formula Calculation • Example • Calculating the sub total for a number of textbooks. • The formula multiplies the quantity and price of each textbook and adds them together • Formula is shown in the formula bar after

16. Performing Calculation • Cell addressing • Identified by the column and row, e.g. B6 • Records cell addresses in formulas in three ways • Relative referencing • Calling cells by just their column and row labels • Cell addresses will be changed when copy them e.g. C1 "=(A1+B1)"  C2 "=(A2+B2)" • Absolute referencing • Accomplished by placing dollar signs "$" e.g. "=($A$1+$B$1)" • Mixed referencing • Only the row or column is fixed. e.g. "=(A$1+$B2)"

17. Performing Calculation • Reference operators refer to a cell or group of cells • Range operator “:” • TWO cell addresses separated by a colon • Refers to ALL the cells included in the reference e.g. “A1:C3” includes A1, A2, A3, B1, B2, B3, C1, C2, and C3. • Union operator “,” • Two or more cells separated by a comma • Refers ONLY to the cells (rather a range) • e.g. “A7,B8,C9” includes only cells A7, B8, and C9

18. Performing Calculation • Linking worksheets • Use the value from a cell in another worksheet • Format: "sheet_name!cell_address" e.g.: "=A1+Sheet2.A2"

19. Performing Calculation – Functions • Functions • Allow you to quickly perform calculations • More efficient way than typing formula • formula: "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10" • function: "=SUM(D1:D10)" • Format for using functions • Use an equal sign to begin a formula • Specify the function name • Enclose arguments within parentheses • Use a reference operator (a comma “,” or colon “:”) to separate arguments

20. Functions

21. Performing Calculation –Functions • The Sum icon • Automatically adds a column of numbers • Highlight all cells above current cell and add • For empty column, Sum adds the row values • Recalculations • Automatically recalculate when changing cell entries • Tools > Cell Content> Recalculate

22. Performing Calculation –Sort • Sort on one column • Sort Ascending button • Sort Descending button • Sort on multiple columns • Highlight all columns • Data > Sort • Select 1st column (key word) in Sort By field • Select 2nd and 3rd column in Then By field. • Choose Header row or No header row box

23. Creating Charts • Charting – Another important feature • Represent data in a visual format • Often makes it easier to see the relationship • Three popular types: • Line chart – shows a trend during a period of time • Column chart – displays bars of various lengths • Pie chart – shows the relationship of parts to a whole • Chart will automatically update if data changes • Chart Wizard • Easy way to create charts • Highlight all the cells and click Chart Wizard button

24. Chart Wizard • Step 1: Chart Type • Choose the Chart type and the subtype if necessary from the first dialog box of Chart Wizard • Click Next after selection

25. Chart Wizard • Step 2: Data Range • Select the data range (if different from the area highlighted) by clicking the icon on the right • You can choose to chart the data by columns or rows • You can also choose to make the first row or column to be the label or not • Press Next after you select

26. Chart Wizard • Step 3: Data Series • Click the data series and modify the name and X or YValues in the data range • If you want to change select different range for name or categories, click the icon on the right to select • Press Next to move to the next step

27. Chart Wizard • Step 4: Chart Elements • Enter the title and subtitle of the chart, and that of the X and Y axes • Other options for the grid lines and legend • Press Finish after selection to create the chart

28. Editing Charts • Editing a chart • Right click the chart, select Edit in the pop-down menu, then a different menu will be generated for chart editing • If you want to edit the format of the chart, you can select any relating selection – Format > Title, Axis, Grid, Legend, Chart wall, Chart area, Chart types, Data ranges, etc. • Select an object in the chart, you can edit its properties -- Format > Object Properties • Note that any part of a chart you want to format can be selected

29. Summary • Spreadsheet software • Allows users to organize data in rows and columns and perform calculations on the data • OpenOffice Calc • Windows • Worksheets • Performing calculations • Creating charts