Get started with Business Intelligence in the new Office

www.office.com/setup Blogs: This post is brought to you by Seayoung Rhee, Product Marketing Manager in the SharePoint Product Marketing Group.

Reliable business intelligence and insight are key to a thriving business. Excel, SharePoint and Office 365 make business intelligence (BI) in the new Office more accessible to everyone across an organization as part of their everyday work.  New and enhanced features in familiar tools such as Excel empower everyone in a business to easily explore, model, analyze, and visualize data from various sources. With SharePoint, users of all levels can collaboratively develop and share insights through dashboards and scorecards, and this experience continues seamlessly in the cloud with Office 365. For corporate environments, these features are further enhanced by SQL Server for higher levels of performance and scalability both on-premises and in the cloud with BI Azure. Ultimately with Microsoft BI, anyone in the organization can develop the insights that help drive new discoveries and make better, more informed decisions.

Explore your data

PowerPivot was a popular add-in to Excel 2010, allowing users to create large data models with hundreds of millions of rows in Excel. This feature is now natively embedded in Excel to reduce the hassle of downloads and installation. PowerPivot is also supported in SharePoint with SQL Server Analysis Services where the workbooks with PowerPivot models can be shared broadly across the organization. New features like Quick Explore aid users in navigating their data, and Quick Analysis provides previews of their charts, graphs, and scorecards.

Users can:

  • Combine and analyze large datasets with PowerPivot
  • Summarize data and discover trends with Quick Explore
  • Instantly preview charts and pivot tables with Quick Analysis

www.office.com/setup

Visualize your insights

Introduced with SQL Server 2012, Power View provided a canvas to create visual dashboards in SharePoint. Now this feature is embedded into Excel and SharePoint so that business users can create stunning reports to share and interact across the organization. Excel Services now supports the Field List to pivot and change views in the browser. We’ve also enhanced PerformancePoint Services to offer additional features such as themes, filter enhancements, authentication enhancements, a new BI portal, and server-side migration.

Users can:

  • Perform interactive data exploration with Power View
  • Share reports with your colleagues with Excel Services
  • Work together to make decisions on dashboards with PerformancePoint Services

www.office.com/setup

Control your assets

Self Service BI has often raised concerns for IT Pros in managing the integrity of reports and security. With the new Inquire tool in Excel and Audit and Control Manager in SharePoint, IT governance teams can now further manage self-service BI for end user created assets. Together, these features will aid in comparing various versions of reports and tracking changes in report libraries to ensure one version of the truth of your insights.

Users can:

  • Consolidate and simplify management with SharePoint
  • Diagnose workbooks for errors and track changes with Spreadsheet Inquire
  • Manage spreadsheet risk within an organization with Audit and Control Manager

www.office.com/setup

Getting started

A great way to get started using Microsoft BI is through our new Microsoft BI Solution Builder online tool that will help configure the right platform for your organization.

Try it out today at: http://www.bisolutionbuilder.com/

You can find more resources on Microsoft BI at the Microsoft Business Intelligence blog.

Frequently asked questions

Does Office 365 support BI features?

With the new updates to Office 365, we now support features like Quick Explore, PowerPivot and Power View in SharePoint Online for basic BI scenarios. However, there are a few limitations compared to the on-premise environment:

  • Workbook size limited to 10MB
  • No external data refresh
  • No PerformancePoint Services
  • No PowerPivot Gallery

We will announce details on the full Microsoft Cloud BI offering in the future.

Are Excel workbooks backwards compatible?

The data model-enabled workbooks in Excel 2013 are not backward compatible with previous versions of Excel. However, Excel 2010 workbooks with PowerPivot models will be prompted for automatic upgrade to Excel 2013 Data Models when opened in the Excel 2013 client for forward compatibility. Excel 2010 workbooks saved in SharePoint will need to be opened in the Excel 2013 client and upgraded to the newer data model to be used in Excel Services in SharePoint 2013.

Are BI features supported in Office Home and Student RT?

On the client side, the embedded Excel in Office Home and Student RT shipped with Windows RT does not include the majority of BI features that the full Excel 2013 Professional Plus client provides. In particular, PowerPivot, Power View, Quick Explore, and Inquire is not available in Office Home and Student RT.

On the SharePoint side, most features are supported in the mobile browsers of today’s tablets, except the web parts that require Silverlight, such as the PowerPivot Gallery, Power Views in Excel Services, and Decomposition Trees in PerformancePoint Services.

Seayoung Rhee

Original Post: https://blogs.office.com/2013/04/03/get-started-with-business-intelligence-in-the-new-office/

Securing company data and avoiding risk with Office 365 (Video)

www.office.com/setup Blogs: Today’s post was written by Nick Portello, Network Manager, Steve Moore Chevrolet. Read more Office 365 customer stories here.

