- Dedication
- A Note Regarding Supplemental Files
- Introduction
- I. Examining the Excel environment
- 1. What’s new in Microsoft Excel 2013
- 2. Exploring Excel fundamentals
- Examining the Excel 2013 workspace
- Exploring file-management fundamentals
- Importing and exporting files
- Using the Help system
- Recovering from crashes
- 3. Custom-tailoring the Excel workspace
- 4. Security and privacy
- II. Building worksheets
- 5. Planning your worksheet design
- 6. How to work a worksheet
- 7. How to work a workbook
- III. Formatting and editing worksheets
- 8. Worksheet editing techniques
- Copying, cutting, and pasting
- Inserting and deleting
- Undoing previous actions
- Editing cell contents
- Finding and replacing stuff
- Getting the words right
- Editing multiple worksheets
- Auditing and documenting worksheets
- Outlining worksheets
- Consolidating worksheets
- 9. Worksheet formatting techniques
- Formatting fundamentals
- Using themes and cell styles
- Formatting conditionally
- Formatting in depth
- Formatting individual characters
- Formatting as you type
- Understanding the General format
- Formatting numbers
- Aligning data in cells
- Aligning text vertically
- Using fonts
- Customizing borders
- Applying colors and patterns
- Controlling the size of cells
- Using template files to store formatting
- 8. Worksheet editing techniques
- IV. Adding graphics and printing
- 10. Creating and formatting graphics
- Using the Shapes tools
- Creating WordArt
- Creating SmartArt
- Inserting other graphics
- Formatting graphics
- Working with graphic objects
- More tricks with graphic objects
- 11. Printing and presenting
- 10. Creating and formatting graphics
- V. Creating formulas and performing data analysis
- 12. Building formulas
- Formula fundamentals
- Understanding the precedence of operators
- Using cell references in formulas
- Editing formulas
- Understanding error values
- Using functions: A preview
- Working with formulas
- Naming cells and cell ranges
- Using names in formulas
- Defining and managing names
- Editing names
- Workbook-wide vs. worksheet-only names
- Creating names semiautomatically
- Naming constants and formulas
- Using relative references in named formulas
- Creating three-dimensional names
- Inserting names in formulas
- Creating a list of names
- Replacing references with names
- Using Go To with names
- Getting explicit about intersections
- Creating three-dimensional formulas
- Formula-bar formatting
- Using structured references
- Naming cells and cell ranges
- Worksheet calculation
- Using arrays
- Linking workbooks
- Creating conditional tests
- Formula fundamentals
- 13. Using functions
- 14. Everyday functions
- Understanding mathematical functions
- Understanding text functions
- Understanding logical functions
- Understanding information functions
- Understanding lookup and reference functions
- 15. Formatting and calculating date and time
- 16. Functions for financial analysis
- Calculating investments
- Calculating depreciation
- Analyzing securities
- The DOLLARDE and DOLLARFR functions
- The ACCRINT and ACCRINTM functions
- The INTRATE and RECEIVED functions
- The PRICE, PRICEDISC, and PRICEMAT functions
- The DISC function
- The YIELD, YIELDDISC, and YIELDMAT functions
- The TBILLEQ, TBILLPRICE, and TBILLYIELD functions
- The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD functions
- The DURATION and MDURATION functions
- Using the Euro Currency Tools add-in
- 17. Functions for analyzing statistics
- Analyzing distributions of data
- Understanding linear and exponential regression
- Using the Analysis Toolpak data analysis tools
- Installing the Analysis Toolpak
- Using the Descriptive Statistics tool
- Creating histograms
- Using the Rank And Percentile tool
- Generating random numbers
- Distributing random numbers uniformly
- Distributing random numbers normally
- Generating random numbers using Bernoulli distribution
- Generating random numbers using binomial distribution
- Generating random numbers using Poisson distribution
- Generating random numbers using discrete distribution
- Generating semi-random numbers using patterned distribution
- Sampling a population of numbers
- Calculating moving averages
- 18. Performing a what-if analysis
- Using data tables
- Using the Scenario Manager
- Using the Goal Seek command
- Using the Solver
- Stating the objective
- Specifying variable cells
- Specifying constraints
- Other Solver options
- Saving and reusing the Solver parameters
- Assigning the Solver results to named scenarios
- Generating reports
- The Sensitivity report
- The Answer report
- The Limits report
- VI. Creating charts
- 19. Designing charts
- Selecting data for your chart
- Creating a new chart
- Changing the chart type
- Assigning a series to a secondary axis
- 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
- Adding and removing gridlines
- Working with axes
- Modifying a chart’s data
- Adding moving averages and other trendlines
- Adding error bars
- 20. Formatting charts
- 21. Using sparklines
- 19. Designing charts
- VII. Managing databases and tables
- 22. Managing information in tables
- How to organize a table
- Creating a table
- Adding totals to a table
- Sorting tables and other ranges
- Filtering a list or table
- Using filters
- Determining how many rows pass the filter
- Removing a filter
- Using filter criteria in more than one column
- Using a filter to find the top or bottom n items
- Using a filter to display blank entries
- Using filters to select dates
- Using filters to specify more complex criteria
- Using custom filters to specify complex relationships
- Using the Advanced Filter command
- Removing duplicate records
- Using filters
- Using formulas with tables
- Formatting tables
- 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
- Displaying totals and subtotals
- Sorting PivotTable fields
- Filtering PivotTable fields
- Changing PivotTable calculations
- Grouping and ungrouping data
- Displaying the details behind a data value
- Creating PivotCharts
- Moving beyond PivotTables
- 24. An introduction to PowerPivot
- 25. Working with external data
- Using and reusing Office Data Connections
- Opening an entire Access table in Excel
- Working with data in text files
- Working with XML files
- Using Microsoft Query to import data
- Choosing tables and fields (columns)
- Filtering records
- Sorting records
- Working directly with Microsoft Query
- Using a web query to return Internet data
- Using an existing web query
- Creating your own web query
- Using the From Web command
- Copying and pasting from the web browser
- Exporting from Internet Explorer to Excel
- VIII. Using Excel collaboratively
- 26. Collaborating on a network or by email
- 27. Working in the cloud
- IX. Automating Excel
- 28. Recording macros
- 29. Creating custom functions
- 30. Debugging macros and custom functions
- Using design-time tools
- Dealing with run-time errors
- X. Integrating and extending Excel
- 31. Linking, hyperlinking, and embedding
- 32. Using Excel data in Word documents
- XI. Appendixes
- A. Menu-to-ribbon command reference
- B. Keyboard shortcuts
- Shortcuts by key: Function keys
- Shortcuts by key: Control and navigation keys
- Shortcuts by key: Numeric keys
- Shortcuts by key: Symbol keys and keypad
- Shortcuts by key: Letter keys
- Shortcuts by task: Insert charts
- Shortcuts by task: Work in dialog box text boxes
- Shortcuts by task: Work in dialog boxes
- Shortcuts by task: Edit data
- Shortcuts by task: Work with formulas
- Shortcuts by task: Enter data
- Shortcuts by task: Select cells
- Shortcuts by task: Filter tables
- Shortcuts by task: Work with borders
- Shortcuts by task: Format data
- Shortcuts by task: Work with Help
- Shortcuts by task: Insert, delete, and copy cells
- Shortcuts by task: Work with macros
- Shortcuts by task: Work with the ribbon
- Shortcuts by task: Move and scroll in End mode
- Shortcuts by task: Move and scroll in worksheets
- Shortcuts by task: Move within a selected range
- Shortcuts by task: Print
- Shortcuts by task: Work in Backstage view
- Shortcuts by task: Select cells, rows, columns, and objects
- Shortcuts by task: Select cells with special characteristics
- Shortcuts by task: Work with outlines
- Shortcuts by task: Work with options menus
- Shortcuts by task: Work with task panes
- Shortcuts by task: Navigate Windows
- Shortcuts by task: Work with worksheets
- C. Function reference
- D. Index to troubleshooting topics
- E. About the authors
- Index
- About the Authors
- Copyright
- 22. Managing information in tables
- 12. Building formulas
About the Authors
Mark Dodge is an Excel expert who has coauthored several popular books, including Microsoft Excel 2010 Inside Out. He is a former senior technical writer for the Microsoft Office User Assistance group.
Craig Stinson is a computer journalist and author specializing in Microsoft Office and Windows. He has written or coauthored more than 20 books, including Windows 7 Inside Out, Deluxe Edition, and Microsoft Excel 2010 Inside Out.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.