GETTING GREAT RESULTS WITH EXCEL PIVOT TABLES, POWERQUERY AND POWERPIVOT
Get more out of your data with step-by-step tutorials for the Excel features you need to know Excel is still the most popular tool for organizing and analyzing data, and today's professionals are expected to have a high degree of fluency with it. Complex Excel tools like Pivot Tables, PowerQuer...
| Other Authors: | |
|---|---|
| Format: | eBook |
| Language: | Inglés |
| Published: |
Hoboken, New Jersey :
John Wiley & Sons
[2024]
Newark : 2024. |
| Edition: | 1st ed |
| Series: | Tech Today Series
|
| Subjects: | |
| See on Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009811313706719 |
Table of Contents:
- Cover
- Title Page
- Copyright Page
- Dedication Page
- Acknowledgments
- About the Author
- About the Technical Editor
- Contents at a Glance
- Contents
- Introduction
- What Does This Book Cover?
- Who Should Read This Book
- Reader Support for This Book
- Companion Download Files
- How to Contact the Author
- Chapter 1 Preparing the Data for an Excel Pivot Table
- What Is Data?
- What the Data Should Look Like
- Types of Data That Can Be Used in Excel Pivot Tables
- Using Excel Data
- Importing Data from External Data Sources into Excel
- Importing Data from a Text/CSV File
- Importing Data from an Access Database
- Importing Data from a Web Page
- Connecting to an ODBC Database
- Importing Data from a Different Excel Workbook
- Refreshing the Data
- Using Power Query to Clean the Data
- Using the Queries &
- Connections Window
- Analyzing the Table
- Splitting a Column
- Merging Columns
- Changing Data Types
- Removing Columns and Rows
- Undoing Steps in Power Query
- Replacing Values
- Trimming Spaces from the Beginning and End of the Data in a Column
- Combining Tables Using the Append Query
- Combining Tables Using the Merge Query
- Creating Subtotals in Your Data Using the GroupBy Query
- Using Power Query to Create Calculations
- Calculating Age/Years of Service
- Using the Built-in Date Functions
- Using the Built-In Time Functions
- Using a Custom Column for Other Calculations
- Calculating a Line Total
- Calculating the Days to Ship
- Calculating the New Total
- Changing or Viewing an Existing Custom Column
- Using a Conditional Column for Calculations
- Calculating a Reorder
- Changing or Viewing an Existing Conditional Column
- Summary
- Chapter 2 Summarizing and Presenting Data with a Pivot Table
- What Is a Pivot Table?
- Making a Pivot Table from Scratch.
- The PivotTable Fields Window
- Summarizing Data on One Field
- The Default Calculation in the Values Section
- Showing the Detail with a Drill Down
- Creating a Cross Tab or Cross Reference
- Adding More Layers of Detail to the Pivot Table
- Creating and Managing Groups
- Changing the Grouping Order
- Adding Groupings to Columns
- Creating Your Own Group
- Removing Your Own Group
- Using Recommended PivotTables and Analyzing Data
- Creating a Pivot Table Using Recommended PivotTables
- Creating a Pivot Table Using Analyze Data
- Making the Pivot Table Look Better
- Formatting Numbers
- Managing Subtotals
- Calculating Grand Totals
- Working with Blank Rows
- Changing the Layout
- Applying Styles
- Replacing Spaces and Other Options
- Using Conditional Formatting
- Summary
- Chapter 3 Using Calculations in Pivot Tables
- Using Built-In Calculations in Your Pivot Table
- Adding Calculation Fields to the Pivot Table
- Changing the Calculation Type of a Field
- Changing the Order of the Calculations
- The Calculations in the Summarize Values By Tab
- The Calculations in the Show Values As Tab
- Removing Calculations from the Pivot Table
- Delaying Calculations in a Pivot Table
- Changing the Way Errors Display in the Pivot Table
- Creating Custom Calculations
- Calculated Fields
- To Change or Delete a Calculated Field
- Calculated Items
- To Change or Delete a Calculated Item
- The Solve Order
- Documenting Your Calculated Items and Fields
- Limitations of Calculated Fields and Items
- Refreshing Values on the Pivot Table
- Manual Refresh
- Automatic Refresh
- Changing the Data Source
- Using Numbers from the Pivot Table in Other Calculations
- Enabling the GETPIVOTDATA Function
- Using the GETPIVOTDATA Function
- Summary
- Chapter 4 Sorting and Filtering the Pivot Table
- Sorting the Pivot Table.
- Sorting by a Column
- Sorting by a Row
- Putting the Pivot Table in Ascending Order
- Creating Your Own Sort Order
- Creating Your Own Sort Order by Dragging Cells
- Creating Your Own Sort Order by Using a Custom List
- Sorting a Pivot Table with Multiple Fields
- Filtering the Pivot Table
- Filter by Selection
- Clearing the Filter
- Using AutoFilters
- Label Filters
- Value Filters
- Top 10 Filters
- Date Filters
- Showing Items with No Data
- Using the Filters Section
- Creating New Sheets from the Field in the Filters Section
- Using Slicers and Timelines
- Creating a Timeline
- Updating Multiple Pivot Tables Using Slicers and Timelines
- Removing a Slicer or a Timeline Window
- Summary
- Chapter 5 Making the Pivot Table More Visual with Charts
- Creating a Chart from a Pivot Table
- PivotChart Features
- The Design Tab and the Format Tab for a Chart
- Managing and Modifying Chart Elements
- The Chart Title
- Data Labels
- Changing the Number Format of the Data Labels
- Changing the Data Labels to Display Vertically
- Trendlines
- Adding a Trendline to a Chart
- Creating a Forecast from a Trendline
- Formatting the Numbers of the Y-Axis or X-Axis
- Creating and Managing a Pie Chart
- Changing the Data Labels on the Pie Chart to Percents
- Separating a Section from a Pie Chart
- Changing the Pie Chart to a Doughnut Chart
- Creating a Combo Chart
- Creating and Using Chart Templates
- Summary
- Chapter 6 Summarizing Data by Date and Time
- Summarizing a Pivot Table by the Built-In Date Periods
- Summarizing a Pivot Table by a Single Period of Time
- Summarizing a Pivot Table by More Than One Time Period
- Showing/Hiding the Totals for Each Time Period
- Expanding/Collapsing Time Periods in the Pivot Table
- Using Slicers to Filter the Pivot Table by Date and Time.
- Displaying Earliest and Most Recent Dates
- Summarizing the Pivot Table by Number of Days
- Summarizing the Pivot Table Using Custom Date Calculations
- Summarizing the Pivot Table by Week
- Using the Day of the Week in a Pivot Table
- Using a Fiscal Period in a Pivot Table
- Summary
- Chapter 7 Creating a Pivot Table from Multiple Spreadsheets
- Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard
- Creating a Pivot Table Using the Data Model
- Adding, Changing, or Deleting Relationships
- Adding Additional Tables into the Data Model
- Creating and Managing Sets
- Changing or Deleting a Set
- Summary
- Chapter 8 Improving a Pivot Table with Power Pivot
- Activating the Power Pivot Add-In
- Using the Power Pivot Add-In
- Adding Tables to the Data Model
- Adding Formatted Excel Tables into the Data Model
- Adding Tables from Other Sources into the Data Model
- Joining Tables
- Changing, Deleting, and Managing the Relationships
- Creating a Pivot Table from the Power Pivot for Excel Window
- Adding Calculations to a Pivot Table Using the Data Model
- Adding a Calculated Column to a Table in the Data Model
- Adding a Measure into the Data Model
- Creating a KPI
- Summary
- Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables
- Looking at a Finished Dashboard
- Creating Your Own Dashboard
- Adding Charts to Your Dashboard
- Adding Slicers and Timelines to Your Dashboard
- Displaying Totals and Percent of Totals
- Working with Form Controls, Macros, and VBA Code
- Adding Finishing Touches to Your Dashboard
- Hiding Sheets
- Hiding the Pivot Table, Gridlines, Column Headings, and the Formula
- Summary
- Index
- EULA.
