Save Time Using Excel 2010 PivotTables

For example, you'll see Sum of Sales, Count of Region, and so on. However, you can simply overwrite this name with your own. Just select the cell that contains the field you want to rename and type a new name.

Rename a field by typing over the original name. When you try to rename fields, you might run into a problem if you try to use exactly the same field name that appears in the data. For example, suppose you have a field called Sales in your source data. As a value field, it appears as Sum of Sales , but sensibly you want it to say Sales.

However, when you try to use Sales, Excel complains that the field already exists, and throws a "PivotTable field name already exists" error message. Excel doesn't like your new field name. As a simple workaround, just add a space to the end of your new field name. You can't see a difference, and Excel won't complain. Adding a space to the name avoids the problem.

There are many situations when it makes sense to add the same field to a pivot table more than once. It may seem odd, but you can indeed add the same field to a pivot table more than once. For example suppose you have a pivot table that shows a count of employees by department. The count works fine, but you also want to show the count as a percentage of total employees.

In this case, the simplest solution is to add the same field twice as a Value field:. Setting a field to show percent of total. The Name field has been added twice. Any time you add a numeric field as a Value in a pivot table, you should set the number format directly on the field. You may be tempted to format the values you see in the pivot table directly, but this is not a good idea, because it's not reliable as the pivot table changes. Setting the format directly on the field will ensure that the field is displayed using the format you want, no matter how big or small the pivot table becomes.

For example, assume a pivot table that shows a breakdown of sales by Region. When you first add the Sales field to the pivot table, it will be displayed in General number format, since it's a numeric field. To apply the Accounting number format to the field itself:. Setting format directly on a value field.

Whenever you see a total displayed in a pivot table, you can easily see and extract the data that makes up the total by "drilling down". For example, assume you are looking at a pivot table that shows employee count by department. You can see that there are 50 employees in the Engineering department, but you want to see the actual names.

To see the 50 people that make up this number, double-click directly on the number 50 and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers. You can use this same approach to see and extract data behind totals wherever you see them in a pivot table. Double click a total to "drill down". The 50 Enginners, extracted into a new sheet automatically.

Once you have one pivot table set up, you might want to see a different view of the same data. You could of course just rearrange your existing pivot table to create the new view. But if you're building a report that you plan to use and update on an on-going basis, the easiest thing to do is clone an existing pivot table, so that both views of the data are always available. There are two easy ways to clone a pivot table. The first way involved duplicating the worksheet that holds the pivot table.

If you have a pivot table set up in worksheet with a title, etc. Another way to clone a pivot table is to copy the pivot table, and paste it somewhere else. Using these approaches, you can make as many copies as you like. When you clone a pivot table this way, both pivot tables share the same pivot cache.

• 23 things you should know about Excel pivot tables.
• Murdock Cracks Ice (A Matt Murdock Murder Mystery)!
• Expand, collapse, or show details in a PivotTable or PivotChart.
• 5 Reasons to Use an Excel Table as the Source of a Pivot Table.
• Need more help?!

This means that when you refresh any one of the clones or the original all of the related pivot tables will be refreshed. How to clone a pivot table. After you've cloned a pivot table, you might run into a situation where you really don't want the clone to be linked to the same pivot cache as the original. A common example is after you've grouped a date field in one pivot table, refresh, and discover that you've also accidentally grouped the same date field in another pivot table that you didn't intend to change.

When pivot tables share the same pivot cache, they also share field grouping as well. Here's one way to unclone a pivot table, that is, unlink it from the pivot cache it shares with other pivot tables in the same worksheet:.

The Data Contains a Column with the Date & Time

Your pivot table will now use it's own pivot cache and will not refresh with the other pivot table s in the workbook, or share the same field grouping. The default layout for new pivot tables is the Compact layout. This layout will display "Row Labels" and "Column Labels" as headings in the pivot table. Remember that the job of the pivot table is to Filter and Calculate. It can only filter columns in a vertical manner , so all the revenue numbers need to be in a single column. This gives you a lot more flexibility when you add the Revenue field to the Values area of the pivot.

With this understanding of how the data should be formatted, you will begin to see the power of the pivot table. The Columns area works just like the Rows area. It lists the unique values of a field in the pivot table. The only difference is that it lists the values across the top row of the pivot table. The image above shows what the pivot table will look like if you put the Region field in the Columns area versus the Rows area. When the Region is in the Columns area, the values Sum of Revenue are placed horizontally on the sheet.

The Sum of Revenue calculation works the same as before. The pivot table must Filter and Calculate the Region field in the same way. The values area is calculating the Sum of Revenue. This is starting to look like a more useful report. The pivot table calculates the Sum of Revenue the exact same way as before, by filtering and calculating. Now it just has two fields to filter for when it calculates a cell in the Values area. In the image below the Total Revenue for the South region for Q2 is 1, How is this calculated?

As you add more fields to the Rows and Columns areas, the pivot table just filters on more fields, then calculates. Although the report may get complex looking, the calculation process is actually a very simple two step process; filter then calculate. In the example below I placed the Year field in the Filters area. This does NOT make any immediate changes to the pivot table. Instead, it gives me the option to apply a filter to the entire pivot table. The fields in the Filters area contain drop-down menus that allow you to apply a filter to the entire pivot table.

The following image shows how cell L79 is calculated in the pivot table. Each cell in the Values area represents an intersection of the fields in the Rows, Columns, and Filter areas. The source data is filtered based on this criteria, then calculates the Values area for the specified type of calculation sum, count, average, etc. Well, we can take everything we just learned and apply it here. The pivot table works in the same basic way. In the image above I put the Year and Qtr fields in the Rows area of the pivot table.

You can see how this instantly gives us a different report using the exact same source data. This flexibility is the beauty and power of the pivot table. First, I add the Year field to the Rows area. As we learned before, the pivot table will only list the unique values removes duplicates in the Rows area. Even though there are a lot of rows in the source data that contain and , the values , are only listed once in the pivot table. Next, I add the Qtr field to the Rows area below the Year field. The list of unique values in the child field Qtr repeats for each unique value in the parent field Year.

This continues to happen as you add more fields to the Rows area. The report above displays our quarterly sales trend for each year. It nests each quarter under each year in the Rows area, and makes for a very useful report. In the following image I reversed the fields in the Rows area of the pivot. Now Qtr is on top and Year is below it. The same principal still applies.

The unique values for each Year are listed below the unique values of each Qtr. This gives our report a totally different look, but it is still very useful. This report allows us to compare the year-over-year sales for each quarter. The same thing happens as you add multiple fields to the Columns area. When multiple fields are added to one area, the Values area of the pivot table still works the same way using the filter and calculate principal.

The source data will be filtered for all the filter criteria, then the Sum of Revenue is calculated. This happens for each cell in the Values area of the pivot table.

Download 200+ Excel Shortcuts

The Values area will still be filtered and calculated in the same way! I hope you have learned how pivot tables work by filtering and calculating your data. But the most important part is how the data is structured in a tabular format. Each field must have one unique characteristic that describes the data. The proper layout of the source data will really help you conceptualize your pivot table reports. This will also help you obtain your data in the proper format. Often times we are at the mercy of our IT department to provide us with data. Requesting your data in the correct tabular structure will allow you to create just about any report you can imagine with a pivot table.

As you learn the power of pivot tables, your reports will naturally get more advanced. You will be able to quickly mine and analyze your data in new ways, helping you discover trends that can make a big impact for your organization. I have only scratched the surface of what pivot tables can do. There is a seemingly infinite number of ways you can use pivot tables and pivot charts to answer business questions, and impress your boss.

With new tools like PowerPivot, there is no question that pivot tables are the way of the future for analyzing and presenting data. Understanding the concepts of how pivot tables work will really prepare you for any reporting tool outside of Excel. There are a ton of great ways to learn pivot tables depending on what type of learning environment suites you best. If you really want to immerse yourself and learn quickly, I recommend taking an online course. Here are two great resources. You can also download my pivot table checklist and all the practice Excel files that go with the videos.

This course is a great way to get started with pivot tables. It contains over 2 hours of instruction that is broken up into short minute videos. Learning pivot tables takes time.

With practice you will gain a better understanding of how pivot tables work, and how they can benefit you. Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. You would probably want to add another column to the table for runner name. This long table format would make it easy to format the data in a pivot table.

