Now that we know about Excel Recommended Charts, let us explore it in-depth. This is an implementation of artificial intelligence or machine learning at your fingertips. Don’t underestimate it… exploit it.
Photo credit: Elsie esq. / Foter / CC BY and salendron / Foter / CC BY-NC-SA
Contents
How do Excel Recommended charts work
Excel analyzes the kind of data you have and decided the most appropriate visualization for it. It looks at many things. Here are some of them:
- Is the data tabular or cross-tab?
- Are there headings?
- How many numeric columns / rows are available?
- Is there a single level or multiple levels of hierarchy?
- Is the data small or large (in terms of number of rows)?
- Are there date columns in it?
Based upon this it does provides very sensible recommendations.
Automatic Pivot Generation
One feature of Recommended Charts may not be obvious – but it is extremely powerful. So let me highlight it first. Consider this data. and its recommended charts…
Notice that icon? That indicates a Pivot Table. Our data does not have a pivot table at all.
Recommended charts has done it automatically. It internally created a pivot table and it has found totals by department and totals for Employees / Outsourced separately.
The second and third chart is based upon the pivoted version of the data. Now if you choose one of these Auto-Pivoted types – in this case I am choosing the third one, it actually adds a new sheet, adds the equivalent Pivot Table and then adds the chart.
Very impressive and useful. Is it not?
Problem with this approach
If you try the second type in the recommended charts, you will notice that one bar is empty. Why is that? Because, in the department column, all rows do NOT have the department mentioned. That is why all the blanks were summed together to give a tall column based upon BLANKS. This is a side-effect which you need to be aware of.
The solution is to fill the departments appropriately and then use the recommended chart.
Want to know how to fill these gaps quickly (assuming you have hundreds of such gaps)?
Applied Knowledge: Go To Special – Practical Usage
Bad Data to Good Data: Filling in gaps using Power Query
Working with raw data
Consider this data… I have 200 rows of data. What will Excel recommend in this case?
Well, Excel understood that this is Tabular data (good data in our language). So most of the recommended charts are Pivot Based. Is that not very smart?
New charts are also supported
I am now using Office 2016. So the newly added charts were also recommended in this case – which are NOT directly based upon a pivot in the sense it does not create a Pivot Table when you choose it.
What does NOT get recommended
It interprets dates like numbers. Therefore, you may see some not very appropriate charts being recommended. Ignore them.
If there are multiple numeric columns, it gets confused about which is the most important one. In such cases it may not create histograms on the column which is most relevant. You will need to do so manually.
In Conclusion
It is a great feature for quickly looking at various views of the same data and finding out maximum possible usable and actionable information from data with ZERO manual formatting and tweaking.
***
***
