Microsoft 365 Excel All-in-One For Dummies E-Book

David H. Ringstrom

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1066

Veröffentlichungsjahr: 2025

Beschreibung

Get into the nitty gritty of Excel, the all-important spreadsheet tool

Microsoft 365 Excel All-in-One For Dummies offers an all-new way to tackle data in Excel. Start with the basics of inputting, formatting, and organizing data and work your way to pro techniques that help with any career field or reason for using the app. Gain the skills to apply advanced formulas and functions, create stunning data visualizations, build dashboards and reports, and automate your spreadsheets. You'll also get a glimpse into how AI tools can boost your work. Eight mini-books come together in this expanded Excel reference. With Microsoft 365 Excel All-in-One For Dummies, you'll soon be the go-to Excel guru in your office or school.

• Learn the basics of Excel—organizing data, performing calculations, and formatting your spreadsheet.
• Use functions to handle advanced math and financial analysis.
• Create data visualizations and summaries to help tell your story
• Apply basic programming skills using VBA

This book is perfect for professionals or students who need to up their Excel game with insight from pros who know the classic spreadsheet app inside and out.

Leseprobe

Microsoft® 365 Excel® All-in-One For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft® 365 Excel® All-in-One For Dummies Cheat Sheet” in the Search box.

Table of Contents

Cover

Table of Contents

Title Page

Copyright

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Book 1: Getting Started with Excel

Chapter 1: Getting Started with Spreadsheets

Exploring Excel’s User Interface

Entering and Editing Data

Handling Workbook Operations

Managing Worksheet Tasks

Collaborating with Others

Chapter 2: Carrying Out Basic Calculations

Understanding Operators

Exploring the Order of Operations

Contrasting Cell References

Replicating Formulas

Calculating Sums

Analyzing Data with Statistical Functions

Chapter 3: Formatting Cells and Worksheets

Applying Basic Formatting Commands

Exploring the Format Cells Dialog Box

Applying Cell Styles

Merging and Centering Cells

Wrapping Text

Tapping into Text Boxes

Inserting Images

Enhancing Accessibility

Adjusting Row Heights and Column Widths

Hiding and Unhiding Rows and Columns

Hiding and Unhiding Workbooks

Modifying Page Setup

Chapter 4: Organizing Data and Creating Tables

Organizing Data

Creating Tables

Chapter 5: Navigating Worksheets and Workbooks

Finding and Replacing Data within Cells

Exploring with the Navigation Task Pane

Jumping to Locations with Go To

Targeting Specific Cells with Go To Special

Navigating with the Name Box

Activating Worksheets

Reordering Worksheets

Splitting Worksheet Windows

Viewing Two or More Worksheets at Once

Applying Custom Views

Zooming In and Out

Book 2: Working with Formulas and Functions

Chapter 1: Tapping Into Formula and Function Fundamentals

Working with Excel Fundamentals

Gaining the Upper Hand on Formulas

Using Functions in Formulas

Chapter 2: Saving Time with Function Tools

Getting Familiar with the Insert Function Dialog Box

Finding the Correct Function

Entering Functions Using the Insert Function Dialog Box

Directly Entering Formulas and Functions

Chapter 3: Building Array Formulas and Functions

Discovering Arrays

Using Arrays in Formulas

Changing the Shape of Arrays

Working with Functions That Return Arrays

Chapter 4: Fixing Errors in Formulas

Catching Errors as You Enter Them

Auditing Formulas

Watching the Watch Window

Evaluating and Checking Errors

Making an Error Behave the Way You Want

Book 3: Going Farther with Functions

Chapter 1: Calculating Loans, Interest, and Depreciation

Understanding How Excel Handles Money

Figuring Loan Calculations

Looking into the Future

Depreciating the Finer Things in Life

Measuring Your Internals

Chapter 2: Performing Functional Math

Adding It All Together with the SUM Function

Rounding Out Your Knowledge

Leaving All Decimals Behind with INT

Leaving Some Decimals Behind with TRUNC

Looking for a Sign

Ignoring Signs

Using PI to Calculate Circumference and Diameter

Generating and Using Random Numbers

Creating a Sequence

Raising Numbers to New Heights

Multiplying Multiple Numbers

Summing Things Up

Chapter 3: Working with Date and Time Functions

Understanding How Excel Handles Dates

Formatting Dates

Making a Date with DATE

Breaking a Date with DAY, MONTH, and YEAR

Converting a Date from Text

Finding Out What TODAY Is

Determining the Day of the Week

Working with Workdays

Understanding How Excel Handles Time

Formatting Time

Deconstructing Time with HOUR, MINUTE, and SECOND

Finding the Time NOW

Chapter 4: Manipulating Text with Functions

Breaking Apart Text

Putting Text Together

Changing Text

Comparing, Finding, and Measuring Text

Book 4: Analyzing Data

Chapter 1: Using Basic Data Analysis Techniques

What Is Data Analysis, Anyway?

Analyzing Data with Conditional Formatting

Summarizing Data with Subtotals

Grouping Related Data

Consolidating Data from Multiple Worksheets

Chapter 2: Working with Data Analysis Tools

Working with Data Tables

Analyzing Data with Goal Seek

Analyzing Data with Scenarios

Optimizing Data with Solver

Chapter 3: Analyzing Table Data with Functions

Introducing the Database Functions

Retrieving a Value from a Table

Summing a Column’s Values

Counting a Column’s Values

Averaging a Column’s Values

Determining a Column’s Maximum and Minimum Values

Multiplying a Column’s Values

Deriving a Column’s Standard Deviation

Calculating a Column’s Variance

Book 5: Summarizing, Visualizing, and Illustrating Data

Chapter 1: Creating Charts

Introducing Excel Charts

Creating a Chart from Scratch

Deconstructing and Customizing Charts

Chapter 2: Creating and Using PivotTables

Understanding PivotTables

Exploring PivotTable Features

Building a PivotTable from an Excel Range or Table

Creating a PivotTable from External Data

Refreshing PivotTable Data

Adding Multiple Fields to a PivotTable Area

Pivoting a Field to a Different Area

