ASAP Utilities for Excel

ASAP Utilities is and always will be the best hands-down add-on for Excel day-to-day users.

http://www.asap-utilities.com/blog/

I used the "Copy values to empty cells below filled cells selection" function twice and it told me I saved 2 minutes using ASAP Utilities.  I believe it too!

Another command that a couple finance people I worked with in the past could have used is "Export worksheet as separate files", especially when dealing with 500 franchise worksheets.

Convert Text? to numbers, quick numbering, random numbering...

Not to mention a few other tricks...

Please do not remove this header 
Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com)

Title	Active	Name	Full name	Type
Analysis ToolPak	FALSE	analys32.xll	C:\Program Files\Microsoft Office 15\root\office15\LIBRARY\ANALYSIS\analys32.xll	Add-In
Analysis ToolPak - VBA	FALSE	atpvbaen.xlam	C:\Program Files\Microsoft Office 15\root\office15\LIBRARY\ANALYSIS\atpvbaen.xlam	Add-In
ASAP Utilities	TRUE	ASAP Utilities.xla	C:\Program Files (x86)\ASAP Utilities\ASAP Utilities.xla	Add-In
Euro Currency Tools	FALSE	eurotool.xlam	C:\Program Files\Microsoft Office 15\root\office15\LIBRARY\eurotool.xlam	Add-In
Solver Add-in	FALSE	solver.xlam	C:\Program Files\Microsoft Office 15\root\office15\LIBRARY\SOLVER\solver.xlam	Add-In
Power View	TRUE	AdHocReportingExcelClientLib.AdHocReportingExcelClientAddIn.1	not available	COM Add-In
ASAP Utilities Library Package (is needed for ASAP Utilities)	TRUE	ASAP_Utilities.adConnect	not available	COM Add-In
Microsoft Power Map for Excel	TRUE	ExcelPlugInShell.PowerMapConnect	not available	COM Add-In
Microsoft Power Query for Excel	TRUE	Microsoft.Mashup.Client.Excel	not available	COM Add-In
Inquire	TRUE	NativeShim.InquireConnector.1	not available	COM Add-In
Microsoft Office PowerPivot for Excel 2013	TRUE	PowerPivotExcelClientAddIn.NativeEntry.1	not available	COM Add-In

Kasper de Jonge PowerPivot Blog » Why SharePoint 2013 (ECS) is great for Multi dimensional!

One of the cool new features of Sharepoint 2013 is Excel Services with Field Lists.   Previously, design of a pivot table would need to be done prior to publishing to the server.   Now users have a way of designing self-service reports in the browser. 

Drag and drop, self service reporting is here.
Kasper de Jonge PowerPivot Blog » Why SharePoint 2013 (ECS) is great for Multi dimensional!

Frederik Vandeputte - Switch function Trick

=SWITCH(TRUE();
[Score] >=1 && [Score] <5; "C";
[Score] >= 5 && [Score] < 8; "B";
[Score] >=8 && [Score] <=10; "A";
"Wrong Value"
)

So SWITCH(TRUE();expression1; value1; expression2; value2; expression3; value3; .... ElseValue)  is the trick for rewriting nested IFs in DAX.

Frederik Vandeputte

the data connection uses windows authentication and user credentials could not be delegated

3 things to get Sharepoint 2010 and Excel Services 2010 working with Windows credentials against Analysis Services and/or Power Pivot.

1. Under Central Admin, Manage Service Applications, Start Security Token Service App.
2. Under Central Admin, Manage Farm Features, Activate Excel Service.
3. In c2wtshost.exe.config in c:\program files\windows identity foundation\v3.5, modify allowed callers with

Remember that when testing security locally from the server, it is the logged-in Windows user, not the Sharepoint user, that is passing the credentials.

Further details.

http://powerpivotgeek.com/2010/02/08/the-data-connection-uses-windows-authentication-and-user-credentials-could-not-be-delegated/

Kasper de Jonge BI Blog

More on Sharepoint & BI solutions.

They released a blog specific to the subject: http://blogs.msdn.com/sharepointbi where you can find more information about SharePoint Insights. You can also check out the official SharePoint Insights page and download the new datasheet and whitepaper to help you start thinking about what SharePoint 2010 can help your organizations use Business Intelligence.

Kasper de Jonge BI Blog

Microsoft Excel Boxes | Facebook

Office is on Facebook?  Apparently!

Cori Have you heard of Power Pivot? Check out Microsoft's new Excel Add-In! www.facebook.com/powerpivot - Become a Fan!

PowerPivot

Microsoft's PowerPivot for Excel 2010 is a data analysis tool for business users that delivers unmatched computational power directly within the software you already know and love—Microsoft Excel. With PowerPivot, you can transform mass quantit...ies of data with incredible speed into meaningful information to get the business answers you need in seconds. Then, you can effortlessly share your findings with others. PowerPivot can even help your IT department improve operational efficiencies through SharePoint-based management tools.Read More

Technology Product / Service:327 fans

Microsoft Excel Boxes | Facebook

PowerPivot Team Blog : Introduction to Data Analysis Expressions (DAX) in Gemini

Another language to learn?  Yes, if you want to use PowerPivot calculated members.  Fortunately it’s not that complex…

What Is DAX?

DAX is an expression language based on Excel formula syntax. Because DAX is designed to work with multiple tables of data, it includes functions that implement relational database concepts. DAX also adds new functionality that allows you to create dynamic aggregations, making DAX formulas smart about calculating values in a PivotTable. Excel users will be happy to learn that DAX includes part of the existing Excel function library, and many functions are the same as Excel functions.

PowerPivot Team Blog : Introduction to Data Analysis Expressions (DAX) in Gemini

One limitation… no spreadsheet-driven data constants or variables.

DAX Doesn’t Refer to Data by Cell Location

In DAX, we do not have the Excel notion of addressing a single cell of data. We do not identify values by where they are located in the grid. We can’t refer to cell B23 or range B12:C15. DAX functions always operate on columns of data in a table. Instead of viewing our data set as a large rectangle of cells, we think of the data as a set of tables which contain columns and rows.