book

Excel 2013: The Missing Manual

Name: Excel 2013: The Missing Manual
Author: Matthew MacDonald
ISBN: 9781449359515

by Matthew MacDonald

April 2013

Beginner

1020 pages

31h 19m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Excel 2013: The Missing Manual
The Missing Credits
About the AuthorAbout the Creative TeamAcknowledgmentsThe Missing Manual Series
Introduction
What You Can Do with ExcelThe New Features in Excel 2013The Office 365 Subscription ServiceOffice RT: Office for TabletsThe Office Web AppsAbout This BookAbout the OutlineAbout→These→ArrowsContextual TabsButtons with MenusDialog Box LaunchersBackstage ViewOrdinary MenusAbout Shortcut KeysOnline ResourcesRegistrationFeedbackErrataExamplesSafari© Books Online
1. Worksheet Basics
1. Creating Your First Spreadsheet
Starting a WorkbookAdding Information to a WorksheetAdding Column TitlesAdding DataEditing DataEditing Cells with the Formula BarUsing the RibbonThe Tabs of the RibbonCollapsing the RibbonUsing the Ribbon with the KeyboardThe Quick Access ToolbarUsing the Status BarGoing BackstageSaving FilesThe Excel File FormatSharing Your Spreadsheet with Older Versions of ExcelSaving Your Spreadsheet for Excel 2003Saving Your Spreadsheet As a PDFPassword-Protecting Your SpreadsheetDisaster RecoveryAutoRecover SettingsOpening FilesOpening Files in Other FormatsProtected 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 TimesaversMoving Around the Grid with Shortcut KeysThe Go To FeatureAutoCompleteAutoCorrectAutoFitAutoFillCustom AutoFill ListsFlash FillDealing 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 ColumnsInserting RowsInserting Copied or Cut CellsDeleting Columns and Rows
4. Managing Worksheets
Worksheets and WorkbooksAdding and Removing WorksheetsMoving Between 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
2. 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 Window
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 PermutationsBASE() and DECIMAL(): Converting Numbers to Different BasesROMAN() and ARABIC(): Using Roman NumeralsTrigonometry 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 Need to Pay Off a Loan or Meet an Investment TargetRATE(): Calculating the Interest Rate You Need to Achieve a TargetRRI(): Calculating the Interest Rate Your Investments AchieveNPV() 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 TimesTODAY() 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 LookupsIFNA(): Dealing with Failed 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 ReferencesFORMULATEXT(): Getting the Text of a FormulaOther Reference and Lookup FunctionsINDIRECT() and ADDRESS(): Working with Cell References Stored As TextTRANSPOSE(): Changing Rows to 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 CatalogDealing with Blank ValuesDealing with Incorrect Values
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
3. Organizing Your Information
14. Tables: List Management Made Easy
The Basics of TablesCreating a TableFormatting a TableEditing a TableSelecting Parts of a TableSorting a TableApplying a Simple Sort OrderSorting with Multiple CriteriaSorting by ColorFiltering a TableHiding Specific ValuesFiltering DatesFiltering NumbersFiltering TextApplying Filters with SlicersDealing 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
Using the Office Online TemplatesCreating a New Workbook from an Office Online TemplateMake Your Favorite Templates Stick AroundDownloading Templates from the Office Online WebsiteRolling Your Own TemplatesSetting Up a Personal Template FolderBuilding a Custom Template
4. Charts and Graphics
17. Creating Basic Charts
Charting 101Embedded and Standalone ChartsAdding a Recommended ChartPicking from the Full Range of ChartsSelecting a ChartBasic Tasks with ChartsMoving and Resizing a ChartCreating a Standalone ChartEditing and Adding to Chart DataFiltering 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 TypesColumnBarLinePieAreaScatter (XY)StockSurfaceDonutBubbleRadar
18. Formatting and Perfecting Charts
Chart Styles and LayoutsChart StylesChart ColorsChart LayoutsAdding Chart ElementsAdding TitlesAdding a LegendAdding Data Labels to a SeriesAdding Individual Data LabelsAdding a Data TableFormatting Chart ElementsSelecting a Chart ElementColoring the BackgroundFancy FillsGradient FillsTexture FillsPicture FillsFancy Borders and LinesFormatting Data Series and Data PointsImproving 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 Picture from a FilePositioning and Resizing a PicturePicture Touch-UpRemoving the Background Behind a PictureCropping and Shaping a PicturePicture Borders, Effects, and StylesCompressing PicturesExcel’s Clip Art LibraryDrawing ShapesDrawing a ShapeAdding Text to a ShapeSelecting and Arranging ShapesConnecting ShapesSmartArt
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
5. Sharing Data with the Rest of the World
21. Protecting Your Workbooks
Understanding Excel’s SafeguardsData ValidationSettingsInput MessageError AlertData Validation with Formulas and Cell ReferencesData Validation with ListsLocked and Hidden CellsProtecting a WorksheetProtecting an Entire WorkbookProtecting Cell Ranges (with More Passwords)Allowing Specific Windows Users to Edit a Range
22. Worksheet Collaboration
Your Excel IdentityPreparing Your WorkbookWorkbook ProtectionChecking for IssuesDocument PropertiesDistributing Your WorkbookSending by EmailAdding CommentsInserting a CommentShowing and Hiding CommentsFine-Tuning CommentsReviewing CommentsPrinting CommentsTracking ChangesTurning 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 ActionReviewing Workbooks with InquireTurning on the Inquire Add-InGenerating a Spreadsheet ReportComparing two WorkbooksFollowing the Formula Trail
23. Using Excel on the Web
Putting Your Files OnlineIntroducing SkyDriveUsing SkyDriveYour Web WorkflowUploading a File to SkyDrive Using Your BrowserUploading a Workbook to SkyDrive Using ExcelUsing the Excel Web AppSupported FeaturesUnsupported FeaturesPartially Supported FeaturesSaving FilesTaking a Workbook Back to Desktop ExcelSharing Your FilesInviting Specific PeopleCreating a Sharing LinkSpreading the Word on Social NetworksCollaboration: The Excel Web App’s SpecialtyCreating a Survey
24. Exchanging Data with Other Programs
Sharing Information in WindowsEmbedding and Linking ObjectsEmbedding an Excel Chart in Another ProgramCreating a Linked Chart ObjectImporting Objects into ExcelTransferring DataExporting Tables of DataImporting Tables of DataImporting Text Files
6. Advanced Data Analysis
25. 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
26. 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 FilteringSlicersTimelinesGroup FilteringPivot ChartsCreating a Pivot ChartManipulating a Pivot Chart
27. Analyzing Databases
Excel and DatabasesCreating a Data ConnectionConnecting to an Access DatabaseRefreshing DataData Source SecurityConnecting to a SQL Server DatabaseReusing Your Database ConnectionThe Data Model: Boosting Pivot TablesDealing with Big TablesUnderstanding RelationshipsDefining a RelationshipImporting RelationshipsGoing Behind the Scenes with PowerPivotCreating a Calculated Field with PowerPivot
28. Analyzing XML and Web Data
Understanding XMLWhat Is XML, Really?Three Rules of XMLThe PrologElementsNestingXML Files and SchemasRetrieving Information from XMLMapping a Simple DocumentImporting and Exporting XMLMapping ListsGaining the Benefits of XML MappingCreating Web QueriesThe Limitations of Web QueriesCreating a Web QueryConnecting to Online Data Services with ODataCreating an OData Query
7. Programming Excel
29. 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
30. 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
8. Appendix
A. Customizing the Ribbon
Adding Your Favorites to the QATAdding ButtonsCustomizing Specific WorkbooksPersonalizing the RibbonChanging Existing TabsCreating Your Own TabExporting Your Custom Ribbon
Index
About the Author
Copyright