Grouping PivotTable Values

Filtering PivotTable Values

Chapter 3: Performing PivotTable Calculations

Messing Around with PivotTable Summary Calculations

Working with PivotTable Subtotals

Introducing Custom Calculations

Inserting a Custom Calculated Field

Inserting a Custom Calculated Item

Editing a Custom Calculation

Deleting a Custom Calculation

Chapter 4: Building PivotCharts

Introducing the PivotChart

Creating a PivotChart

Working with PivotCharts

Book 6: Reporting and Querying Data

Chapter 1: Introducing Power Pivot

Understanding the Power Pivot Internal Data Model

Linking Excel Tables to Power Pivot

Chapter 2: Advanced Moves with PivotTables

Creating a PivotTable

Customizing PivotTable Reports

Understanding Slicers

Creating a Standard Slicer

Getting Fancy with Slicer Customizations

Controlling Multiple PivotTables with One Slicer

Creating a Timeline Slicer

Chapter 3: Working Directly with the Internal Data Model

Directly Feeding the Internal Data Model

Managing Relationships in the Internal Data Model

Managing Queries and Connections

Creating a New PivotTable Using the Internal Data Model

Filling the Internal Data Model with Multiple External Data Tables

Chapter 4: Adding Formulas to Power Pivot

Enhancing Power Pivot Data with Calculated Columns

Utilizing DAX to Create Calculated Columns

Understanding Calculated Measures

Chapter 5: Meeting Power Query and Its Connection Types

Power Query Basics

Understanding Column-Level Actions

Understanding Table Actions

Importing Data from Files

Importing Data from Database Systems

Managing Data Source Settings

Data Profiling with Power Query

Book 7: Creating Dashboards and Reports

Chapter 1: Getting in the Dashboard State of Mind

Defining Dashboards and Reports

Preparing for Greatness

A Quick Look at Dashboard Design Principles

Chapter 2: Building a Super Model

Understanding Data Modeling Best Practices

Finding Excel Functions That Really Deliver

Using Smart Tables That Expand with Data

Introducing Dynamic Arrays

Exploring Dynamic Array Functions

Chapter 3: Dressing Up Your Data Tables

Table Design Principles

Getting Fancy with Custom Number Formatting

Chapter 4: Formatting Your Way to Visualizations

Enhancing Reports with Conditional Formatting

Using Symbols to Enhance Reporting

Wielding the Magical Camera Tool

Enhancing Excel Reports with Shapes

Chapter 5: Displaying Performance against a Target

Showing Performance with Variances

Showing Performance against Organizational Trends

Using a Thermometer-Style Chart

Using a Bullet Graph

Showing Performance against a Target Range

Book 8: Automating Excel with Macros and VBA

Chapter 1: Macro Fundamentals

Choosing to Use a Macro

Recording a Macro

Understanding Macro Security

Storing and Running Macros

Exploring Macro Examples

Chapter 2: Getting Cozy with the Visual Basic Editor

Working in the Visual Basic Editor

Working with the Project Explorer

Working with a Code Pane

Customizing the VBE

Chapter 3: The Anatomy of Macros

A Brief Overview of the Excel Object Model

A Brief Look at Variables

Understanding Event Procedures

Error Handling in a Nutshell

Chapter 4: Working with Workbooks

Installing Macros

Creating a New Workbook from Scratch

Saving a Workbook When a Particular Cell Is Changed

Saving a Workbook before Closing

Protecting a Worksheet on Workbook Close

Unprotecting a Worksheet

Opening a Workbook to a Specific Tab

Opening a Specific Workbook Chosen by the User

Determining Whether a Workbook Is Already Open

Determining Whether a Workbook Exists in a Directory

Closing All Workbooks at Once

Printing All Workbooks in a Directory

Preventing the Workbook from Closing until a Cell Is Populated

Creating a Backup of the Current Workbook with Today’s Date

Index

About the Authors

Connect with Dummies

End User License Agreement

List of Tables

Book 2 Chapter 1

TABLE 1-1 Basic Formulas

TABLE 1-2 Referencing Cells

TABLE 1-3 Order of Operations

TABLE 1-4 Error Types

TABLE 1-5 Using Functions in Formulas

TABLE 1-6 Arguments in Functions

Book 2 Chapter 2

TABLE 2-1 Function Categories in the Insert Function Dialog Box

Book 2 Chapter 4

TABLE 4-1 Valid Formulas That Return Incorrect Answers

Book 3 Chapter 1

TABLE 1-1 Depreciation Methods

Book 3 Chapter 2

TABLE 2-1 Using the ROUND Function

TABLE 2-2 Using the ROUNDUP and ROUNDDOWN Functions

TABLE 2-3 Using FLOOR and CEILING for Sophisticated Rounding

TABLE 2-4 Rounding to Even or Odd Integers

TABLE 2-5 Using INT and ROUND to Process Random Values

TABLE 2-6 The Power of Raising Numbers to a Power

TABLE 2-7 Argument Values for the SUBTOTAL Function

Book 3 Chapter 3

TABLE 3-1 How Excel Represents Time

Book 3 Chapter 4

TABLE 4-1 How MID Works

TABLE 4-2 TEXTJOIN arguments

TABLE 4-3 The Rounding Feature

TABLE 4-4 Formatting Options for the TEXT Function

TABLE 4-5 Applying the SUBSTITUTE Function

TABLE 4-6 Removing Spaces

TABLE 4-7 Changing Text Case

TABLE 4-8 Comparing FIND and SEARCH

TABLE 4-9 Finding One String inside Another String

TABLE 4-10 Using the SEARCH Function

Book 4 Chapter 3

TABLE 3-1 Database Function Arguments

Book 6 Chapter 1

TABLE 1-1 Limitations of the Internal Data Model

Book 6 Chapter 5

TABLE 5-1 Column-Level Actions

TABLE 5-2 Table-Level Actions

Book 7 Chapter 3

TABLE 3-1 Common Date and Time Format Codes

List of Illustrations

Book 1 Chapter 1

FIGURE 1-1: Microsoft Excel’s user interface.

FIGURE 1-2: The Status Bar.

