Table of Contents s657

Table of Contents

Overview of New Features 3

Launching Excel 2013 4

New Features of Excel 2013 5

FILE Menu 5

Compatibility with Previous Versions 6

Customizing the Ribbon 6

Customizing the Quick Access Toolbar 7

Worksheets 7

Moving and Copying Worksheets 7

Zooming Worksheets 8

Flash Fill 10

Quick Analysis Tool 10

Sparklines 11

Overview of New Features

Excel files are known as workbooks. In Excel 2013 a single workbook can store as many sheets as fit into the computer’s memory, and these sheets are stacked like pages in a notebook and referred to as worksheets. By default a new workbook in Excel 2013 consists of a single worksheet rather than the three that were available when a new workbook was created in previous versions.

Previously one Excel window contained all the open worksheets. In Excel 2013 the workbook window behaves differently. Each workbook appears as a separate instance of Excel. If three workbooks are open, three complete Excel interfaces—including ribbons, tabs, the formula bar, the status bar, and other items—are available, making it easier to work with multiple workbooks, particularly for multiple-monitor users.

The Microsoft Office button has been replaced by the FILE tab. Clicking on the FILE tab opens what is referred to as Backstage view. This is a single location where all the essential controls for and information about documents and behind-the-scene options and settings reside. Traditional file menu commands such as New, Open, Save, Save As, and Print are also available here.

Ribbon commands can now be freely rearranged; for example, changing the order and position of tabs, removing groups, creating custom groups and commands, and showing and hiding existing tabs.

Other new features of Excel are:

·  A new paste view that shows how copied data will look before it is pasted has been added. Hovering over the Paste menu displays Paste Options and shows what each option will do on the worksheet if selected.

·  A new Quick Analysis tool has been added. If more than one empty cell is selected, the Quick Analysis tool appears at the lower-right corner of the selected range. Clicking it displays a context-sensitive toolbar containing tabs and controls applicable to the selected content. Hovering the mouse over the icons displays the live preview of what will be applied to the selected data.

·  The Sparklines feature creates a tiny chart in a worksheet cell that provides a visual representation of data. This feature is used to show trends in a series of values, such as seasonal increases or decreases or economic cycles, or to highlight maximum and minimum values. A sparkline should be positioned near its data for greatest impact.

·  A Flash Fill feature has been added. As data are entered into a cell, Excel compares the data to data in adjacent cells and, if a pattern is detected, Flash Fill offers a fill solution.

·  A new Screenshot menu on the INSERT tab allows the capture of almost any window on the computer in a picture format. The Copy as Picture command or the Picture, Link, or Camera buttons can be used to take pictures of the worksheets.

·  A Recommended Charts button on the INSERT tab attempts to determine appropriate charts based on selected data, rather than presenting every available chart type option. After a chart is created, it is now easier to modify it. The chart elements and style buttons appear to the right of the selected chart.

Launching Excel 2013

The following screen will be displayed when MS Excel is launched:

Previously opened workbooks will be listed under Recent and can be opened by clicking on the name of the file.

To open a new blank workbook, click on Blank workbook, the first option in the right-hand pane.

New Features of Excel 2013

FILE Menu

Clicking on the FILE menu displays a screen full of information and options that Microsoft refers to as Backstage view. Many of the FILE menu commands are available here as clickable items on the left side of the screen. The right side of the screen provides additional clickable items.

Commands available in the Backstage view are organized on pages, which are displayed by clicking the page tabs in the colored left pane. The Backstage view (shown above) of Excel 2013 includes the following pages:

The Backstage view includes a link to the Excel Options window, from which program settings can be managed. Links to close the active item (for example, the current worksheet or current window) or exit the program are available. To redisplay the program or active item and the ribbon, click the Back arrow located above the page tabs.

Compatibility with Previous Versions

When opening a workbook that was created in a previous version in Excel 2013, the file is opened in compatibility mode, which is indicated in the title bar. The file can be worked on normally; when it is saved, it remains in the old format. However, if any changes were made using the Excel 2013 features that are not compatible with the older version, the Excel compatibility checker intervenes when saving the file and displays the list of features that are not supported by the earlier version. At this point the file can be saved in the older version or can be saved in the new format with the new features. To get out of compatibility mode, either save the file using the Save As command or convert the opened file to the Excel 2013 version.

Customizing the Ribbon

In Excel 2013 the ribbon is completely customizable. Command groups can be rearranged, the order and the position of the tabs can be changed, groups can be removed, and custom groups and tabs can be created. Keep in mind that the commands from the existing groups and existing tabs cannot be deleted.

To customize, right-click on the ribbon and select the Customize the Ribbon option command from the shortcut menu that appears. This will display the following screen:

The Excel Options window has two scrollable lists. The Choose commands from: list is on the left side. These commands can be used to create new command groups and tabs. The list on the right shows the current contents of the ribbon. The first level of the outline represents tabs; the second level outlines command groups. Click the plus sign icon to reveal the subordinate groups. Many groups have additional plus signs that expose another level of commands.