Content preview from Excel 2013: The Missing Manual

The Missing Credits

About the Author

Matthew MacDonald (author) is a four-time Microsoft MVP and a technology writer with well over a dozen books to his name. Office geeks can follow him into the world of databases with Access 2013: The Missing Manual. Web fans can build an online home with him in Creating a Website: The Missing Manual. And human beings of all description can discover just how strange they really are in the quirky handbooks Your Brain: The Missing Manual and Your Body: The Missing Manual.

About the Creative Team

Peter McKie (editor) graduated from Boston University’s School of Journalism and lives in New York City. In his spare time, he manages the Facebook page and website that chronicle the history of his summer community. Email: pmckie@oreilly.com.

Melanie Yarbrough (production editor) lives and works in Cambridge, MA. When not ushering books through production, she’s sewing, writing, and baking whatever she can think up. Email: myarbrough@oreilly.com.

Julie Hawks (indexer) is an indexer for the Missing Manual series. She is currently pursuing a master’s degree in Religious Studies while discovering the joys of warm winters in the Carolinas. Email: juliehawks@gmail.com.

Carla Spoon (proofreader) is a freelance writer and copy editor. An avid runner, she works and feeds her tech gadget addiction from her home office in the shadow of Mount Rainier. Email: carla_spoon@comcast.net ...

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

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design