facebook google plus twitter
Webucator's Free Upgrading to Microsoft Office 2013 Tutorial

Lesson: Upgrading to Excel 2013

Welcome to our free Upgrading to Microsoft Office 2013 tutorial. This tutorial is based on Webucator's Upgrading to Microsoft Office 2013 course.

Microsoft Excel 2010 and 2013 provide a number of new features. In this lesson, you will review how to perform basic tasks in Excel 2013, as well as what features are new or have changed.

Lesson Goals

  • Learn how to start Microsoft Excel.
  • Learn how to create a workbook.
  • Learn how to add or remove worksheets.
  • Learn about formulas in Excel.
  • Learn about Ribbon tabs, groups, and commands, as well as on-demand tool tabs.
  • Learn how to work with previous versions of Excel.
  • Learn about Excel 2013 new features including Flash Fill and Slicers.

Starting Microsoft Excel

To start Microsoft Excel (see screen shot below):

  1. Click the Start menu.
  2. Select All Programs.
  3. Select Microsoft Office 2013.
  4. Select Excel 2013.

Creating a Workbook

When you open Microsoft Excel, a workbook is automatically opened as well. As you can see in the screens hot below, the default workbook is named "Book1".

Cell A1 is automatically selected when Excel opens. To enter a number or text in cell A1, simply begin typing:

Saving a Workbook

The first time you save a Microsoft Excel workbook, you need to give it a name and location. To do this:

  1. From the FILE menu, select Save As: and navigate to where you want to save the file.
  2. Selecting Save As opens up a dialog box in which you can see:
    1. The workbook location, or where on your computer Excel will save your workbook. You can select a new location by clicking on the arrows.
    2. The file name. Note that this is highlighted as Microsoft Excel expects you to choose your own name for the workbook. Simply begin typing to do so.
    3. The file type. Note that this defaults to "Excel Workbook", which is the default file type for Microsoft Excel 2013 workbooks. When final, you can choose to save your workbook as another type, such as a pdf, simply by choosing "PDF (*.pdf)" here.
  3. After you have entered these fields, simply click Save to save the workbook.

Adding and Deleting Worksheets

Depending on your settings, Excel workbooks typically open with one to three worksheets, named Sheet1, Sheet2, etc:

To add additional worksheets, simply click on the Insert worksheet icon ().

To delete a worksheet, simply right-click on the worksheet name and select Delete:

To change the name of a worksheet, double-click the current name and begin typing:

Closing a Workbook

The two most common ways to close a Microsoft Excel workbook are:

  1. Click one of the "X's" in the upper-right-hand corner:
  2. Select Close from the FILE menu:

When you close your workbook, you will be prompted to save if you have made any changes since the last time you saved.

The Status Bar

The Status Bar, located at the bottom of Excel, shows basic information about your workbook and enables you to change your viewing settings. Specific items on the Status Bar include:

  1. Ready status. Ready indicates that you are ready to begin entering data. Enter indicates that you are working within a cell. Edit indicates that you are editing existing data within a cell.
  2. Information about highlighted data. You can customize what you see, but things you see by default include Average, Count and Sum.
  3. View controls. You can choose from a selection of views (Normal, Page Layout, and Page Break Preview).
  4. Zoom control. You can zoom in or out, to make the workbook bigger or smaller, based on your personal preference. Zooming changes the size of what you're viewing. It does not change what you actually print out.

Using Formulas

In Excel 2013, formulas now reside on the FORMULAS tab of the Ribbon.

On this tab, you can create and work with formulas in your workbook.

However, there are other places within Excel 2013 where you can use formulas.

When on the HOME tab, in the Editing group, you can use the AutoSum button to quickly add your data.

Also, when working in Excel 2013, you can work with formulas regardless of what tab you are on in the Ribbon. Simply do the following:

  1. Put your cursor in the relevant cell.
  2. Type your formula in the Insert Function text box on the Formula bar.

Tabs

Microsoft Excel is a powerful program which is used to analyze and present data, perform calculations, and much more. Microsoft Excel has hundreds of commands for working with different scenarios. To make it easier for users to find the specific commands they are looking for, commands are organized onto eight main tabs:

  1. HOME. The HOME tab includes commands for formatting worksheets, cells and data and commands for inserting and deleting columns and rows.
  2. INSERT. Use the INSERT tab to insert tables, illustrations, charts, links, sparklines, headers & footers, custom text and symbols, and more.
  3. PAGE LAYOUT. Use the PAGE LAYOUT tab to change your margins, change the page background, change the page orientation, and more.
  4. FORMULAS. Use the FORMULAS tab to browse and select formulas and functions, to define names, to audit formulas, and more.
  5. DATA. Use the DATA tab to access external data, to sort & filter, to access data tools, to group cells together, to add subtotals, and more.
  6. REVIEW. Use the REVIEW tab to check spelling, add comments, protect your worksheet or workbook, and more.
  7. VIEW. Use the VIEW tab to change your workbook view, show or hide gridlines, headings, the formula bar and the ruler, arrange windows, freeze panes, zoom in or out, and more.
  8. ADD-INS. You may or may not see the ADD-INS tab. This tab shows up after you install and activate your first Add-In. Add-Ins are beyond the scope of this course.

Note that the FILE menu is not the same as a tab. The FILE menu takes you to the Backstage view, where you manage, rather than make changes to, your workbook.

Groups

To further organize the many commands available in Microsoft Excel, commands are organized in groups on each tab. Each group contains three or more related commands. The following table lists the groups found on each tab:

Tab Group
HOME
  1. Clipboard
  2. Font
  3. Alignment
  4. Number
  5. Styles
  6. Cells
  7. Editing
INSERT
  1. Tables
  2. Illustrations
  3. Apps
  4. Charts
  5. Reports
  6. Sparklines
  7. Filters
  8. Links
  9. Text
  10. Symbols
PAGE LAYOUT
  1. Themes
  2. Page Setup
  3. Scale to Fit
  4. Sheet Options
  5. Arrange
FORMULAS
  1. Function Library
  2. Defined Names
  3. Formula Auditing
  4. Calculation
  5. Solutions
Data
  1. Get External Data
  2. Connections
  3. Sort & Filter
  4. Data Tools
  5. Outline
  6. Analysis
REVIEW
  1. Proofing
  2. Language
  3. Comments
  4. Changes
VIEW
  1. Workbook Views
  2. Show
  3. Zoom
  4. Window
  5. Macros
ADD-INS
  1. Custom Toolbars

Commands

Commands are controls that enable you to accomplish specific tasks, such as bolding a word, wrapping text, changing the format of a number to percent, or adding a column.

The HOME Tab

On the Excel 2013 HOME tab, you will find commands that you will use often when working in Excel. The Copy and Paste commands are found on the HOME tab in the Clipboard group.

The Font group contains commands to format text within your workbook.

In the Alignment group, you will find commands to center and align your text.

The Number group contains commands that you can use to format the numbers that appear in your worksheets.

In the Styles group, you will see options to apply conditional formatting, as well as table and cell styles, to help make your workbooks look polished and professionals.

Within the Cells group, you will find the options to insert, delete, and format cells within your worksheets.

And finally on the HOME tab is the Editing group, which contains the Sort & Filter and the Find & Select options, along with the AutoSum command and the Fill and Clear commands.

On-demand Commands on the Ribbon

One of the new features in Excel 2010 and 2013, as discussed previously, are tools on the Ribbon that appear on demand, as you work. These tools will automatically appear as you work in your worksheet. They will be relevant to what you are doing at the time.

For example, if you are working with a PivotTable in your Excel worksheet, when you are working in your table, you will notice ANALYZE and DESIGN tabs, along with the PivotTable Tools, will appear on the Ribbon.

When you click away from the PivotTable, the on-demand tools will disappear, and the standard tabs will remain.

Keyboard Shortcuts

