Microsoft

10 best new features in Excel 2013

Susan Harkins lists the new features from which Excel 2013 users will get the most benefit.

Almost everyone will find something to appreciate in Excel 2013. It's more functional and easy to use. Everyone's going to love Flash Fill. Experienced number crunchers will appreciate the new data model feature. Inexperienced users forced to get more out of the application than their experience really supports will appreciate Recommended Charts, Recommended PivotTables, and Quick Analysis. The emphasis seems to be on easy access to features that were, prior to 2013, a bit harder to implement. These are the 10 new features that I think will benefits most users.

1. Flash Fill

Perhaps the closest thing to magic Excel users will ever see in a workbook is Flash Fill. This feature anticipates formatting and data requirements by recognizing patterns so it can finish what you start. Figure A shows a typical use for Flash Fill. First, I transposed a record’s name value into last name, first name order. While doing the same for the second record, Flash Fill recognized a pattern and suggested possible entries to complete the column. When Flash Fill displays this list, you have two choices: Press Enter to complete the list or press [Esc] to remove it. To learn more about this feature, read Excel 2013 Flash Fill anticipates needs on the fly.

Flash Fill can complete this list for you (Figure A): 

Excel_New_Ftrs.FigA.png

2. Recommended Charts

Most users don't always know the best chart type for their data. Recommended Charts takes the guesswork out of the process. Simply click inside the data you want to chart, click the Insert tab, and click Recommended Charts to see a collection of suggested chart types, as shown in Figure B. Then, click a thumbnail to preview your data as that chart type. Double-click a thumbnail to embed the chart. Using this feature lets you work with confidence knowing the chart will highlight your data in a meaningful way.

Excel_New_Ftrs.FigB.jpg

3: Recommended PivotTables

This feature works similar to Recommended Charts, but generates a quick PivotTable. It’s a great new feature for most users, because so many of us struggle with PivotTables. Click inside the data range (which must have header cells) and click the Insert tab. In the Tables group, click Recommended PivotTables to display the dialog shown in Figure C. Select a thumbnail and click OK to embed an effective PivotTable in a new sheet.

Excel_New_Ftrs.FigC.jpg

PivotTables are a simple as a few clicks and a choice—no more struggling with field lists and table frames!

4: Quick Analysis

Use Quick Analysis to quickly preview your data in meaningful ways. For instance, Quick Analysis offers a second new way to generate appropriate charting for your data. Select the data and then click the Quick Analysis icon at the bottom-right, as shown in Figure D. Click the Charts tab to view the suggested chart types. If you don't see this icon, check your settings:

  1. Click the File tab and choose Options from the left pane.
  2. Choose General in the left pane (the default).
  3. In the User Interface Options section, the Show Quick Analysis Options On Selection option should be checked. Enabled is the default, so you should see this icon unless someone has changed this setting.
  4. Click OK.

Excel_New_Ftrs.FigD.jpg
Let Excel choose the most appropriate chart type!

5: Timelines

A timeline lets you filter records in a PivotTable—it works similar to a slicer, but you'll filter by dates. For instance, Figure E shows a PivotTable and timeline. (I used the same data range used in #3.) Once you have a PivotTable arranged, adding the timeline is simple:

  1. With the PivotTable selected, click the contextual Analyze tab.
  2. In the Filter group, click Insert Timeline.
  3. In the resulting dialog, check the date field (in this case, that’s Date) and click OK. Excel will embed the timeline alongside the PivotTable.

Excel_New_Ftrs.FigE.jpg

Use the new Timeline with a PivotTable.

To use the timeline, just drag the scroll bar or click a tile to further filter personnel totals by specific months. In the upper-right corner, you can change to years, quarters, months, and days. To clear the timeline filter, click the Clear button in the upper-right corner.

Thanks to #3, you don’t need specialized knowledge to put this all together! To learn more about this new feature, read 10 steps to adding a timeline to an Excel 2013 PivotTable.

6: Cloud support

Microsoft claims that its cloud support is the true shining star of the Office 2013 suite. If you need it, you probably agree; many organizations aren’t taking full advantage of it yet. If you’re curious, you can quickly hook up to SkyDrive or your organization’s SharePoint team site by using the Save As (or Open) screen, as shown in Figure F. Doing so has two advantages:

  • You have quick and easy access to your Excel files on any device that runs Excel 2013 (including a Windows tablet and smartphone).
  • Using Office 365 (you’ll need a subscription), you can review and edit your workbooks online using almost any web browser.
    Excel_New_Ftrs.FigF.jpg

7: Data Model and Relationships

Excel 2013’s new integrated data model support is well beyond a simple recommendation tip like this. You’ll want to study and familiarize yourself with all of the possibilities:

  • Create PivotTables based on multiple tables.
  • Create one-to-one and one-to many relations between tables.
  • Easily connect to OData, Windows Azure DataMarket, and SharePoint.
  • Drill down to detail levels in a PivotTable or PivotChart.
  • Drill up for a high-end view.

