27,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2021
Excel-erate your productivity with the only guide you'll need to the latest versions of Microsoft Excel Microsoft Excel offers unsurpassed functionality and accessibility for data exploration and analysis to millions of users around the world. And learning to unlock its full potential is easier than you can imagine with help from Excel All-in-One For Dummies. Follow along with Excel expert and veteran author Paul McFedries as he walks you through every feature and technique you need to know to get the most out of this powerful software. You'll learn how to design worksheets, use formulas and functions, collaborate with colleagues and review their work, create charts and graphics, manage and analyze data, and create macros. Plus, you'll discover all the capabilities Microsoft has included in the newest versions of Excel, including dark mode and accessibility features. This indispensable reference allows you to: * Get a firm grasp of Excel basics with the book's step-by-step guides before moving on to more advanced topics, like data analysis * Access up-to-date information on all the new versions of Excel, including the ones bundled with Microsoft 365, Office 2021, and the LTSC/Enterprise Edition * Enjoy the convenience of a single, comprehensive resource detailing everything you need to know about Excel Perfect for people coming to Excel for the very first time, Excel All-in-One For Dummies, Office 2021 Edition is also a must-read resource for anyone looking for a refresher on foundational or advanced Excel techniques.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1335
Ähnliche
Excel® All-in-One For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2022 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2021948861
ISBN: 978-1-119-83072-6 (pbk); ISBN: 978-1-119-83075-7 (ePDF); ISBN: 978-1-119-83074-0 (ePub)
Excel® All-in-One For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel All-in-One For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Title Page
Copyright
Introduction
About This Book
Foolish Assumptions
Conventions Used in This Book
Icons Used in This Book
Beyond the Book
Where to Go from Here
Book 1: Excel Basics
Chapter 1: Excel: The 50-Cent Tour
A Bird's-Eye View of Excel
Checking Out Excel’s Start Screen
Getting Comfy with the Excel User Interface
Getting Help
Launching and Quitting Excel
Chapter 2: Customizing Excel
Tailoring the Quick Access Toolbar to Your Tastes
Exercising Your Options
Using Office Add-ins
Using Excel’s Own Add-ins
Book 2: Worksheet Design
Chapter 1: Building Worksheets
Creating Fresh Workbooks
Getting to Know Excel's Data Types
Data Entry 101
Linking to Other Documents
Saving the Data
Document Recovery to the Rescue
Chapter 2: Formatting Worksheets
Selecting Cells and Ranges
Adjusting Columns and Rows
Formatting Tables from the Ribbon
Formatting Tables with the Quick Analysis Tool
Formatting Cells from the Ribbon
Formatting Cell Ranges with the Mini-Toolbar
Using the Format Cells Dialog Box
Hiring Out the Format Painter
Easier Formatting with Cell Styles
Analyzing Data with Conditional Formatting
Chapter 3: Editing and Proofing Worksheets
Opening a Workbook
Editing a Cell
A Worksheet with a View
Copying and Moving Stuff Around
Finding and Replacing Worksheet Data
Getting It Right: Spell Checking Your Worksheets
Looking Up and Translating Stuff
Marking Invalid Data
Chapter 4: Managing Worksheets
Reorganizing the Worksheet
Reorganizing the Workbook
Working with Multiple Workbooks
Consolidating Worksheets
Chapter 5: Printing Worksheets
Printing from the Backstage View
Quick Printing the Worksheet
Working with the Page Setup Options
Headers and Footers
Solving Page Break Problems
Printing the Formulas in a Report
Book 3: Formulas and Functions
Chapter 1: Building Basic Formulas
Getting Started with Formulas
Augmenting Formulas with Worksheet Functions
Copying Formulas
Adding Array Formulas
Naming Cells and Ranges
Adding Linking Formulas
Controlling Formula Recalculation
Circular References
Chapter 2: Logical Functions and Error Trapping
Understanding Error Values
Using Logical Functions
Error-Trapping Formulas
Whiting-Out Errors with Conditional Formatting
Checking It Twice: Auditing Your Formulas
Removing Errors from the Printout
Chapter 3: Date and Time Formulas
Understanding Dates and Times
Using Date Functions
Using Time Functions
Chapter 4: Finagling Financial Formulas
Some Financial Function Basics Before You Begin
The PV, NPV, and FV Functions
The PMT Function
Depreciation Functions
Excel's Advanced Financial Functions
Chapter 5: Math and Statistical Formulas
Math & Trig Functions
Statistical Functions
Chapter 6: Lookup, Information, and Text Formulas
Looking Up Stuff with the Lookup Functions
Getting Cell Reference Info with the Reference Functions
Interrogating Excel
Working with Text Functions and Formulas
Book 4: Worksheet Collaboration and Review
Chapter 1: Protecting Workbooks and Worksheet Data
Password-Protecting the Workbook
Setting Up Worksheet Data Protection
Protecting the Workbook Structure
Chapter 2: Preparing a Workbook for Distribution
Getting Your Workbook Ready for Review
Annotating Workbooks
Chapter 3: Sharing Workbooks and Worksheet Data
Sharing Your Workbooks Online
Sharing Your Workbooks via Email
Sharing Excel Data with Office Programs
Making Shared Workbooks Accessible
Exporting Workbooks to Other Usable File Formats
Collaborating
Conversationally with Comments
Book 5: Charts and Graphics
Chapter 1: Charting Worksheet Data
Worksheet Charting 101
Adding Sparkline Graphics to a Worksheet
Adding Infographics to a Worksheet
Printing Charts
Chapter 2: Adding Graphic Objects
Going Graphical: The Basics of Working with Graphic Objects
Inserting Different Types of Graphics
Making Changes to Graphics
Drawing Graphics
Adding Screenshots of the Windows Desktop
Using Themes
Book 6: Data Management
Chapter 1: Building and Maintaining Tables
Table Basics
Sorting Table Data
Subtotaling Table Data
Chapter 2: Filtering and Querying a Table
Filtering Data
Using the Database Functions
Querying External Data
Book 7: Data Analysis
Chapter 1: Performing What-If Analysis
Using Data Tables
Exploring Different Scenarios
Hide and Goal Seeking
Using Solver
Chapter 2: Performing Large-Scale Data Analysis
Creating PivotTables
Formatting a PivotTable
Sorting and Filtering the PivotTable Data
Modifying the PivotTable
Creating PivotCharts
Using the Power Pivot Add-in
Using the 3D Maps Feature
Creating Forecast Worksheets
Book 8: Macros and VBA
Chapter 1: Recording and Running Macros
Macro Basics
Assigning Macros to the Ribbon and the Quick Access Toolbar
Macro Security
Chapter 2: VBA Programming
Using the Visual Basic Editor
Creating Custom Excel Functions
Index
About the Authors
Connect with Dummies
End User License Agreement
List of Tables
Book 1 Chapter 1
TABLE 1-1 Keystrokes for Moving the Cell Pointer
Book 1 Chapter 2
TABLE 2-1 Advanced Excel Options
Book 2 Chapter 1
TABLE 1-1 Common Ways to Enter Dates and Times
TABLE 1-2 Keys Used in Completing Data Entry
TABLE 1-3 Keystrokes for Moving Within a Selection
Book 2 Chapter 2
TABLE 2-1 The Formatting Command Buttons in the Home Tab's Font, Alignment, and ...
Book 2 Chapter 3
TABLE 3-1 The Paste Special Dialog Box Options
Book 2 Chapter 4
TABLE 4-1 Outline Buttons
Book 3 Chapter 1
TABLE 1-1 The Different Types of Operators in Excel
TABLE 1-2 Natural Order of Operator Precedence in Formulas
TABLE 1-3 The Calculation Options in Excel
Book 3 Chapter 2
TABLE 2-1 Error Values in Excel
Book 3 Chapter 4
TABLE 4-1 Excel's Advanced Financial Functions
Book 3 Chapter 6
TABLE 6-1 The CELL Functions
info_type
Arguments
TABLE 6-2 Text Values Returned by the “format”
info_type
Book 5 Chapter 1
TABLE 1-1 Parts of a Typical Chart
TABLE 1-2 Format Tab Options
Book 6 Chapter 1
TABLE 1-1 Techniques for Navigating the Data Form
Book 6 Chapter 2
TABLE 2-1 The Comparative Operators in the Selection Criteria
TABLE 2-2 The Database Functions in Excel
Book 7 Chapter 1
TABLE 1-1 Solver Option Settings
List of Illustrations
Book 1 Chapter 1
FIGURE 1-1: The Excel Start screen with the Home tab selected that appears imme...
FIGURE 1-2: The Excel program window as it appears after first opening a blank ...
FIGURE 1-3: The Excel Backstage view displaying the Info screen with permission...
FIGURE 1-4: Excel’s Ribbon consists of a series of tabs containing command butt...
FIGURE 1-5: When you select a Ribbon tab by pressing Alt and the KeyTip letter ...
FIGURE 1-6: The Excel Status bar.
FIGURE 1-7: The Excel Help task pane.
FIGURE 1-8: Launching Excel from the Windows Start screen.
Book 1 Chapter 2
FIGURE 2-1: Click Customize Quick Access Toolbar to see a few useful commands t...
FIGURE 2-2: Use the buttons on the Quick Access Toolbar tab of the Excel Option...
FIGURE 2-3: The General tab’s options enable you to change many global Excel se...
FIGURE 2-4: The Formulas tab’s options enable you to change how Excel works wit...
FIGURE 2-5: The Proofing tab’s options enable you to change AutoCorrect and spe...
FIGURE 2-6: The Save tab’s options enable you to change the automatic save and ...
FIGURE 2-7: The Editing and Cut, Copy, and Paste options on the Advanced tab co...
FIGURE 2-8: The display options in the center of the Advanced tab control what’...
FIGURE 2-9: The Advanced tab options control calculation, general, and 1-2-3 co...
FIGURE 2-10: The Customize Ribbon tab options enable you to control which tabs ...
FIGURE 2-11: Inserting the Excel Mini Calendar and Date Picker add-in into an E...
FIGURE 2-12: Activating built-in Excel Add-ins in the Add-Ins dialog box.
Book 2 Chapter 1
FIGURE 1-1: The New screen is home to a fistful of templates you can use to get...
FIGURE 1-2: Searching for finance-related templates.
FIGURE 1-3: When you click a template thumbnail, Excel displays more details ab...
FIGURE 1-4: The downloaded template now appears in the New screen.
FIGURE 1-5: Click the alert indicator to open the menu attached to the Number S...
FIGURE 1-6: One way to complete a cell entry is to click the Enter button in th...
FIGURE 1-7: A Windows touchscreen after displaying and undocking the standard T...
FIGURE 1-8: You can add your own automated replacements to the AutoCorrect tab.
FIGURE 1-9: Dragging the Fill handle to fill in a series with the twelve months...
FIGURE 1-10: The series of monthly column headings with the AutoFill Options dr...
FIGURE 1-11: Some series created with AutoFill by using custom increments.
FIGURE 1-12: Creating a custom list of cities for AutoFill.
FIGURE 1-13: New data table containing full names that need to be split up in s...
FIGURE 1-14: Creating a custom drop-down list in the Data Validation dialog box...
FIGURE 1-15: Selecting a city from the custom drop-down list.
FIGURE 1-16: Getting an error message after trying to input a city that’s not o...
FIGURE 1-17: Click the lower half of the Link button to see a list of recent do...
FIGURE 1-18: Use the Insert Hyperlink dialog box to insert a link in a workshee...
FIGURE 1-19: The Excel title bar comes with a Save icon.
FIGURE 1-20: Use the Save As screen to specify where you want to save a new wor...
FIGURE 1-21: Specifying the folder and filename prior to saving the new workboo...
Book 2 Chapter 2
FIGURE 2-1: Worksheet with a nonadjacent cell selection made up of several diff...
FIGURE 2-2: Selecting the cells in the first column of the table with AutoSelec...
FIGURE 2-3: Selecting all the remaining columns of the table with AutoSelect. (...
FIGURE 2-4: Selecting a cell range with the Go To dialog box.
FIGURE 2-5: Selecting a format for the new table in the Table Styles gallery.
FIGURE 2-6: Indicating the range of the table in the Create Table dialog box af...
FIGURE 2-7: After you select an initial table format, the Table Design tab appe...
FIGURE 2-8: Previewing the selected data formatted as a table with the Quick An...
FIGURE 2-9: Right-click your cell selection to display its shortcut menu along ...
FIGURE 2-10: Options for customizing the formatting assigned by the Currency nu...
FIGURE 2-11: You can create your own number format using the Custom category in...
FIGURE 2-12: Worksheet with long text entries that spill over into blank cells ...
FIGURE 2-13: Worksheet after wrapping long text entries in their cells, increas...
FIGURE 2-14: Worksheet after rotating a table’s column headings up 90 degrees. ...
FIGURE 2-15: You can assign new fonts, font sizes, attributes, and colors on th...
FIGURE 2-16: The border options available on the Home tab’s Borders button’s dr...
FIGURE 2-17: The border options available on the Border tab of the Format Cells...
FIGURE 2-18: Using the options on the Fill tab to select a new fill color, grad...
FIGURE 2-19: Selecting a new gradient for your cell selection in the Fill Effec...
FIGURE 2-20: You can apply multiple styles to a cell selection using the Cell S...
FIGURE 2-21: The initial Data Bars gradient fill applied to some worksheet data...
FIGURE 2-22: You can use the Quick Analysis tool’s Formatting option to apply c...
FIGURE 2-23: Define a new conditional formatting rule using the options in the ...
Book 2 Chapter 3
FIGURE 3-1: Use the Open screen to find and open a workbook for editing.
FIGURE 3-2: Select a location to see its folders.
FIGURE 3-3: You can use the Open dialog box to select the workbook file you wan...
FIGURE 3-4: When you select Delete Cells, Excel asks how you want the remaining...
FIGURE 3-5: Worksheet after deleting the cell with the duplicate entry. (Sample...
FIGURE 3-6: Inserting three blank cells for missing entries in two columns of a...
FIGURE 3-7: The worksheet table after entering the missing entries in the newly...
FIGURE 3-8: The Income Analysis worksheet after zooming out to a 50% magnificat...
FIGURE 3-9: Worksheet at 232% magnification after zooming in on the cell range ...
FIGURE 3-10: The income worksheet after freezing column A and rows 1:2 in the w...
FIGURE 3-11: The income worksheet after zooming in on the cell range J20:L25 af...
FIGURE 3-12: The custom views you create appear in the Custom Views dialog box.
FIGURE 3-13: The Paste Option button’s palette with the option buttons that com...
FIGURE 3-14: The paste options in the Paste Special dialog box give you control...
FIGURE 3-15: Transposing a copy of the production schedule table so that dates ...
FIGURE 3-16: Finding a value in a worksheet by using the options on the Find ta...
FIGURE 3-17: Updating dates in a worksheet using the Find and Replace feature.
FIGURE 3-18: Spell checking a worksheet with the Spelling dialog box.
FIGURE 3-19: Using Data Validation to mark entries below a specified threshold ...
Book 2 Chapter 4
FIGURE 4-1: The Regional Income worksheet with the window divided into two hori...
FIGURE 4-2: The Regional Income worksheet with the window divided into two vert...
FIGURE 4-3: Splitting the worksheet window into four panes: two horizontal and ...
FIGURE 4-4: Automatic outline applied to the CG Music sales data with three lev...
FIGURE 4-5: The outline after applying automatic styles with the Settings dialo...
FIGURE 4-6: Collapsed worksheet outline showing first- and secondary-level summ...
FIGURE 4-7: Totally collapsed worksheet outline showing only the first-level su...
FIGURE 4-8: Worksheet outline expanded to show only details for Download sales ...
FIGURE 4-9: Manually adjusting the level 1 rows in the worksheet outline. (Samp...
FIGURE 4-10: Collapsing the adjusted worksheet outline to the first level summa...
FIGURE 4-11: Use Excel's View Side by Side command to compare data stored on tw...
FIGURE 4-12: The Workbook Statistics command tells you a few vital statistics f...
FIGURE 4-13: Using the Consolidate dialog box to total sales data for three yea...
FIGURE 4-14: The Consolidated worksheet after having Excel total sales from the...
Book 2 Chapter 5
FIGURE 5-1: You can use the Print screen in the Backstage view to customize you...
FIGURE 5-2: Viewing a worksheet in Page Layout view. (Sample workbook: Income A...
FIGURE 5-3: Page 1 of a ten-page report in Print Preview. (Sample workbook: Inc...
FIGURE 5-4: Page 1 of a ten-page report after selecting the Zoom to Page button...
FIGURE 5-5: You can adjust your report margins from the Margins tab in the Page...
FIGURE 5-6: Drag a marker to adjust its margin in the Page Preview window when ...
FIGURE 5-7: A landscape mode report in Page Layout view. (Sample workbook: Inco...
FIGURE 5-8: Specify the rows and columns to use as print titles on the Sheet ta...
FIGURE 5-9: Page 2 of a sample report in Print Preview with defined print title...
FIGURE 5-10: Clicking inside the header (or footer) displays the Header & Foote...
FIGURE 5-11: The first page of a report in Page Layout view shows you how the h...
FIGURE 5-12: A worksheet in Page Layout view showing the custom footer. (Sample...
FIGURE 5-13: Preview page breaks in a report in Page Break Preview. (Sample wor...
FIGURE 5-14: Page 1 of the report in the Print Preview window after adjusting t...
Book 3 Chapter 1
FIGURE 1-1: The formula result appears in the cell, while the formula itself ap...
FIGURE 1-2: You can use cell addresses as formula operands.
FIGURE 1-3: Using AutoSum to create a SUM formula that totals a column of numbe...
FIGURE 1-4: Using the AutoSum feature to create a SUM formula that totals a row...
FIGURE 1-5: Adding a row of totals with the Quick Analysis tool’s Sum button. (...
FIGURE 1-6: Use the Insert Function dialog box to, you know, insert a function.
FIGURE 1-7: Use the Function Arguments dialog box to specify the arguments for ...
FIGURE 1-8: Type the function name followed by the left parenthesis to see a Sc...
FIGURE 1-9: The Function Arguments dialog box shows the function syntax.
FIGURE 1-10: An original formula copied with the Fill handle across the last ro...
FIGURE 1-11: An original formula copied with the Fill handle down the last colu...
FIGURE 1-12: Copying the formula in cell B9 across produces #DIV/0! errors in t...
FIGURE 1-13: Using an absolute cell reference in the divisor gets rid of the er...
FIGURE 1-14: A worksheet with two different sizes of arrays.
FIGURE 1-15: The goal: to calculate each employee's hourly wages for the first ...
FIGURE 1-16: An array formula makes short work of hourly wage calculation. (Sam...
FIGURE 1-17: You can define a name using the Formula bar's Name box. (Sample wo...
FIGURE 1-18: You can use the New Name dialog box to define a new name.
FIGURE 1-19: In the Use in Formula list, click the name you want to paste into ...
FIGURE 1-20: Creating names from the row and column headings in a worksheet dat...
FIGURE 1-21: The Name Manager lists all names defined in the workbook.
FIGURE 1-22: Income statement with a resolvable circular reference.
Book 3 Chapter 2
FIGURE 2-1: Using the IF function to determine whether to charge tax on an item...
FIGURE 2-2: Using the IFS function to test multiple conditions in a single form...
FIGURE 2-3: Using the SWITCH function to match days of the week against a serie...
FIGURE 2-4: Blank 2023 Production Schedule spreadsheet that’s full of #DIV/0! e...
FIGURE 2-5: 2023 Production Schedule spreadsheet after trapping all the #DIV/0!...
FIGURE 2-6: Clicking the Trace Precedents command button shows the direct prece...
FIGURE 2-7: Clicking the Trace Precedents command button again shows the indire...
FIGURE 2-8: Clicking the Trace Precedents command button a third time shows a p...
FIGURE 2-9: Double-clicking the dotted tracer arrow opens the Go To dialog box ...
FIGURE 2-10: Clicking the Trace Dependents command button shows all the depende...
FIGURE 2-11: Flagging an error value in a worksheet in the Error Checking dialo...
FIGURE 2-12: Using the Trace Error option to show the precedents and dependents...
FIGURE 2-13: Calculating each part of a formula in the Evaluate Formula dialog ...
Book 3 Chapter 3
FIGURE 3-1: Using the DATE function to combine separate date information into a...
FIGURE 3-2: Using the NETWORKDAYS function to find the number of workdays betwe...
Book 3 Chapter 4
FIGURE 4-1: Using the PV function to calculate the present value of various inv...
FIGURE 4-2: Using the NPV function to calculate the net present value of an inv...
FIGURE 4-3: Loan Payments table using the PMT function to calculate various loa...
FIGURE 4-4: A Depreciation table showing 10-year depreciation of an asset using...
Book 3 Chapter 5
FIGURE 5-1: Rounding off the value of pi with the ROUND, ROUNDUP, and ROUNDDOWN...
FIGURE 5-2: Using SUMIF to total sales by items sold. (Sample workbook: Chris C...
FIGURE 5-3: Using SUMIFS to total sales by location as well as the items sold. ...
FIGURE 5-4: Home sales worksheet using common statistical functions. (Sample wo...
FIGURE 5-5: A version of the home sales worksheet with various counting formula...
FIGURE 5-6: Selecting a statistical analysis tool added by the Analysis ToolPak...
Book 3 Chapter 6
FIGURE 6-1: The XLOOKUP formula in cell F4 returns sales based on the country e...
FIGURE 6-2: The XLOOKUP formula in cell F4 returns the country based on the sal...
FIGURE 6-3: A formula with nested XLOOKUP functions to return the number of uni...
FIGURE 6-4: Using the VLOOKUP function to return the amount of the tip to add f...
FIGURE 6-5: Using the HLOOKUP function to return the price of a bakery item fro...
FIGURE 6-6: Doing a two-way lookup in the Production Schedule table. (Sample wo...
FIGURE 6-7: Using the TRANSPOSE function to change the orientation of a simple ...
FIGURE 6-8: Using the PROPER function to convert names in all uppercase letters...
FIGURE 6-9: Worksheet after replacing names in all uppercase letters with prope...
FIGURE 6-10: Using the TEXTJOIN function to create a CSV file for export where ...
FIGURE 6-11: Worksheet after concatenating the first and last names in column C...
Book 4 Chapter 1
FIGURE 1-1: Setting a password to open and modify the workbook in the General O...
FIGURE 1-2: Entering the password required to open a protected workbook file.
FIGURE 1-3: Use the Protect Sheet dialog box to select your worksheet protectio...
FIGURE 1-4: Designating the range to be unlocked by a password in a protected w...
FIGURE 1-5: Assigning the range title, address, and password in the New Range d...
FIGURE 1-6: Setting the permissions for each user in the Permissions dialog box...
FIGURE 1-7: Getting ready to protect the sheet in the Allow Users to Edit Range...
FIGURE 1-8: Using the Tab key to move from unlocked cell to unlocked cell in a ...
FIGURE 1-9: Protecting a workbook in the Protect Structure and Windows dialog b...
Book 4 Chapter 2
FIGURE 2-1: Checking a workbook’s properties in its Info screen in the Excel Ba...
FIGURE 2-2: Filling out the signing information in the Signature Setup dialog b...
FIGURE 2-3: Filling in the signature in the Sign dialog box.
FIGURE 2-4: The workbook after adding the digital signature line graphic with t...
FIGURE 2-5: Adding notes to various cells of a worksheet. (Sample workbook: Inc...
FIGURE 2-6: Adding inked notes to a worksheet with my finger on a Windows table...
Book 4 Chapter 3
FIGURE 3-1: Use the Send Link dialog box to invite coworkers or clients to shar...
FIGURE 3-2: Use the Link Settings dialog box to customize the link you're shari...
FIGURE 3-3: When you click Copy Link, Excel copies the link address to the Clip...
FIGURE 3-4: Dragging the cell range A1:B23 from the Historical Sales worksheet ...
FIGURE 3-5: Word memo after copying the worksheet data.
FIGURE 3-6: With the copy-and-paste method, use the Paste Special dialog box to...
FIGURE 3-7: Editing the embedded worksheet sales data from within the Word memo...
FIGURE 3-8: To link data, select the Paste Link option in Word’s Paste Special ...
FIGURE 3-9: A linked chart pasted into the Word memo.
FIGURE 3-10: Editing an embedded Excel chart in a PowerPoint slide.
FIGURE 3-11: Use the Alt Text pane to compose your alternative text for a visua...
FIGURE 3-12: If the Accessibility Checker discovers one or more ways you can ma...
FIGURE 3-13: Use this dialog box to type and post your comment.
FIGURE 3-14: Use this dialog box to type and post a comment reply.
Book 5 Chapter 1
FIGURE 1-1: A typical clustered column chart containing a variety of standard c...
FIGURE 1-2: Insert Chart dialog box with the Recommended Charts tab selected. (...
FIGURE 1-3: Open the Insert Chart dialog box and then use the All Charts tab to...
FIGURE 1-4: Previewing the embedded chart to insert from the Quick Analysis too...
FIGURE 1-5: Clustered column chart created on its own chart sheet. (Sample work...
FIGURE 1-6: Clustered column chart on its own chart sheet after selecting a new...
FIGURE 1-7: The clustered column chart after switching the columns and rows. (S...
FIGURE 1-8: Using the Select Data Source dialog box to remove the empty Genre l...
FIGURE 1-9: Repositioning the chart legend in the example clustered column usin...
FIGURE 1-10: Clustered column chart on its own Chart sheet with data table and ...
FIGURE 1-11: Formatting the Chart Title with the options in the Format Chart Ti...
FIGURE 1-12: Formatting the Vertical (Value) Axis with the options in the Forma...
FIGURE 1-13: Sparklines graphics representing the variation in the data in a wo...
FIGURE 1-14: Adding a Bing Maps infographic to illustrate the 2021 population b...
FIGURE 1-15: Adding a People Graph infographic to illustrate the 2021 populatio...
FIGURE 1-16: Adding a Filled Map chart infographic to illustrate the 2021 popul...
Book 5 Chapter 2
FIGURE 2-1: When you click a graphic object to select it, the rotation and circ...
FIGURE 2-2: Graphic objects on top obscure any worksheet data and parts of othe...
FIGURE 2-3: The worksheet after moving the hundred-dollar bill graphic to the t...
FIGURE 2-4: Grouping the hundred-dollar bill photo and its caption to turn them...
FIGURE 2-5: Selecting a web image to download into your worksheet.
FIGURE 2-6: Selecting an online 3-D model to insert into your worksheet.
FIGURE 2-7: Selecting a local image to insert into your worksheet.
FIGURE 2-8: Use this box to select from a large collection of ready-to-use imag...
FIGURE 2-9: Selecting the text style for the new WordArt text from its drop-dow...
FIGURE 2-10: Select the SmartArt list, diagram, or picture to insert in the wor...
FIGURE 2-11: Adding text for a new organizational chart in the SmartArt text pa...
Book 6 Chapter 1
FIGURE 1-1: Creating an employee table with the row of field names and first da...
FIGURE 1-2: Opening the data form in the new table to add a new record. (Sample...
FIGURE 1-3: Using the Remove Duplicates dialog box to remove duplicate records ...
FIGURE 1-4: Set up to sort records alphabetically by location and salary. (Samp...
FIGURE 1-5: Employee table sorted by location and salary. (Sample workbook: Emp...
FIGURE 1-6: The Sort dialog box with five levels of sorting keys for sorting th...
FIGURE 1-7: The Personnel table after sorting by department, supervisor, last, ...
FIGURE 1-8: Using the Subtotal dialog box to subtotal the salaries for each dep...
FIGURE 1-9: Bottom of the table showing the subtotals and grand total for depar...
Book 6 Chapter 2
FIGURE 2-1: The employee table after filtering the Dept and Location fields. (S...
FIGURE 2-2: Using the Top 10 Items AutoFilter to filter out all records except ...
FIGURE 2-3: Using Custom AutoFilter to filter out records except for those with...
FIGURE 2-4: Using Advanced Filter to copy records that meet the criteria in the...
FIGURE 2-5: Copied records where the location is Boston and the date hired is p...
FIGURE 2-6: Copied records where the location is Boston and the date hired prio...
FIGURE 2-7: Copied records extracted from the table for employees whose salarie...
FIGURE 2-8: Using DSUM to total the salaries over $55,000 in the Employee table...
FIGURE 2-9: Using the Navigator to select which data tables and queries from th...
FIGURE 2-10: Customers worksheet with the data imported from the Access Custome...
FIGURE 2-11: Orders worksheet with the data imported from the Orders data table...
FIGURE 2-12: Importing the data in a tab-delimited text file into a worksheet u...
FIGURE 2-13: Creating a new query in the Power Query Editor using a data table ...
FIGURE 2-14: Setting the filtering and sorting criteria for the new data query ...
FIGURE 2-15: The Contoso Stores data queried in the Power Query Editor after lo...
Book 7 Chapter 1
FIGURE 1-1: Creating a one-variable data table. (Sample workbook: Data Table 1 ...
FIGURE 1-2: The completed one-variable data table. (Sample workbook: Data Table...
FIGURE 1-3: Creating a two-variable data table. (Sample workbook: Data Table 2 ...
FIGURE 1-4: The completed two-variable data table. (Sample workbook: Data Table...
FIGURE 1-5: Adding a new Most Likely scenario for the sales forecast. (Sample w...
FIGURE 1-6: Specifying the changing values in the Scenario Values dialog box. (...
FIGURE 1-7: Worksheet after showing the Worst Case scenario. (Sample workbook: ...
FIGURE 1-8: Designating the result cells in the Scenario Summary dialog box. (S...
FIGURE 1-9: A Scenario Summary report for the scenarios in the Sales Forecast t...
FIGURE 1-10: Using goal seeking to find out how much sales must increase to rea...
FIGURE 1-11: A worksheet showing a goal-seeking solution and the Goal Seek Stat...
FIGURE 1-12: Specifying the parameters to apply to the model in Solver Paramete...
FIGURE 1-13: The Solver Results dialog box showing that Solver found a solution...
FIGURE 1-14: Modifying the solution options in the Options dialog box.
Book 7 Chapter 2
FIGURE 2-1: Previewing the PivotTable created from the selected data in the Qui...
FIGURE 2-2: Previewed PivotTable created on a new worksheet with the Quick Anal...
FIGURE 2-3: Creating a new PivotTable from the sample PivotTables displayed in ...
FIGURE 2-4: Indicate the data source and PivotTable location in the Create Pivo...
FIGURE 2-5: A new PivotTable displaying the blank table grid and the PivotTable...
FIGURE 2-6: A completed PivotTable after adding the fields from the employee ta...
FIGURE 2-7: The revised PivotTable in the Outline Form with an extra blank row ...
FIGURE 2-8: The PivotTable after filtering the Gender Filter Report field and t...
FIGURE 2-9: Employee PivotTable showing the men’s salaries in the Engineering d...
FIGURE 2-10: Employee PivotTable using a timeline filter to show the salaries f...
FIGURE 2-11: Pivoting the table so that Dept is now the Column Labels Field and...
FIGURE 2-12: The PivotTable after adding Profit Sharing as another Report Filte...
FIGURE 2-13: Creating a calculated field for a PivotTable.
FIGURE 2-14: Modifying the PivotTable options in the PivotTable Options dialog ...
FIGURE 2-15: Clustered column PivotChart moved to its own PivotChart sheet. (Sa...
FIGURE 2-16: The Power Pivot window with tabs for all the data tables imported ...
FIGURE 2-17: Switching from Data View to Diagram View in the Power Pivot for Ex...
FIGURE 2-18: 3D Maps window with new Clustered Column 3-D animation for visuali...
FIGURE 2-19: Defining the options for a new line chart that shows historical an...
FIGURE 2-20: Worksheet with embedded line chart depicting historical and foreca...
Book 8 Chapter 1
FIGURE 1-1: Getting ready to record the CompanyName macro in the Record Macro d...
FIGURE 1-2: Selecting the macro to run in the Macro dialog box.
Book 8 Chapter 2
FIGURE 2-1: The Visual Basic Editor window as it normally appears when editing ...
FIGURE 2-2: The buttons on the Visual Basic Editor’s Standard toolbar.
FIGURE 2-3: Using the Find feature to locate the code to edit in the Code windo...
FIGURE 2-4: The CompanyName Code window after adding variables and the InputBox...
FIGURE 2-5: The Worksheet Title dialog box that appears when you run the Compan...
FIGURE 2-6: Entering the Commission user-defined function in the Personal Macro...
FIGURE 2-7: Adding a description for the Commission user-defined function.
FIGURE 2-8: Selecting a cell in the RV Sales worksheet into which to enter the ...
FIGURE 2-9: Specifying the TotalSales and ItemsSold arguments in the RV Sales w...
FIGURE 2-10: Completed worksheet for all salespeople computed with the Commissi...
FIGURE 2-11: Protecting the VBA project so that its user-defined functions can’...
FIGURE 2-12: Activating the new add-in file in the Add-Ins dialog box.
Guide
Cover
Title Page
Copyright
Table of Contents
Begin Reading
Index
About the Authors
Pages
iii
iv
1
2
3
4
5
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
461
462
463
464
465
466
467
468
469
470
471
472
473
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
765
766
767
Introduction
Excel All-in-One For Dummies brings together plain and simple information on using all aspects of Microsoft Excel. It’s designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for all sorts of computing devices running Windows (desktops, laptops, tablets, and even smartphones), Excel offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would want to go to all the trouble.
The truth is that understanding how to use the abundance of features offered by Excel is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work; in other words, “what’s in it for you.” I have endeavored to cover both the “how to” and “so what” aspects in all my discussions of Excel features, being as clear as possible and using a minimum of tech-speak.
Fortunately, Excel is well worth the effort to get to know because it’s definitely one of the best data-processing and analysis tools that has ever come along. From let-Excel-do-all-the-work features such as Flash Fill and Analyze Data to eyebrow-raisingly powerful tools such as PivotTables and Solver, Excel has whatever you need to get your numbers crunched. Excel is a blast to use when you know what you’re doing, and my great hope is that this “fun” aspect of using the program comes through on every page (or, at least, every other page).
About This Book
As the name states, Excel All-in-One For Dummies is a reference (whether you keep it on your desk or use it to prop up your desk is your business). This means that although the chapters in each book are laid out in a logical order, each stands on its own, ready for you to dig into the information at any point.
As much as possible, I have endeavored to make the topics within each book and chapter stand on their own. When there’s just no way around relying on some information that’s discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you’re of a mind to.
Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You’ll find that although most topics are introduced in a conversational manner, I don’t waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).
Foolish Assumptions
I’m only going to make one foolish assumption about you, and that is that you have some need to use Microsoft Excel under Windows in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.
As far as your hardware and software go, I’m assuming that you already have Excel installed on your computing device running under Windows. And it doesn't matter whether you obtained Excel with a Microsoft 365 subscription or an Office 2021 purchase; this book covers the core functionality of both flavors of Excel.
I’m not assuming, however, that when you’re using Excel that you’re sitting in front of a large screen monitor and making cell entries and command selections with a physical keyboard or connected mouse. With the introduction of Microsoft’s Surface 4 tablet and the support for a whole slew of different Windows tablets, you may well be entering data and selecting commands with your finger or stylus using the Windows Touch keyboard and Touch pointer.
To deal with the differences between using Excel on a standard desktop or laptop computer with access only to a physical keyboard and mouse and a touchscreen tablet or smartphone environment with access only to the virtual Touch keyboard, I’ve outlined the touchscreen equivalents to common commands you find throughout the text, such as “click,” “double-click,” “drag,” and so forth, in the section that explains selecting by touch in Book 1, Chapter 1.
Warning: Because of the diversity of the devices that Excel runs on and the places where its files can be saved and used, if you’re using Excel 2007 or Excel 2010 for Windows, much of the file-related information in this book may only confuse and confound you. If you’re still using a version prior to Excel 2007, which introduced the Ribbon interface, this edition will be of no use to you because your version of the program works nothing like what this book describes.
Conventions Used in This Book
This book follows a number of different conventions modeled primarily after those used by Microsoft in its various online articles and help materials. These conventions deal primarily with Ribbon command sequences and shortcut or hot key sequences that you encounter.
Excel is a sophisticated program that uses the Ribbon interface first introduced in Excel 2007. In Chapter 1, I explain all about this Ribbon interface and how to get comfortable with its command structure. Throughout the book, you may find Ribbon command sequences using the shorthand developed by Microsoft whereby the name on the tab on the Ribbon and the command button you select are separated by arrows, as in
Home ⇒ Copy
This is shorthand for the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it’s not already displayed) and then click the Copy button, which sports the traditional side-by-side page icon.
Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by arrows, as in
Formulas ⇒ Calculation Options ⇒ Manual
This is shorthand for the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it’s not already displayed) and then click the Calculation Options command button followed by the Manual drop-down menu option.
The book occasionally encourages you to type something specific into a specific cell in the worksheet. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, still have to press the Enter key or click the Enter button on the Formula bar to make the entry stick.
When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the Status bar at the bottom of the screen. This book renders messages that you see onscreen like this:
CALCULATE
This is the message that tells you that Excel is in manual recalculation mode (after using the earlier Ribbon command sequence) and that one or more of the formulas in your worksheet are not up to date and are in sore need of recalculation.
Occasionally I give you a shortcut key combination that you can press to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Shortcut key combinations are written like this: Alt+FS or Ctrl+S. (Both of these shortcut key combos save workbook changes.)
With the Alt key combos, you press the Alt key until the shortcut key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start pressing the shortcut key letters. (By the way, these are all lowercase shortcut key letters — I only put them in caps to make them stand out in the text.)
Shortcut key combos that use the Ctrl key are of an older vintage, and they work a little bit differently because, on a physical keyboard, you have to hold down the Ctrl key as you press the shortcut key letter. (Again, unless you see the Shift key in the sequence as in Ctrl+Shift+C, don’t worry about uppercase letters.)
Finally, if you're really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, option buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.
Icons Used in This Book
The following icons are strategically placed in the margins throughout all eight books in this volume. Their purpose is to get your attention, and each has its own way of doing that.
This icon denotes some really cool information (in my humble opinion) that will pay off by making your work a lot more enjoyable or productive (or both).
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you may end up taking a detour that wastes valuable time.
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you’ll be sorry. I reserve this icon for those times when you can lose data and otherwise screw up your spreadsheet.
This icon denotes a tidbit that makes free use of (oh no!) technical jargon. You may want to skip these sections (or, at least, read them when no one else is around).
Beyond the Book
Examples:
This book’s sample Excel workbooks can be found at
www.wiley.com/go/excel2022aiofd
or at my website (
www.paulmcfedries.com
). The filenames of the workbooks used in the book's examples are given in the text.
Cheat Sheet:
To locate this book's Cheat Sheet, go to
www.dummies.com
and search for
Excel All-in-One For Dummies.
See the Cheat Sheet for info on Excel shortcut keys, keyboard cell-selection techniques, setting regional dates, and sharing workbooks from OneDrive.
Where to Go from Here
The question of where to go from here couldn’t be simpler: Go to Book 1, Chapter 1 and find out what you’re dealing with. Which book you go to after that is a matter of personal interest and need. Just go for the gold and don’t forget to have some fun while you’re digging!
Book 1
Excel Basics
Contents at a Glance
Chapter 1: Excel: The 50-Cent Tour
A Bird's-Eye View of Excel
Checking Out Excel’s Start Screen
Getting Comfy with the Excel User Interface
Getting Help
Launching and Quitting Excel
Chapter 2: Customizing Excel
Tailoring the Quick Access Toolbar to Your Tastes
Exercising Your Options
Using Office Add-ins
Using Excel’s Own Add-ins
Chapter 1
Excel: The 50-Cent Tour
IN THIS CHAPTER
Getting to know Excel’s Start screen and program window
Selecting commands from the Ribbon
Unpinning the Ribbon
Using Excel on a touchscreen device
Getting around the worksheet and workbook
Using Excel’s Tell Me feature when you need help
Launching and quitting Excel
If you're new to Excel, launching the program for the first time can be headache-inducing. So many icons! So many lines! So little guidance! The problem is that you've been catapulted into what appears to be a strange and exotic landscape without a map, a guidebook, or even a “You are here” sign. You wouldn't travel to Upper Volta or lower Manhattan without these tools in your travel kit, so why are you visiting Excel that way?
Ah, but your purchase of this book shows that you are, indeed, a savvy and sophisticated traveler. Now you've got the guidebook you need to learn the language, customs, and tourist hotspots of Excel. And the map? That's what this chapter is all about. Here you take a guided tour that points out the main features of the Excel landscape; shows you the best ways to navigate from the spreadsheet equivalent of Point A to Point B; and tells you how to call for help should the need arise.
Oh, and by the way: You are here. Let's go!
A Bird's-Eye View of Excel
Excel relies primarily on the onscreen element called the Ribbon, which is how you select the vast majority of Excel commands. In addition, Excel offers a single toolbar (the Quick Access toolbar, which is hidden by default), some context-sensitive buttons and command bars in the form of the Quick Analysis tool and mini-bar, contextual tabs that only appear on the Ribbon when you select certain Excel elements (such as a table or image), along with a number of task panes (such as Clipboard, Research, Thesaurus, and Selection to name a few).
Among the features supported when selecting certain style and formatting commands is the Live Preview, which shows you how your actual worksheet data will appear in a particular font, table formatting, and so on before you actually apply it. Excel also supports an honest-to-goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet. Page Layout view has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. The Backstage view attached to the File tab on the Excel Ribbon enables you to get at-a-glance information about your spreadsheet files as well as save, share, preview, and print them. Last but not least, Excel is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially with the program’s Live Preview.
