book

Microsoft® Office Excel 2003 Inside Out

by Craig Stinson, Mark Dodge

September 2003

Intermediate to advanced

1056 pages

30h 58m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Office Excel 2003
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
Acknowledgments
We'd Like to Hear from You!
About the CD
What's On the CD
Using the CD
System Requirements
Support Information
Conventions and Features Used in this Book
Text Conventions

Design Conventions
About the Authors
1. Examining the Excel Environment
1. What's New in Microsoft Office Excel 2003
New and Improved for 2003Online AssistanceControlling Web InteractionGetting Software UpdatesComparing Worksheets Side by SideImproved Statistical FunctionsNew Task PanesDocument WorkspacesInformation Rights ManagementImproved List ManagementSupport for User-Defined XML Structures
If You Missed the Last Upgrade …
Onward …
2. Excel Fundamentals
What Happens After You Install Excel?Registering ExcelSubscribing to ExcelGetting Updates
Recovering from Crashes
Using AutoRecoverHeroic Measures
Examining the Excel Workspace
Facts About WorksheetsUsing the Workbook WindowThe Title BarGetting Around in the WorkbookWorkbook Navigation TipsResizing the WindowExploring Menus and Dialog BoxesMorphing MenusAccessing Menus and Commands with the KeyboardDialog Boxes and TabsThe Joy of Shortcut MenusUnderstanding the Formula BarAbout the Status BarIntroducing Toolbars
File Management Fundamentals
Creating and Opening WorkbooksInstalling Your Own TemplatesUse Your Own Folders for TemplatesChanging the Default Book and SheetSaving FilesRules for File NamingFile FormatsSpecifying the Default File FormatCreating Automatic Backup FilesProtecting FilesAdding Summary Information to FilesSaving the Entire WorkspaceOpening FilesSpecial Ways to Open FilesOpening Files When You Start ExcelRecovering Corrupted FilesSearching for Files
Importing and Exporting Files
Using the Open and Save As Commands to Import and Export FilesWhat Is XML?Importing and Exporting Text FilesSharing Data with Excel for the MacintoshAdjusting Date Values
Online Help Works—Really!
The Best Way to Use HelpManaging the AssistantYou're Fired!Using the AssistantSelecting Office Assistant OptionsGetting Answers to Unasked QuestionsBrowsing the Help SystemIdentifying Toolbar ButtonsCustomer Feedback OptionsOnline ContentShared WorkspaceWhat's on the Help Menu
3. Custom-Tailoring the Excel Workspace
Customizing Toolbars and MenusManaging Buttons and CommandsCustomizing Toolbars on the FlyRemoving and Adding Buttons and CommandsRearranging Buttons and CommandsControlling the Display of Button FacesGiving Face to the FacelessRearranging Menus and SubmenusChanging the Face of ButtonsHow to Use the Button EditorCopying Button FacesDoes Size Matter?
Managing Toolbars
Positioning Bars on Your Screen
Creating New Toolbars and Menus
Creating New ToolbarsAttaching Custom Toolbars to WorkbooksDeleting Custom ToolbarsCreating New MenusResetting MenusPreserving Toolbar and Menu ChangesRestoring Toolbars and MenusRestoring Personalized Toolbars and MenusOther Toolbar and Menu Options
Controlling Other Elements of the Excel Interface
Displaying Underlying FormulasHiding ZerosChanging the Color PaletteCopying Palettes from Other Workbooks
Enhancing Accessibility
2. Building Worksheets
4. Worksheet Design Tips
Which Data Should Be in Rows, and Which in Columns?
Will You Need to Print the Worksheet?
Who Is the Audience?
Would Your Worksheet Survive Without You?
Does the Worksheet Rely on Imported Data?
Do You Need More Than One Worksheet?
Have You Allowed Room to Insert New Rows and Columns?
5. How to Work a Worksheet
Moving AroundNavigating RegionsNavigating Regions with the KeyboardNavigating Regions with the MouseNavigating with Special Keys
Selecting Stuff
Selecting with the MouseSelecting Beyond the Window BordersSelecting Columns, Rows, and Multiple AreasSelecting RegionsSelecting with the Go To Command
Entering Stuff
Making Entries in Cells and in the Formula BarEntering Simple Numeric and Text ValuesUsing Special CharactersUnderstanding the Difference Between Displayed Values and Underlying ValuesCreating Long Text ValuesUsing Word WrappingUnderstanding Numeric Text EntriesEntering SymbolsMaking Entries in RangesEditing and Undoing Entries
Managing Worksheets
Inserting and Deleting SheetsNaming and Renaming SheetsColoring Sheet TabsMoving and Copying Sheets
Looking at Worksheets
Splitting Sheets into PanesFreezing PanesZooming WorksheetsUsing Custom Views
Protecting Worksheets
Unlocking Individual CellsProtecting WorkbooksAllowing Password Access to Specific Cell RangesHiding Cells and SheetsUsing Passwords
6. How to Work a Workbook
Managing Multiple WorkbooksNavigating Between Open WorkbooksArranging Workbook WindowsGetting the Most Out of Your ScreenComparing Sheets Side-By-Side
Opening Multiple Windows for the Same Workbook
Useful Inconsistencies of New Windows
Hiding and Protecting Workbooks
Hiding WorkbooksProtecting WorkbooksSaving Workbooks or Windows as HiddenHiding Worksheets
3. Formatting and Editing Worksheets
7. Worksheet Editing Techniques
Copying, Cutting, and PastingCopying and PastingCollecting Multiple Items on the ClipboardPasting MultiplesUsing the Paste Options Smart TagCutting and PastingPasting Selectively Using Paste SpecialPasting Using Math OperatorsPasting LinksSkipping Blank CellsTransposing EntriesUsing Paste Special with ArraysPasting HyperlinksMoving and Copying with the Mouse
Inserting and Deleting
Inserting Columns and RowsInserting CellsInserting Copied or Cut CellsDeleting Cells, Columns, and RowsInserting, Deleting, and Clearing Cells with the MouseDragging with the Right Mouse Button
Undoing Previous Actions
Redoing What You've UndoneRepeating Your Last Action
Editing Cell Contents
Editing in the Formula BarEditing Directly in CellsEditing OptionsClearing Cell Contents and FormatsFilling and Creating Data SeriesExtending with AutoFillDragging the Fill Handle with the Right Mouse ButtonCreating Custom ListsImporting Custom ListsUsing Fill Options on the Edit MenuExtending Existing Formatting
Finding and Replacing Stuff
Finding FormattingSpecifying Variables Using Wildcard CharactersReplacing What You Find
Editing Multiple Worksheets
Grouping Sheets for EditingEditing by GroupFilling a Group
Getting the Words Right
Fixing Errors as You TypeFormatting As You TypeUsing Custom AutoCorrect Smart TagsLetting Excel Help with Typing ChoresCreating Your Own Typing ShorthandCheking Yer Speling
8. Worksheet Formatting Techniques
Formatting FundamentalsPainting FormatsTurbo Formatting with AutoFormat
Formatting in Cells
Formatting Individual CharactersFormatting as You TypeUsing the General FormatFormatting NumbersFormatting CurrencyUsing the Euro Currency Tools Add-inUsing Accounting FormatsUsing Accounting UnderlinesFormatting PercentagesFormatting FractionsFormatting Scientific (Exponential) ValuesUnderstanding the Text FormatUsing the Special Formats
Creating Custom Formats
Creating New Number FormatsCreating Four-Part FormatsAdding Color to FormatsUsing Custom Format Condition Operators
Aligning Data in Cells
Aligning Text HorizontallyIndenting Cell ContentsDistributing Cell ContentsCentering Text Across ColumnsFilling Cells with CharactersWrapping Text in CellsJustifying Text in Cells
Aligning Text Vertically
Controlling Text OrientationShrinking Text to Fit in CellsSelecting Alignment Using Toolbars
Using Fonts
Formatting Fonts with Toolbar Buttons
Customizing Borders
Applying Border Formats with Toolbar ButtonsDrawing Borders
Applying Colors and Patterns
Adding Colors with ToolbarsAdding Graphic Backgrounds to Worksheets
Controlling the Size of Cells
Changing Column WidthsUsing Column CommandsChanging Row HeightsUsing Row CommandsMerging and Unmerging Cells
9. Advanced Formatting and Editing Techniques
Applying Conditional Formats
Formatting with Styles
Applying a StyleDefining StylesDefining Styles by ExampleDefining Styles from ScratchModifying a StyleOverriding a Style with Direct Formatting ChangesMerging Styles from Different WorkbooksDeleting a Style
Using Template Files to Store Formatting
Adding Templates to the XLStart Folder
Auditing and Documenting Worksheets
Checking for ErrorsEvaluating and Auditing FormulasWatching FormulasTracing Cell ReferencesTracing Dependent CellsClearing Tracer ArrowsTracing Precedent CellsTracing ErrorsTracing References to Other WorksheetsAdding Comments to CellsPrinting CommentsUsing Go To SpecialSelecting Precedents and DependentsSelecting Row or Column DifferencesHaving Excel Read Cells to You
Outlining Worksheets
Outlining a Worksheet with Nonstandard LayoutExtending the Outline to New Worksheet AreasHiding or Clearing an OutlineCollapsing and Expanding Outline LevelsDisplaying a Specific Outline LevelSelecting Only Visible CellsUngrouping and Grouping Columns and Rows
Consolidating Worksheets
Consolidating by PositionConsolidating by CategoryCreating Links to the Source Worksheets
4. Adding Graphics and Printing
10. Creating Spiffy Graphics
Using the Drawing ToolsDrawing Constrained ObjectsUsing Tear-Off Palettes
Creating Graphic Objects
Drawing Freehand Lines and PolygonsAdjusting Freehand Shapes with the Edit Points CommandWorking with CurvesWorking with Text BoxesWorking with AutoShapesUsing Connectors and CalloutsMore AutoShapesShape ShiftingCreating WordArt
Working with Graphic Objects
Selecting and Grouping ObjectsSelecting Objects That Contain TextFormatting ObjectsChanging Colors in the PaletteAdding TransparencyFormatting Lines and ArrowsApplying ShadowsApplying 3-D EffectsFormatting Objects That Contain TextSetting the Default Format for ObjectsPositioning ObjectsTools to Help You Position Objects on the WorksheetProtecting Objects
Inserting Other Objects
Using Clip Art and Other MediaManaging Your Clip Organizer ExhibitionsImporting GraphicsInserting PicturesInserting Organization ChartsInserting DiagramsInserting Scanned ImagesFormatting Pictures
More Tricks with Objects
Assigning Macros to ObjectsTaking Pictures of Your WorksheetsUsing the Camera ButtonUsing the Copy Picture and Paste Picture Commands
Gallery of Spiffy Examples
11. Printing and Presenting
Controlling the Appearance of Your PagesSetting Page OptionsPrinting Wide or TallSetting a Reduction RatioSpecifying Paper Size and Print QualitySetting the First Page NumberSetting MarginsCentering Your Work on the PageCreating a Header and FooterCreating Custom Headers and FootersAdding Pictures to Headers and FootersChanging Fonts in Headers and FootersSetting Sheet OptionsSpecifying Rows and Columns to Print on Every PagePrinting GridlinesPrinting CommentsPrinting DraftsTranslating Screen Colors to Black and WhitePrinting Row and Column HeadingsSetting the Printing Order of Large Print Ranges
Adjusting Page Breaks
Inserting and Removing Manual Page Breaks
Using Print Preview
Specifying What and Where to Print
Defining a Default Print Area
Setting Printer Driver Options
5. Creating Formulas and Performing Data Analysis
12. Building Formulas
Formula FundamentalsUnderstanding the Precedence of OperatorsUsing Cell References in FormulasEntering Cell References with the MouseUnderstanding Relative, Absolute, and Mixed ReferencesCreating References to Other Worksheets in the Same WorkbookCreating References to Worksheets in Other WorkbooksUnderstanding Row-Column Reference StyleHow Copying Affects Cell ReferencesEditing FormulasUnderstanding Reference SyntaxUsing Numeric Text in FormulasAbout Text Values
Using Functions: A Preview
Using the AutoSum ButtonInserting FunctionsCreating Three-Dimensional FormulasFormula-Bar FormattingCreating Natural-Language FormulasSubstituting Labels Automatically
Naming Cells and Cell Ranges
Using Names in FormulasDefining and Managing NamesEditing NamesWorkbook-Wide vs. Worksheet-Only NamesCreating Names SemiautomaticallyNaming Constants and FormulasUsing Relative References in Named FormulasCreating Three-Dimensional NamesPasting Names into FormulasCreating a List of NamesReplacing References with NamesUsing Go To with Named Ranges
Understanding Error Values
Worksheet Calculation
Recalculating ManuallyCopying Formulas and Pasting Only Their Resulting ValuesEvaluating Part of a FormulaUsing Circular ReferencesUnderstanding the Precision of Numeric Values
Using Arrays
One-Dimensional ArraysArray Formula RulesTwo-Dimensional ArraysSingle-Cell Array FormulasUsing Array ConstantsUnderstanding Array Expansion
Linking Workbooks
Saving Linked WorkbooksOpening a Dependent WorkbookEditing LinksCopying, Cutting, and Pasting in Linked WorkbooksCopying and Pasting between WorkbooksCutting and Pasting between WorkbooksCutting and Pasting Cells Referred to by Dependent Formulas
Creating Conditional Tests
Using the Conditional Sum and Lookup WizardsCreating Conditional Sum FormulasCreating Lookup Formulas
13. Using Functions
Using Excel's Built-In Function Reference
Installing the Analysis ToolPak
Exploring the Syntax of Functions
Expressions as ArgumentsTypes of ArgumentsNumeric ValuesText ValuesLogical ValuesNamed ReferencesArraysMixed Argument Types
Inserting Functions
Inserting References and Names
14. Everyday Functions
Understanding Mathematical FunctionsUsing the SUM FunctionThe AutoSum ButtonUsing Selected Mathematical FunctionsThe PRODUCT and SUMPRODUCT FunctionsThe MOD FunctionThe COMBIN FunctionThe RAND and RANDBETWEEN FunctionsUsing the Rounding FunctionsThe ROUND, ROUNDDOWN, and ROUNDUP FunctionsThe EVEN and ODD FunctionsThe FLOOR and CEILING FunctionsThe INT FunctionThe TRUNC Function
Understanding Text Functions
Using Selected Text FunctionsThe TEXT FunctionThe DOLLAR FunctionThe LEN FunctionThe ASCII Functions: CHAR and CODEThe Clean-Up Functions: TRIM and CLEANThe EXACT FunctionThe Case Functions: UPPER, LOWER, and PROPERUsing the Substring Text FunctionsThe FIND and SEARCH FunctionsThe RIGHT and LEFT FunctionsThe MID FunctionThe CONCATENATE Function
Understanding Logical Functions
Using Selected Logical FunctionsThe IF FunctionThe AND, OR, and NOT FunctionsNested IF FunctionsOther Uses for Conditional Functions
Understanding Information Functions
Using Selected Information FunctionsThe TYPE and ERROR.TYPE FunctionsThe COUNTBLANK FunctionUsing the IS Information Functions
Understanding Lookup and Reference Functions
Using Selected Lookup and Reference FunctionsThe VLOOKUP and HLOOKUP FunctionsThe LOOKUP FunctionThe ADDRESS FunctionThe CHOOSE FunctionThe MATCH FunctionThe INDEX FunctionThe INDIRECT FunctionThe ROW and COLUMN FunctionsThe ROWS and COLUMNS FunctionsThe AREAS FunctionThe TRANSPOSE Function
15. Formatting and Calculating Date and Time
Understanding How Excel Records Dates and Times
Entering Dates and Times
Entering a Series of DatesExtending an Existing Date Series
Formatting Dates and Times
Creating Your Own Date and Time FormatsMeasuring Elapsed Time
Calculating with Date and Time
Working with Date and Time Functions
Using the TODAY and NOW FunctionsUsing the WEEKDAY FunctionUsing the YEAR, MONTH, and DAY FunctionsUsing the HOUR, MINUTE, and SECOND FunctionsUsing the DATEVALUE and TIMEVALUE FunctionsWorking with Specialized Date FunctionsUsing the EDATE and EOMONTH FunctionsUsing the YEARFRAC FunctionUsing the WORKDAY and NETWORKDAYS Functions
16. Functions for Financial Analysis
Calculating InvestmentsThe PV FunctionThe NPV FunctionThe FV FunctionThe PMT FunctionThe IPMT FunctionThe PPMT FunctionThe NPER FunctionThe RATE FunctionThe IRR FunctionThe MIRR Function
Calculating Depreciation
The SLN FunctionThe DDB and DB FunctionsThe VDB FunctionThe SYD Function
Analyzing Securities
The DOLLARDE and DOLLARFR FunctionsThe ACCRINT and ACCRINTM FunctionsThe INTRATE and RECEIVED FunctionsThe PRICE, PRICEDISC, and PRICEMAT FunctionsThe DISC FunctionThe YIELD, YIELDDISC, and YIELDMAT FunctionsThe TBILLEQ, TBILLPRICE, and TBILLYIELD FunctionsThe COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD FunctionsThe DURATION and MDURATION Functions
17. Functions for Analyzing Statistics
Analyzing Distributions of DataUsing Built-In Statistical FunctionsThe AVERAGE FunctionThe MEDIAN, MODE, MAX, MIN, and COUNT FunctionsThe SUMIF and COUNTIF FunctionsUsing Functions That Analyze Rank and PercentileThe PERCENTRANK FunctionThe PERCENTILE and QUARTILE FunctionsThe SMALL and LARGE FunctionsThe RANK FunctionUsing Sample and Population Statistical FunctionsCalculating Sample Statistics: VAR and STDEVCalculating Total Population Statistics: VARP and STDEVP
Understanding Linear and Exponential Regression
Calculating Linear RegressionThe LINEST FunctionThe TREND FunctionThe FORECAST FunctionThe SLOPE FunctionThe STEYX FunctionCalculating Exponential RegressionThe LOGEST FunctionThe GROWTH Function
Using the Analysis Toolpak Data Analysis Tools
Using the Descriptive Statistics ToolCreating HistogramsAnalyzing Distribution with the FREQUENCY FunctionUsing the Rank and Percentile ToolGenerating Random NumbersRandom Number Distribution MethodsDistributing Random Numbers UniformlyDistributing Random Numbers NormallyGenerating Random Numbers Using Bernoulli DistributionGenerating Random Numbers Using Binomial DistributionGenerating Random Numbers Using Poisson DistributionGenerating Random Numbers Using Discrete DistributionGenerating Semi-Random Numbers Using Patterned DistributionSampling a Population of NumbersCalculating Moving Averages
18. Performing What-If Analysis
Using Data TablesData Tables Based on One Input VariableSingle-Variable Tables with More Than One FormulaData Tables Based on Two Input VariablesEditing Tables
Using the Scenario Manager
Defining ScenariosBrowsing Your ScenariosAdding, Editing, and Deleting ScenariosTracking ChangesRouting and Merging ScenariosCreating Scenario ReportsThe Scenario Summary ReportThe Scenario PivotTable Report
Using the Goal Seek Command
Precision and Multiple Solutions
Using the Solver
Stating the ObjectiveSpecifying Variable CellsSpecifying ConstraintsSpecifying Integer ConstraintsSaving and Reusing the Solver ParametersAssigning the Solver Results to Named ScenariosOther Solver OptionsLinear ModelsViewing Iteration ResultsGenerating ReportsThe Sensitivity ReportThe Answer ReportThe Limits Report
6. Collaboration and the Internet
19. Collaborating with Excel
Saving and Retrieving Files on Remote Computers
Sharing Workbooks on a Network
Using Advanced Sharing OptionsTracking ChangesProtecting the Change HistoryReviewing ChangesCanceling the Shared Workbook Session
Combining Changes Made to Multiple Workbooks
Merging Workbooks
Distributing Workbooks and Worksheets by E-Mail
Sending an Entire Workbook as an E-Mail AttachmentSending the Current Sheet as the Body of an E-Mail MessageSending a Workbook for ReviewRouting Workbooks to a Workgroup
Controlling Document Access with Information Rights Management
Protecting a Document with IRMSetting an Expiration DateAllowing Users to PrintEnabling the Copy CommandAllowing Programmatic AccessMaking the Current Settings the DefaultAdding New Users and Modifying Permissions SettingsUsing a Protected Document
Using a SharePoint Team Services Site
Downloading and Uploading DocumentsChecking Documents In and OutUsing the Shared Workspace Task PaneContacting Team MembersGetting Document Status InformationAssigning and Monitoring TasksOpening Related DocumentsUsing LinksGetting Information About the Current DocumentTracking Changes with Document VersioningCreating a New Document WorkspaceCreating a Document Workspace from Within ExcelCreating a Document Workspace via E-MailCreating a Document Workspace from Within SharePointManaging a Document Workspace
Using Web Discussions
20. Transferring Files to and from Internet Sites
Working with FTP SitesAdding a Site to Your My Places Bar
Saving and Publishing Excel Files in HTML
Considering the OptionsDo You Want Interactivity?To Save or To Publish?Single-File Web Page or Web Page?Saving an Entire Workbook Without InteractivityChanging the TitlePublishing Without InteractivityPublishing with Interactivity
Using the Interactive Web Components
7. Integrating Excel with Other Applications
21. Linking and Embedding
Embedding vs. Linking
Embedding vs. Static Pasting
Embedding and Linking from the Clipboard
Embedding and Linking with the Object Command
Manipulating Embedded Objects
Managing Links
Choosing Automatic or Manual UpdateUpdating on File OpenFixing Broken Links
Linking vs. Hyperlinking
22. Using Hyperlinks
Creating a Hyperlink in a CellTurning Ordinary Text into a HyperlinkLinking to a Web Site or Local FileLinking to a Location in the Current DocumentLinking to a New FileLinking to an E-Mail Message
Assigning a Hyperlink to a Graphic, Toolbar Button, or Menu Command
Editing, Removing, and Deleting a HyperlinkFormatting a Hyperlink
Using the HYPERLINK Function
23. Using Excel Data in Word and PowerPoint Documents
Using Excel Tables in Word DocumentsPasting an Excel Table from the ClipboardUsing Paste Special to Control the Format of Your TableUsing the Microsoft Excel Worksheet ObjectUsing Formatted Text (RTF) and HTML FormatUsing Unformatted Text and Unformatted Unicode TextUsing Picture, Bitmap, and Picture (Enhanced Metafile)Paste-Linking an Excel Table into WordLinking to Named RangesLinking with HyperlinksUsing the Object Command
Using Excel Charts in Word Documents
Using Excel to Supply Mail-Merge Data to Word
Using Excel Data in PowerPoint
Paste-Linking Excel Data into PowerPointUsing Excel Charts in PowerPoint
8. Creating Charts
24. Basic Charting Techniques
Creating a New ChartStep 1: Choosing a Chart TypeStep 2: Specifying the Data to PlotStep 3: Choosing Chart OptionsSpecifying Chart and Axis TitlesDisplaying or Hiding AxesCategory vs. Time ScalingManual Scaling and Other Axis Formatting OptionsDisplaying GridlinesDisplaying or Hiding a LegendDisplaying Data LabelsAdding a Data TableStep 4: Telling Excel Where to Put Your Chart
Creating Combination (Overlay) Charts
Changing a Chart's Size and Position
Plotting Hidden Cells
Handling Missing Values
Changing the Default Chart Type
Printing Charts
Saving, Opening, and Protecting Charts
Working with Embedded Chart Objects
25. Enhancing the Appearance of Your Charts
Working with the Chart Menu and Chart Toolbar
Selecting Chart Elements
Copying Formats from One Chart to Another
Adding a Customized Chart to the Chart Wizard Gallery
Repositioning Chart Elements with the Mouse
Moving and Resizing the Plot Area
Working with Titles
Creating a Two-Line TitleFormatting a TitleFormatting Individual Characters in a Title
Adding Text Annotations
Working with Data Labels
Label Positioning and Alignment OptionsNumeric Formatting Options for Data LabelsFont and Patterns Options for Data LabelsEditing Data LabelsPositioning and Formatting Data Labels IndividuallyGenerating Useful Data Labels on XY (Scatter) Charts
Working with Axes
Specifying the Line Style, Color, and WeightSpecifying the Position of Tick Marks and Tick-Mark LabelsChanging the Numeric Format Used by Tick-Mark LabelsScaling Axes ManuallyScaling a Value Axis ManuallyScaling a Time-Scaled Category Axis Manually
Adding, Removing, and Formatting Gridlines
Formatting Data Series and Markers
Assigning a Series to a Secondary Value AxisUsing Two or More Chart Types in the Same ChartChanging the Series OrderToggling the Column/Row OrientationChanging Colors, Patterns, Fills, and Borders for MarkersUsing the Invert If Negative OptionUsing the Vary Colors by Point (Slice) OptionAdjusting Spacing in Two-Dimensional Column and Bar ChartsAdjusting Data Point Spacing in Three-Dimensional ChartsAdding Series Lines in Stacked Column and Bar ChartsChanging Shapes in Three-Dimensional Column and Bar ChartsSmoothing the Lines in Line and XY (Scatter) ChartsChanging Line and Marker Styles in Line, XY (Scatter), and Radar ChartsAdding High-Low Lines and Up and Down Bars to Line ChartsAdding Drop Lines to Area and Line ChartsExploding Pie Slices and Doughnut BitesUsing Formatting and Split Options in Pie-Column and Pie-Pie ChartsChanging the Angle of the First Pie Slice or Doughnut Bite
Working with Data Tables
Formatting Background Areas
Filling an Area with a Color GradientFilling an Area with a PatternFilling an Area with a Texture or Picture
Changing Three-Dimensional Viewing Angles
Adjusting the ElevationChanging the RotationChanging the HeightChanging the PerspectiveChanging the Axis Angle and Scale
26. Working with Chart Data
Adding DataUsing Copy and PasteAdding SeriesUsing List Features to Create Expanding Charts
Removing Data
Changing or Replacing Data
Plotting or Marking Every nth Point
Changing the Plot Order
Using Multilevel Categories
Adding Trend Lines
Adding Error Bars
What-If Charting: Dragging Markers to Change Data
27. Advanced Charting Techniques
Using Named Ranges to Create Dynamic ChartsPlotting New Data AutomaticallyPlotting Only the Most Recent Points
Using Arrays to Create a Static Chart
Using Bubble Charts
Using Radar Charts
Creating Gannt Charts
Assorted Formatting Issues
Tick-Mark Labels Without AxesTick-Mark Labels on the Plot AreaFormatting Selected Gridlines or Tick-Mark LabelsStaggered Tick-Mark LabelsPlotting Your Own Projection (Extrapolation) Line
9. Managing Databases and Lists
28. Managing Information in Lists
Building and Maintaining a ListUsing Label-Based Formulas in Calculated ColumnsTurning on the Label-Based Formula FeatureMaking Label-Based Formulas Available in All WorkbooksUsing (or Disabling) Other List-Building AidsAutomatic Format and Formula ExtensionAutoComplete for Cell ValuesMove on EnterCustom Lists
Working with List Objects
Publishing a List ObjectDealing with Data Type and Formatting IssuesToggling the Total RowResizing a List ObjectInserting and Deleting Rows and Columns within a List Object
Validating Data Entry
Specifying Data Type and Acceptable ValuesSpecifying an Input Message (Prompt)Specifying Error Alert Style and Message
Using Excel's Form Command to Work with Lists
Adding RowsFinding Records
Sorting Lists and Other Ranges
Sorting on a Single ColumnSorting on More than One ColumnSorting Only Part of a ListSorting by ColumnsSorting Cells That Contain FormulasSorting Months, Weekdays, or Custom ListsPerforming a Case-Sensitive SortMaking Case-Sensitive Sorting the Default
Filtering a List
Using the AutoFilter CommandUsing AutoFilter Criteria in More than One ColumnUsing AutoFilter to Find the Top or Bottom n ItemsUsing AutoFilter to Display Blank or Nonblank EntriesUsing the Custom Option to Specify More Complex CriteriaFinding an Alphabetical Range of Text ValuesUsing Wildcards in Custom CriteriaPerforming Aggregate Calculations on AutoFiltered DataRemoving AutoFilter CriteriaUsing the Advanced Filter CommandSpecifying a Criteria RangeAn Example Using Two Columns Joined by ORAn Example Using Three ORs on a ColumnAn Example Using Both OR and ANDApplying Multiple Criteria to the Same ColumnUsing Computed CriteriaExtracting Filtered RowsPerforming Aggregate Calculations on Filtered Data
Using Subtotals to Analyze a List
Subtotaling on More Than One ColumnSubtotaling with More Than One Aggregation FormulaUsing Automatic Page BreaksRemoving or Replacing SubtotalsGrouping by DateUsing the SUBTOTAL Function
Using Functions to Extract Details from a List
The Database Statistical FunctionsCOUNTIF and SUMIFUsing the Conditional Sum WizardCOUNTBLANKVLOOKUP and HLOOKUPUsing the Lookup WizardMATCH and INDEXUsing INDEX with a List Control to Create a Data Browser
29. Working with External Data
Using File, Open to Import External Data FilesOpening Text FilesUsing the Text Import WizardParsing Clipboard TextOpening Microsoft Access Tables in ExcelOpening dBase Files
Working with XML Files
Opening or Importing an XML ListRefreshing an XML ListManipulating Columns in an XML ListCreating an Ad Hoc Mapping of XML Elements to List ColumnsExporting an XML List
Using a Query to Retrieve External Data
Reusing an Existing QueryCreating a New Database QueryCreating a Simple ExampleModifying a Query's Refresh Behavior and Other PropertiesEditing a QueryWorking Directly with Microsoft QueryGetting to QueryAdding and Removing TablesWorking with JoinsAdding, Removing, and Moving FieldsRenaming FieldsSorting the Result SetFiltering the Result SetPerforming Aggregate CalculationsCreating a Parameter-Based QuerySaving a QueryReturning the Result Set to Excel
Using a Web Query to Return Internet Data
Using an Existing Web QueryCreating Your Own Web QueryUsing the New Web Query CommandCopying and Pasting from the Web BrowserUsing Internet Explorer's Export To Microsoft Excel Command
30. Analyzing Data with PivotTable Reports
A Simple Example
Creating a PivotTable
Starting the PivotTable And PivotChart WizardStep 1: Specifying the Type of Data SourceStep 2: Indicating the Location of Your Source DataStep 3: Telling the Wizard Where to Put Your PivotTableLaying Out the PivotTable
Pivoting a PivotTable
Using the Page AxisDisplaying Totals for a Field in the Page AreaMoving Page Fields to Separate Workbook PagesSelecting Items to Display on the Row and Column Axes
Creating a PivotChart
Refreshing a PivotTable
Refreshing on File Open
Selecting Elements of a PivotTable
Formatting a PivotTable
Using AutoFormat with PivotTablesChanging the Numeric Format for the Data AreaChanging the Way a PivotTable Displays Empty CellsChanging the Way a PivotTable Displays Error ValuesMerging Labels
Using Multiple Data Fields
Renaming Fields and Items
Sorting Items
Using AutoSortRearranging Items by Hand
Showing the Top or Bottom Items in a Field
Hiding and Showing Inner Field Items
Displaying the Details Behind a Data Value
Grouping and Ungrouping Data
Creating Ad Hoc Item GroupingsGrouping Numeric ItemsGrouping Items in Date or Time RangesRemoving Groups (Ungrouping)
Using Grand Totals and Subtotals
Grand TotalsSubtotalsSubtotals for Innermost Fields
Changing a PivotTable's Calculations
Using a Different Summary FunctionApplying Multiple Summary Functions to the Same FieldUsing Custom CalculationsUsing Calculated Fields and ItemsCreating a Calculated FieldCreating a Calculated ItemDisplaying a List of Calculated Fields and Items
Referencing PivotTable Data from Worksheet Cells
Creating a PivotTable from External Data
Refreshing PivotTable Data from an External SourceRefreshing at Regular Time Intervals
Using a PivotTable to Consolidate Ranges
Building a PivotTable from an Existing PivotTable
Printing PivotTables
Using Row and Column Headings as Print TitlesRepeating Item Labels on Each Printed PagePrinting Each Outer Row Field Item on a New Page
Using the PivotTable Web Component
10. Automating Excel
31. Recording Macros
Using the Macro RecorderRunning a Macro Without Using a Keyboard Shortcut
Behind the Scenes: The VBA Environment
Getting Help on VBA KeywordsObjects, Methods, and PropertiesThe Object BrowserCollections of ObjectsManipulating an Object's Properties Without Selecting the ObjectNaming Arguments to Methods
Adding Code to an Existing Macro
Using Absolute and Relative References
Macro Subroutines
Using the Personal Macro Workbook
Going On from Here
32. Creating Custom Functions
Using Custom FunctionsWhat's Happening
Understanding Custom Function Rules
Using VBA Keywords in Custom Functions
Documenting Macros and Custom Functions
Creating Custom Functions with Optional Arguments
Making Your Custom Functions Available Anywhere
33. Debugging Macros and Custom Functions
Using Design-Time ToolsCatching Syntax ErrorsCatching Misspelled Variable NamesStepping Through CodeSetting Breakpoints with the Toggle Breakpoint CommandSetting Conditional Breakpoints Using Debug.AssertUsing the Watch Window to Monitor Variable Values and Object PropertiesSetting Conditional Breakpoints with the Watch WindowUsing Quick Watch to Monitor a Variable or Add a Watch ItemUsing the Immediate Window
Dealing with Run-Time Errors
11. Appendixes
A. Installing Microsoft Excel
System RequirementsAdditional Requirements and Recommendations
Installing Office
Uninstalling Office
Installing Additional Components
Installing International FeaturesUsing On-Screen Keyboard
Repairing Your Office Installation
B. Using Speech and Handwriting Recognition
Using the Language BarControlling the Language Bar
Using Speech Recognition
Training Your Computer and Your VoiceIssuing Verbal CommandsSpecial Voice CommandsUsing Your Voice to Input TextVoice Commands for DictationSpeaking PunctuationForcing Numeric RecognitionSpelling Out Words
Using Handwriting Recognition
Setting Handwriting Options
C. Keyboard Shortcuts
Charts and Select Chart Elements
Data Forms
Dialog Box Edit Boxes
Dialog Boxes
Edit Data
Enter and Calculate Formulas
Enter Data
Enter Special Characters
Extend a Selection
Filter Lists
Format, Cells Dialog Box—Border Tab
Format Data
Help
Help Window
Insert, Delete, and Copy Cells
Languages
Macros
Menus and Toolbars
Move and Scroll—In End Mode
Move and Scroll—Worksheets
Move Within a Selected Range
Open, Save As, and Insert Picture Dialog Boxes
PivotTable and PivotChart Wizard Layout Dialog Box
PivotTable—Display and Hide Items
PivotTable—Change the Layout
Print
Print Preview
Select Cells, Rows, Columns, and Objects
Select Cells with Special Characteristics
Send E-Mail Messages
Show, Hide, and Outline Data
Smart Tags
Speech Recognition and Text-To-Speech
Task Panes
Windows and Office Interface
Worksheets
D. Function Reference
Index
SPECIAL OFFER: Upgrade this ebook with O’Reilly

Content preview from Microsoft® Office Excel 2003 Inside Out

Chapter 29. Working with External Data

Microsoft Excel is a superb tool for analyzing data, but before you can do any analysis, you have to get the data into Excel. In many cases, the information you need to work with resides somewhere "outside"—in a mainframe text file, on a Web site, in an XML file, or in a database program such as Oracle or Microsoft Access. Excel provides excellent tools for importing that information onto your worksheets; those tools are the subject of this chapter.

Using File, ...

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: 073561511XCatalog Page Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Office Excel 2003 Inside Out

by Craig Stinson, Mark Dodge

Chapter 29. Working with External Data

Using File, ...

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.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

Show Me Microsoft® Office Excel 2003

Special Edition Using® Microsoft® Office Excel 2003

Formulas and Functions with Microsoft® Excel 2003

Formulas and Functions: Microsoft® Excel 2010

Publisher Resources