Cargando…
Microsoft® Excel® 2016 bible /
The complete guide to Excel 2016, from Mr. Spreadsheet himself Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features a...
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Indianapolis, IN :
John Wiley & Sons, Inc.,
[2015]
|
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
Tabla de Contenidos:
- Ch. 1 Introducing Excel
- Identifying What Excel Is Good For
- Seeing What's New in Excel 2016
- Understanding Workbooks and Worksheets
- Moving Around a Worksheet
- Navigating with your keyboard
- Navigating with your mouse
- Using the Ribbon
- Ribbon tabs
- Contextual tabs
- Types of commands on the Ribbon
- Accessing the Ribbon by using your keyboard
- Using Shortcut Menus
- Customizing Your Quick Access Toolbar
- Working with Dialog Boxes
- Navigating dialog boxes
- Using tabbed dialog boxes
- Using Task Panes
- Creating Your First Excel Workbook
- Getting started on your worksheet
- Filling in the month names
- Entering the sales data
- Formatting the numbers
- Making your worksheet look a bit fancier
- Summing the values
- Creating a chart
- Printing your worksheet
- Saving your workbook
- ch. 2 Entering and Editing Worksheet Data
- Exploring Data Types
- Numeric values
- Text entries
- Formulas.
- Entering Text and Values into Your Worksheets
- Entering Dates and Times into Your Worksheets
- Entering date values
- Entering time values
- Modifying Cell Contents
- Deleting the contents of a cell
- Replacing the contents of a cell
- Editing the contents of a cell
- Learning some handy data-entry techniques
- Automatically moving the cell pointer after entering data
- Using navigation keys instead of pressing Enter
- Selecting a range of input cells before entering data
- Using Ctrl+Enter to place information into multiple cells simultaneously
- Entering decimal points automatically
- Using AutoFill to enter a series of values
- Using AutoComplete to automate data entry
- Forcing text to appear on a new line within a cell
- Using AutoCorrect for shorthand data entry
- Entering numbers with fractions
- Using a form for data entry
- Entering the current date or time into a cell
- Applying Number Formatting.
- Using automatic number formatting
- Formatting numbers by using the Ribbon
- Using shortcut keys to format numbers
- Formatting numbers by using the Format Cells dialog box
- Adding your own custom number formats
- ch. 3 Essential Worksheet Operations
- Learning the Fundamentals of Excel Worksheets
- Working with Excel windows
- Moving and resizing windows
- Switching among windows
- Closing windows
- Activating a worksheet
- Adding a new worksheet to your workbook
- Deleting a worksheet you no longer need
- Changing the name of a worksheet
- Changing a sheet tab color
- Rearranging your worksheets
- Hiding and unhiding a worksheet
- Controlling the Worksheet View
- Zooming in or out for a better view
- Viewing a worksheet in multiple windows
- Comparing sheets side by side
- Splitting the worksheet window into panes
- Keeping the titles in view by freezing panes
- Monitoring cells with a Watch Window.
- Working with Rows and Columns
- Inserting rows and columns
- Deleting rows and columns
- Changing column widths and row heights
- Changing column widths
- Changing row heights
- Hiding rows and columns
- ch. 4 Working with Cells and Ranges
- Understanding Cells and Ranges
- Selecting ranges
- Selecting complete rows and columns
- Selecting noncontiguous ranges
- Selecting multisheet ranges
- Selecting special types of cells
- Selecting cells by searching
- Copying or Moving Ranges
- Copying by using Ribbon commands
- Copying by using shortcut menu commands
- Copying by using shortcut keys
- Copying or moving by using drag-and-drop
- Copying to adjacent cells
- Copying a range to other sheets
- Using the Office Clipboard to paste
- Pasting in special ways
- Using the Paste Special dialog box
- Performing mathematical operations without formulas
- Skipping blanks when pasting
- Transposing a range.
- Using Names to Work with Ranges
- Creating range names in your workbooks
- Using the Name box
- Using the New Name dialog box
- Using the Create Names from Selection dialog box
- Managing names
- Adding Comments to Cells
- Formatting comments
- Changing a comment's shape
- Reading comments
- Printing comments
- Hiding and showing comments
- Selecting comments
- Editing comments
- Deleting comments
- ch. 5 Introducing Tables
- What Is a Table?
- Creating a Table
- Changing the Look of a Table
- Working with Tables
- Navigating in a table
- Selecting parts of a table
- Adding new rows or columns
- Deleting rows or columns
- Moving a table
- Working with the Total Row
- Removing duplicate rows from a table
- Sorting and filtering a table
- Sorting a table
- Filtering a table
- Filtering a table with slicers
- Converting a table back to a range
- ch. 6 Worksheet Formatting
- Getting to Know the Formatting Tools.
- Using the formatting tools on the Home tab
- Using the Mini toolbar
- Using the Format Cells dialog box
- Using Different Fonts to Format Your Worksheet
- Changing Text Alignment
- Choosing horizontal alignment options
- Choosing vertical alignment options
- Wrapping or shrinking text to fit the cell
- Merging worksheet cells to create additional text space
- Displaying text at an angle
- Controlling the text direction
- Using Colors and Shading
- Adding Borders and Lines
- Adding a Background Image to a Worksheet
- Using Named Styles for Easier Formatting
- Applying styles
- Modifying an existing style
- Creating new styles
- Merging styles from other workbooks
- Controlling styles with templates
- Understanding Document Themes
- Applying a theme
- Customizing a theme
- ch. 7 Understanding Excel Files
- Creating a New Workbook
- Opening an Existing Workbook
- Filtering filenames
- Choosing your file display preferences.
- Saving a Workbook
- Using AutoRecover
- Recovering versions of the current workbook
- Recovering unsaved work
- Configuring AutoRecover
- Password-Protecting a Workbook
- Organizing Your Files
- Other Workbook Info Options
- Protect Workbook options
- Check for Issues options
- Manage Versions options
- Browser View options
- Compatibility Mode section
- Closing Workbooks
- Safeguarding Your Work
- Excel File Compatibility
- Checking compatibility
- Recognizing the Excel 2016 file formats
- Saving a file for use with an older version of Excel
- ch. 8 Using and Creating Templates
- Exploring Excel Templates
- Viewing templates
- Creating a workbook from a template
- Modifying a template
- Understanding Custom Excel Templates
- Working with the default templates
- Using the workbook template to change workbook defaults
- Creating a worksheet template
- Editing your template
- Resetting the default workbook.
- Creating custom templates
- Saving your custom templates
- Using custom templates
- Getting ideas for creating templates
- ch. 9 Printing Your Work
- Basic Printing
- Changing Your Page View
- Normal view
- Page Layout view
- Page Break Preview
- Adjusting Common Page Setup Settings
- Choosing your printer
- Specifying what you want to print
- Changing page orientation
- Specifying paper size
- Printing multiple copies of a report
- Adjusting the page margins
- Understanding page breaks
- Inserting a page break
- Removing manual page breaks
- Printing row and column titles
- Scaling printed output
- Printing cell gridlines
- Printing row and column headers
- Using a background image
- Adding a Header or a Footer to Your Reports
- Selecting a predefined header or footer
- Understanding header and footer element codes
- Other header and footer options
- Other Print-Related Topics
- Copying Page Setup settings across Sheets.
- Preventing certain cells from being printed
- Preventing objects from being printed
- Creating custom views of your worksheet
- Creating PDF files
- ch. 10 Introducing Formulas and Functions
- Understanding Formula Basics
- Using operators in formulas
- Understanding operator precedence in formulas
- Using functions in your formulas
- Examples of formulas that use functions
- Function arguments
- More about functions
- Entering Formulas into Your Worksheets
- Entering formulas manually
- Entering formulas by pointing
- Pasting range names into formulas
- Inserting functions into formulas
- Function entry tips
- Editing Formulas
- Using Cell References in Formulas
- Using relative, absolute, and mixed references
- Changing the types of your references
- Referencing cells outside the worksheet
- Referencing cells in other worksheets
- Referencing cells in other workbooks
- Using Formulas in Tables
- Summarizing data in a table.
- Using formulas within a table
- Referencing data in a table
- Correcting Common Formula Errors
- Handling circular references
- Specifying when formulas are calculated
- Using Advanced Naming Techniques
- Using names for constants
- Using names for formulas
- Using range intersections
- Applying names to existing references
- Working with Formulas
- Not hard-coding values
- Using the Formula bar as a calculator
- Making an exact copy of a formula
- Converting formulas to values
- ch. 11 Creating Formulas That Manipulate Text
- A Few Words About Text
- Text Functions
- Working with character codes
- The CODE function
- The CHAR function
- Determining whether two strings are identical
- Joining two or more cells
- Displaying formatted values as text
- Displaying formatted currency values as text
- Repeating a character or string
- Creating a text histogram
- Padding a number
- Removing excess spaces and nonprinting characters.
- Counting characters in a string
- Changing the case of text
- Extracting characters from a string
- Replacing text with other text
- Finding and searching within a string
- Searching and replacing within a string
- Advanced Text Formulas
- Counting specific characters in a cell
- Counting the occurrences of a substring in a cell
- Extracting the first word of a string
- Extracting the last word of a string
- Extracting all but the first word of a string
- Extracting first names, middle names, and last names
- Removing titles from names
- Creating an ordinal number
- Counting the number of words in a cell
- ch. 12 Working with Dates and Times
- How Excel Handles Dates and Times
- Understanding date serial numbers
- Entering dates
- Understanding time serial numbers
- Entering times
- Formatting dates and times
- Problems with dates
- Excel's leap year bug
- Pre-1900 dates
- Inconsistent date entries.
- Date-Related Worksheet Functions
- Displaying the current date
- Displaying any date
- Generating a series of dates
- Converting a nondate string to a date
- Calculating the number of days between two dates
- Calculating the number of workdays between two dates
- Offsetting a date using only workdays
- Calculating the number of years between two dates
- Calculating a person's age
- Determining the day of the year
- Determining the day of the week
- Determining the week of the year
- Determining the date of the most recent Sunday
- Determining the first day of the week after a date
- Determining the nth occurrence of a day of the week in a month
- Calculating dates of holidays
- New Year's Day
- Martin Luther King, Jr., Day
- Presidents' Day
- Easter
- Memorial Day
- Independence Day
- Labor Day
- Columbus Day
- Veterans Day
- Thanksgiving Day
- Christmas Day
- Determining the last day of a month.
- Determining whether a year is a leap year
- Determining a date's quarter
- Time-Related Worksheet Functions
- Displaying the current time
- Displaying any time
- Calculating the difference between two times
- Summing times that exceed 24 hours
- Converting from military time
- Converting decimal hours, minutes, or seconds to a time
- Adding hours, minutes, or seconds to a time
- Rounding time values
- Working with non-time-of-day values
- ch. 13 Creating Formulas That Count and Sum
- Counting and Summing Worksheet Cells
- Basic Counting Formulas
- Counting the total number of cells
- Counting blank cells
- Counting nonblank cells
- Counting numeric cells
- Counting text cells
- Counting nontext cells
- Counting logical values
- Counting error values in a range
- Advanced Counting Formulas
- Counting cells by using the COUNTIF function
- Counting cells based on multiple criteria
- Using And criteria
- Using Or criteria.
- Combining And and Or criteria
- Counting the most frequently occurring entry
- Counting the occurrences of specific text
- Entire cell contents
- Partial cell contents
- Total occurrences in a range
- Counting the number of unique values
- Creating a frequency distribution
- The FREQUENCY function
- Using formulas to create a frequency distribution
- Using the Analysis ToolPak to create a frequency distribution
- Using a pivot table to create a frequency distribution
- Summing Formulas
- Summing all cells in a range
- Computing a cumulative sum
- Ignoring errors when summing
- Summing the "top n" values
- Conditional Sums Using a Single Criterion
- Summing only negative values
- Summing values based on a different range
- Summing values based on a text comparison
- Summing values based on a date comparison
- Conditional Sums Using Multiple Criteria
- Using And criteria
- Using Or criteria
- Using And and Or criteria.
- Ch. 14 Creating Formulas That Look Up Values
- Introducing Lookup Formulas
- Functions Relevant to Lookups
- Basic Lookup Formulas
- The VLOOKUP function
- The HLOOKUP function
- The LOOKUP function
- Combining the MATCH and INDEX functions
- Specialized Lookup Formulas
- Looking up an exact value
- Looking up a value to the left
- Performing a case-sensitive lookup
- Looking up a value from multiple lookup tables
- Determining letter grades for test scores
- Calculating a grade-point average
- Performing a two-way lookup
- Performing a two-column lookup
- Determining the cell address of a value within a range
- Looking up a value by using the closest match
- ch. 15 Creating Formulas for Financial Applications
- The Time Value of Money
- Loan Calculations
- Worksheet functions for calculating loan information
- PMT
- PPMT
- IPMT
- RATE
- NPER
- PV
- A loan calculation example
- Credit card payments.
- Creating a loan amortization schedule
- Summarizing loan options by using a data table
- Creating a one-way data table
- Creating a two-way data table
- Calculating a loan with irregular payments
- Investment Calculations
- Future value of a single deposit
- Calculating simple interest
- Calculating compound interest
- Calculating interest with continuous compounding
- Future value of a series of deposits
- Depreciation Calculations
- Financial Forecasting
- ch. 16 Miscellaneous Calculations
- Unit Conversions
- Solving Right Triangles
- Area, Surface, Circumference, and Volume Calculations
- Calculating the area and perimeter of a square
- Calculating the area and perimeter of a rectangle
- Calculating the area and perimeter of a circle
- Calculating the area of a trapezoid
- Calculating the area of a triangle
- Calculating the surface and volume of a sphere
- Calculating the surface and volume of a cube.
- Calculating the surface and volume of a rectangular solid
- Calculating the surface and volume of a cone
- Calculating the volume of a cylinder
- Calculating the volume of a pyramid
- Rounding Numbers
- Basic rounding formulas
- Rounding to the nearest multiple
- Rounding currency values
- Working with fractional dollars
- Using the INT and TRUNC functions
- Rounding to an even or odd integer
- Rounding to n significant digits
- ch. 17 Introducing Array Formulas
- Understanding Array Formulas
- A multicell array formula
- A single-cell array formula
- Creating an Array Constant
- Understanding the Dimensions of an Array
- One-dimensional horizontal arrays
- One-dimensional vertical arrays
- Two-dimensional arrays
- Naming Array Constants
- Working with Array Formulas
- Entering an array formula
- Selecting an array formula range
- Editing an array formula
- Expanding or contracting a multicell array formula.
- Using Multicell Array Formulas
- Creating an array from values in a range
- Creating an array constant from values in a range
- Performing operations on an array
- Using functions with an array
- Transposing an array
- Generating an array of consecutive integers
- Using Single-Cell Array Formulas
- Counting characters in a range
- Summing the three smallest values in a range
- Counting text cells in a range
- Eliminating intermediate formulas
- Using an array in lieu of a range reference
- ch. 18 Performing Magic with Array Formulas
- Working with Single-Cell Array Formulas
- Summing a range that contains errors
- Counting the number of error values in a range
- Summing the n largest values in a range
- Computing an average that excludes zeros
- Determining whether a particular value appears in a range
- Counting the number of differences in two ranges
- Returning the location of the maximum value in a range.
- Finding the row of a value's nth occurrence in a range
- Returning the longest text in a range
- Determining whether a range contains valid values
- Summing the digits of an integer
- Summing rounded values
- Summing every nth value in a range
- Removing nonnumeric characters from a string
- Determining the closest value in a range
- Returning the last value in a column
- Returning the last value in a row
- Working with Multicell Array Formulas
- Returning only positive values from a range
- Returning nonblank cells from a range
- Reversing the order of cells in a range
- Sorting a range of values dynamically
- Returning a list of unique items in a range
- Displaying a calendar in a range
- ch. 19 Getting Started Making Charts
- What Is a Chart?
- Understanding How Excel Handles Charts
- Embedded charts
- Chart sheets
- Creating a Chart
- Hands On: Creating and Customizing a Chart
- Selecting the data
- Choosing a chart type.
- Experimenting with different styles
- Experimenting with different layouts
- Trying another view of the data
- Trying other chart types
- Working with Charts
- Resizing a chart
- Moving a chart
- Copying a chart
- Deleting a chart
- Adding chart elements
- Moving and deleting chart elements
- Formatting chart elements
- Printing charts
- Understanding Chart Types
- Choosing a chart type
- Column charts
- Bar charts
- Line charts
- Pie charts
- XY (scatter) charts
- Area charts
- Radar charts
- Surface charts
- Bubble charts
- Stock charts
- New Chart Types for Excel 2016
- Histogram charts
- Pareto charts
- Waterfall charts
- Box & whisker charts
- Sunburst charts
- Treemap charts
- Learning More
- ch. 20 Learning Advanced Charting
- Selecting Chart Elements
- Selecting with the mouse
- Selecting with the keyboard
- Selecting with the Chart Elements control
- User Interface Choices for Modifying Chart Elements.
- Using the Format task pane
- Using the chart customization buttons
- Using the Ribbon
- Using the Mini toolbar
- Modifying the Chart Area
- Modifying the Plot Area
- Working with Titles in a Chart
- Working with a Legend
- Working with Gridlines
- Modifying the Axes
- Value axis
- Category axis
- Working with Data Series
- Deleting or hiding a data series
- Adding a new data series to a chart
- Changing data used by a series
- Changing the data range by dragging the range outline
- Using the Edit Series dialog box
- Editing the Series formula
- Displaying data labels in a chart
- Handling missing data
- Adding error bars
- Adding a trendline
- Modifying 3-D charts
- Creating combination charts
- Displaying a data table
- Creating Chart Templates
- Learning Some Chart-Making Tricks
- Creating picture charts
- Creating a thermometer chart
- Creating a gauge chart
- Creating a comparative histogram
- Creating a Gantt chart.
- Plotting mathematical functions with one variable
- Plotting mathematical functions with two variables
- ch. 21 Visualizing Data Using Conditional Formatting
- About Conditional Formatting
- Specifying Conditional Formatting
- Formatting types you can apply
- Making your own rules
- Conditional Formats That Use Graphics
- Using data bars
- A simple data bar
- Using data bars in lieu of a chart
- Using color scales
- A color scale example
- An extreme color scale example
- Using icon sets
- An icon set example
- Another icon set example
- Creating Formula-Based Rules
- Understanding relative and absolute references
- Conditional formatting formula examples
- Identifying weekend days
- Highlighting a row based on a value
- Displaying alternate-row shading
- Creating checkerboard shading
- Shading groups of rows
- Displaying a total only when all values are entered
- Creating a Gantt Chart
- Working with Conditional Formats.
- Managing rules
- Copying cells that contain conditional formatting
- Deleting conditional formatting
- Locating cells that contain conditional formatting
- ch. 22 Creating Sparkline Graphics
- Sparkline Types
- Creating Sparklines
- Customizing Sparklines
- Sizing Sparkline cells
- Handling hidden or missing data
- Changing the Sparkline type
- Changing Sparkline colors and line width
- Highlighting certain data points
- Adjusting Sparkline axis scaling
- Faking a reference line
- Specifying a Date Axis
- Auto-Updating Sparklines
- Displaying a Sparkline for a Dynamic Range
- ch. 23 Enhancing Your Work with Pictures and Drawings
- Using Shapes
- Inserting a Shape
- Adding text to a Shape
- Formatting Shapes
- Stacking Shapes
- Grouping objects
- Aligning and spacing objects
- Reshaping Shapes
- Printing objects
- Using SmartArt
- Inserting SmartArt
- Customizing SmartArt
- Changing the layout and style.
- Learning more about SmartArt
- Using WordArt
- Working with Other Graphics Types
- About graphics files
- Inserting screenshots
- Displaying a worksheet background image
- Using the Equation Editor
- ch. 24 Customizing the Excel User Interface
- Customizing the Quick Access Toolbar
- About the Quick Access toolbar
- Adding new commands to the Quick Access toolbar
- Other Quick Access toolbar actions
- Customizing the Ribbon
- Why you may want to customize the Ribbon
- What can be customized
- How to customize the Ribbon
- Creating a new tab
- Creating a new group
- Adding commands to a new group
- Resetting the Ribbon
- ch. 25 Using Custom Number Formats
- About Number Formatting
- Automatic number formatting
- Formatting numbers by using the Ribbon
- Using shortcut keys to format numbers
- Using the Format Cells dialog box to format numbers
- Creating a Custom Number Format
- Parts of a number format string.
- Custom number format codes
- Custom Number Format Examples
- Scaling values
- Displaying values in thousands
- Displaying values in hundreds
- Displaying values in millions
- Appending zeros to a value
- Displaying leading zeros
- Specifying conditions
- Displaying fractions
- Displaying a negative sign on the right
- Formatting dates and times
- Displaying text with numbers
- Suppressing certain types of entries
- Filling a cell with a repeating character
- ch. 26 Using Data Validation
- About Data Validation
- Specifying Validation Criteria
- Types of Validation Criteria You Can Apply
- Creating a Drop-Down List
- Using Formulas for Data Validation Rules
- Understanding Cell References
- Data Validation Formula Examples
- Accepting text only
- Accepting a larger value than the previous cell
- Accepting nonduplicate entries only
- Accepting text that begins with a specific character
- Accepting dates by the day of the week.
- Accepting only values that don't exceed a total
- Creating a dependent list
- ch. 27 Creating and Using Worksheet Outlines
- Introducing Worksheet Outlines
- Creating an Outline
- Preparing the data
- Creating an outline automatically
- Creating an outline manually
- Working with Outlines
- Displaying levels
- Adding data to an outline
- Removing an outline
- Adjusting the outline symbols
- Hiding the outline symbols
- ch. 28 Linking and Consolidating Worksheets
- Linking Workbooks
- Creating External Reference Formulas
- Understanding link formula syntax
- Creating a link formula by pointing
- Pasting links
- Working with External Reference Formulas
- Creating links to unsaved workbooks
- Opening a workbook with external reference formulas
- Changing the startup prompt
- Updating links
- Changing the link source
- Severing links
- Avoiding Potential Problems with External Reference Formulas.
- Renaming or moving a source workbook
- Using the Save As command
- Modifying a source workbook
- Intermediary links
- Consolidating Worksheets
- Consolidating worksheets by using formulas
- Consolidating worksheets by using Paste Special
- Consolidating worksheets by using the Consolidate dialog box
- A workbook consolidation example
- Refreshing a consolidation
- More about consolidation
- ch. 29 Excel and the Internet
- Saving a Workbook on the Internet
- Saving Workbooks in HTML Format
- Creating an HTML file
- Creating a single-file web page
- Opening an HTML File
- Working with Hyperlinks
- Inserting a hyperlink
- Using hyperlinks
- E-Mail Features
- Discovering Office Add-Ins
- ch. 30 Protecting Your Work
- Types of Protection
- Protecting a Worksheet
- Unlocking cells
- Sheet protection options
- Assigning user permissions
- Protecting a Workbook
- Requiring a password to open a workbook.
- Protecting a workbook's structure
- VBA Project Protection
- Related Topics
- Saving a worksheet as a PDF file
- Marking a workbook final
- Inspecting a workbook
- Using a digital signature
- Getting a digital ID
- Signing a workbook
- ch. 31 Making Your Worksheets Error Free
- Finding and Correcting Formula Errors
- Mismatched parentheses
- Cells are filled with hash marks
- Blank cells are not blank
- Extra space characters
- Formulas returning an error
- #DIV/O! errors
- #N/A errors
- #NAME? errors
- #NULL! errors
- #NUM! errors
- #REF! errors
- #VALUE! errors
- Absolute/relative reference problems
- Operator precedence problems
- Formulas are not calculated
- Actual versus displayed values
- Floating point number errors
- "Phantom link" errors
- Using Excel Auditing Tools
- Identifying cells of a particular type
- Viewing formulas
- Tracing cell relationships
- Identifying precedents
- Identifying dependents.
- Tracing error values
- Fixing circular reference errors
- Using the background error-checking feature
- Using Formula Evaluator
- Searching and Replacing
- Searching for information
- Replacing information
- Searching for formatting
- Spell-Checking Your Worksheets
- Using AutoCorrect
- ch. 32 Importing and Cleaning Data
- Importing Data
- Importing from a file
- Spreadsheet file formats
- Database file formats
- Text file formats
- Importing HTML files
- Importing XML files
- Importing a text file into a specified range
- Copying and pasting data
- Data Cleanup Techniques
- Removing duplicate rows
- Identifying duplicate rows
- Splitting text
- Using Text to Columns
- Using Flash Fill
- Changing the case of text
- Removing extra spaces
- Removing strange characters
- Converting values
- Classifying values
- Joining columns
- Rearranging columns
- Randomizing the rows
- Extracting a filename from a URL.
- Matching text in a list
- Changing vertical data to horizontal data
- Filling gaps in an imported report
- Checking spelling
- Replacing or removing text in cells
- Adding text to cells
- Fixing trailing minus signs
- A Data Cleaning Checklist
- Exporting Data
- Exporting to a text file
- CSV files
- TXT files
- PRN files
- Exporting to other file formats
- ch. 33 Introducing Pivot Tables
- About Pivot Tables
- A pivot table example
- Data appropriate for a pivot table
- Creating a Pivot Table Automatically
- Creating a Pivot Table Manually
- Specifying the data
- Specifying the location for the pivot table
- Laying out the pivot table
- Formatting the pivot table
- Modifying the pivot table
- More Pivot Table Examples
- What is the daily total new deposit amount for each branch?
- Which day of the week accounts for the most deposits?
- How many accounts were opened at each branch, broken down by account type?
- What's the dollar distribution of the different account types?
- What types of accounts do tellers open most often?
- In which branch do tellers open the most checking accounts for new customers?
- Learning More
- ch. 34 Analyzing Data with Pivot Tables
- Working with Nonnumeric Data
- Grouping Pivot Table Items
- A manual grouping example
- Automatic grouping examples
- Grouping by date
- Grouping by time
- Creating a Frequency Distribution
- Creating a Calculated Field or Calculated Item
- Creating a calculated field
- Inserting a calculated item
- Filtering Pivot Tables with Slicers
- Filtering Pivot Tables with a Timeline
- Referencing Cells Within a Pivot Table
- Creating Pivot Charts
- A pivot chart example
- More about pivot charts
- Another Pivot Table Example
- Using the Data Model
- Learning More About Pivot Tables
- ch. 35 Performing Spreadsheet What-If Analysis
- A What-If Example
- Types of What-If Analyses.
- Performing manual what-if analysis
- Creating data tables
- Creating a one-input data table
- Creating a two-input data table
- Using Scenario Manager
- Defining scenarios
- Displaying scenarios
- Moodifying scenarios
- Merging scenarios
- Generating a scenario report
- ch. 36 Analyzing Data Using Goal Seeking and Solver
- What-If Analysis, in Reverse
- Single-Cell Goal Seeking
- A goal-seeking example
- More about goal seeking
- Introducing Solver
- Appropriate problems for Solver
- A simple Solver example
- More about Solver
- Solver Examples
- Solving simultaneous linear equations
- Minimizing shipping costs
- Allocating resources
- Optimizing an investment portfolio
- ch. 37 Analyzing Data with the Analysis ToolPak
- The Analysis ToolPak: An Overview
- Installing the Analysis ToolPak Add-In
- Using the Analysis Tools
- Introducing the Analysis ToolPak Tools
- Analysis of Variance
- Correlation
- Covariance.
- Descriptive Statistics
- Exponential Smoothing
- F-test (two-sample test for variance)
- Fourier Analysis
- Histogram
- Moving Average
- Random Number Generation
- Rank and Percentile
- Regression
- Sampling
- T-Test
- Z-Test (two-sample test for means)
- ch. 38 Working with Get & Transform
- Get & Transform: An Overview
- Data Sources for Get & Transform
- Example: A Simple Query
- Choosing the data source
- Removing extraneous columns
- Importing the data
- Modifying the query
- Refreshing the query
- How Your Actions Are Recorded
- Example: Returning Summarized Data
- Example: Transforming Data from a Web Query
- Separating the year from the movie title
- Fixing the dollar values
- Adding an Index column
- Importing the data
- Example: Merging Two Web Queries
- Performing the first web query
- Performing the second web query
- Merging the two queries
- Example: Getting a List of Files.
- Example: Choosing a Random Sample
- Example: Unpivoting a Table
- Tips for Using Get & Transform
- Learning More
- ch. 39 Introducing Visual Basic for Applications
- Introducing VBA Macros
- Displaying the Developer Tab
- About Macro Security
- Saving Workbooks That Contain Macros
- Two Types of VBA Macros
- VBA Sub procedures
- VBA functions
- Creating VBA Macros
- Recording VBA macros
- Recording your actions to create VBA code: The basics
- Recording a macro: A simple example
- Examining the macro
- Testing the macro
- Editing the macro
- Another example
- Running the macro
- Examining the macro
- Rerecording the macro
- Testing the macro
- More about recording VBA macros
- Absolute versus relative recording
- Storing macros in your Personal Macro Workbook
- Assigning a macro to a shortcut key
- Assigning a macro to a button
- Adding a macro to your Quick Access toolbar
- Writing VBA code.
- The basics: Entering and editing code
- How VBA works
- Objects and collections
- Properties
- Methods
- Variables
- Controlling execution
- A macro that can't be recorded
- Learning More
- ch. 40 Creating Custom Worksheet Functions
- Overview of VBA Functions
- An Introductory Example
- A custom function
- Using the function in a worksheet
- Analyzing the custom function
- About Function Procedures
- Executing Function Procedures
- Calling custom functions from a procedure
- Using custom functions in a worksheet formula
- Function Procedure Arguments
- A function with no argument
- A function with one argument
- Another function with one argument
- A function with two arguments
- A function with a range argument
- A simple but useful function
- Debugging Custom Functions
- Inserting Custom Functions
- Learning More
- ch. 41 Creating UserForms
- Why Create UserForms?
- UserForm Alternatives
- The InputBox function.
- The MsgBox function
- Creating UserForms: An Overview
- Working with UserForms
- Adding controls
- Changing the properties of a control
- Handling events
- Displaying a UserForm
- A UserForm Example
- Creating the UserForm
- Testing the UserForm
- Creating an event handler procedure
- Another UserForm Example
- Creating the UserForm
- Testing the UserForm
- Creating event handler procedures
- Testing the UserForm
- Making the macro available from a worksheet button
- Making the macro available on your Quick Access toolbar
- More on Creating UserForms
- Adding accelerator keys
- Controlling tab order
- Learning More
- ch. 42 Using UserForm Controls in a Worksheet
- Why Use Controls on a Worksheet?
- Using Controls
- Adding a control
- About Design mode
- Adjusting properties
- Common properties
- Linking controls to cells
- Creating macros for controls
- Reviewing the Available ActiveX Controls
- CheckBox
- ComboBox.
- CommandButton
- Image
- Label
- ListBox
- OptionButton
- ScrollBar
- SpinButton
- TextBox
- ToggleButton
- ch. 43 Working with Excel Events
- Understanding Events
- Entering Event-Handler VBA Code
- Using Workbook-Level Events
- Using the Open event
- Using the SheetActivate event
- Using the NewSheet event
- Using the BeforeSave event
- Using the BeforeClose event
- Working with Worksheet Events
- Using the Change event
- Monitoring a specific range for changes
- Using the SelectionChange event
- Using the BeforeRightClick event
- Using Nonobject Events
- Using the OnTime event
- Using the OnKey event
- ch. 44 VBA Examples
- Working with Ranges
- Copying a range
- Copying a variable-size range
- Selecting to the end of a row or column
- Selecting a row or column
- Moving a range
- Looping through a range efficiently
- Prompting for a cell value
- Determining the type of selection
- Identifying a multiple selection.
- Counting selected cells
- Working with Workbooks
- Saving all workbooks
- Saving and closing all workbooks
- Working with Charts
- Modifying the chart type
- Modifying chart properties
- Applying chart formatting
- VBA Speed Tips
- Turning off screen updating
- Preventing alert messages
- Simplifying object references
- Declaring variable types
- ch. 45 Creating Custom Excel Add-Ins
- What Is an Add-In?
- Working with Add-Ins
- Why Create Add-Ins?
- Creating Add-Ins
- An Add-In Example
- About Module1
- About the UserForm
- Testing the workbook
- Adding descriptive information
- Creating the user interface for your add-in macro
- Protecting the project
- Creating the add-in
- Installing the add-in
- Appendix A Worksheet Function Reference
- Appendix B Excel Shortcut Keys.