The world [of Office] is changing…

Yigal Edery, Microsoft, Principal Group Program Manager, Office BI
Eli Schwartz, Microsoft, Senior Lead Program Manager

The world of productivity is changing rapidly. New technologies are born daily. Data is everywhere. People are everywhere, and on different devices. And they all want access to their data wherever and whenever they are.

In this session, we will show how Office has been evolving to fit the modern era. We will take a look at Excel and how it’s been evolving to support these needs, as well as at the Power BI offering coming from Microsoft to further enhance the life of you, the data analyst.

One app to rule your data: An introduction to Power Query.

Ken Puls

Power Query is one of the most exciting new components for Excel 2010 and Excel 2013. If you’ve ever needed to import data from a file, database or web page, this course is for you. Power Query adds a huge amount of functionality to Excel, making it easy to source, clean up and convert the data into a format that is easily useable by Excel. By the end of this one hour class, you’ll learn how we can use Power Query to:

• Import data from text files and web pages
• Append data from one table to another
• Merge two data sets together
• Import all files in a folder in one shot
• Un-pivot tables with ease
• Create dashboards from email in minutes

Text file, CSV file, Excel file, database, email or web page… you need one
app to rule your data: That app is Power Query for Excel.

GetPivotData

Roger Govier

Many people use formula based solutions for aggregating data for their Monthly or Quarterly reports. These reports often summarize thousands of rows of raw data and people make use of formulae such as SUMPRODUCT, SUMIF, COUNTIF, AVERAGEIF and their newer counterparts of COUNTIFS, SUMIFS and AVERAGEIFS to generate all of the summarised data.

This information is then displayed in a Dashboard or a formatted report for their managers to read.

All of the “heavy lifting” for producing these summaries of data can be achieved more easily with Pivot Tables – where not a single formula needs to be written.

However, many people are put off Pivot Tables because they don’t understand them, or they don’t like the format of the information is produced. GetPivotData is a little used, and not well understood method of pulling data that has been calculated via a Pivot table into a report of the exact layout and format required. This session is all about de-mystifying GetPivotData and showing how a single universal formula can be used to create the reports required.

The session will include

• Brief review of report summaries using SUMIF formulae
• A quick overview of generating Pivot Tables for large datasets,
  and the different formatting that can be achieved natively with Pivot
  Tables.
• An examination of what more than 70% of all Pivot tables produced
  actually are based upon
• Introducing the GetPivotData formula
• Dissecting GetPivotData and understanding better how it works
• Building a universal GetPivotData formula, that can be used with
  all reports.
• Alternatives to GetPivotData for extracting data from a Pivot table
  using Named ranges with Index and Match
• A few VBA examples how to easily generate the Named ranges

M is for Data Monkey: Simple tweaks for major effects

Ken Puls

Power Query’s user interface is incredibly effective and allows us to do some amazing things to our data.

But did you know that under the covers, the Power Query interface is a macro recorder?

In this class Ken will teach you how to make light edits to Power Query’s macro language: M. We’ll look at creating basic M functions, implementing dynamic file paths to your source data, consolidating multiple Excel workbooks and other practical techniques that require light manipulation in the M language.

You’ll leave armed not only with some M patterns, but the knowledge of how to easily implement them into your own solutions.

Practices that make working with Excel charts less painful

Jon Peltier

Using charts in Excel can be frustrating, especially with complicated data sets or complex chart styles.

There are a few things you can do with your data or with the chart’s own elements to make your life easier and make your charts more informative.

I’ll discuss:

• Data
  • Setting up the data
  • Dynamic data
  • Changing data
  • Series formula
• Formatting
  • Clean formatting
• Text
  • Data labels
  • Linking to cells
  • Dummy series
  • Data labels
  • Stacked chart totals
  • Floating bars

How to make your Excel Power pivot model professional.

Henk Vlootman

Excel Power pivot is an awesome tool, acting as bridge between your databases and Excel.

But there are some downsides to this combination. Your dashboard e.g. is bound to your Excel (Power pivot) model. It means that only one person at a time can view the dashboards.

If you make your Power pivot a server-based application, you create different dashboards in as much Excel models as you want. If you have a SharePoint farm the dashboards can also be published on that farm. You can easily port your Excel Power pivot model to a SQL Server tabular model.

But what are the bottlenecks?

In this session:

• Power pivot and the Microsoft platforms
• How to port an Excel Power pivot model to SQL server tabular mode
  (live)
• The main problem if you want to port your model
• The do’s and the don’ts

Working With Multiple Criteria

Liam Bastick

So how many left-handed Israeli women with three sons bought your product on Tuesdays in March?

Management loves to get to the bottom of multiple criteria analysis but how can you do it simply in Excel?

Are PivotTables always the answer? This session looks at what techniques to use when in your financial analysis using Excel.

Topics will include:

• What do we mean by multiple criteria analysis?
• SUMIF, SUMIFS and SUMPRODUCT
• DBASE functions
• The advantages and disadvantages of PivotTables
• Introducing Power Pivot
• What to use when and why

Business Intelligence in Excel vNext

Yigal Edery, Microsoft, Principal Group Program Manager, Office BI
Eli Schwartz, Microsoft, Senior Lead Program Manager

Business Intelligence and data analysis are becoming more and more critical in today’s world, with massive amounts of data available to support organizations striving to make data-driven decisions. Excel is the data analysis tool of choice for one billion users around the world.

In this keynote, we will discuss how Microsoft is improving Excel vNext to enable much more powerful and streamlined data analysis experiences for the excel users and enabling you to cope with the data challenges of the modern era.

Optimizing your Power Pivot models using Power Query (and other performance tips)

Ken Puls

You know the scene; you’ve built a beautiful Power Pivot model that has solved all of your organization’s problems. You open it up, click Refresh and wait patiently for all the data to stream in. That’s okay, there’s a lot of data and you expect that. Then you click a slicer and… wait…

Time during the initial data load is one thing, but consumer’s patience during use is another. Their tolerance for latency is much less.

In this session we’ll explore the things you can do to increase your Power Pivot model performance. We’ll look at the key factors that affect performance, as well as the things you can do to deal with them. In addition, we’ll look at how sourcing your data with Power Query can give you ultimate control over your data’s size and shape, and tricks to optimize it for Power Pivot consumption.

Making chart types that Excel didn’t know about

Jon Peltier

Excel offers a wide variety of built-in charts, some very useful. But some of the most useful chart types you may want to use are not built into Excel. With a little magic, you can use regular Excel charts to achieve the appearance you want.

• Waterfall charts
• Boxplots
• Marimekko charts
• Clustered-stacked charts

Make your VBA UDFs efficient

Charles Williams

VBA UDFs have a reputation for being slow.

I will show you a few simple steps that can make your VBA UDFs run 100’s of times faster:

• Minimising the overhead
• Minimise data transfer times
• Volatility
• Use Excel’s built-in functions efficiently
• Variants, ranges arrays
• Whole column references
• VBA Array functions
• Handling uncalculated cells
• …

Avoiding Common Mistakes in Valuations Modelling

Liam Bastick

This session looks at an area of financial modelling that is often developed incorrectly: creating a valuation from your financial model.

Aimed at Excel professionals working or with an interest in valuations, this session provides an introduction to:

• Creating the correct free cash flows from modelled cash flow statements
• Understanding the difference between equity, enterprise and project valuations
• Getting the logic wrong: the relationship between control premia, majority and minority shareholder equity valuations
• Modelling discount factors correctly
• Frequent mistakes in terminal value calculations
• Why ‘IRR’ are the first three letters of ‘irrelevant’

Slicers and Timelines and their use with Pivot Tables and Tables

Roger Govier

This session will show how Slicers (introduced with Excel 2010) for use with Pivot Tables, and Timelines (introduced with Excel 2013), which along with Slicers can be used with Pivot Tables and Tables to make reporting neater and easier to filter.

Straight “out of the box”, Slicers are big and bold and ugly and take up far too much of the screen “real estate”. Many people who have only looked at them cursorily are put off and believe they cannot be effectively used in Dashboard reporting.

This session will show how easily they can be formatted for better use, including some of the formatting methods which are “hidden” from the user.

The session will include

• When Slicers were introduced
• Why Slicers were introduced
• What Slicers are and how they were designed for use with Pivot Tables
• Formatting slicers to be more “user friendly” and take up less screen “real estate”
• Advanced formatting with methods which are not at all obvious from the user interface.
• Using Slicers with Tables
• Using Timelines with Tables and Pivot Tables
• A brief look at the Slicer Object model and how they can be manipulated with VBA

Checking Your Excel Spreadsheets

Liam Bastick

For those that audit models, have their models audited or those who don’t want to be embarrassed by reporting staff turnover as #DIV/0! (and being one of those staff turned over), this session provides brief tricks and tips for checking your models.

Discussion will include:

• Using error checks to your advantage and highlighting the three types of check
• Developing a model finalisation checklist
• Simple testing techniques
• Excel’s built-in auditing tools
• Cracking circular references
• The importance of charting
• Overview of audit tools available