Merged cells can also be hard to understand. As a workaround, create a new column that uses the DATE or DATEVALUE functions, and format it as a date.Īnalyze Data won't work when Excel is in compatibility mode (i.e. String dates like "" will be analyzed as if they are text strings. In the meantime, you can filter your data, then copy it to another location to run Analyze Data on it. There is currently no workaround for this. Here are some reasons why Analyze Data may not work on your data:Īnalyze Data doesn't currently support analyzing datasets over 1.5 million cells. If you have complicated, or nested data, you can use Power Query to convert tables with cross-tabs, or multiple rows of headers.ĭidn't get Analyze Data? It's probably us, not you. Avoid double rows of headers, merged cells, etc. Headers should be a single row of unique, non-blank labels for each column. Make sure you have good headers for the columns.
To create an Excel table, click anywhere in your data and then press Ctrl+T. Here are some tips for getting the most out of Analyze Data:Īnalyze Data works best with data that's formatted as an Excel table. Or you could ask Analyze Data to display average sales by year.Īnalyze Data works best with clean, tabular data. For example, you might only want to see the sum of sales by year.
When you choose fields and how to summarize them, Analyze Data excludes other available data - speeding up the process and presenting fewer, more targeted suggestions. You can save time and get a more focused analysis by selecting only the fields you want to see. If you do not have a question in mind, in addition to Natural Language, Analyze Data analyzes and provides high-level visual summaries, trends, and patterns. It may not be available in all countries or regions at this time. The Natural Language Queries functionality in Analyze Data is being made available to customers on a gradual basis.
To learn more about the different update channels for Office, see: Overview of update channels for Microsoft 365 apps. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office. The bottom line? PivotTables are incredibly flexible and efficient tools for translating mountains of raw data into meaningful and intelligent insights.Analyze Data is available to Microsoft 365 subscribers in English, French, Spanish, German, Simplified Chinese, and Japanese.
With PivotTables, you could break your data down by individual products or product categories, add calculated fields to compare margins and identify low-performers, cross-filter by geographic region to understand where certain products sell best, or explore trended sales to see which products may be losing momentum - all in a matter of minutes. Let’s say you’re looking at sales data for a national manufacturer and searching for opportunities to cut costs. Pivots allow you to apply custom segments and filters to your data, create calculated fields and metrics, and convert raw values into percentages, running totals, ranks or indices with the click of a button (no formulas or functions required!) The good news? This is where PivotTables shine. One thing that makes analytics so challenging is that insights often lurk just beneath the surface, yet remain virtually invisible until the data is broken down in an extremely specific way. This is a great way to explore high-level trends and compare segments of your data, and expose the patterns and trends worth digging deeper into. Think of the field list as your PivotTable “command center”, where you can assemble your data, set up filters and segments, and control the exact layout of your table by simply dragging and dropping your fields.
Now when it comes time to start exploring your data, the field list is your best friend.
Pro tip: double click on any cell in your pivot to reveal the source data used to calculate its value Using those coordinates, Excel drills down to that specific “slice” of raw source data, applies some sort of summarization (sum, count, average, max, etc.), and displays the result. In a nutshell, each individual cell is calculated independently based on its “coordinates” - defined by the rows, columns, and filters in your table.
Anyone can follow instructions, but unless you have a solid grasp of what’s going on behind the scenes, you won’t be able to harness the full potential of PivotTables. The first step to mastering any skill is understanding the fundamental concepts that make it work.