D100 - Spreadsheet Inputs and Formatting

Select two methods of selecting a range of adjoining cells in Excel.

1. Double-click on the first cell in the range and enter the last cell in the range. 2. Right-click on the first cell in the range and enter the last cell in the range.

In Google Sheets, what are two methods that allow a user to move data from one cell to another cell?

1. Double-click the cell and then drag the data to the desired cell. 2. Click on the cell then choose the move data option from the Data menu.

Range

2 or more cells that are selected to create a source to be used in a formula

Days since last

=DATEDIF(J21,J27,"D")

Month

=MONTH(J21)

Todays Date

=TODAY()

Year

=YEAR(J21)

Years since

=YEARFRAC(J27,J21)

=SUM(A12+A13)

Adding two cells

AVERAGE

Calculates the simple average of a set of numbers

Absolute Cell Reference

Cell reference that remains constant when formula pasted into new cell • Use $ in from of row number to keep absolute (EX: =A3+A$1)

Relative Cell References

Cell references that update automatically when formula is pasted to new cell

Changing Cell Data Type

Click the drop-down menu • General is the default format used Select format you want to use • Other options are available by clicking the arrow at the bottom right of the number ribbon

Series Fill

Completes numeric pattern within range of cells • Type number in field • Select Fill option and Series • Select either Columns or Rows • Enter desired Stop Value

AutoFill

Completes previously typed words when you begin to repeat the typing

COUNTA

Counts the number of cells in a range that are not empty

Ctrl X

Cuts selected text

Formulas

Expressions that calculate value of a cell • Can be simple or complex • Can be reused if same calculation performed in different cell

Wrap Text

Feature that allows entire cell content to appear Displays content on multiple lines within cell • Located in Alignment area

Orientation

Feature that allows you to angle cell contents • Located in Alignment area

Merge Cells

Feature that allows you to combine cells Creates larger cell that span columns or rows • Located in Alignment area

Indent

Feature that allows you to increase or decrease space between left edge of cell and where contents appear in cell • Located in Alignment area

Format Painter

Feature that applies format from one cell to another • Located in Clipboard area Select formatted cell to copy • Click on Format Painter • Select destination cell to apply formatting

AutoSum

Function that totals one or more numbers in a cell range • Select blank cell in the row below cells you want to add together • Click AutoSum or Alt + - Formula will appear in the cell, click Enter to get results

SUM

Function that totals one or more numbers in a cell range • Select blank cell in the row below cells you want to add together • Click fx button and select SUM • Cell ranges will appear and click OK

Ctrl + Home

Highlights A1 cell

- (The minus sign)

In a spreadsheet formula, it means subtract.

Series Autofill

Inserts values into range of cells by completing pattern in previous cell • Type numbers in A1 and A2 • Highlight column range you want Autofilled • Select Fill option and Series • Select Autofill

Format

Making a change in the default settings

Mixed References

Mix of Absolute and Relative Cell references (EX: A1 (Relative)+C$4 (Absolute) )

Ctrl + End

Moves the cursor to last cell used

Standard Deviation

Number that tells you how far other numbers are from the mean • =STDEV(cell:cell)

When adjusting the width of a column or the height of a row, which pointer(s) is/are used?

Placing your pointer between two column or row headings allows you to adjust the width or height.

Standard Deviation Population

Population standard deviation for each category will be displayed (data represents entire population) • =STDEVP(cell:cell)

Sort

Putting data in order

: (the colon)

Range operator: Means inclusive; including all the cells. For Example; B3:B37 means b3,b4, b5,...to b37

Referencing a Range of Cells

Referring to more that one cell at a time • Cells must be in continuous block • Specify upper-left cell in range, followed by : and lower right cell in block (EX: A2:C2)

TODAY

Returns current date

NOW

Returns current date and time (formatted as a date and time)

DAY

Returns day portion of date (Number between 1 - 31)

HOUR

Returns hour portion of time (Number between 0 - 23)

MAX

Returns largest value in a set of numbers

MINUTE

Returns minute portion of time (Number between 0 - 59)

MONTH

Returns month portion of date (Number between 1 - 12)

COUNT

Returns number of cells in a range that contains numbers

SECOND

Returns second portion of time (Number between 0 - 59)

MIN

Returns smallest value in a set of numbers

YEAR

Returns year portion of date (Number between 1900 - 9999)

Filter

Sorting for items by specific criteria

Which pointer, called the fill handle, allows a user to copy a cell's data to an adjoining cell or cells?

The fill handle is located in the lower right corner of the cell in most spreadsheet programs.

Calculations

Using a cell equation to automatically calculate • Start with = and select cells you want to calculate (EX: =A1+A5)

Referencing a Cell

Using values stored in another cell of a worksheet

/ (the forward slash)

in a formula in a spreadsheet it means Divide

* (the asterisk) (Shift the number 8 key)

in a formula it means multiply

+ (the plus sign)

in a spreadsheet formula it means add

=SUM(__ __)

the basic formula for most computations

Average

the sum of all numbers divided by the amount of numbers =Average(A15:A37)

Fill Feature

• Automatically populates cells in a worksheet • Located in Editing group on Home tab • Fill Up, Down, Left, Right automatically copies contents of one cell to other contiguous cells

Add Chart Element

• Axes • Axes Titles • Chart Title • Data Labels • Gridlines • Legend

A1

• Both column and row references are relative • Changes when reference is copied and pasted to other cells

$A$1

• Column and row references are absolute • Remains constant when reference is copied and pasted to other cells

$A1

• Column reference is absolute • Remains constant when copied and pasted to other cells • Row reference is relative • Changes when copied and pasted to cells in other rows

A$1

• Column reference is relative • Changes when copied and pasted to other cells • Row reference is absolute • Remains constant when copied and pasted to cells in other rows

Freezing Panes

• Locks rows and columns on large worksheet so they do not disappear while scrolling through data table • Highlight cell, column, or row below and directly to the right of area you want to view • Click View in the ribbon • Select Freeze Panes • Make selection of what you want to freeze

Constructing a Pivot Table

• Prepare source data • First row should contain labels describing each column • Source data should be free of subtotals • Data should be consistent

Ctrl Z

• Undo last action • Redo last discarded action