book

Excel 2003: The Missing Manual

Name: Excel 2003: The Missing Manual
Author: Matthew MacDonald
ISBN: 9780596006648

by Matthew MacDonald

December 2004

Beginner

791 pages

26h 21m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Excel: The Missing Manual
A Note Regarding Supplemental Files
The Missing Credits
About the AuthorAbout the Creative TeamAcknowledgementsThe Missing Manual Series
Introduction
What Excel Is all AboutAbout This BookAbout the OutlineAbout→ These→ ArrowsThe Very BasicsExamplesAbout MissingManuals.comSafari-Enabled
I. Worksheet Basics
1. Creating and Navigating Worksheets
1.1. Creating a Basic Worksheet1.1.1. Starting a New Worksheet1.1.2. Adding the Column Titles1.1.3. Adding Data1.2. Editing Data1.3. Navigating in Excel1.3.1. The Menus1.3.2. The Task Pane1.3.3. The Toolbars1.3.3.1. Moving toolbars1.3.3.2. Toolbars with missing buttons1.3.3.3. Toolbar tricks1.3.4. The Formula Bar1.3.5. The Status Bar1.4. Saving Files1.4.1. Saving Your Spreadsheet in Other Formats1.4.2. Saving Your Spreadsheet with a Password1.4.3. Disaster Recovery1.5. Opening Files1.5.1. Searching for Files
2. Adding Information to Worksheets
2.1. Adding Different Types of Data2.1.1. Controlling Your Data Types2.1.1.1. How Excel decides your data is text2.1.1.2. How Excel decides your data is numeric2.1.1.3. How Excel decides your data is a date or time2.2. Quick Ways to Add Data2.2.1. AutoComplete2.2.2. AutoCorrect2.2.3. AutoFill2.2.3.1. Custom AutoFill Lists2.2.4. AutoFit2.2.5. Undo and Redo2.2.5.1. Undo quirks
3. Moving Data Around a Worksheet
3.1. Selecting Cells3.1.1. Making Continuous Range Selections3.1.2. Making Non-Contiguous Selections3.1.3. Automatically Selecting Your Data3.1.4. Making Selections with the Keyboard3.2. Moving Cells Around3.2.1. A Simple Cut-and-Paste or Copy-and-Paste3.2.2. A Fancy Cut-and-Paste or Copy-and-Paste3.2.3. The Clipboard3.2.4. Special Pasting3.3. Adding and Moving Columns or Rows3.3.1. Inserting Columns3.3.2. Inserting Rows3.3.3. Inserting Copied or Cut Cells3.3.4. Deleting Columns and Rows
4. Formatting Worksheets
4.1. Formatting Cell Values4.1.1. Formatting Numbers4.1.1.1. General4.1.1.2. Number4.1.1.3. Currency4.1.1.4. Accounting4.1.1.5. Percentage4.1.1.6. Fraction4.1.1.7. Scientific4.1.1.8. Text4.1.2. Formatting Dates and Times4.1.3. Special Formats for Special Numbers4.1.4. Custom Formats4.1.4.1. Creating a custom format4.1.4.2. Custom format string codes4.1.4.3. Date and time format strings4.1.4.4. Number format strings4.1.4.5. Text format strings4.2. Formatting Cell Appearance4.2.1. Alignment and Orientation4.2.2. Fonts and Color4.2.2.1. Special characters4.2.3. Borders and Patterns4.3. Smart Ways to Apply Formatting4.3.1. Shortcuts with the Toolbars4.3.1.1. Drawing borders4.3.1.2. Formatting individual characters4.3.2. AutoFormat4.3.3. The Format Painter4.3.4. Using Styles4.3.4.1. Creating a style4.3.4.2. Applying a style4.3.4.3. Transferring styles4.3.5. Conditional Formatting
5. Managing Worksheets and Workbooks
5.1. Worksheets and Workbooks5.1.1. Adding, Removing, and Hiding Worksheets5.1.2. Naming and Rearranging Worksheets5.1.3. Grouping Sheets5.1.3.1. Moving, copying, deleting, or hiding grouped worksheets5.1.3.2. Formatting cells, columns, and rows in grouped worksheets5.1.3.3. Entering data or changing cells in grouped worksheets5.1.3.4. Cutting, copying, and pasting cells in grouped worksheets5.1.3.5. Adjusting printing and display options in grouped worksheets5.1.4. Moving Worksheets from One Workbook to Another5.2. Find and Replace5.2.1. The Basic Find5.2.2. More Advanced Searches5.2.3. Finding Formatted Cells5.2.4. Finding and Replacing Values5.3. Spell Check5.3.1. Spell Checking Options

