As part of this month’s update, a new Excel feature gives you the ability to personalize the default layout for your PivotTables. Enabling users to personalize the PivotTable defaults started as a feature request in our Excel UserVoice forum. Now, when you’re building complex reports or performing one-off analyses, you can quickly get started with your favorite PivotTable layout. This feature is available for Excel 2016 on Windows as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get the latest update.
This month, we also included a tip when using OLAP connections to make your PivotTables much faster. Read on to learn more.
Personalize your PivotTable layout
There are two ways you can adjust the layout settings for the PivotTable defaults. One way is to simply click the newly added Edit Default Layout button under the File menu to display the Edition Default Layout dialog. Here you can make changes to many of your favorite layout options. Included are all the settings in the “Layout” chunk of the PivotTable Design contextual ribbon. We also included all the settings in the PivotTable Options dialog.
You can also import a layout from a PivotTable already in your workbook and customize the layout. This is a great way to start if you have a PivotTable in your workbook that has a layout you’d like to use all the time. Simply open the Edit Default Layout dialog, click anywhere within a PivotTable in your workbook and then click the Import button.
Either way, all new PivotTables you insert will have your favorite layout!
To learn more about how you can use this new feature, visit our support page.
Tip for OLAP PivotTables
If you use OLAP connections, making a change to your default layout could make the PivotTables you create much faster! Disabling the Subtotals and Grand Totals will help you take advantage of the performance improvements delivered in a previous update to Excel 2016.
In the Edit Default Layout dialog, simply set the Subtotals option to Do Not Show Subtotals and set the Grand Totals option to Off for Rows and Columns. This tip can work alongside changes to all the other options in the Edit Default Layout dialog. So feel free to keep toggling!
If you have any suggestions for a new feature you would like to see in Excel, head over to the UserVoice forum and become a part of the conversation!
Connect the power of Excel to your Facebook ad accounts with FAME
Facebook Ads Manager for Excel automates your campaign reporting by downloading your ads performance data directly into Excel sheets.
Facebook advertisers frequently rely on the power of Excel to manage their ads accounts and evaluate performance, but exporting multiple Facebook accounts to an Excel worksheet takes time. With Facebook Ads Manager for Excel (FAME) you can quickly run a single report to download data from multiple ad accounts, helping you save time and work faster.
Downloading XLS or CSVs from multiple ads accounts and organizing metrics for custom reports takes a good deal of manual effort. And reports to evaluate ads data are requested with increasing regularity. While it is possible to develop customized reporting solutions using the Facebook Marketing API, these App based tools rarely provide the familiarity or flexibility of Excel.
Facebook Ads Manager for Excel, or FAME, allows advertisers to build their report templates in Excel and dynamically download rich performance data directly into sheets. Data can be combined from multiple Facebook ads accounts without manual integration . And information can be refreshed weekly, or as often as every 15 minutes, to ensure your data is always up-to-date.
FAME is available for free in the Microsoft Office Store. The FAME add-in seamlessly integrates with Excel 2016 to provide reporting capabilities.
Sign in with your Facebook account, and you’ll be able to create and download reports. Pre-designed templates help you quickly pull down common metrics, or customize your reports to deliver exactly what you need to know.
Facebook Ads Manager for Excel requires Microsoft Excel 2016. To download FAME from the Office Store complete the following steps:
Open Excel 2016 on your computer.
Click the [Insert] tab.
Click [Store] to go to the Office Store.
In the [Search] box, type “Facebook Ads Manager” and then click <MAGNIFYING GLASS ICON>. You should now see Facebook Ads Manager for Excel in the list.
Next to Facebook Ads Manager for Excel, click [Add].
One of the great things about being on the Excel team is the opportunity to meet with a broad set of customers. In talking with Excel users, it’s obvious that significant confusion exists about what exactly is “big data.” Many customers are left on their own to make sense of a cacophony of acronyms, technologies, architectures, business models and vertical scenarios.
It is therefore unsurprising that some folks have come up with wildly different ways to define what “big data” means. We’ve heard from some folks who thought big data was working two thousand rows of data. And we’ve heard from vendors who claim to have been doing big data for decades and don’t see it as something new. The wide range of interpretations sometimes reminds us of the old parable of the blind men and an elephant, where a group of men touch an elephant to learn what it is. Each man feels a different part, but only one part, such as the tail or the tusk. They then compare notes and learn that they are in complete disagreement.
Defining big data
On the Excel team, we’ve taken pointers from analysts to define big data as data that includes any of the following:
•High volume—Both in terms of data items and dimensionality.
•High velocity—Arriving at a very high rate, with usually an assumption of low latency between data arrival and deriving value.
•High variety—Embraces the ability for data shape and meaning to evolve over time.
And which requires:
•Cost-effective processing—As we mentioned, many of the vendors claim they’ve been doing big data for decades. Technically this is accurate, however, many of these solutions rely on expensive scale-up machines with custom hardware and SAN storages underneath to get enough horsepower. The most promising aspect of big data is the innovation that allows a choice to trade off some aspects of a solution to gain unprecedented lower cost of building and deploying solutions.
•Innovative types of analysis—Doing the same old analysis on more data is generally a good sign you’re doing scale-up and not big data.
•Novel business value—Between this principle and the previous one, if a data set doesn’t really change how you do analysis or what you do with your analytic result, then it’s likely not big data.
At the same time, savvy technologists also realize sometimes their needs are best met with tried and trusted technologies. When they need to build a mission critical system that requires ACID transactions, a robust query language and enterprise-grade security, relational databases usually fit the bill quite well, especially as relational vendors advance their offerings to bring some of the benefits of new technologies to their existing customers. This calls for a more mature understanding of the needs and technologies to create the best fit.
Excel’s role in big data
There are a variety of different technology demands for dealing with big data: storage and infrastructure, capture and processing of data, ad-hoc and exploratory analysis, pre-built vertical solutions, and operational analytics baked into custom applications.
The sweet spot for Excel in the big data scenario categories is exploratory/ad hoc analysis. Here, business analysts want to use their favorite analysis tool against new data stores to get unprecedented richness of insight. They expect the tools to go beyond embracing the “volume, velocity and variety” aspects of big data by also allowing them to ask new types of questions they weren’t able to ask earlier: including more predictive and prescriptive experiences and the ability to include more unstructured data (like social feeds) as first-class input into their analytic workflow.
Broadly speaking, there are three patterns of using Excel with external data, each with its own set of dependencies and use cases. These can be combined together in a single workbook to meet appropriate needs.
When working with big data, there are a number of technologies and techniques that can be applied to make these three patterns successful.
Import data into Excel
Many customers use a connection to bring external data into Excel as a refreshable snapshot. The advantage here is that it creates a self-contained document that can be used for working offline, but refreshed with new data when online. Since the data is contained in Excel, customers can also transform it to reflect their own personal context or analytics needs.
When importing big data into Excel, there are a few key challenges that need to be accounted for:
•Querying big data—Data sources designed for big data, such as SaaS, HDFS and large relational sources, can sometimes require specialized tools. Thankfully, Excel has a solution: Power Query, which is built into Excel 2016 and available separately as a download for earlier versions. Power Query provides several modern sets of connectors for Excel customers, including connectors for relational, HDFS, SaaS (Dynamics CRM, SalesForce), etc. We’re constantly adding to this list and welcome your feedback on new connectors we should provide out of the box at our UserVoice.
•Transforming data—Big data, like all data, is rarely perfectly clean. Power Query provides the ability to create a coherent, repeatable and auditable set of data transformation steps. By combining simple actions into a series of applied steps, you can create a reliably clean and transformed set of data to work with.
Handling large data sources—Power Query is designed to only pull down the “head” of the data set to give you a live preview of the data that is fast and fluid, without requiring the entire set to be loaded into memory. Then you can work with the queries, filter down to just the subset of data you wish to work with, and import that.
•Handling semi-structured data—A frequent need we see, especially in big data cases, is reading data that’s not as cleanly structured as traditional relational database data. It may be spread out across several files in a folder or very hierarchical in nature. Power Query provides elegant ways of treating both of these cases. All files in a folder can be processed as a unit in Power Query so you can write powerful transforms that work on groups (even filtered groups!) of files in a folder. In addition, several data stores as well as SaaS offerings embrace the JSON data format as a way of dealing with complex, nested and hierarchical data. Power Query has a built-in support for extracting structure out of JSON-formatted data, making it much easier to take advantage of this complex data within Excel.
•Handling large volumes of data in Excel—Since Excel 2013, the “Data Model” feature in Excel has provided support for larger volumes of data than the 1M row limit per worksheet. Data Model also embraces the Tables, Columns, Relationships representation as first-class objects, as well as delivering pre-built commonly used business scenarios like year-over-year growth or working with organizational hierarchies. For several customers, the headroom Data Model is sufficient for dealing with their own large data volumes. In addition to the product documentation, several of our MVPs have provided great content on Power Pivot and the Data Model. Here are a couple of articles from Rob Collie and Chandoo.
Live query of an external source
Sometimes, either the sheer volume of data or the pattern of the analysis mean that importing all of the source data into Excel is either prohibitive or problematic (e.g., by creating data disclosure concerns).
Customers using OLAP PivotTables are already intimately familiar with the power of combining lightweight client side experiences in PivotTables and PivotCharts with scalable external engines. Interactively querying external sources with a business-friendly metadata layer in PivotTables allows users to explore and find useful aggregations and slices of data easily and rapidly.
One very simple way to create such an interactive query table external source with a large volume of data is to “upsize” a data model into a standalone SQL Server Analysis Services database. Once a user has created a data model, the process of turning it into a SQL Server Analysis Services cube is relatively straightforward for a BI professional, which in turn enables a centrally managed and controlled asset that can provide sophisticated security and data partitioning support.
As new technologies become available, look for more connectors that provide this level of interactivity with those external sources.
Export from an application to Excel
Due to the user familiarity of Excel, “Export to Excel” is a commonly requested feature in various applications. This typically creates a static export of a subset of data in the source application, typically exported for reporting purposes, free from the underlying business rules. As more applications are hosted in the browser, we’re adding new APIs that extend integration options with Excel Online.
Summary
We hope we were able to give you a set of patterns to help make discussions on big data more productive within your own teams. We’re constantly looking for better ways to help our customers make sense of the technology landscape and welcome your feedback!
Use the PowerPoint app to start this year with a new, fresh coat of creativity. What’s your blank canvas?
How to AutoFit Cell Width and Height to Cell Data in Excel
Many times, our text doesn’t fit perfectly in Excel cells. To adjust the size without having to drag and find the ideal fit, a double-click can take care of it for you. With a quick click, you can AutoFit your cells so that your text fits comfortably within your spreadsheet, and doesn’t trail off into the next cell.
See the video demonstration above and steps below.
•In your spreadsheet, find the cells where the text doesn’t fit.
•Say for instance your text in cell C3 is cut off by the text in D3.
•Go to the column title bar. •Hover over the space between columns C and D.
•There is a thin grey line between the columns.
•A new icon should appear.
•While the icon is present, double click and your column will AutoFit the text in column C. It will jump and readjust.
To AutoFit your text in row, follow these same steps.
Microsoft Office team today released a new Google Chrome extension that lets you create and open Word, Excel, PowerPoint, OneNote and Sway documents using Office Online right from the Chrome browser. You can also use the extension to open and view existing Office documents stored on OneDrive, on OneDrive for Business and on your computer right from Chrome.
The quickest way to view, edit, and create Office files in your browser.
Built for Chrome – Use Word, Excel, PowerPoint, OneNote, and Sway Online without needing Office installed.
Create with confidence – Use familiar formatting and layout options to express your ideas in full fidelity.
Work on the go – Get to your files from anywhere, thanks to integration with OneDrive and OneDrive for Business.
An Overview on how to Create Advanced Microsoft Dynamics CRM Online Reports using Microsoft Excel, Power Query and OData endpoint
By Ben Ward, Applications Analyst, MCTS, MCP, MS
Due to the cloud based nature of Microsoft Dynamics CRM Online, end users are not granted access to the backend SQL database (for a good reason which I will not go into now. Trust me, it’s long and pretty boring). This restriction to the SQL database makes pulling extensive reports a little more difficult than first thought. However, the good folks at Microsoft have provided an OData endpoint to be used with other products to allow extensive reports to be created. An OData endpoint is basically a URL for users to reference to access their data without permissions to manipulate the data with the SQL database. This ensures the data remains untouched inside the SQL database but users can download and access the data for other uses such as reporting.
Here is the high-level overview of how OData endpoints can be used:
The following example can be used to access your Microsoft Dynamics CRM Online OData endpoint:
[Your Organization Root URL]/XRMServices/2011/OrganizationData.svc
Once the OData endpoint has been referenced in Power Query, select the entities where the desire data resides and start editing the tables to fit your business needs. If you are familiar with SQL databases, Power Query editor will be very easy to use.
Note: Microsoft Visual Studio can also be used to access OData endpoints and create tables, which will be covered in another post.
About the author:
Ben has worked at Managed Solution for over two years and is currently working on CRM customization and administration, Microsoft SharePoint integration and customization as well as Business Intelligence analytics including SQL reporting. Ben is a Microsoft Certified Technology Specialist, Microsoft Certified Professional, Microsoft Specialist and has six Dynamic CRM certifications.
.png)
