Excel for Mac 2011 One of the most important skills a working professional possesses is the ability to work with financial data and prepare quantitative analyses to support financial decision-making. In this section: Overview of Spreadsheets, Cells and Cell Data, Formulas, Functions, Formatting Spreadsheets, Spreadsheet Graphics and Advanced Spreadsheet Tools. Skills > Excel for Mac 2011 Overview of Spreadsheets

Excel worksheets are grids: Rows are numbered, starting with 1. Columns are named with letters, starting with A. Cells are the intersection of a row and column (e.g., E6). In this section: Spreadsheet Tools and Features, Case Study, Well-Formed Spreadsheets and Uses. Skills > Excel for Mac 2011 > Overview of Spreadsheets Spreadsheet Tools and Features Tools on the Formulas tab allow use of pre-made functions to generate

formulas efficiently and accurately. Other tools (e.g., SmartArt graphics, charts, and Sparklines) are used for displaying information graphically. Home tab tools enhance worksheet formatting, with a variety of number and cell formats. Skills > Excel for Mac 2011 > Overview of Spreadsheets > Spreadsheet Tools and Features Spreadsheet Case Study This video introduces the Madison Artists

Gallery project case study that will be used throughout the Excel video lessons. Skills > Excel for Mac 2011 > Overview of Spreadsheets > Case Study Well-Formed Spreadsheets Well-formed spreadsheets are neatly organized and properly formatted to help support financial reporting and decision making. Before entering any data, plan the layout.

Skills > Excel for Mac 2011 > Overview of Spreadsheets > Well-Formed Spreadsheets Spreadsheet Uses Spreadsheet programs make it easy to enter, edit, and format text and numerical data for professional-looking documents. Spreadsheets are best suited for decisions that need quantitative support. Learning about the various situations in which an Excel spreadsheet can help with data collection, organization, analysis, and decision making.

Skills > Excel for Mac 2011 > Overview of Spreadsheets > Spreadsheet Uses Cells & Cell Data Theres a phrase commonly used by computing professionals that says, Garbage in, garbage out. It means is that bad data and poorly structured formulas will result in bad information and unusable results coming out. The video lessons on this page are designed so you can do a better job of creating accurate and efficient formulas to work with data so you dont experience garbage in,

garbage out. In this section: Creating and Editing Spreadsheets, Selecting Objects, Entering and Editing Data, Adjusting Rows and Columns, Grouping Rows and Columns, Copying Cell Data, Using the Fill Handle and Inserting/Deleting. Skills > Excel for Mac 2011 > Cells and Cell Data Creating and Editing Spreadsheets Create spreadsheets from scratch or from available templates. To edit an existing file, navigate to storage location and double-click. To rename a tab, Control-click, choose Rename, and type new

name. To rearrange tab order, use clickand-drag to new location. To delete a tab, Control-click and choose Delete. Skills > Excel for Mac 2011 > Cells and Cell Data > Creating and Editing Spreadsheets Selecting Objects An object is anything that can be selected, edited, or manipulated. When you select a picture object, Format Picture contextual tab appears. To see a formula, click the cell

and the formula appears in Formula bar. Other types of objects include charts, graphs, videos, and sound clips. Skills > Excel for Mac 2011 > Cells and Cell Data > Selecting Objects Entering and Editing Data To enter data in a cell, click the cell, then type the text/data OR type equal sign to add a formula. To edit data in cell, click cell and replace

entire cell contents by typing OR doubleclick cell, position insertion point, and make changes. Click outside a cell to save/commit changes. Skills > Excel for Mac 2011 > Cells and Cell Data > Entering and Editing Data Adjusting Rows and Columns To adjust column width (or row height) to fit the data, use AutoFit: position pointer on the border, then double-click to automatically adjust the fit.

