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.