FIGURE 1-3: Use the Fill Handle.

FIGURE 1-4: The Insert dialog box.

FIGURE 1-5: The Move or Copy dialog box.

Book 1 Chapter 2

FIGURE 2-1: Context menu options for the Fill Handle.

Book 1 Chapter 3

FIGURE 3-1: The Format Cells dialog box.

FIGURE 3-2: The Cell Styles gallery.

Book 1 Chapter 4

FIGURE 4-1: The Sort dialog box.

FIGURE 4-3: The Sort Options dialog box.

FIGURE 4-2: The Custom Lists dialog box.

FIGURE 4-4: The Remove Duplicates dialog box.

FIGURE 4-5: The SUM function versus the SUBTOTAL function.

FIGURE 4-6: The Create Table dialog box.

FIGURE 4-7: The Modify Table Style dialog box.

FIGURE 4-8: The structured reference version of the Formula AutoComplete list.

Book 1 Chapter 5

FIGURE 5-1: The Find and Replace dialog box.

FIGURE 5-2: The Navigation task pane.

FIGURE 5-3: The Go To Special dialog box.

Book 2 Chapter 1

FIGURE 1-1: The Info tab shows details about your Excel file.

FIGURE 1-2: Looking at a workbook and worksheets.

FIGURE 1-3: Changing the name of a worksheet.

FIGURE 1-4: Getting to know the Formulas tab of the ribbon.

FIGURE 1-5: Eyeing the Watch Window.

FIGURE 1-6: Looking at what goes into a worksheet.

FIGURE 1-7: Selecting a range of cells.

FIGURE 1-8: Adding a name to the workbook.

FIGURE 1-9: Using the Name Box to find the named area.

FIGURE 1-10: Trying a table.

FIGURE 1-11: Formatting data.

FIGURE 1-12: Using the Format Cells dialog box for advanced formatting options.

FIGURE 1-13: Entering a formula that references cells.

FIGURE 1-14: Completing the formula.

FIGURE 1-15: A finished formula.

FIGURE 1-16: Getting ready to drag the formula down.

FIGURE 1-17: Populating cells with a formula by using the Fill Handle.

FIGURE 1-18: Getting a message from Excel.

FIGURE 1-19: Entering the AVERAGE function.

FIGURE 1-20: The Insert Function dialog box.

FIGURE 1-21: Getting ready to enter some arguments to the function.

FIGURE 1-22: Using RefEdit to enter arguments.

FIGURE 1-23: Completing the function entry.

FIGURE 1-24: Math was never this easy!

FIGURE 1-25: Nesting functions.

FIGURE 1-26: Getting a result from nested functions.

Book 2 Chapter 2

FIGURE 2-1: Use the Insert Function dialog box to easily enter functions in a w...

FIGURE 2-2: The AutoSum button offers quick access to basic functions and the I...

FIGURE 2-3: The Insert Function dialog box.

FIGURE 2-4: The Function Arguments dialog box shows that the TODAY function tak...

FIGURE 2-5: Ready to input function arguments.

FIGURE 2-6: Getting instant results in the Function Arguments dialog box.

FIGURE 2-7: Getting the final answer from the function.

FIGURE 2-8: Adding a table and a named area to a worksheet.

FIGURE 2-9: Entering arguments.

FIGURE 2-10: Calculating a sum based on cell and range references.

FIGURE 2-11: Entering a formula in the Formula Box has its conveniences.

FIGURE 2-12: Entering functions has never been this easy.

FIGURE 2-13: Completing the direct-in-the-cell formula entry.

Book 2 Chapter 3

FIGURE 3-1: Creating a named area with the New Name dialog box.

FIGURE 3-2: A stock portfolio.

FIGURE 3-3: Calculating the value of a stock portfolio the old-fashioned way.

FIGURE 3-4: Calculating the value of a stock portfolio using an array function.

FIGURE 3-5: Making an easy calculation using an array formula.

FIGURE 3-6: Expanding an array or range.

FIGURE 3-7: Shrink an array with DROP.

FIGURE 3-8: Only a small section of this array is relevant.

FIGURE 3-9: The TAKE function has created an array of just the needed informati...

FIGURE 3-10: Using HSTACK and VSTACK.

FIGURE 3-11: Transposing data.

FIGURE 3-12: Preparing an area to receive transposed data.

FIGURE 3-13: Completing the function.

FIGURE 3-14: Transposed data after formatting.

FIGURE 3-15: Using the Paste Special dialog box to transpose data.

Book 2 Chapter 4

FIGURE 4-1: Using parentheses in a formula.

FIGURE 4-2: Fixing mismatched parentheses.

FIGURE 4-3: Correcting a circular reference.

FIGURE 4-4: Setting calculation and iteration options.

FIGURE 4-5: Hunting down circular references.

FIGURE 4-6: Browsing for an unfound external workbook.

FIGURE 4-7: Using the Workbook Links pane to correct external reference problem...

FIGURE 4-8: Confirming that you mean to break links.

FIGURE 4-9: Calculating a sum with no possible error.

FIGURE 4-10: Excel detects a possible error.

FIGURE 4-11: Deciding what to do with the possible error.

FIGURE 4-12: Understanding precedents and dependents.

FIGURE 4-13: Tracing formulas.

FIGURE 4-14: Examining the components of a complex formula.

FIGURE 4-15: Using the Watch Window to keep an eye on a formula’s result.

FIGURE 4-16: Evaluating a formula.

FIGURE 4-17: Checking the cause of an error.

FIGURE 4-18: Two ways to prevent an error from being seen.

Book 3 Chapter 1

FIGURE 1-1: Using the Format Cells dialog box to control numeric display.

FIGURE 1-2: The PMT function calculates the loan payment amount.

FIGURE 1-3: The NPER function calculates the number of payments for a loan.

FIGURE 1-4: The PDURATION function calculates the number of payments for a loan...

FIGURE 1-5: The RATE function calculates the periodic interest rate.

FIGURE 1-6: The PV function calculates the principal amount of a loan.

FIGURE 1-7: Earning extra money in an investment.

FIGURE 1-8: Depreciating an asset.

FIGURE 1-9: Offsetting depreciation periods from the calendar.

FIGURE 1-10: Calculating the return on a business venture.

FIGURE 1-11: Comparing business opportunities.

Book 3 Chapter 2

FIGURE 2-1: Using the SUM function to add noncontiguous numbers.

FIGURE 2-2: Calculating a sum from a range of cells.

FIGURE 2-3: Calculating a sum of multiple ranges.

FIGURE 2-4: Using AutoSum to guess a range for the SUM function.

FIGURE 2-5: Using FLOOR and CEILING to round to a desired multiple.

FIGURE 2-6: Using INT to drop unnecessary decimals.

FIGURE 2-7: Putting the PRODUCT function to work.

FIGURE 2-8: Working with the SUBTOTAL function.

FIGURE 2-9: Following the steps used by SUMPRODUCT.

FIGURE 2-10: Being productive with SUMPRODUCT.

FIGURE 2-11: Using SUMIF for targeted tallying.

FIGURE 2-12: Using SUMIFS to get a multiple filtered sum.

Book 3 Chapter 3

FIGURE 3-1: Using the Format Cells dialog box to control how dates are displaye...

FIGURE 3-2: Using the DATE function to assemble a date from separate month, day...

FIGURE 3-3: Splitting apart a date with the DAY, MONTH, and YEAR functions.

FIGURE 3-4: Using the DAY function to analyze customer activity.

FIGURE 3-5: Converting dates to their serial equivalents with the DATEVALUE fun...

FIGURE 3-6: Using WEEKDAY tells you which day of the week a date falls on.

FIGURE 3-7: Counting workdays with NETWORKDAYS.

FIGURE 3-8: Splitting time with the HOUR, MINUTE, and SECOND functions.

FIGURE 3-9: Using the HOUR function to summarize results.

FIGURE 3-10: Calculating minutes elapsed with the MINUTE function.

Book 3 Chapter 4

FIGURE 4-1: Getting the three left characters from a larger string.

FIGURE 4-2: Using MID to pull characters from any position in a string.

FIGURE 4-3: Using TEXTSPLIT to break apart a string of text.

FIGURE 4-4: Putting strings together with CONCATENATE.

FIGURE 4-5: Putting strings together with TEXTJOIN.

FIGURE 4-6: Using DOLLAR to round numbers and format them as currency.

FIGURE 4-7: Formatting options in the Format Cells dialog box.

FIGURE 4-8: Using TEXT to report in a well-formatted manner.

FIGURE 4-9: Using REPLACE to change text.

FIGURE 4-10: Removing spaces with the TRIM function.

FIGURE 4-11: Comparing strings with the EXACT function.

Book 4 Chapter 1

FIGURE 1-1: The Greater Than dialog box and some highlighted values.

FIGURE 1-2: Use the Duplicate Values rule to highlight worksheet duplicates.

FIGURE 1-3: The Top 10 Items dialog box with the top five values highlighted.

FIGURE 1-4: The higher the value, the longer the data bar.

FIGURE 1-5: Excel applies an icon based on each cell’s value.

FIGURE 1-6: Use the New Formatting Rule dialog box to create a custom rule.

FIGURE 1-7: Use the Conditional Formatting Rules Manager dialog box to edit you...

FIGURE 1-8: Use the Subtotal dialog box to apply subtotals to a range.

FIGURE 1-9: Some subtotals applied to the Total column for each customer.

FIGURE 1-10: When you group a range, Excel displays its outlining tools.

FIGURE 1-11: Consolidate multiple worksheets by adding a range from each one.

Book 4 Chapter 2

FIGURE 2-1: This data table has the input values in a column.

FIGURE 2-2: Enter the address of the input cell.

FIGURE 2-3: The data table results.

FIGURE 2-4: For a two-input data table, enter one set of values in a column and...

FIGURE 2-5: Enter the addresses of the input cells.

FIGURE 2-6: The two-input data table results.

FIGURE 2-7: Using Goal Seek to calculate the annual deposit required to end up ...

FIGURE 2-8: Goal Seek took all of a second or two to find a solution.

FIGURE 2-9: Creating a scenario for a mortgage analysis.

FIGURE 2-10: Example values for a scenario’s changing cells.