Office 365 Customer Steve Moore Chevrolet_Nick PortelloWhen I joined Steve Moore Chevrolet as the sole IT staffer six months ago, the dealership was in dire need of new technology. Employees coped with aging PCs, an outdated POP3 email service, a poor mobility experience, data loss, and no collaboration tools. Then I heard about Microsoft Office 365, which gives you the latest version of Microsoft Office, plus a suite of cloud-based communication and collaboration tools that solved all our problems. Unlike Google Apps, the UI was familiar to our employees and Microsoft offered a flexible subscription-based payment option, which is ideal for our fluctuating workforce. Now that our documents are stored in Microsoft SharePoint Online and SkyDrive Pro, we have easy access to collaboration tools with robust security. I no longer worry about falling out of compliance with Chevrolet auditors, losing important financial data, or having to pay fines that could reach $250,000. I can also find data on-demand for Chevrolet auditors and use my mobile device to look up information, which saves about 40 percent of every day-I’m no longer running between my computer and the sales, service, and administration departments. I can focus on what I really need to do. Thanks to Office 365, we are all able to get on with our work!

Learn more about Steve Moore Chevrolet and Office 365 by watching the video below and reading the case study.

Original Post: https://blogs.office.com/2013/04/03/securing-company-data-and-avoiding-risk-with-office-365-video/

Update to SkyDrive app for iOS

www.office.com/setup Blogs: We aim to make SkyDrive the place for all your documents, notes, photos, videos and other files.  With the release of Windows 8 and Windows RT back in October, more and more people every day are using SkyDrive for their most important files through the SkyDrive app, as well as through SkyDrive integration in File Explorer.  Of course, there are great SkyDrive experiences for Windows devices, but being the place for all your files means we invest a significant amount of effort ensuring you have a great experience across all the devices you want to use.

Today, we’re excited to release v3.0 of the SkyDrive app for iOS that includes improved photo features, an updated UI, and a number of other new features and enhancements. You can install or update the SkyDrive app for iOS now via iTunes.

 

Here are the updates we’ve made to v3.0 of the SkyDrive app for iOS:

  • Support for iPhone 5 and iPad Mini
  • Updated app icons and user experience
  • Works better with your photos:
    • Download full resolution photos to your iPhone or iPad
    • Control the size of photos you upload and download
    • Photo metadata is retained when you upload to SkyDrive
  • Opening and saving files to SkyDrive works better with other apps on your iOS devices
  • Many other small changes, bug fixes and performance improvements

 

SkyDrive-for-iOS_thumb_1A7913B3 - www.office.com/setup

 

 

Install or update the SkyDrive app now via iTunes. As always, we hope you enjoy these updates and we look forward to hearing what you think.

 

Mike Torres, Group Program Manager, SkyDrive apps

Original Post: https://blogs.office.com/2013/04/03/update-to-skydrive-app-for-ios/

Google pulling the rug out again?

www.office.com/setup Blogs: Remember the vintage game “Pull the Rug Out?” It is a board game where players stack different items on top of a rug. Eventually, one of the players tries to pull out the rug without tumbling the pieces stacked on top of it.

Google’s recent announcement that it is phasing out several additional services, including the highly popular Google Reader, reminded me of this game, as I recall that the stack most always toppled to the ground. Google introduced Google Reader, gradually built up its popularity, and then pulled the rug out with little warning, causing its customers to stumble. As one Google customer put it: “Google spends millions of wasted dollars on pet projects, then kills one of their best products on a whim.”

Google’s most recent spring cleaning brings the total number of services it has discontinued to 70 in just a year-and-a-half. That’s right-a whopping 70 services that have been shut down in just 18 months.

Among the services Google will discontinue with little warning is Google Cloud Connect, introduced with a lot of fanfare just two years ago. Cloud Connect is a plug-in that enables Google users to share and edit Microsoft Word, PowerPoint and Excel files. To continue collaborating with Microsoft Office, Google users have just four weeks to uninstall Cloud Connect and install Google Drive. Companies that can’t get to it by April 30 will be out of luck, leaving them without a way to collaborate on Office documents used by millions of employees, partners, and customers.

But of all the services Google is discontinuing this time, the one that’s drawing the biggest backlash is Google Reader, a service used by hundreds of thousands of users to keep track of their favorite websites and blogs via RSS feeds.

The news of Google Reader’s shut down drew outrage from users, who put together a petition demanding that Google reinstate the service. “Our confidence in Google’s other products — Gmail, YouTube, and yes, even Plus — requires that we trust you in respecting how and why we use your other products,” the petition says. “This isn’t just about our data in Reader. This is about us using your product because we love it, because it makes our lives better, and because we trust you not to nuke it.”

Many users said the shutdown undermined their trust in Google. As one power user told the Huffington Post. “I’m a Google product user that invested a lot of time in many of those products because Google gave me something in return for what I give them – my data.

Shutdown of Google Reader because of a ‘lack of consumer appeal?’ No way. The simple reason: RSS can’t be controlled and monetized easily,” another user wrote.

What can Google customers conclude from the latest round of Google’s spring cleaning? First, there’s no roadmap they can count on. While discontinuing underperforming services is understandable, IT managers need a product roadmap that will allow them to plan their product strategy. Unlike Office 365, which is updated on a regular schedule, and offers users a 12-month advance notice of significant changes, Google often pulls the rug out with little warning, forcing users to abruptly shift gears.

