Be Excel-lent

Publication:

California CPA Magazine

Date:

May 2011

Author:

Jeff Lenning

Microsoft released Excel 2010 about a year ago and, while you may have upgraded by now, you may be like me. Sometimes I’ll upgrade an application, but not take the time to learn about its new features. So I continue doing my work on the “DILLY” method—you know, Do It Like Last Year. But each new version of Excel includes enhancements that improve the way we work. So, if you haven’t had a chance to fully explore Excel 2010, consider this your tutorial.

I think Excel 2010 is the best version yet. It retains Excel 2007’s Ribbon Interface, so getting up to speed on this version is a piece of cake. Remember moving from Excel 2003 to Excel 2007? Brutal.

Generally speaking, Microsoft’s development efforts over the past three years can be grouped into four areas:

  1. New features (slicers and sparklines)
  2. Enhancements to existing features (conditional formatting, pivot tables and tables)
  3. Worksheet functions (50 new worksheet functions and function improvements)
  4. Technical specifications (faster processing, bigger specs).

This article will highlight selected new features and list several feature enhancements. A more comprehensive list can be found on Microsoft’s website, www.calcpa.org/Ex2010Whats New. If you’d like to download the companion workbook used in the screenshots below to practice these new features, visit http://clickconsulting.com/excel-2010. The three new features of Excel 2010 we’ll discuss here are slicers, Office Web Apps and sparklines. Although Office Web Apps are neither a feature of Office 2010 nor require an Office 2010 license to use, they were introduced with the launch of Office 2010.

 

Slicers

Slicers make it easy to apply filters and see the selected filters. For you pivot table users, you’ll be excited to implement this feature in your workbooks.

Historically, applying, editing and removing pivot table filters required several steps, and when a filter had multiple items it was unclear which items were selected. Slicers were introduced as a way to make it easier to filter pivot table reports.

To create a slicer, first build your pivot table using the normal methods (Insert > Pivot Table). Next, select the Pivot Table Tools > Options > Insert Slicer button. Select the appropriate field from the Insert Slicers dialog box and click “OK.” Excel will place the slicer control into the drawing layer of the worksheet for you to freely position. An example of region, quarter and rep slicers are shown in Figure 1.


Figure 1

 

You can click the east or west region button to filter the report accordingly, and can apply additional filters by selecting a quarter or rep. The small “clear filter” button in the upper-right corner will clear the filters. Selecting multiple items is done by ctrl+clicking the filter buttons.

For more info, check out Excel’s help files or watch Microsoft’s nifty slicers video at www.calcpa.org/slicers.

 

Office Web Apps

Office Web Apps were rolled out in conjunction with, and are tightly integrated into, Office 2010. Office Web Apps are free, lightweight versions of Excel, Word, OneNote and PowerPoint that are used through a web browser. They are not designed to replace the desktop versions, but rather to complement the desktop versions and enable anytime access from any internet connected device. For those of you familiar with Google Docs, this is Microsoft’s version.

You can use this service for free without owning a copy of Office 2010. Note, however, that these free versions (Figure 2) do not have all of the features, ribbon tabs and buttons available in the desktop version. You are limited to basic workbook creation and editing.


Figure 2

 

Still, Office Web Apps offers a quick and easy way to create and share documents. Simply visit www.live.com to sign in or register. Then, navigate to the Office menu, where you can make your way through your folders or create new documents. You can edit the documents within your browser. Since the documents are stored on the internet, you can share them without the need of emailing attachments. Changes made by multiple workers are stored on the central document. You can download the file to your computer at will, and also upload existing Excel files.

It is also fairly easy to edit the files directly with Excel by clicking on the “Edit in Excel” (or “Open in Excel”) . You can also utilize the online storage without visiting the website. To save an Excel file from within Excel 2010 to Microsoft’s cloud storage (i.e., your www.live.com portal), select File > Save & Send > Save to Web. You’ll be prompted to log in, and then the file is automatically saved to your online account. Be patient, as saving and retrieving files from the online storage may take more time than accessing files on your local network. Files stored online can be accessed through the web browser or from within Excel 2010.

