Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I am trying to create a Pivot Table in Excel. I have very limited knowledge of Excel and I am having a difficult time creating one.
Solved by K. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 25/09/2018 - 06:55
Welcome to ExcelChat!
Excelchat Expert 25/09/2018 - 06:56
I see that your question is about Pivot Table creation.
Excelchat Expert 25/09/2018 - 06:56
I will help you with the problem and explain the solution afterwards.
Excelchat Expert 25/09/2018 - 06:56
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 25/09/2018 - 06:56
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 25/09/2018 - 06:56
Could you please share the data based on which we need to create a Pivot Table? You can attach a file using the paper clip icon, or paste the data in the sheet which you may see near that chat
User 25/09/2018 - 06:57
It is confidential data that I am unable to share with you, but I can describe to you the things I am trying to create a Pivot Table for
Excelchat Expert 25/09/2018 - 06:58
Maybe you can replace the sensitive data with random number and texts?
Excelchat Expert 25/09/2018 - 06:58
Just few lines of data will be enough to understand the structure of your data
Excelchat Expert 25/09/2018 - 06:58
And explain how to make the whole solution
User 25/09/2018 - 06:59
I work for a mobile dental company and we are needing a table that has the school name we go to, the city the school is in, the last 3 school years we have went to (2015-2016, 2016-2017, 2017-2018) and the difference in the numbers of children seen each year in those schools.
Excelchat Expert 25/09/2018 - 07:01
So the table should show the following columns per row, right?
User 25/09/2018 - 07:01
Is there a phone number I can call? Typing all of this is kind of diffucult
Excelchat Expert 25/09/2018 - 07:01
School - City - Number of children in 2015/16 - Number of children in 2016/17 - Number of children in 2017/18
User 25/09/2018 - 07:02
yes that's right
Excelchat Expert 25/09/2018 - 07:02
Unfortunately no, sorry, we can provide the support only within this chat. Using another ways is forbidden for us.
User 25/09/2018 - 07:03
There should also be collumns comparing the school years 15/16 compared to 16/17 with the differences
User 25/09/2018 - 07:03
then 16/17 compared to 17/18
User 25/09/2018 - 07:06
like if we went up with the number of children seenor down
Excelchat Expert 25/09/2018 - 07:06
Could you send me the required structure of the output? And maybe some small sample of your data structure. I would just need to see the column name and the type of data which is stored in it, so you can just type there any numbers or text depending on the column
User 25/09/2018 - 07:06
ok how do i show you that
Excelchat Expert 25/09/2018 - 07:07
You can attach the file using the paper clip icon
Excelchat Expert 25/09/2018 - 07:09
When you see the pop up, please extend the session
User 25/09/2018 - 07:14
Here is a sample
[Uploaded an Excel file]
Excelchat Expert 25/09/2018 - 07:15
As I understand, this is the structure of required output result, right?
User 25/09/2018 - 07:15
I need the schools in alphabetical order but everything else needs to follow...ie all other information needs to follow when the schools go into order
User 25/09/2018 - 07:15
Thats the hardest part of trying to get his to work
User 25/09/2018 - 07:16
*this
Excelchat Expert 25/09/2018 - 07:18
Okay, I got your problem, I am working on its solution. I will write you back when I ready to share the result. If you want to provide a sample of the original data, feel free to share. Otherwise I will make it on the data which I made by myself, so it is not a big problem.
User 25/09/2018 - 07:20
Ok, sounds good.
User 25/09/2018 - 07:28
I have to leave my computer for just a couple of minutes. I hope this session doesn't expire before I get back.
Excelchat Expert 25/09/2018 - 07:28
Yes, we have 7 more minutes, and you will see the pop up to extend the session, so please do it within this time
Excelchat Expert 25/09/2018 - 07:34
Okay, I think I can start explaining how to build this table
Excelchat Expert 25/09/2018 - 07:34
I will send you the file with the basic data, and you will follow my steps to perform it
Excelchat Expert 25/09/2018 - 07:36
[Uploaded an Excel file]
Excelchat Expert 25/09/2018 - 07:36
Here is the file for which I will create a step-by-step guide
Excelchat Expert 25/09/2018 - 07:36
1. Open the file and select the table with data (A2:E6)
Excelchat Expert 25/09/2018 - 07:37
2. In the insert tab, select pivot table and specify the desired location of it (you can choose existing worksheet and select the location somewhere near the table to see both of them at once)
Excelchat Expert 25/09/2018 - 07:37
I placed it at H2
Excelchat Expert 25/09/2018 - 07:38
3. Move School and City fields to the rows
Excelchat Expert 25/09/2018 - 07:39
4. In the Design tab, click Report Layout -> Show in Tabular Form
Excelchat Expert 25/09/2018 - 07:40
5. Right click on the school column (in my case it is H2) and remove the tick from 'Subtotal School"
Excelchat Expert 25/09/2018 - 07:40
By this moment, your pivot table should consist of School Name and the city near it
Excelchat Expert 25/09/2018 - 07:41
6. Move the fields with the years data to the VALUES section in your Pivot Table
Excelchat Expert 25/09/2018 - 07:41
We will need 1 column of 2015/2016
Excelchat Expert 25/09/2018 - 07:42
2 columns of 2016/2017, 2 columns of 2017/2018
Excelchat Expert 25/09/2018 - 07:43
Sorry, in our case just 1 column of 2017/2018
Excelchat Expert 25/09/2018 - 07:43
Because we have just 3 years of data
Excelchat Expert 25/09/2018 - 07:43
The thing is that we just copy the data layout which you shared with me, so depending on how many columns with years data is required, the same amount of them is putted to the VALUES section
Excelchat Expert 25/09/2018 - 07:44
So in our case we put there 1 year of 2015, 2 years of 2016, and 1 year of 2017
Excelchat Expert 25/09/2018 - 07:45
7. On the Analyze tab, click Fileds, Items & Sets -> Calculated Field
Excelchat Expert 25/09/2018 - 07:45
8. Give it the required name, like Difference 2016/2015
Excelchat Expert 25/09/2018 - 07:45
9. In the formula field, press the =
Excelchat Expert 25/09/2018 - 07:46
Doubleclick on Field 2016/2017
Excelchat Expert 25/09/2018 - 07:46
Press minus
Excelchat Expert 25/09/2018 - 07:46
And Doubleclick on Field 2015/2016
Excelchat Expert 25/09/2018 - 07:46
In this way we calculate the difference between first two years
Excelchat Expert 25/09/2018 - 07:47
Press OK, and now in the VALUE section move the new column to be located after years 2015 and 2016, just as you showed in your file
User 25/09/2018 - 07:47
I can not get this chat to stay open and it is making me go crazy! haha....oh my goodness.
Excelchat Expert 25/09/2018 - 07:48
Yes.. it is probably quite hard if you have one monitor
Excelchat Expert 25/09/2018 - 07:48
But don't worry, at the end of the session you will able to access the chat log in your session history
User 25/09/2018 - 07:49
It keeps going to the left of the screen. I have 2 monitors, but it keeps shifting to the left and everytime I try to read what you're saying, I shifts over and drops me to the bottom. I am so frustrated.
Excelchat Expert 25/09/2018 - 07:49
Sorry for that experience.. technical disadvantages of this platform
Excelchat Expert 25/09/2018 - 07:49
We have just 6 minutes left, so I will finish the explanation if you don't mind
Excelchat Expert 25/09/2018 - 07:50
10. Repeat the process of adding the custom fields for each required period
Excelchat Expert 25/09/2018 - 07:50
11. In the VALUES section, move the field in the required order so that you had your years data, and then their difference
Excelchat Expert 25/09/2018 - 07:51
12. You can left click on any field at VALUES section (press the arrow), enter the Value Field Settings and change the name of it
Excelchat Expert 25/09/2018 - 07:53
13. Right click on the Grand Total (in my case this is H7), and press Remove Grand Total
Excelchat Expert 25/09/2018 - 07:55
That is it, these are the required steps to achieve the table which you shared in the file, it is not really complicated, but you have to go through the bunch of buttons in Excel. I hope that this guide will help you to achieve your result.
Excelchat Expert 25/09/2018 - 07:55
Thanks for coming to Excelchat, wish you a nice day! The log is available at the session history

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.