Microsoft 365 Excel All-in-One For Dummies
1st Edition
Published on 24. July 2025
819 pages
978-1-394-34465-9 (ISBN)
Description
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.
More details
Other editions
Additional editions
David H. Ringstrom | Michael Alexander | Dick Kusleika
Microsoft 365 Excel All-in-One For Dummies
Book
08/2025
1st Edition
Wiley
€44.00
Available immediately
Persons
This book collects bestselling Excel content from the For Dummies series, including selections from Microsoft 365 Excel For Dummies (David H. Ringstrom), Microsoft Excel Dashboards & Reports (Michael Alexander), Microsoft 365 Excel Formulas & Functions For Dummies (Ken Bluttman), Microsoft Excel Power Pivot & Power Query For Dummies (Michael Alexander), Microsoft Excel Data Analysis For Dummies (Paul McFedries), Excel Macros For Dummies (Dick Kusleika).
Content
- Intro
- Title Page
- Copyright Page
- Table of Contents
- 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
- Traversing the ribbon
- Customizing the Quick Access Toolbar
- Activating worksheets
- Exploring the Status Bar
- Entering and Editing Data
- Starting a task tracker
- Applying basic formatting
- Leveraging worksheet functions
- Handling Workbook Operations
- Saving Excel workbooks
- Opening existing workbooks
- Creating new workbooks
- Using Excel templates
- Managing Worksheet Tasks
- Renaming Excel worksheets
- Moving or copying worksheets
- Adding or deleting worksheets
- Hiding or unhiding worksheets
- Grouping worksheets
- Collaborating with Others
- Sharing static copies of workbooks
- Coauthoring simultaneously
- Commenting collectively
- Chapter 2 Carrying Out Basic Calculations
- Understanding Operators
- Analyzing arithmetic operators
- Comparing values with operators
- Combining text
- Linking cells across sheets and workbooks
- Exploring range operators
- Navigating sheet operators
- Interpreting workbook operators
- Exploring the Order of Operations
- Prioritizing with parentheses
- Elevating with exponents
- Multiplying and dividing with precision
- Adding and subtracting with ease
- Contrasting Cell References
- Recognizing relative references
- Mastering mixed references
- Applying absolute references
- Replicating Formulas
- Copying with keyboard shortcuts
- Automating with AutoFill
- Calculating Sums
- Computing amounts with AutoSum
- Totaling with SUM
- Improving the integrity of totals
- Adding numbers stored as text
- Adding or subtracting dates and times
- Analyzing Data with Statistical Functions
- Calculating averages with AVERAGE
- Calculating a weighted average
- Calculating conditional averages
- Finding the smallest and largest values
- Chapter 3 Formatting Cells and Worksheets
- Applying Basic Formatting Commands
- Exploring the Format Cells Dialog Box
- Formatting numbers
- Crafting custom number formats
- Applying Cell Styles
- Modifying predefined styles
- Creating custom styles
- Applying and identifying cell styles
- Transferring cell styles between workbooks
- Merging and Centering Cells
- Merging across columns
- Centering across a selection
- Wrapping Text
- Tapping into Text Boxes
- Inserting Images
- Enhancing Accessibility
- Adjusting Row Heights and Column Widths
- Hiding and Unhiding Rows and Columns
- Hiding rows and columns
- Unhiding rows and columns
- Grouping rows and columns
- Hiding and Unhiding Workbooks
- Modifying Page Setup
- Setting print ranges and page breaks
- Adding headers, footers, and print titles
- Chapter 4 Organizing Data and Creating Tables
- Organizing Data
- Using the Sort feature
- Getting started with basic sorting
- Resolving sorting mishaps
- Creating custom sorts
- Leveraging custom lists
- Applying sort options
- Removing duplicates
- Removing duplicates from a single column
- Eliminating duplicates across multiple columns
- Finessing the Filter feature
- Clearing filters
- Reapplying filters
- Calculating with SUBTOTAL
- Pairing SUBTOTAL with filters
- Differentiating SUBTOTAL
- Using the AGGREGATE function
- Summarizing lists with the Subtotal feature
- Formatting subtotals
- Removing subtotals
- Jump-starting insights with Quick Analysis
- Creating Tables
- Comparing data ranges to Excel tables
- Creating Excel tables
- Resizing tables
- Naming tables
- Navigating to and from tables
- Controlling table formatting
- Automating formulas and features
- Adding total rows
- Crafting calculated columns
- Leveraging structured references
- Disabling structured references
- Crafting self-expanding formulas
- Unpacking table quirks
- Converting a table to a data range
- Chapter 5 Navigating Worksheets and Workbooks
- Finding and Replacing Data within Cells
- Searching within worksheet cells
- Replacing text within worksheet 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
- Creating custom views
- Using the Custom Views Quick Access Toolbar shortcut
- Zooming In and Out
- Book 2 Working with Formulas and Functions
- Chapter 1 Tapping Into Formula and Function Fundamentals
- Working with Excel Fundamentals
- Understanding workbooks and worksheets
- Introducing the Formulas tab
- Working with rows, columns, cells, ranges, and tables
- Formatting your data
- Getting help
- Gaining the Upper Hand on Formulas
- Entering your first formula
- Understanding references
- Copying formulas with the Fill Handle
- Assembling formulas the right way
- Using Functions in Formulas
- Looking at what goes into a function
- Arguing with a function
- Nesting functions
- 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
- Selecting a function that takes no arguments
- Selecting a function that uses arguments
- Entering cells, ranges, named areas, and tables as function arguments
- Getting help in the Insert Function dialog box
- Using the Function Arguments dialog box to edit functions
- Directly Entering Formulas and Functions
- Entering formulas and functions in the Formula Bar
- Entering formulas and functions directly in worksheet cells
- 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
- Getting parentheses to match
- Avoiding circular references
- Mending broken links
- Using the Error Checking feature for formulas
- 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
- Going with the cash flow
- Formatting for currency
- Choosing separators
- Figuring Loan Calculations
- Calculating the payment amount
- Calculating interest payments
- Calculating payments toward principal
- Calculating the number of payments
- Calculating the number of payments with PDURATION
- Calculating the interest rate
- Calculating the principal
- Looking into the Future
- Depreciating the Finer Things in Life
- Calculating straight-line depreciation
- Creating an accelerated depreciation schedule
- Creating an even faster accelerated depreciation schedule
- Calculating a midyear depreciation schedule
- Measuring Your Internals
- Chapter 2 Performing Functional Math
- Adding It All Together with the SUM Function
- Rounding Out Your Knowledge
- Just plain old rounding
- Rounding in one direction
- Directional rounding, pure and simple
- Rounding to the multiple of choice
- Rounding to the next even or odd number
- 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
- The all-purpose RAND function
- Precise randomness with RANDBETWEEN
- Creating a Sequence
- Raising Numbers to New Heights
- Multiplying Multiple Numbers
- Summing Things Up
- Using SUBTOTAL
- Using SUMPRODUCT
- Using SUMIF and SUMIFS
- 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
- Isolating the day
- Isolating the month
- Isolating the year
- Converting a Date from Text
- Finding Out What TODAY Is
- Counting the days until your birthday
- Counting your age in days
- Determining the Day of the Week
- Working with Workdays
- Determining workdays in a range of dates
- Workdays in the future
- Understanding How Excel Handles Time
- Formatting Time
- Deconstructing Time with HOUR, MINUTE, and SECOND
- Isolating the hour
- Isolating the minute
- Isolating the second
- Finding the Time NOW
- Chapter 4 Manipulating Text with Functions
- Breaking Apart Text
- Bearing to the LEFT
- Swinging to the RIGHT
- Staying in the MIDdle
- Splitting up
- Finding the long of it with LEN
- Putting Text Together
- Putting text together with CONCATENATE
- Putting text together with TEXTJOIN and a delimiter
- Changing Text
- Making money
- Turning numbers into text
- Repeating text
- Swapping text
- REPLACE
- SUBSTITUTE
- Giving text a trim
- Making a case
- Comparing, Finding, and Measuring Text
- Going for perfection with EXACT
- Finding and searching
- FIND
- SEARCH
- Book 4 Analyzing Data
- Chapter 1 Using Basic Data Analysis Techniques
- What Is Data Analysis, Anyway?
- Cooking raw data
- Dealing with data
- Building data models
- Performing what-if analysis
- Analyzing Data with Conditional Formatting
- Highlighting cells that meet some criteria
- Showing pesky duplicate values
- Highlighting the top or bottom values in a range
- Analyzing cell values with data bars
- Analyzing cell values with color scales
- Analyzing cell values with icon sets
- Creating a custom conditional formatting rule
- Editing a conditional formatting rule
- Removing conditional formatting rules
- Summarizing Data with Subtotals
- Grouping Related Data
- Consolidating Data from Multiple Worksheets
- Consolidating by position
- Consolidating by category
- Chapter 2 Working with Data Analysis Tools
- Working with Data Tables
- Creating a basic data table
- Creating a two-input data table
- Skipping data tables when calculating workbooks
- Analyzing Data with Goal Seek
- Analyzing Data with Scenarios
- Creating a scenario
- Applying a scenario
- Editing a scenario
- Deleting a scenario
- Optimizing Data with Solver
- Understanding Solver
- Appreciating the advantages of Solver
- Knowing when to use Solver
- Loading the Solver add-in
- Optimizing a result with Solver
- Adding constraints to Solver
- Saving a Solver solution as a scenario
- 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
- Using Recommended Charts
- Building charts directly
- Deconstructing and Customizing Charts
- Customizing charts
- Maintaining chart data
- Moving and resizing charts
- Transferring chart formatting and elements
- Creating and applying chart templates
- Managing templates
- 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
- Building a PivotTable from Microsoft Query
- Building a PivotTable from a new data connection
- Refreshing PivotTable Data
- Refreshing PivotTable data manually
- Refreshing PivotTable data automatically
- Adding Multiple Fields to a PivotTable Area
- Pivoting a Field to a Different Area
- Grouping PivotTable Values
- Grouping numeric values
- Grouping date and time values
- Grouping text values
- Filtering PivotTable Values
- Applying a report filter
- Filtering row or column items
- Filtering PivotTable values
- Filtering a PivotTable with a slicer
- Chapter 3 Performing PivotTable Calculations
- Messing Around with PivotTable Summary Calculations
- Changing the PivotTable summary calculation
- Trying out the difference summary calculation
- Applying a percentage summary calculation
- Adding a running total summary calculation
- Creating an index summary calculation
- Working with PivotTable Subtotals
- Turning off subtotals for a field
- Displaying multiple subtotals for a field
- Introducing Custom Calculations
- Formulas for custom calculations
- Checking out the custom calculation types
- Understanding custom calculation limitations
- 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
- Understanding PivotChart pros and cons
- Taking a PivotChart tour
- Understanding PivotChart limitations
- Creating a PivotChart
- Creating a PivotChart from a PivotTable
- Embedding a PivotChart on a PivotTable's worksheet
- Creating a PivotChart from an Excel range or table
- Working with PivotCharts
- Moving a PivotChart to another sheet
- Filtering a PivotChart
- Changing the PivotChart type
- Adding data labels to your PivotChart
- Sorting the PivotChart
- Adding PivotChart titles
- Moving the PivotChart legend
- Displaying a data table with the PivotChart
- Book 6 Reporting and Querying Data
- Chapter 1 Introducing Power Pivot
- Understanding the Power Pivot Internal Data Model
- Linking Excel Tables to Power Pivot
- Preparing Excel tables
- Adding Excel tables to the data model
- Creating relationships between Power Pivot tables
- Managing existing relationships
- Using the Power Pivot data model in reporting
- Chapter 2 Advanced Moves with PivotTables
- Creating a PivotTable
- Changing and rearranging a PivotTable
- Adding a report filter
- Keeping the PivotTable fresh
- Customizing PivotTable Reports
- Changing the PivotTable layout
- Customizing field names
- Applying numeric formats to data fields
- Changing summary calculations
- Suppressing subtotals
- Removing all subtotals at one time
- Removing the subtotals for only one field
- Removing grand totals
- Showing and hiding data items
- Hiding or showing items without data
- Sorting the PivotTable
- Understanding Slicers
- Creating a Standard Slicer
- Getting Fancy with Slicer Customizations
- Size and placement
- Data item columns
- Miscellaneous slicer settings
- 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
- Creating your first calculated column
- Formatting calculated columns
- Referencing calculated columns in other calculations
- Hiding calculated columns from end users
- Utilizing DAX to Create Calculated Columns
- Identifying DAX functions that are safe for calculated columns
- Building DAX-driven calculated columns
- Sorting by month in Power Pivot-driven PivotTables
- Referencing fields from other tables
- Nesting functions
- Understanding Calculated Measures
- Creating a calculated measure
- Editing and deleting calculated measures
- Chapter 5 Meeting Power Query and Its Connection Types
- Power Query Basics
- Starting the query
- Understanding query steps
- Refreshing Power Query data
- Managing existing queries
- Understanding Column-Level Actions
- Understanding Table Actions
- Importing Data from Files
- Getting data from Excel workbooks
- Getting data from CSV and text files
- Getting data from PDF files
- Getting data from folders
- Importing Data from Database Systems
- A connection for every database type
- Relational and OLAP databases
- Azure databases
- ODBC connections to nonstandard databases
- Getting data from other data systems
- Walk-through: Getting data from a database
- Managing Data Source Settings
- Data Profiling with Power Query
- Data Profiling options
- Data Profiling quick actions
- Book 7 Creating Dashboards and Reports
- Chapter 1 Getting in the Dashboard State of Mind
- Defining Dashboards and Reports
- Defining reports
- Defining dashboards
- Preparing for Greatness
- Establish the audience for, and purpose of, the dashboard
- Delineate the measures for the dashboard
- Catalog the required data sources
- Define the dimensions and filters for the dashboard
- Determine the need for drill-down features
- Establish the refresh schedule
- A Quick Look at Dashboard Design Principles
- Rule number 1: Keep it simple
- Don't turn your dashboard into a data repository
- Avoid the fancy formatting
- Limit each dashboard to one printable page
- Use layout and placement to draw focus
- Format numbers effectively
- Use titles and labels effectively
- Chapter 2 Building a Super Model
- Understanding Data Modeling Best Practices
- Separating data, analysis, and presentation
- Starting with appropriately structured data
- Spreadsheet reports make for ineffective data models
- Flat data files lend themselves nicely to data models
- Tabular datasets are perfect for PivotTable-driven data models
- Avoiding turning your data model into a database
- Using tabs to document and organize your data model
- Testing your data model before building reporting components on top of it
- Finding Excel Functions That Really Deliver
- The VLOOKUP function
- VLOOKUP basics
- Applying VLOOKUP formulas in a data model
- Using data validation drop-down lists in the data model
- The HLOOKUP function
- HLOOKUP basics
- Applying HLOOKUP formulas in a data model
- The SUMPRODUCT function
- SUMPRODUCT basics
- A twist on the SUMPRODUCT function
- Applying SUMPRODUCT formulas in a data model
- The CHOOSE function
- CHOOSE basics
- Applying CHOOSE formulas in a data model
- Using Smart Tables That Expand with Data
- Converting a range to an Excel table
- Converting an Excel table back to a range
- Introducing Dynamic Arrays
- Getting the basics of dynamic arrays
- Understanding spill ranges
- Referencing spill ranges
- Exploring Dynamic Array Functions
- The SORT function
- The SORTBY function
- The UNIQUE function
- The FILTER function
- The XLOOKUP function
- Chapter 3 Dressing Up Your Data Tables
- Table Design Principles
- Use colors sparingly
- De-emphasize borders
- Use effective number formatting
- Subdue your labels and headers
- Getting Fancy with Custom Number Formatting
- Number formatting basics
- Formatting numbers in thousands and millions
- Hiding and suppressing zeroes
- Applying custom format colors
- Formatting dates and times
- Chapter 4 Formatting Your Way to Visualizations
- Enhancing Reports with Conditional Formatting
- Applying basic conditional formatting
- Using the Highlight Cells Rules
- Applying Top/Bottom Rules
- Creating Data Bars
- Applying Color Scales
- Using Icon Sets
- Adding your own formatting rules manually
- Showing only one icon
- Showing Data Bars and icons outside of cells
- Representing trends with Icon Sets
- Using Symbols to Enhance Reporting
- Wielding the Magical Camera Tool
- Finding the Camera tool
- Using the Camera tool
- Enhancing a dashboard with the Camera tool
- Enhancing Excel Reports with Shapes
- Creating visually appealing containers with shapes
- Layering shapes to save space
- Constructing your own infographic widgets 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
- Creating a bullet graph
- Adding data to your bullet graph
- Final thoughts on formatting bullet graphs
- Creating qualitative bands
- Creating horizontal bullet graphs
- 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
- Examining the macro
- Editing the macro
- Testing the macro
- Comparing absolute and relative macro recording
- Recording macros with absolute references
- Recording macros with relative references
- Understanding Macro Security
- Macro-enabled file extensions
- Trusted documents
- Trusted locations
- Storing and Running Macros
- Storing macros in your Personal Macro Workbook
- Assigning a macro to a button and other form controls
- Placing a macro on the Quick Access Toolbar
- Exploring Macro Examples
- Building navigation buttons
- Dynamically rearranging PivotTable data
- Offering one-touch reporting options
- Chapter 2 Getting Cozy with the Visual Basic Editor
- Working in the Visual Basic Editor
- VBE menu bar
- VBE toolbars
- Project Explorer
- Code pane
- Immediate window
- Working with the Project Explorer
- Adding a new VBA module
- Removing a VBA module
- Working with a Code Pane
- Minimizing and maximizing windows
- Getting VBA code into a module
- Customizing the VBE
- Editor tab
- Auto Syntax Check
- Require Variable Declaration
- Auto List Members
- Auto Quick Info
- Auto Data Tips
- Auto Indent
- Drag-and-Drop Text Editing
- Default to Full Module View
- Procedure Separator
- Editor Format tab
- Code Colors
- Font
- Size
- Margin Indicator Bar
- General tab
- Docking tab
- Chapter 3 The Anatomy of Macros
- A Brief Overview of the Excel Object Model
- Understanding objects
- Understanding collections
- Understanding properties
- Understanding methods
- A Brief Look at Variables
- Understanding Event Procedures
- Worksheet events
- Workbook events
- Error Handling in a Nutshell
- On Error GoTo SomeLabel
- On Error Resume Next
- On Error GoTo 0
- Chapter 4 Working with Workbooks
- Installing Macros
- Event macros
- Personal Macro Workbook
- Standard 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
- EULA
