Licenses required in Excel 2013 & Office 365 for Power View and/or Power BI:

Standard

Here’s a quick chart that I created based on reading up from different sources about licenses required in office 2013/office 365 to use Power View and/or Power BI. It was not straight forward and I had to use multiple sources to find this information, so hopefully this helps you:

Power View Power BI Excel 2013 License

Please Note:

Double check w/ official sources. The chart is meant to guide to find the right license for your needs. Please use this as a starting point & use official resources before making a purchase decision.

Sources Used:

http://www.microsoft.com/en-us/powerBI/pricing.aspx#fbid=ygmkLW5EEH9

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02/27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx

http://sqlblog.com/blogs/marco_russo/archive/2013/08/19/excel-2013-stand-alone-now-includes-powerpivot.aspx

http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx

http://cwebbbi.wordpress.com/2013/02/01/office-2013-office-365-editions-and-bi-features/

A Note about “Edit Report” button seen on Power View reports in SharePoint:

Standard

Power View reports that are hosted on SharePoint has “Edit report” which you cannot hide:

Power View Edit report buttonWe can’t hide this button but how do you give someone “read-only” access to Power View?

To do so, you’ll have to host the Power View report in a SharePoint library where a user has read-only access. When a user opens the report, he/she could click on edit report & make changes to Power View report but they won’t be able to save the report instead they’ll see an error:

Power View Access Denied

Official resource: http://office.microsoft.com/en-us/excel-help/power-view-in-sharepoint-server-create-save-and-print-reports-HA102834736.aspx

[update]:

Also, check out this MSDN forum thread, it describes a solution to hide the “toolbar”: http://social.technet.microsoft.com/Forums/sharepoint/en-US/cec0feff-1055-4b53-a5ae-00373abdabdd/how-to-disable-edit-report-option-in-powerview-report-in-sharepoint-2013?forum=sharepointdevelopment

How to add secondary axis in a chart in Excel 2010?

Standard

In this post, I’ll show you how you can add secondary axis in a chart in Excel 2010:

First up, Why do you need secondary axis?

Look at the Data and the chart that I just created:

creating a chart from Data excel 2010

Notice something? Column A is not “visible” – Is it? So Now let’s see the effect how adding a Secondary Axes:

secondary axis in a chart in excel 2010

Looks better? Here are the steps:

1. Select the chart

2. can you see charts tool options in the menu bar? Yes? Great!

3. Switch to Format Tab

4. select the column that you want to put on a secondary axes:

select column in format area data excel chart

5. After selecting the column, click on Format Selection

6. In the dialog box, select secondary axes:

secondary axis excel 2010 chartclick on close.

7. Can you see the secondary axis now? Great!

secondary axis in a chart in excel 2010

8. Let’s do one more thing!

Let’s change the chart type of the secondary axis. Here are the steps:

a. Make sure the column is selected from the format Tab

b. Switch to the Design Tab under the charts Tool

c. Click on change chart type and select the chart of your choice. I select a column chart and this is how it looks:

more than one chart type on a single chart in excel 2010

Conclusion:

In this post, we saw how to add a secondary axis. We also saw how to change the chart type of the secondary axis.

Trying out FLASH FILL which is a new Excel 2013 Feature:

Standard

I learned about an interesting new feature in Excel 2013 which they call “FLASH FILL”. Instead of trying to explain it, Let me demo it:

1. I’ve following data in Excel 2013:

Data Set Excel 20132. Now the requirement is to split the FULL NAME into FIRST NAME and LAST NAME.

So I entered Kim in cell B2

Then I typed just Mi in the cell B3 and Excel 2013 predicts correctly for cell B3, B4 and B5 that I am filling up the first name from the Full Name column.

See:

Predictive Data Entry by Excel 2013I hit enter for the column First Name.

I performed similar steps for the column Last Name. And there you have it:

FLASH FILL options excel 2013That’s about it for demo of Excel 2013’s Flash Fill – very cool!

I’ve posted how to split the values in a column for Excel 2010, you can read that here: How to split the content of one excel cell into separate columns?

Your comments are very welcome!

How to clean similar textual data in Excel via Fuzzy lookup add-in?

Standard

In this post, we would see how to get started with Fuzzy look-up add-in for excel.

First up, Which problem does Fuzzy Lookup add-in for excel solve? It cleans similar (a.k.a matching) textual data in Excel. E.g. “Mr Paras Doshi”, “Doshi Paras”, “Paras A Doshi” are similar and may refer to the same person – Fuzzy look-up helps you detect such similar textual data. This add-in is really handy if you are “combining” data from different systems where the data is not in the same format – using this add-in you can detect similar looking text and clean the data-set at hand. With that, Here are the steps to download, install and play with this add-in:

1) Download “Fuzzy Lookup add-in for Excel. Read: Over view, system requirements and Instructions

URL: http://www.microsoft.com/en-us/download/details.aspx?id=15011 

(If this doesn’t work – search for “Fuzzy lookup add in for excel”

2) Note the sample excel file called “Portfolio” that comes with the download files. We’ll open it after the Successful installation of the add-in:

1 excel 2010 sample excel file to play with fuzzy lookup

3)Install it.

I left the default on all dialog boxes.

4) Open the sample file: “portfolio

5) Click on Install if you see a dialog box “Microsoft Office customization folder”

(please read the message in the dialog box too)

6) Can you see Fuzzy Lookup in Excel Toolbar? Yes? Great!

7) Now if you have opened the Portfolio file then you’ll see a tutorial on how to get started on the worksheet named portfolio:

3 excel 2010 tutorial to play with fuzzy lookup

8) follow the above tutorial to get started. I just did!

2 excel 2010 tutorial to play with fuzzy lookup output

Conclusion:

In this post, we saw how Fuzzy Lookup add-in for excel 2010 help you find matching text from two data sources. I hope that helps.

 

Microsoft Business Intelligence: Power View can be Exported to PowerPoint (PPT)!

Standard

A short post to point out that Power View reports can be exported to PowerPoint (PPT) – and PPT slides would also have a “click to interact” button and if the security plus network access is configured correctly then the Interactive data exploration without leaving the Slides! very cool. I had pointed this fact out earlier here. And the official resource can be found here: Export a Power View Report to PowerPoint

I am referring to SQL Server 2012 BI and SharePoint 2010 here. And here is a  step by step guide:

1) Create report > Save it > And then Go to File > Export to Power Point

Export Power View reports to PowerPoint 2010 SQL Server 2012

A note about security: “Export to PowerPoint” requires windows authentication method.

2) Select the location of the PPT file that will have the “Power View” reports.

Export to PowerPoint requires windows authentication method File Location

Now you can store the PowerPoint file at any place but it would be important to consider whether the machine from which it would be accessed has the network access to the SharePoint Power View reports. If not, the Power View reports would just show up as “static images” and the click to interact would not work.  Important security point to consider for your scenario.

3) Open the File > start slideshow > Navigate to the slide (if applicable) > can you see the “click to Interact” button? Yes? Great! you can do interactive data exploration from the PowerPoint environment itself! I find this very Impressive because Power View is meant for Business Users to do Data Exploration and create rich visualizations and once they are done with it – they can export it to PowerPoint and show their Power View chops to their boss and peers and more importantly, make better business decisions. Any-how, here’s the image:

CLICK to Interact button Export to PowerPoint Powe View SQL Server 2012 SharePoint 2010

That’s about it!