Microsoft 365 Excel All-in-One For Dummies

Name: Microsoft 365 Excel All-in-One For Dummies
Brand: Wiley
Price: 28.99 EUR
Availability: OnlineOnly

David H. Ringstrom Michael Alexander Dick Kusleika Paul McFedries Ken Bluttman(Autor*in)

Wiley (Verlag)

1. Auflage

Erschienen am 24. Juli 2025

819 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-394-34465-9 (ISBN)

28,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Personen

Inhalt

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

Systemvoraussetzungen

Als PDF speichern Als Link merken