FIGURE 2-11: The goal for this data model is to find the break-even point (wher...

FIGURE 2-12: The completed Solver Parameters dialog box.

FIGURE 2-13: The Solver Results dialog box and the solution to the break-even p...

FIGURE 2-14: The completed Add Constraint dialog box.

FIGURE 2-15: The Solver Results dialog box and the final solution to the break-...

Book 4 Chapter 3

FIGURE 3-1: A table and its criteria range.

FIGURE 3-2: Use DGET to retrieve a value from a table based on your criteria.

FIGURE 3-3: Use DSUM to add a column’s values based on your criteria.

FIGURE 3-4: Use DCOUNT (or DCOUNTA) to tally a column’s values based on your cr...

FIGURE 3-5: Use DAVERAGE to average a column’s values based on your criteria.

FIGURE 3-6: Use DMAX and DMIN to return a column’s largest and smallest values ...

FIGURE 3-7: Use DPRODUCT to multiply a column’s values based on your criteria.

FIGURE 3-8: Use DSTDEV (or DSTDEVP) to derive the standard deviation of a colum...

Book 5 Chapter 1

FIGURE 1-1: The Recommended Charts tab of the Insert Chart dialog box.

FIGURE 1-2: The All Charts tab of the Insert Chart dialog box.

FIGURE 1-3: A standard Clustered Columns chart.

FIGURE 1-4: The Select Data Source dialog box.

FIGURE 1-5: The Move Chart dialog box.

FIGURE 1-6: The abbreviated version of the Paste Special dialog box.

Book 5 Chapter 2

FIGURE 2-1: Some great data, but how do you make sense of it?

FIGURE 2-2: The PivotTable creates order out of data chaos.

FIGURE 2-3: The features of a typical PivotTable.

FIGURE 2-4: You start with a blank PivotTable and the PivotTable Fields task pa...

FIGURE 2-5: The features of a typical PivotTable.

FIGURE 2-6: Import the external data to a PivotTable Report.

FIGURE 2-7: Select Refresh Data When Opening the File to tell Excel to refresh ...

FIGURE 2-8: The Grouping dialog box.

FIGURE 2-9: From the filter field’s drop-down list, select a report filter.

FIGURE 2-10: Excel filters the PivotTable to show just the selected items in ea...

Book 5 Chapter 3

FIGURE 3-1: Use the Value Field Settings dialog box to choose a summary calcula...

FIGURE 3-2: A PivotTable that shows sales in two years: 2021 and 2022.

FIGURE 3-3: Use the Value Field Settings dialog box to choose a summary calcula...

FIGURE 3-4: The PivotTable from Figure 3-2 is now using the Difference From cal...

FIGURE 3-5: A PivotTable that shows quarterly sales by region.

FIGURE 3-6: The PivotTable from Figure 3-5, now using the % Of calculation.

FIGURE 3-7: A PivotTable showing monthly order totals.

FIGURE 3-8: The PivotTable from Figure 3-7, with the Running Total In calculati...

FIGURE 3-9: A PivotTable showing units sold by category and region.

FIGURE 3-10: The PivotTable from Figure 3-9, with the Index calculation applied...

FIGURE 3-11: A PivotTable with multiple subtotals.

FIGURE 3-12: A custom calculated field, ready for insertion into the PivotTable...

FIGURE 3-13: The custom calculated field in action.

FIGURE 3-14: A custom calculated item, ready for action.

FIGURE 3-15: Two custom calculated items added to the row area.

Book 5 Chapter 4

FIGURE 4-1: The major sights to see in the PivotChart landscape.

FIGURE 4-2: Excel kicks things off with a blank PivotTable and PivotChart, and ...

FIGURE 4-3: An embedded PivotChart and its PivotTable.

FIGURE 4-4: Use the Move Chart dialog box to move a PivotChart to another works...

FIGURE 4-5: Pull down the Report Filter Field List and then select an item.

FIGURE 4-6: The PivotChart from Figure 4-5 now displayed as a pie chart.

FIGURE 4-7: A data table shown below a PivotChart.

Book 6 Chapter 1

FIGURE 1-1: The Power Pivot ribbon interface.

FIGURE 1-2: The Customers, InvoiceHeader, and InvoiceDetails worksheets.

FIGURE 1-3: Converting the data range into an Excel table.

FIGURE 1-4: Giving your newly created Excel table a friendly name.

FIGURE 1-5: The Power Pivot window shows all the data that exists in your data ...

FIGURE 1-6: Each table you add to the data model is placed on its own tab in Po...

FIGURE 1-7: Diagram view allows you to see all tables in the data model.

FIGURE 1-8: To create a relationship, you simply click and drag a line between ...

FIGURE 1-9: When you create relationships, the Power Pivot diagram shows join l...

FIGURE 1-10: Use the Manage Relationships dialog box to edit or delete existing...

FIGURE 1-11: Use the Edit Relationship dialog box to adjust the tables and fiel...

FIGURE 1-12: You now have a Power Pivot–driven PivotTable that aggregates acros...

Book 6 Chapter 2

FIGURE 2-1: The Create PivotTable dialog box.

FIGURE 2-2: The PivotTable Fields task pane.

FIGURE 2-3: Select the Market check box.

FIGURE 2-4: Add the Sales Amount field by selecting its check box.

FIGURE 2-5: Adding a layer of analysis is as easy as bringing in another field.

FIGURE 2-6: Your business segments are now column oriented.

FIGURE 2-7: Adding Region to the Filters drop zone displays a Region drop-down ...

FIGURE 2-8: Refreshing the PivotTable captures changes made to your data.

FIGURE 2-9: Changing the range that feeds the PivotTable.

FIGURE 2-10: Select the new range that feeds the PivotTable.

FIGURE 2-11: The three layouts for a PivotTable report.

FIGURE 2-12: Changing the layout of the PivotTable.

FIGURE 2-13: Right-click the target field, and then select the Value Field Sett...

FIGURE 2-14: Type the new name of the field in the Custom Name text box.

FIGURE 2-15: Changing the type of summary calculation used in a field.

FIGURE 2-16: Subtotals sometimes muddle the data you’re trying to show.

FIGURE 2-17: The report shown in Figure 2-16, without subtotals.

FIGURE 2-18: Select the None radio button in the Field Settings dialog box to r...

FIGURE 2-19: To remove Bikes from this analysis …

FIGURE 2-20: … deselect the Bikes check box.

FIGURE 2-21: The analysis from Figure 2-19, without the Bikes segment.

FIGURE 2-22: All sales periods are showing.

FIGURE 2-23: Filtering for the Europe region causes certain sales periods to di...

FIGURE 2-24: Select the Show Items with No Data option to force Excel to displa...

FIGURE 2-25: All sales periods are now displayed, even if there is no data to b...

FIGURE 2-26: Applying a sort to a PivotTable field.

FIGURE 2-27: Default PivotTable Filter fields do not work together to limit fil...

FIGURE 2-28: Filter fields show the phrase

(Multiple Items)

whenever multiple s...

FIGURE 2-29: Slicers work together to show you relevant data items based on you...

FIGURE 2-30: Slicers do a better job at displaying multiple item selections.

FIGURE 2-31: Inserting a slicer.

FIGURE 2-32: Select the fields for which you want slicers created.

FIGURE 2-33: Select the fields you want filtered using slicers.

FIGURE 2-34: The fact that you can see the current filter state gives slicers a...

FIGURE 2-35: Clearing the filters on a slicer.

FIGURE 2-36: Adjust the slicer size and placement by dragging its position poin...

FIGURE 2-37: The Format Slicer pane offers more control over how the slicer beh...

FIGURE 2-38: Adjust the Number of Columns property to display the slicer data i...

FIGURE 2-39: The Slicer Settings dialog box.

FIGURE 2-40: Choose the PivotTables to be filtered by this slicer.

FIGURE 2-41: Select the date fields for which you want slicers created.

FIGURE 2-42: Click a date selection to filter the PivotTable or PivotChart.

FIGURE 2-43: You can expand the range on the Timeline slicer to include more da...

FIGURE 2-44: Quickly switch among Quarters, Years, Months, and Days.

Book 6 Chapter 3

FIGURE 3-1: This table shows transactions by employee number.

FIGURE 3-2: This table provides information on employees: first name, last name...

FIGURE 3-3: When you create a new PivotTable from the Transactions table, be su...

FIGURE 3-4: Create a new PivotTable from the Employees table, and select the Ad...

FIGURE 3-5: Select All in the PivotTable Fields List to see both tables in the ...

FIGURE 3-6: When Excel prompts you, choose to create the relationship between t...

FIGURE 3-7: Build the appropriate relationship using the Table and Column drop-...

FIGURE 3-8: You’ve achieved your goal of showing sales by job title.

FIGURE 3-9: The Manage Relationships dialog box enables you to make changes to ...

FIGURE 3-10: Use the Queries & Connections task pane to manage the queries and ...

FIGURE 3-11: In the PivotTable from an External Data Source dialog box, click t...

FIGURE 3-12: Use the Existing Connections dialog box to select the Data Model a...

FIGURE 3-13: The newly created PivotTable shows all tables in the internal data...

FIGURE 3-14: Getting data from a Microsoft Access database.

FIGURE 3-15: Enable the selection of multiple tables.

FIGURE 3-16: Place a check mark next to each table you want import to the inter...

FIGURE 3-17: Create a PivotTable Report from the Import Data dialog box.

FIGURE 3-18: You’re ready to build your PivotTable analysis based on multiple e...

Book 6 Chapter 4

FIGURE 4-1: Start the calculated column by entering an operation on the Formula...

FIGURE 4-2: Calculated columns automatically show up in the PivotTable Fields L...

FIGURE 4-3: You can use the formatting tools found on the Power Pivot window’s ...

FIGURE 4-4: The new Gross Margin calculation is using the previously created ca...

FIGURE 4-5: Right-click and select Hide from Client Tools.

FIGURE 4-6: Hidden columns are grayed out, and calculated columns have darker h...

FIGURE 4-7: The Insert Function dialog box shows you all available DAX function...

FIGURE 4-8: The DAX SUM function can only sum the column as a whole.

FIGURE 4-9: DAX functions can help enhance the invoice header data with Year an...

FIGURE 4-10: Using DAX functions to supplement a table with Year, Month, and Mo...

FIGURE 4-11: DAX calculations are immediately available in any connected PivotT...

FIGURE 4-12: Month names in Power Pivot–driven PivotTables don’t automatically ...

FIGURE 4-13: The Sort by Column dialog box lets you define how columns are sort...

FIGURE 4-14: The month names now show in the correct month order.

FIGURE 4-15: The discount amount in the Customers table can be used in a calcul...

FIGURE 4-16: Use the RELATED function to look up a field from another table.

FIGURE 4-17: The final discount amount calculated column using the Discount% co...

FIGURE 4-18: Creating a new calculated measure.

FIGURE 4-19: Calculated measures can be seen in the PivotTable Fields List.

Book 6 Chapter 5

FIGURE 5-1: Starting a Power Query web query.

FIGURE 5-2: Enter the target URL containing the data you need.

FIGURE 5-3: Select the correct data source and then click the Transform Data bu...

FIGURE 5-4: The Query Editor window allows you to shape, clean, and transform d...

FIGURE 5-5: Change the data type of the High, Low, and Close fields to currency...

FIGURE 5-6: Removing errors caused by text values that couldn’t be converted to...

FIGURE 5-7: The Import Data dialog box gives you more control over how the resu...

FIGURE 5-8: Your final query pulled from the internet: transformed, put into an...

FIGURE 5-9: You can view and manage query steps in the Applied Steps section of...

FIGURE 5-10: Right-click any query step to edit, rename, delete, or move the st...

FIGURE 5-11: Select the data sources you want to work with, and then click the ...

FIGURE 5-12: Preview the data and use the drop-down lists to tell Power Query h...

FIGURE 5-13: The available tables and pages in the PDF are shown in the Navigat...

FIGURE 5-14: Data preview of the files in the target folder.

FIGURE 5-15: Use the Power Query Editor to add more file attributes to the impo...

FIGURE 5-16: Select the view you want imported, and then click the Load button.

FIGURE 5-17: The final imported database data.

FIGURE 5-18: Edit a data source by selecting it and clicking the Edit Permissio...

FIGURE 5-19: Edit the credentials for a data source by clicking the Edit button...

FIGURE 5-20: Data Profiling options are found in the Data Preview group under t...

FIGURE 5-21: Choose Column Profiling Based on Entire Data Set to get a more com...

FIGURE 5-22: Right-clicking a column profile histogram bar exposes the quick ac...

Book 7 Chapter 1

FIGURE 1-1: Reports present data for viewing but don’t lead readers to conclusi...

FIGURE 1-2: Dashboards provide at-a-glance views into key measures relevant to ...

FIGURE 1-3: Each box in this dashboard layout mockup represents a component and...

FIGURE 1-4: Studies show that users pay particular attention to the upper left ...

Book 7 Chapter 2

FIGURE 2-1: Avoid hard-coded tables that fuse data, analysis, and presentation.

FIGURE 2-2: An effective data model separates data, analysis, and presentation.

FIGURE 2-3: A spreadsheet report.

FIGURE 2-4: A flat data file.

FIGURE 2-5: A tabular dataset.

FIGURE 2-6: A model map allows you to document how each range interacts with yo...

FIGURE 2-7: In this example, the VLOOKUP function helps to look up the appropri...

FIGURE 2-8: Using the VLOOKUP function to extract and shape data.

FIGURE 2-9: You can use data validation to create a predefined list of valid va...

FIGURE 2-10: HLOOKUP formulas help to find March and June numbers from the look...

FIGURE 2-11: In this example, HLOOKUP formulas pull and reshape data without di...

FIGURE 2-12: Without the SUMPRODUCT function, getting the total sales involves ...

FIGURE 2-13: The SUMPRODUCT function allows you to perform the same analysis wi...

FIGURE 2-14: The SUMPRODUCT function can be used to filter data based on criter...

FIGURE 2-15: The SUMPRODUCT function can be used to pull summarized numbers fro...

FIGURE 2-16: The CHOOSE function allows you to find values from a defined set o...

FIGURE 2-17: The CHOOSE formulas ensure that the appropriate data is synchronou...

FIGURE 2-18: The date in both the table and chart ends in June.

FIGURE 2-19: Converting a range of data to an Excel table.

FIGURE 2-20: Excel tables can be used as the source for charts, PivotTables, na...

FIGURE 2-21: Excel tables automatically expand when new data is added.

FIGURE 2-22: To remove Excel table functionality, convert the table back to a r...

FIGURE 2-23: A simple formula that references a range.

FIGURE 2-24: Excel automatically spills the results into the surrounding cells.

FIGURE 2-25: Dynamic arrays work with any traditional Excel function that accep...

FIGURE 2-26: Spill ranges will visually show a line around them.

FIGURE 2-27: A spill error caused by an obstruction in the spill range.

FIGURE 2-28: Referencing a single cell in a spill range doesn’t allow you to ca...

FIGURE 2-29: Using the spill range operator to apply a function to the entire s...

FIGURE 2-30: Using the spill range operator to count all values in the referenc...

FIGURE 2-31: Using the SORT function to sort students by the change in test sco...

FIGURE 2-32: Using the SORT function to sort by columns.

FIGURE 2-33: Using the SORTBY function to apply a multi-column sort.

FIGURE 2-34: Adding the

Exactly_once

argument extracts only values that appear ...

FIGURE 2-35: Filtering records where the Change value is greater than 10.

FIGURE 2-36: Getting the FILTER criteria from cell F1.

FIGURE 2-37: Combining SORT with FILTER to sort results.

FIGURE 2-38: Using multiple filter conditions.

FIGURE 2-39: Criteria evaluation behind the scenes.

FIGURE 2-40: Using the

+

operator to return results if the first condition is

T

...

FIGURE 2-41: A basic XLOOKUP function to find Customer Type based on revenue.

FIGURE 2-42: Enter a comma to see choices for the next argument.

FIGURE 2-43: XLOOKUP results using approximate matching.

FIGURE 2-44: Using wildcard characters to perform complex searches.

Book 7 Chapter 3

FIGURE 3-1: A poorly designed table.

FIGURE 3-2: Remove unnecessary cell coloring.

FIGURE 3-3: Minimize the use of borders and use single accounting underlines to...

FIGURE 3-4: Use the Border tab of the Format Cells dialog box to customize your...

FIGURE 3-5: Single accounting underlines effectively call out your column heade...

FIGURE 3-6: Use number formatting to eliminate clutter in the table and draw at...

FIGURE 3-7: Send your labels and headers to the background by subduing their co...

FIGURE 3-8: Before and after applying table design principles.

FIGURE 3-9: Choose a base format.

FIGURE 3-10: The Type input box allows you to customize the syntax for the numb...

FIGURE 3-11: Formatting numbers applies only a cosmetic look. Look at the Formu...

FIGURE 3-12: Custom number formatting that shows zeroes as

n/a

.

FIGURE 3-13: Dates and times can also be formatted using the Format Cells dialo...

Book 7 Chapter 4

FIGURE 4-1: Excel’s predefined conditional formatting scenarios include Highlig...

FIGURE 4-2: Each scenario has its own dialog box for defining the rule’s trigge...

FIGURE 4-3: Cells greater than 400 are formatted.

FIGURE 4-4: Each scenario has its own dialog box you can use to define the trig...

FIGURE 4-5: With conditional formatting, you can easily see that September thro...

FIGURE 4-6: Conditional formatting with Data Bars.

FIGURE 4-7: Conditional formatting with Color Scales.

FIGURE 4-8: Applying Icon Sets.

FIGURE 4-9: Conditional formatting with Icon Sets.

FIGURE 4-10: With a custom formatting rule, you can tag the above-average value...

FIGURE 4-11: Select the Format All Cells Based on Their Values rule and then us...

FIGURE 4-12: Change the Type drop-down boxes to Formula and enter the appropria...

FIGURE 4-13: The Conditional Formatting Rules Manager dialog box.

FIGURE 4-14: This new rule is meant to apply to any cell value that you don’t w...

FIGURE 4-15: Click Stop If True to tell Excel to stop evaluating those cells th...

FIGURE 4-16: This table is now formatted to show only one icon.

FIGURE 4-17: Showing Data Bars inside the same cell as values can make it diffi...

FIGURE 4-18: Edit the formatting rule to show only the Data Bars, not the data.

FIGURE 4-19: Data Bars, cleanly placed next to the data values.

FIGURE 4-20: The up arrow indicates an upward trend, the down arrow indicates a...

FIGURE 4-21: You can adjust the thresholds that define what

up, down,

and

flat

...

FIGURE 4-22: Use symbols to add an extra layer of analysis to charts.

FIGURE 4-23: The starting data with a holding cell for symbols.

FIGURE 4-24: Use the Symbol dialog box to insert symbols into the holding cell.

FIGURE 4-25: Copy the newly inserted symbols to the Clipboard.

FIGURE 4-26: Create a custom number format using the symbols.

FIGURE 4-27: Your symbols are now part of the number formatting.

FIGURE 4-28: Not surprisingly, the icon for the Camera tool looks like a camera...

FIGURE 4-29: Enter some simple numbers in a range and create a basic chart from...

FIGURE 4-30: A live picture is created via the Camera tool.

FIGURE 4-31: Use the Camera tool to get multiple source ranges into a compact a...

FIGURE 4-32: A peekaboo tab.

FIGURE 4-33: A deconstructed view of the peekaboo tab.

FIGURE 4-34: A visual banner made with shapes.

FIGURE 4-35: Combine shapes with a chart to save dashboard real estate.

FIGURE 4-36: Use the Edit Points feature to construct your own shape.

FIGURE 4-37: Using a newly constructed shape to create custom infographic eleme...

Book 7 Chapter 5

FIGURE 5-1: A typical chart showing performance against a target.

FIGURE 5-2: Consider using variances to plot performance against a target.

FIGURE 5-3: Measuring data when there’s no target for a measure.

FIGURE 5-4: Start a new column and enter a formula.

FIGURE 5-5: Thermometer-style charts offer a unique way to show performance aga...

FIGURE 5-6: Bullet graphs display multiple perspectives in an incredibly compac...

FIGURE 5-7: The parts of a bullet graph.

FIGURE 5-8: Start with data that contains the main data points of the bullet gr...

FIGURE 5-9: Switch the orientation of the chart to read from columns.

FIGURE 5-10: Use the Change Chart Type dialog box to change the Target series t...

FIGURE 5-11: Adjust the marker to a dash.

FIGURE 5-12: Be sure to delete the newly created secondary vertical axis.

FIGURE 5-13: Your formatted bullet graph.

FIGURE 5-14: To add more data to your chart, manually expand the chart’s data s...

FIGURE 5-15: Expanding the data source automatically creates new bullet graphs.

FIGURE 5-16: Try setting gap widths to zero to create clean-looking qualitative...

FIGURE 5-17: Rotate all labels so that they’re on their sides.

FIGURE 5-18: A horizontal bullet graph.

FIGURE 5-19: You can create a component that plots performance against a target...

FIGURE 5-20: Create a chart feeder that contains formulas that define the data ...

FIGURE 5-21: Add a row for the performance values.

FIGURE 5-22: Use the Change Chart Type dialog box to change the Values series t...

FIGURE 5-23: Format the Lower Limit series so that it’s hidden.

Book 8 Chapter 1

FIGURE 1-1: The Record Macro dialog box.

FIGURE 1-2: Your pre-totaled worksheet containing two tables.

FIGURE 1-3: Your post-totaled worksheet.

FIGURE 1-4: The Macro dialog box.

FIGURE 1-5: Recording a macro with relative references.

FIGURE 1-6: The Trusted Locations window allows you to add directories that are...

FIGURE 1-7: You can find the form controls on the Developer tab.

FIGURE 1-8: Assign a macro to the newly added button.

FIGURE 1-9: Adding a macro to the Quick Access Toolbar.

FIGURE 1-10: Use macros to build buttons that help users navigate your reports.

FIGURE 1-11: This report allows users to choose their perspective.

FIGURE 1-12: The macros behind these buttons rearrange the data fields in a Piv...

FIGURE 1-13: Offering prerecorded views not only saves time and effort, but als...

FIGURE 1-14: You can give your audience a choice in how they view data.

Book 8 Chapter 2

FIGURE 2-1: The VBE with significant elements identified.

FIGURE 2-2: The Project Explorer with two projects open, expanded to show their...

FIGURE 2-3: Code modules are visible in the Project Explorer in a folder called...

FIGURE 2-4: The Editor tab in the Options dialog box.

FIGURE 2-5: Change the way the VBE looks with the Editor Format tab.

FIGURE 2-6: The General tab of the Options dialog box.

FIGURE 2-7: The Docking tab of the Options dialog box.

Book 8 Chapter 3

FIGURE 3-1: Getting to the built-in module for a worksheet.

FIGURE 3-2: The default

SelectionChange

event for the Worksheet object.

FIGURE 3-3: Click the Event drop-down list to choose the most appropriate event...

FIGURE 3-4: The default Open event for the Workbook object.

FIGURE 3-5: Click the Event drop-down list to choose the most appropriate event...

Book 8 Chapter 4

FIGURE 4-1: Changing any cell in range C5:C16 forces the workbook to save.

FIGURE 4-2: Enter or paste your code in the

Worksheet_Change

event Code pane.

FIGURE 4-3: A message box opens when you attempt to close the workbook.

FIGURE 4-4: Type or paste your code in the

Workbook_BeforeClose

event Code pane...

FIGURE 4-5: Type or paste your code in the

Workbook_BeforeClose

event Code pane...

FIGURE 4-6: Type or paste your code in the

Workbook_Open

event Code pane.

FIGURE 4-7: You want your workbook to automatically open to the sheet called St...

FIGURE 4-8: Type or paste your code in the

Workbook_Open

event Code pane.

FIGURE 4-9: The macro opens the Open dialog box.

FIGURE 4-10: You can avoid this annoying message box when opening a workbook th...

FIGURE 4-11: You can prevent your workbook from closing until a specific cell i...

FIGURE 4-12: Type or paste your code in the

Workbook_BeforeClose

event Code pan...

Guide

Cover

Table of Contents

Title Page

Copyright

Begin Reading

Index

About the Authors

Pages

iii

iv

1

2

3

4

5

6

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

38

39

40

41

42

43

44

45

46

47

48

49

50

51

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

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

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

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

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

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

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

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

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

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

499

500

501

502

503

504

505

506

507

508

509

510

511

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

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

560

561

562

563

564

565

566

567

568

569

570

571

572

573

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

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

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

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

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

Microsoft® 365 Excel® All-in-One For Dummies®

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.

Media and software compilation copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.

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 or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

The manufacturer’s authorized representative according to the EU General Product Safety Regulation is Wiley-VCH GmbH, Boschstr. 12, 69469 Weinheim, Germany, e-mail: [email protected].

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 365 and Excel are trademarks or 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. Microsoft 365 Excel® All-in-One For Dummies®, is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR 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 WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

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 [phone]. 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 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 is available from the publisher.

ISBN 978-1-394-34463-5 (pbk); ISBN 978-1-394-34465-9 (ebk); ISBN 978-1-394-34464-2 (ebk)

Introduction

Welcome to Microsoft 365 Excel All-in-One For Dummies! Whether you’re just getting started with spreadsheets or you’ve been crunching numbers for many years, you’ve made the right choice with this book. Packed with tips, tricks, and real-world examples, it gives you the information you need to harness Excel’s power and use the app’s features to simplify your work, bring order to chaotic data, and unlock valuable insights.

About This Book

Microsoft 365 Excel All-in-One For Dummies contains the following eight minibooks:

Book 1

: Getting Started with Excel