What's new in Excel 2013?
The first thing you’ll see when you open Excel is a brand new look. It’s cleaner, but it’s also designed to help you get professional-looking results quickly. You’ll find many new features that let you get away from walls of numbers and draw more persuasive pictures of your data, guiding you to better, more informed decisions.
You can access Excel 2013 videos at the link below:
Start screen sets the scene
Excel's new Start Screen helps you get to work more quickly. Along its left edge are the most recently used worksheets, any of which can be pinned to your Recent list so they will always be visible. Here, too, you can click Open Other Workbooks to access your files from a disk or the cloud. A range of templates appears here to help you quick start a project. These can also be pinned, or you can use the search feature to look online for other templates. A list of suggested searches can help you get started.
New users will appreciate the template choices, and existing users will like the Recent file list and quick access to existing files. Although the Start Screen can be disabled, it is very useful. The Open tab has links to recently accessed files and locations.
Enjoy a new Backstage View
The Backstage View, introduced in Office 2010, is accessible from the File menu. In Excel this has been revamped to show exactly what you’re doing so you can choose the appropriate task.
The Open tab now gives you access to recently accessed workbooks, making it a combination of the Open and Recent tabs from Excel 2010. You can pin worksheets to this list or click Computer to access recently accessed locations (any of which you can pin permanently here, too). There’s also access to your OneDrive account, and the option to set up additional OneDrive or SharePoint accounts.
Want to split first and last names into two columns? Look to the new Flash Fill feature.
Make Flash Fill magic
The most whiz-bang new feature is the Flash Fill tool. Its predictive data entry can detect patterns and extract and enter data that follows a recognizable pattern. It solves some common problems that currently require cumbersome workarounds to achieve.
One such problem is extracting a person's first name from a column of full names. In a blank column adjacent to the one that contains full names, you simply type the first name and then click the Home tab, and select Fill, Flash Fill. The first names of everyone in the list will be entered into that that column immediately. You can use the same process to extract last names, to join first and last names, to extract months, days or years from dates and even extract values from cells. While you could have always done this with formulas, now Flash Fill ensures anyone can do it very quickly and easily.
Changes to the ribbon for charts
The new Recommended Charts button on the Insert tab lets you pick from a variety of charts that are right for your data. Related types of charts like scatter and bubble charts are under one umbrella. And there’s a brand new button for combo charts—a favourite chart you've asked for. When you click a chart, you’ll also see a simpler Chart Tools ribbon. With just a Design and Format tab, it should be easier to find what you need.
Take the guess work out of which chart to choose to best display your data.
Simplify choices with Recommended Charts
This falls somewhere between a whiz-bang new feature and something that makes working in Excel more intuitive. Recommended Charts shows only a subset of chart types that are appropriate to the data you've selected. It will help inexperienced users create charts that help explain the data and don’t confuse the viewer.
To use the tool, select the data that you want to chart, click the Insert tab and select Recommended Charts. A dialog appears with a range of charts to choose from—click each in turn to see how your data will look plotted on that chart. Select the desired option and click OK, and the chart is created automatically.
Change the look of your chart by selecting options from the pop-up menu.
Chart tools get smarter
In previous versions of Excel, when a chart is selected, the Chart Tools tab revealed three additional tabs: Design, Layout, and Format. The interface is simpler in Excel 2013, with only the Design and Format tabs to choose from.
In addition, a set of icons appears 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. Click Chart Elements to add or remove elements, such as axis titles and legends; click Chart Styles to change the style and colour of your chart; or click Chart Filters to view filtered data using a live preview.
Quick Analysis offers formatting, totals and charts for analysing your data.
Fine tune charts quickly
Three new chart buttons let you quickly pick and preview changes to chart elements (like titles or labels), the look and style of your chart, or to the data that is shown. To learn more about it, see Format your chart.
Richer data labels
Now you can include rich and refreshable text from data points or any other text in your data labels, enhance them with formatting and additional freeform text, and display them in just about any shape. Data labels stay in place, even when you switch to a different type of chart. You can also connect them to their data points with leader lines on all charts, not just pie charts.
View animation in charts
See a chart come alive when you make changes to its source data. This isn't just fun to watch—the movement in the chart also makes the changes in your data much clearer.
Quickly analyse your data
The new Quick Analysis tool can help both new and experienced users find options for working with selected data. To use it, select the data to analyse, and the Quick Analysis icon appears in the bottom-right corner of the selected data.
Click that icon, and a dialog appears showing a range of tools for analysing the data, such as Formatting, Charts, Totals, Tables and Sparklines. Click any option, and a series of selectable choices appear; preview those choices by mousing over them. Next, click the option you like to apply it to your data. This feature speeds up the process of formatting, charting and writing formulas.
PivotTables just became ridiculously simple to create.
Answer questions instantly with Pivot Tables
Pivot Tables are a powerful tool for analysing and answering questions about your data, but they’re not easy for new users to create. For the first time, though, if you can click a mouse key, then you can create a meaningful Pivot Table, thanks to the new Recommended PivotTables. To use it, select your data, including headings, and choose Insert, Recommended PivotTables. A dialog appears showing a series of PivotTables with explanations of what they show. All you need do is to select the table that shows what you want to see, click OK, and the PivotTable is automatically drawn for you.
Excel 2013 now integrates Power View for beefy analysis and reporting.
Create a PivotTable that suits your data
Picking the right fields to summarize your data in a PivotTable report can be a daunting task. Now you can get some help with that. When you create a PivotTable, Excel recommends several ways to summarize your data, and shows you a quick preview of the field layouts so you can pick the one that gives you the insights you’re looking for.
Use one Field List to create different types of PivotTables
Create the layout of a PivotTable that uses one table or multiple tables by using one and the same Field List. Revamped to accommodate both single and multi-table PivotTables, the Field List makes it easier to find the fields you want in your PivotTable layout, switch to the new Excel Data Model by adding more tables, and explore and navigate to all of the tables.
Use multiple tables in your data analysis
The new Excel Data Model lets you to tap into powerful analysis features that were previously only available by installing the Power Pivot add-in. In addition to creating traditional PivotTables, you can now create PivotTables based on multiple tables in Excel. By importing different tables, and creating relationships between them, you’ll be able to analyse your data with results you aren't able to get from traditional PivotTable data.
If you’re using Office Professional Plus 2013, you can take advantage of Power Query for Excel. Use Power Query to easily discover and connect to data from public and corporate data sources. This includes new data search capabilities, as well as capabilities to easily transform and merge data from multiple data sources so that you can continue to analyse it in Excel.
If you're using Office 2013, or Excel 2013, you can take advantage of Power Map for Excel. Power Map is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways by using geographic and time-based data. You can discover insights that you might not see in traditional two-dimensional (2-D) tables and charts. Power Map is built into Office 365 Pro Plus, but you'll need to download a preview version to use it with Office 2013 or Excel 2013.
Connect to new data sources
To use multiple tables in the Excel Data Model, you can now connect to and import data from additional data sources into Excel as tables or PivotTables. For example, connect to data feeds like OData, Windows Azure DataMarket, and SharePoint data feeds. You can also connect to data sources from additional OLE DB providers.
Create relationships between tables
When you've got data from different data sources in multiple tables in the Excel Data Model, creating relationships between those tables makes it easy to analyse your data without having to consolidate it into one table. By using MDX queries, you can further leverage table relationships to create meaningful PivotTable reports.
Use a timeline to show data for different time periods
A timeline makes it simpler to compare your PivotTable or PivotChart data over different time periods. Instead of grouping by dates, you can now simply filter dates interactively or move through data in sequential time periods, like rolling month-to-month performance, in just one click.
Use Drill Down, Drill Up, and Cross Drill to get to different levels of detail
Drilling down to different levels of detail in a complex set of data is not an easy task. Custom sets are helpful, but finding them among a large number of fields in the Field List takes time. In the new Excel Data Model, you’ll be able to navigate to different levels more easily. Use Drill Down into a PivotTable or PivotChart hierarchy to see granular levels of detail, and Drill Up to go to a higher level for “big picture” insights.
Use OLAP calculated members and measures
Tap into the power of self-service Business Intelligence (BI) and add your own Multidimensional Expression (MDX)-based calculations in PivotTable data that is connected to an Online Analytical Processing (OLAP) cube. No need to reach for the Excel Object Model—now you can create and manage calculated members and measures right in Excel.
Create a standalone PivotChart
A PivotChart no longer has to be associated with a PivotTable. A standalone or de-coupled PivotChart lets you experience new ways to navigate to data details by using the new Drill Down, and Drill Up features. It’s also much easier to copy or move a de-coupled PivotChart.
Make quick reports with Power View
The Power View add-in, available for previous versions of Excel, is now integrated inside Excel 2013. Power View is typically used for analysing large quantities of data brought in from external data sources—just the sort of tool that big business might use.
Incorporated within Excel, it’s now accessible to anyone. To see it at work, select your data and choose Insert, Power View. The first time you use it, the feature installs automatically. Then a Power View sheet will be added to your workbook, and the analysis report will be created.
You can add a title and then filter the data and organize it to display the way you like. The Power View tab on the Ribbon tool bar displays report format options, such as Theme and text formats, as well View options for Field List and Filters Area panels that you can use to filter and sort your data.
Click the Power View button on the ribbon to discover insights about your data with highly interactive, powerful data exploration, visualization, and presentation features that are easy to apply. Power View lets you create and interact with charts, slicers, and other data visualizations in a single sheet. Learn more about the Power Pivot in Excel 2013 add-in.
Try to work on a worksheet that someone else is editing? You'll be warned that it's locked. You can view and download it, but can't change it.
Power Pivot for Excel add-in
If you’re using Office Professional Plus 2013, the Power Pivot add-in comes installed with Excel. The Power Pivot data analysis engine is now built into Excel so that you can build simple data models directly in Excel. The Power Pivot add-in provides an environment for creating more sophisticated models. Use it to filter out data when importing it, define your own hierarchies, calculation fields, and key performance indicators (KPIs), and use the Data Analysis Expressions (DAX) language to create advanced formulas.
If you’re using Office Professional Plus 2013, the Inquire add-in comes installed with Excel. It helps you analyse and review your workbooks to understand their design, function, and data dependencies, and to uncover a variety of problems including formula errors or inconsistencies, hidden information, broken links and others. From Inquire, you can start a new Microsoft Office tool, called Spreadsheet Compare, to compare two versions of a workbook, clearly indicating where changes have occurred. During an audit, you have full visibility of the changes in your workbooks.