More information is available at www.live.com or www.calcpa.org/MSvideo. As with any cloud based service, be sure to consider the related security and privacy issues. If your data includes sensitive client financial data, think twice before using this free cloud service.

 

Sparklines

Think of sparklines (Figure 3) as charts that occupy only a single cell. They are terrific for dashboards, trends, key performance indicators, metrics—anytime you need to present data in a small space.


Figure 3

 

To insert a sparkline, select the cell, click the Insert Ribbon tab and then select the correct sparkline style button. Walk through the wizard, and you are done.

For more, check out Microsoft’s video at www.calcpa.org/sparklines. Now let’s check out some of the enhancements to existing features.

 

Pivot Table Improvements

Pivot Table row labels can finally be repeated (filled down). You can turn this on for all row labels by navigating to Report Layout > Outline > Report Layout > Repeat All Item Labels. You can turn this on for an individual field item within the Field Settings dialog.

Microsoft also released Power Pivot, a Pivot Table add-in that’s like a superpowerful Pivot Table system that enables you to work with larger data sets and create more sophisticated pivot tables. Think of Power Pivot as a utility that sits between your data source and your pivot table: Power Pivot enables you to pull in data from almost anywhere, including multiple data sources at the same time, and manipulate data before it flows into the pivot table report. For more, visit www.powerpivot.com. To get started, download the Power Pivot add-on at www.calcpa.org/PPaddon.

Named Sets is a Pivot Table feature only available when using an online analytical processing source and, for many of us, that means Power Pivot sources. You’ll be able to use Named Sets when you build a pivot table using Power Pivot.

Named Sets allow you to choose the data points presented in the pivot table in a very comprehensive and robust way. For example, say you have a data column for Year and another column for Measure. The Measure column has either Budget or Actual for values. If you wanted the pivot table report to show 2011 Actual and 2012 Budget, you could not do it with typical field filters in traditional pivot tables. However, this is straightforward when using Named Sets along with Power Pivot.

There was access to the Show Values As in prior versions of Excel’s Pivot Table, and we now have more options, including the ability to show values as a percent of the parent row or column.

 

Ribbon Customization

Finally, you can edit the Ribbon! This is wonderful news for those who customized Excel 2003 Menus to make life easier—only to be disappointed with the lack of customization in Excel 2007. Excel 2010 allows you to easily modify the Ribbon to, among other things, create your own Ribbon tabs. All you do is right-click on any Ribbon tab and select Customize the Ribbon.

 

Conditional Formatting

Conditional formatting is the ability to dynamically format a cell based on its value. If you’ve never played with this longstanding Excel feature, you’ll find it quite useful. I end up implementing it in many of my workbooks.

Substantial enhancements to this feature were made in Excel 2007, and developers introduced Icon Sets, data bars and a more flexible rules system. Excel 2010 enhancements include an Icon Sets format that have new icons and the ability to mix icons, hide certain icons from view, show icons only and generally improved rules. Data bars also were improved with additional formats and more flexible rules and customizations.

 

Table Enhancements

I strongly encourage you to implement tables into your Excel workbooks if you haven’t already. Tables were introduced in Excel 2007 and represent an important tool that, among other things, auto-expand when new data is typed below the table. Thus any formulas or objects that refer to the table data will automatically include the newly entered data.

Since Excel 2007, as you scroll down a worksheet when a table is active, the standard column labels (A, B, C, etc.) are replaced with the table headers. Beginning with 2010, a sort and filter drop-down menu is included in the column headers, making large tables easier to work with since sorting and filtering can be applied without the need to jump back to the top of the sheet.

Well, now that your busy season is over, I hope you’ll have some time to have some fun—like taking a trip to Catalina, playing Just Dance 2 for Wii or even playing with some of Excel’s new features.

And remember, Excel rules.

This article was written by Jeff Lenning