Hi Prakash - I go through a few in this video: - layout constantly changing - filtering required to display just one entry - slows down file calculation - formatting difficult to control - named range automatically created I see situations where formula-based approaches work better. I hope this helps and good luck!
Why didn't you convert your original data set into a "Table" ? Would have made pivot tables and formulas a lot quicker to set up and help maintain any future updates?
Pivot tables are actually quite easy, fast and automated - thus they reduce potential for error in calculations - a must in data analysis! Slick visualization (Dashboards) can then be created from pivot table calculations with Slicers. Another feature of Pivot Tables which I teach users about is the ‘drill down’ feature - to get the detail behind the totals in a separate tab. That always seems like ‘magic’ to them and can be quite useful - depending on the need.
@@TigerSpreadsheetSolutions put slicers with your pivot tables and you’ll have an easy to use dynamic setup. I like your videos but this one is I find hard to agree with.
I like pivot tables in Excel. They are easy to build when you want to answer quickly a question that involves data aggregation. It's way easier than doing it in a traditional DBMS like PostgreSQL for instance. However, like you, I got asked specifically not to use a pivot table on a job interview assignment last week. It was a wakening call for me: never get complacent with only one tool in your box. I always relied on the Tidyverse's tools in R for such tasks whenever I needed to build a robust solution and on Excel pivot tables during exploratory data analysis. Now I know better.
I love videos like these, pivot tables are useful, but overused, and when working in a organization which works on excel, you can see some very unique and intriging pivot tables. Knowing how to work with functions, to get the data and present only what you want makes the difference beetwen a report, an a good and informative report!
Thank you very much good sir. I'm from Indonesia and this video is really helpful. But i have some difficulties on using this file that you have shared. On the MINIFS part, there is this "#NAME?" error. As i look at the function, there is this "_xlfn." before the MINIFS Function. Would you mind to help me to solve this error good sir?
No chance to make a charts like pivot charts. This is in my opinion the reason why Pivot tables are quite convenient. Also if you ad data to your data source. You can make an offset of the selected data range, but this is quite complicated. So the Pivot Tables may not be very nice, but for charts in my opinion first choice. The update easily you can slice data and can make very nice dashboards.
@@jonathansternberg5309 of course there's a way! Use named references as sources in your chart data references. Easy as 1-2-3. And if you do the first basic step anyone should do with a table of data, namely convert it to an actual table, then your named reference to the table column will always include new data. No need for complicating it with offset formula magic.
@@Luggruff You are right. I had my situation in mind where I am due to data imported data via a macro, not able to convert the data into a table, because for some reason the table doesn't expand. But with data convertet into an actual table it is easy. The data reference though can be at least in the beginning not so easy as 1-2-3
Certainly a different approach, but how do you apply this to do comparisons. For example, a pivot table can show you all the different regions (in your example) next to each other without a lot of work and what do you do if they add/remove a region. Pivot tables are much more dynamic than this. Is there a reason you didn't set up the data in a table? Especially useful unless the data is completely static, even then you get the benefit of referencing column names and don't have to worry if you've selected the same number of rows. As for pivot tables resizing, just change the auto resize option.
The auto-resize option sounds useful. For comparison - I would create a list of the unique values and use formulae to create the table. Personally, I don't use tables because I find the formula references over-complicated. I am in no way suggesting this suits everybody, just that there are different ways to do it if you are required to.
I’d agree that pivot tables are not ideal to pass on to others, though very quick and easy for personal analysis. This is a great alternative when sharing data with others IF the questions are static.
I know where You coming from, i had trouble with pivot tables myself for a long time. I was using workarounds like one presented here (in most cases a FILTER function). It was annoying and VERY limited. In Your example, lets say my boss says a simple thing - he want to see data for north and west regions. Can i re-desing the whole thing for an extra condition? Sure. But then i`ll get fired for wasting top brass time with eye candy ;) With a pivot table its a single click. TLDR, pivot tables are a powerfull, well established, industry standard thing, that any serious user HAS to know. Period. Anyhow, as always, i enjoyed the video. Im waiting for an indepth tutorial for pivot tables. Cheers :) Edit: I enjoy eyecandy. Eye candy is good for presentation - i strongly agree. I redesign data (charts, tables, whole shabang) that senior analyst created, to present it to simple production folk for a living. Down to manually picked colors, so theres no confusion whats what. But You can build all of that on top of a pivot table.
Great thoughts - thank you! Yes, an 'analyst' should know pivot tables. But the video is also for professionals (not analysts as such) - I find, of these people, pivot tables are more a mystery and a nuisance than an industry standard. Thanks for the comment!
you can show with the excel built in functions only the results for one region. that is maybe "the problem". In Pivot Tables you can make very fast reports, when it comes to show differences in like in your example, the regions. i dont like to give pivot tables as well to co workers, because they mess it up 😆 thatswhy to include some slicers is my way of focus the user of the table where he/she belongs.
Wonderful and informative video! I really appreciate your perspective in that maybe your clients/customers do not feel comfortable using pivot tables. I share your perspective and always strive to know who my clients are and what their competency level is in using Excel. Based on that level, is how I plan and develop my Excel solution for them. Thank you for putting the emphasis on the client's comfort level and not just giving us a "technical" video of how to do something in Excel. I have enjoyed all your videos and instructions.
I am also the same re Pivot Tables. Knowing whether to put info in columns or rows and formatting data. I prefer your dashboard method as it was something I have been thinking of doing myself on a revamp of your 6 part football league table tutorial which I left comments on. Basically I am a big rugby league fan and your simple and easy to use dashboard will come in handy for the Rugby League 2021 World Cup sheet I am doing. Keep up the good work Chris
Thank you! I thought it was just me that was uncomfy with pivot tables. You just confirmed my similar approach for a solution. I had three diff filter condtions I needed to apply to the same count field, so 3 diff columns. I tried with pivot table and couldn't apply conditions from other columns using calculated fields. I finally had to go to Countifs. I eventually wound up using sumproduct because multi filter s got complex. I wound up with more control than with pivot table. Only downside is I cant doubleclick and get a drill down table, but I got over it. Thank you again for sharing!
Pivot tables are helpful and easy to build although the formatting may be annoying - but I remain a big fan of sumifs/countifs/averagesifs and built-in formulas in my data source tab to get to equivalent summaries. So to me it boils down to personal preferences...I like the lay out of your preferred version, it is very clean and easy to read for the audience 👏👏👏 Thank you for sharing!!
I personally use and love pivot tables,and I can build report after report with them. I not only love them but they have revolutionized how I think about data. However, as you mention, my colleagues don’t understand how they work and how beneficial they can be, so much so that I’ve been instructed not to use them.
If I were your client, I'd be asking where the rest of the data I requested was. I'm no great fan of Pivot Tables (and the awful GETPIVOTDATA) but this is myopic to the point of Luddite-ism
This is so refreshing! I thought it was just me who avoided using pivot tables! So far, there hasn't been one instance where I couldn't get the job done using good old fashioned formulae. I feel like I'm the one who's in control with COUNTIF, SUMIF, etc. Nice to know I'm not strange after all...
Many thanks for this. I've been using Excel for 25 years and I also share your distrust / dislike of pivot tables. I prefer using the kind of formulae you're demonstrating here. People seem to be divided between PT lovers and PT haters - either one extreme or the other! But I recently discovered how to set up a PT in tabular format, which makes it look more like a database, and I'm starting to like them a bit (shock horror). But the way they seem to self-format and jump around outside of your control still annoys me! I think they're useful for a 'quick and dirty' analysis, but presentationally, I still think manual tables with SUMIFS / XLOOKUP etc formulae are better.
@@TigerSpreadsheetSolutions In theory yes - I've got one for example where I'm trying to do a simple division formula - spend divided by conversions but EVERYTHING yields an error.
99% of the time, the data I'm working with is dynamic data, so if I can't start with a table, then I don't use that technique, Messing around with ranges after the fact is the biggest annoyance I have with spreadsheets so I use whatever technique that minimizes that.
Messing around with tables is the biggest annoyance I have with spreadsheets so I use whatever technique that minimises that. You can define ranges dynamically?
If you can't create tables, but the columns are always the same, then try an offset formula as a named range. Or even better: Learn to use Power Query and keep your data file external. And if it always has the same format, you can even just completely replace it (with the same file name) whenever you need to. Power Query will build you a table, very fast, and you can have steps applied to define which data is the header, what type of data it is (text, dates, numbers, decimal numbers, etc.), add calculated columns, concatenate columns, separate columns etc. etc.. all in a second before the table even generates. And in the end, you have a table you can reference absolutely, all the time. Power Query also compresses your data, so your main file will be smaller than the data file you are actually importing. ..and best of all, you can have multiple external files, and end up with only tabled data in your working file.
Great Video. There are alwys many many ways in Excel to get the results you want. If you familiar with Pivot-Tables and you know what to do, I think they are great and fast (I (have) to use them a lot) If your focus is more on formulas and you know your formulas well and can combine them, maybe you are faste in some ways. Of course you can create greate dashboards with formulas, but also you can do it with Pivot-Tables and slicers. I would format the Dataset into a Table and named it...because if the Dataset gets bigger, you don´t of to customize your Pivot-Table/Formula Like many things in Excel...you have to solve the Problem the way you can do it best...at the end, the result have to be correct :-)
Pivot tables are useful for those who don't work in analysis to pull some quick figures from a table. You didn't say in this video, but it's difficult to use inconsistent formulae in a pivot table, and they also can struggle with changing data sets. Job adverts often ask for "vlookup and pivot table skills", it's a bit of a buzz word. But I agree, I'm not a fan, they feel restrictive. Being able to create your own tables / charts, combined with some VBA and macros, means regular analysis can be achieved with a couple of clicks, which would otherwise be hours worth of work with pivoting.
All I'm arguing is that things change when you have to create tools for other people. And as data analysts, shouldn't we at least be working towards that ...?
📺Watch Next: Excel Data Analysis Formulae For Beginners (SUMIFS, AVERAGEIFS, DSUM etc.)
th-cam.com/video/t7-ej1pwh8M/w-d-xo.html
Mr.chris, can you tell me the downside of pivot table. Thank you
Hi Prakash - I go through a few in this video:
- layout constantly changing
- filtering required to display just one entry
- slows down file calculation
- formatting difficult to control
- named range automatically created
I see situations where formula-based approaches work better. I hope this helps and good luck!
@@TigerSpreadsheetSolutions thank you sir. Of course layout is always difficult to control
Why didn't you convert your original data set into a "Table" ? Would have made pivot tables and formulas a lot quicker to set up and help maintain any future updates?
I don't use table for various reasons - I prefer dynamic ranges. I will put together a video about this one day. Thanks for the comment!
Pivot tables are actually quite easy, fast and automated - thus they reduce potential for error in calculations - a must in data analysis! Slick visualization (Dashboards) can then be created from pivot table calculations with Slicers. Another feature of Pivot Tables which I teach users about is the ‘drill down’ feature - to get the detail behind the totals in a separate tab. That always seems like ‘magic’ to them and can be quite useful - depending on the need.
They can be a great option ... if you have customers that are willing to be 'trained', which I don't often find.
@@TigerSpreadsheetSolutions put slicers with your pivot tables and you’ll have an easy to use dynamic setup. I like your videos but this one is I find hard to agree with.
@@yuei A radical idea: perhaps you could disagree with something but still 'like' it?
agreed, last thing you want to teach ppl is not to use pivot tables, lol
Fahad Qureshi why is that?
I like pivot tables in Excel. They are easy to build when you want to answer quickly a question that involves data aggregation. It's way easier than doing it in a traditional DBMS like PostgreSQL for instance. However, like you, I got asked specifically not to use a pivot table on a job interview assignment last week. It was a wakening call for me: never get complacent with only one tool in your box.
I always relied on the Tidyverse's tools in R for such tasks whenever I needed to build a robust solution and on Excel pivot tables during exploratory data analysis. Now I know better.
Caty - great to hear, I've found myself in many such situations! But the learning that results is like gold dust for your career ...
Wow I never thought to do it this way, but I like it! Thank you for showing!
Great to hear - thanks for watching!
I love videos like these, pivot tables are useful, but overused, and when working in a organization which works on excel, you can see some very unique and intriging pivot tables. Knowing how to work with functions, to get the data and present only what you want makes the difference beetwen a report, an a good and informative report!
Could not agree more Michal - welcome to the channel!
Thank you very much good sir. I'm from Indonesia and this video is really helpful. But i have some difficulties on using this file that you have shared. On the MINIFS part, there is this "#NAME?" error. As i look at the function, there is this "_xlfn." before the MINIFS Function. Would you mind to help me to solve this error good sir?
It might be because you are not using and up-to-date version of Excel, MINIFS and MAXIFS are recent additions. Good luck!
To select all the data in a uninterrupted range you can press ctrl+shift+* from the num pad. Thank you for this video.
I think that's the shortcut I was trying to remember during filming! Thanks for the reminder ...
One key less... Ctrl + A
Great stuff! Thank you.
Thanks, Leo!
This is very helpful, but how about when you have data that is very long or consistently changing especially in row length.
I use INDIRECT to dynamically define ranges that might change. Excel tables is another option.
@@TigerSpreadsheetSolutions Thank you!
How do you add charts to this data? For the most part I use Pivot tables to create charts
thank you and appreciate your videos
No chance to make a charts like pivot charts.
This is in my opinion the reason why Pivot tables are quite convenient. Also if you ad data to your data source. You can make an offset of the selected data range, but this is quite complicated. So the Pivot Tables may not be very nice, but for charts in my opinion first choice. The update easily you can slice data and can make very nice dashboards.
@@jonathansternberg5309 of course there's a way! Use named references as sources in your chart data references. Easy as 1-2-3. And if you do the first basic step anyone should do with a table of data, namely convert it to an actual table, then your named reference to the table column will always include new data. No need for complicating it with offset formula magic.
@@Luggruff You are right. I had my situation in mind where I am due to data imported data via a macro, not able to convert the data into a table, because for some reason the table doesn't expand. But with data convertet into an actual table it is easy. The data reference though can be at least in the beginning not so easy as 1-2-3
Very helpful, thank you 🙂
You're welcome!
Certainly a different approach, but how do you apply this to do comparisons. For example, a pivot table can show you all the different regions (in your example) next to each other without a lot of work and what do you do if they add/remove a region. Pivot tables are much more dynamic than this.
Is there a reason you didn't set up the data in a table? Especially useful unless the data is completely static, even then you get the benefit of referencing column names and don't have to worry if you've selected the same number of rows.
As for pivot tables resizing, just change the auto resize option.
The auto-resize option sounds useful. For comparison - I would create a list of the unique values and use formulae to create the table. Personally, I don't use tables because I find the formula references over-complicated. I am in no way suggesting this suits everybody, just that there are different ways to do it if you are required to.
I’d agree that pivot tables are not ideal to pass on to others, though very quick and easy for personal analysis. This is a great alternative when sharing data with others IF the questions are static.
Thanks, Jane. The formulae can be set up dynamically, too.
I know where You coming from, i had trouble with pivot tables myself for a long time. I was using workarounds like one presented here (in most cases a FILTER function). It was annoying and VERY limited. In Your example, lets say my boss says a simple thing - he want to see data for north and west regions. Can i re-desing the whole thing for an extra condition? Sure. But then i`ll get fired for wasting top brass time with eye candy ;) With a pivot table its a single click.
TLDR, pivot tables are a powerfull, well established, industry standard thing, that any serious user HAS to know. Period. Anyhow, as always, i enjoyed the video. Im waiting for an indepth tutorial for pivot tables. Cheers :)
Edit: I enjoy eyecandy. Eye candy is good for presentation - i strongly agree. I redesign data (charts, tables, whole shabang) that senior analyst created, to present it to simple production folk for a living. Down to manually picked colors, so theres no confusion whats what. But You can build all of that on top of a pivot table.
Great thoughts - thank you! Yes, an 'analyst' should know pivot tables. But the video is also for professionals (not analysts as such) - I find, of these people, pivot tables are more a mystery and a nuisance than an industry standard. Thanks for the comment!
you can show with the excel built in functions only the results for one region.
that is maybe "the problem".
In Pivot Tables you can make very fast reports, when it comes to show differences in like in your example, the regions.
i dont like to give pivot tables as well to co workers, because they mess it up 😆
thatswhy to include some slicers is my way of focus the user of the table where he/she belongs.
Yes, I’m a big fan of slicers, too. You can tabulate the data like in a pivot table with multiple formulae.
Wonderful and informative video! I really appreciate your perspective in that maybe your clients/customers do not feel comfortable using pivot tables. I share your perspective and always strive to know who my clients are and what their competency level is in using Excel. Based on that level, is how I plan and develop my Excel solution for them. Thank you for putting the emphasis on the client's comfort level and not just giving us a "technical" video of how to do something in Excel. I have enjoyed all your videos and instructions.
Great to hear Douglas, I think you'll fit in around here! It's all about creating value for normal people ...
I am also the same re Pivot Tables. Knowing whether to put info in columns or rows and formatting data.
I prefer your dashboard method as it was something I have been thinking of doing myself on a revamp of your 6 part football league table tutorial which I left comments on.
Basically I am a big rugby league fan and your simple and easy to use dashboard will come in handy for the Rugby League 2021 World Cup sheet I am doing.
Keep up the good work Chris
Good luck with it Dave - let me know how it goes
Thanks alot
You're welcome - do check out the other videos on the channel ...
Pivot tables seem powerful at first, but when you try to use them it gets very restrictive very quickly. It's almost useless feature in practice.
Very interesting perspective. I still think they're very powerful if you can tolerate the unpredictable layout ...
Thank you! I thought it was just me that was uncomfy with pivot tables. You just confirmed my similar approach for a solution. I had three diff filter condtions I needed to apply to the same count field, so 3 diff columns. I tried with pivot table and couldn't apply conditions from other columns using calculated fields. I finally had to go to Countifs. I eventually wound up using sumproduct because multi filter s got complex. I wound up with more control than with pivot table. Only downside is I cant doubleclick and get a drill down table, but I got over it. Thank you again for sharing!
Good to hear - the new FILTER formula might help you get that drill down type analysis, thanks for the comment!
Pivot tables are helpful and easy to build although the formatting may be annoying - but I remain a big fan of sumifs/countifs/averagesifs and built-in formulas in my data source tab to get to equivalent summaries. So to me it boils down to personal preferences...I like the lay out of your preferred version, it is very clean and easy to read for the audience 👏👏👏 Thank you for sharing!!
Thanks very much for the comment - I think those formulae give you the cleanest solution ...
I personally use and love pivot tables,and I can build report after report with them. I not only love them but they have revolutionized how I think about data. However, as you mention, my colleagues don’t understand how they work and how beneficial they can be, so much so that I’ve been instructed not to use them.
Thanks for sharing your experience - I think this kind of thing is more common than people think
If I were your client, I'd be asking where the rest of the data I requested was. I'm no great fan of Pivot Tables (and the awful GETPIVOTDATA) but this is myopic to the point of Luddite-ism
Great vocab!
It's like compare apples and oranges
But both do data analysis? So you're talking essentially about the same function.
thanks chris :)
You're welcome, Christian!
Good bye pivot tables, been needing this.
This is so refreshing! I thought it was just me who avoided using pivot tables! So far, there hasn't been one instance where I couldn't get the job done using good old fashioned formulae. I feel like I'm the one who's in control with COUNTIF, SUMIF, etc. Nice to know I'm not strange after all...
Welcome, Steven!
Just too beautiful, both conceptually and technically.
Thank you and welcome to the channel!
Many thanks for this.
I've been using Excel for 25 years and I also share your distrust / dislike of pivot tables. I prefer using the kind of formulae you're demonstrating here. People seem to be divided between PT lovers and PT haters - either one extreme or the other!
But I recently discovered how to set up a PT in tabular format, which makes it look more like a database, and I'm starting to like them a bit (shock horror). But the way they seem to self-format and jump around outside of your control still annoys me!
I think they're useful for a 'quick and dirty' analysis, but presentationally, I still think manual tables with SUMIFS / XLOOKUP etc formulae are better.
Thanks for the comment Ian - not saying I'm a hater necessarily, it's just a case of using the right thing for the right job ...
Problem is that they're confusing and not user friendly. Especially the formulas. Its so stupid how calculated fields are structured.
I tend to agree, Chris - but perhaps this kind of thing gets better the more you use them?
@@TigerSpreadsheetSolutions In theory yes -
I've got one for example where I'm trying to do a simple division formula - spend divided by conversions but EVERYTHING yields an error.
Agree 100%. Pivot tables tend to take control away from you.
Thanks for comment, Tommy!
Why don't you make the data into a table?
I actually don’t use tables, either ...
@@TigerSpreadsheetSolutions WHAT! ...........
99% of the time, the data I'm working with is dynamic data, so if I can't start with a table, then I don't use that technique, Messing around with ranges after the fact is the biggest annoyance I have with spreadsheets so I use whatever technique that minimizes that.
Messing around with tables is the biggest annoyance I have with spreadsheets so I use whatever technique that minimises that.
You can define ranges dynamically?
If you can't create tables, but the columns are always the same, then try an offset formula as a named range. Or even better: Learn to use Power Query and keep your data file external. And if it always has the same format, you can even just completely replace it (with the same file name) whenever you need to. Power Query will build you a table, very fast, and you can have steps applied to define which data is the header, what type of data it is (text, dates, numbers, decimal numbers, etc.), add calculated columns, concatenate columns, separate columns etc. etc.. all in a second before the table even generates. And in the end, you have a table you can reference absolutely, all the time. Power Query also compresses your data, so your main file will be smaller than the data file you are actually importing.
..and best of all, you can have multiple external files, and end up with only tabled data in your working file.
Like you I do not like PivotTables, this a fabulous alternative, Thanks you.
Welcome, John!
Great Video.
There are alwys many many ways in Excel to get the results you want.
If you familiar with Pivot-Tables and you know what to do, I think they are great and fast (I (have) to use them a lot)
If your focus is more on formulas and you know your formulas well and can combine them, maybe you are faste in some ways.
Of course you can create greate dashboards with formulas, but also you can do it with Pivot-Tables and slicers.
I would format the Dataset into a Table and named it...because if the Dataset gets bigger, you don´t of to customize your Pivot-Table/Formula
Like many things in Excel...you have to solve the Problem the way you can do it best...at the end, the result have to be correct :-)
Indeed, it's all about user expectation and familiarity level as you suggest.
Pivot tables are useful for those who don't work in analysis to pull some quick figures from a table. You didn't say in this video, but it's difficult to use inconsistent formulae in a pivot table, and they also can struggle with changing data sets. Job adverts often ask for "vlookup and pivot table skills", it's a bit of a buzz word. But I agree, I'm not a fan, they feel restrictive. Being able to create your own tables / charts, combined with some VBA and macros, means regular analysis can be achieved with a couple of clicks, which would otherwise be hours worth of work with pivoting.
Thanks Hazel - love your perspective
Me too
you're in good company ...
Hard agree on this - I do use pivots for myself sometimes as they can be powerful but if I have to give data to someone else I never do
All I'm arguing is that things change when you have to create tools for other people. And as data analysts, shouldn't we at least be working towards that ...?
❤️❤️❤️❤️
Welcome back, Christian!
alt + N + V
Good shout
Good bye pivot tables, been needing this.
Welcome to the channel!