Customizing the Quick Access Toolbar

The Quick Access Toolbar is the small toolbar in the top left-hand corner of the screen. The toolbar may be docked in two places—either above or below the ribbon. By default it appears above the ribbon on the title bar. Moving it below the ribbon has two advantages: it is closer to the action on the worksheet, and more space is available for additional tools. On the other hand, leaving the toolbar at the top allows it to occupy unused space on the title bar. The default options available on the toolbar are Save, Undo, and Redo. The easiest way to add a command to Quick Access Toolbar is to right click on the command to display the shortcut menu (shown below) and clicking Add to Quick Access Toolbar.

Worksheets

By default Excel 2013 provides one worksheet when a new workbook is created. However, it is possible to add as many worksheets as the computer’s memory will allow. Worksheets are added by clicking on the plus sign visible in the sheet tab area. A maximum of 31 characters can be used to rename the sheet. Keep in mind that the name determines the width of the corresponding sheet tab. It is a good practice to keep the worksheet name concise to see more worksheets tabs.

Moving and Copying Worksheets

To move the worksheet from one place to another in the same workbook, click a sheet tab to select it and then drag it to its new location. When the sheet is dragged, a small worksheet icon appears and a tiny arrow indicates where the worksheet will be inserted in the tab order.

When moving a worksheet, remember the following tips:

·  To move the worksheet to a location that is not visible on the screen, drag it past the visible tab in either direction. The sheet tabs scroll in the direction the sheet is being dragged.

·  Several sheets can be moved at the same time. First select several sheets and then drag to the desired location.

·  To make a copy of the worksheet, hold down Ctrl key while dragging the worksheet to the new location. When a worksheet is copied, Excel appends a number in parentheses to the copy’s name.

·  Nonadjacent worksheets can be selected by pressing the Ctrl key and clicking on the desired sheet tabs. Then the selected group of sheets can be dragged to move or create copies.

Zooming Worksheets

Use the zooming control in the bottom right-hand corner of the screen to change the size of the worksheet display (or click the VIEW tab and use the Zoom button). Clicking the Zoom button displays additional zoom/reduction options. The Zoom command affects only the selected worksheets. To change the zoom level of multiple worksheets, group the worksheets before zooming and then change the zoom percentage.

Creating Screenshots or Pictures of the Worksheet

The screenshot menu takes a snapshot of any window and everything that appears on the computer screen, with one exception: the current Excel window. To take a picture of any part of a workbook, open another workbook and take the screenshot from there. There are two ways to use the Screenshot feature: capture a full window with a single click or drag the mouse to select a specific area of the screen.

Click on the Take a Screenshot button on the INSERT tab to display the menu shown on the right.

The Screenshot menu shows thumbnails of every window that is open on the computer, including applications, webpages, etc. Click on the desired thumbnail to immediately insert an image of the window onto the active worksheet.

Clicking on the Screen Clipping option minimizes Excel, and the user can select any part of the screen including any other open Excel windows. As soon as the user is finished selecting the area to capture, the image is inserted onto the active worksheet.

Filtering a List or Table

Filtering means hiding all rows except those that meet specified criteria. When a table is created in Excel, filters are added to the header row automatically. To turn header row filters on or off, select any cell in the table and click on the Filter button in the Sort & Filter group on the DATA tab. When the filters are on, Excel displays small arrows next to each of the column headings. Clicking the arrow next to any heading reveals a list of column’s unique values, which can be used to specify filtering criteria.

Immediately after the filters are applied, Excel displays the number of rows that meet the criteria on the status bar. As a reminder that the data are filtered, Excel displays the row numbers in blue color.

To remove a filter from a single column, click the small arrow to the right of the column heading and then click Clear Filter From “Column Name.” To remove all the filters currently in effect, click the Clear button in the Sort & Filter group on the DATA tab.

The filter criteria can be specified in as many columns as needed. Filter the list on one column; then filter the resulting list on another column. Repeat the process as many times as needed. Based on the type of data stored in the column, Excel will present different filtering options.

For a numeric column, the data can be filtered to find the top or bottom n items that make up the top or bottom n percent of the column’s total.

Dates can be frustrating to filter. The user usually wants to filter the data based on some sort of grouping. If the column being filtered by contains dates, the selection list automatically groups the dates into months and years. Click the small arrows for the column that contains dates, and a list of years will be displayed. Expanding the years will show months as shown on the right.

Flash Fill

A new feature in Excel 2013, Flash Fill is a data assistant that provides the option for Excel to add data to cells based on a pattern. As data are typed into cells, Excel compares the data to adjacent cells. If a pattern is detected, Flash Fill offers a solution. This command can be used to split data into multiple columns (also known as parsing) or combining data from two or more cells into one (also known as concatenating). This feature can also be used to change case of text.