Excel

Microsoft Excel – an electronic spreadsheet program that runs on Windows computers and is used to produce professional-looking documents that perform numeric calculations rapidly and accurately. The document produced is called a worksheet.

· Enter data quickly and accurately

· Recalculate data easily

· Perform a what-if analysis

· Change the appearance of information

· Create charts

· Share information with other users

· Create new worksheets from existing ones quickly

The Worksheet Window

· Title bar – displays the program name and the filename of the open worksheet

· Menu bar – contains menus from which you select Excel commands

· Name box – displays the active cell address

· Formula Bar – allows you to enter or edit data in the worksheet

· Toolbars – contain buttons for frequently used Excel commands

o Standard toolbar – located below the menu bar and contains buttons that perform actions within the worksheet

o Formatting toolbar – contains buttons that change the worksheet’s appearance

· Cell – the intersection of a column and a row

· Cell Address – the unique location of each cell designated by the column letter and the row number

· Task pane – an organizational tool that allows you to perform routine tasks quickly and easily

· Cell pointer – a dark rectangle that outlines the cell in wh8ich you are working

· Sheet tabs – below the worksheet grid that let you keep your work in a collection called a workbook

· Status bar – provides a brief description of the active command or task in progress and shows the status of important keys

Closing and Exiting Excel

· Closing Excel – Click File, then select Close

· Exit Excel – Click File, then select Exit

Planning and Designing a Worksheet

· Purpose – determine the purpose and give it a meaningful title

· Output – determine your worksheet’s desired results

· Input – collect all the information that will produce the results you want

· Formulas – determine the calculations necessary to achieve the desired results

· Sketch – sketch on paper how you want the worksheet to look; identify where to place the labels and values

· Create – create the worksheet by entering the labels first to establish the structure

Editing Cell Entries

· Edit Mode - Change cell content by selecting the cell

o Either click the formula bar or Press F2

o Or double-click the cell

· Insertion-point – a blinking vertical line appearing in the formula bar which is used for editing

· Cancel button –a button with an X that can be selected on the formula bar before confirming the cell entry

· Confirm button – a button with a check to confirm the entry

· Formulas – usually start with the equal sign followed by cell addresses and range names

· Arithmetic formulas – use one or more arithmetic operators to perform calculations;

· Precedence - Excel follows mathematical operations according to the rules of precedence; precedence rules can be overridden using parenthesis

· Cell referencing – using a cell address or range name in a formula

o Any formula containing a cell reference will be automatically recalculated using the new value

· Simple formula – contains one arithmetic operator

· Complex formula – an equation that uses more than one type of arithmetic operator

· Case sensitive – Excel is not case sensitive, you may type either uppercase or lowercase characters when you enter cell addresses

· Selecting a range – Click the starting cell, hold down the mouse button, drag to the last cell to select a range

Excel Functions

· Functions – predefined worksheet formulas that enable you to perform complex calculations easily

· Enter functions – you can type functions or you can use the Insert Function button to select the function you need from a dialog box

Formatting

· Formatting – determines how labels and values appear in a cell

o Changes only the way a value or label appears, does not alter cell data

o May be formatted before or after data is entered

Formatting Column Widths

· Default column width – 8.43 characters wide, a little less than one inch

· Adjustments – use the mouse or the Column command on the Format menu

· Column heading – the orange box at the top of each column containing a letter

· Cross arrow pointer – used to adjust the width of a column

· Width – sets the width to a specific number of characters

· Autofit Selection– feature used to resize a column to the widest entry

· Hide – Hide(s) columns(s)

· Unhide – Unhide(s) columns(s)

· Standard Width – Resets width to default widths

Inserting and Deleting Rows and Columns

· Insert rows – rows are inserted above the cell pointer.

· Insert columns – columns are inserted to the left of the cell pointer

· Insert multiple rows – drag across row headings to select the same number of rows as you want to insert

· Delete rows – Use the Edit menu, or right-click the selected row and click Delete. (Clicking delete on the keyboard removes the contents of a selected row; the row itself remains)

Planning and Designing a Chart

· Purpose – determine the purpose of the chart and identify the data relationships you want to communicate graphically

· Output – determine the results you want to see, and decide which chart type is most appropriate to use

· Identify – identify the worksheet data you want to the chart to illustrate

· Sketch – sketch the chart, then use your sketch to decide where the chart elements should be placed

· X-axis (category axis)– the horizontal axis of a chart that often contains the names of data groups

· Y-axis (value axis) – the vertical axis of a chart that often contains numerical values that help you interpret the size of chart elements

· Z-axis – in a 3-D chart, the y-axis is referred to as the z-axis

· Plot area – the area inside the horizontal and vertical axes

· Tick marks – on the y-axis marks the scale of measure for each value

· Data point – each value in a cell that has been selected for a chart

· Data marker – visually represents each data point

· Data series – a collection of related data points

· Legend – used to identify the data series

Creating a Chart

When charting any data, make sure all series are for the same time period.

· Select the range – click the cell at the beginning of the range, drag the mouse to the cell at the ending of the range

· Selecting non-contiguous cells - click the cell at the beginning of the range, press and hold [Ctrl] while selecting each range

· Sample Chart – in the chart wizard, you can preview the chart using your selected data by pressing and holding the Press and Hold to View Sample button

· Chart Wizard

o Select the range then click the Chart Wizard button on the Standard toolbar

o Select Chart Type - Clustered column chart is the default

o The Sample chart with a legend displays. Type the chart title

o Adjust the chart’s location and size

· Sizing handles – the small squares at the corners and sides of the chart’s border that indicate that the chart is selected

Hands-On exam section

You will create a worksheet that requires the following skills:

· Entering data values and labels

· Adjusting the column width

· Centering across columns

· Changing font face and size

· Renaming the worksheet

· Using relative and absolute referencing

· Using Autofill

· Formatting cells

· Using Help

· Adding borders and shading

· Inserting and Deleting rows and columns

You will create a chart using the chart wizard that requires the following skills:

· Selecting the correct chart type

· Showing the data labels and percent

· Selecting the correct legend options

· Saving the chart on a new worksheet

· Naming the worksheet

· Designate particular data on the chart