Title Microsoft® Excel® 2016 bible / John Walkenbach. [O'Reilly electronic resource]

Publication Info.	Indianapolis, IN : John Wiley & Sons, Inc., [2015]
	©2015

To Access:
Available on O'Reilly for Public Libraries

Bookmark link: https://library.naperville-lib.org:444/record=b3172723~S1

QR Code

Description	1 online resource
Note	Includes index.
Contents	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.
Summary	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 and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more. Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2016 Bible. Create functional spreadsheets that work Master formulas, formatting, pivot tables, and more Get acquainted with Excel 2016's new features and tools Customize downloadable templates and worksheets Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel 2016 Bible has you covered with complete coverage and clear expert guidance.
Subject	Microsoft Excel (Computer file)
	Microsoft Excel (Computer file)
	Electronic spreadsheets -- Computer programs.
	Business -- Computer programs.
	Tableurs -- Logiciels.
	Bureautique.
	Logiciels.
	Informatique.
	Manuels.
	Computers and IT.
	Business -- Computer programs
	Electronic spreadsheets -- Computer programs
	Computers and IT.
Added Title	Excel® 2016 bible
ISBN	9781119067498 (electronic bk.)
	1119067499 (electronic bk.)
	9781119067504
	1119067502
	1119067510
	9781119067511
	1523110708
	9781523110704

Patron reviews: add a review

You can...

Copies