Analyze Data - Help with Structuring Data to Improve Analysis

New Contributor

I have a table of library book circulation figures that I have put into a pivot table. The table shows a certain class of items (fiction, non fiction, etc.) and the circulation numbers for each class across a span of several years. I'd like to use the Analyze Data tool to help me answer some questions for a report I'm writing, but I can't seem to format the questions properly.  I'd like for the tool to show me the total of each class across each year in a graph, perhaps a stacked bar graph, or line graph, to indicate trends. 


What is the best way to do this? Should I change the labels on my tables to get better answers from the tool? Thanks in advance for any help. 

 CleanShot 2021-07-06 at 11.39.35.jpeg

7 Replies


Thanks for sending a question about the Analyze Data feature. Analyze Data can’t currently create suggestions based on PivotTables, so you’ll want to make sure you are running it on your original data.


If you have a column for each of the years, you’ll want to mention each one specifically, for example: 2017, 2018, 2019 and 2020 by item class as column chart (or a different chart type). Note that it's easier to ask questions over time when you have a single column with multiple date values.


If this doesn't work, you might want to make sure your data is formatted as a table. Please select all of it and click on Format as Table in the Home tab and then try Analyze Data again.


Let us know if this doesn’t solve your issue.


Thanks for your feedback,



Thanks for your answer, @Mar Gines Marin. I also have the data formatted as a table, but still couldn't get the answers I wanted out of the analyze data tool. This table has something like 15,000 lines of data, so I'm not exactly sure how I'd combine each year into one column. How does that work?

Sorry that didn't work. I wasn't suggesting you changed the structure of your data, just that the scenarios for which we have optimized the feature so far typically have dates grouped in columns. We continue to iterate and improve the quality of the feature and we'll incorporate your feedback as we do so.

Thanks again for reaching out, @skeskali 


@Mar Gines Marin  My data is structured in a regular table in another worksheet (see screenshot). This is not a pivot table. I guess what I was looking for was suggestions or specific syntax that would help me get the answers I needed from such a large data set. My brain thinks in natural language, not Excel, so I think that asking a table like this one "Show me the top istat for each location for each year" should work, but it doesn't. Is there somewhere I might learn more about the proper syntax structure and how I might get more out of this tool? 

CleanShot 2021-07-07 at 18.33.32.png

@skeskali We currently don't have documentation of the sort you describe. Based on the structure of the data, we aren't able to understand "for each year". I think that asking: "top istat based on year 2017 for each location" would give you what you want, but you'd need to ask the same question for each year.

We'll explore how to improve this scenario.




Hi @skeskali,

You stated: "I'd like for the tool to show me the total of each class across each year in a graph, perhaps a stacked bar graph, or line graph, to indicate trends."


Just for the fun of it, and the only thing you have to change is switch the time periods and the category in your pivot table.  Have you tried putting the time periods in the ROWS, and the category in the Columns then make a graph out of it? Just wondering if you have tried that approach. Then a different graph for the location, the same approach or maybe the first value in the column would be the location, then category or probably even better, in the rows the first value would be the year, then location and in the column would be the categories. (recommended)


That approach might yield a graph chart that looks something like this:




Hi @skeskali 


Hope attached visualization (Cross Tab or Hierarchical Bar Chart) will help you in analyzing data. 


No Code Data Visualization add-in used to create this chart. 


You can check here for Microsoft Excel & Office 365.


And if you are comfortable with Google Sheets, you can find here.