In Excel 2010 and 2013, keyboard shortcuts that begin with the CTRL key, such as CTRL+N to create a new, blank workbook and CTRL+F to display the Find and Replace dialog box, remain the same as in previous versions of Excel.

However, shortcuts that in previous Excel versions involved pressing ALT have changed; these are now called KeyTips.

To display KeyTips in your workbook:

  1. Press ALT. Doing this makes KeyTips appear for all of the tabs on the Ribbon.
  2. Now press the corresponding key to open the tab or Quick Access Toolbar you want to use.
  3. Press the letter of the command you wish to execute.

For example, to add a comment in your workbook, you would

  1. Press ALT.
  2. Then press R to display the Review tab.
  3. Then C to insert your comment.

Working with Other Versions of Excel

Excel 2010 and 2013 workbooks use the .xlsx file extension. Excel 2010 and 2013 are based on Office Open XML formats (but you do not need to know how to use XML to work in Excel 2010/2013).

The reasoning behind this change is because XML helps keep workbooks safer by:

  1. Separating files containing macros or scripts.
  2. Keeping workbook sizes smaller.
  3. Helping to prevent data corruption and loss by making workbooks less susceptible to damage.

Opening Workbooks Created in Previous Excel Versions

You can open workbooks that were created in previous versions of Excel. When you open such a workbook, it will open in Compatibility Mode.

Saving an Excel File Created in a Previous Version

When you save a file that was created originally in a previous version of Excel, the Save As dialog box will open automatically, to save the file with an .xlsx file extension.

Saving an Excel File as a Previous Version

Also, when you save an Excel 2010/2013 file as a previous version, Compatibility Checker will identify any new features that will not work in that version.

One Workbook per Window Feature

In previous versions of Excel, when working with multiple workbooks, they would all open in the same window.

This would make it difficult to work with two workbooks at once. However, in Excel 2013, each workbook opens in its own window. This makes it easier to work with them, especially if you are using multiple monitors.

Using Flash Fill

Flash Fill is similar to the AutoComplete feature, but it is new to Excel 2013. It looks for patterns in your data and shows a suggestion. As soon as it detects what you are attempting to do, it shows a recommended pattern.

In the following example, full names have been entered in Column A. In Column B, this is what happens when you begin entering just first names, pressing Enter after each:

Using Flash Fill

Duration: 05 to 10 minutes.

In this exercise, you will use the new Flash Fill feature.

  1. Open a new, blank worksheet in Excel.
  2. In column A, enter the following:
    1. Angela Ang
    2. Bobbi Born
    3. Chris Carter
    4. Doris Day
    5. Edward Everly
  3. Use Flash Fill to enter just the first names in column B.

Solution:

  1. In a new, blank document, enter the data in column A.
  2. Click in cell B1, and type "Angela" and press Enter.
  3. Click in cell B2, and as you begin to type "Bobbi", Flash Fill fills in rest of the first names.
  4. To accept the rest of the data, press Enter.
  5. Close the worksheet without saving your changes.

New Functions in Excel 2013

Excel 2013 contains a number of new functions that are available to work with that are available in the Function Library group of the FORMULAS tab.

Some of the new functions include the following. To view all of the functions, click each button in the Function Library.

  1. DECIMAL function: Available on the Math & Trig tab. This function converts the text of a number in a given base into a decimal number.
  2. ACOT function: Available on the Math & Trig tab. This function returns the arccotangent of a number.
  3. ENCODEURL function: Available on the More Functions tab in the Web section. This function returns a URL-encoded string.
  4. DAYS function: Available on the Date & Time tab. This function shows the number of days between two dates.

Excel 2013 contains a number of new functions that are available to work with that are available in the Function Library group of the FORMULAS tab.

Some of the new functions include the following. To view all of the functions, click each button in the Function Library.

  1. DECIMAL function: Available on the Math & Trig tab. This function converts the text of a number in a given base into a decimal number.
  2. ACOT function: Available on the Math & Trig tab. This function returns the arccotangent of a number.
  3. ENCODEURL function: Available on the More Functions tab in the Web section. This function returns a URL-encoded string.
  4. DAYS function: Available on the Date & Time tab. This function shows the number of days between two dates.

