GETTING GREAT RESULTS WITH EXCEL PIVOT TABLES, POWERQUERY AND POWERPIVOT / Thomas Fragale.

Format:

Book

Author/Creator:

Fragale, Thomas, author.

Series:

Tech Today Series

Language:

English

Subjects (All):

Querying (Computer science).

Electronic spreadsheets--Computer programs.

Electronic spreadsheets.

Business--Computer programs.

Business.

Microsoft Excel (Computer file).

Physical Description:

1 online resource

Edition:

1st ed.

Place of Publication:

Hoboken, New Jersey : John Wiley & Sons, [2024]

Newark : John Wiley & Sons, Incorporated, 2024.

Summary:

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, PowerQuery, and PowerPivot can help you manage and report on data the way you need to. Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot offers a fresh look at how these tools can help you. Author and Microsoft Certified Trainer Thomas Fragale breaks down the topics into easy-to-use steps and screenshots, so you'll be able to put your advanced Excel skills into practice right away. Using Pivot Tables, PowerQuery, and PowerPivot, you can import, sort, transform, summarize, and present your data, all without having to be a programmer. This book takes the technical jargon out of using these features, so you can do your job more efficiently, bring value to your teams, and advance your career. The plain-English instructions inside will help anyone learn to get quick, meaningful results from your data, without having a degree in computing. Get easy-to-understand walkthroughs for analyzing data and creating dashboards in Microsoft Excel Learn how to organize data in Excel and use advanced features to find patterns and insights Summarize any kind of data faster and easier, leaving you more time for other tasks Turn raw numbers into new knowledge, reports, and charts that tell coworkers and customers what they need to know This book is great for anybody who has tons of raw data and needs to make sense of it. Managers, salespeople, finance professionals, marketers--along with anyone else who works with large amounts of data--will love this quick and easy guide to Pivot Tables, PowerQuery, and PowerPivot.

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 &amp

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

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

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

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

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

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

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

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

Index

EULA.

Notes:

OCLC-licensed vendor bibliographic record.

ISBN:

9781394243075

1394243073

OCLC:

1428895968