ce1556 microsoft office microsoft excel part a. objectives define worksheets and workbooks use...
Post on 27-Dec-2015
216 Views
Preview:
TRANSCRIPT
CE1556 Microsoft CE1556 Microsoft OfficeOffice
Microsoft ExcelPart A
ObjectivesObjectives
Define worksheets and workbooks Use spreadsheets across disciplines Plan for good workbook and worksheet
design Identify Excel window components Enter and edit data in cells
Objectives (continued)Objectives (continued)
Describe and use symbols and the order of precedence
Display cell formulas Insert and delete rows and columns Use cell ranges, move, copy, paste, paste
special, and AutoFill Manage worksheets
Objectives (continued)Objectives (continued)
Format worksheets Select page setup options for printing Manage cell comments
Define Worksheets andDefine Worksheets and WorkbooksWorkbooks
Spreadsheet ─ computerized equivalent of a ledger Excel ─ a computerized spreadsheet
application used to build and manipulate worksheets and workbooks
Worksheet ─ a spreadsheet that may contain data, values, formulas, and/or charts
Workbook ─ a collection of related worksheets within one file
Excel workbook
This workbook currently has three worksheets
Define Worksheets and Workbooks
Using Spreadsheets Using Spreadsheets Across DisciplinesAcross Disciplines
Spreadsheets have applications in varied disciplines
Used for business applications, such as accounting
Used for “what-if” analysis in business planning Can also be used in scientific applications
Geologists can use to chart data about scientific phenomena
Social Scientists can use to predict voting results
Planning for Good Planning for Good Workbook and Workbook and
Worksheet DesignWorksheet Design
Plan before you start entering data Steps to ensure a good design:
Decide on the purpose of the spreadsheet and how it will be constructed
Make it obvious where data is to be entered Enter data and set up formulas wherever possible
Allow Excel to do what it was designed for – automatic calculation
Planning for Good Workbook Planning for Good Workbook and Worksheet Designand Worksheet Design
Test multiple times to make sure the results are what you expect
Know what your results should be, so that you know your result is correct
Format the worksheet so it looks appealing Document the worksheet as thoroughly as
possible Save and print the results
Identifying Excel Window Identifying Excel Window ComponentsComponents
Worksheet is divided into a grid of rows and columns
Rows are numbered; columns are lettered Cell ─ an intersection of a column and a row Cell reference ─ the address of that
intersection Designated by column letter, then row number
Navigate worksheets using either the mouse or keyboard
Identifying Excel Window Identifying Excel Window ComponentsComponents
Active cell The cell you are working in; where data will
be input Formula bar
Shows the active cell’s contents Name box
Displays active cell’s address or name it has been given
Sheet tabs What sheets of workbook are available
Identifying Excel Window Identifying Excel Window ComponentsComponents
Status Bar Displays information about a selected command
or operation in progress Select All button
used to select all elements of the worksheet Ribbon
Primary replacement for menus and toolbars made up of tabs, groups, and commands
Tab Designed to be task-oriented Each one is made up of several groups to
facilitate viewing all of its functions without opening menus
Active Cell
Formula Bar
Name Box
Sheet Tabs
Status Bar
Select All button
Identifying Excel WindowComponents
Enter Data in a CellEnter Data in a Cell
Create a new workbook and enter data Three types of data you can enter:
Text ─ letters, numbers, symbols, and spaces
Values ─ numbers that represent a quantity, an amount, a date or time
Formulas ─ combination of numbers, cell references, operators, and/or functions
Edit Data in a CellEdit Data in a Cell
Three most common methods to edit data in a cell: Select the cell you want to edit, click in the
Formula Bar, make changes, press Enter Double-click in the cell to be edited, make
the changes, press Enter Select the cell, press the F2 key, make the
changes, press Enter Two options to clear the contents of the cell:
Click on the cell and delete Click on Clear arrow in the Editing group
on the Home tab
Using Save and Save AsUsing Save and Save As
Click the Office button, then select Save or Save As
Use the Save As option if you need to assign a name to the file Provides the Save As dialog box
Once named, use the Save command
Mathematical Mathematical Operations and Operations and
FormulasFormulas
Mathematical operations are the backbone of Excel
Formulas are used to perform mathematical operations and arrive at a calculated result
Must begin with an equals (=) sign Used to automate calculations that were
done manually
Operator Symbols and Operator Symbols and Order PrecedenceOrder Precedence
Operator symbols include: Addition (+), Subtraction (-), Multiplication
(*), and Division (/), Exponentiation (^) Order of Precedence controls the
sequence in which arithmetic operations are performed: Basic rules – anything in parenthesis
performed first; then multiplication and division; then addition and subtraction
Displaying Cell FormulasDisplaying Cell Formulas
Press the Ctrl key plus the tilde (~) key to display formulas in a worksheet
Insert/Delete Rows or Insert/Delete Rows or ColumnsColumns
Due to modifications required in a worksheet, rows and columns may need to be inserted
To insert a new row Click on the row number below where you want the
new row inserted To insert a new column
Click on the column letter to the right of where you want the new column inserted
Click the Insert pull-down arrow on the Cells group on the Home tab
Select Insert Sheet Rows or Insert Sheet Columns
Insert/Delete Individual Insert/Delete Individual CellsCells
May need to insert and delete individual cells instead of entire row or column
Can “shift cells” to the left, right, up or down to insert and/or delete individual cells
Insert/Delete Individual Insert/Delete Individual CellsCells
RangesRanges
A range is a rectangular group of cells in a worksheet Can be one cell; may be entire
worksheet Select a range
Click and hold left mouse button and drag from beginning of range to end
Select first cell, then hold the Shift key while clicking the last cell
Can be contiguous (together) or noncontiguous (not together)
Auto FillAuto Fill
Enables you to copy the content of a cell or a range of cells
Drag the fill handle over an adjacent cell or range of cells
The fill handle is a small black square appearing in the bottom-right corner of a cell
Use to repetitively copy contents of one cell Use to complete a sequence like years or
months
Formula BasicsFormula Basics
Formulas are used to perform mathematical operations and arrive at a calculated result
Must begin with an equals (=) sign Contain mathematical operators Used to automate calculations that were
once done manually
Creating a FormulaCreating a Formula
Rather than typing a cell address, use an alternative method that involves minimal typing
Pointing uses the mouse or arrow keys to select the cell directly when creating a formula
Copy Formulas with Fill Copy Formulas with Fill HandleHandle
Use the fill handle, a small black square in the bottom right corner of a selected cell, to copy formulas
Provides a clear-cut alternative method for copying the contents of a cell
Can be used to duplicate formulas
FunctionsFunctions
A predefined formula that can be selected from a list
Already has the formula information; just requires cell references
Do not replace all formulas Take values, perform operations, and
return results
Functions (continued)Functions (continued)
SUM is the most commonly used function represented by a sigma () Adds values within a specified range
Syntax refers to the grammatical structure of a formula
Must adhere to stated structure of formula Arguments are values ─ used as input and
returned as output Function Wizard automates entering the
function formulas
ChartsCharts
A chart is a graphic or visual representation of data
Multiple chart types can enhance information, adding visual appeal and making it easy to analyze data
Choosing a Chart Type Choosing a Chart Type
Graphic representation of data Attractive, clear way to convey
information Select the type of chart that best
presents your message Add enhancements to better
communicate your information
Choosing a Chart Type Choosing a Chart Type (continued)(continued)
Data point - numeric value that describes a single item on a chart
Data series - group of related data points Category label - describes a group of
data points in a chart
Choosing a Chart TypeChoosing a Chart Type
Which chart would best suit the data shown in the worksheet below?
Questions to AskQuestions to Ask
Percentage of the total revenue by city? Percentage of total revenue by product? Percentage of total revenue each
product produces in each city? Percentage of total revenue each city
produces in each product?
Column ChartsColumn Charts
Used to show actual numbers rather than percentages
Displays data comparisons vertically in columns The X or horizontal axis depicts categorical labels The Y or vertical axis depicts numerical values The plot area contains graphical representation of
values in data series The chart area contains entire chart and all of its
elements
Column ChartsColumn Charts
Column chart displays the revenue of software sales by city
The height of the column reflects revenue of each city Pittsburgh has the highest revenue and Buffalo has
the lowest revenue
Add a 3-D EffectAdd a 3-D Effect
3-D can enhance the display of one set of data
Bar ChartsBar Charts
Column charts with a horizontal orientation
Emphasizes the difference between items
Bar ChartBar Chart
Clustered bar chart shows totals for each software category in a uniquely colored bar
Pie ChartsPie Charts
Effective way to display proportional relationships
The pie denotes the total amount Each slice corresponds to its respective
percentage of the total
Pie ChartPie Chart
3-D Pie Chart3-D Pie Chart
A 3-D pie chart may be misleading
One section may “appear” larger than the others, but may not really be larger
Creating a ChartCreating a Chart
Six main steps to create a chart Specify the data series Select the range of cells to chart Select the chart type Insert the chart and designate the chart
location Choose chart options/add graphics in
charts Change the chart location and size
Six StepsSix Steps
Specify the data series The rows and/or columns that contain the
data you want to chart Select the range to chart
Can be a single cell, but most often is multiple cells
Cells may be adjacent or non-adjacent Use Shift key to select adjacent cells; use
Ctrl key to select non-adjacent cells
Six Steps (continued) Six Steps (continued)
Select the chart type Each type presents data in a different way Pick the type that will best visually
illustrate the information you want to convey
Select a Chart TypeSelect a Chart Type
Chart Type Purpose
Column Compares categories, shows changes over time
Bar Shows comparison between independent variables. Not used for time or dates
Pie Shows percentages of a whole. Exploded pie emphasizes a popular category
Line Shows change in a series over categories or time
Doughnut Compares how two or more series contribute to the whole
Scatter Shows correlation between two sets of values
Stock Shows high low stock prices
Six Steps (continued)Six Steps (continued)
Insert chart and designate location Insert as an embedded object in the
worksheetCan print worksheet and chart on one
page Insert the chart as a New Sheet
Will require you to print the worksheet and chart on separate pages
You can choose the location to display the chart
Six Steps (continued)Six Steps (continued)
Choose chart options using the Design, Layout and Format tabs The Design tab can be used to display
data in rows or columns The Layout tab can be used to change
the display of chart elements The Format tab can be used to apply
special effects
Six Steps (continued)Six Steps (continued)
To change the chart location and size Select the chart to reveal sizing handles Drag the sizing handles to achieve
desired location and size
Questions?Questions?
top related