The Unofficial Guide to Excel 2003

by Kelly, Julia

• ISBN13: 9780471763215
• ISBN10: 0471763217
• Format: Paperback
• Copyright: 2006-02-27
• Publisher: Wiley

Summary

To use Microsoft(r) Excel(r) with confidence you'll need to know its quirks and shortcuts.Find out what the manual doesn't always tell you in this insider's guide to using Excel in the real world. What are the best practices used by pros? Are there shortcuts for repetitive tasks? From writing formulas to using charts, first get the official way, then the best way from an expert. * Unbiased coverage of how to get the most out of Excel, create workbooks and templates, and get comfortable with the Excel environment * Savvy, real-world advice, to help you set up, enter, format, and organize your data * Time-saving techniques and practical guidance on creating custom macros with VBA, using PivotTables and PivotCharts, and sharing data * Tips and hacks on how to use pre-set macros, AutoFill double-click, and other useful shortcuts to save you time * Sidebars and tables featuring toolbar buttons and more ways to beproductive in Excel Watch for these graphic icons in every chapter to guide you to specific practical information. Bright Ideas are smart innovations that will save you time or hassle. Hacks are insider tips and shortcuts that increase productivity. When you see Watch Out! heed the cautions or warnings to help you avoid common pitfalls. And finally, check out Inside Scoops for practical insights from the author. It's like having your own expert at your side!

Author Biography

Julia Kelly lives in rural north Idaho with horses, cats, small garden dogs, assorted wild animals, free-range chickens, and bird dogs who’ve finally learned not to “retrieve” the free-range chickens.

Table of Contents

Introduction	xxi
I First Things First	1	(66)
1 Getting In and Out	3	(42)
Starting Excel	3	(1)
Starting with the Quick Launch toolbar	3	(1)
The Excel environment	4	(10)
Task panes	4	(3)
The Formula bar	7	(1)
Menus	8	(1)
Toolbars	9	(4)
The Status bar	13	(1)
Getting help	14	(7)
The Ask A Question box	15	(3)
The dialog box ? button	18	(1)
That silly Office Assistant	18	(2)
Excel, heal thyself	20	(1)
Searching the Microsoft Knowledge Base	20	(1)
Opening workbook files
When Excel is closed	21	(4)
When Excel is already
Opening several files at	25	(9)
Multiple windows	25	(2)
Multiple taskbar buttons	27	(1)
Searching for a file	27	(7)
Saving workbook files	34	(3)
Setting workbook properties	35	(1)
Using Hyperlinks
AutoRecover	36	(1)
Creating and using templates	37	(5)
Creating your own templates	38	(2)
Using Excel's prebuilt templates
Changing Excel's default templates	40	(7)
Change the default workbook template	41	(1)
Change the default worksheet template	42	(1)
Closing workbook files	42	(1)
Quitting Excel	43	(1)
Just the facts	43	(2)
2 All About Worksheets	45	(22)
Worksheet basics	46	(1)
Selecting worksheets	47	(3)
Selecting a single worksheet	47	(1)
Selecting several worksheets	48	(1)
Shoot multiple worksheets	49	(1)
Deselecting a group of worksheets	50	(1)
Naming worksheets	50	(1)
Sheet tab colors	51	(1)
Rearranging worksheets	51	(3)
Moving worksheets	51	(1)
Copying worksheets	52	(1)
Moving or copying worksheets to another workbook	52	(2)
Adding and deleting worksheets	54	(2)
Changing the default number of worksheets	55	(1)
Hiding worksheets	55	(1)
Moving around in a worksheet	56	(5)
Selecting cells	57	(1)
Freezing panes	58	(1)
Splitting panes	59	(2)
Multiple windows	61	(3)
Opening windows in the same workbook	61	(1)
Opening windows in different workbooks	62	(1)
Closing windows	63	(1)
Comparing side by side	63	(1)
Zooming your view	64	(1)
Just the facts	65	(2)
II Getting the Data In	67	(102)
3 Entering and Editing Data	69	(40)
Entering data directly	70	(2)
Punctuation for automatic formatting	70	(1)
Punctuation for numeric formatting	70	(1)
Punctuation for date or time formatting	71	(1)
Data entry techniques	72	(13)
Enter data in a single cell	73	(1)
Enter data in a table	74	(1)
Enter numbers with a fixed decimal	75	(1)
Enter fractions	76	(1)
Start a new line in a cell	76	(1)
Make the same entry in several cells	77	(1)
AutoFill a text entry	78	(1)
AutoFill numbers	79	(1)
Repeat an entry in the column	80	(2)
AutoComplete	80	(1)
Pick From List	81	(1)
Auto Correct	82	(2)
Create a custom list	84	(1)
Delete data	84	(1)
Undo an action	85	(1)
Creating a dynamic list	85	(2)
Creating a data form	87	(1)
Validating data during entry	88	(2)
Editing cell entries	90	(1)
Finding and replacing data	91	(3)
Find a string	91	(2)
Replace a string	93	(1)
Moving and copying data	94	(4)
Between workbooks and worksheets	95	(1)
Paste items	95	(3)
Inserting, deleting, and rearranging	98	(3)
Delete a row or column	98	(1)
Insert a row or column	99	(1)
Insert and delete cells	100	(1)
Rearrange rows, columns, and cells	100	(1)
Breaking a column into multiple columns	101	(2)
Adding worksheet comments	103	(2)
Checking your spelling	105	(2)
Just the facts	107	(2)
4 Working Data Magic with Calculations	109	(28)
Simple calculations, quick answers	109	(4)
AutoCalculate	109	(2)
AutoSum	111	(1)
Use the calculator	112	(1)
About cell references	113	(3)
Cell reference types	114	(1)
Changing reference types	115	(1)
Writing formulas	116	(2)
Arithmetic operators	116	(1)
Simple formulas	117	(1)
Moving and copying formulas	118	(2)
Copy a formula cell	118	(1)
Copy with AutoFill	119	(1)
Linking worksheets and workbooks	120	(2)
Formulas that link worksheets	120	(1)
Formulas that link workbooks	121	(1)
Using cell names	122	(8)
Naming cells	122	(4)
The Name box	123	(1)
The Create Names dialog box	124	(1)
The Define Name dialog box	124	(2)
Edit names	126	(1)
Delete names	126	(1)
Using names in formulas	127	(1)
Paste names	128	(1)
Apply names	128	(2)
Editing formulas	130	(1)
Tracing a formula	131	(1)
Locating worksheet errors	132	(3)
Locating errors in formulas	133	(1)
Finding invalid data in a worksheet	134	(1)
Just the facts	135	(2)
5 All About Functions	137	(32)
Using functions	137	(3)
Common and useful functions	140	(20)
AVERAGE	141	(1)
SUM functions	141	(5)
SUM	142	(1)
SUMIF	142	(2)
SUBTOTAL	144	(2)
MAX and MIN	146	(1)
COUNT functions	147	(2)
VLOOKUP	149	(1)
IF	150	(1)
PMT	151	(2)
NOW and TODAY	153	(1)
Text functions	154	(1)
CONCATENATE	155	(2)
OFFSET	157	(2)
The Analysis Toolpak	159	(1)
Install the Analysis Toolpak	159	(1)
CONVERT	159	(1)
Combining functions for more power	160	(7)
Dynamic range with OFFSET and COUNTA	161	(3)
Test the dynamic range	161	(1)
Use a dynamic	161	(3)
Nested IF functions	164	(1)
Text functions and CONCATENATION	165	(1)
Unique invoice numbers	165	(1)
Change letter case	166	(1)
Calculating dates and times	166	(1)
Just the facts	167	(2)
III Formatting Everything	169	(42)
6 Formatting Cells	171	(22)
The Formatting toolbar	171	(3)
Display the Formatting toolbar	172	(2)
Great formatting toolbar buttons	174	(1)
Formatting the font	174	(2)
Better borders	176	(3)
Simple borders	177	(1)
Elaborate borders	177	(1)
The Borders toolbar	178	(1)
Vibrant colors	179	(3)
Use the color palettes	179	(1)
Use the Format Cells dialog box	180	(1)
Create custom colors	181	(1)
Fit the text in the cell	182	(8)
Alignment	183	(1)
Indent	183	(1)
Rotate	184	(1)
Change column width and row height	185	(1)
Drag to fit	185	(1)
Double-click to fit	186	(1)
Wrap text	186	(1)
Hide and unhide columns and rows	187	(1)
Shrink entries	188	(1)
Merge cells	188	(1)
Transpose a table	189	(1)
Copying formatting with Format Painter	190	(1)
Automatic table formatting	191	(1)
Just the facts	192	(1)
7 Formatting Numbers and Using Styles	193	(18)
Applying number formats to cells	193	(1)
Toolbar buttons for formats	194	(1)
The Format Cells dialog box	195	(1)
Formatting dates and times	196	(2)
Formatting text entries	198	(1)
Creating custom formats	198	(2)
Applying conditional number formats	200	(2)
Converting actual values to displayed values	202	(1)
Hiding zeroes	203	(1)
Using styles to streamline formatting	204	(5)
Get out your Style box	204	(1)
Use the Style box	204	(1)
Applying a built-in style	205	(1)
Changing a style definition	206	(1)
Creating a custom style by example	207	(1)
Copying styles from other workbooks	207	(1)
Deleting a style	208	(1)
Just the facts	209	(2)
IV Organizing Data	211	(78)
8 Sorting, Filtering, and Querying	213	(24)
Sorting lists	213	(6)
Sorting a list by a single field	215	(1)
Sorting a list by more than one field	216	(1)
Sorting according to a custom order	217	(1)
Undoing a sort	218	(1)
Filtering records	219	(4)
Filter with a single criterion	220	(1)
Filter with multiple criteria	221	(3)
AND Filter or an OR Filter	221	(1)
Filter with two different fields	221	(1)
Filter with two criteria in the same field	222	(1)
Filter for a text string	222	(1)
Calculating filtered records	223	(1)
The Query Wizard — A better filter	224	(9)
Prepare to run a query	225	(1)
Define the data source	225	(3)
Run the Query Wizard	228	(4)
Reuse a query	232	(1)
Delete a query	233	(1)
Query a Web page	233	(3)
Just the facts	236	(1)
9 Finding and Comparing Answers	237	(22)
Consolidating a table	238	(2)
Subtotaling a table	240	(2)
Scenarios	242	(4)
Create scenarios	243	(2)
Create a scenario summary	245	(1)
Compare answers with data tables	246	(4)
One-input data table	246	(2)
Two-input data table	248	(2)
Goal seeking for simple answers	250	(2)
Goal Seek a simple problem	250	(1)
Goal Seek something more difficult	251	(1)
Answering complex questions with Solver	252	(5)
Install the Solver add-in	253	(1)
Solver terminology	253	(1)
A Solver example	253	(4)
Just the facts	257	(2)
10 Summarizing Data Dynamically with PivotTables	259	(1)
Using PivotTables	259	(1)
PivotTable terms	260	(1)
Creating a PivotTable	261	(5)
Change the data source range	265	(1)
Refresh data	265	(1)
Delete a PivotTable	266	(1)
The PivotTable toolbar	266	(1)
Changing PivotTable arrangement	267	(7)
Pivot the table	267	(1)
Add fields	268	(2)
Remove fields	270	(1)
Show and hide inner-field details	270	(2)
Show and hide specific details	272	(1)
Group items in a field	272	(1)
Create page fields	273	(1)
Create separate pages	274	(1)
Changing PivotTable calculations	274	(3)
Showing underlying details	277	(1)
Formatting a PivotTable	278	(1)
Format numbers	278	(1)
Format the table	279	(1)
Just the facts	279	(2)
11 Displaying PivotTables Graphically with PivotCharts	281	(1)
PivotCharts versus normal charts	282	(1)
Creating PivotCharts	282	(3)
Create a PivotChart from a PivotTable	282	(1)
Create a PivotChart from source data	283	(1)
Create a normal chart from PivotTable data	284	(1)
Rearranging PivotCharts	285	(1)
Formatting PivotCharts	286	(1)
Embedding a PivotChart on a worksheet	287	(1)
Just the facts	288	(1)
V Creating Charts and Graphics	289	(1)
12 Presenting Data Visually with Charts	291	(66)
Creating a chart	291	(4)
Create an instant chart	292	(1)
Use the Chart Wizard	292	(3)
The Chart toolbar	295	(1)
Types of charts	296	(4)
Changing the chart type	300	(1)
Changing chart data	301	(3)
Add data to an embedded chart	301	(1)
Add data to a chart sheet	302	(1)
Change the source range	302	(1)
Add a data table	303	(1)
Changing chart orientation	304	(1)
Moving and resizing an embedded chart	305	(1)
Move an embedded chart	305	(1)
Resize an embedded chart	306	(1)
Changing the chart location	306	(1)
Just the facts	307	(2)
13 Formatting Charts	309	(1)
Chart formatting possibilities	309	(1)
Formatting chart elements	310	(3)
Select a chart element to format	310	(1)
Format an element	311	(1)
Interpolate missing data points	311	(2)
Add free-floating titles	313	(1)
Delete a chart element	313	(1)
Changing axis scale	313	(1)
Reordering the series	314	(2)
Resizing and exploding a Pie chart	316	(2)
Make the pie bigger	316	(1)
Explode the pie	317	(1)
Changing 3-D perspective	318	(2)
Rotate by dragging	319	(1)
Rotate with precision	319	(1)
Adding a trendline	320	(6)
Add a trendline to a chart	321	(3)
Format the trendline and text box	324	(1)
Delete a trendline or text box	324	(1)
Use a moving average in business data	324	(2)
Adding a secondary	326	(1)
Working with data labels	326	(3)
Add data labels	328	(1)
Format data	328	(1)
Delete data labels	329	(1)
Creating picture markers	329	(2)
Saving and reusing your custom chart format	331	(2)
Save a custom chart format	331	(1)
Apply a custom chart format	332	(1)
Just the facts	333	(2)
14 Adding Pictures and Graphic Objects	335	(1)
Using the Drawing toolbar	335	(2)
Drawing basic shapes	337	(2)
Formatting for all graphic objects	339	(5)
Formatting objects	339	(2)
Properties tab	339	(1)
Colors and Lines tab	340	(1)
Protection tab	341	(1)
Change the stack order	341	(1)
Align and distribute objects	342	(1)
Group objects	343	(1)
Creating AutoShapes	344	(1)
Drawing text boxes	345	(1)
3-D and shadows	346	(1)
Creating WordArt	347	(1)
Inserting pictures and clip art	348	(3)
Creating diagrams and organization charts	351	(1)
Pasting pictures of cells	352	(2)
Put the Camera button on a toolbar	353	(1)
Take a picture	353	(1)
Just the facts	354	(3)
VI Printing	357	(1)
15 Printing Just What You Need	359	(32)
Previewing printed pages	359	(2)
Printing a quick copy of a worksheet	361	(1)
Printing part of a worksheet	361	(4)
Set a print area	361	(1)
Change the print area	361	(1)
Set multiple print areas	362	(1)
Remove a print area	362	(1)
Print a selected range	362	(1)
Print specific pages	363	(2)
Printing a chart	365	(1)
Print a chart sheet	365	(1)
Print an embedded chart	365	(1)
Printing several worksheets	366	(1)
Just the facts	367	(2)
16 Formatting the Printed Page	369	(1)
Setting page breaks	369	(2)
Changing the page layout	371	(5)
Change page orientation	371	(2)
Change paging order	373	(1)
Change page centering	373	(1)
Change page margins	374	(7)
Set margins precisely	375	(1)
Set margins visually	375	(1)
Printing a specific number of pages	376	(2)
Printing row/column labels on every page	378	(3)
Creating a header or footer	381	(4)
Use a prebuilt header or footer	382	(1)
Create a custom header or footer	382	(2)
Insert a picture	384	(1)
Edit and customize your headers/footers	384	(1)
Printing worksheet gridlines	385	(1)
Printing in black and white	385	(1)
Formatting printed charts	385	(4)
Resize a printed chart	385	(3)
Use the page margins	386	(1)
Use the Page Setup dialog box	387	(1)
Print data markers in black and white	388	(1)
Just the facts	389	(2)
VII Sharing Data with Other Users and Other Programs	391	(1)
17 Sharing a Workbook with Other Users	393	(36)
Sharing a workbook	394	(2)
Who's using a shared workbook?	395	(1)
Saving a shared workbook	395	(1)
Reviewing changes in a shared workbook	396	(6)
Resolving change conflicts	397	(1)
Tracking changes in a shared workbook	398	(1)
Accepting and rejecting tracked changes	398	(1)
Printing a history of changes	399	(2)
Protect change tracking in a shared workbook	401	(1)
Unsharing a workbook	401	(1)
Merging shared workbooks	402	(1)
Sending and routing workbooks	402	(3)
Send a workbook	403	(1)
Route a workbook	404	(1)
Protecting workbooks and worksheets	405	(5)
Protect and unprotect a workbook	406	(1)
Protect a worksheet	407	(1)
Unlock cells before protecting a worksheet	408	(1)
Protect a workbook file	408	(2)
Just the facts	410	(3)
18 Sharing a Workbook Between Programs	413	(1)
Shared-data terminology	413	(1)
Sending data between Excel and Word	414	(7)
Paste data	416	(1)
Link data	417	(2)
Change a link's source	418	(1)
Freeze linked data	418	(1)
Delete a linked object	419	(1)
Embed data	419	(2)
Send data from Word to Excel	421	(1)
Sending data between Excel and Access	421	(3)
Pasting Excel data into PowerPoint	424	(1)
Dragging a hyperlink into Excel	424	(1)
Saving a worksheet as a Web page	425	(2)
Just the facts	427	(2)
VIII Customizing and Automating	429	(1)
19 Customizing Your Workspace	431	(54)
Changing your screen display	431	(3)
Fill the screen	432	(1)
Zoom the view	433	(1)
Personalizing your toolbars	434	(7)
Move toolbars	434	(1)
Hide and show toolbars	435	(1)
Change toolbar buttons	435	(2)
Remove and move buttons	435	(1)
Add buttons	436	(1)
Create custom toolbars	437	(1)
Create custom buttons	438	(2)
My custom toolbar	440	(1)
Customizing your menu bar	441	(3)
Add a command to a menu	442	(1)
Remove a command from a menu	442	(1)
Create a new menu for the bar	443	(1)
Just the facts	444	(1)
20 Adding Controls to a Worksheet	445	(1)
What can worksheet controls do?	446	(2)
The Forms toolbar controls	448	(2)
All worksheet controls	450	(1)
List boxes and combo boxes	451	(3)
Create a list box	451	(1)
Create a combo box	452	(2)
Option buttons and group boxes	454	(2)
Create option buttons	454	(2)
Create a group box	456	(1)
Scroll bars and spinners	456	(3)
Create a spinner	457	(1)
Create a scroll bar	457	(2)
Check boxes	459	(1)
Button controls	460	(1)
Labels	461	(1)
Just the facts	462	(1)
21 Using Macros to Automate Repetitive Work	463	(1)
Introducing macros	463	(2)
Recording a macro	465	(4)
Record a simple macro	466	(2)
Record a macro with relative references	468	(1)
Running a macro	469	(3)
Run a macro with a keystroke	469	(1)
Attach a macro to a toolbar button	470	(1)
Attach a macro to a menu command	471	(1)
Attach a macro to an object	471	(1)
Exploring the Visual Basic Editor	472	(4)
The Project window	472	(2)
Modules	474	(1)
The Code window	474	(2)
The Sub line	474	(1)
The End Sub line	475	(1)
Statements	475	(1)
Comments	476	(1)
The Visual Basic Editor Standard toolbar	476	(1)
Editing a macro	476	(5)
Create a toggle macro	477	(3)
Fix errors in a macro	480	(1)
Deleting a macro	481	(1)
Unhide the Personal Macro Workbook	481	(1)
Hide the Personal Macro Workbook	482	(1)
Setting macro virus protection	482	(2)
Just the facts	484	(1)
IX Appendixes	485	(1)
Glossary	487	(18)
A Recommended Resources	505	(1)
Books	505	(1)
Internet sites	506	(3)
B Build Creative Worksheets	509	(1)
Checkbook calculator	509	(20)
Build the worksheet	511	(2)
Write the macros	513	(4)
Time-log template	517	(6)
Chart presentation	523	(6)
Index	529