book

Microsoft® Excel® 2010 Inside Out

Name: Microsoft® Excel® 2010 Inside Out
Author: Mark Dodge and Craig Stinson
ISBN: 9780735647824

by Mark Dodge and Craig Stinson

August 2010

Intermediate to advanced

1152 pages

46h 8m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Excel® 2010 Inside Out
Dedication
A Note Regarding Supplemental Files
Acknowledgments
Questions and Support
Using the Companion Content
Your Companion eBook
Support Information
We Want to Hear from You
Conventions and Features Used in This Book

Text Conventions
Design Conventions
1. Examining the Excel Environment
1. What’s New in Microsoft Excel 2010
New and Improved for 2010
Backstage ViewRibbon CustomizationSparklinesPaste PreviewImproved Picture EditingOffice Web AppsSlicersImproved Conditional FormattingNew Functions and Functional ConsistencyImproved Math Equation SupportImproved Charting CapacityAdditional SmartArt Graphics64-Bit EditionOffice Mobile 2010
If You Missed the Last Upgrade
Retired in 2007
If You Missed the Last Two Upgrades
Onward
2. Exploring Excel Fundamentals
What Happens After You Install Excel?
Activating ExcelGetting Updates
Examining the Excel 2010 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 PreviewUnderstanding Contextual Tool SetsUsing the Quick Access ToolbarAccessing Commands with the KeyboardThe Joy of Shortcut MenusMeet the Formula BarFacts About the Status BarIntroducing Backstage View
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 ExcelIntroducing Office Web AppsUsing Web File FormatsImporting and Exporting Text FilesOther File Formats
Using the Help System
Help on the SurfaceHelp in Depth
Recovering from Crashes
Understanding AutoRecoverManaging Versions
3. Custom-Tailoring the Excel Workspace
Customizing the Ribbon
Identifying Items in the Customize Ribbon and Quick Access Toolbar CategoriesCreating a Custom Ribbon TabCreating a Custom Command GroupAdding Existing Command Groups to Custom TabsResetting and Recycling Your Customizations
Customizing the Quick Access Toolbar
Positioning the ToolbarAdding Tools to the ToolbarAdding Tools as You WorkRemoving ToolsAdding and Organizing ToolsToo Many Tools?Creating Your Own ButtonsSaving and Resetting Your Custom Quick Access ToolbarRestoring the ToolbarExploring Other Toolbar and Ribbon Options
Controlling Other Elements of the Excel 2010 Interface
Displaying Underlying FormulasHiding Zeros
Enhancing Accessibility
4. Security and Privacy
The Trust Center
Trusted Publishers, Locations, and DocumentsAdd-Ins, ActiveX Settings, and Macro SettingsMessage BarExternal ContentFile Block SettingsPrivacy 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 Regions
Navigating 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 Nonadjacent RangesSelecting 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 Workbooks
Navigating 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 WorksheetsMarking as Final
3. Formatting and Editing Worksheets
8. Worksheet Editing Techniques
Copying, Cutting, and Pasting
Copying and PastingCollecting Multiple Items on the ClipboardPasting MultiplesUsing the Paste Options ButtonCutting 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 Auto FillDragging 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 ActionsLetting 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 ErrorsChecking 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 Fundamentals
Formatting 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 Styles
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 FormatsAligning Data in CellsAligning Text HorizontallyAligning 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 and Formatting Graphics
Using the Shapes Tools
Drawing 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 Other Graphics
Inserting Clip Art and Media FilesInserting PicturesInserting Other Objects
Formatting Graphics
Using Picture ToolsMaking Adjustments to Your ImagesUsing Drawing ToolsUsing SmartArt ToolsFormatting Text in GraphicsApplying Compression to PicturesUsing Advanced Object Formatting EffectsFormatting Fills and LinesApplying ShadowsApplying 3-D EffectsFormatting 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 ObjectsCreating Screenshots or Pictures of Your WorksheetsTaking ScreenshotsCopying Cells as a PicturePasting Cells as a PictureCreating Linked Images of Cells Using the Camera Button
11. Printing and Presenting
Controlling the Appearance of Your Pages
Setting Page OptionsPrinting Wide or TallSpecifying Paper Size and Print QualitySetting a Reduction (Scaling) RatioSetting the First Page NumberWorking in Page Layout ViewSetting MarginsCreating a Header and FooterUsing the Header/Footer TabAdding Pictures to 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
Controlling What and Where to Print
Adjusting Page Breaks
Using Page Break PreviewInserting and Removing Manual Page Breaks
Using Print Preview
Creating Portable Documents
5. Creating Formulas and Performing Data Analysis
12. Building Formulas
Formula Fundamentals
Understanding 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 WorkbooksHow Copying Affects Cell 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 NamesInserting 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 Conditional FunctionsUsing Lookup Functions
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 Functions
Using the SUM FunctionThe Sum ButtonThe SUMIF, SUMIFS, and COUNTIF FunctionsUsing 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 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, NETWORKDAYS, WORKDAY.INTL and NETWORKDAYS.INTL Functions
16. Functions for Financial Analysis
Calculating Investments
The 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 Data
Using Built-In Statistical FunctionsThe AVERAGE FunctionsThe MEDIAN, MODE.SNGL, MODE.MULT, MAX, MIN, and COUNT FunctionsUsing Functions That Analyze Rank and PercentileThe PERCENTRANK FunctionsThe PERCENTILE and QUARTILE FunctionsThe SMALL and LARGE FunctionsThe RANK FunctionsUsing Sample and Population Statistical FunctionsCalculating Sample Statistics: VAR.S and STDEV.SCalculating Total Population Statistics: VAR.P and STDEV.P
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 NumbersDistributing 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 Tables
Data 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 ConstraintsOther Solver OptionsLinear ModelsViewing Iteration ResultsSaving and Reusing the Solver ParametersAssigning the Solver Results to Named ScenariosGenerating 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
Adding Glow and Soft Edges to Chart Markers
Saving Templates to Make Chart Formats Reusable
20. Using Sparklines
Creating Sparklines
Creating Groups of SparklinesExpanding a Set of Sparklines
Customizing Sparklines
Changing the Sparkline Color and WeightEmphasizing Particular PointsCustomizing AxesSetting Minimum and Maximum Values for the Vertical AxisPlotting a Group of Sparklines Against a Common Vertical AxisUsing a Time-Scaled Horizontal AxisAdding Text to Sparklines
Removing Sparklines
21. Advanced Charting Techniques
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 CommandPlotting Noncontiguous 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
22. 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 RelationshipsUsing 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 RowsRemoving 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
23. 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
Filtering with the Report Filter AxisFiltering with SlicersConnecting a Slicer to Multiple PivotTablesFormatting SlicersFiltering in the Field List Window
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
24. Working with External Data
Using and Reusing Data Connections
Setting 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 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 From Web CommandCopying and Pasting from the Web BrowserExporting from Internet Explorer to Excel
8. Collaborating
25. 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, Chart, 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
26. Collaborating Using the Internet
Using Windows Live SkyDrive
Saving Files to SkyDriveManaging SkyDrive Folders
Using Microsoft Office Web Apps
Issues with Web Apps
9. Automating Excel
27. 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
28. 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
29. Debugging Macros and Custom Functions
Using Design-Time Tools
Catching 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 App ication
30. Using Hyperlinks
Creating a Hyperlink in a Cell
Turning 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 an Object Without Triggering the Hyperlink
Editing or Deleting a Hyperlink
Using Formulas to Create Hyperlinks
31. 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
32. Using Excel Data in Word Documents
Using Excel Tables in Word Documents
Pasting an Excel Table from the ClipboardUsing Paste Special to Control the Format of Your TableUsing the Microsoft Excel Worksheet Object FormatUsing RTF and HTML FormatsUsing 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
Excel 2003 File Menu
Excel 2003 Edit Menu
Excel 2003 View Menu
Excel 2003 Insert Menu
Excel 2003 Format Menu
Excel 2003 Tools Menu
Excel 2003 Data Menu
Excel 2003 Chart Menu
Excel 2003 Window Menu
Excel 2003 Help Menu
Excel 2003 Standard Toolbar
Excel 2003 Formatting Toolbar
B. Keyboard Shortcuts
Keyboard Shortcuts by Key
Keyboard Shortcuts by Task
C. Function Reference
Alphabetical List of Excel 2010 Functions
Alphabetical List of Excel 2010 Functions (continued)
Index
About the Authors
Copyright

Content preview from Microsoft® Excel® 2010 Inside Out

Using Multilevel Categories

Excel lets you categorize your categories. This sounds redundant, but a quick example illustrates the concept. Suppose you want to plot the data shown in Figure 21-19. (Figure 21-20 shows the resulting chart.) The series are months, and the categories are the sales offices located in different cities. The city sales are further classified by state, however. To create this multilevel chart, you simply select the data, including both columns of category information, and plot it in the usual manner. Excel recognizes the second category column and creates the appropriate subcategories.

This worksheet uses multilevel categories; city sales offices are grouped by state.

Figure 21-19. This worksheet uses multilevel ...

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: 9780735647824Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Excel® 2010 Inside Out

by Mark Dodge and Craig Stinson

Using Multilevel Categories

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

Microsoft® Excel® 2010 Plain & Simple

Using Microsoft® Excel® 2010

Microsoft® Office Excel® 2007 Inside Out

Excel® 2010 Bible

Publisher Resources