New Functions in Excel 2013

Excel 2013 contains a number of new functions that are available to work with that are available in the Function Library group of the FORMULAS tab.

Some of the new functions include the following. To view all of the functions, click each button in the Function Library.

  1. DECIMAL function: Available on the Math & Trig tab. This function converts the text of a number in a given base into a decimal number.
  2. ACOT function: Available on the Math & Trig tab. This function returns the arccotangent of a number.
  3. ENCODEURL function: Available on the More Functions tab in the Web section. This function returns a URL-encoded string.
  4. DAYS function: Available on the Date & Time tab. This function shows the number of days between two dates.

New Functions in Excel 2013

Excel 2013 contains a number of new functions that are available to work with that are available in the Function Library group of the FORMULAS tab.

Some of the new functions include the following. To view all of the functions, click each button in the Function Library.

  1. DECIMAL function: Available on the Math & Trig tab. This function converts the text of a number in a given base into a decimal number.
  2. ACOT function: Available on the Math & Trig tab. This function returns the arccotangent of a number.
  3. ENCODEURL function: Available on the More Functions tab in the Web section. This function returns a URL-encoded string.
  4. DAYS function: Available on the Date & Time tab. This function shows the number of days between two dates.

Using Slicers to Filter Data

Slicers was a new feature added to Excel 2010, which allows users to filter PivotTable data. In Excel 2013, you can now use slicers to filter table data.

To filter data using slicers:

  1. Click in the table.
  2. From the TABLE TOOLS DESIGN tab, from the Tools group, select Insert Slicer.
  3. In the Insert Slicers dialog box, check the check boxes of the desired slicers.
  4. Click OK.
  5. The slicers are now displayed in the worksheet.

Filtering Data with Slicers

Duration: 05 to 10 minutes.

In this exercise, you will use slicers to filter data in Excel 2013.

  1. Open Filtering with Slicers.xlsx from your Upgrading-2013-Excel/Exercises folder.
  2. Create slicers for the data for the months of January, February, and March.

Solution:

  1. Click in the table.
  2. From the TABLE TOOLS DESIGN tab, from the Tools group, select Insert Slicer.
  3. In the Insert Slicers dialog box, check the Jan, Feb, and Mar check boxes and click OK.
  4. View the slicers in your worksheet.

Creating a PivotTable Timeline

You can now use the PivotTable Timeline feature to filter your data by time.

To create a PivotTable Timeline:

  1. Click the PivotTable.
  2. From the PIVOTTABLE TOOLS ANALYZE tab, in the Filter group, select Insert Timeline.
  3. In the Insert Timeline dialog box, check the check box of the date fields you want in the timeline.
  4. The timeline is displayed. To filter by date, click the arrow next to the time section and make a selection.
  5. Drag the scroll bar to see the filtered data.
  6. To see a specific time period, click and drag the timeline handles.
  7. Click the Clear Filter button to clear the timeline.

Creating a Timeline

Duration: 05 to 10 minutes.

In this exercise, you will create a PivotTable timeline.

  1. Open PivotTables Timeline.xlsx from your Upgrading-2013-Excel/Exercises folder.
  2. Create a PivotTable Timeline based on the PivotTable showing all periods.

Solution:

  1. Open the PivotTable and click it to select it.
  2. From the PIVOTTABLE TOOLS ANALYZE tab, in the Filter group, select Insert Timeline.

Creating a Standalone PivotChart

In previous versions of Excel, a PivotChart needed to be associated with a PivotTable; however, in Excel 2013 you can now create a standalone PivotChart.

To create a standalone PivotChart:

  1. Click in your data.
  2. Select the Insert tab and, in the Charts group, select Recommended Charts.
  3. On the Recommended Charts tab, select a chart with the PivotChart icon and view the preview.
  4. When you find a chart you would like to select, click OK.
  5. The PivotChart is displayed.