It also makes it easy to add entries to the table. Formatted a similar data set for track times as mm: The table rounds to seconds and does not display the true time in fractions of a second as entered in the data sheet. Hi Mitch, Did you change the number formatting for the values area inside the pivot table? If you want to send me your file I would be happy to take a quick look.

Thanks, This is very good information. I have the following question, however. I can solve this with a lookup table of the weekday names or by adding nested IF statements to get the weekday names, but do you have an easier method that would yield the names of the days? You can use the TEXT function to solve this. The following formulas will return the day names of the week. Click Here to Leave a Comment Below 70 comments. Michelle - December 6, Hi Jon, I have a similar dataset to Olivier who also left a comment and am hoping you have a good solution for me: Thanks a lot in advance Michelle.

Haseeb - November 2, Hi, I want to group pivot table results based on months but the months range from 25th to 25th not from or Soda - July 25, Hi, May you please advise me basically how to use [ ]. Bruno - June 27, Hello Jon. Ted Reilly - May 9, brilliant Thank you so much. Soumya Vullam - April 30, Hi Jon, This is an awesome way to optimize the time in finding the time range — I wanted to use the time range vlookup method to find out number of tickets raise to the system engineers in an hour gap time frame — i.

So I have created this range for the look up table — Min Time Range 6: Varun Sinha - February 26, thanks it will surely help in making my analysis more accurate.

Design the layout and format of a PivotTable - Excel

Peter Guy - January 22, Hi Jon, Hoping you can help with something simple that just does not want to give me results. Time1 Time2 Time3 Time4 Minimum Rachel - November 22, Hi Jon, I am currently analysing dietary data. Date Time Participant no. Lou G - October 12, I need to know this too…. I have a column of dates: Brian - July 19, I am looking for a faster way to reconcile two different data sets into one data set. Jon Acampora - July 24, Hi Brian, Yes, there are probably a lot of ways to automate this process. Phil - July 6, I am using Excel for Mac Michael - February 2, hello Jon, please i need more clarification on how to range time of advert within the time belt.

Iris - January 23, Yaay! I then want to know how many attended the same jobs within that same time period and how many were are other incidents at the same time but from the same station Thanks very much. Claire - December 20, Thanks very much that is useful as it confirms what I have done since first mailing you.

Number of Tickets created every 30 minutes between 9am to 9pm. JC - November 8, This is really fantastic stuff Jon! Is it possible to compute this average by some simplistic way within Pivot? Elisha Wan - November 7, I have one column with dates, and another column with numbers. Olivier - August 23, I have a similar problem and was hoping you have a good solution for me: Thanks a lot in advance Olivier. Mustafa - August 19, Hi Jon, Can you help me solve problem with time series in excel? Callum - August 9, Hi John, Great stuff, very useful!

Im having issue with one aspect of the above formula — the grouping technique. I created the below range Min time Range Callum Cleary - August 15, Works perfectly, thanks Jon! Jon Acampora - August 20, Awesome! Prem Singh - August 2, wow great function. Jon Acampora - August 8, Thanks Prem! Angele - May 26, You are just Awesome. Thank you very much. Jon Acampora - May 30, Thanks Angele! I think you are awesome too! Thank you, Vicky with regards to the first date of my first value in each column. Jason - April 7, How could I make 3 groups of times like 5am-8am, 8am-5pm, and 5pm to 11pm?

Jon Acampora - April 7, Great question Jason! Sundas - March 15, I need to group data and calculate averages for every 5 minutes. Please let me know if you have any questions. Jon Acampora - October 20, Great suggestion! Mike L - October 19, Bookmarked! Jon Acampora - October 19, Awesome!

Design the layout and format of a PivotTable

I really appreciate the positive feedback. Ken Sadeckas - October 15, Perfect. Catherine - October 14, Jon, The ideas you generate and solutions you create are quite amazing. Jon Acampora - October 14, You would probably want to add another column to the table for runner name.

Mitch - April 19, Jon, Formatted a similar data set for track times as mm: Jon Acampora - April 26, Hi Mitch, Did you change the number formatting for the values area inside the pivot table? Best Excel Tutorial - October 14, Wow! This is really clever. Thanks for this post. Jon Acampora - October 14, Thanks! Jon Acampora - October 14, Thanks Karen! Ken Sadeckas - October 14, Thanks, This is very good information.

Jon Acampora - October 14, Thanks Ken!