Computer Science Exam Review - Excel
Session 1.1 – The Excel Window
Workbook – Where Excel stores its spreadsheets in files
Name Box – Displays the cell reference of the active cell
Select All button – Used to select all of the cells in the active worksheet
Row Headings – The numbers along the left side of the worksheet window that identify the
different rows in the worksheet. You click a row heading to select the entire
Sheet tab scrolling buttons – Scroll the list of sheet tabs in the worksheet
Active Sheet – the sheet currently displayed in the workbook window and its tab is white
Formula bar – displays the value or formula entered in the active cell
Sheets – Excel is made up of individual sheets. Each sheet is identified by a name, which
appears in the sheet tab
Cell – The intersection of a row and a column
Active Cell – The cell currently selected in the active worksheet is the active cell. The active
cell is outlined with a thick border and the corresponding row and column
headings are highlighted.
Column Headings – The letters along the top of the worksheet window that identify the
different columns in the worksheet. You click a letter to select the entire
Worksheet – displays the contents of the spreadsheet laid out in a grid of rows and columns
Spreadsheet – A collection of text and numbers laid out in a rectangular grid.
Cell Reference – Indicates a cells column and row location
Text Data – Any combination of letters, numbers, and symbols that form words and
Number Data – Any numerical value that can be used in a mathematical calculation
Truncated – when text is hidden because it is too large to fit in the cell to be displayed
AutoComplete – When Excel tries to complete what you’re typing by displaying possible
words/numbers from previously recorded entries
Pixel – A single point on a computer monitor or printout. A column width of 8.43 characters
equals 64 pixels.
Point – Is approximately 1/72 of an inch. The default row height is 15 points or 20 pixels.
Autofitting – Eliminates empty space by matching the row height or column width to its
tallest or longest cell entry.
Clearing – Clearing data from a worksheet removes the data, leaving blank cells where the
data had been.
Deleting – Deleting data from the worksheet removes both the data and the cells.
Navigation Between Worksheets
- A workbook can have two kids of sheets: o Worksheets – contains a grid of rows and columns into which the user enters
data values and formulas
o Chart sheets – contains Excel chart that provides visual representation of
o Charts can also be embedded within worksheets, displaying both the data
and charts in one sheet
Navigating Within a Worksheet
- Each cell within a worksheet is identified by a cell reference
Entering Text, Numbers, and Dates
- Text data is often referred to a text string, because it contains a string of text
- Date and time data are commonly recognized format for date and time values.
- By default, text is left-aligned in cells and numbers, dates, and times are right-
- If the adjacent cells contain text or data, only the text that fits into the cell is
displayed. The rest of the text entry is hidden, or truncated, from the view.
- Excel tries to anticipate the remaining characters by displaying text that begins with
the same letters as a previous entry in the same column. This feature, known as
AutoComplete, helps make entering repetitive text easier. To accept the suggested
text, press the Tab or Enter key. To override the suggested text, continue to type the
text you want to enter in the cell.
International Date Formats
- Many international businesses adopt ISO (International Organization for
Standardization) dates in the format yyyy-mm-dd
Deleting and Clearing a Row or Column
- When a cell is deleted, the remaining cells in the worksheet shift into the deleted
- Note: pressing the Delete key does not DELETE the selected row or column; instead,
it CLEARS the contents, leaving the row or column in place Session 1.2 – Worksheet Data
Spelling checker – verifies the words in the active worksheet against the program’s
Cell Range/Range – a group of cells
Formula – an expression that returns a value
Nonadjacent range – is two or more distinct adjacent ranges
Adjacent range – is a group of cells in a single rectangular block of cells
View buttons – change how the worksheet content is displayed
Sum button – (also referred to as the AutoSum feature) inserts Excel functions to sum,
average, or count all the values in a column or row, as well as display the minimum
or maximum value in a column or row.
Range reference – Indicates the location and size of a cell range
Function – A named operation that returns a value. Functions are used to simplify formulas,
reducing what might be a long formula into a compact statement.
Normal View – Shows the contents of the worksheet
Page Layout View – Shows how the worksheet will appear when printed
Page Break Preview – Displays the location of the different breaks within the worksheet
Portrait Orientation – The page is TALLER than it is WIDER
Landscape Orientation – The page is WIDER than it is TALLER
Formula View – a view of the worksheet contents that displays formulas instead of the
Scaling – A printout reduces the width and the height of the printout to fit the number of
pages you specify by shrinking the text size as needed.
Moving and Copying Range
- One way to move a cell or range is to select it, position the pointer over the bottom
border of the selection, and then drag the selection to a new location. This technique
is called drag and drop because you are dragging the range and dropping it in a new
- If too complex, its often more efficient to cut and past the cell contents. Cutting
places the cell contents into computer memory or on the Clipboard. The contents
can then be pasted from the Clipboard into a new location in the worksheet.
Entering a Formula
- Every Excel formula begins with an equal sign (=) followed by an expression
describing the operation that returns the value.
- Note: if you dot begin the formula with the equal sign, Excel assumes that you are
entering text and will not treat the cell contents as a formula.
- A formula is written using operators that combine different values, resulting in a
single value that is then displayed within the cell.
- The most commonly used operators are arithmetic operators that perform
addition, subtraction, multiplication, division and exponentiation. - The order of precedence is a set of predefined rules used to determine the
sequence in which operators are applies in a calculation. Remember, BEDMAS?
Copying and Pasting Formulas
- With formulas, Excel adjusts the formula’s cell references to reflect the new location
of the formula in the worksheet.
Entering Functions with the AutoSum Feature
- The AutoSum feature includes buttons to quickly insert the SUM, AVERAGE, COUNT,
MIN and MAX functions to generate the following:
o Sum of the values in the column or row
o Average value in column or row
o Total count of numeric values in the column or row
o Min value in the column or row
o Max value in the column or row
Editing Worksheet Content
- Sometimes you need to edit only a portion of an entry rather than change the entire
content of a cell, especially if the cell contains a large block of text or a complex
formula. To edit the cell contents, you can work in Edit mode.
Using Find and Replace
- You can use the Find command to locate a string of characters known as a search
string and replace the text with a replacement string of new text characters. Session 5.1 – An Excel Table
Field – each column represents a field, which describes some characteristics of a person,
place or thing such as a last name, address, city or state
Header Row – The first row of the range that contains field names
Record – each row represents a record, which is a collection of related fields that are
Sort – rearranging the records in a table or range based on one or more fields
Field name/Column Header – is a unique label that describes the contents of the data in a
Excel Table – is a range of related data that is managed independently from data in other
rows and columns in the worksheet
Sort Field – the fields that you use to order data
Structured Range of Data – commonly referred to as a list or table
Data Definition Table – documentation that lists the fields to be maintained for each record
(in this case, each art object) and a description of the information each field will
Freezing – lets you keep headings visible as you work with the data in a large worksheet. As
you scroll the data, the first row remains on the screen so the column headers are
visible, making it easier to identify the data in each record.
Ascending Order – Arranges text alphabetically from A to Z, numbers from smallest to
largest, and dates from oldest to newest. BLANK FIELDS ARE PLACED AT THE END
OF THE TABLE.
Descending Order – Arranges text alphabetically from Z to A, numbers from largest to
smallest and dates from newest to oldest. BLANK FIELDS ARE PLACED AT THE END
OF THE TABLE.
Primary Sort Field – the first sort field
Secondary Sort Field – the second sort field… And so on
Custom List – indicates the sequence in which you want data ordered. Excel provides four
predefined custom sort lists.
Renaming an Excel Table
- Table names must start with a letter or an underscore and can use any combination
of letters, numbers and underscores for the rest of the name. Table names CANNOT
include spaces. Session 5.2 – Filtering Table Data
Total Row – Used to calculate summary statistics (including sum. Average, count,
maximum, and minimum) for any column in an Excel table
Split bar – Indicates the division between the panes. You can drag the split bar to resize the
Flitering – The process of displaying a subset of rows in an Excel table that meets the
criteria you specify
Pane – Each area of a worksheet
Criteria filters – Enable you to specify various conditions in addition to those that are based
on an “equal” criterion
- When you want to redisplay all of the data in a filtered table, you need to clear (or
remove) the filters. When you clear a filter from a column, any other filters are still
- The Subtotal command offers many kinds of summary information, including
counts, sums, averages, minimum and maximums. The Subtotal command inserts a
subtotal row into the range for each group of data and adds a grand total row below
the last row of data.
- The Subtotal command cannot be used in an Excel table, so you must first convert
the Excel table to a range.
Using the Subtotal Outline View
- The Subtotal feature “outlines” your worksheet so you can control the level of
details that is displayed. The three Outline buttons at the top of detail in the
worksheet. By default, the highest level is active; in this case, Level 3.
- Level 3 displays the most detail – the individual art object records, the
subtotals and the grand total.
- Level 2 displays subtotals and the grand total, but not the individual records.
- Level 1 displays only the grand total Session 5.3 – PivotTable and PivotChart
PivotTable – An interactive table used to group and summarize either a range of data or an
Excel table into concise, tabular format for easier reporting and analysis
Values field – The fields that contain summary data in a PivotTable, this PivotTable uses the
total of the Appraised Value as the values field.
PivotChart – A graphical representation of the data in a PivotTable
Slicer – An object used to filter a PivotTable or PivotChart. You can create more than one
slicer for a PivotTable or PivotChart
Category Fields – are the fields that group the values in a PivotTable. Category fields appear
in PivotTables as row labels, column labels and report filters
Analyzing Data with PivotTables
- A PivotTable summarizes fata into categories using functions such as COUNT, SUM,
AVERAGE, MAX and MIN
- In PivotTable terminology, the fields that contain values in a PivotTable, such as
condition, location, and year acquired. Category fields appear in PivotTables as row
labels, column labels, and report filters, which allows you to focus on a subset of the
PivotTable by displaying one, several or all items.
Summarizing Data with a PivotTable
- The PivotTable omits all the detailed data, enabling readers to focus on the bugger
picture. This makes it easier for readers to understand the results and gain insights
about the topic.
Creating a PivotTable
- When you create a PivotTable you need to specify where to find the data for the
- The PivotTable Field List is divided into two sections. The upper field list section
displays the names of each field in the [ArtObjects] table. You check a field check box
to add that field to the PivotTable.
- The lower layout section includes boxes for the four areas in which you can place
fields: Report Filter, Row Labels, Column Labels, and Values.
Figure 5-32; Layout areas for a PivotTable
- Row labels – The fields you want to display as the rows in the PivotTable. One row
in display for each unique item in this area. You can have nested row fields.
- Column Labels – The fields you want to display as columns at the top of the
PivotTable. One column is displayed for each unique item in this area. You can have
nested column fields.
- Report Filter – A field used to filter the report by selecting one or more items,
enabling you to display a subset of data in a PivotTable report.
- Values – The fields you want to summarize Changing the PivotTable Report Layout Options
- The Compact report layout places all fields from the row area in a single column and
indents the items from each field below the other fields. This is the default layout;
each field in the row area takes a column in the PivotTable. By default, the outline
form shows the subtotals for each group at the top of every group. The Tabular
report layout displays one column for each field and leaves space for column
headers. A total for each group appears at the bottom of each group. You can find
these report layout options on the PivotTable Tools Design tab in the Layout group.
Adding a Report Filter to a PivotTable
- A report filter is used to filter the PivotTable to display summarized data for one or
more field items or all field items in the Report Filter area.
Sorting PivotTable Fields
- You can sort a PivotTable field either by its own items or by the values in the body of
the PivotTable. To sort a PivotTable field, you can use any of the Sort buttons on the
Options tab to sort the Information in a PivotTable report.
Creating Different Types of PivotTable Reports
- Most PivotTable summaries are based on numeric data, but PivotTables can also
contain only nonnumeric data. You cannot add nonnumeric data, so you must use
the COUNT function to produce summaries.
- You can use PivotTables to combine items into groups. Items that appear as row
labels or column labels can be grouped. If items are numbers or dates, they can be
grouped automatically using the Grouping dialog box, or they can be grouped
manually using the Ctrl key to select items in a group