Spreadsheets Flashcards
electronic spreadsheet
a computer file created by spreadsheet software which allows users to input and organize data, create charts and perform data analysis
Name two popular spreadsheet software
Microsoft Excel and Mac Numbers
usage of electronic spreadsheet
to produce accounting reports, financial models, inventory reports, quotation and personal mail lists
worksheet
contains cells to input data
workbook
a spreadsheet file that contains one or more worksheets to organize data
data on different worksheets can be _____
interdependent
row number
1 to 1,048,576
column letter
A to XFD
cell
The intersection of a row and a column
modify data in an active cell
Double-click the cell and then change the cell content in the cell
different data formats in cells
- General
- Number
- Currency
- Percentage
- Date
- Time
wrap text
merging and formatting cells so that the text does not overflow to the right but wraps around the cell
When you enter too much text in a cell
if it is left-aligned it will overflow to the right on a single row
Autofill
allows users to create a series of values based on the value of one or more selected cells adjacent to each other
usage of autofill
to create number sequences, time lists, day lists, week lists, month lists and regular text patterns
how to function autofill
- Use the fill handle (A tiny solid square located at the bottom right corner of a cell).
- Move the cursor above the fill handle and it will change to a cross +
- Drag it downwards to fill the cells below.
Sorting
process of reordering the records according to a specific criterion
how can sorting be performed in an Excel spreadsheet
by either simple sort list or sorting with multiple criteria
Filtering
a function to select and display the records meeting the criteria specified by a user, leaving the rest of the records hidden
Conditional formatting
can change the appearance of cells on the basis of conditions
that you specify
chart
an important tool for data analysis and presentation
chart usage
to visualize the relationship between data
different chart types
- Column chart
- bar chart
- Line chart
- Pie chart
- Scatter chart
- Radar chart
types of cell references
relative cell reference and absolute cell reference
relative cell reference example
=A1+A2
format of cell reference linking to cells in other worksheet
=’7B grades’ ! A1
format of MAX function (example)
=MAX(A1:A4)
format of MIN function (example)
=MIN(A1:A4)
format of SUM function (example)
=SUM(A1:A4)
format of AVERAGE function (example)
=AVERAGE(A1:A5)
format of ROUND function (example)
=ROUND(A1,x)
(x = round off to x decimal places)
(round off to integar if x=0)
format of COUNT function (example)
=COUNT(A1:A4)
COUNT function
counts the number of cells that contain numbers (including dates and formula) as within the specified range
format of COUNTIF function (example)
=COUNTIF(C1:C10, “=Apples”)
more than or equal to symbol in COUNTIF function
> =
more than or equal to symbol in COUNTIF functions
> =
not equal to symbol in COUNTIF function
<>
format of IF function (example & meaning)
=IF(A1>=50,”pass”,”fail”)
Display “Pass” if the score is higher than or equal to 50. Otherwise display “Fail”.
VALUE!
shown when there is something wrong with the value
DIV/0!
shown when a number is divided by 0, which is not defined mathematically
####
shown when cell is not wide enough to show the data (numeric data)
NAME?
shown when function is incomplete or contains spelling mistakes
how does absolute cell reference work
When a formula is copied or moved to other cells, the absolute cell reference in the formula remains unchanged.
absolute cell reference examples
$A$5
The relative cell reference in the target cell is ________
changed accordingly
