Excel 2011 for the Mac Training

Microsoft Excel is a popular spreadsheet program that is part of the Microsoft Office suite of products. This LIS Training document deals with Excel 2011, which is the latest version of Excel for the Mac.

Goals

Upon completion of this 1-on-1 training, you will be able to:

Training

New Features

Ribbon
In Microsoft Excel 2011 the ribbon is located directly below the standard toolbar and is organized into different tabs, consisting of different commands. The Ribbon toolbar allows the user to easily access formatting features. Users can easily toggle between different formatting such as the layout, tables, charts, formulas, font, alignment and much more.

Home Tab
The left most tab in Excel workbook. It contains the features and commands that you will probably use the most. Each tab is arranged into logical and related groups. For example, Edit, Font, Aligment, Number, Format, Cells, and Theme.

Excel - 1

Layout Tab
Allows you to do page setup, set margins, text layout, page background, print options and window options.

Excel - 2

Tables Tab
You can change table styles and designs as you like. Insert and Delete options will make it easier to manipulate the tables. Under Tables tab, you can apply quick the themes and table styles over Excel 2011 spreadsheet. Excel provides many handy methods for inserting tables into your workbooks. For example, you can insert a table with a header or without a header. To insert a table in Excel use the following options:

  • On the Tables tab, under Table options, click New, and then click Insert table with header or Insert table without header.
  • You can use different table styles to give your tables a more professional look.

Excel - 3

Charts Tab
Display data in a graphical format. This can help you and your audience visualize relationships between data. When you create a chart, you can select from many chart types. When you are done creating a chart, you can customize it by applying chart quick layouts or styles.

Excel - 4

SmartArt Tab
Allows you to create graphics very easily, such as the life cycle of a plant, using the templates provided. This new feature allows you to make that average Excel workbook into a flashy graphics and text loaded workbook. You can create an organizational chart by using a SmartArt graphic to show the reporting relationships in your organization. On the SmartArt tab, under Insert SmartArt Graphic, click Hierarchy, and then click Organization Chart, which is the first SmartArt graphic. You can use different layouts to do your graphic models.

Excel - 5

Formulas Tab
Lets you do all things formula-related. Many different formulas are found in the Insert command under Formulas tab.

Excel - 6

Data tab
Allows you to work with data in many different ways such as what if analysis. It allows you to sort and filter your data, do analysis, use external data sources, and use tools.

Excel - 7

Review Tab
Allows you to track changes made, add comments and share the workbook. You can insert your own tracked changes and comments, hide tracked changes in a printout, protect the workbooks or sheets with a password and set a password to make sure that future changes are tracked. Before you could not check the spelling errors in previous versions of Excel but now you can. To check the spelling you can simply select the text and click Review tab and then under Proofing click on Spelling.

Excel - 8

Toolbox
Contains Formula Builder, Reference Tools, Scrapbook, and the Compatibility Report in Excel workbook. Toolbox has some specific tools. For example, the PowerPoint Toolbox contains Formula Builder, reference tools which provide access to formulas, thesaurus, dictionary, and translation information.To change the display settings of the toolbar you can click on Toolbox SettingsToolbox and make the changes you want.

Excel - 9

Added Functions
The long expected Excel function Macros has been added in Excel 2011 for Mac. Integrated-VB support will let you create and customize functions. To find these integrated formulas click on Formulas tab and the click Insert command and a pile of formulas will be shown.

Excel - 10

Templates
With more improved templates in Excel 2011 for Mac, you can have a good view on your spreadsheet, as it contains legible templates, which are easier to design and change. To apply different templates to your workbook click on Excel - 11 icon in standard toolbar.

Excel - 12

Create a Workbook

A file that you create with Excel is called a “workbook”. A workbook is a set of spreadsheets called “worksheets”. In Excel, you create and save data in a workbook. You can start with a blank workbook, existing saved workbooks or a template. When you open Excel, the Excel Workbook Gallery opens. This gallery provides a variety of template choices and quick access to recent workbooks. First, open Microsoft Excel by clicking Macintosh HD > Applications >Microsoft Office 2011 > Microsoft Excel. In the Excel Workbook Gallery, under Templates, click All, click Excel Workbook, and then click Choose.

Excel - 13

Enter Data into a Worksheet

A worksheet is a grid array of “cells”, into which you can enter data. The columns are lettered, and the rows are numbered. A cell can be referred to by its “reference”, which is its column letter followed by its row number. For example, A1 is the top left cell. The basic process for entering data into a cell is to select a cell, and start typing data into it. A selected cell has a black rectangle around it. Let’s enter some data into our spreadsheet.

1. Click on cell B1 to select it. Type “Price,” and hit the Tab button.

Note: when you hit the Tab button after entering data, you move one cell to the right.

2. Type “Qty” in cell C1, hit Tab, and type “Ext Price” in cell D1.

3. Select cell A2, type “Whizbang,” and hit Enter.

Note: when you hit Enter, you move one cell down.

4. In cell A3, type “Gadget.” In cell A4, type “Gizmo.” In cell A5, type “Red Stapler.”

5. You may notice that the text “Red Stapler,” is longer than the width of the cell. If so, let’s adjust the A column to be wide enough to fit that text in it. Move your cursor between the A and B column. Your cursor will turn into a two-sided arrow with a vertical line. You can manually resize columns by simply dragging the edge between the columns, or you can simply double-click and Excel will automatically make the column just as wide as it needs to be. Let’s do that.

Excel - 14

6. You just got a call from the Whizbang people, and they are changing the name of their product from Whizbang to Thingamabob. Click on cell A2 and type “Thingamabob.”

Notice that the normal behavior when you click on a cell is for the contents of the cell to be replaced with whatever you type. This is normally desirable behavior, especially when you’re dealing with small strings of text, like numbers. This probably wouldn’t be very desirable if, for example, you needed to edit the text “Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch” (which, believe it or not, is really the name of a Welsh town). To avoid retyping that entire string of text, you can edit the text in the Formula bar.

Auto-resize the column again by double-clicking between A and B. Your spreadsheet should now look something like this:

Excel - 15

7. Let’s start entering numerical data. First, let’s enter in the prices. Click on cell B2, and start typing in the following values, hitting Enter between values: 4.99, 9.56, 3.22, 14.77.

8. Let’s format these cells so that they are formatted as currency values. Click on cell B2, hold the mouse button down, and drag your cursor to cell B5. Another way to select these cells is to click on B2 (or navigate to it with the arrow keys on your keyboard), hold the Shift key, and use the arrow keys on your keyboard to move down to the bottom cell you want to select). Ctrl-click on the cells and select Format Cells… and then select Currency or simply look into Home tab under Number and select Currency from the drop down box.

Excel - 16

9. Let’s enter quantities into the Qty column. Use the values 2,5,3,1.

10. For the Ext Price column, we are going to explore two powerful features in which Excel makes your life easier: formulas and replication. The formula for extended price is simply the price multiplied by the quantity. So the formula that you want to type for cell D2 is:

=(B2*C2)

11. After you have entered this and you hit the enter key, the cell will display 9.98, which is the result of that formula. If you want to look at the formula that makes that result, you want to look in the Formula Bar.

Excel - 17

12. Let’s put the formulas in the other three cells. Instead of typing in the formula three more times, we can use “replication” to have Excel automatically make the formulas for us. To do this, click on D2. At the bottom right corner of the cell there is a light blue square. Move your cursor on top of this square, click, and drag to D5. Excel understands that your extended price is determined by multiplying the two cells to the left of the current cell, so it applies that pattern to the other three cells. If you prefer to use the keyboard shortcuts, Ctrl+D will work as well.

Excel - 18

Excel - 20

13. It turns out that red staplers are on sale. The price is actually $9.99. Click on cell B5 and type 9.99 to change the price. Notice that the cell is still formatted as a currency value, and also notice that the extended price changed automatically.

14. Select cells D2 through D5 and format them as currency values.

Excel - 19

Format Cells as Table

One of Excel 2011’s new features is the ability to quickly format a series of cells as a table. With a couple of clicks, you can take some boring looking data and make it semi-exciting. We are going to do this to the data we entered earlier.

  1. Select cells A1 through D5 by clicking on A1, holding the Shift key, and clicking on D5.
  2. Click on Tables tab and select a table style of you choice.
  3. Now your data will be formed a table.
  4. To give a little bit of distinction to the products in table, This makes the product names bold you can choose many different styles of you choice under Table tab.
  5. Right now, the items in the table are in no particular order. Let’s change that. Click on the little arrow at the far right of Column1 and choose Sort A to Z Ascending. Now the products are arranged in alphabetical order.
  6. Even though your table looks radically different now, you can still edit it like a spreadsheet. Click on A1 and type “Product.”
  7. You also can sort the table by price by clicking on the arrow on the far right of the Price column, and choose Sort A to Z Ascending. Your spreadsheet should look like this:

Excel - 21

Save Workbook

To save, click File > Save. You can also use the CMD+S keyboard shortcut.

Print Workbook

To print a workbook, click File > Print…. You can also use the CMD+P keyboard shortcut.

Save a Workbook for Use With Previous Versions of Excel

Excel 97, Excel X, Excel 2004 all saved files in the same file format. Office 2011 introduces a new file format based on XML. This new file format is the same as in Microsoft Office 2008. It makes for smaller files that are more human-readable and are less likely to become corrupted. However, previous versions of Office cannot open files in the new format without an add-on. If you need to share files with somebody who does not have Office 2011 and does not have the needed add-on you may save your workbook in the older format. However, if you use any Excel 2011-specific features in this workbook they will be lost in translation.

The add-on from Microsoft is available here. It is listed as Open XML File Format Converter for Mac 1.1.9 and found in the top half of the page under Additional Tools

.

Note: If you are using Microsoft Excel 2008 then there is no need to install the Format Converter.

To save as an Excel 97-2004 workbook, click File > Save As…, and under format choose Excel 97-2004 Workbook (.xls).

Excel - 22

Additional Resources

Review the Help within Excel 2011

Excel 2011 has a built-in help system. Click Excel - 23.

  • You can select a topic by clicking on the text, such as Entering and importing data or PivotTable .
  • You can also type a word or phrase into the box at the top of the window, click the Ask a Question field, and then type the subject you would like help on.

Review Online Microsoft Resources

Consult Lynda.com

Lynda.com is a provider of web-based software training videos and resources. If you are interested in access to Lynda.com please contact the Technology Help Desk at helpdesk@luther.edu or 563-387-1000.

Review the Technology Help Desk web site

Contact the Technology Help Desk