New Features of Excel 2013
Microsoft Excel 2013 has a new look, it’s cleaner and primed for use on tablets and phones. You can swipe and tap your way through worksheets. You can analyse data more easily with FlashFill, use recommend Pivot Table and Charting layouts and use Slicers now with filtered lists. The Quick Analysis gallery allows a 1 click method to create tables, apply conditional formatting, insert Sparklines and edit charts.
Excel 2013 notices patterns in the user’s data entry and then auto completes the remaining data so there’s no need to use formulas or macros to do this. Data gets filled in automatically. Flash Fill is like a data assistant that quickly finishes your work for you!
For example the Flash Fill feature will automatically complete a list of surnames after starting to type the second surname. It recognises patterns and predicts what data to fill in for you. Start typing the initials and Flash Fill fills the list for you.
Flash Fill also recognises text case. After typing a name in upper case, clicking Flash Fill on the Data Ribbon fills all the rest of the names in upper case.
Recommended Pivot Table
This option allows you to preview several other recommended pivot tables allowing the user to select the best one.
To see the recommended pivot tables select the Insert tab, Pivot Table then click Analyse. Recommended Pivot Tables. Simply select one on offer and press OK.
Use Timelines with pivot data
Now with Excel 2013 you can insert a scrolling Timeline to filter date fields. The Timeline can be formatted to view by day, month or quarter.
Use multiple tables to analyse data
The pivot table field list has been revamped to include both single and multiple tables.
To add more tables, switch to the Excel Data Model.
Use Slicers to filter tables
Although Slicers were introduced with Excel 2010 with Excel 2013 they can be used to filter tables as well as pivot tables.
To insert a Slicer first select the Home tab, Format as Table. Then from the Design tab choose Insert Slicer.
This new feature of Excel 2013 recommends charts using the Quick Preview feature. You can then select the most appropriate chart.
Excel 2013 Recommended charts
Quick Analysis Tool
This is a single click method, for applying Conditional Formatting, Creating Tables or adding Sparklines to make analysis more effective. To use the Quick Analysis Tool simply highlight tabular data and click the icon at the bottom right of the selected data. Or press Ctrl + q with your cursor anywhere inside the data range.
From the Quick Analysis gallery choose one of the types of analysis.
In this example the Colour Scale formatting is used to look for patterns of high and low spots.
Icon Set is another option available under Quick Analysis formatting. For example totals meeting a target display a green arrow or a red arrow if they don’t. To define the rule use Conditional Formatting, Manage Rules to define the target criteria)
The Charts option in the Quick Analysis gallery allows the user to quickly view and analyse data by choosing a variety of chart types.
This gallery option allows totals and summary functions to be added to the data including running totals.
This is a quick way to convert a range into a Table or blank Pivot Table on a new worksheet.
Introduced in Excel 2010 Sparklines create charts within a cell. It can help to show one figure in context with others and illustrate trends. In this example a blank column is created for the sparklines before selecting the adjacent data.
Chart formatting control
Excel 2013 includes a more interactive way to customise charts.
Once a chart is created select Chart Elements (+) for quick and easy editing of elements such as the chart title, legend and gridlines.
Selecting the filter funnel is used to chart specific data points. Click on the Design tab to change the chart type.
2013 includes a default setting so that workbooks are automatically saved online. This ensures all users are working on the latest version, with the added protection of control of the viewing and editing permissions.
Share part of a worksheet via embedding selected sections.
Present online – 2013 makes it easier to share workbooks with colleagues using a Lync conversation or meeting.