6. Viewing and Printing Worksheets
6.1. Controlling Your View6.1.1. Zooming6.1.2. Viewing Distant Parts of a Spreadsheet at Once6.1.3. Freezing Columns or Rows6.1.4. Hiding Data6.1.5. Saving View Settings6.1.6. Viewing Multiple Workbooks at Once6.2. Printing6.2.1. How to Print an Excel File6.2.2. Customizing Print Settings6.2.2.1. Page settings6.2.2.2. Margins settings6.2.2.3. Creating headers and footers6.2.2.4. Sheet settings6.2.3. Getting a Print Snapshot6.2.4. Page Break Preview: A Bird’s-Eye View of Your Worksheet
II. Formulas and Functions
7. Building Basic Formulas
7.1. Creating a Basic Formula7.1.1. Excel’s Order of Operations7.1.2. Cell References7.1.3. How Excel Formats Cells that Contain Cell References7.1.4. Functions7.1.5. Specifying Cell Ranges7.1.6. Formula Errors7.1.7. Logical Operators7.2. Formula Shortcuts7.2.1. Point-and-Click Formula Creation7.2.2. Point-and-Click Formula Editing7.2.3. Using the Insert Function Button to Quickly Find and Use Functions7.2.4. The Analysis ToolPak7.3. Copying Formulas7.3.1. Absolute Cell References7.3.2. Partial Fixed References7.3.3. Referring to Other Worksheets and Workbooks
8. Math and Statistical Functions
8.1. Rounding Numbers8.1.1. ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers8.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions8.1.3. INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers8.1.4. EVEN( ) and ODD( ): Rounding Up to Even or Odd Values8.2. Groups of Numbers8.2.1. SUM( ): Summing Up Numbers8.2.2. COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List8.2.3. MAX( ) and MIN( ): Finding Maximum and Minimum Values8.2.4. LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers8.2.5. AVERAGE( ) and MEDIAN( ): Finding Average or Median Values8.2.6. MODE( ): Finding Numbers that Frequently Occur in a List8.2.7. PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions8.2.8. FREQUENCY( ): Identifying to Which Category a Number Belongs8.3. General Math Functions8.3.1. PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots8.3.2. QUOTIENT( ) and MOD( ): Higher Division8.3.3. ABS( ) and SIGN( ): Absolute Value and Determining a Number’s Sign8.3.4. RAND( ) and RANDBETWEEN( ): Generating Random Numbers8.3.5. GCD( ) and LCM( ): Greatest and Least Common Denominator8.3.6. COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations8.4. Trigonometry and Advanced Math8.5. Advanced Statistics
9. Financial Functions
9.1. The World of Finance9.2. Financial Functions9.2.1. FV( ): Future Value9.2.2. PV( ): Present Value9.2.3. PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make9.2.4. NPER( ): Figuring Out How Much Time You’ll Need to Pay Off a Loan or Meet an Investment Target9.2.5. RATE( ): Figuring the Interest Rate You Need to Achieve Future Value9.2.6. NPV( ) and IRR( ): Net Present Value and Internal Rate of Return9.3. Depreciation9.4. Other Financial Functions
10. Manipulating Dates, Times, and Text
10.1. Manipulating Text10.1.1. CONCATENATE( ): Joining Strings of Text Together10.1.2. LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String10.1.3. LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String10.1.4. UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization10.1.5. TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters10.1.6. SUBSTITUTE( ): Replacing One Sequence of Characters with Another10.1.7. TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa10.1.8. Other Text Functions10.2. Manipulating Dates and Times10.3. Math with Dates and Times10.4. Date and Time Functions10.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time10.4.2. DATE( ) and TIME( ): Dates and Times in Calculations10.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations10.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations10.4.5. WEEKDAY( ): Determining the Day of the Week10.4.6. DATEDIF( ): Calculating the Difference Between Dates10.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers10.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates10.5. Analysis ToolPak Date Functions10.5.1. EDATE( ): Calculating Future Dates10.5.2. YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates10.5.3. EOMONTH( )10.5.4. NETWORKDAYS( ): Counting the Number of Business Days10.5.5. WORKDAY( ): Figuring Out When Days Will Fall in the Future10.5.6. WEEKNUM( ): Figuring Out in Which Week a Date Falls
11. Lookup, Reference, and Information Functions
11.1. The Basic Lookup11.1.1. VLOOKUP( ): Vertical Lookups11.1.2. HLOOKUP( ): Horizontal Lookups11.2. Advanced Lookups11.2.1. MATCH( ): Finding the Position of Items in a Range11.2.2. INDEX( ): Retrieving the Value from a Cell11.2.3. Performing a “Left Lookup”11.2.4. Performing a Double Lookup11.2.5. The Lookup Wizard11.2.6. OFFSET( ): Moving Cell References to a New Location11.2.7. Other Reference and Lookup Functions11.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored as Text11.2.9. TRANSPOSE( ): Changing Rows into Columns and Vice Versa11.3. Information Functions11.3.1. The “IS” Functions: Checking the Value Inside a Cell11.3.2. TYPE( ) and ERROR.TYPE( ): Finding a Value’s Data Type or Error Type11.3.3. INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet’s Cells11.4. Tutorial: Quickly Generating Invoices from a Product Catalog
12. Advanced Formula Writing
12.1. Conditions in Formulas12.1.1. IF( ): Building Conditional Formulas12.1.2. COUNTIF( ): Counting Only the Cells You Specify12.1.3. SUMIF( ): Adding Only the Cells You Specify12.1.4. SUMPRODUCT( ): Multiplying Two Ranges12.2. Descriptive Names for Cell References12.2.1. Creating and Using a Named Range12.2.2. Managing Named Ranges12.2.3. Naming Formulas and Constants12.2.4. Automatically Creating Named Ranges12.2.5. Applying Names to Existing Formulas12.3. Variable Data Tables12.3.1. Creating a One-Variable Data Table12.3.2. Creating a Two-Variable Data Table12.4. Controlling Recalculation12.5. Solving Formula Errors12.5.1. Step-by-Step Evaluation12.5.2. Tracing Precedents and Dependents12.5.3. Error Checking
III. Organizing Worksheets
13. Data Lists
13.1. The Basics of Data Lists13.1.1. Creating a List13.1.2. Editing a List13.2. The Data Form13.2.1. Editing with the Data Form13.2.2. Searching with the Data Form13.2.3. Advanced Searching13.3. Sorting and Filtering a List13.3.1. Applying a Simple Sort Order13.3.2. Sorting with Multiple Criteria13.3.3. Applying an Automatic Filter13.3.4. Creating a Custom Filter13.4. Performing List Calculations13.4.1. Dynamic Calculations13.4.2. The Total Row13.4.3. The SUBTOTAL( ) Function13.4.4. The Database Functions
14. Grouping and Outlining Data
14.1. Basic Data Grouping14.1.1. Creating a Group14.1.2. Nesting Groups Within Groups14.1.3. Summarizing Your Data14.1.4. Combining Data from Multiple Tables14.2. Grouping Timesavers14.2.1. AutoOutline14.2.2. Automatic Subtotaling14.2.3. Your Own Custom Grouping Toolbar
15. Templates
15.1. Understanding Templates15.2. Starting from a Template15.2.1. Spreadsheet Solutions Templates15.2.2. Discovering the Finer Points of Templates15.2.3. Office Online Templates15.2.4. Searching for Office Online Templates15.3. Creating Templates15.3.1. Understanding Custom Templates15.3.2. Building a Custom Template15.3.3. Sharing Templates with Others15.4. Fine-Tuning Templates15.4.1. Data Validation15.4.1.1. Settings15.4.1.2. Input Message15.4.1.3. Error Alert15.4.2. Data Validation with Formulas and Cell References15.4.3. Data Validation with Lists15.4.4. Locked and Hidden Cells15.4.5. Protecting the Entire Workbook
IV. Charts and Graphics
16. Creating Basic Charts
16.1. Charting 10116.1.1. Embedded and Standalone Charts16.1.2. The Chart Wizard16.2. Basic Tasks with Charts16.2.1. Moving and Resizing a Chart16.2.2. Changing Chart Options16.2.3. Editing and Adding to Chart Data16.2.4. Printing Charts16.2.4.1. Embedded charts16.2.4.2. Standalone charts16.3. Practical Charting16.3.1. Charts with Multiple Series of Numbers16.3.2. Controlling the Data Excel Plots on the X-Axis16.3.3. Data that Uses a Date or Time Scale16.3.4. Non-Contiguous Chart Ranges16.4. Chart Types16.4.1. Column16.4.2. Bar16.4.3. Line16.4.4. Pie16.4.5. XY (Scatter)16.4.6. Area16.4.7. Donut16.4.8. Radar16.4.9. Surface16.4.10. Bubble16.4.11. Stock16.4.12. Cylinder, Cone, and Pyramid
17. Formatting and Perfecting Charts
17.1. Formatting Chart Elements17.1.1. Manipulating Chart Elements17.1.2. The Chart Toolbar17.1.3. Coloring the Background17.1.4. Fancy Fills17.1.5. Adding Titles17.1.6. Adding a Legend17.1.7. Adding Data Labels to a Series17.1.8. Adding Individual Data Labels17.1.9. Formatting Data Series and Data Points17.1.10. Controlling a Chart’s Scale17.2. Improving Your Charts17.2.1. Adding a Trendline17.2.2. Adding Error Bars to Scientific Data17.2.3. Adding a Data Table17.2.4. Formatting 3-D Charts17.2.5. Changing the Order of Your Data Series17.2.6. Changing the Shape of a 3-D Column17.2.7. Changing the Way Excel Plots Blank Values17.3. Advanced Charting17.3.1. Exploding Slices in a Pie17.3.2. Grouping Slices in a Pie17.3.3. Gaps, Widths, and Overlays in a Column Chart17.3.4. Creating Combination Charts
18. Inserting Graphics
18.1. Adding Pictures to a Worksheet18.1.1. Inserting a Picture18.1.2. Positioning and Resizing a Picture18.1.3. Picture Touch-Up18.1.4. Compressing Pictures18.2. Excel’s Clip Art Library18.3. Drawing Shapes18.3.1. Drawing a Shape18.3.2. Adding Text to a Shape18.3.3. Layers18.4. Drawing Graphic Objects in Charts
V. Advanced Data Analysis
19. Scenarios and Goal Seeking
19.1. Using Scenarios19.1.1. Creating a New Scenario19.1.2. Managing Scenarios19.1.3. Creating a Summary Report19.2. Using Goal Seek19.2.1. Goal Seeking with Complex Equations19.3. Solver19.3.1. Understanding Solver19.3.2. Defining a Problem in Solver19.3.3. Advanced Solver Solutions19.3.4. Saving Solver Models19.3.5. Configuring Solver
20. Pivot Tables
20.1. Summary Tables Revisited20.1.1. Life Without Pivot Tables20.1.2. Life with Pivot Tables20.2. Building Pivot Tables20.2.1. Preparing a Pivot Table20.2.2. Understanding Pivot Table Regions20.2.3. Laying Out a Pivot Table20.2.4. Rearranging a Pivot Table20.3. Advanced Pivot Tables20.3.1. Creating Multi-Layered Pivot Tables20.3.2. Hiding and Showing Details20.3.3. Filtering Pivot Tables20.3.4. Formatting the Pivot Table20.3.5. Adding a Calculated Field20.3.6. Creating Pivot Charts
VI. Sharing Data with the Rest of the World
21. Worksheet Collaboration
21.1. Your Excel Identity21.2. Using Comments21.2.1. Inserting a Comment21.2.2. Fine-Tuning Comments21.2.3. Navigating Comments21.2.4. Printing Comments21.3. Using Change Tracking21.3.1. Switching On Change Tracking21.3.2. Understanding the Change Log21.3.3. Highlighting Changes21.3.4. Examining the Change Log21.3.5. Accepting and Rejecting Changes21.3.6. Merging Multiple Revisions into One Workbook21.4. Setting Up a Review Chain21.4.1. Sending a Workbook by Email with Excel21.4.2. Sending a Workbook for Review21.4.3. Routing a Workbook to Multiple People21.5. Workbook Sharing21.5.1. Multiple Users Without Workbook Sharing21.5.2. Turning On Workbook Sharing21.5.3. Understanding Workbook Sharing
22. Exchanging Data with Other Programs
22.1. Sharing Information in Windows22.2. Embedding and Linking Objects22.2.1. Exporting Charts22.2.2. Editing an Embedded Object22.2.3. Editing a Linked Object22.2.4. Importing Objects22.3. Transferring Data22.3.1. Exporting Tables of Data22.3.2. Importing Tables of Data22.3.3. Importing Text Files22.4. Excel and Databases22.4.1. Defining a Data Source22.4.2. Querying a Data Source
23. XML
23.1. XML 10123.1.1. What Is XML, Really?23.1.2. Three Rules of XML23.1.2.1. The prolog23.1.2.2. Elements23.1.2.3. Nesting23.1.3. XML Files and Schemas23.2. The Excel XML Story23.3. Linking Spreadsheets to XML23.3.1. Mapping a Simple Document23.3.2. Importing and Exporting XML23.3.3. Mapping Lists23.3.4. Gaining the Benefits of XML Mapping
24. Connecting Worksheets to the Web
24.1. Putting Worksheets on the Web24.1.1. Saving and Publishing HTML24.1.2. Saving a Simple Web Page24.1.3. Creating an Interactive Web Page24.2. Performing Web Queries24.2.1. The Limitations of Web Queries24.2.2. Creating a Web Query24.2.3. The Research Task—a Web Query Alternative24.3. Using Hyperlinks24.3.1. Adding a Hyperlink to a Web Page or Document24.3.2. Adding a Hyperlink to a Worksheet Location24.3.3. The HYPERLINK( ) Function: Creating a Link on Your Own
VII. Programming Excel
25. Automating Tasks with Macros
25.1. Macros 10125.2. The Macro Recorder25.2.1. Relative and Absolute Recording25.2.2. Where Macros Live25.2.3. Recording a Macro25.2.4. Playing a Macro25.3. Creating Practical Macros25.3.1. Inserting a Header25.3.2. Alternating Row Formatting25.3.3. A Combined Task25.3.4. Security and Macros25.3.5. Attaching a Macro to a Toolbar or Menu25.3.6. Attaching a Macro to a Button Inside a Worksheet
26. Programming Spreadsheets with VBA
26.1. The Visual Basic Editor26.1.1. The Project Window26.1.2. Modules and Macros26.1.3. Finding and Moving Macros26.1.4. Debugging a Macro26.2. Understanding Macro Code26.2.1. The Anatomy of a Macro26.2.2. Objects 10126.2.3. Using Properties and Methods26.2.4. Hunting for Objects26.3. Exploring the VBA Language26.3.1. Entering Text in the Current Cell26.3.2. Moving to Other Cells26.3.3. Editing Specific Cells26.3.4. Formatting Cells26.3.5. Using Variables26.3.6. Making Decisions26.3.7. Repeating Actions with a Loop26.3.8. Creating Custom Functions
VII. Appendixes
A. Getting Help in Excel
A.1. Asking QuestionsA.1.1. Searching Without the Web in Excel 2003A.1.2. Browsing the Help SystemA.2. The Office AssistantA.3. Other Help Menu Options
B. Customizing Excel
B.1. The Customize Dialog BoxB.1.1. Managing ToolbarsB.1.2. Adding, Removing, and Rearranging CommandsB.1.3. Using Special CommandsB.1.4. Returning to NormalB.1.5. Attaching Toolbars to a Workbook
C. Quick Menu Reference
C.1. File MenuC.1.1. NewC.1.2. OpenC.1.3. CloseC.1.4. SaveC.1.5. Save AsC.1.6. Save as Web PageC.1.7. Save WorkspaceC.1.8. File SearchC.1.9. PermissionC.1.10. Web Page PreviewC.1.11. Page SetupC.1.12. Print AreaC.1.13. Print PreviewC.1.14. PrintC.1.15. Send ToC.1.16. PropertiesC.1.17. ExitC.2. Edit MenuC.2.1. UndoC.2.2. RepeatC.2.3. CutC.2.4. CopyC.2.5. Office ClipboardC.2.6. PasteC.2.7. Paste SpecialC.2.8. Paste as HyperlinkC.2.9. FillC.2.10. ClearC.2.11. DeleteC.2.12. Delete SheetC.2.13. Move or Copy SheetC.2.14. FindC.2.15. ReplaceC.2.16. Go ToC.2.17. LinksC.2.18. ObjectC.3. View MenuC.3.1. NormalC.3.2. Page Break PreviewC.3.3. Task PaneC.3.4. ToolbarsC.3.5. Formula BarC.3.6. Status BarC.3.7. Header and FooterC.3.8. CommentsC.3.9. Custom ViewsC.3.10. Full ScreenC.3.11. ZoomC.4. Insert MenuC.4.1. CellsC.4.2. RowsC.4.3. ColumnsC.4.4. WorksheetC.4.5. ChartC.4.6. SymbolC.4.7. Page BreakC.4.8. FunctionC.4.9. NameC.4.10. CommentC.4.11. PictureC.4.12. DiagramC.4.13. ObjectC.4.14. HyperlinkC.5. Format MenuC.5.1. CellsC.5.2. RowC.5.3. ColumnC.5.4. SheetC.5.5. AutoFormatC.5.6. Conditional FormattingC.5.7. StyleC.6. Tools MenuC.6.1. SpellingC.6.2. ResearchC.6.3. Error CheckingC.6.4. SpeechC.6.5. Shared WorkspaceC.6.6. Share WorkbookC.6.7. Track ChangesC.6.8. Compare and Merge WorkbooksC.6.9. ProtectionC.6.10. Online CollaborationC.6.11. Goal SeekC.6.12. ScenariosC.6.13. Formula AuditingC.6.14. SolverC.6.15. MacroC.6.16. Add-InsC.6.17. AutoCorrect OptionsC.6.18. CustomizeC.6.19. OptionsC.6.20. Data AnalysisC.7. Data MenuC.7.1. SortC.7.2. FilterC.7.3. FormC.7.4. SubtotalsC.7.5. ValidationC.7.6. TableC.7.7. Text to ColumnsC.7.8. ConsolidateC.7.9. Group and OutlineC.7.10. PivotTable and PivotChart ReportC.7.11. Import External DataC.7.12. ListC.7.13. XMLC.7.14. Refresh DataC.8. Chart MenuC.8.1. Chart TypeC.8.2. Source DataC.8.3. Chart OptionsC.8.4. LocationC.8.5. Add DataC.8.6. Add TrendlineC.8.7. 3-D ViewC.9. Window MenuC.9.1. New WindowC.9.2. ArrangeC.9.3. Compare Side by Side withC.9.4. HideC.9.5. UnhideC.9.6. SplitC.9.7. Freeze PanesC.10. Help MenuC.10.1. Microsoft Excel HelpC.10.2. Show the Office AssistantC.10.3. Microsoft Office OnlineC.10.4. Contact UsC.10.5. Check for UpdatesC.10.6. Detect and RepairC.10.7. Activate ProductC.10.8. Customer Feedback OptionsC.10.9. About Microsoft Office Excel
Index
About the Author
Colophon
Copyright

