How To Create A Pivot Table In Excel 2017
There is no doubt that pivot tables are incredibly powerful, and save us a lot of time when summarizing data.
However, we also spend a lot of time doing repetitive tasks with pivot tables. Do you spend extra time doing any of the following?
- Searching for fields in the field list
- Formatting numbers
- Working with source data
- Changing layout settings
These small tasks can be very time consuming and frustrating!
Do You Spend Extra Time Building Pivot Tables?
If you use pivot tables frequently then you could be wasting a lot of time with some of these boring repetitive tasks, which takes time away from the fun stuff.
If you have pivot tables with more than 20-30 fields, then just scrolling through the field list to find the field you're looking for can take a lot of time.
And if you don't know the name of the field you are looking for, then you probably do something like the following:
- Find the sheet that contains the source data.
- Scroll horizontally through the columns until you find the field.
- Go back to the sheet that contains the pivot table.
- Find the field name in the field list.
- Add it to the pivot table.
- REPEAT for all the fields you want to add.
This process alone can be very time consuming.
How Can We Make This Faster?
My name is Jon, and I have been using Excel all day everyday for over 10 years. I love Excel! 🙂
And, I have probably built thousands of pivot tables. I use pivot tables for everything from a quick list of unique values, to reconciling accounts, to creating sophisticated interactive dashboards. They have a seemingly infinite number of uses, and they are just awesome!
When you start using pivot tables frequently, you begin to realize that some of Excel's built-in tools could be a little more efficient. There are certain tasks that just take way too much time with the built-in field list and various options menus.
Fortunately, VBA (the language used to write macros) allows us to program pivot tables to help automate some of these tasks. Over the years I have written a lot of macros that perform specific tasks to modify or update a pivot table.
However, I wanted to bring these automation routines into an application that everyone could use. This is where the PivotPal add-in was born.
PivotPal started as a simple form with a search box where you could quickly find a field in the field list, then add it to one of the areas in the pivot using keyboard shortcuts. That feature alone saved me a ton of time.
But the features quickly grew from there… I also wanted a way to quickly format numbers, instead of having to navigate through four menus just to change a number format.
I also wanted the ability to work on the pivot table from the source data sheet. Why should we have to constantly jump back and forth between two sheets that might not be located next to each other in the workbook?
The list goes on, and I spent over a year adding features to enhance PivotPal.
PivotPal Saves You Time
The goal of PivotPal is to save you a ton of time when working with your pivot tables. The add-in is packed features that make things faster and easier.
Let's take a look at some of the features.
Quickly Find Fields & Add Them to the Pivot
PivotPal contains a search box that allows you to quickly find fields in the field list. It works similar to a Google search, and the results are narrowed down as you type.
Once you find the field, you can use keyboard shortcuts or the mouse to add the field to any of the areas in the pivot table. You will never have to spend time scrolling through the field list again.
You can also select multiple fields in the list to quickly add or remove fields, and even change number formatting.
Click Here to Download PivotPal Today!
Number Formatting Made Easy
When formatting a number in the values area you typically have to step through 4 menus just to change a number format. There is no doubt that this takes extra time.
PivotPal contains a few features that relieve a lot of pain with this process.
First, PivotPal automatically applies number formatting. When you add a field to the values area with PivotPal, the number formatting for the field in the source data is detected and automatically applied to the pivot table. This means you have to take zero steps to format the field. It's automatically done for you!
If you the source data is not formatted, or you want to change the formatting of the field, you can do so in one step by selecting from the list of formats in the Formats drop-down menu on the PivotPal window. This list of number formats can be easily customized using the My Number Formats tool that is included with PivotPal.
The My Number Formats tool also allows you to apply your favorite number formats to any cell in the workbook. It is not limited to pivot tables.
If you are constantly adding custom number formats like ($1.0M) to your workbooks, then this tool will allow you to do that in one click.
New Feature: Automatic Number Formatting
I've added a new feature that automatically formats the numbers in the Values area of the pivot table when using the built-in Pivot Table Fields List.
The Automatic Number Formatting feature detects the number formatting that is used in the source data range, and automatically applies it to the pivot table when a field is added to the values area. If the source data is not formatted, or does not exist in the workbook, then a default number format that you specify is applied to the pivot table.
Either way, you will never have ugly looking pivot tables with the General number format.
Click Here to Download PivotPal Today!
Working With Your Source Data
When building pivot tables it is only natural to spend a lot of time working with the source data. This includes tasks like:
- Finding fields
- Investigating blanks and errors
- Converting data types (dates, numbers, text)
- Writing formulas
- Changing the data source range
- Drilling down on the details of a number in the pivot
The built-in menus don't allow you to work with source data while building the pivot table. Unless your pivot table is on the same sheet as the source data, you spend a lot of time flipping back and forth between the pivot sheet and source data sheet.
PivotPal solves some of these problems with a few great features.
Build Pivots from the Source Data Sheet
Often times you will know what field you want to add to a pivot table, but you don't know the name of the field. This is especially true when you are working with data that is exported from a database system. The field names aren't always descriptive.
Let's say you want to add the field that contains the product groupings, but you don't know the name of the field.
With PivotPal you would simply press the GoTo Data button on the PivotPal window. This will take you to the source data sheet, and the PivotPal window will remain open.
You can then browse through your sheet or use Excel's Find menu to find the field.
Selecting any cell in the column of the field will automatically select that field in the PivotPal window.
Now all you have to do is press one of the Area buttons on the PivotPal window to add the field to your pivot table. It's that easy!
Press the GoTo Pivot button to return back to your pivot table and view the results.
Click Here to Download PivotPal Today!
Filtering Your Source Data
When you double-click on a cell in the values area of the pivot (right-click> Show Details…), Excel adds a new sheet to your workbook that contains the rows of data that make up that slice (intersection) of the pivot. I call these drill-down sheets.
This can be handy information if you are trying to investigate a number, but your workbook can quickly become cluttered with drill-down sheets. The other problem is that the drill-down sheet is not formatted, and usually difficult to read.
PivotPal has a Filter Source button that will show the exact same results by applying filters to your source data sheet.
This means you can investigate the actual source data, instead of a drill-down sheet. If you need to make changes to the data, then it is already filtered for the rows you want to see.
And, your workbook will NOT become cluttered with drill-down pages.
This feature has a lot of great benefits, and you will find yourself using it all the time.
Click Here to Download PivotPal Today!
Layout Settings Done Your Way
Did you know that there are over 30 different layout and options settings for pivot tables? We spend a lot of time changing these settings for each pivot table we create.
Often times we are applying the same settings over-and-over again. Do you find yourself doing a process similar to this:
1. Change layout to Tabular format
2. Turn Subtotals off
3. Display zeros for Blanks
4. Insert blank line after item
Everyone has their own preferences, but applying these settings can take extra steps.
PivotPal has a feature called My Pivot Layouts that allows you to create profiles with your favorite settings, then quickly apply them to any pivot table with the click of a button.
Just selecting the layout profile you want to apply, and all the settings will be automatically adjusted for you. It will save you a ton of time!
Click Here to Download PivotPal Today!
Additional Features
PivotPal is packed with features to help you get your job done faster.
- Source Range Warning – Have you ever forgotten to update the data source range after adding new data? I have, and it can be an embarrassing mistake. PivotPal will warn you when there are more rows in your source data then what is currently set as the source range in your pivot table. In other words, it will save your butt from embarrassing mistakes. 🙂
- Apply Number Formats to Any Workbook – Your workbook does not have to contain pivot tables to use the My Number Formats tool. You can apply your custom number formats to cells in any workbook.
- Jump to any Pivot Table – There is a drop-down menu at the top of the PivotPal window that contains a list of all the pivot tables in the workbook. It also tells you what sheet the pivot table is on. This makes it easy to jump to the other pivot tables using the GoTo Pivot button. You can also quickly check to see which fields are used on other pivot tables by just selecting from the list. The field list will be loaded for the selected pivot table.
- Pivot Info List – The button to the right of the Pivot Drop-down opens a window that contains a list of all the pivot tables in the workbook. This list contains additional details about the pivot table including the pivot cache index number, source range, source type, and total pivot cache count. This feature will be enhanced in the future to include a lot more.
- Works with PowerPivot – PivotPal will work with your Power Pivot models too. This saves a lot of time when searching for fields in many different tables.
- Function and Calculation Types – PivotPal contains two drop-down menus for the function (sum, count, average) and calculation types (% of totals, rank, etc.). This allows you to quickly change the calculation type of the value field(s) you have selected in the list. Yes, you can apply these settings to multiple fields at the same time. When selecting a calculation type, the name of the field is automatically changed to describe the calculation. Instead of "Sum of Revenue2", the field will be named "Revenue % of Total". This is something that Excel does NOT do.
Change How You Work With Pivot Tables
PivotPal will help increase your productivity, and alleviate some of the frustrations you face when working with pivot tables.
PivotPal is meant to compliment the built-in menus in Excel. It won't completely replace these menus, but instead it provides a quicker solution for some of the tasks we do the most.
This time savings will allow you to spend more time on creative and analytic (fun) part of your job. Spending time on this type of work will make a bigger impact for your organization and your career.
What Others Are Saying About PivotPal
Wow!!! Pivot Pal makes customizing pivot tables super easy and fast. It also saves your custom setups for reuse in new pivot tables.Jim F
PivotPal is a great tool! It saves me a ton of time when working on unfamiliar data when I am creating a Pivot Table by allowing me to construct the Pivot while looking at the source data. That is an extremely helpful feature.
Between constructing Pivot Tables while looking at the source data and being able to add more than one item at a time to a Pivot Table field saves me quite a bit of time.
Another great feature is being able to instantly filter the source data to match what is currently filtered in the Pivot Table takes away the hassle of drill down sheets and deleting drill down sheets when I am finished analyzing the data.Peter R
I really appreciate having been able to review the PivotPal ahead of its launch and its had a serious workout already on a Dashboard I've created for my company's global use.
Using the PivotPal has been quite a 'light bulb moment' in that it has helped to really cement my understanding of how Pivots in MS Excel work – quite apart from making Pivot Tables much easier and faster to set up, especially for multiple tables, it's also helped me understand much better MS Excel's high level Pivot Table workflow.
As well as being feature rich PivotPal also uses the valuable screen and workspace real estate much more efficiently than Excel's native Pivot management which has been a real benefit whilst developing my spreadsheet Dashboard in meeting rooms and on my sofa using just my laptop without the luxury of twin monitors back at my desk.
I work for a leading automotive manufacturer with a global manufacturing and design footprint and your tool has been key in developing a new dashboard we've just agreed to at our most senior level to use globally as was one of our new key metrics in our Product Development teams. So it was very timely to have an easy to manage and understand PivotPal tool to help!David W
PivotPal is now a part of our Hero Tools Suite
The Hero Tools Suite combines all of our add-ins into one Excel add-in with over 100 features that will save you time with your everday Excel tasks.
How To Create A Pivot Table In Excel 2017
Source: https://www.excelcampus.com/pivotpal/
Posted by: branhamfars1969.blogspot.com
0 Response to "How To Create A Pivot Table In Excel 2017"
Post a Comment