Thanks! I presume you might've added this annexure video following feedback from one of your viewers. Again Good job and looking forward to more such informative contents. What makes your videos more useful and special to the viewers like me is that you don't hesitate to share the sample files which is very important for any serious learners who wants to practice hands-on. I would also kindly request if you could post videos on Power Query, Power Pivot, DAX formulas etc. in future. Thanks again. Cheers!
Thanks again - super useful feedback. And yes - I did add the video following a query, as I do try to listen to feedback and tailor videos on that basis. I do have a few videos on power query, which I have added to a playlist, and do have a number of future videos planned on the topics you have suggested.
I am following several channels which talks about MS Excel. But frankly I haven't seen any of them discussing about this particular topic of "calculated rows and columns". Truly it's a new knowledge which I feel elated to know for the first time and surely it will add value to my future projects, and yes I have made this channel to my subscription list without any second thought. Thanks @Bitesize Excel
Thank you so much for the lovely feedback - it really means a lot to me as a relatively new channel! Do let me know if there is anything you'd like to see in future videos. There is also a second video on this topic - which shows you how to remove a calculated row or column (as I had forgotten to discuss in this video). I'll put the link in a pinned post.
I was looking for how to add multiple columns video in Pivot table for an hour but no lucky until finding this helpful video that solved out my issue. I have already watched part 2 that are great. Thanks. 😃
Really happy that you found this video helpful! I hadn't seen any other videos on this either, but find it such a useful trick to know - so thought it would make a good video topic. Do let me know if there is anything else you find helpful, as always good to know for future videos! 😄
Some constructrive feedback; edit out when you tidy your sheet. It has no value and is very frustrating to sit through. You get to the subject of the actual video 1min 21 seconds into the video. I would recommend Starting the video there, preface quickly that they need to have a pivot table set up (perhaps reference a different video, of give a timestamp to skip the pivot table setup) and then start with the content immediately. Good quality, thanks.
Thanks for the feedback, really helpful for future reference - did add some timestamps to this video so that people could skip the intro bits. I'll also consider the other aspect, although have had feedback the other way that some people like the setup intro.... Maybe say at the beginning in future videos to skip to the timestamp if you already have a pivot table setup!
When you click in you pivot table - you should see a tab at the top called 'design'. In there is a drop down called 'grand totals' where you can add grand totals for rows and columns. I don't currently do zoom sessions (may be something I could consider), but do take video requests. There are lots of useful pivot table tips in the videos in my tables and pivot tables playlist: th-cam.com/play/PLJWCoDBpUWeNfDpsZ1eZTilyHHnUSq7Y0.html The Beginner's Guide to pivot tables and introduction to pivot table videos are probably particularly useful. 😀
It matters what field or cell you currently have selected in your pivot table. If you select something that is in the 'values' section you would will get the calculated field option, but the calculated item will be greyed out. You need to select something from the 'Rows' section of the pivot table and then should be able to select calculated item. Hope this helps! 😄
Working on a spreadsheet for my job. New to PT’s and frustrated with formulas and PT errors. Goal: show the average transit time in days by destination from port to warehouse. Port date and Warehouse date are all in date format and a column is already created with the port date subtracted from warehouse date. How might I ask pivot table or excel to ignore blank cells when a shipment has not yet arrived? The error is giving a number like 40771 or 453759. Thanks!!
The number is likely you taking a zero value away from the other date - which will give you a number (the date in number format). You could correct this in your base data or do you want to correct in just your pivot table?
Hello, Question about Pivot Tables. I have pivot table that has a column of parts, the next column is years (so row 1 would be 2001, row 2 would be 2002 and so on, and it would be like this for all the parts). I want to see the difference from 2001 to 2002 is a sort of subtotal like view. so for part number 1, i would have three rows, row one would be sales for 2001, row 2 would be sales for 2002, and row 3 would be the difference in 2002 to 2001. is this possible and how?
You can get the % change in a new column by adding a second total column and then using % difference from column. I can do a quick video over the weekend if you are interested in seeing that method.
Where is your data coming from? If it is coming from power pivot (data model) I've found that calculated field doesn't work. In a normal pivot table I don't think there is a way of having both grand total and grand average (but welcome any solutions if anyone is aware of a way) - you can work a solution with power pivot or measures, but I'll probably need to do a video to work through that as it's a bit more complicated.
Do you have an example of one that isn't working right and I might be able to suggest how to fix it? My usual method for troubleshooting complicated formulas is to break it down into steps and see that each step is working as I expect until I can build up to the full formula.
Hi, I have table with: Client name; Country; Credit amount in November; Credit amount in December; Changes (+/-) Nov/Dec. Can I made pivot table with formula which shows only inflow (positive changes in Dec) in each country?
Yes you can. In your calculated field you could use the following formula: =IF('Changes (+/-) Nov/Dec' >0,'Changes (+/-) Nov/Dec',) This will give you '0' values for any row that was negative. You can then use the value filters (in the drop down on the top left of your pivot table) to filter your pivot table by anything greater than 0 (if that is what you want). You can also automate this also via power query rather than using pivot tables - see my videos on power query if you want to get a feel for the great capability of power query in excel.
Can you help me with two more? 1. I have added a slicers with date. But the dates are not in order. It has been shuffer. The actual data is in correct order. Could you please let me know how to correct it in the slicer? 2. I have added a line charge and included a slicer which contains list of team member names. I have given black color to the line however, when i select any name from the slicer, line color is going to blue for some of the name. Could you please help with this? I'm doing a project and I have few hours left to complete it. Your help is greatly appreciated. Thanks!
Good afternoon D Z. I'll see if I can help. 1. On your first query - there are two things that might help. First make sure your dates are formatted as dated in your source data. Then you can try one of the following. (a) when you input your slicer, right click on the top of the box and there should be an option in the list to sort from oldest to newest (or vica versa). (b) Alternatively you could try insert a timeline instead of a slicer (option is below the insert slicer on your PivotTable Analyse tab - it should give you the option of creating for any date or time data. This gives a box with a timeline or your day on it and in the top right corner you can set it to days, months, quarters or years. You can then select the appropriate time period you want to view. 2. I'm not fully clear on the issue for this one, is it the slicer style that is changing colour in parts. If you could provide a bit more information I will see can I help. Hope the first part is useful and best of luck! :-)
@@BitesizeExcelThank you so much for the quick response. I have checked the format and confirmed that everything is in the correct date format. I have tried ascending and descending sorting but the dates are not still in order. 2nd option is nice but I have built the dashboard and layouts completely based on thia slicer design. Any other option to fix it. On the 2nd one - I have inserted a line chart and added a slicer with list of teams members. I have given the color of the line as black. When i select some of the team members, the color of the line is automatically getting changed to blue. And that is only for some of the team members, for others it is still black. Not sure what is happening
Could you maybe share a few screenshots or an anonymised example to bitesize.excel@gmail.com so that I can get a better understanding of the issue? Or share a version via Google Drive.
Great question and probably one I should have covered in the video (will do a future update on this). If you click onto the field you want to remove, then go back to your Pivot Table analyse tab. Select the drop down y 'Fields, Items & Sets' and select either your 'Calculated Item...' or 'Calculated Field...' option (whichever you wish to remove). Then there is a little drop down next to the 'Name:' box and in this the fields you have added previously should be listed. Select the one you want to remove and hit 'Delete'. I hope this helps and do let me know if you have any further questions.
hi, thank u for this I have been using pivot tables for a bit and they are time saving at times. I have a database of projects their budgets and costs. I know that I can create custom subtotals and custom calculated columns, but was wondering if i could add a line underneath a subtotal to do a calculation e.g. i have one budget for the whole project but several categories of expenses. So I need to show the breakdown of expenses and at the end sum of it and then a new line showing the value of surplus or deficit. Another thing I just noticed, if I added it to a data model to create a measure and check the variance, I am not able to choose custom subtotals I would prefer to do it without VBA, but if it is a must, why not
I'll try have a play around in the next few days and see can I recreate a simple version of your spreadsheet and issue. So it is not just a total you are looking for - but custom sub-totals?
@@BitesizeExcel hi thank u for this Suppose u got multiple peojects Each project will have its own budget, single one of course Then u have multiple items for expenses Then u will get your subtotal Ideally u will have another row below Budget minus total expenses. If the number is positive then u have a surplus amount and u will have it If it is negative then u have a deficit and u would state the amount I am insisting on finding a solution as i would have couple of hundred groups like this I hope we can find s’th Insofar i saw calculated fields and saw items too. I trust the latter wouldnt cut it as it add an observation and not that surplus/deficit row
Thank you Ahmad - I understand the issue now and will have a look to see the best way of getting the result you want. Is your original data in a single table? E.g. Project 1, Budget, Amount Project 1, Spend 1, Amount Project 1, Spend 2, Amount Project 1, Spend 3, Amount Project 2, Budget, Amount Project 2, Spend 1, Amount Project 2, Spend 2, Amount Project 2, Spend 3, Amount Project 2, Spend 4, Amount Project 3, Budget, Amount Project 3, Spend 1, Amount Project 3, Spend 2, Amount
@@ahmed007Jaber Fabulous - this is very helpful. Presume the budget would be the same for all instances of Project A. If so, that is enough information to have a look!
Check out Part 2, which shows you how to remove any previously added rows or columns: th-cam.com/video/nS9oB4EXXUo/w-d-xo.html 👍
Thanks! I presume you might've added this annexure video following feedback from one of your viewers. Again Good job and looking forward to more such informative contents. What makes your videos more useful and special to the viewers like me is that you don't hesitate to share the sample files which is very important for any serious learners who wants to practice hands-on. I would also kindly request if you could post videos on Power Query, Power Pivot, DAX formulas etc. in future. Thanks again. Cheers!
Thanks again - super useful feedback. And yes - I did add the video following a query, as I do try to listen to feedback and tailor videos on that basis. I do have a few videos on power query, which I have added to a playlist, and do have a number of future videos planned on the topics you have suggested.
I am following several channels which talks about MS Excel. But frankly I haven't seen any of them discussing about this particular topic of "calculated rows and columns". Truly it's a new knowledge which I feel elated to know for the first time and surely it will add value to my future projects, and yes I have made this channel to my subscription list without any second thought. Thanks @Bitesize Excel
Thank you so much for the lovely feedback - it really means a lot to me as a relatively new channel! Do let me know if there is anything you'd like to see in future videos. There is also a second video on this topic - which shows you how to remove a calculated row or column (as I had forgotten to discuss in this video). I'll put the link in a pinned post.
I was looking for how to add multiple columns video in Pivot table for an hour but no lucky until finding this helpful video that solved out my issue. I have already watched part 2 that are great. Thanks. 😃
Really happy that you found this video helpful! I hadn't seen any other videos on this either, but find it such a useful trick to know - so thought it would make a good video topic. Do let me know if there is anything else you find helpful, as always good to know for future videos! 😄
Exactly what I needed. Thank you for this terrific content!
Glad it was helpful! Really like when my little tips help people!
Thank you so much. I was looking for this.
Glad you found it useful!
Thank you so much. Very helpful🤩🤩
thank you for this valuable piece of info💛💙
You're so welcome! Thank you for the lovely feedback and glad you found it helpful!
very good, thank you!!
Glad you liked it!
Some constructrive feedback; edit out when you tidy your sheet. It has no value and is very frustrating to sit through. You get to the subject of the actual video 1min 21 seconds into the video. I would recommend Starting the video there, preface quickly that they need to have a pivot table set up (perhaps reference a different video, of give a timestamp to skip the pivot table setup) and then start with the content immediately. Good quality, thanks.
Thanks for the feedback, really helpful for future reference - did add some timestamps to this video so that people could skip the intro bits. I'll also consider the other aspect, although have had feedback the other way that some people like the setup intro.... Maybe say at the beginning in future videos to skip to the timestamp if you already have a pivot table setup!
Amazingly helpful video!!! I'm using it all the time
It is a great tip - that not many seem to know!
How do I add the grand total field as i don't see it in the list to get the total percentage? Do you do zoom sessions?
When you click in you pivot table - you should see a tab at the top called 'design'. In there is a drop down called 'grand totals' where you can add grand totals for rows and columns. I don't currently do zoom sessions (may be something I could consider), but do take video requests. There are lots of useful pivot table tips in the videos in my tables and pivot tables playlist: th-cam.com/play/PLJWCoDBpUWeNfDpsZ1eZTilyHHnUSq7Y0.html
The Beginner's Guide to pivot tables and introduction to pivot table videos are probably particularly useful. 😀
Hi there, Calculated item option is greyed out in my pivot table and only calculated field is available, what is the issue here ?
It matters what field or cell you currently have selected in your pivot table. If you select something that is in the 'values' section you would will get the calculated field option, but the calculated item will be greyed out. You need to select something from the 'Rows' section of the pivot table and then should be able to select calculated item. Hope this helps! 😄
عالی
Thanks for the comment and hope you liked the video!
Working on a spreadsheet for my job. New to PT’s and frustrated with formulas and PT errors. Goal: show the average transit time in days by destination from port to warehouse. Port date and Warehouse date are all in date format and a column is already created with the port date subtracted from warehouse date. How might I ask pivot table or excel to ignore blank cells when a shipment has not yet arrived? The error is giving a number like 40771 or 453759. Thanks!!
The number is likely you taking a zero value away from the other date - which will give you a number (the date in number format). You could correct this in your base data or do you want to correct in just your pivot table?
What if you wanted a cumaltive view on your calculated fields by date ?
Do you have a bit more detail on what you'd like to see - certainly look to create a video on this!
Hello, Question about Pivot Tables. I have pivot table that has a column of parts, the next column is years (so row 1 would be 2001, row 2 would be 2002 and so on, and it would be like this for all the parts). I want to see the difference from 2001 to 2002 is a sort of subtotal like view. so for part number 1, i would have three rows, row one would be sales for 2001, row 2 would be sales for 2002, and row 3 would be the difference in 2002 to 2001. is this possible and how?
You can get the % change in a new column by adding a second total column and then using % difference from column. I can do a quick video over the weekend if you are interested in seeing that method.
@@BitesizeExcel no need
That works
Thank you
@C C No problem at all - glad it helped! 😀
When I try to add a calculated field, it is greyed out. I have added a "Grand Total" but need to have a "Grand Average" column as well.
Where is your data coming from? If it is coming from power pivot (data model) I've found that calculated field doesn't work. In a normal pivot table I don't think there is a way of having both grand total and grand average (but welcome any solutions if anyone is aware of a way) - you can work a solution with power pivot or measures, but I'll probably need to do a video to work through that as it's a bit more complicated.
How do you resolve a complecated formula? If there are some errors on the field?
Do you have an example of one that isn't working right and I might be able to suggest how to fix it? My usual method for troubleshooting complicated formulas is to break it down into steps and see that each step is working as I expect until I can build up to the full formula.
Hi, I have table with: Client name; Country; Credit amount in November; Credit amount in December; Changes (+/-) Nov/Dec. Can I made pivot table with formula which shows only inflow (positive changes in Dec) in each country?
Yes you can. In your calculated field you could use the following formula:
=IF('Changes (+/-) Nov/Dec' >0,'Changes (+/-) Nov/Dec',)
This will give you '0' values for any row that was negative. You can then use the value filters (in the drop down on the top left of your pivot table) to filter your pivot table by anything greater than 0 (if that is what you want).
You can also automate this also via power query rather than using pivot tables - see my videos on power query if you want to get a feel for the great capability of power query in excel.
Can you help me with two more?
1. I have added a slicers with date. But the dates are not in order. It has been shuffer. The actual data is in correct order. Could you please let me know how to correct it in the slicer?
2. I have added a line charge and included a slicer which contains list of team member names. I have given black color to the line however, when i select any name from the slicer, line color is going to blue for some of the name. Could you please help with this?
I'm doing a project and I have few hours left to complete it. Your help is greatly appreciated. Thanks!
Good afternoon D Z. I'll see if I can help.
1. On your first query - there are two things that might help. First make sure your dates are formatted as dated in your source data. Then you can try one of the following. (a) when you input your slicer, right click on the top of the box and there should be an option in the list to sort from oldest to newest (or vica versa). (b) Alternatively you could try insert a timeline instead of a slicer (option is below the insert slicer on your PivotTable Analyse tab - it should give you the option of creating for any date or time data. This gives a box with a timeline or your day on it and in the top right corner you can set it to days, months, quarters or years. You can then select the appropriate time period you want to view.
2. I'm not fully clear on the issue for this one, is it the slicer style that is changing colour in parts. If you could provide a bit more information I will see can I help.
Hope the first part is useful and best of luck! :-)
@@BitesizeExcelThank you so much for the quick response. I have checked the format and confirmed that everything is in the correct date format. I have tried ascending and descending sorting but the dates are not still in order. 2nd option is nice but I have built the dashboard and layouts completely based on thia slicer design. Any other option to fix it.
On the 2nd one - I have inserted a line chart and added a slicer with list of teams members. I have given the color of the line as black. When i select some of the team members, the color of the line is automatically getting changed to blue. And that is only for some of the team members, for others it is still black. Not sure what is happening
Could you maybe share a few screenshots or an anonymised example to bitesize.excel@gmail.com so that I can get a better understanding of the issue? Or share a version via Google Drive.
How do I edit or remove the calculated fields and items?
Great question and probably one I should have covered in the video (will do a future update on this). If you click onto the field you want to remove, then go back to your Pivot Table analyse tab. Select the drop down y 'Fields, Items & Sets' and select either your 'Calculated Item...' or 'Calculated Field...' option (whichever you wish to remove). Then there is a little drop down next to the 'Name:' box and in this the fields you have added previously should be listed. Select the one you want to remove and hit 'Delete'.
I hope this helps and do let me know if you have any further questions.
@@BitesizeExcel thanks a lot! It's very helpful
No problem. I also made that question the topic of my video on Monday - so do check it out!
hi,
thank u for this
I have been using pivot tables for a bit and they are time saving at times.
I have a database of projects their budgets and costs. I know that I can create custom subtotals and custom calculated columns, but was wondering if i could add a line underneath a subtotal to do a calculation e.g. i have one budget for the whole project but several categories of expenses. So I need to show the breakdown of expenses and at the end sum of it and then a new line showing the value of surplus or deficit.
Another thing I just noticed, if I added it to a data model to create a measure and check the variance, I am not able to choose custom subtotals
I would prefer to do it without VBA, but if it is a must, why not
I'll try have a play around in the next few days and see can I recreate a simple version of your spreadsheet and issue. So it is not just a total you are looking for - but custom sub-totals?
@@BitesizeExcel hi thank u for this
Suppose u got multiple peojects
Each project will have its own budget, single one of course
Then u have multiple items for expenses
Then u will get your subtotal
Ideally u will have another row below
Budget minus total expenses. If the number is positive then u have a surplus amount and u will have it
If it is negative then u have a deficit and u would state the amount
I am insisting on finding a solution as i would have couple of hundred groups like this
I hope we can find s’th
Insofar i saw calculated fields and saw items too. I trust the latter wouldnt cut it as it add an observation and not that surplus/deficit row
Thank you Ahmad - I understand the issue now and will have a look to see the best way of getting the result you want.
Is your original data in a single table? E.g.
Project 1, Budget, Amount
Project 1, Spend 1, Amount
Project 1, Spend 2, Amount
Project 1, Spend 3, Amount
Project 2, Budget, Amount
Project 2, Spend 1, Amount
Project 2, Spend 2, Amount
Project 2, Spend 3, Amount
Project 2, Spend 4, Amount
Project 3, Budget, Amount
Project 3, Spend 1, Amount
Project 3, Spend 2, Amount
@@BitesizeExcel
I might be able to change i did it as follows
Proj, budget, expense name, expense value
Proj A, 2000, laundry, 200
Etc
@@ahmed007Jaber Fabulous - this is very helpful. Presume the budget would be the same for all instances of Project A. If so, that is enough information to have a look!