Faster and easier ways to
Before analysis can begin, you must be able
to bring in the data relevant to the business question you are trying to
answer. Excel 2016 now comes with built-in functionality that brings ease and
speed to getting and transforming your data—allowing you to find and bring all
the data you need into one place. These new capabilities, previously only
available as a separate add-in called Power Query, can be found natively within
Excel 2016. Access them from the “Get & Transform” section on the Data tab.
Use these capabilities to connect to data
from a wide range of sources—like tables in public websites, corporate data in
databases and cubes, cloud-based sources like Azure, unstructured data like
Hadoop and services like Salesforce—then bring the data into your workbook’s
data model or display them as tables within a worksheet.
The Query Editor, which opens when creating a
new query or opening an existing one under Get & Transform, provides
intuitive data shaping and transformation options, enabling you to prepare a
data set with only the information you want to see and in the way you want to
see it. Each step you take to transform the data can be repeated and rearranged
to help you work faster and more efficiently. When you refresh the data
connections, every step you took to transform the data is preserved. And once
you have the information prepared, you can share your queries easily with
Additional features available under the Get
& Transform section include accessing recent sources you’ve used in
previous queries and options to combine queries together. With a premium
version of Excel 2016 (MSOffice ProPlus or Office 365 ProPlus) and a Power BI
license, you can also use search to discover corporate data available to you
across your company that is shared in a corporate catalog or even share your
own queries with others.
Integrating the Power Query technology also
provides benefits for programmability. With these features integrated into
native Excel, you can use VBA and the Object Model to program a related query
and transform actions. And if you need more than the predefined actions
provided, you can use the powerful query language (M) to create additional
actions and capabilities.
Streamlined data analysis
Excel is known for its flexible and powerful
analysis experiences, through the familiar PivotTable authoring environment.
With Excel 2010 and Excel 2013, this experience was significantly enhanced with
the introduction of Power Pivot and the Data Model, bringing the ability to
easily build sophisticated models across your data, augment them with measures
and KPIs, and then calculate over millions of rows with high speed. In Excel
2016, we focused on making these experiences more discoverable, consistent and
streamlined, so that you can focus less on managing your data and more on uncovering
the insights that matter:
- Automatic relationship detection discovers and creates relationships among the tables used for your
workbook’s data model, so you don’t have to. Excel 2016 knows when your
analysis requires two or more tables to be linked together and notifies
you. With one click, it does the work to build the relationships, so you
can take advantage of them immediately.
- Creating, editing and deleting custom measures can now be done directly from the PivotTable fields list, saving
you a lot of time when you need to add additional calculations for your
- Automatic time grouping helps you to use your time-related fields in your PivotTable more
powerfully, by auto-detecting and grouping them on your behalf. Once
grouped together, simply drag the group to your PivotTable in one action
and immediately begin your analysis across the different levels of time
with drill-down capabilities.
- PivotChart drill-down buttons allow you to drill in and out across groupings of time and other
hierarchical structures within your data.
- Search in the PivotTable field list helps you get to the fields that are important to
you across your entire data set.
- Smart rename gives you the ability
to rename tables and columns in your workbook’s data model. With each
change, Excel 2016 automatically updates any related tables and
calculations across your workbook, including all worksheets and DAX
- Multiple usability improvements have also been made. For example, delayed updating allows you to
perform multiple changes in Power Pivot without the need to wait until
each is propagated across the workbook. The changes will be propagated at
one time, once the Power Pivot window is closed.
Publishing and sharing in
A report is not complete without being able
to share it with the right people. Once you’re finished preparing your data
analysis, you can share it with your workgroup or clients through Power BI with
just one button. Once published to Power BI, use your data models to quickly
construct interactive reports and dashboards. With Excel Online support built
into Power BI service, you can also display your fully formatted Excel
worksheets as well.
Taking advantage of Excel
With rich features in Excel Online, your peers and clients are able to
view and interact with your Excel data analysis and reports from anywhere. All
you need to do is share your workbook through Power BI, SharePoint or OneDrive.
Recently, we’ve included new features to make it even easier to consume and
interact with your workbook from the web, including Search in Filters and the
ability to change the settings of a value field in a PivotTable. With Office
365, Microsoft is providing more features and improvements to Excel Online on a