As one of the oldest of the Microsoft Office applications, Excel was already packed with extensive spreadsheet capabilities but now it has become more functional. Data analysis and complex tasks are easier with features such as Recommended Charts, Recommended PivotTables, and Quick Analysis. Other changes make options easier to find so you can quickly get professional results. Let’s explore these top Excel 2013 new features.
The first change you’ll see when you launch Excel 2013 is a startup screen rather than a blank workbook, as in older versions of Excel. In the left pane, you’ll see a list of your most recent Excel documents as well as the option to open additional files. In the right pane, you can pick from various templates, such as blank, calendar, inventory, and many more as well as search Microsoft’s library of online templates. If you are a long-time Excel user, this new landing page may take some getting used but it can be disabled if the earlier startup is preferred.
In addition, the Backstage View has a new interface which gives you fast access to your data and file management options including accessing your work online.
The AutoFill feature in Excel is a huge timesaver but perhaps the closest thing to magic in a workbook is the new Flash Fill feature which goes further by filling in your work for you. As soon as Flash Fill detects a pattern in your data, it enters the rest of your information all at once. For example, imagine you have a column of contacts with a first and last name. With earlier versions of Excel, you would need to write a formula to automatically extract just the first name into another column. With Flash Fill, type the first name in an adjacent column. Repeat typing the next first name in the row below and Excel kicks in and duplicates the pattern and formatting for the rest of the data. Its predictive data entry recognizes text, dates, and even values. One of the handiest Excel 2013 new features!
Many Excel users don’t always know the best chart type for their data. With the Recommended Charts feature, Excel displays a collection of suggested chart types for the selected data. You’ll see a preview of how your data looks in different charts and then simply pick the one that displays your results that way you want to present. Although Recommended Charts can help inexperienced users create charts that explain the data and don’t confuse the audience, it’s a handy tool for all Excel users who want a quick, professional chart.
In addition to the Recommended Charts feature, the Excel 2013 charts options have been given a facelift with a simplified Chart Tools Ribbon and more robust chart choices. In earlier versions of Excel, advanced formatting options were buried deep in hard to find, complex dialog boxes. With Excel 2013, a set of icons appear outside the top right edge of a chart when it is selected. Click any of these buttons-Chart Elements, Chart Styles or Chart Filters—to reveal additional chart formatting options. Or select the chart element such as the data series, right-click it, and choose Format from the pop-up menu. The new Format pane magically appears with options that are tailored for the selected chart element.
The new Quick Analysis tool helps both new and experienced Excel users find options for showing data in meaningful ways. Just select the data you want to analyze and then click on the Quick Analysis button that appears to the bottom right of your selected data (or press [Ctrl] + Q). Preview your data for conditional formatting, Sparklines, tables or charts, and make your choice stick in just one click.
Excel PivotTables are a powerful tool for analyzing, summarizing, and answering questions about your data but some users find them difficult to create. Thanks to another of the Excel 2013 new features, the Recommended PivotTables option, you can quickly create a meaningful PivotTable with just a click. To try these, start inside your data (which must have column headings), and choose Insert > Recommended PivotTables. A dialog displays with a series of PivotTables and explanations on the results. Pick the table that shows what you want to see and the PivotTable is automatically drawn for you. And you can still build your own custom PivotTables as needed.
Slicers were first introduced in Excel 2010 as an interactive way to filter PivotTable data. Now in Excel 2013, slicers can also filter data in Excel tables, query tables, and other data tables. Easy to set up and use, slicers show the current filter so you’ll clearly know what data you’re looking at.
A timeline lets you filter records in a PivotTable—it works similar to a slicer but you’ll filter by dates. After creating a PivotTable, adding the timeline is simple. First, select the PivotTable and then pick the contextual Analyze tab. From the Filter group, click Insert Timeline. From the dialog box, check the date field you want to use and the timeline is embedded alongside the PivotTable. To use the timeline, just drag the scroll bar or click a tile to further filter your data by time increments.
The Power View add-in, available for previous versions of Excel, is now integrated inside of Excel 2013. Power View is often used for analyzing large quantities of data brought in from external data sources. To try it out, select your data and choose Insert > Power View. This feature installs the first time you use it. Then a Power View worksheet is added to your workbook and the analysis report is created. With the Power View Ribbon tab you can format, filter, and sort your data.
A long overdue new feature is that now each Excel 2013 workbook has its own window. This improvement makes it easier to work on two workbooks at once side by side especially when you’re using two monitors.
By Dawn Bjork, MCT, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant