Microsoft Excel 365 Bible

Description

Your personal, hands-on guide to the latest and most useful features in Microsoft Excel 365 Excel 365 is Microsoft’s latest cloud-based version of its world-famous spreadsheet app. Powerful and user-friendly, it’s an ideal solution for businesses and people looking to make sense of—and draw intelligence from—their data. The Excel 365 Bible carries over the best content from the best-selling Excel 2019 Bible while reflecting how a new generation uses Excel in Excel 365. The authoring team with their decades of Excel and business intelligence experience and recognition from the Excel community as Excel MVPs delivers an accessible and authoritative roadmap to Excel 365. Interested in the basics? You’ll learn to create spreadsheets and workbooks and navigate the user interface. If you’re ready for more advanced topics you can skip right to the material on creating visualizations, crafting custom functions, and using Visual Basic for Applications to script automations. You’ll also get: • Over 900 pages of powerful tips, tricks, and strategies to unlock the full potential of Microsoft Excel 365 • Guidance on how to import, manage, and analyze large amounts of data • Advice on how to craft predictions and "What-If Analyses" based on data you already have Perfect for anyone new to Excel, as well as experts and advanced users, the Excel 365 Bible is your comprehensive, go-to guide for everything you need to know about the world’s most popular, easy-to-use spreadsheet software. Cover Title Page Copyright Page About the Authors About the Technical Editor Acknowledgments Contents at a Glance Contents Introduction Looking at What’s New in Excel 365 Is This Book for You? Software Versions Conventions Used in This Book Excel commands Typographical conventions Mouse conventions How This Book Is Organized How to Use This Book What’s on the Website Part I Getting Started with Excel Chapter 1 Introducing Excel Understanding What Excel Is Used For 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 Changing Your Mind 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 Chapter 2 Entering and Editing Worksheet Data Exploring Data Types Numeric values Text entries Formulas Error values Entering Text and Values into Your Worksheets Entering numbers Entering text Using Enter mode 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 selection after entering data Selecting a range of input cells before entering data Using Ctrl+Enter to place information into multiple cells simultaneously Changing modes 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 Using Excel on a Tablet Exploring Excel’s tablet interface Entering formulas on a tablet Introducing the Draw Ribbon Chapter 3 Performing Basic 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 Selecting 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 Chapter 4 Working with Excel Ranges and Tables Understanding Cells and Ranges Selecting ranges Selecting complete rows and columns Selecting noncontiguous ranges Selecting multi-sheet 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 Showing comments Replying to comments Editing comments and replies Deleting comments and replies Resolving comment threads Adding Notes to Cells Showing notes Formatting notes Editing notes Deleting notes Working with Tables Understanding a table’s structure The header row The data body The total row The resizing handle Creating a table Adding data to a table Sorting and filtering table data Sorting a table Filtering a table Filtering a table with slicers Changing the table’s appearance Chapter 5 Formatting Worksheets Getting to Know the Formatting Tools Using the formatting tools on the Home tab Using the Mini toolbar Using the Format Cells dialog box Formatting Your Worksheet Using 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 Using colors and shading Adding borders and lines Using Conditional Formatting Specifying conditional formatting Using graphical conditional formats Using data bars Using color scales Using icon sets 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-rowshading Creating checkerboard shading Shading groups of rows Working with conditional formats Managing rules Copying cells that contain conditional formatting Deleting conditional formatting Locating cells that contain conditional formatting 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 Chapter 6 Understanding Excel Files and Templates Creating a New Workbook Opening an Existing Workbook About Protected View Filtering filenames Choosing your file display preferences Saving a Workbook File-Naming Rules 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 Version History Manage Workbook options Browser View options Compatibility Mode section Closing Workbooks Safeguarding Your Work Working with Templates Exploring Excel templates Viewing templates Creating a workbook from a template Modifying a template Using default templates Using the workbook template to change workbook defaults Creating a worksheet template Editing your template Resetting the default workbook Using custom workbook templates Creating custom templates Saving your custom templates Using custom templates Chapter 7 Printing Your Work Doing Basic Printing Using Print Preview 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 Exploring other header and footer options Exploring 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 Chapter 8 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 Part II Working with Formulas and Functions Chapter 9 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 Using Formula AutoComplete 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 Introducing Formula Variables Understanding the LET function Formula variables in action 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 Chapter 10 Understanding and Using Array Formulas Understanding Legacy Array Formulas Example of a legacy array formula Editing legacy array formulas Introducing Dynamic Arrays Dynamic Arrays and Compatibility Understanding spill ranges Referencing spill ranges Exploring Dynamic Array Functions The SORT function The SORTBY function The UNIQUE function The RANDARRAY function The SEQUENCE function The FILTER function Using multiple conditions with the FILTER function Filtering records that contain a search term The XLOOKUP function XLOOKUP with wildcards Chapter 11 Using Formulas for Common Mathematical Operations Calculating Percentages Calculating percent of goal Calculating percent variance Calculating percent variance with negative values Calculating a percent distribution Calculating a running total Applying a percent increase or decrease to values Dealing with divide-by-zero errors Rounding Numbers Rounding numbers using formulas Rounding to the nearest penny Rounding to significant digits Counting Values in a Range Using Excel’s Conversion Functions Chapter 12 Using Formulas to Manipulate Text Working with Text When a Number Isn’t Treated as a Number Using Text Functions Joining text strings Setting text to sentence case Removing spaces from a text string Extracting parts of a text string Finding a particular character in a text string Finding the second instance of a character Substituting text strings Counting specific characters in a cell Adding a line break within a formula Cleaning strange characters from text fields Padding numbers with zeros Formatting the numbers in a text string Using the DOLLAR function Chapter 13 Using Formulas with Dates and Times Understanding 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-1900dates Inconsistent date entries Using Excel’s Date and Time Functions Getting the current date and time Calculating age Calculating the number of days between two dates Calculating the number of workdays between two dates Using NETWORKDAYS.INTL Generating a list of business days excluding holidays Extracting parts of a date Calculating number of years and months between dates Converting dates to Julian date formats Calculating the percent of year completed and remaining Returning the last date of a given month Using the EOMONTH function Calculating the calendar quarter for a date Calculating the fiscal quarter for a date Returning a fiscal month from a date Calculating the date of the Nth weekday of the month Calculating the date of the last weekday of the month Extracting parts of a time Calculating elapsed time Rounding time values Converting decimal hours, minutes, or seconds to a time Adding hours, minutes, or seconds to a time Chapter 14 Using Formulas for Conditional Analysis Understanding Conditional Analysis Checking if a simple condition is met Checking for multiple conditions Validating conditional data Looking up values Checking if Condition1 AND Condition2 are met Referring to logical conditions in cells Checking if Condition1 OR Condition2 are met Performing Conditional Calculations Summing all values that meet a certain condition Summing greater than zero Summing all values that meet two or more conditions Summing if values fall between a given date range Using SUMIFS Getting a count of values that meet a certain condition Getting a count of values that meet two or more conditions Finding nonstandard characters Getting the average of all numbers that meet a certain condition Getting the average of all numbers that meet two or more conditions Chapter 15 Using Formulas for Matching and Lookups Introducing Lookup Formulas Leveraging Excel’s Lookup Functions Looking up an exact value based on a left lookup column Looking up an exact value based on any lookup column Looking up values horizontally Hiding errors returned by lookup functions Finding the closest match from a list of banded values Finding the closest match with the INDEX and MATCH functions Looking up values from multiple tables Looking up a value based on a two-way matrix Using default values for match Finding a value based on multiple criteria Returning text with SUMPRODUCT Finding the last value in a column Finding the last number using LOOKUP Chapter 16 Using Formulas with Tables and Conditional Formatting Highlighting Cells That Meet Certain Criteria Highlighting cells based on the value of another cell Highlighting Values That Exist in List1 but Not List2 Highlighting Values That Exist in List1 and List2 Highlighting Based on Dates Highlighting days between two dates Highlighting dates based on a due date Chapter 17 Making Your Formulas 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/0! errors #N/A errors #NAME? errors #NULL! errors #NUM! errors #REF! errors #SPILL! errors #VALUE! errors Operator precedence problems Formulas are not calculated Problems with decimal precision “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 Part III Creating Charts and Other Visualizations Chapter 18 Getting Started with Excel Charts What Is a Chart? How Excel handles charts Embedded charts Chart sheets Parts of a chart Chart limitations Basic Steps for Creating a Chart Creating the chart Switching the row and column orientation Changing the chart type Applying a chart layout Applying a chart style Adding and deleting chart elements Formatting chart elements Modifying and Customizing Charts Moving and resizing a chart Converting an embedded chart to a chart sheet Copying a chart Deleting a chart Adding chart elements Moving and deleting chart elements Formatting chart elements Copying a chart’s formatting Renaming a chart 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 Newer Chart Types for Excel Histogram charts Pareto charts Waterfall charts Box & whisker charts Sunburst charts Treemap charts Funnel charts Map charts Chapter 19 Using Advanced Charting Techniques Selecting Chart Elements Selecting with the mouse Selecting with the keyboard Selecting with the Chart Elements control Exploring the 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 Modifying the value axis Modifying the 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 Creating combination charts Displaying a data table Creating Chart Templates Chapter 20 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 Chapter 21 Visualizing with Custom Number Formats and Shapes Visualizing with Number Formatting Doing basic number formatting Using shortcut keys to format numbers Using the Format Cells dialog box to format numbers Getting fancy with custom number formatting Formatting numbers in thousands and millions Hiding and suppressing zeros Applying custom format colors Formatting dates and times Using symbols to enhance reporting Using Shapes and Icons as Visual Elements Inserting a shape Inserting SVG icon graphics Inserting 3D models Formatting shapes and icons Enhancing Excel reports with shapes Creating visually appealing containers with shapes Layering shapes to save space Constructing your own infographic widgets with shapes Creating dynamic labels Creating linked pictures Using SmartArt and WordArt SmartArt basics WordArt basics Working with Other Graphics Types About graphics files Inserting screenshots Displaying a worksheet background image Using the Equation Editor Part IV Managing and Analyzing Data Chapter 22 Importing and Cleaning Data Importing Data Importing from a file Spreadsheet file formats Database file formats Text file formats HTML files XML files Importing vs. opening Importing a text file Copying and pasting data Cleaning Up Data 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 Following a data cleaning checklist Exporting Data Exporting to a text file CSV files TXT files PRN files Exporting to other file formats Chapter 23 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 Using Data Validation without Restricting Entry Showing an input message Making suggested entries Chapter 24 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 Chapter 25 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 Using Intermediary links Consolidating Worksheets Time to Rethink Your Consolidation Strategy? Consolidating worksheets by using formulas Consolidating worksheets by using Paste Special Consolidating worksheets by using the Consolidate dialog box Viewing a workbook consolidation example Refreshing a consolidation Learning more about consolidation Chapter 26 Introducing PivotTables About PivotTables A PivotTable example Data appropriate for a PivotTable Creating a PivotTable Automatically Creating a PivotTable Manually Specifying the data Specifying the location for the PivotTable Laying out the PivotTable Formatting the PivotTable Modifying the PivotTable Seeing More PivotTable 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? How much money was used to open the accounts? What types of accounts do tellers open most often? In which branch do tellers open the most checking accounts for new customers? Learning More Chapter 27 Analyzing Data with PivotTables Working with Non-Numeric Data Grouping PivotTable Items Grouping items manually Grouping items automatically Grouping by date Grouping by time Using a PivotTable to Create a Frequency Distribution Creating a Calculated Field or Calculated Item Creating a calculated field Inserting a calculated item Filtering PivotTables with Slicers Filtering PivotTables with a Timeline Referencing Cells within a PivotTable Creating PivotCharts A PivotChart example More about PivotCharts Using the Data Model Chapter 28 Performing Spreadsheet What-If Analysis Looking at a What-If Example Exploring Types of What-If Analyses Performing manual what-if analysis Creating data tables Creating a one-inputdata table Creating a two-inputdata table Using Scenario Manager Defining scenarios Displaying scenarios Modifying scenarios Merging scenarios Generating a scenario report Analyzing Data with Artificial Intelligence Using Excel’s suggestions Querying analyzed data Chapter 29 Analyzing Data Using Goal Seeking and Solver Exploring What-If Analysis, in Reverse Using Single-Cell Goal Seeking Looking at a goal-seeking example Learning more about goal seeking Introducing Solver Looking at appropriate problems for Solver Seeing a simple Solver example Exploring Solver options Seeing Some Solver Examples Solving simultaneous linear equations Minimizing shipping costs Allocating resources Optimizing an investment portfolio Chapter 30 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) Chapter 31 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 Protecting a VBA Project Related Topics Saving a worksheet as a PDF file Marking a workbook as final Inspecting a workbook Using a digital signature Getting a digital ID Signing a workbook Part V Understanding Power Pivot and Power Query Chapter 32 Introducing Power Pivot Understanding the Power Pivot Internal Data Model The Power Pivot Ribbon Linking Excel tables to Power Pivot Preparing your Excel tables Adding your Excel tables to the data model Creating relationships between your PowerPivot tables Managing existing relationships Using Power Pivot data in reporting Loading Data from Other Data Sources Loading data from relational databases Loading data from SQL Server Loading data from other relational database systems Loading data from flat files Loading data from external Excel files Loading data from text files Loading data from the Clipboard Refreshing and managing external data connections Manually refreshing your Power Pivot data Setting up automatic refreshing Editing your data connection Chapter 33 Working Directly with the Internal Data Model Directly Feeding the Internal Data Model Managing Relationships in the Internal Data Model Managing Queries & Connections Chapter 34 Adding Formulas to Power Pivot Enhancing Power Pivot Data with Calculated Columns Creating your first calculated column Formatting your calculated columns Referencing calculated columns in other calculations Hiding calculated columns from end users Utilizing DAX to Create Calculated Columns Identifying DAX functions safe for calculated columns Building DAX-driven calculated columns Month sorting in Power Pivot–driven PivotTables Referencing fields from other tables Nesting functions Understanding Calculated Measures Editing and deleting calculated measures Using Cube Functions to Free Your Data Chapter 35 Introducing Power Query Understanding Power Query Basics Understanding query steps Viewing the Advanced Query Editor Refreshing Power Query data Managing existing queries Understanding column-level actions Understanding table actions Getting Data from External Sources Importing data from files Getting data from Excel workbooks Getting data from CSV and text files Getting data from PDF files Importing data from database systems Importing data from relational and OLAP databases Importing data from Azure databases Importing data using ODBC connections to nonstandard databases Getting Data from Other Data Systems Managing Data Source Settings Editing data source settings Data Profiling with Power Query Data profiling options Data profiling quick actions Chapter 36 Transforming Data with Power Query Performing Common Transformation Tasks Removing duplicate records Filling in blank fields Filling in empty strings Concatenating columns Changing case Finding and replacing specific text Trimming and cleaning text Extracting the left, right, and middle values Extracting first and last characters Extracting middle characters Splitting columns using character markers Unpivoting columns Unpivoting other columns Pivoting columns Creating Custom Columns Concatenating with a custom column Understanding data type conversions Spicing up custom columns with functions Adding conditional logic to custom columns Grouping and Aggregating Data Working with Custom Data Types Chapter 37 Making Queries Work Together Reusing Query Steps Understanding the Append Feature Creating the needed base queries Appending the data Understanding the Merge Feature Understanding Power Query joins Merging queries Understanding fuzzy matching Chapter 38 Enhancing Power Query Productivity Implementing Some Power Query Productivity Tips Getting quick information about your queries Organizing queries in groups Selecting columns in your queries faster Renaming query steps Quickly creating reference tables Copying queries to save time Viewing query dependencies Setting a default load behavior Preventing automatic data type changes Avoiding Power Query Performance Issues Using views instead of tables Letting your back-end database servers do some crunching Upgrading to 64-bit Excel Disabling privacy settings to improve performance Disabling relationship detection Part VI Automating Excel Chapter 39 Introducing Visual Basic for Applications Introducing VBA Macros Displaying the Developer Tab Learning about Macro Security Saving Workbooks That Contain Macros Looking at 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 Testing the macro Editing the macro Relative versus absolute recording Another example Running the macro Examining the macro Rerecording the macro Testing the macro More about recording VBA macros 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 The Excel object model Objects and collections Properties Methods The Range object Variables Controlling execution A macro that can’t be recorded Learning More Chapter 40 Creating Custom Worksheet Functions Introducing VBA Functions Seeing a Simple Example Creating a custom function Using the function in a worksheet Analyzing the custom function Learning about Function Procedures What a Function Can’t Do Executing Function Procedures Calling custom functions from a procedure Using custom functions in a worksheet formula Using Function Procedure Arguments Creating a function with no arguments Creating a function with one argument Creating another function with one argument Creating a function with two arguments Creating a function with a range argument Creating a simple but useful function Debugging Custom Functions Inserting Custom Functions Learning More Chapter 41 Creating UserForms Understanding Why to Create UserForms Exploring UserForm Alternatives Using the InputBox function Using the MsgBox function Creating UserForms: An Overview Working with UserForms Adding controls Changing the properties of a control Handling events Displaying a UserForm Looking at a UserForm Example Creating the UserForm Testing the UserForm Creating an event handler procedure Looking at Another UserForm Example Creating the UserForm Creating event handler procedures Showing the UserForm Testing the UserForm Making the macro available from a worksheet button Making the macro available on your Quick Access Toolbar Enhancing UserForms Adding accelerator keys Controlling tab order Learning More Chapter 42 Using UserForm Controls in a Worksheet Understanding Why to Use Controls on a Worksheet Using Controls Adding a control Learning about Design mode Adjusting properties Using 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 Chapter 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 Special Application Events Using the OnTime event Using the OnKey event Chapter 44 Seeing Some 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 Creating a workbook 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 Chapter 45 Creating Custom Excel Add-Ins Understanding Add-Ins Working with Add-Ins Understanding When to Create Add-Ins Creating Add-Ins Looking at an Add-In Example Learning about Module1 Learning 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 Index EULA