book

Microsoft® Office Excel® 2007 Inside Out

Name: Microsoft® Office Excel® 2007 Inside Out
ISBN: 9780735623217

by Mark Dodge, Craig Stinson

January 2007

Intermediate to advanced

1008 pages

32h 43m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Office Excel® 2007 Inside Out
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
Acknowledgments
About the CD
What’s on the CD
System Requirements
Support Information
Conventions and Features Used in This Book
Text Conventions
Design Conventions
1. Examining the Excel Environment

1. What’s New in Microsoft Office Excel 2007
New and Improved for 2007Results-Oriented User InterfaceGraphic EnhancementsImproved Formatting and Output ToolsPage Layout ViewNew TemplatesUpdated Conditional-Formatting FeaturesIncreased Capacity and SpeedBetter and Easier TablesImproved PivotTables and PivotChartsBetter Database ConnectivityFormula AutoCompleteTools for Creating FormulasBuilt-In Analysis Toolpak FunctionsEnhanced Charting FeaturesNew XLSX File FormatImproved Sorting and FilteringCollaboration EnhancementsEnhanced Security Features
Retired in 2007
If You Missed the Last Upgrade . . .
Onward . . .
2. Exploring Excel Fundamentals
What Happens After You Install Excel?Activating ExcelGetting Updates
Examining the Excel 2007 Workspace
Facts About WorksheetsUsing the Workbook WindowThe Title BarGetting Around in the WorkbookWorkbook Navigation TipsResizing the WindowExploring the RibbonThe Ribbon SpeaksDrop-Down Lists and Dialog Box LaunchersGalleries and Live PreviewIntroducing Contextual Tool SetsWhere Is the File Menu?Using the Quick Access ToolbarAccessing Commands with the KeyboardThe Joy of Shortcut MenusMeet the Formula BarFacts About the Status Bar
Exploring File Management Fundamentals
Creating WorkbooksInstalling Your Own TemplatesCreate Your Own Template TabsSaving FilesRules for File NamingFile FormatsEnsuring File Compatibility with Previous Versions of ExcelUnderstanding the "XL" 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 Files
Importing and Exporting Files
Using the Open and Save As Commands to Import and Export FilesSharing Data with Excel for the MacintoshSharing Data Beyond ExcelUsing Web File FormatsImporting and Exporting Text FilesOther File Formats
Using the Online Help System
Help on the SurfaceHelp In Depth
Recovering from Crashes
Using AutoRecoverCalling Dr. Office
3. Custom-Tailoring the Excel Workspace
Customizing the Quick Access ToolbarPositioning the ToolbarAdding Tools to the ToolbarAdding Tools As You WorkRemoving ToolsAdding and Organizing ToolsIdentifying Items in the Customize Dialog BoxToo Many Tools?Creating Your Own ButtonsAttaching Custom Toolbar Configurations to WorkbooksRestoring the ToolbarExploring Other Toolbar and Ribbon Options
Controlling Other Elements of the Excel 2007 Interface
Displaying Underlying FormulasHiding Zeros
Enhancing Accessibility
4. Security and Privacy
The Trust CenterTrusted Publishers and LocationsAdd-Ins, ActiveX Settings, and Macro SettingsMessage BarExternal ContentPrivacy Options
File Security
Removing Personal Information from Your WorkbooksUsing Digital Signatures
Other Security Features
2. Building Worksheets
5. Planning Your Worksheet Design
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 for New Data?
6. How to Work a Worksheet
Moving Around RegionsNavigating Regions with the KeyboardNavigating Regions with the MouseNavigating with Special Keys
Understanding Selection
Selecting with the MouseZooming to Select Large Worksheet AreasSelecting Columns, Rows, and Multiple AreasSelecting RegionsUsing the Find & Select CommandsSelecting with Go To SpecialSelecting Precedents and DependentsSelecting Row or Column Differences
Techniques for Entering Data
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 Text WrappingUnderstanding Numeric Text EntriesEntering SymbolsMaking Entries in RangesEditing and Undoing Entries
Managing Worksheets
Inserting and Deleting WorksheetsNaming and Renaming WorksheetsMoving and Copying Worksheets
Viewing Worksheets
Splitting Worksheets into PanesFreezing PanesZooming WorksheetsUsing Custom Views
Protecting Worksheets
Unlocking Individual CellsProtecting the WorkbookAllowing Password Access to Specific Cell RangesHiding Cells and WorksheetsUsing Passwords
7. How to Work a Workbook
Managing Multiple WorkbooksNavigating Between Open WorkbooksArranging Workbook WindowsGetting the Most Out of Your ScreenComparing Worksheets Side by Side
Opening Multiple Windows for the Same Workbook
Useful Inconsistencies of New Windows
Hiding and Protecting Workbooks
Hiding WorkbooksProtecting WorkbooksEncrypting WorkbooksSaving Workbooks or Windows as HiddenHiding Worksheets
3. Formatting and Editing Worksheets
8. 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 EntriesPasting HyperlinksMoving and Copying with the Mouse
Inserting and Deleting
Inserting Columns and RowsInserting CellsInserting Copied or Cut CellsDeleting Cells, Columns, and RowsClearing CellsInserting, 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 Cells or in the Formula BarEditing OptionsFilling and Creating Data SeriesExtending with AutoFillDragging the Fill Handle with the Right Mouse ButtonUsing the Series CommandUsing the Fill Menu CommandsDistributing Long Entries Using the Justify CommandCreating Custom ListsImporting Custom ListsExtending Existing Formatting
Finding and Replacing Stuff
Finding FormattingSpecifying Variables Using Wildcard CharactersReplacing What You Find
Getting the Words Right
Fixing Errors As You TypeTyping Internet and Network AddressesUsing Custom AutoCorrect Smart TagsLetting Excel Help with Typing ChoresCheking Yer SpelingResearch Resources
Editing Multiple Worksheets
Grouping Worksheets for EditingWhat You Can Do in Group-Editing ModeFilling a Group
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 CellsTweaking Your CommentsPrinting Comments
Outlining Worksheets
Outlining a Worksheet with Nonstandard LayoutExtending the Outline to New Worksheet AreasHiding an OutlineCollapsing and Expanding Outline LevelsDisplaying a Specific Outline LevelUngrouping and Grouping Columns and Rows
Consolidating Worksheets
Consolidating by PositionConsolidating by CategoryCreating Links to the Source Worksheets
9. Worksheet Formatting Techniques
Formatting FundamentalsFormatting TablesOptions for Applying Table FormatsCreating Custom Table FormatsRemoving the Automatic Table FeaturesPainting Formats
Using Themes and Cell Styles
Formatting with ThemesCreating Custom ThemesFormatting with Cell StylesCreating Custom Cell StylesCreating Cell Styles by ExampleModifying and Duplicating Cell StylesMerging Cell Styles from Different WorkbooksDeleting a Cell Style
Formatting Conditionally
Creating Conditional Formatting RulesManaging Conditional Formatting RulesCopying, Clearing, and Finding Conditional FormatsCreating Conditional Formatting Formulas
Formatting in Depth
Formatting Individual CharactersFormatting As You TypeUnderstanding the General FormatFormatting NumbersUsing Currency FormatsUsing Accounting FormatsFormatting PercentagesFormatting FractionsFormatting Scientific (Exponential) ValuesUnderstanding the Text FormatUsing the Special FormatsCreating Custom Number FormatsCreating New Number FormatsCreating Four-Part FormatsAdding Color to FormatsUsing Custom Format Conditional OperatorsAligning Data in CellsAligning Text HorizontallyIndenting Cell ContentsDistributing Cell ContentsCentering Text Across ColumnsFilling Cells with CharactersWrapping Text in CellsJustifying Text in CellsAligning Text VerticallyControlling Text OrientationShrinking Text to Fit in CellsUsing FontsCustomizing BordersApplying Colors and PatternsAdding Graphic Backgrounds to WorksheetsControlling the Size of CellsChanging Column WidthsChanging Row HeightsMerging and Unmerging Cells
Using Template Files to Store Formatting
4. Adding Graphics and Printing
10. Creating Spiffy Graphics
Using the Shapes ToolsDrawing Constrained ObjectsDrawing Freehand Lines and PolygonsAdjusting Freehand Shapes with the Edit Points CommandWorking with CurvesWorking with Text BoxesAdding Text to Other ShapesWorking with ShapesUsing Connectors and Callouts
Creating WordArt
Creating SmartArt
Inserting Graphics
Inserting Clip Art and Media FilesManaging ClipsInserting PicturesImporting PicturesInserting Other Objects
Formatting Graphics
Formatting Text in GraphicsApplying Compression to PicturesUsing the Format Shape Dialog BoxFormatting Fills and LinesGradient FillsGradient StopsPicture or Texture FillLine Colors and StylesApplying ShadowsApplying 3-D EffectsFormatting PicturesFormatting Objects That Contain TextFormatting Embedded Objects
Working with Graphic Objects
Selecting and Grouping ObjectsPositioning ObjectsTools to Help You Position Objects on the WorksheetProtecting Objects
More Tricks with Graphic Objects
Assigning Macros to ObjectsTaking Pictures of Your WorksheetsUsing the Camera ButtonUsing the Copy As Picture and Paste As Picture Commands
Gallery of Spiffy Examples
11. Printing and Presenting
Controlling the Appearance of Your PagesSetting Page OptionsPrinting Wide or TallSpecifying Paper SizeSetting a Reduction RatioSetting the First Page NumberWorking in Page Layout ViewSetting MarginsCreating a Header and FooterCreating Custom Headers and FootersAdding Pictures to Headers and FootersChanging Fonts in Headers and FootersSetting Worksheet OptionsSpecifying the Area to Be PrintedSpecifying Rows and Columns to Print on Every PagePrinting Gridlines and HeadingsPrinting Comments and ErrorsPrinting DraftsTranslating Screen Colors to Black and WhiteSetting the Printing Order of Large Print Ranges
Specifying What and Where to Print
Printing ImmediatelyPrinting to a File
Adjusting Page Breaks
Using Page Break PreviewInserting and Removing Manual Page Breaks
Using Print Preview
Setting Printer Driver Options
Creating Portable Documents
5. Creating Formulas and Performing Data Analysis
12. Building Formulas
Formula FundamentalsUnderstanding the Precedence of OperatorsUsing Cell References in FormulasEntering Cell References by ClickingUnderstanding 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 ReferencesCopying Relative ReferencesCopying Absolute ReferencesCopying Mixed ReferencesEditing FormulasUnderstanding Reference SyntaxUsing Numeric Text in FormulasAbout Text ValuesUnderstanding Error Values
Using Functions: A Preview
Using the Sum ButtonInserting a FunctionUsing Formula AutoComplete
Working with Formulas
Naming Cells and Cell RangesUsing Names in FormulasDefining and Managing NamesEditing NamesWorkbook-Wide vs. Worksheet-Only NamesCreating Names SemiautomaticallyNaming Constants and FormulasUsing Relative References in Named FormulasCreating Three-Dimensional NamesUsing Names in FormulasCreating a List of NamesReplacing References with NamesUsing Go To with NamesGetting Explicit About IntersectionsCreating Three-Dimensional FormulasFormula-Bar FormattingUsing Structured ReferencesUnderstanding Structured Reference SyntaxUsing Operators with Column SpecifiersAbout the Special Item SpecifiersUsing Formula AutoComplete with Structured ReferencesFilling and Copying Structured References
Worksheet Calculation
Recalculating ManuallyCalculating Part of a FormulaWorking with 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 Workbooks
Creating Conditional Tests
Using the Conditional Sum and Lookup WizardsCreating Conditional Sum FormulasCreating Lookup Formulas
13. Using Functions
Using the Built-In Function Reference in Excel
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 Sum 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 Cleanup 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 REPLACE and SUBSTITUTE FunctionsThe 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
Using the Euro Currency Tools Add-In
17. Functions for Analyzing Statistics
Analyzing Distributions of DataUsing Built-In Statistical FunctionsThe AVERAGE FunctionThe MEDIAN, MODE, MAX, MIN, and COUNT FunctionsThe SUMIF, SUMIFS, 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
Installing the Analysis ToolpakUsing 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. Creating Charts
19. Basic Charting Techniques
Selecting Data for Your Chart
Choosing a Chart Type
Changing the Chart Type
Switching Rows and Columns
Choosing a Chart Layout
Choosing a Chart Style
Moving the Chart to a Separate Chart Sheet
Adding, Editing, and Removing a Chart Title
Adding, Editing, and Removing a Legend
Adding and Positioning Data Labels
Adding a Data Table
Manipulating Axes
Adding Axis Titles
Changing the Rotation of Chart Text
Displaying Gridlines
Adding Text Annotations
Changing the Font or Size of Chart Text
Applying Shape Styles and WordArt Styles
Saving Templates to Make Chart Formats Reusable
20. Charting Beyond the Ribbon
Selecting Chart Elements
Repositioning Chart Elements with the Mouse
Formatting Lines and Borders
Formatting Areas
Using Transparency to Create a Minimal Chart Display on the WorksheetFilling an Area with a Color GradientFilling an Area with a Texture or Picture
Formatting Text
Working with Axes
Specifying the Line Style, Color, and WeightSpecifying the Position of Tick Marks and Axis LabelsChanging the Numeric Format Used by Axis LabelsChanging the Scale of a Value AxisChanging the Positions of Tick Marks and GridlinesChanging the Point Where Axes IntersectReversing the Value-Axis ScaleUsing Logarithmic ScalingApplying a Scaling FactorChanging the Scale of a Text Category AxisAdjusting the Spacing Between GridlinesCuring Label OverlapAdjusting the Position of Category LabelsChanging the Intersection of the Value AxisSwitching a Category Axis from Text to DateChanging the Scale of a Date Category AxisChanging the Minimum and MaximumChanging the Major and Minor UnitsChanging the Base UnitFormatting a Depth (Series) Axis
Working with Data Labels
Labeling Individual Data PointsLabeling with Ad Hoc Text
Formatting Data Series and Markers
Assigning a Series to a Secondary AxisUsing Two or More Chart Types in the Same Chart
Modifying the Data Source for Your Chart
Using the Mouse to Add Data PointsModifying Data with the Select Data Source CommandUsing Discontiguous Source RangesChanging the Way Excel Plots Empty and Hidden Cells
Using Multilevel Categories
Adding Moving Averages and Other Trendlines
Adding Error Bars
Adding High-Low Lines and Up and Down Bars
7. Managing Databases and Tables
21. Managing Information in Tables
How to Organize a Table
Creating a Table
Overwriting Default HeadersTurning a Table Back into an Ordinary RangeNaming a TableExpanding a Table
Adding Totals to a Table
Sorting Tables and Other Ranges
Sorting on a Single ColumnSorting on More Than One ColumnSorting Only Part of a ListSorting by ColumnSorting Cells That Contain FormulasSorting Months, Weekdays, or Custom ListsPerforming a Case-Sensitive Sort
Filtering a List or Table
Using FiltersDetermining How Many Rows Pass the FilterRemoving a FilterUsing Filter Criteria in More Than One ColumnUsing a Filter to Find the Top or Bottom n ItemsUsing a Filter to Display Blank EntriesUsing Filters to Select DatesUsing Filters to Specify More Complex CriteriaUsing Custom Filters to Specify Complex RelationshipsFinding an Alphabetical Range of Text ValuesUsing Wildcards in Custom 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 CriteriaReferencing Cells Within the ListReferencing a Cell Outside the ListReferencing All Rows in a ColumnExtracting Filtered RowsThe Unique Records Only Check BoxRemoving Duplicate Records
Using Formulas with Tables
Referencing the Total RowExplicitly Referencing the Current RowReferencing Parts of a Table
Formatting Tables
Using Themes to Change Style AppearanceCustomizing Table Styles
22. Analyzing Data with PivotTable Reports
Introducing PivotTables
Creating a PivotTable
Rearranging PivotTable Fields
Refreshing a PivotTable
Changing the Numeric Format of PivotTable Data
Choosing Report Layout Options
Formatting a PivotTable
Customizing the Display of Empty or Error CellsMerging and Centering Field LabelsHiding Outline ControlsHiding Row Labels and Column Labels
Displaying Totals and Subtotals
Customizing Subtotals
Sorting PivotTable Fields
Filtering PivotTable Fields
Changing PivotTable 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
Grouping and Ungrouping Data
Creating Ad Hoc Item GroupingsGrouping Items in Date or Time Ranges
Displaying the Details Behind a Data Value
Creating PivotCharts
23. Working with External Data
Using and Reusing Data ConnectionsSetting Refresh OptionsRequiring or Not Requiring a Password to RefreshRefreshing on Demand
Opening an Entire Access Table in Excel
Working with Data in Text Files
Using the Text Import WizardParsing Clipboard Text
Working with XML Files
Creating an Ad Hoc Mapping of XML Elements to Table ColumnsImporting XML Data Using an Existing XML Structure
Using Microsoft Query to Import Data
Choosing Tables and Fields (Columns)Filtering RecordsSorting RecordsSaving the Query or Moving to Microsoft QueryWorking Directly with Microsoft QueryGetting to QueryAdding and Removing TablesWorking with JoinsAdding, Removing, and Moving FieldsRenaming FieldsSorting the Result SetSorting with the ToolbarFiltering the Result SetCreating Exact-Math CriteriaUsing Multiple Exact-Match CriteriaUsing Menu Commands to Specify Exact-Match CriteriaRemoving CriteriaSpecifying Comparison CriteriaFiltering on Fields That Are Not in the Result SetLimiting the Result Set to Unique EntriesComparing FieldsPerforming Aggregate CalculationsClicking Through the TotalsCycling Through the FunctionsUsing Menu CommandsAggregating Groups of RecordsUsing More Than One Aggregate FieldFiltering on Calculated FieldsCreating 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 From Web CommandSaving the IQY File and Setting Formatting OptionsCopying and Pasting from the Web BrowserUsing the Internet Explorer Export To Microsoft Excel Command
8. Collaborating
24. Collaborating on a Network or by E-Mail
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
Distributing Workbooks and Worksheets by E-Mail
Sending an Entire Workbook as an E-Mail AttachmentSending a Worksheet or Range by E-MailSending a Workbook for Review
Controlling Document Access with Information Rights Management
Installing IRMProtecting a Document with IRMSetting an Expiration DateAllowing Users to PrintAllowing Users to Click the Copy CommandAllowing Programmatic AccessMaking the Current Settings the DefaultAdding New Users and Modifying Permissions SettingsUsing a Protected Document
25. Collaborating Using the Internet
Using a Windows SharePoint Services SiteDownloading and Uploading DocumentsChecking Documents In and OutUsing the Document Management Task PaneContacting Team MembersGetting Document Status InformationAssigning and Monitoring TasksOpening Related DocumentsUsing LinksGetting Information About the Current DocumentCreating 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
Exporting Excel Tables to SharePoint
Using Excel Services
Publishing a Workbook to Excel Services
9. Automating Excel
26. Recording Macros
Configuring Macro Security
Using the Macro Recorder
Recording with Relative ReferencesWhat to Do When the Macro Recorder Does Not Give You What You Expect
Introducing the Visual Basic Editor
Learning the Basics of Visual Basic
Objects, Methods, and PropertiesThe Object BrowserCollections of ObjectsManipulating an Object’s Properties Without Selecting the ObjectNaming Arguments to Methods
Adding Code to or Editing Recorded Macros
Using Subroutines in Macros
Using the Personal Macro Workbook
Going On from Here
27. Creating Custom Functions
Creating a Simple Custom Function
Using Custom Functions
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
28. 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
10. Integrating Excel with Other Applications
29. 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 Shape, Image, or Chart
Selecting a Cell or Object Without Triggering the Hyperlink
Editing or Deleting a Hyperlink
Using Formulas to Create Hyperlinks
30. Linking and Embedding
Embedding vs. Linking
Embedding vs. Static Pasting
Embedding and Linking from the Clipboard
Embedding and Linking with the Object Command
Managing Links
Choosing Automatic or Manual UpdateFixing Broken Links
Linking vs. Hyperlinking
31. Using Excel Data in Word Documents
Using Excel Tables in Word DocumentsPasting an Excel Table from the ClipboardUsing Paste Special to Control the Format of Your TableUsing the Microsoft Office Excel Worksheet Object FormatUsing Formatted Text (RTF) and HTML FormatUsing Unformatted Text and Unformatted Unicode TextUsing Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile)Paste-Linking an Excel Table into WordLinking with HyperlinksUsing the Object Command
Using Excel Charts in Word Documents
Using Excel to Supply Mail-Merge Data to Word
11. Appendixes
A. Menu to Ribbon Command Reference
File Menu
Edit Menu
View Menu
Insert Menu
Format Menu
Tools Menu
Data Menu
Chart Menu
Window Menu
Help Menu
Standard Toolbar
Formatting Toolbar
B. Keyboard Shortcuts
Keyboard Shortcuts by KeyFunction KeysControl and Navigation KeysNumeric KeysSymbols and KeypadAlphabet Keys
Keyboard Shortcuts by Task
Charts and Select Chart ElementsDialog Box Text BoxesDialog BoxesEdit DataEnter and Calculate FormulasEnter DataExtend a SelectionFilter ListsFormat Cells Dialog Box—Border TabFormat DataHelpHelp WindowInsert, Delete, and Copy CellsMacrosRibbon and ButtonsMove and Scroll—In End ModeMove and Scroll—WorksheetsMove Within a Selected RangePrintPrint PreviewSelect Cells, Rows, Columns, and ObjectsSelect Cells with Special CharacteristicsShow, Hide, and Outline DataSmart TagsTask PanesWindows and Office InterfaceWorksheets
C. Function Reference
D. Index to Troubleshooting Topics
E. About the Authors
F. Choose the Right Book for You
Published and Upcoming TitlesPlain & SimpleStep by StepInside OutOther Titles
Index
About the Authors
SPECIAL OFFER: Upgrade this ebook with O’Reilly

Content preview from Microsoft® Office Excel® 2007 Inside Out

Chapter 23. Working with External Data

Using and Reusing Data Connections

Opening an Entire Access Table in Excel

Working with Data in Text Files

Working with XML Files

Using Microsoft Query to Import Data

Using a Web Query to Return Internet Data

Microsoft Office Excel 2007 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 resides somewhere "outside"—on a server, on a Web site, in an XML file, or perhaps in a database program such as Oracle or Microsoft Office Access. Office Excel 2007 supports a wide variety of data formats, including SQL Server (and SQL Server Analysis Services), Access, dBase, FoxPro, Oracle, Paradox, and various kinds of text files. ...

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

Special Edition Using Microsoft® Office Excel® 2007

Publisher Resources

ISBN: 9780735623217Catalog Page Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Office Excel® 2007 Inside Out

by Mark Dodge, Craig Stinson

Chapter 23. Working with External Data

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

Special Edition Using Microsoft® Office Excel® 2007

Master Visually®: Excel® 2007

Microsoft® Excel® 2010 Inside Out

Special Edition Using Microsoft Office 2007

Publisher Resources