Excel - General Question on Pivot Table - Expert Solution

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

can not turn my data into a pivot table... reference invalid
Solved by I. B. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 06/08/2018 - 05:16
hello..
User 06/08/2018 - 05:16
hello0
User 06/08/2018 - 05:16
let me send you the file
Excelchat Expert 06/08/2018 - 05:16
ok..
User 06/08/2018 - 05:17
[Uploaded an Excel file]
User 06/08/2018 - 05:17
I want to make a pivot table of 2017 sheet and if I select all the data it says the reference is invalid when creating a pivot tbale
User 06/08/2018 - 05:17
I dont know why
Excelchat Expert 06/08/2018 - 05:18
hold on.. let me look at it..
Excelchat Expert 06/08/2018 - 05:19
do you want to make pivot table from A to K columns?
Excelchat Expert 06/08/2018 - 05:19
on 2017 sheet..??
User 06/08/2018 - 05:19
yes
User 06/08/2018 - 05:19
or at least tell me why its not working when I do it so I can do it mysekf
Excelchat Expert 06/08/2018 - 05:21
don't have any problem to make pivot table..
Excelchat Expert 06/08/2018 - 05:21
hold on..
User 06/08/2018 - 05:21
what the heck
User 06/08/2018 - 05:22
yeah I have no idea whats happening on my end
User 06/08/2018 - 05:22
this usually isn't difficult
User 06/08/2018 - 05:22
I was wondering if the file was corrupted or something
Excelchat Expert 06/08/2018 - 05:22
hold on..
Excelchat Expert 06/08/2018 - 05:22
let me check somthing..
User 06/08/2018 - 05:22
it says data source reference is not valid
User 06/08/2018 - 05:22
no idea why
Excelchat Expert 06/08/2018 - 05:24
you already have 2017 Pivot..
Excelchat Expert 06/08/2018 - 05:24
do you need this sheet..??
User 06/08/2018 - 05:24
yeah I do
User 06/08/2018 - 05:24
I want to make two separate pivots
User 06/08/2018 - 05:24
is that not possible?
Excelchat Expert 06/08/2018 - 05:24
yes possible but this pivot source it not this file..
User 06/08/2018 - 05:25
???
User 06/08/2018 - 05:25
what do you mean
Excelchat Expert 06/08/2018 - 05:25
current 2017 Pivot was made from [branchlessbanking (1).xlsx]2017'!$B:$M
User 06/08/2018 - 05:26
oh
User 06/08/2018 - 05:26
that makes sense
User 06/08/2018 - 05:26
but
User 06/08/2018 - 05:26
why does that effect me making a. pivot from 2017
Excelchat Expert 06/08/2018 - 05:26
no .. only problem is I can't unload new file after I made pivot from 2017 sheet because it exceed 20M limit..
User 06/08/2018 - 05:27
so what do you recommend I do
User 06/08/2018 - 05:27
just create a new document with the same Data and go??
Excelchat Expert 06/08/2018 - 05:27
let try other way..
User 06/08/2018 - 05:27
ok
User 06/08/2018 - 05:28
let me know
Excelchat Expert 06/08/2018 - 05:28
what version of excel are you using..
User 06/08/2018 - 05:28
16.15
User 06/08/2018 - 05:28
for mac
Excelchat Expert 06/08/2018 - 05:29
hold on..
User 06/08/2018 - 05:32
ok
Excelchat Expert 06/08/2018 - 05:33
I was trying upload file for you.. but no success..
User 06/08/2018 - 05:33
too large?
User 06/08/2018 - 05:33
do you want to send via email?
Excelchat Expert 06/08/2018 - 05:34
company is not allow exchange email address..
User 06/08/2018 - 05:34
ok
Excelchat Expert 06/08/2018 - 05:34
sorry about that..
User 06/08/2018 - 05:34
so lets do a solution
User 06/08/2018 - 05:34
thats fair not your policy
User 06/08/2018 - 05:35
frustrating though
Excelchat Expert 06/08/2018 - 05:35
ok..
User 06/08/2018 - 05:35
what did you do to fix it
User 06/08/2018 - 05:35
so I can follow the same steps
Excelchat Expert 06/08/2018 - 05:35
first select area A1:K89858
User 06/08/2018 - 05:35
ok
Excelchat Expert 06/08/2018 - 05:36
click Insert>PivotTable
User 06/08/2018 - 05:37
there is no pivotable option under insert
Excelchat Expert 06/08/2018 - 05:37
hold on..
Excelchat Expert 06/08/2018 - 05:37
how do you make pivot table in Mac..
User 06/08/2018 - 05:37
data --> summarize with pivot table
User 06/08/2018 - 05:38
I can do insert pivot chart
User 06/08/2018 - 05:38
but thats not what I wan
User 06/08/2018 - 05:38
want
Excelchat Expert 06/08/2018 - 05:39
I am trying to find out why you have error by following your steps..
User 06/08/2018 - 05:39
I do not know why it says the Data source is not valid
User 06/08/2018 - 05:40
on the Microsoft help site it says bc their may be brackets in the file name that are invalid
User 06/08/2018 - 05:40
but I dont think theres anything invalid about my file name
Excelchat Expert 06/08/2018 - 05:41
I think you are trying to making from other data source which is not found..
User 06/08/2018 - 05:41
I changed the file name and it work
User 06/08/2018 - 05:41
worked
Excelchat Expert 06/08/2018 - 05:42
did you changed name " [branchlessbanking (1).xlsx]2017'!$B:$M"
User 06/08/2018 - 05:43
I just added a space between branchless banking and it worked
Excelchat Expert 06/08/2018 - 05:43
I am happy that you figure it out..
Excelchat Expert 06/08/2018 - 05:45
[Uploaded an Excel file]
Excelchat Expert 06/08/2018 - 05:45
I eliminate 2008..and made pivot from 2017
Excelchat Expert 06/08/2018 - 05:46
I think you hare choosing other data source when you making pviot table..
Excelchat Expert 06/08/2018 - 05:47
if you want to make pivot from sheet.. you need assign area from that sheet..
Excelchat Expert 06/08/2018 - 05:47
and you will not have problem to making pivot table..
Excelchat Expert 06/08/2018 - 05:47
Do you have any other questions..??

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.