book

Excel 2007: The Missing Manual

by Matthew MacDonald

December 2006

Beginner

856 pages

52h 17m

English

O'Reilly Media, Inc.

Read now

Unlock full access

About the AuthorAbout the Creative TeamAcknowledgementsThe Missing Manual Series
What You Can Do with ExcelExcel’s New FaceThe RibbonUsing the Ribbon with the KeyboardThe Office MenuThe Quick Access ToolbarExcel’s New FeaturesAbout This BookAbout the OutlineAbout → These → ArrowsContextual tabsDrop-down buttonsDialog box launchersOrdinary menusAbout Shortcut KeysAbout ClickingExamplesAbout MissingManuals.comSafari® Enabled
1.1. Creating a Basic Worksheet1.1.1. Starting a New Workbook1.1.2. Adding the Column Titles1.1.3. Adding Data1.2. Editing Data1.3. Navigating in Excel1.3.1. The Tabs of the Ribbon1.3.2. The Formula Bar1.3.3. The Status Bar1.3.4. Excel Options1.4. Saving Files1.4.1. The Excel 2007 File Format1.4.2. Saving Your Spreadsheet in Older Formats1.4.2.1. Compatibility mode1.4.2.2. The Compatibility Checker1.4.3. Saving Your Spreadsheet As a PDF1.4.4. Saving Your Spreadsheet with a Password1.4.5. Disaster Recovery1.5. Opening Files1.5.1. Opening Files—with a Twist1.5.2. Opening Multiple Spreadsheets at Once
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 Redo
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.1. Worksheets and Workbooks4.1.1. Adding, Removing, and Hiding Worksheets4.1.2. Naming and Rearranging Worksheets4.1.3. Grouping Sheets4.1.3.1. Moving, copying, deleting, or hiding grouped worksheets4.1.3.2. Formatting cells, columns, and rows in grouped worksheets4.1.3.3. Entering data or changing cells in grouped worksheets4.1.3.4. Cutting, copying, and pasting cells in grouped worksheets4.1.3.5. Adjusting printing and display options in grouped worksheets4.1.4. Moving Worksheets from One Workbook to Another4.2. Find and Replace4.2.1. The Basic Find4.2.2. Find All4.2.3. More Advanced Searches4.2.4. Finding Formatted Cells4.2.5. Finding and Replacing Values4.3. Spell Check4.3.1. Spell Checking Options
5.1. Formatting Cell Values5.1.1. Formatting Numbers5.1.1.1. General5.1.1.2. Number5.1.1.3. Currency5.1.1.4. Accounting5.1.1.5. Percentage5.1.1.6. Fraction5.1.1.7. Scientific5.1.1.8. Text5.1.2. Formatting Dates and Times5.1.3. Special Formats for Special Numbers5.1.4. Custom Formats5.1.4.1. Creating a custom format5.1.4.2. Custom format string codes5.1.4.3. Date and time format strings5.1.4.4. Number format strings5.1.4.5. Text format strings5.2. Formatting Cell Appearance5.2.1. Alignment and Orientation5.2.2. Fonts and Color5.2.2.1. Special characters5.2.3. Borders and Fills5.2.4. Drawing Borders by Hand

