Excel 2013: New tools for the everyday user — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Excel 2013: New tools for the everyday user

by on
in Centerpiece,Excel Training,Office Technology

If you have the newest version of Microsoft Excel, you’ll notice a few things work a bit differently. By and large, if you’ve used Excel 2007 or 2010, you can operate the newest version just fine. While there are some high-powered features for the expert data crunchers, there are plenty of cool new tools for the everyday Excel user.

Flash fill

Often, when you get data from other systems, it doesn’t come in quite right. For example, you may get several data elements in a single column, such as item description, SKU and quantity class. What if you need to extract the SKU number by itself? In Excel 2013, you can simply type the first SKU, press Enter, and start typing the second one. You’ll see a grayed list pop up. To populate the rest of the column, press the Tab key.

Quick analysis tool

This is easily one of the best new features of Excel 2013. Just highlight your data, and let the software suggest how you might present it visually. For example, select a table of items and their total sales for the month and the Quick Analysis icon pops up. When you click on the icon, a whole menu of formatting choices, from conditional formatting to charts, appears. Hover over the choices to get a preview of how the selected formatting will present your data.

New functions

There are 50 new functions in Excel 2013. Here are four favorites.

1. IFNA: Use this function with VLOOKUP to show a value besides #N/A. Say column H has the item number and the formula in I is designed to find the price of that item from a table (A1:C7), if the item number is not found, this form-ula will show “Item Not Found” instead of #N/A: =IFNA(VLookUp(H1,$A$1:$C$7,3,0)

2. FORMULATEXT: This function will display the text of a formula in a different cell. This is a very helpful tool when documenting how a workbook functions. The syntax is simple, =FORMULATEXT(A1), where A1 is the cell that contains the formula.

3. DAYS: Given an End Date and a Start Date, the Days function will give you the number of days. In the Days function, the first argument is end date and the last argument is start date. So, if A1 contains the start date and A2 the end date, the formula would read: =DAYS(A2,A1).

4. ARABIC: The ARABIC function converts Roman numerals to ­Arabic numerals. So, if you had Roman numerals: V, X, XX in cells A1:A5, =ARABIC(A1) would return 5, =ARABIC(A2), 10, =ARABIC(A3), 20, and so on to 50.

One workbook, one window

In earlier versions of Excel, all workbooks opened in a single session would only show in one window. This meant you could not snap workbooks to the right and left of the screen or drag one to another monitor, unless you opened multiple in­­stances of Excel. This was both inconvenient and memory intensive. Now, each time you open an Excel workbook, you get a new window.

Leave a Comment