Issues with PIVOT TABLES - fixing common errors in 6 minutes (Microsoft Excel for beginners)
ฝัง
- เผยแพร่เมื่อ 16 ก.ค. 2024
- Pivot table errors can be so frustrating! In this beginners tutorial we take a look at some common issues you might experience when setting up PIVOT TABLES and how to fix these. If you are just learning how to use Pivot Tables and are running into problems, then this easy tutorial is for you. In just 6 minutes we look at common errors and how you can fix them!
UPDATE: check out our new guide to Pivot Tables, which contains more tips to help you resolve issues and a follow along file: • Beginners guide to Piv...
Learn Excel online for free: Our Excel for beginners tutorial will teach you all you need to know to take you from zero to Excel hero!
What Microsoft Excel tips and tricks do you want to learn? Let me know for future videos! I'll continue to develop tutorials covering Excel basics for beginners and will build up a catalogue of free Microsoft Excel course materials! :-)
Check out our other videos and playlists!
- Introduction tutorials for beginners: • Excel for beginners - ...
- Have you checked out our introduction to PIVOT TABLES video? • Learn Pivot Tables in ...
- Want to know about PIVOT CHARTS - check out this beginners tutorial: • Introduction to PIVOT ...
Or check out our FREE Excel course for beginners playlists. These are broken down into weeks by topic, such as Week 8 - Tables, conditional formatting and pivot tables: • FREE Excel course for ...
Thank You!!
Love Your videos ❤️❤️❤️❤️
Why thank you so much! :)
Thanks
You are welcome. Do let me know if there are any other videos you'd like to see! :)
Hello @Bitesize Excel , Thank you for this tutorial. I have this problem: when i try to refresh, i get his response" This command requires at least two rows of data source. You cannot use the command on a a selection in only one row", How can i resolve this.
Have you tried checking the source data. To do this, select the 'PivotTable Analyze' tab. Then click 'Change Data Source' and ensure that your whole table is selected (or that the correct data is corrected). I've seen this issue when there has been changes to the source data and this usually resolves it. Hope it helps!
Thanks for the response. I will try and let you know.
Hello @Bitesize Excel , Thank you for this tutorial 😊😊
I am trying to group my data by Decade but pivot table giving me this error -"the field you are moving cannot be placed inthta area of the report" each time . I calculated Decade from the Year field in my data using the formula LEFT(Year,3)&"0's". Could you please help , I want to have a look at the data by decades.
I've tried recreating the issue, but my pivot table does allow me to add the newly created field as a row or column, so I'm not 100% sure why yours isn't working. Is the formula in your source table?
Thanks for the video, I'm trying to turn on again the autofit, I enabled the option but it's not working. Hope you can help me.
Hhhhmmm - that is strange. When I right click the pivot table, then re-check the 'Autofit column widths on update' and click 'OK' the columns straight away start to resize again when I update my pivot table. I will have a think about what might cause the issue, but at the moment I don't seem to be able to re-create it.
How to get a row grand total if there are 2 different data in the column? (Something like the data in your 1st example. Add sum of grand total and sum of population)
Great question and one I hadn't yet covered in a video. In your Pivot Table toolbar - there is a calculation field. Under the 'Fields, items & Sets' option there is a 'Calculated Field' option, which allows you to add an additional column based on a calculation you set up (for your query you'd name your field in the first cell and then put the fields you want to add in the second with + signs between). I'll do a quick video on this and get it up by tomorrow - do check it out.
Generally when I want to do more complex calculations on my data and add new columns I'll use Power Pivot. I've put out a few videos on this in the last few weeks if you are interested.
@@BitesizeExcel Thank you so much for the solution. Much appreciated😁
Not a problem at all. I've now done a video on calculating rows and columns in Pivot Tables which will be a useful reference: th-cam.com/video/F0Ct3IeT1p8/w-d-xo.html
Do let me know if you have any other questions - as it's really useful for creating new content! :)
Hi, I have paid 1200Rs for ur 5 says workshop and PPT options but getting error that unable to join group since it's full
Dear Nilam. I don't have any paid courses or material available, only free resources on TH-cam, so this must be someone else's course. I'm sorry I'm not able to help, but hope you get this resolved.
My pivot table keeps piling up in one row or in the heading spaces. How to fix that
Any change of a screenshot or a bit more information on your issue? It may be something to do with your layout - I'd start with checking the report layout options in the design tab.
pivot table error 'an issue with the data model is preventing Microsoft Excel from opening this workbook. try restarting Excel'
There could be several reasons for this and probably not the easiest to work through / explain in an issue. Could be the version of excel you are using. Do you know if anyone else can open it? I'd suggest reading the following thread on Microsoft and see if any of the solutions help: answers.microsoft.com/en-us/msoffice/forum/all/an-issue-with-the-data-model-is-preventing/e55aaab9-b23b-4474-bb8f-85563abead4a
there is issue in grouping after apllying pivot it shows cannot group that selection
Are you trying to group a date range into weeks/months/years, etc? If so, the usual reason for getting the “Cannot group that selection” Error is that the the date column either is blank, contains invalid dates, text or errors. If you highlight the date column in you source data and use "Go to special) (CTRL+G, then special in the bottom left corner of the pop-up box). Then select constants, text and errors. This will select all the incorrect values and you can use formatting to colour them and then work through correcting them. Might do a short video on this next week. Hope it helps! :)
No I am applying grouping on numbers after pivot
And in my laptop it seems group selection is disabled
Hhmmm - I've seen the issue before with dates. What type of information are you trying to group?
Number
It might be the same issue - numbers stored as text. Try the same method I used for dates and see can you ID any.
I have 4 columns
Person
Quantity
Rework
Rework %
The precentage is never right, example
Person 1, quantity 2, reworked 1, 9.09%
Why is it not 50%.
All the others are wrong as well.
Sounds like it is giving the percentage of the column total. Have you added a calculated row to work out the rework %? If not might be worth checking this video out: th-cam.com/video/F0Ct3IeT1p8/w-d-xo.html
There is also a part 2 of that video that shows you how to remove a calculated column.
@@BitesizeExcel thankyou, when I am at work later I will check that out.
@@BitesizeExcel thanks for your help, I've got it sorted now. 👍
How to concatenae the tables
Happy to create a video on concatenation - do you have any specific examples of the type of information you would like to concatenate?