Content preview from Excel 2003: The Missing Manual

Chapter 14. Grouping and Outlining Data

As you saw in the last chapter, Excel’s data lists are great tools for managing big tables made up of long, uniform columns (like lists of customers or products). But these data lists don’t work as well for tables that show different groups of information, each of which has its own collection of subtotals.

For example, imagine a company sales report that lists a year’s worth of quarterly results for each of its regions around the world. If you try to cram all this data into one long list—including subtotals for each region—you’re likely to end up with a spreadsheet that looks like a numerical version of Twister. You’d be better off breaking out each region separately, in its own table, and then tying everything together separately, in another table that sums everything up.

Excel’s grouping and outlining features are perfect for dealing with multiple tables. They help you quickly and easily expand and collapse big chunks of data and, in the process, make calculating summary information much easier. These tricks are low-tech and remarkably easy to implement, but they rank as one of Excel’s best-kept productivity secrets. In fact, Excel doesn’t even include a toolbar to work with grouping and outlining—which is a shortcoming you’ll learn to overcome in this chapter.

Basic Data Grouping

The starting point for simplifying your worksheets is learning how to group data. Grouping data lets you tie related columns or rows into a single unit. Once you’ve ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596006640Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel 2003: The Missing Manual

by Matthew MacDonald

Chapter 14. Grouping and Outlining Data

Basic Data Grouping

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.