As one reporter has put it, Google’s lack of a roadmap is unsettling. “I could be part of the in crowd and say that I get it,” wrote John McGreavy of Information Week. “But I’m not sure I do. It can take me six months to socialize (my favorite new buzzword) an innovation, and another six to implement it. Google, is that product going to be around after six months, or replaced?”

Second, because Google’s an advertising company, if there’s no way to capitalize on a service via advertising, it appears that service gets the boot. As Brian Solomon of Forbes points out, Google’s business model is to introduce products for free, hoping to make money later down the line. But instead of asking users to pay for these services, Google instead “shoehorn[s] their massive user base to compensate in other ways: ever encroaching advertising and declining privacy.”

There was no obvious way to capitalize on Google Reader with advertising, writes Solomon. “Because Google couldn’t find a way to capitalize on Reader through indirect means like advertising  … they feel no loyalty to keep it open – even when millions of people depend on it,” he says.

If you’re a Google customer, maybe it’s time you evaluate whether you want surprises like these. As a business, you invest a tremendous amount of resources into the productivity of your users, and Google’s little surprises compromise that productivity. Productivity impacts your business’ bottom line — how many times can you afford to have the rug pulled out from beneath you?

Original Post: https://blogs.office.com/2013/04/04/google-pulling-the-rug-out-again/

Using conditional formatting to highlight dates in Excel

www.office.com/setup Blogs: This tutorial on using conditional formatting to highlight dates is brought to us by MVP Frédéric Le Guen, with special acknowledgment and thanks to Ken Puls for assistance with translation from French to English. 

Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this.

Taking this functionality a step further, when you mix date functions with conditional formatting, you can create spreadsheets that display date alerts automatically when a deadline is near or differentiates between types of days, like weekends and weekdays.

The basics of conditional formatting for dates

To find conditional formatting for dates, go to

Home > Conditional Formatting > Highlight Cell Rules > A Date Occuring.

www.office.com/setup

You can select the following date options, ranging from yesterday to next month:

www.office.com/setup

These 10 date options generate rules based on the current date. If you need to create rules for other dates (e.g., greater than a month from the current date), you can create your own new rule.

Below are step-by-step instructions for a few of my favorite conditional formats for dates.

Highlighting weekends

When you design an automated calendar you don’t need to color the weekends yourself. With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function.  Assume that you have the date table–a calendar without conditional formatting:

www.office.com/setup

To change the color of the weekends, open the menu Conditional Formatting > New Rule

www.office.com/setup

In the next dialog box, select the menu Use a formula to determine which cell to format.

www.office.com/setup

In the text box Format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2)>5

The parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends.

www.office.com/setup

Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.

Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

www.office.com/setup

Click OK, then open Conditional Formatting> Manage Rules

www.office.com/setup

Select This Worksheet to see the worksheet rules instead of the default selection. In Applies to, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.

www.office.com/setup

Now you will see a different color for the weekends. Note: This example shows the result in the Excel Web App.

Highlighting holidays

To enrich the previous workbook, you could also color-code holidays. To do that, you need a column with the holidays you’d like to highlight in your workbook (but not necessarily in the same sheet). In our example, we have US public holidays in column AH (as related to the year in the cell B2).

www.office.com/setup

Again, open the menu Conditional Formatting > New Rule. In this case, we use the formula COUNTIF in order to count if the number of public holidays in the current month is greater than 1.

=COUNTIF($AH$4:$AH$16,B$5)>1

Then, in the dialog box Manage Rules, select the range B4:AF11. If you want to highlight the holidays over the weekends, you move the public holiday rule to the top of the list.

www.office.com/setup

This example in the Excel Web App below shows the result. Change the value of the month and the year to see how the calendar has a different format.

Highlighting delays

In case we want to change the color of cells based on our approach on a date again, we will use conditional formatting to make it work for us.

In the following example, we show:

  • yellow dates between 1 and 2 months
  • orange dates between 2 and 3 months
  • purple dates more than 3 months

We then construct three rules conditional formatting using formula DATEDIF . Respectively for the three cases the following formulas:

=DATEDIF($B2,$E$2,”m”)>0

=DATEDIF($B2,$E$2,”m”)>1

=DATEDIF($B2,$E$2,”m”)>2

www.office.com/setup

In the Excel Web App below, try changing some dates to experiment with the result.

Color scales

Rather than choose a different color set for each period in our timeframe, we will work with the option of color scales to color our cells.

First, go into a new column (column E), calculate the difference in number of days in a year again with the DATEDIF formula and the parameter “yd”.

=DATEDIF($D2,TODAY(),”yd”)

Then choose the menu Conditional Formatting> New Rule option Format all cells based on their value and choose the following options:

    • Scale = 3 colors
    • Minimum = 0 red
    • Midpoint = 10 yellow
    • Maximum = 30 white

www.office.com/setup

The result is a gradient color scale with nuances from white to red through yellow.  The closer to 0, the more red it will have, the closer to 10 the more yellow, and the closer to 30 the more white.  In the Excel Web App below, try changing some dates to experiment with the result.

Frédéric Le Guen

Original Post: https://blogs.office.com/2013/04/04/using-conditional-formatting-to-highlight-dates-in-excel/