book

Excel 2010: The Missing Manual

Name: Excel 2010: The Missing Manual
Author: Matthew MacDonald
ISBN: 9781449394196

by Matthew MacDonald

June 2010

Beginner

898 pages

32h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Excel 2010: The Missing Manual
A Note Regarding Supplemental Files
The Missing Credits
About the AuthorAbout the Creative TeamAcknowledgementsThe Missing Manual Series
Introduction
What You Can Do with ExcelThe Modern Face of ExcelThe RibbonUsing the Ribbon with the KeyboardBackstage ViewThe Quick Access ToolbarExcel’s New FeaturesAbout This BookAbout the OutlineAbout→These→ArrowsContextual tabsButtons with menusDialog box launchersBackstage viewOrdinary menusAbout Shortcut KeysExamplesAbout MissingManuals.comSafari® Books Online
One. Worksheet Basics
1. Creating Your First Spreadsheet
Creating a Basic WorksheetStarting a New WorkbookAdding the Column TitlesAdding DataEditing DataMoving Around the GridShortcut KeysThe Go To FeatureA Tour of the Excel WindowThe Tabs of the RibbonThe Formula BarThe Status BarGoing BackstageExcel OptionsSaving FilesThe Excel File FormatSharing Your Spreadsheet with Excel 2007Saving Your Spreadsheet for Excel 2003Saving a Spreadsheet in Other FormatsSaving Your Spreadsheet As a PDFSaving Your Spreadsheet As an HTML FileSaving Your Spreadsheet with a PasswordDisaster RecoveryAutoRecover settingsOpening FilesOpening Recent DocumentsProtected ViewOpening Files—with a TwistWorking with Multiple Open Spreadsheets
2. Adding Information to Worksheets
Adding Different Types of DataHow Excel Identifies TextHow Excel Identifies NumbersHow Excel Identifies Dates and TimesRegional DatingHandy TimesaversAutoCompleteAutoCorrectAutoFillCustom AutoFill listsAutoFitDealing with Change: Undo, Redo, and AutoRecoverUndo and RedoAutoRecoverSpell CheckSpell Checking OptionsAdding HyperlinksCreating a Link to a Web Page or DocumentCreating a Link to a Worksheet Location
3. Moving Data
Selecting CellsMaking Continuous Range SelectionsMaking Noncontiguous SelectionsAutomatically Selecting Your DataMaking Selections with the KeyboardMoving Cells AroundA Simple Cut-and-Paste or Copy-and-PasteA Quicker Cut-and-Paste or Copy-and-PasteFancy Pasting TricksPaste SpecialCopying Multiple Items with the ClipboardAdding and Moving Columns or RowsInserting Columns and RowsInserting RowsInserting Copied or Cut CellsDeleting Columns and Rows
4. Managing Worksheets
Worksheets and WorkbooksAdding and Removing WorksheetsHiding WorksheetsNaming and Rearranging WorksheetsMoving Worksheets from One Workbook to AnotherGrouping WorksheetsManaging grouped sheetsFormatting grouped sheetsEntering data in grouped sheetsFind and ReplaceThe Basic FindFind AllMore Advanced SearchesFinding Formatted CellsFinding and Replacing Values
5. Formatting Cells
Formatting Cell ValuesChanging the Cell Value FormatFormatting NumbersGeneralNumberCurrencyAccountingPercentageFractionScientificTextFormatting Dates and TimesSpecial Formats for Special NumbersCustom FormatsCreating a custom formatCustom format string codesDate and time format stringsNumber format stringsText format stringsFormatting Cell AppearanceAlignment and OrientationFonts and ColorSpecial charactersBorders and FillsDrawing Borders by Hand