6.1. The Format Painter6.2. Styles and Themes6.2.1. Custom Styles6.2.2. Modifying Styles6.2.3. Transferring Styles Between Workbooks6.2.4. Themes: A Package of Styles6.2.4.1. Fonts6.2.4.2. Colors6.2.4.3. Effects6.2.5. Modifying Themes6.3. Conditional Formatting6.3.1. The Basics of Conditional Formatting6.3.2. Highlighting Specific Values6.3.3. Data Bars6.3.4. Color Scales6.3.5. Icon Sets6.3.6. Fine-Tuning a Formatting Rule6.3.6.1. Fine-tuning data bars6.3.6.2. Fine-tuning color scales6.3.6.3. Fine-tuning icon sets6.3.7. Using Multiple Rules
7.1. Controlling Your View7.1.1. Zooming7.1.2. Viewing Distant Parts of a Spreadsheet at Once7.1.3. Freezing Columns or Rows7.1.4. Hiding Data7.1.5. Saving View Settings7.1.6. Viewing Multiple Workbooks at Once7.2. Printing7.2.1. How to Print an Excel File7.2.2. Quick Printing7.2.3. Previewing Your Printout7.2.4. Creating Headers and Footers7.2.5. Customizing Print Settings7.2.5.1. Margins7.2.5.2. Paper size and orientation7.2.5.3. Sheet settings7.3. Controlling Pagination7.3.1. Page Breaks7.3.2. Scaling7.3.3. Page Break Preview: A Bird’s-Eye View of Your Worksheet
8.1. Creating a Basic Formula8.1.1. Excel’s Order of Operations8.1.2. Cell References8.1.3. How Excel Formats Cells That Contain Cell References8.1.4. Functions8.1.4.1. Using a function in a formula8.1.4.2. Using cell references with a function8.1.4.3. Using cell ranges with a function8.1.5. Formula Errors8.1.6. Logical Operators8.2. Formula Shortcuts8.2.1. Point-and-Click Formula Creation8.2.2. Point-and-Click Formula Editing8.2.3. The Formulas Tab8.2.4. Using the Insert Function Button8.3. Copying Formulas8.3.1. Absolute Cell References8.3.2. Partially Fixed References8.3.3. Referring to Other Worksheets and Workbooks8.3.3.1. Updating formulas that refer to other workbooks
9.1. Rounding Numbers9.1.1. ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers9.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions9.1.3. INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers9.1.4. EVEN( ) and ODD( ): Rounding Up to Even or Odd Values9.2. Groups of Numbers9.2.1. SUM( ): Summing Up Numbers9.2.2. COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List9.2.3. MAX( ) and MIN( ): Finding Maximum and Minimum Values9.2.4. LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers9.2.5. AVERAGE( ) and MEDIAN( ): Finding Average or Median Values9.2.6. MODE( ): Finding Numbers That Frequently Occur in a List9.2.7. PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions9.2.8. FREQUENCY( ): Identifying to Which Category a Number Belongs9.3. General Math Functions9.3.1. PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots9.3.2. QUOTIENT( ) and MOD( ): Higher Division9.3.3. ABS( ) and SIGN( ): Absolute Value and Determining a Number’s Sign9.3.4. RAND( ) and RANDBETWEEN( ): Generating Random Numbers9.3.5. GCD( ) and LCM( ): Greatest and Least Common Denominator9.3.6. COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations9.4. Trigonometry and Advanced Math9.5. Advanced Statistics
10.1. The World of Finance10.2. Financial Functions10.2.1. FV( ): Future Value10.2.2. PV( ): Present Value10.2.3. PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make10.2.4. NPER( ): Figuring Out How Much Time You’ll Need to Pay Off a Loan or Meet an Investment Target10.2.5. RATE( ): Figuring the Interest Rate You Need to Achieve Future Value10.2.6. NPV( ) and IRR( ): Net Present Value and Internal Rate of Return10.3. Depreciation10.4. Other Financial Functions
11.1. Manipulating Text11.1.1. CONCATENATE( ): Joining Strings of Text Together11.1.2. LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String11.1.3. LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String11.1.4. UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization11.1.5. TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters11.1.6. SUBSTITUTE( ): Replacing One Sequence of Characters with Another11.1.7. TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa11.1.8. Other Text Functions11.2. Manipulating Dates and Times11.3. Math with Dates and Times11.3.1. Using Dates and Times with Ordinary Functions11.4. Date and Time Functions11.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time11.4.2. DATE( ) and TIME( ): Dates and Times in Calculations11.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations11.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations11.4.5. WEEKDAY( ): Determining the Day of the Week11.4.6. DATEDIF( ): Calculating the Difference Between Dates11.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers11.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates11.4.9. EDATE( ): Calculating Future Dates11.4.10. YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates11.4.11. EOMONTH( ): Finding the Last Day of Any Month11.4.12. NETWORKDAYS( ): Counting the Number of Business Days11.4.13. WORKDAY( ): Figuring Out When Days Will Fall in the Future11.4.14. WEEKNUM( ): Figuring Out in Which Week a Date Falls
12.1. The Basic Lookup12.1.1. VLOOKUP( ): Vertical Lookups12.1.2. HLOOKUP( ): Horizontal Lookups12.2. Advanced Lookups12.2.1. MATCH( ): Finding the Position of Items in a Range12.2.2. INDEX( ): Retrieving the Value from a Cell12.2.3. Performing a “Left Lookup”12.2.4. Performing a Double Lookup12.2.5. The Lookup Wizard12.2.6. OFFSET( ): Moving Cell References to a New Location12.2.7. Other Reference and Lookup Functions12.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored As Text12.2.9. TRANSPOSE( ): Changing Rows into Columns and Vice Versa12.3. Information Functions12.3.1. The “IS” Functions: Checking the Value Inside a Cell12.3.2. TYPE( ) and ERROR.TYPE( ): Finding a Value’s Data Type or Error Type12.3.3. INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet’s Cells12.4. Tutorial: Quickly Generating Invoices from a Product Catalog
13.1. Conditions in Formulas13.1.1. IF(): Building Conditional Formulas13.1.2. COUNTIF(): Counting Only the Cells You Specify13.1.3. SUMIF(): Adding Only the Cells You Specify13.1.4. COUNTIFS() and SUMIFS(): Counting and Summing Using Multiple Criteria13.2. Descriptive Names for Cell References13.2.1. Creating and Using a Named Range13.2.2. Creating Slightly Smarter Named Ranges13.2.3. Naming Formulas and Constants13.2.4. Managing Named Ranges13.2.5. Automatically Creating Named Ranges13.2.6. Applying Names to Existing Formulas13.3. Variable Data Tables13.3.1. Creating a One-Variable Data Table13.3.2. Creating a Two-Variable Data Table13.4. Controlling Recalculation13.5. Solving Formula Errors13.5.1. Step-by-Step Evaluation13.5.2. Tracing Precedents and Dependents13.5.3. Error Checking
14.1. The Basics of Tables14.1.1. Creating a Table14.1.2. Formatting a Table14.1.3. Editing a Table14.1.4. Selecting Parts of a Table14.2. Sorting and Filtering a Table14.2.1. Applying a Simple Sort Order14.2.2. Sorting with Multiple Criteria14.2.3. Sorting by Color14.2.4. Filtering with the List of Values14.2.5. Creating Smarter Filters14.2.5.1. Filtering dates14.2.5.2. Filtering numbers14.2.5.3. Filtering text14.3. Dealing with Duplicate Rows14.3.1. Highlighting Duplicates14.3.2. Removing Duplicates Automatically14.4. Performing Table Calculations14.4.1. Dynamic Calculations14.4.2. Column Names14.4.3. Table Names14.4.4. The Total Row14.4.5. The SUBTOTAL() Function14.4.6. The Database Functions
15.1. Basic Data Grouping15.1.1. Creating a Group15.1.2. Nesting Groups Within Groups15.1.3. Summarizing Your Data15.1.4. Combining Data from Multiple Tables15.2. Grouping Timesavers15.2.1. Auto Outline15.2.2. Automatic Subtotaling
16.1. Understanding Templates16.2. Creating a New Workbook from a Template16.2.1. Downloading Templates (Method 1: The New Workbook Dialog Box)16.2.2. Downloading Templates (Method 2: The Office Online Web Site)16.3. Creating Templates16.3.1. Understanding Custom Templates16.3.2. Building a Custom Template16.3.3. Sharing Templates with Others
17.1. Charting 10117.1.1. Embedded and Standalone Charts17.1.2. Creating a Chart with the Ribbon17.1.3. The Chart Tools Ribbon Tabs17.2. Basic Tasks with Charts17.2.1. Moving and Resizing a Chart17.2.2. Creating a Standalone Chart17.2.3. Editing and Adding to Chart Data17.2.4. Changing the Chart Type17.2.5. Printing Charts17.2.5.1. Embedded charts17.2.5.2. Standalone charts17.3. Practical Charting17.3.1. Charts with Multiple Series of Numbers17.3.2. Controlling the Data Excel Plots on the X-Axis17.3.3. Data That Uses a Date or Time Scale17.3.4. Non-Contiguous Chart Ranges17.3.5. Changing the Order of Your Data Series17.3.6. Changing the Way Excel Plots Blank Values17.4. Chart Types17.4.1. Column17.4.2. Bar17.4.3. Line17.4.4. Pie17.4.5. Area17.4.6. XY (Scatter)17.4.7. Stock17.4.8. Surface17.4.9. Donut17.4.10. Bubble17.4.11. Radar
18.1. Chart Styles and Layouts18.1.1. Chart Styles18.1.2. Chart Layouts18.2. Adding Chart Elements18.2.1. Adding Titles18.2.2. Adding a Legend18.2.3. Adding Data Labels to a Series18.2.4. Adding Individual Data Labels18.2.5. Adding a Data Table18.3. Selecting Chart Elements18.4. Formatting Chart Elements18.4.1. Coloring the Background18.4.2. Fancy Fills18.4.2.1. Gradient fills18.4.2.2. Texture fills18.4.2.3. Picture fills18.4.3. Fancy Borders and Lines18.4.4. Formatting Data Series and Data Points18.4.5. Reusing Your Favorite Charts with Templates18.5. Improving Your Charts18.5.1. Controlling a Chart’s Scale18.5.2. Adding a Trendline18.5.3. Adding Error Bars to Scientific Data18.5.4. Formatting 3-D Charts18.5.5. Changing the Shape of a 3-D Column18.6. Advanced Charting18.6.1. Exploding Slices in a Pie18.6.2. Grouping Slices in a Pie18.6.3. Gaps, Widths, and Overlays in a Column Chart18.6.4. Creating Combination Charts
19.1. Adding Pictures to a Worksheet19.1.1. Inserting a Picture19.1.2. Positioning and Resizing a Picture19.1.3. Picture Touch-Up19.1.4. Compressing Pictures19.1.5. Cropping and Shaping a Picture19.1.6. Picture Borders, Effects, and Styles19.2. Excel’s Clip Art Library19.3. Drawing Shapes19.3.1. Drawing a Shape19.3.2. Adding Text to a Shape19.3.3. Selecting and Arranging Shapes19.3.4. Connecting Shapes19.3.5. SmartArt
20.1. Using Scenarios20.1.1. Creating a New Scenario20.1.2. Managing Scenarios20.1.3. Creating a Summary Report20.2. Using Goal Seek20.2.1. Goal Seeking with Complex Equations20.3. Solver20.3.1. Understanding Solver20.3.2. Defining a Problem in Solver20.3.3. Advanced Solver Solutions20.3.4. Saving Solver Models20.3.5. Configuring Solver
21.1. Summary Tables Revisited21.1.1. Life Without Pivot Tables21.1.2. Life with Pivot Tables21.2. Building Pivot Tables21.2.1. Preparing a Pivot Table21.2.2. Pivot Table Regions21.2.3. Laying Out a Pivot Table21.2.4. Formatting a Pivot Table21.2.5. Rearranging a Pivot Table21.3. Multi-Layered Pivot Tables21.3.1. Hiding and Showing Details21.4. Fine-Tuning Pivot Table Calculations21.4.1. Changing the Type of Calculation21.4.2. Adding a Calculated Field21.5. Filtering a Pivot Table21.5.1. Report Filtering21.5.2. Group Filtering21.6. Pivot Charts21.6.1. Creating a Pivot Chart21.6.2. Manipulating a Pivot Chart
22.1. Understanding Excel’s Safeguards22.2. Data Validation22.2.1. Settings22.2.2. Input Message22.2.3. Error Alert22.2.4. Data Validation with Formulas and Cell References22.2.5. Data Validation with Lists22.3. Locked and Hidden Cells22.3.1. Protecting a Worksheet22.3.2. Protecting the Entire Workbook22.3.3. Protecting Cell Ranges (with More Passwords)22.3.4. Allowing Specific Windows Users to Edit a Range
23.1. Preparing Your Workbook23.1.1. Document Properties23.1.2. Distributing a Document23.2. Adding Comments23.2.1. Inserting a Comment23.2.2. Showing and Hiding Comments23.2.3. Fine-Tuning Comments23.2.4. Reviewing Comments23.2.5. Printing Comments23.3. Tracking Changes23.3.1. Switching On Change Tracking23.3.2. Understanding the Change Log23.3.3. Highlighting Changes23.3.4. Examining the Change Log23.3.5. Accepting and Rejecting Changes23.3.6. Merging Multiple Revisions into One Workbook23.4. Sharing Your Workbook23.4.1. Multiple Users Without Workbook Sharing23.4.2. Turning On Workbook Sharing23.4.3. Workbook Sharing in Action
24.1. Excel and Databases24.1.1. Connecting to an Access Database24.1.2. Refreshing Data24.1.3. Data Source Security24.1.4. Connecting to a SQL Server Database24.1.5. Reusing Your Database Connection24.2. Understanding XML24.2.1. What Is XML, Really?24.2.2. Three Rules of XML24.2.2.1. The prolog24.2.2.2. Elements24.2.2.3. Nesting24.2.3. XML Files and Schemas24.3. Excel and XML24.3.1. Mapping a Simple Document24.3.2. Importing and Exporting XML24.3.3. Mapping Lists24.3.4. Gaining the Benefits of XML Mapping
25.1. Sharing Information in Windows25.2. Embedding and Linking Objects25.2.1. Exporting Charts out of Excel25.2.2. Editing a Linked Object25.2.3. Editing an Embedded Object25.2.4. Importing Objects into Excel25.3. Transferring Data25.3.1. Exporting Tables of Data25.3.2. Importing Tables of Data25.3.3. Importing Text Files
26.1. Putting Worksheets on the Web26.1.1. Saving an HTML File26.2. Performing Web Queries26.2.1. The Limitations of Web Queries26.2.2. Creating a Web Query26.2.3. The Research Pane—a Web Query Alternative26.3. Using Hyperlinks26.3.1. Adding a Hyperlink to a Web Page or Document26.3.2. Adding a Hyperlink to a Worksheet Location26.3.3. The HYPERLINK() Function: Creating a Link on Your Own
27.1. Macros 10127.1.1. Macro-Free and Macro-Enabled Workbooks27.2. The Macro Recorder27.2.1. Relative and Absolute Recording27.2.2. Where Macros Live27.2.3. Recording a Macro27.2.4. Playing a Macro27.3. Macro Security27.3.1. The Trust Center27.3.2. Setting Up a Trusted Location27.4. Creating Practical Macros27.4.1. Inserting a Header27.4.2. Alternating Row Formatting27.4.3. A Combined Task27.4.4. Placing a Macro on the Quick Access Toolbar27.4.5. Attaching a Macro to a Button Inside a Worksheet
28.1. The Visual Basic Editor28.1.1. The Project Window28.1.2. Modules and Macros28.1.3. Finding and Moving Macros28.1.4. Debugging a Macro28.2. Understanding Macro Code28.2.1. The Anatomy of a Macro28.2.2. Objects 10128.2.3. Using Properties and Methods28.2.4. Hunting for Objects28.3. Exploring the VBA Language28.3.1. Entering Text in the Current Cell28.3.2. Moving to Other Cells28.3.3. Editing Specific Cells28.3.4. Formatting Cells28.3.5. Using Variables28.3.6. Making Decisions28.3.7. Repeating Actions with a Loop28.3.8. Creating Custom Functions
A.1. The Quick Access ToolbarA.1.1. Adding ButtonsA.1.2. Customizing Specific Workbooks

Content preview from Excel 2007: The Missing Manual

Chapter 24. Querying Databases and XML Files

An ordinary Excel workbook stores a bunch of information, and uses Excel tools (like formulas and charts) to analyze it. However, a great deal of the world’s business information isn’t stored in Excel files. Instead, it sits inside databases (organized catalogs of information managed by high-powered computer software).

Typical mid-size companies (or bigger ones) store product catalogs, customer lists, and purchase orders in databases. When they need to change this information (or just take a look at it), they use another program that talks to the database. Databases are extremely fast, secure, and durable. They’re the best place to put missioncritical information.

At first glance, this approach (storing big volumes of information in a database) seems to cut Excel out of the loop. However, Excel’s greatest strength isn’t storing information, but analyzing it. In fact, Excel is the perfect tool for analyzing information, no matter where it comes from. Your company might track orders using a custom application that dumps its results into a database. If you want to find out what all the numbers amount to, and make a prediction about your company’s future, you can pull that info out of a database (using a process called querying), and then start charting away. In other words, your Excel workbook gets a copy of the information that’s stored in the database so that you can put it under the microscope. Best of all, you can refresh your workbook ...