book

Microsoft Excel 2013 Inside Out

Name: Microsoft Excel 2013 Inside Out
Author: Mark Dodge and Craig Stinson
ISBN: 9780735674264

by Mark Dodge and Craig Stinson

April 2013

Intermediate to advanced

1168 pages

31h 45m

English

Microsoft Press

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Introduction
Who this book is forAssumptions about youHow this book is organizedAbout the companion contentAcknowledgmentsSupport and feedbackErrata & supportWe want to hear from youStay in touch
I. Examining the Excel environment
1. What’s new in Microsoft Excel 2013
New and improved for 2013New templatesQuick analysisFlash FillRecommended chartsNew chart-formatting controlsTimelineOther improvementsRetired in 2013If you missed the last upgradeIf you missed the last two upgradesMoving onward with Office 2013
2. Exploring Excel fundamentals
Examining the Excel 2013 workspaceFacts about worksheetsUsing the workbook windowGetting around in the workbookWorkbook navigation tipsWorking with workbook windowsExploring 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 menusUsing the Quick Analysis toolMeet the formula barFacts about the status barIntroducing Backstage viewExploring file-management fundamentalsCreating workbooksInstalling your own templatesSaving 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 filesOpening filesSpecial ways to open filesOpening files when you start ExcelRecovering corrupted filesImporting and exporting filesImporting and exporting filesSharing data with Excel for the MacintoshSharing data beyond ExcelOffice Web AppsUsing web file formatsImporting and exporting text filesOther file formatsUsing the Help systemHelp on the surfaceHelp in depthRecovering from crashesUnderstanding AutoRecoverManaging versions
3. Custom-tailoring the Excel workspace
Customizing the ribbonIdentifying 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 customizationsCustomizing the Quick Access ToolbarPositioning 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 optionsControlling other elements of the Excel 2010 interfaceDisplaying underlying formulasHiding zerosEnhancing accessibilityInstalling apps
4. Security and privacy
The Trust CenterTrusted publishers, locations, and documentsApp catalogs, add-ins, ActiveX settings, and macro settingsMessage barExternal contentFile block settingsPrivacy optionsFile securityRemoving personal information from your workbooksUsing digital signaturesOther security features
II. 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 keysUnderstanding selectionSelecting with the mouseZooming to select large worksheet areasSelecting columns, rows, and nonadjacent rangesSelecting regionsUsing the find and select commandsSelecting with Go To SpecialSelecting precedents and dependentsSelecting row or column differencesTechniques for entering dataMaking 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 entriesManaging worksheetsInserting and deleting worksheetsNaming and renaming worksheetsMoving and copying worksheetsViewing worksheetsSplitting worksheets into panesFreezing panesZooming worksheetsUsing custom viewsProtecting worksheetsUnlocking 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 windowsComparing worksheets side by sideOpening multiple windows for the same workbookUseful inconsistencies of new windowsHiding and protecting workbooksHiding workbooksProtecting workbooksEncrypting workbooksSaving workbooks or windows as hiddenHiding worksheetsMarking as final
III. Formatting and editing worksheets
8. Worksheet editing techniques
Copying, cutting, and pastingCopying 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 mouseInserting and deletingInserting columns and rowsInserting cellsInserting copied or cut cellsDeleting cells, columns, and rowsClearing cellsInserting, deleting, and clearing cells with the mouseDragging with the right mouse buttonUndoing previous actionsRedoing what you’ve undoneRepeating your last actionEditing cell contentsEditing in cells or in the formula barEditing optionsFilling cells 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 listsAutomatic parsing and concatenation using Flash FillControlling automatic formattingFinding and replacing stuffFinding formattingSpecifying variables using wildcard charactersReplacing what you findGetting the words rightFixing errors as you typeTyping Internet and network addressesUsing custom AutoCorrect actionsLetting Excel help with typing choresCheking yer spelingResearch resourcesEditing multiple worksheetsGrouping worksheets for editingWhat you can do in group-editing modeFilling a groupAuditing and documenting worksheetsChecking for errorsEvaluating and auditing formulasWatching formulasTracing cell referencesTracing dependent cellsClearing tracer arrowsTracing precedent cellsTracing errorsTracing references to other worksheetsAdding comments to cellsTweaking your commentsPrinting commentsOutlining worksheetsOutlining 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 rowsConsolidating worksheetsConsolidating 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 formatsUsing themes and cell stylesFormatting 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 styleFormatting conditionallyCreating conditional formatting rulesManaging conditional formatting rulesCopying, clearing, and finding conditional formatsCreating conditional formatting formulasFormatting in depthFormatting 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 cellsUsing template files to store formatting
IV. Adding graphics and printing
10. Creating and formatting 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 calloutsCreating WordArtCreating SmartArtInserting other graphicsInserting online pictures and clip artInserting picturesInserting other objectsFormatting graphicsUsing 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 objectsWorking with graphic objectsSelecting and grouping objectsPositioning objectsTools to help you position objects on the worksheetProtecting objectsMore tricks with graphic objectsAssigning 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 pagesSetting 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 controlsAdding 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 rangesControlling what and where to printAdjusting page breaksUsing Page Break PreviewInserting and removing manual page breaksUsing Print PreviewCreating portable documents
V. 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 workbooksHow copying affects cell referencesCopying relative referencesCopying absolute referencesCopying mixed referencesEditing formulasUnderstanding reference syntaxUsing numeric text in formulasAbout text valuesUnderstanding error valuesUsing functions: A previewUsing the Sum buttonInserting a functionUsing Formula AutoCompleteWorking with formulasNaming 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 referencesWorksheet calculationRecalculating manuallyCalculating part of a formulaWorking with circular referencesUnderstanding the precision of numeric valuesUsing arraysOne-dimensional arraysArray formula rulesTwo-dimensional arraysSingle-cell array formulasUsing array constantsUnderstanding array expansionLinking workbooksSaving linked workbooksOpening a dependent workbookEditing linksCopying, cutting, and pasting in linked workbooksCopying and pasting between workbooksCutting and pasting between workbooksCreating conditional testsUsing conditional functionsUsing lookup functions
13. Using functions
Using the built-in function reference in ExcelExploring the syntax of functionsExpressions as argumentsTypes of argumentsNumeric valuesText valuesLogical valuesNamed referencesArraysMixed argument typesInserting functionsInserting references and names
14. Everyday functions
Understanding mathematical functionsUsing the SUM functionThe Sum buttonThe SUMIF, SUMIFS, and COUNTIF functionsUsing selected mathematical functionsThe AGGREGATE functionThe 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 functionUnderstanding text functionsUsing 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 functionUnderstanding logical functionsUsing selected logical functionsThe IF functionThe AND, OR, and NOT functionsNested IF functionsOther uses for conditional functionsUnderstanding information functionsUsing selected information functionsThe TYPE and ERROR.TYPE functionsThe COUNTBLANK functionUsing the IS information functionsUnderstanding lookup and reference functionsUsing 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 timesEntering dates and timesEntering a series of datesExtending an existing date seriesFormatting dates and timesCreating your own date and time formatsMeasuring elapsed timeCalculating with date and timeWorking with date and time 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 investmentsThe PV functionThe NPV functionThe FV functionThe PMT functionThe IPMT functionThe PPMT functionThe NPER functionThe RATE functionThe IRR functionThe MIRR functionCalculating depreciationThe SLN functionThe DDB and DB functionsThe VDB functionThe SYD functionAnalyzing securitiesThe 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 functionsUsing the Euro Currency Tools add-in
17. Functions for analyzing statistics
Analyzing distributions of dataUsing 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.PUnderstanding linear and exponential regressionCalculating linear regressionThe LINEST functionThe TREND functionThe FORECAST functionThe SLOPE functionThe STEYX functionCalculating exponential regressionThe LOGEST functionThe GROWTH functionUsing the Analysis Toolpak data analysis toolsInstalling 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 a 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 tablesUsing the Scenario ManagerDefining scenariosBrowsing your scenariosAdding, editing, and deleting scenariosTracking changesRouting and merging scenariosCreating scenario reportsThe Scenario Summary reportThe Scenario PivotTable reportUsing the Goal Seek commandPrecision and multiple solutionsUsing the SolverStating 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
VI. Creating charts
19. Designing charts
Selecting data for your chartCreating a new chartChanging the chart typeUsing a combo chart typeAssigning a series to a secondary axisSwitching rows and columnsChoosing a chart layoutChoosing a chart styleMoving the chart to a separate chart sheetAdding, editing, and removing a chart titleAdding, editing, and removing a legendAdding and positioning data labelsAdding a data tableAdding and removing gridlinesAdding and editing axis titlesWorking with axesChanging 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 display format of a value axisChanging the scale of a category axisChanging the beginning and end points on a date axisChanging the base unitModifying a chart’s dataUsing the mouse to add data pointsFiltering chart dataPlotting noncontiguous source rangesChanging the way Excel plots empty and hidden cellsAdding moving averages and other trendlinesAdding error bars
20. Formatting charts
Formatting charts with the Chart Styles gallerySelecting chart elementsRepositioning chart elements with the mouseFormatting lines and bordersFormatting areasChoosing a solid fillFilling an area with a color gradientFilling an area with a texture or pictureMaking areas transparentFormatting textUsing WordArtChanging the rotation of axis labelsChanging the interval between axis labelsChanging the position of axis labelsChanging the numeric format used by axis labelsAdding arrows, shapes, and text
21. Using sparklines
Creating sparklinesCreating groups of sparklinesExpanding a set of sparklinesCustomizing sparklinesChanging 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 sparklinesRemoving sparklines
VII. Managing databases and tables
22. Managing information in tables
How to organize a tableCreating a tableOverwriting default headersTurning a table back into an ordinary rangeNaming a tableExpanding a tableAdding totals to a tableSorting tables and other rangesSorting 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 sortFiltering a list or tableUsing 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 recordsUsing formulas with tablesReferencing the total rowExplicitly referencing the current rowReferencing parts of a tableFormatting tablesUsing themes to change style appearanceCustomizing table styles
23. Analyzing data with PivotTable reports
Introducing PivotTablesCreating a PivotTableRearranging PivotTable fieldsRefreshing a PivotTableChanging the numeric format of PivotTable dataChoosing report layout optionsFormatting a PivotTableCustomizing the display of empty or error cellsMerging and centering field labelsHiding outline controlsHiding the Row Labels and Column Labels headingsDisplaying totals and subtotalsCustomizing subtotalsSorting PivotTable fieldsFiltering PivotTable fieldsFiltering with the Report Filter axisFiltering with slicersConnecting a slicer to multiple PivotTablesFormatting slicersFiltering in the PivotTable Fields paneChanging PivotTable calculationsUsing 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 itemsGrouping and ungrouping dataCreating ad hoc item groupingsGrouping items in date or time rangesDisplaying the details behind a data valueCreating PivotChartsMoving beyond PivotTables
24. An introduction to PowerPivot
Using a PivotTable on an Excel tableUsing PowerPivot in Office 2013Adding information to the Excel tableCreating a data model with many tablesUnderstanding relationshipsUnderstanding the data modelQuerying the data modelThe PowerPivot add-inCreating a Power View reportLoading data from external sourcesUsing the DAX languageCreating a calculated columnCreating a calculated fieldRefreshing the PowerPivot data model
25. Working with external data
Using and reusing Office Data ConnectionsSetting refresh optionsRequiring or not requiring a password to refreshRefreshing on demandOpening an entire Access table in ExcelWorking with data in text filesUsing the Text Import WizardParsing Clipboard textWorking with XML filesCreating an ad hoc mapping of XML elements to table columnsImporting XML data using an existing XML structureUsing Microsoft Query to import dataChoosing 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 setCreating exact-match criteriaUsing multiple exact-match criteriaUsing menu commands to specify exact-match criteriaFiltering on fields that are not in the result setLimiting the result set to unique entriesComparing fieldsPerforming aggregate calculationsCycling 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 ExcelUsing a web query to return Internet dataUsing an existing web queryCreating your own web queryUsing the From Web commandCopying and pasting from the web browserExporting from Internet Explorer to Excel
VIII. Using Excel collaboratively
26. Collaborating on a network or by email
Saving and retrieving files over a networkSharing workbooks on a networkUsing advanced sharing optionsTracking changesProtecting the change historyReviewing changesCanceling the shared workbook sessionCombining changes made to multiple workbooksDistributing workbooks and worksheets by emailSending an entire workbook as an email attachmentSending a worksheet, chart, or range by emailSending a workbook for review
27. Working in the cloud
Using SkyDriveWorking with the SkyDrive applicationUsing the SkyDrive notification iconSpecifying which folders to synchronizeSaving workbooks to and retrieving them from SkyDriveWorking with your cloud-based SkyDriveUploading, downloading, and managing files and foldersOpening documentsSharing folders and documentsOpening recent documents in SkyDriveSeeing and accessing items others have shared with youFetching files from remote computersUsing Microsoft Office Web AppsHow the Web App handles unsupported features
IX. Automating Excel
28. Recording macros
Configuring macro securityUsing the macro recorderRecording with relative referencesWhat to do when the macro recorder does not give you what you expectIntroducing the Visual Basic EditorLearning the basics of Visual BasicObjects, methods, and propertiesThe Object BrowserCollections of objectsManipulating an object’s properties without selecting the objectNaming arguments to methodsAdding code to or editing recorded macrosUsing subroutines in macrosUsing the Personal Macro WorkbookGoing on from here
29. Creating custom functions
Creating a simple custom functionUsing custom functionsUnderstanding custom function rulesUsing VBA keywords in custom functionsDocumenting macros and custom functionsCreating custom functions with optional argumentsMaking your custom functions available anywhere
30. 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 windowDealing with run-time errors
X. Integrating and extending Excel
31. Linking, hyperlinking, and embedding
Linking and embedding with OLE technologyEmbedding vs. linkingEmbedding vs. static pastingEmbedding and linking from the ClipboardEmbedding and linking with the Object commandManaging linksChoosing automatic or manual updateFixing broken linksLinking with hyperlinksCreating a hyperlink in a cellTurning ordinary text into a hyperlinkLinking to a website or local fileLinking to a location in the current documentLinking to a new fileLinking to an email messageAssigning a hyperlink to a shape, image, or chartSelecting a cell or an object without triggering the hyperlinkEditing or deleting a hyperlinkUsing formulas to create hyperlinks
32. 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 Excel Worksheet Object formatUsing RTF and HTML formatsUsing Unformatted Text and Unformatted Unicode TextUsing Bitmap and Picture (Enhanced Metafile)Paste-linking an Excel table into WordLinking with hyperlinksUsing the Object commandUsing Excel charts in Word documentsUsing Excel to supply mail-merge data to Word
XI. Appendixes
A. Menu-to-ribbon command reference
Excel 2003 File menuExcel 2003 Edit menuExcel 2003 View menuExcel 2003 Insert menuExcel 2003 Format menuExcel 2003 Tools menuExcel 2003 Data menuExcel 2003 Chart menuExcel 2003 Window menuExcel 2003 Help menuExcel 2003 Standard toolbarExcel 2003 Formatting toolbar
B. Keyboard shortcuts
Shortcuts by key: Function keysShortcuts by key: Control and navigation keysShortcuts by key: Numeric keysShortcuts by key: Symbol keys and keypadShortcuts by key: Letter keysShortcuts by task: Insert chartsShortcuts by task: Work in dialog box text boxesShortcuts by task: Work in dialog boxesShortcuts by task: Edit dataShortcuts by task: Work with formulasShortcuts by task: Enter dataShortcuts by task: Select cellsShortcuts by task: Filter tablesShortcuts by task: Work with bordersShortcuts by task: Format dataShortcuts by task: Work with HelpShortcuts by task: Insert, delete, and copy cellsShortcuts by task: Work with macrosShortcuts by task: Work with the ribbonShortcuts by task: Move and scroll in End modeShortcuts by task: Move and scroll in worksheetsShortcuts by task: Move within a selected rangeShortcuts by task: PrintShortcuts by task: Work in Backstage viewShortcuts by task: Select cells, rows, columns, and objectsShortcuts by task: Select cells with special characteristicsShortcuts by task: Work with outlinesShortcuts by task: Work with options menusShortcuts by task: Work with task panesShortcuts by task: Navigate WindowsShortcuts by task: Work with worksheets
C. Function reference
Alphabetical list of Excel 2013 functionsAlphabetical list of Excel 2013 functions (continued)
D. Index to troubleshooting topics
E. About the authors
Index
About the Authors
Copyright

Overview

You’re beyond the basics—so dive in and really put your spreadsheet skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Learn how the experts tackle Excel 2013—and challenge yourself to new levels of mastery. Includes companion eBook and sample files.

Topics include:

Customizing the Excel workspace

Best practices for designing and managing worksheets

Creating formulas and functions

Performing statistical, what-if, and other data analysis

Core to advanced charting techniques

Using graphics and sparklines

Managing databases and tables

Automating Excel with macros and custom functions

Collaborating in Excel online, in the cloud, and more

Extending Excel

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

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills