Office In-Depth: Microsoft Excel

A few Fridays back marked the launch of the latest version of Microsoft’s eponymous Office lineup, and to celebrate we are going to be releasing a series of Office 2013 posts, including Reviews and How-Tos, to find out what Microsoft have brought to the table in this shiny new suite of Productivity software.

Today we’re talking about everyone’s favourite spreadsheet package, Excel!

As I mentioned in part one, Microsoft have focused on three central goals in Office 2013:

  • To integrate tightly with the Cloud, Microsoft has allowed users to access their files and settings via their Skydrive service.
  • A rejuvenated user interface, to blend with the radical changes sweeping in with the introduction of the Metro UI.
  • Universal use, across all kinds of Windows devices with the addition of enhanced pen and touch support.

So far we’ve seen some awesome new Cloud features, including saving files and settings to Skydrive, and Cloud apps; a completely refreshed UI; and multi-device support.

Introduction

As before, we are going to be focusing on four key points. This time, we are going to be looking at:

Business Intelligence, and why (In my opinion) Excel 2013 is on track to become number 1 in the business intelligence package on the market today! Secondly, we are going to be finding about a whole range of new core functions such as  Quick Analysis, Flash Fill and Multi-Monitor Support. Thirdly, we’ll be looking at how Excel 2013 is changing how we handle our data, including a look at the new Excel Button. Finally, we’re going to be looking at the new spreadsheet analytics and auditing capabilities.

Business Intelligence

Business Intelligence, much like “Synergy” used to be a word reserved for white-collar boardrooms and meetings dedicated to “What colour paper should we print our service invoices on”; nowadays it has become a crucial part of the Data Cycle. Excel has added a whole host of new features for “Self-Service” Business Intelligence features,  to make it easier for day-to-day users to present data in a more digestible format.

One of the major changes that Microsoft have made in Excel 2013, is the integration of the Vertipaq engine (Now known as In-Memory BI Engine) which allows for faster processing of large spreadsheets and datasets. This is a welcome change form previous version, allowing for Excel to utilise the higher-specification of in-office computers that we are seeing.

Power Views

The core of presenting BI in Excel is the Power View, the Power View tool allows users to create an internal Data Model which can be edited in a Power View Sheet in Excel. According to Microsoft:

A workbook can contain only one internal Data Model, [but] you can base a Power View sheet on the Data Model in that workbook or on an external data source. A single Excel workbook can contain multiple Power View sheets, and each of the sheets can be based on a different data model.

Power View sheets can contain charts, tables and visualisations, independent from a workbook’s internal data mode, including the use of Key Performance Indicators (KPIs) and Relationships between different tables inside of the Workbook (Similar to how you would with an Access Database).

I have been playing around with this feature for a little while, and I’m completely in love with it! As someone that does a lot of data analysis and presentation in his day to day work, having all of this available without having to use plug ins and complex vbscripts is a lifesaver! Especially with Oracle’s Essbase Excel-addon being End of Life’d. My only major issue with Power View is that Power View sheets can only be edited in the desktop app and Excel Services, and not in the Web App or Office 365

PowerPivot

If you’ve ever referred to yourself as an Excel “Poweruser”, you’ve probably used Pivot; and I don’t mean the element in Tony Hoare’s ‘quicksort’ algorithm either! (he said as he pushed his glasses back up his nose) I am of course talking about Pivot Tables!

 Access, mash-up and analyze data from virtually any source and rapidly create compelling analytical models with PowerPivot, now in Excel.

PowerPivot takes Pivot Tables to the next level! PowerPivot started it’s life as a free add-in for Excel 2010 (For those of you on Excel 2010 who want to try it out, it’s available here).  Power Pivot brings a level of BI usually unseen outside of dedicated packages. PowerPivot is designed for creating compelling reports and analytical applications; it is a fantastic way of presenting data, and sharing insights into that data. Having it integrated into Excel has only proven to be a good thing. While PowerPivot isn’t for everyone, it can be an extremely powerful tool for anyone looking to better understand their data.

Timeline Filters and Slicing

In Excel 2010 Microsoft introduced Slicer Filters, they are widely used to circumvent the classic Pivot Table Filter landing zone limitations. Slicers are shareable, so you can share a Slicer between Pivot Tables and Charts, meaning that they can influence more than one object at the same time. This makes them ideal for creating dashboards, and for use with Power View. They are also Contextual, meaning that they can underline the information that needs attention, avoiding issues with NULL fields and empty items.

In Excel 2013, Slicers can now completely hide NULL fields and empty items.

The reason I mention Slicers is because of the new Timeline Slicer function! The Timeline Slicer allows you to slice up Pivot Table Date dimensions (Such as cubes) or Date tables (In tabular data). Using the Timeline Slicer a graphical date-range selector is created, allowing you to choose on which level (Date, Month, Quarter, Year) you wish to operate, and then simply slide the date range you want to use. It’s a really simple way to filter by dates, as well as working across multiple data sets and PivotTables.

My only gripe with this is the inability to choose a custom time hierarchy (Namely weeks!) or to tie it into to calendar (For example, allowing it to plot based on a company’s fiscal year).

Core Functions

Before I start talking about Analytics, Charts, Pivoting and Filling; I want to talk about monitors. I am one of the biggest proponents of “More Screens Are Always Better” you will ever meet, and when I found out that I could use Excel on multiple monitors I near-enough choked! The power you can feel having a sales analysis sheet sprawled across three screens in all of it’s Pivoty glory is one of the greatest feelings that an Excel Poweruser can have! Hats off to Microsoft for finally allowing us to take full advantage of our screen real-estate!

But enough of my gushing about monitors, we have some awesome new features to look at!

Flash Fill

One of the most touted features of Excel 2013 is Flash Fill. Flash Fill automatically re-formats your data based on trends, similar to how Autofill detects patterns. The various press sources have been going wild about this feature, and I can see why. Microsoft’s example of this feature is a list that contains email ids with a pattern of firstname.lastname. Once a user fills two or three email ids, the others are prepared by Flash fill and the blank slots in front of the each name are automatically populated. Flash Fill can Extract, Concatenate (Join), Insert or Reverse Data  on Text, Dates, Numbers and Time data types!

Recommendations and Quick Analysis

One of the most frustrating parts of Excel when you’re a new user (Or just need to create a quick dashboard) is quickly building charts and Pivot Tables; Excel 2013 has expedited both of these processes with the Recommendations tool and Quick Analysis tool!

The “Chart Recommendations” presents users with a list of the most common types of chart for the selected data.

This works alongside Quick Analysis to make it much easier to quickly format and present data. Quick Analysis is a new in-context formatting and presentation system, fusing Chart Recommendations, Pivot Table Recommendations and Context-Specific formatting into one easy-to-access menu.

Excel Everywhere

It seems that everything revolves around a web browser nowadays, with the Excel web app, and Office 365 allowing you to access your favourite spreadsheet package from your web browser. But what if you could embed your favourite Excel data into your own websites? With Excel Mashup you can! Excel Mashup allows you to add a single button to a website, embedding your spreadsheet in a lightbox. Adding an Excel button is a pretty simple process, even for novice users, as it only requires you to add two lines of code to a page (With WordPress and other CMS plugins in the works, to make it even easier). It’s all done through Microsoft’s new site Excel Mashup, which also contains documentation and a code generator.

An even cooler way to bring your Excel data into your website is the new Embed, allowing you to display a Workbook and edit it as if it were in Excel using Skydrive! What makes this even more interesting is how it features a full-featured JS API, so you can bring your own code into the equation. For a demonstration of how this works, click here.

Conclusion

Overall, I have been taken aback by the quality and quantity of new features in Excel 2013. While there’s not a whole lot that I can say in the negative, I do feel that in a few places Microsoft could have tried a little bit harder; such as allowing Power View sheets to be edited in the Web App and Office 365, though I suspect this will be fixed in a later version or update.

I would urge anyone who currently utilises Excel for BI or complex Workbooks to consider upgrading! Anyone else? Perhaps not so much. Although, as with Word, combined with Windows 8, Excel has really grown up into something fantastic!

Like the article? You should subscribe and follow us on Twitter.


theatre-aglow
theatre-aglow
theatre-aglow
theatre-aglow