To manually set column width (or row height), drag pointer. To adjust a group of columns (or rows), use Format button in Cells group on Home tab. Skills > Excel for Mac 2011 > Cells and Cell Data > Adjusting Rows and Columns Grouping Rows and Columns A group of specific cells in a worksheet is called a range. To reference a range, include the beginning cell, a colon, and the ending cell (e.g., B3:D3).

To select a range of adjacent cells, click a cell and drag the pointer to select additional cell(s). To select a range of non-adjacent cells, click a cell, press and hold the Command key, then click additional cell(s). Skills > Excel for Mac 2011 > Cells and Cell Data > Grouping Rows and Columns Copying Cell Data To copy contents of a single cell (or range of cells), select cell(s), click Copy button,

click destination, click Paste button. To copy adjacent cells, highlight cells to be copied. To copy non-adjacent cells, select first cell, hold down Command key, select additional cell(s) to be copied. Display Paste Options menu by clicking Paste button. Skills > Excel for Mac 2011 > Cells and Cell Data > Copying Cell Data Using the Fill Handle The fill handle is the fastest way to copy and paste content from one cell or range to

another. To copy, click in cell, move mouse over fill handle, press and hold mouse, then drag through ending cell. Use fill handle to complete a text series (e.g., month names). Skills > Excel for Mac 2011 > Cells and Cell Data > Using the Fill Handle Inserting and Deleting To insert row, select row number below new row location, click Insert button. To insert column, select column letter

right of new column, click Insert button. To delete row (or column), select row number (or column letter) to delete, click Delete button. To insert line break in cell, press and hold Control and Option keys, then press Return key. Skills > Excel for Mac 2011 > Cells and Cell Data > Inserting and Deleting Formulas An expression that usually

results in a numerical value. Every Excel formula starts with an equal sign (=), followed by the expression needed to calculate the value. In this section: Using Arithmetic Formulas, Formula Auditing & Using Named Ranges. Skills > Excel for Mac 2011 > Formulas Using Arithmetic Formulas The name box tells which cell is currently selected.

Most Excel formulas contain cell references, either a cell address or a range of cells. As a result, when a cell value changes, the formula results display the new value. Skills > Excel for Mac 2011 > Formulas > Using Arithmetic Formulas Formula Auditing To examine contents of a formula, click a cell and the formula is visible in the Formula bar OR double-click a cell and a color-coded formula is also visible in the cell plus referenced cells are color-coded.

To display formulas instead of results: Click Show button and choose Show Formulas, then again to display results. Press Control+grave accent key to display formulas, then again to display results. Skills > Excel for Mac 2011 > Formulas > Formula Auditing Using Named Ranges To create a defined name, select the cell or range, click Insert menu, point to Name, and click Define.

To use a defined name in a formula, type equal sign (=), click Insert menu, point to Name, click Paste, and select a defined name. Skills > Excel for Mac 2011 > Formulas > Using Named Ranges Functions A function is a pre-made formula that computes a value. Functions are the fastest and most accurate

way to include formulas in a spreadsheet. In this section: Using Functions, Copying Functions, 3D References, Absolute References, Using Logical Functions (IF), Advanced Functions (VLOOKUP), Advanced Functions (PMT) and Function/Formula Troubleshooting Skills > Excel for Mac 2011 > Functions Using Functions SUM adds the values in specified range. MIN determines lowest (minimum) value. MAX determines highest (maximum) value. AVERAGE calculates average value. MEDIAN calculates median value.

COUNTA counts number of cells not empty. COUNTIF counts number of cells that meet a certain condition. Skills > Excel for Mac 2011 > Functions > Using Functions Copying Functions Copying a formula is just like copying a number or value. You can use Copy and Paste buttons on the Standard toolbar OR you can grab the fill handle and drag it. Skills > Excel for Mac 2011 > Functions > Copying Functions

3D References A 3-D reference refers to the same cell or range of cells on multiple worksheets. In addition to summing, you can use 3-D references to compute statistics, such as averages, and much more. Skills > Excel for Mac 2011 > Functions > 3D Functions Absolute References So far you have used relative

references, which means that when you copy a formula from one cell to another, the cell ranges automatically adjust to reflect the new position of the formula in the worksheet. An absolute reference doesnt change when the formula is copied to another cell. Adding dollar signs before the column and row of a cell reference tells Excel to always go to that cell. Skills > Excel for Mac 2011 > Functions > Absolute References

Using Logical Functions (IF) A logical function is a formula that tests a condition that can either be true or false and then returns a value based on the result of the condition. The IF function returns one value if the condition is true, and another if the condition is false. The Formula Builder button in the Function group on Formulas tab can help! Skills > Excel for Mac 2011 > Functions > Using Logical Functions (IF)

Advanced Functions (VLOOKUP) VLOOKUP looks up values in a table of data and then inserts those values in another location in a worksheet. The VLOOKUP function requires three pieces of information: Value you want to look up. Range of cells where the lookup table is located. Table column with the value you want.

Skills > Excel for Mac 2011 > Functions > Advanced Functions (VLOOKUP) Advanced Functions (PMT) The PMT, or Payment, function calculates what a series of loan payments will be before you commit to the decision. The PMT function requires three pieces of information: Amount borrowed. Annual interest rate. Duration of loan. NOTE: Be careful with annual/monthly and negative values!

Skills > Excel for Mac 2011 > Functions > Advanced Functions (PMT) Function and Formula Troubleshooting A green triangle in the upper left corner lets you know that Excel can provide information about an error. Holding the mouse pointer over the information icon displays a screentip. Tools on the Formulas tab in the Audit Formulas group include: Trace Precedents Trace Dependents Check for Errors

Skills > Excel for Mac 2011 > Functions > Function and Formula Troubleshooting Formatting Spreadsheets In this section: Number Formats, Aligning Cell Contents, Sorting and Filtering Data, Working With Tables, Applying Borders and Shading, and Applying Conditional Formatting. Skills > Excel for Mac 2011 > Formatting Spreadsheets Number Formats Currency format includes dollar sign at left, commas, zero, and 2 decimal places.

Accounting format changes dollar sign to be aligned on left margin and cells with zero show a dash. Percent format includes percentage sign. Comma format applies Accounting format without dollar signs and aligns decimals. Increase/Decrease decimal places with buttons in Number group on Home tab. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Number Formats Aligning Cell Contents The general rule for formatting is to

center column headings, keep text left-aligned, and keep numbers rightaligned, with decimal places aligned. To fully display text in a cell, you may need the Wrap Text button. Adjust vertical alignment with the Align Top, Align Middle, and Align Bottom buttons. Center text over multiple columns with the Merge button. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Aligning Cell Contents Sorting and Filtering Data When working with large sets of

data, it can be useful to sort (rearrange) the data to make it easier to read. You can also filter data to show only the data you need at the moment. Both sorting and filtering can make data easier to use when making decisions. The tools are in the Sort & Filter group on the Data tab. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Sorting and Filtering Data Working with Tables

A table style gives the range a cohesive look; with coordinated font colors, fill colors, and more. This formatting makes a table easier to read and work with. More importantly, a range designated as an Excel table can be analyzed, sorted, transferred, filtered, and otherwise treated as separate from the rest of the Excel worksheet. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Working with Tables Applying Borders & Shading

One way to draw attention to the contents of a cell or range is to add a border to the top, left, bottom, or right sides, or to all sides at once. You can add borders to one cell at a time, or to a range of cells. Shading is another way to draw attention to individual cells or ranges. Using tools on the Fill tab, you can modify fill colors (background or foreground) and pattern style. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Applying Borders and Shading