6. Smart Formatting Tricks
The Format PainterStyles and ThemesCustom StylesModifying StylesTransferring Styles Between WorkbooksThemes: A Package of StylesFontsColorsEffectsModifying ThemesConditional FormattingThe Basics of Conditional FormattingHighlighting Specific ValuesUsing Multiple Rules
7. Viewing and Printing Worksheets
Controlling Your ViewZoomingViewing Distant Parts of a Spreadsheet at OnceFreezing Columns or RowsHiding DataSaving View SettingsViewing Multiple Workbooks at OncePrintingHow to Print an Excel FilePage Layout View: A Better Print PreviewCreating Headers and FootersControlling PaginationPage BreaksScalingPage Break Preview: A Bird’s-Eye View of Your Worksheet
Two. Formulas and Functions
8. Building Basic Formulas
Creating a Basic FormulaExcel’s Order of OperationsCell ReferencesHow Excel Formats Cells That Contain Cell ReferencesFunctionsUsing a Function in a FormulaUsing Cell References with a FunctionUsing Cell Ranges with a FunctionExcel Compatibility FunctionsFormula ErrorsLogical OperatorsFormula ShortcutsPoint-and-Click Formula CreationPoint-and-Click Formula EditingThe Formulas TabUsing the Insert Function ButtonCopying FormulasAbsolute Cell ReferencesPartially Fixed ReferencesReferring to Other WorksheetsReferring to Other WorkbooksUpdating linksTroubleshooting linksThe Edit Links dialog box
9. Math and Statistical Functions
Rounding NumbersROUND(), ROUNDDOWN(), ROUNDUP(): Rounding NumbersMROUND(), CEILING(), and FLOOR(): More Rounding FunctionsINT() and TRUNC(): Chopping Off Non-Whole NumbersEVEN() and ODD(): Rounding Up to Even or Odd ValuesGroups of NumbersSUM(): Summing Up NumbersCOUNT(), COUNTA(), and COUNTBLANK(): Counting Items in a ListMAX() and MIN(): Finding Maximum and Minimum ValuesLARGE(), SMALL(), and RANK(): Ranking NumbersAVERAGE() and MEDIAN(): Finding Average or Median ValuesPERCENTILE() and PERCENTRANK(): Advanced Ranking FunctionsFREQUENCY(): Putting Numbers into Grouped RangesMODE(): Finding Numbers That Frequently Occur in a ListGeneral Math FunctionsPRODUCT(), FACT(), POWER(), and SQRT(): Products, Factorials,Powers, and Square RootsQUOTIENT() and MOD(): Higher DivisionABS() and SIGN(): Absolute Value and Determining a Number’s SignRAND() and RANDBETWEEN(): Generating Random NumbersGCD() and LCM(): Greatest and Least Common DenominatorCOMBIN() and PERMUT(): Figuring Combinations and PermutationsTrigonometry and Advanced MathAdvanced Statistics
10. Financial Functions
The World of FinanceFinancial FunctionsFV(): Future ValuePV(): Present ValuePMT(), PPMT(), and IPMT(): Calculating the Number of Payments You Need to MakeNPER(): Figuring Out How Much Time You’ll Need to Pay Offa Loan or Meet an Investment TargetRATE(): Figuring the Interest Rate You Need to Achieve Future ValueNPV() and IRR(): Net Present Value and Internal Rate of ReturnDepreciationOther Financial Functions
11. Manipulating Dates, Times, and Text
Manipulating TextCONCATENATE(): Joining Strings of Text TogetherLEFT(), MID(), and RIGHT(): Copying Portions of a Text StringLEN(), FIND(), and SEARCH(): Counting Characters in a StringUPPER(), LOWER(), and PROPER(): Changing CapitalizationTRIM() and CLEAN(): Removing Unwanted Spaces and Non-Printing CharactersSUBSTITUTE(): Replacing One Sequence of Characters with AnotherTEXT(), VALUE(), FIXED(), and DOLLAR(): Converting Text to Numbers and Vice VersaOther Text FunctionsManipulating Dates and TimesMath with Dates and TimesUsing Dates and Times with Ordinary FunctionsDate and Time FunctionsTODAY() and NOW(): Inserting the Current Date and TimeDATE() and TIME(): Dates and Times in CalculationsDAY(), MONTH(), and YEAR(): More Date CalculationsHOUR(), MINUTE(), SECOND(): More Time CalculationsWEEKDAY(): Determining the Day of the WeekDATEDIF(): Calculating the Difference Between DatesDATEVALUE() and TIMEVALUE(): Converting Dates and Times into Serial NumbersDAYS360(): Finding Out the Number of Days Between Two DatesEDATE(): Calculating Future DatesYEARFRAC(): Calculating the Percentage of a Year Between Two DatesEOMONTH(): Finding the Last Day of Any MonthNETWORKDAYS(): Counting the Number of Business DaysWORKDAY(): Figuring Out When Days Will Fall in the FutureWEEKNUM(): Figuring Out in Which Week a Date Falls
12. Lookup, Reference, and Information Functions
The Basic LookupVLOOKUP(): Vertical LookupsHLOOKUP(): Horizontal LookupsAdvanced LookupsMATCH(): Finding the Position of Items in a RangeINDEX(): Retrieving the Value from a CellPerforming a “Left Lookup”Performing a Double LookupOFFSET(): Shifting Cell ReferencesOther Reference and Lookup FunctionsINDIRECT() and ADDRESS(): Working with Cell References Stored As TextTRANSPOSE(): Changing Rows into Columns and Vice VersaThe HYPERLINK() Function: Creating a Dynamic LinkInformation FunctionsThe “IS” Functions: Checking the Value Inside a CellTYPE() and ERROR.TYPE(): Finding a Value’s Data Type or Error TypeINFO() and CELL(): Gathering Info About Your Computer and Your Worksheet’s CellsTutorial: Generating Invoices from a Product Catalog
13. Advanced Formula Writing and Troubleshooting
Conditions in FormulasIF(): Building Conditional FormulasCOUNTIF(): Counting Only the Cells You SpecifySUMIF(): Adding Only the Cells You SpecifyCOUNTIFS() and SUMIFS(): Counting and Summing Using Multiple CriteriaDescriptive Names for Cell ReferencesCreating and Using a Named RangeCreating Slightly Smarter Named RangesNaming Formulas and ConstantsManaging Named RangesAutomatically Creating Named RangesApplying Names to Existing FormulasVariable Data TablesCreating a One-Variable Data TableCreating a Two-Variable Data TableControlling RecalculationSolving Formula ErrorsStep-by-Step EvaluationTracing Precedents and DependentsError Checking
Three. Organizing Worksheets
14. Tables: List Management Made Easy
The Basics of TablesCreating a TableFormatting a TableEditing a TableSelecting Parts of a TableSorting and Filtering a TableApplying a Simple Sort OrderSorting with Multiple CriteriaSorting by ColorFiltering with the List of ValuesCreating Smarter FiltersFiltering datesFiltering numbersFiltering textDealing with Duplicate RowsHighlighting DuplicatesRemoving Duplicates AutomaticallyPerforming Table CalculationsDynamic CalculationsColumn NamesTable NamesThe Total RowThe SUBTOTAL() FunctionThe Database Functions
15. Grouping and Outlining Data
Basic Data GroupingCreating a GroupNesting Groups Within GroupsSummarizing Your DataCombining Data from Multiple TablesGrouping TimesaversAuto OutlineAutomatic Subtotaling
16. Templates
Understanding TemplatesCreating a New Workbook from a TemplateDownloading Templates (Method 1: From Backstage View)Downloading Templates (Method 2: The Office Online Website)Creating TemplatesUnderstanding Custom TemplatesBuilding a Custom TemplateSharing Templates with Others
Four. Charts and Graphics
17. Creating Basic Charts
Charting 101Embedded and Standalone ChartsCreating a Chart with the RibbonThe Chart Tools Ribbon TabsBasic Tasks with ChartsMoving and Resizing a ChartCreating a Standalone ChartEditing and Adding to Chart DataChanging the Chart TypePrinting ChartsEmbedded chartsStandalone chartsPractical ChartingCharts with Multiple Series of NumbersControlling the Data Excel Plots on the X-AxisData That Uses a Date or Time ScaleNoncontiguous Chart RangesChanging the Order of Your Data SeriesChanging the Way Excel Plots Blank ValuesChart TypesColumnBarLinePieAreaXY (Scatter)StockSurfaceDonutBubbleRadar
18. Formatting and Perfecting Charts
Chart Styles and LayoutsChart StylesChart LayoutsAdding Chart ElementsAdding TitlesAdding a LegendAdding Data Labels to a SeriesAdding Individual Data LabelsAdding a Data TableSelecting Chart ElementsFormatting Chart ElementsColoring the BackgroundFancy FillsGradient fillsTexture fillsPicture fillsFancy Borders and LinesFormatting Data Series and Data PointsReusing Your Favorite Charts with TemplatesImproving Your ChartsControlling a Chart’s ScaleAdding a TrendlineAdding Error Bars to Scientific DataFormatting 3-D ChartsChanging the Shape of a 3-D ColumnAdvanced ChartingExploding Slices in a PieGrouping Slices in a PieGaps, Widths, and Overlays in a Column ChartCreating Combination Charts
19. Inserting Graphics
Adding Pictures to a WorksheetInserting a PicturePositioning and Resizing a PicturePicture Touch-UpCompressing PicturesCropping and Shaping a PicturePicture Borders, Effects, and StylesExcel’s Clip Art LibraryDrawing ShapesDrawing a ShapeAdding Text to a ShapeSelecting and Arranging ShapesConnecting ShapesSmartArt
Five. Advanced Data Analysis
20. Visualizing Your Data
Data BarsEditing a Formatting RuleFine-Tuning Data BarsPutting data bars in a separate columnChanging the data bar scaleColor ScalesFine-Tuning Color ScalesIcon SetsFine-Tuning Icon SetsGiving your icons added significanceHiding icons for some valuesSparklinesCreating a SparklineChanging the AxisMarkers
21. Scenarios and Goal Seeking
Using ScenariosCreating a New ScenarioManaging ScenariosCreating a Summary ReportUsing Goal SeekGoal Seeking with Complex EquationsSolverUnderstanding SolverDefining a Problem in SolverMore Advanced Solver ProblemsSaving Solver ModelsConfiguring Solver
22. Pivot Tables
Summary Tables RevisitedLife Without Pivot TablesLife with Pivot TablesBuilding Pivot TablesPreparing a Pivot TablePivot Table RegionsLaying Out a Pivot TableFormatting a Pivot TableRearranging a Pivot TableGetting to the SourceMulti-Layered Pivot TablesHiding and Showing DetailsFine-Tuning Pivot Table CalculationsChanging the Type of CalculationAdding a Calculated FieldFiltering a Pivot TableReport FilteringSlicersGroup FilteringPivot ChartsCreating a Pivot ChartManipulating a Pivot Chart
23. Analyzing Databases, XML, and Web Pages
Excel and DatabasesConnecting to an Access DatabaseRefreshing DataData Source SecurityConnecting to a SQL Server DatabaseReusing Your Database ConnectionUnderstanding XMLWhat Is XML, Really?Three Rules of XMLThe prologElementsNestingXML Files and SchemasExcel and XMLMapping a Simple DocumentImporting and Exporting XMLMapping ListsGaining the Benefits of XML MappingWeb QueriesThe Limitations of Web QueriesCreating a Web QueryThe Research Pane—A Web Query Alternative
Six. Sharing Data with the Rest of the World
24. Protecting Your Workbooks
Understanding Excel’s SafeguardsData ValidationSettingsInput MessageError AlertData Validation with Formulas and Cell ReferencesData Validation with ListsLocked and Hidden CellsProtecting a WorksheetProtecting the Entire WorkbookProtecting Cell Ranges (with More Passwords)Allowing Specific Windows Users to Edit a Range
25. Worksheet Collaboration
Your Excel IdentityPreparing Your WorkbookWorkbook ProtectionChecking for IssuesDocument PropertiesDistributing a DocumentSending by EmailUploading to the WebAdding CommentsInserting a CommentShowing and Hiding CommentsFine-Tuning CommentsReviewing CommentsPrinting CommentsTracking ChangesSwitching On Change TrackingUnderstanding the Change LogHighlighting ChangesExamining the Change LogAccepting and Rejecting ChangesMerging Multiple Revisions into One WorkbookSharing Your WorkbookMultiple Users Without Workbook SharingTurning On Workbook SharingWorkbook Sharing in Action
26. Using Excel on the Web
Putting Your Files OnlineIntroducing SkyDriveYour Web WorkflowUploading a File to SkyDrive (Using Your Browser)Uploading a Workbook to SkyDrive (Using Excel)Uploading a Workbook to a SharePoint ServerViewing a File in Your SkyDrive AccountViewing a File in Someone Else’s SkyDrive AccountUsing the Excel Web AppSupported FeaturesSaving FilesCollaboration: The Excel Web App’s SpecialtyTaking a Workbook Back to Desktop ExcelUnsupported FeaturesPartially Supported Features
27. Exchanging Data with Other Programs
Sharing Information in WindowsEmbedding and Linking ObjectsExporting Charts Out of ExcelEditing a Linked ObjectEditing an Embedded ObjectImporting Objects into ExcelTransferring DataExporting Tables of DataImporting Tables of DataImporting Text Files
Seven. Programming Excel
28. Automating Tasks with Macros
Macros 101Macro-Free and Macro-Enabled WorkbooksThe Macro RecorderRelative and Absolute RecordingWhere Macros LiveRecording a MacroPlaying a MacroMacro SecurityTrusted DocumentsTemporary TrustThe Trust CenterSetting Up a Trusted LocationCreating Practical MacrosInserting a HeaderAlternating Row FormattingA Combined TaskPlacing a Macro on the Quick Access ToolbarAttaching a Macro to a Button Inside a Worksheet
29. Programming Spreadsheets with VBA
The Visual Basic EditorThe Project WindowModules and MacrosFinding and Moving MacrosDebugging a MacroUnderstanding Macro CodeThe Anatomy of a MacroObjects 101Using Properties and MethodsHunting for ObjectsExploring the VBA LanguageEntering Text in the Current CellInteracting with Other CellsEditing Specific CellsFormatting CellsUsing VariablesMaking DecisionsRepeating Actions with a LoopCreating Custom Functions
Eight. Appendix
A. Customizing the Ribbon
Adding Your Favorites to the QATAdding ButtonsCustomizing Specific WorkbooksPersonalizing the RibbonChanging Existing TabsCreating Your Own TabSaving and Reusing Your Custom Ribbon
Index
About the Author
Copyright

Overview

Excel, the world's most popular spreadsheet program, has the muscle to analyze heaps of data. Beyond basic number-crunching, Excel 2010 has many impressive features that are hard to find, much less master -- especially from online help pages. This Missing Manual clearly explains how everything works with a unique and witty style to help you learn quickly.

Navigate with ease. Master Excel's tabbed toolbar and its new backstage view
Perform a variety of calculations. Write formulas for rounding numbers, calculating mortgage payments, and more
Organize your data. Search, sort, and filter huge amounts of information
Illustrate trends. Bring your data to life with charts and graphics -- including miniature charts called Sparklines
Examine your data. Summarize information and find hidden patterns with pivot tables and slicers
Share your spreadsheets. Use the Excel Web App to collaborate with colleagues online
Rescue lost data. Restore old versions of data and find spreadsheets you forgot to save

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: 9781449382360Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills