Spreadsheet Nerdiness

Publication:

California CPA Magazine

Date:

May 2013

Author:

Jeff Lenning

Excel 2013 Highlights

The next version of my most favorite program is here—it’s finally here! Excel 2013 is available, and I’m excited to review some of the highlights. Microsoft clearly invested a lot of time into this product. Here are just a few of the things you’ll notice about Excel 2013.

 

Microsoft Kept the Ribbons and Design from 2010 … Mostly

If you still have nightmares about the transition from 2003 to 2007, fear not. You’ll be right at home in 2013 as Microsoft kept the ribbons intact—but there are new command icons for the new features. The overall design is similar to 2010, but being all white, it feels stark and naked (Figure 1). You can change the color theme from white to gray or dark gray. And there is more animation now while you are using Excel.

Figure 1

 

A Lesson from Word: Each Workbook in its Own Window

You know how each document in Microsoft Word gets its own window? How it makes it easy to open two Word documents at the same time and move them between different monitors? Well, Excel gets that capability now, and each workbook is in its own window. So, if you have multiple monitors, you can easily position workbooks into the desired monitor.

 

Excel App Store?

App stores, whether for iOS or Andriod, are all the rage. And now, Excel has an app store too! Built right into the Excel ribbon is the Apps for Office icon. Now you can extend the built-in functionality of Excel through Microsoft and third-party apps. There aren’t many apps in there now. I see a date picker and about five others, but, hey, it’s just getting started. It will be interesting to see how it develops.

 

Slicers: Not Just For PivotTables Anymore

Slicers is a graphical filtering feature introduced in Excel 2010 and available originally for PivotTables. Microsoft extends this feature to include Tables in 2013 (Figure 2).

Figure 2

 

FlashFill

If you have ever wished for an easier way to split text columns, FlashFill is for you. This new feature sort of watches you do the first one or two, looks at your pattern and then extends the pattern down. Thisfeature represents a faster alternative to formulas or text-to-columns. For example, I had a list of full account numbers (Figure 3). The first segment of the full number represents the business unit, the second segment represents the department and the third segment represents the account number. I needed a column with just the department number. So I entered the first department number, 4960, manually, and then clicked the FlashFill button. Excel instantly populated the remainder of the Dept. column, adapting automatically to the variable length account number segments.

Figure 3

 

Inquire Add-in

While not enabled by default, you can activate the Inquire Add-In, which provides a new ribbon tab. The inquireribbon tab provides a set of utilities designed to analyze workbooks including, for example, a tool to compare two different workbooks. Figure 4 shows the results of a comparison I made between two different versions of a file. The tool successfully identified all areas that were different between the two versions— including changed formulas, entered values and calculated values. The inquire tab provides many other great tools including workbook analysis, which provides a report of the workbook that identifies external data sources and workbook, worksheet and cell relationships. This is a killer add-in!

Figure 4

 

New Functions

There are about 50 new worksheet functions. Many of these are statistical, engineering and trig functions, but the ones that personally interest me the most are:

  • Decimal: Converts a text representation of a number into a decimal.
  • Encodeurl: Returns a URL-encoded string.
  • Formulatext: Returns the formula at the given reference as text.
  • Ifna: Returns the value you specify if the expression resolves to #N/A.
  • Isformula: Returns true if the reference contains a formula.
  • Numbervalue: Converts text to a number.
  • Sheet: Returns the sheet number of the referenced sheet.
  • Webservice: Returns data from a web service.
  • Filterxml: Parse the xml web service results.

 

Recommended Items

Microsoft tried to make it easier to use some features; therefore, Excel can now recommend a PivotTable layout or Chart type. For example, rather than inserting a PivotTable, you can ask Excel to insert a recommended PivotTable, and you’ll see a preview of several suggestions. If you accept a recommended layout, the PivotTable will appear in your workbook with the row, column and value fields already in place based on the recommendation. It saves the steps of dragging and dropping field items. Same with the charts: You can choose recommended charts, and you’ll get a preview of a handful of suggested formats. If you pick one, Excel inserts the recommended chart into your workbook.

 

Data Analysis, Reporting and Charting

The trend of incorporating more powerful data analysis and reporting tools into Excel continues, and there are a bunch of new things to explore in this area. The PowerPivot add-in, which used to be available as a download, is now built-in and feels more polished and consistent with Excel. You can easily create data relationships, kind of like Microsoft Access, so that you can build a PivotTable on two or more distinct Tables. By defining a relationship between Tables, you alleviate the need to use a lookup function like VLOOKUP to join and consolidate the data into a single source first. The new PowerView feature is designed to build interactive reports and includes drill down, drill up and cross drill.

Charts also got some love, including better labels, animations, independent PivotCharts and enhanced charting controls.

 

Online Integration

Leveraging SkyDrive, Excel continues to be tightly integrated with Microsoft’s online services. This makes it easier to use documents stored on the web and to share, embed and collaborate with online workbooks.

 

Conclusion

Those are some of the highlights of Excel 2013. For more new features, check out the links in the sidebar.
And remember: Excel rules!

This article was written by Jeff Lenning