Technology

For the 12 Days of Christmas, 12 New (and Useful) Excel 2013 Features

The next release of Office is finished and is being pressed into DVDs for a Q1 release. Enterprise customers may already have a copy, but you can d...
Bill JelenDecember 18, 2012

I’ve explored every pixel of Excel 2013 while writing four books about it. I know that the marketing team at Microsoft will be promoting newbie features such asFlash Fill, Quick Analysis, and Recommended Charts. Here’s a list of the best features for accounting and finance.

  1. Excel on dual monitors. Every Excel workbook now gets its own window and ribbon. This means you can open two workbooks, position them on different monitors, and actually copy formulas back and forth between the two. (In other words, they’re running in the same instance of Excel to allow for the pasting of formulas, but the interface pretends it’s two instances of Excel to allow for the aforementioned dual monitors.)

2.  Combo Charts get a new interface. You want to show Revenue and GP% on the same chart. There used to be a confusing method for assigning GP% as a line chart on the secondary axis. The new Combo Chart interface (below) makes it incredibly straightforward. For each series, you control the chart type and where it’s plotted.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

 

3.    Pivot tables in four clicks. Microsoft has added a Recommended Pivot Table dialog for people who can’t figure out what they want their pivot tables to look like. Ignore the 10 choices; click OK to accept the first recommendation, and you will have a pivot table in four clicks instead of six.

4.    Relationships instead of VLOOKUP. If you add your worksheets to the pedestrian “Data Model” feature, you can use the Relationships icon to define that CustID in your million row transaction worksheet is related to CustNumber in your customer worksheet. Now, without doing millions of VLOOKUPs, you can create pivot tables from the data on both worksheets. Whether you’re sick of people who feel superior because they can do VLOOKUP, or someone who does VLOOKUP in his sleep, no one can argue that creating a relationship in three clicks is faster than waiting for a million VLOOKUPs to recalculate.

5.    =FORMULATEXT(A2). This new function will put the formula from cell A2 in a cell. Whether you’re trying to document the formulas you used, or you’re looking for plug numbers in a column, this tiny little function is a winner. There are 50 new calculation functions in Excel, none will ever get mentioned in the Microsoft marketing materials, but FORMULATEXT is the one that I can’t see living without. I still use Excel 2010 and Excel 2013 interchangeably, but the first thing that makes me aware that I am not in 2013 is when my =FORMULATEXT() functions don’t work.

6.    Chart data labels from formulas. Technically, the new feature is called “Data Labels from Cells,” but those cells can contain formulas. This means you can have a range of 12 formulas that dynamically label your best month using something like =IF(A2=MAX($A$2:$L$2),”Best Month”,””). When the data changes, the label moves to the correct column. For anyone who has ever manually added a call-out to a chart, you’ll love this one.

7.    Visually  filter worksheets with slicers. The slicer was added in Excel 2010 to allow you to visually filter a pivot table. Now, in Excel 2013 (see below), you can visually filter regular ranges, provided you convert them to tables using Ctrl+T.

 8.    Slot-machine animation reveals unlinked cells. If you change an input value, all of the dependent formula cells in the visible window will animate with the new calculation rolling in from the top of the cell like a slot machine. Sure, this is great for newbies. The thing you’ll love is when you notice a dependent cell that is not getting the slot-machine animation but should be. On a subconscious level, you’ll be drawn to that cell only to discover that a co-worker replaced the formula with a hard-coded value.

9.    Charting from badly shaped data sets. You have 12 columns of monthly values with a quarterly subtotal interspersed after each three columns. This looks great in the worksheet, but it makes any chart built from the table have bizarre spikes at the data points for the quarter. Rather than building a second range without the quarter columns, create a chart from the entire data set. Use the Filter icon to the right of the chart and uncheck the data points that don’t belong. Or . . .  you have the opposite problem on a massive scale: your data is 5,000 rows of transactions without any summary at all. Throw this data set at the Recommended Charts icon and Excel 2013 will offer charts of various summaries of the data.

10.  Power View, GeoFlow, Inquire, and PowerPivot. There is a definite class structure in Excel 2013. If you buy one of the cheaper Home & Student editions, you will not have access to these four add-ins for Excel 2013. Power View lets you build interactive dashboards. GeoFlow puts your pivot table data on a map. Inquire does spreadsheet auditing and version comparisons. PowerPivot lets you load 999 million rows into Excel and write amazing formulas. All of these are included if you get the $399 Pro Plus version of Office.

11.  =WEBSERVICE and Excel Apps to return web data to your worksheet. The new =WEBSERVICE() function will return data from the web. You build a formula-based URL using =ENCODEURL, call the WEBSERVICE function to return an XML data set, then use =FILTERXML to parse out the data from the web page. So far, I’ve used this to get historical weather by city and date, transit routes, and even a list of the last 15 tweets that mention pivot tables. The new App Store for Excel offers more JavaScript apps that can interact with your web page. Apps are usually free or $1.99, and add new functionalities such as mapping data by state.

12.  Tweet your worksheets. Frustrated that your cool friends always have something witty to say on Twitter or Facebook or LinkedIn? Now you can post your Excel 2013 worksheet directly to social media. Nothing says “accountant” like sharing your favorite Excel calculator for loan payments. Seriously, though, the technology behind this is the Excel Web App. With a little preparation (File, Browser View Options), some slicers, and perhaps a hidden worksheet or two, you can get an interactive worksheet that you can embed in your web page or blog post (see below). You don’t need to hire a web designer; you can use Excel as your web design canvas.

Give It a Try
One of the new licensing schemes for Office 2013 is something called Office 365 Home Premium. In this scenario, you get a 12-month license for up to five PCs, laptops, and tablets for $99. Documents and settings can be shared among the five computers. For the rest of December, you can download the Excel 2013 Home Premium preview and use it for free until the end of January. This will give you an opportunity to try out the 12 features described above. Follow these steps to download the free preview:

  1. Visit http://tinyurl.com/xl13preview.
  2. Click the Excel logo at the bottom.
  3. In the right Try It bar, choose Office 365 Home Premium.
  4. Click the big green Try It button.
  5. If you aren’t already signed in with a Microsoft account, register your account, and click Get Started Now.
  6. You will be told that you have five available installs left. Click the Install button.

Bill Jelen is the author of the soon-to-be-published Excel 2013 In Depth.