Applying Conditional Formatting Conditional formatting is useful for highlighting trends in data that might otherwise be difficult to see. Conditional formatting can also be used to highlight cells containing data that meet certain criteria. Skills > Excel for Mac 2011 > Formatting Spreadsheets > Applying Conditional Formatting Spreadsheet Graphics In this section: Creating Charts, Chart Types, Creating Sparklines, Inserting

Pictures, Adjusting Page Layout and Printing Spreadsheets. Skills > Excel for Mac 2011 > Spreadsheet Graphics Creating Charts Charts add visual interest to data- and text-intensive worksheets, showing trends and relationships in the data. Four steps to creating charts: Select the range of cells. Choose a chart style. Modify formatting and titles. Position the finished chart on worksheet or move to

new sheet. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Creating Charts Chart Types The best type of chart depends on the data you want to illustrate. For example, pie charts are best for showing each data value as a percentage of the whole. When you select a chart, two contextual chart tabs are visible; the Chart Layout tab (e.g., chart

title and labels) and the Format tab (e.g., style and background fill). Skills > Excel for Mac 2011 > Spreadsheet Graphics > Chart Types Creating Sparklines A sparkline is a mini-chart within an Excel worksheet cell meant to show a large amount of graphical information in limited space. Three types of sparklines: A line sparkline shows a

trend over time. A column sparkline highlights trends in columnar form. A Win/Loss sparkline shows positive and negative values. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Creating Sparklines Inserting Pictures Its easy to add visual interest to your spreadsheets by inserting a picture or two that supports the data contained on the

spreadsheets. Click where you want to insert the picture, click Insert menu, choose Photo, and click the Picture from File option. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Inserting Pictures Integration: Inserting Graphics From PowerPoint In PowerPoint: Click the graphics border to select the entire graphic. Control-click in the white

space inside the border to display the shortcut menu. Click Copy. In Excel, position cursor, click Paste Special, then Paste as Picture option in the Paste menu. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Integration: Inserting Graphics From PowerPoint Adjusting Page Layout There are two choices for Gridlines: in the View group and in the Print group. The Orientation of worksheets

(Portrait or Landscape) is important when viewing and printing. Headers and Footers appear when viewing and printing worksheets. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Adjusting Page Layout Printing Spreadsheets To control data flow from one page to another, add page breaks. To print only a portion of a

sheet, set a print area. To add print titles to multiple pages, designate the Rows to repeat at top. Scaling lets you adjust the height and width of a printouts contents to fit more data on a single sheet. Skills > Excel for Mac 2011 > Spreadsheet Graphics > Printing Spreadsheets Advanced Spreadsheet Tools In addition to the basic features demonstrated in earlier lessons,

there are more advanced features and tools available to help you take data-intensive worksheets and summarize them into meaningful forms for your readers. These features include Pivot Tables, the What-If and Goal Seek tools, and add-in programs such as Solver. In this section: Pivot Tables, Using Data Tools and Using Add-Ins. Skills > Excel for Mac 2011 > Advanced Spreadsheet Tools

Pivot Tables A PivotTable is an interactive Excel table thats used to summarize a range of data for easy reporting and analysis: Convert the data to a table. Click on any cell in the table. Click the Tables tab and click Summarize with PivotTable button. The Pivot Table is now ready for formatting as a report.

Skills > Excel for Mac 2011 > Advanced Spreadsheet Tools > Pivot Tables Using Data Tools The What-If Analysis button allows you to try out different values in a worksheet to see how the formula results change. The Goal Seek option allows you to specify a goal. Excel then works backwards to determine the value that will produce that goal.

Skills > Excel for Mac 2011 > Advanced Spreadsheet Tools > Using Data Tools Using Add-Ins Solver is a what-if analysis tool used to solve linear programming types of problems, helping to find the optimal solution to a problem, given certain constraints. To load Solver: Click the Tools menu Click Add-Ins. In the Add-Ins available box,

check the Solver box. Skills > Excel for Mac 2011 > Advanced Spreadsheet Tools > Using Add-Ins