Import Data into Excel 2013, and Create a Data Model and Introduction to the Data Model and Relationships in Excel 2013 offer good introductory information on this new feature.

8: Apps for Office

This new feature provides quick access to specialized programs at Office Store. Just a quick click and you’re shopping! To install an app, click the Insert tab and then click Apps for office in the Apps group. You’ll need an account at the store, which the feature will help you create the first time you use it. Figure G shows Bing Maps as an installed app.

Excel_New_Ftrs.FigG.jpg

After creating an Office Store account, adding Bing Maps took just a couple of clicks.

9: Present online

Sharing a workbook online used to take a bit of preparation, but in Excel 2013, on-the-fly sharing is no problem. First, install Lync. If you have Office Professional Plus, you already have it, but you’ll need to configure it. Before sharing, sign into Lync. Then, return to Excel 2013, close all workbooks that you don’t want to share, and do the following:

  1. Click the File tab.
  2. Choose Share in the left pane.
  3. Click Present Online (in the Share section).
  4. Click Present.
  5. Choose a Lync meeting or create one, and click OK.

At this point, you can share the workbook and even allow others to update it.

10: New functions

Users often overlook new functions in a new version. This time around, you’ll want to pay attention, as 2013 has several new functions that even casual users will want to know about. For a complete list, read New functions in Excel 2013. My favorite is DAYS(), which returns the number of days between two dates. The simple expression End Date – StartDate will return the same results, but having a short function is self-documenting and handier. To learn more about my favorite new functions, read 10 new Excel 2013 functions that can save you time.



About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

13 comments
mornlot
mornlot

I liked making new things and share to every body.


chdchan
chdchan

An absolute cell content copy function is very much called for in addition to copy with relative addressing.

chdchan
chdchan

I hope Excel 2014 should add some smarter ways to add "$" to my column or row references instead of requiring me to type tediously. Also it would be best if the compare function for 2 sheets can only highlight those differences found in a more intelligent manner.

carbonman
carbonman

Meh, I'll stick with Office 2K, Office 2010 with an old style overlay and LibreOffice.  I'm so tired of having to relearn how to use MS systems every time they "improve" things.

rickgtoc_z
rickgtoc_z

As scary bad as the built-in chart 'designs' are in Excel 2007 & 2010, I shudder to think what Excel 2013's Recommended Charts might suggest to a novice chart builder.

linux-user
linux-user

Office 2010 and beyond, so many features that I never use and will never use.  The new versions of Office make it difficult to find the key features that I do use.  It's also ironic that after thirteen years of Office 'improvements' that I had more control of important document format with the 2000 version of WordPerfect in comparison with the current version of Word.  

Office 2013, just like Win 8/8.1, too many mouse clicks and motions to get to functions that were accessible in older versions with just one.    Too much of a waste of time and effort.   

tennesseehiker
tennesseehiker

I personally believe Office 2013, particularly Excel 2013, to be Microsoft's best Office package to date.  I teach computer skills at a local help center and to the general public at large, and this software seems to be the easiest to teach and learn.  I love Excel's new features!

DAS01
DAS01

6: Cloud support is hardly new.  Nothing to stop you using it with Office 2010...  

Don't know enough detail to comment about the other features, but in a similar forum the author averred that there was no compelling reason to move from 2010 to 2013.
dbmarketing
dbmarketing

@carbonman You might want to reconsider using old office software. Although Office 2010 and 2013 are radically different, they are not as full of errors and bugs as 2007 and earlier. I don't know why, but in 1994 a number of major bugs were identified in Excel. Those bugs remained right up to Excel 2007. LibreOffice is perfectly okay (or OpenOffice, either one) - when bugs are identified they are usually patched quickly. 

dbmarketing
dbmarketing

@rickgtoc_z But if you're just preparing charts for your boss, then he probably doesn't even care. I do agree though. If you're doing any kind of real stats work, you cannot use Excel - it still fails most basic testing. However, STATA, SPSS, SAS, and Minitab are all viable options.

stephanie.white
stephanie.white

@linux-userLoved your post.  Yours made sense where often time Microsoft's definition of easier escapes me.  For example:

A Word document's statistics used to be File/Properties/Statistics.  Now? Office button (click) / Prepare ??Huh?? (mouse over) / Properties (click) / Document Properties (click) / Advanced Properties (click - if you even notice it) / Document Properties (dialog box - same as Office 2000 I dare say??) / Statistics Tab (click).  Whoo Hoo!

Just like the new features, click, click, click, choose, click ad nauseum.

bswinarski
bswinarski

@DAS01 I find it fascinating that the number 3 wasn't included with the initial release of the stand-alone version of Office 2013.  Apparently, Microsoft recanted and re-added pivot tables at the beginning of August.  At that point, it may have been too late to convince companies to move over to 2013 from 2010.  They may have some movement in 2014, but at that point (even now), companies that didn't move to 2013 are likely waiting for the next release of Office to move over.  And honestly, it's Microsoft's own fault.  One has to wonder if they will 'rinse and repeat' with the next office or if they will learn from this fiasco.

Editor's Picks