Staroffice 5.2 Calc Handbook

by Habraken, Joe

• ISBN13: 9780130293893
• ISBN10: 013029389X
• Format: Paperback
• Copyright: 2000-12-01
• Publisher: Prentice Hall Ptr

Summary

This is the first in-depth, task-focused guide to StarCalc, the spreadsheet component of the #1 open source software suite for Linux, Solaris, and Windows: StarOffice. You'll start by getting comfortable in the StarOffice Calc environment: working with the StarOffice Beamer, using the Gallery, exploring different spreadsheet views, saving your spreadsheets, and getting Help. Next, you'll walk through the fundamentals of entering values, formulas, and functions; working with cells, rows, and columns; formatting cells; and more. The book includes practical coverage of editing and auditing formulas, formatting spreadsheets for different uses, working with lists and databases. You'll find detailed coverage of advanced data analysis with Simply StarOffice Calc, including subtotals, consolidation, sensitivity analysis, goal seek, scenarios, charting, and more. There's detailed coverage of printing your spreadsheets, including a full chapter on publishing spreadsheets to the Web. For all Linux and UNIX users seeking detailed guidance on making the most of StarOffice Calc.

Table of Contents

Preface	xvii
Acknowledgments	xix
Getting Started with StarOffice Calc	1	(30)
Starting StarOffice	2	(1)
To Launch StarOffice	2	(1)
Working on the StarOffice Desktop	3	(6)
To Use the Menu Bar	5	(1)
To Use the Desktop Toolbars	6	(3)
Using the Explorer	9	(2)
Organizing with Explorer Groups	11	(3)
To Create a New Explorer Group	12	(2)
Working with the Beamer	14	(1)
Using the Gallery	14	(1)
Manipulating the Tips Window	15	(1)
Opening StarOffice Calc	16	(3)
Understanding Spreadsheet Geography	19	(1)
Exploring Different Spreadsheet Views	20	(6)
To Hide the Column and Row Headings	20	(1)
To Add Value Highlighting	21	(1)
To Use Page Break Preview	21	(1)
To Use the Zoom	22	(1)
To View Full Screen	23	(1)
To Freeze Column and Row Headings	24	(1)
To Split Calc Windows	24	(2)
Getting Help	26	(4)
To Use Tips and Extended Tips	26	(1)
To Use the Help Contents	27	(2)
To Use the Help Agent	29	(1)
Closing Calc and Exiting StarOffice	30	(1)
Creating Basic Spreadsheets	31	(30)
Entering Spreadhseet Data	32	(1)
Entering Text	32	(3)
To Enter Text in Your Spreadsheet	33	(1)
To Enter Numbers as Textt	33	(1)
To Utilize Autolnput	33	(1)
To Use the Selection List	34	(1)
Entering Values	35	(5)
To Understand Formulas and Functions	35	(1)
To Enter a Formula	36	(1)
To Enter a Function	36	(2)
To Use the SUM Function	38	(2)
To Enter Dates	40	(1)
Saving Your Spreadsheets	40	(3)
To Save Your Calc Spreadsheet	41	(1)
To Save the Spreadsheet with a Password	42	(1)
To Save Changes to the Spreadsheet	43	(1)
Using the Version Feature	43	(3)
To Create a Version of a Calc Spreadsheet	44	(2)
Adding File Properties	46	(1)
Opening a Spreadsheet	47	(1)
To Open a Particular Spreadsheet	47	(1)
Working with Multiple Windows	48	(1)
Navigating a Calc Spreadsheet	49	(2)
To Move from Cell to Cell	49	(1)
To Move from Sheet to Sheet	50	(1)
Working with Cell Ranges	51	(2)
To Select Cell Ranges	52	(1)
To Select Columns and Rows	52	(1)
Basic Editing Techniques	53	(4)
To Edit during Data Entry	53	(1)
To Clear a Cell's Contents	54	(1)
To Copy, Cut, and Paste Data	55	(1)
To Change a Column Width	56	(1)
Using Undo, Redo, and Repeat	57	(1)
Filling Cells Automatically	57	(2)
To Automatically Fill Cells	58	(1)
Creating Your Own Spreadsheets	59	(2)
Working with Cells, Rows, and Columns	61	(28)
Formatting Cell Attributes	62	(1)
Formatting Numbers	62	(3)
To Format a Range of Cells	63	(2)
Understanding Number Formats	65	(5)
To Apply Cell Number Formats	65	(1)
To Apply the Currency Format	66	(1)
To Apply Decimal Places and Leading Zeros	67	(1)
To Apply the Date and Time	68	(1)
To Create Your Own Format	68	(2)
Working with Font Attributes	70	(4)
To Change the Font Attributes	72	(2)
Working with Colors, Borders, and Backgrounds	74	(3)
To Change the Font Color	74	(1)
To Change the Borders	75	(1)
To Change the Background	76	(1)
Aligning Cell Entries	77	(4)
Copying Cell Formats	81	(1)
To Copy Cell Formats	81	(1)
Using AutoFormat	82	(1)
To Use AutoFormat	82	(1)
To Create an AutoFormat	83	(1)
Setting Column Widths	83	(1)
Setting Row Heights	84	(1)
Transposing Column and Row Information	85	(4)
Enhancing Your Spreadsheets	89	(30)
Inserting Spreadsheet Elements	90	(4)
To Insert Cells	90	(1)
To Insert Columns	91	(1)
To Insert Rows	92	(1)
To Insert Sheets	92	(2)
Renaming Sheets	94	(1)
Deleting Elements	94	(2)
To Remove Cells	95	(1)
To Remove Columns and Rows	95	(1)
To Remove Sheets	96	(1)
Hiding Rows and Columns	96	(1)
To Hide Rows	96	(1)
To Hide Columns	97	(1)
Hiding Sheets	97	(1)
Protecting Cells	98	(4)
To Hide Cells with Cell Protection	98	(2)
To Protect Cells from Data Entry	100	(2)
Merging Cells	102	(1)
Aligning Information in Merged Cells	102	(1)
Using Special Alignment Settings	103	(2)
To Alter the Distance from Gridlines	104	(1)
To Alter the Wrapping to Text in a Cell	104	(1)
To Rotate Text and Numbers	104	(1)
Using the Find and Replace Features	105	(4)
To Use Replace	107	(1)
To Use the Search Options	108	(1)
Using the Spellchecker and AutoSpellcheck	109	(1)
Working with AutoCorrect	110	(2)
Using the Thesaurus	112	(1)
Tracking Changes	113	(4)
To Track Changes	114	(1)
To Accept or Reject Changes	114	(2)
To Set Redlining Options	116	(1)
Inserting Notes	117	(2)
Working with Formulas and Values	119	(22)
Creating Spreadsheet Formulas	120	(5)
To Use Operators	120	(1)
To Understand Operator Precedence	121	(1)
To Reference Cells	122	(1)
To Reference Cells Automatically	123	(1)
To Write a Formula	123	(1)
To Edit a Formula	124	(1)
Referencing Cells on Other Sheets and Spreadsheets	125	(2)
To Reference Cells on Another Sheet	126	(1)
Copying and Moving Formulas	127	(2)
To Copy a Formula	128	(1)
To Move a Formula	128	(1)
Naming Cells and Ranges	129	(4)
To Name a Cell Range	129	(1)
To Create Cell Names from Existing Labels	130	(1)
To Insert Cell Names in a Formula	131	(1)
To Modify Cell Names	132	(1)
Working with the Navigator	133	(1)
Using the Calc Detective	134	(3)
To Trace Precedents	134	(2)
To Trace Dependents	136	(1)
To Trace Errors	136	(1)
Using Special Data Entry Techniques	137	(1)
To Enter Data in a Range	138	(1)
Working with Data Series and the Fill Feature	138	(3)
To Create a Data Series	138	(1)
To Create a Geometric Growth Series	139	(2)
Working with Functions	141	(24)
Understanding Calc Functions	142	(1)
Understanding Absolute References	143	(1)
Inserting Functions	144	(5)
To Use the Function List	145	(1)
To Use the Function AutoPilot	146	(3)
Working with Basic Functions	149	(7)
To Use COUNT, MAX, and MIN	150	(1)
To Use the PMT Function	151	(3)
To Use Time and Date Functions	154	(2)
Using Advanced Functions	156	(4)
To Use the IF Function	156	(2)
To Use the LOOKUP Functions	158	(2)
Using Validity Rules	160	(2)
To Set Validity Criteria	160	(1)
To Find Invalid Data Using the Detective	161	(1)
Checking Functions with the AutoPilot	162	(1)
Understanding Common Error Messages	163	(1)
Using Notes with Formulas and Functions	164	(1)
Working with Styles and Templates	165	(18)
Understanding Styles	166	(1)
Creating and Modifying Styles	166	(6)
To Create a Style Based on a Selected Cell	166	(1)
To Apply a Style	167	(1)
To Update a Style	168	(1)
To Use the Fill Format Mode	168	(1)
To Modify a Style	169	(2)
To Create a Style from Scratch	171	(1)
Finding and Replacing Styles	172	(1)
To Find and Replace a Style	172	(1)
Working with Templates	173	(5)
To Use an Existing Template	174	(2)
To Create a Template	176	(1)
To Use Your Template	177	(1)
Using Conditional Formatting	178	(2)
To Create a Style for Conditional Formatting	179	(1)
To Configure Conditional Formatting	179	(1)
Formatting Spreadsheets for a Specific Purpose	180	(3)
Working with Lists and Databases	183	(22)
Using Lists and Databases in Calc	184	(1)
Understanding Calc Database Design	184	(4)
To Define Database Areas	185	(1)
To Plan a Database	186	(2)
Creating a Database	188	(2)
To Create a Database Table	188	(1)
To Define the Database Area	188	(1)
To Change a Database Range	189	(1)
Sorting Data	190	(4)
To Sort with the Main Toolbar Sort Tools	190	(1)
To Sort Using the Sort Dialog Box	191	(1)
To Create a Complex Custom Sort	192	(2)
Filtering Data	194	(5)
To Use AutoFilter	194	(2)
To Set Up a Standard Filter	196	(1)
To Set Up an Advanced Filter	197	(2)
Working with the Outline Feature	199	(1)
To Create Outline Groups	199	(1)
To Collapse or Expand Outline Groups	200	(1)
Exporting and Importing Calc Data	200	(5)
To Export Data	201	(1)
To Import Data	201	(1)
To Import Data with the Beamer	202	(3)
Advanced Data Analysis and Macros	205	(26)
Understanding Advanced Data Analysis	206	(1)
Working with Subtotals	206	(3)
To Create Subtotals	207	(1)
To Copy and Undo Subtotals	208	(1)
Consolidating Data	209	(3)
To Name Areas for Consolidation	210	(1)
To Consolidate Areas	210	(2)
Conducting a Sensitivity Analysis with Multiple Operations	212	(4)
To Create the Multiple Operations Function and Table	213	(1)
To Specify Input Areas for Multiple Operations	214	(2)
Using Goal Seek	216	(3)
To Set Up the Goal Seek Function	217	(1)
To Use Goal Seek	217	(2)
Creating Scenarios	219	(4)
To Create a Scenario Spreadsheet	219	(1)
To Create a Scenario	219	(3)
To Create a Second Scenario	222	(1)
Working with the DataPilot	223	(3)
To Create a DataPilot Table	223	(2)
To Filter a DataPilot Table	225	(1)
To Format and Edit a DataPilot Table	226	(1)
Understanding Calc Macros	226	(5)
To Record a Macro	227	(1)
To View a Macro Script	228	(1)
To Play a Macro	229	(1)
To Assign a Shortcut Key to a Macro	229	(2)
Charting Data in Calc	231	(22)
Using Charts in Calc	232	(4)
Preparing Spreadsheets for Charts	236	(1)
Understanding Chart Geography	236	(3)
Creating a Chart	239	(5)
To Insert a Chart Using the AutoFormat Chart Wizard	239	(2)
To Move and Size a Chart	241	(1)
To Modify the Anchor Type	242	(1)
To Change the Data Area for a Chart	243	(1)
Working with the StarOffice Chart Tools	244	(8)
To Add/Remove Gridlines	245	(1)
To Modify Titles and Labels on a Chart	246	(1)
To Add Data Labels	246	(1)
To Change Data Point Colors	247	(3)
To Change the Chart Area Color	250	(1)
To Change the Chart Type	250	(1)
To Restart the AutoFormat Chart Wizard	251	(1)
Copying Charts to Other StarOffice Applications	252	(1)
Working with Graphics and Objects	253	(18)
Using Graphics and Objects in Calc	254	(1)
Inserting Graphics	254	(2)
To Insert a Graphic from a File	254	(1)
To Move a Graphic	255	(1)
To Size a Graphic	256	(1)
Using the Image Editor	256	(2)
To Insert a Graphic from the Image Editor	257	(1)
To Edit an Image Editor Graphic	258	(1)
Inserting a Graphic from a Scan	258	(1)
To Insert a Scanned Image	258	(1)
To Edit a Scanned Image	259	(1)
Inserting an OLE Object	259	(3)
To Insert a New OLE Object	259	(2)
To Insert an OLE Object from a File	261	(1)
To Edit an OLE Object	261	(1)
Inserting Sounds and Video	262	(2)
To Insert a Sound or Video	262	(1)
To Play a Sound or Video	263	(1)
Inserting a Plug-In	264	(1)
To Insert a Plug-In	264	(1)
Inserting an Applet	265	(1)
To Insert an Applet	265	(1)
To Use the Applet	266	(1)
Inserting a Formula	266	(2)
To Insert a Formula	267	(1)
To Edit a Formula	268	(1)
Formatting Graphics and Objects	268	(3)
To Move and Resize Elements	268	(1)
To Arrange Elements	269	(1)
To Anchor Elements	269	(1)
To Edit Objects	269	(2)
Printing Spreadsheets	271	(20)
Understanding the Print Process	272	(1)
Working with Page Preview	272	(2)
To Use Page Preview	272	(1)
To Zoom in Page Preview	273	(1)
Using Print Ranges	274	(3)
To Create a Print Range	275	(1)
To Edit a Print Range	275	(2)
To Remove a Print Range	277	(1)
Working with Page Formatting	277	(4)
To Set Margins and Page Orientation	277	(2)
To Create Borders	279	(1)
To Create a Background	280	(1)
Working with Headers and Footers	281	(4)
To Create Headers	281	(2)
To Create Footers	283	(1)
To Select Sheet Attributes	284	(1)
Using Page Styles	285	(2)
To Create a Page Style	285	(1)
To Apply a Page Style	286	(1)
Working with Page Breaks	287	(1)
To Enter Page Break Preview	287	(1)
To Insert Manual Page Breaks	288	(1)
Working with Printers	288	(3)
To Check the Default Printer	289	(1)
To Print Your Spreadsheet	289	(2)
Inserting Calc Data in Documents and Web Pages	291	(16)
Using Calc Data in Other Applications	292	(1)
Sharing Calc Data with Writer	292	(7)
To Use the AutoPilot to Create a Document	292	(1)
To Copy and Paste a Calc Spreadsheet	293	(1)
To Drag and Drop a Calc Spreadsheet	294	(1)
To Edit Pasted Data	295	(1)
Linking Calc Spreadsheets to Other Documents	296	(1)
To Copy a Calc Spreadsheet as a DDE Object	296	(2)
To Edit a Calc DDE Link	298	(1)
To Edit the Contents of a DDE Link	298	(1)
Understanding Web Pages	299	(1)
Creating Web Pages	299	(2)
Combining Calc and Web Pages	301	(3)
To Save a Calc Spreadsheet in HTML Format	302	(1)
To View a Calc HTML File	302	(2)
Working with Hyperlinks	304	(3)
To Type in a Hyperlink	304	(1)
To Paste a Hyperlink	305	(1)
To Insert a Hyperlink as a Button	305	(2)
Customizing and Configuring StarOffice Calc	307	(24)
Controlling Your Workspace Environment	308	(1)
Working with General Options	308	(8)
To Set User Data Options	308	(1)
To Set Save Options	309	(1)
To Set Path Options	310	(1)
To Set Language Options	311	(2)
To Set Color Options	313	(1)
To Set Print Options	314	(1)
To Set Font Replacement Options	314	(1)
To Set View Options	315	(1)
To Set Desktop Options	315	(1)
To Set Other Options	316	(1)
Working with Calc Custom Options	316	(4)
To Set Contents Options	316	(1)
To Set Layout Options	317	(1)
To Set Input Options	318	(1)
To Set Grid Options	318	(1)
To Set Sort List Options	319	(1)
To Set Calculate Options	319	(1)
To Set Changes Options	320	(1)
To Set Other Options	320	(1)
Configuring Menus	320	(5)
To Confirm Menu Configuration Changes	321	(1)
To Change a Menu's Structure	322	(1)
To Move an Existing Menu Command	322	(1)
To Delete an Existing Menu Item	323	(1)
To Modify an Existing Menu Item	323	(1)
To Create a New Menu (to the Right of Help)	323	(1)
To Assign a New Function to a Menu Item	324	(1)
To Change a Menu Name	325	(1)
Configuring Toolbars	325	(6)
To Confirm Toolbar Configuration Changes	325	(1)
To Display or Hide a Toolbar by Default	326	(1)
To Create a New Toolbar and Add Buttons	326	(3)
To Change a Toolbar Name	329	(1)
To Display or Hide Toolbar Buttons	330	(1)
To Modify Existing Toolbar Buttons	330	(1)
Appendix A Calc Shortcut Keys	331	(4)
Index	335