Stage 4 Flashcards
Microsoft Excel
It is used to collect and organize data in rows and columns that contain the spreadsheets, the way in which they are shown tables, graphs, and databases.
Main elements of the Excel screen
File menu: Backstage view
Quick access toolbar: The most used command buttons and above or below the ribbon
Ribbon: Start, Insert, Layout, Formulas, Data, Review, View
Spreadsheet: Divided in cells columns (16384) and rows (1048576). Cell references is the name of each cell A1
Name box: It is observed mainly the Cell Reference where the cursor is located.
Formula bar: It is where it is observed the content of the cell.
Active cell: Selected cell that has the filling control
Label sheets: They are used to identify the sheets nad to add more.
Navigation buttons: They are used to move different sheets.
Status bar: Operations performed in the spreadsheet, view buttons and zoom button
Files of Microsoft Excel
Book1, Sheet 1
Visualize many opened books
View tab
Group window, switch windows
Click on the file
Insert sheet
Home tab
Group cells, button insert
Insert sheet
Delete sheet
Home tab
Group cells, button delete
Delete sheet
Rename sheet
Home tab
Group cells, button format
Organize sheets, rename sheet
Numerical data
Numbers, dates and hours
Numbers
Whole
Positive and negative
Comma style
Indicators of the number $ or %
Alphanumerical data
All texts, combination of letters and numbers, symbols and space (String)
Formulas with cell reference
Relative reference: Relative position of the cells
Absolute reference: Refers to the position of the cell that remains fixed when being copied or moved. $
Mixed reference: Relative position and absolute.
External reference: Cells in other spreadsheets or wordbook.
Formulas with cell reference
Formulas that use the name of the cell references to perform the operations with their content.
Functions
Predefined formulas that Excel has to perform operations with the numerial and alphanumerical data
Syntax
= Name of the function (Arguments)
Insert function
Click on the cell Click on the formula tab Select the button insert function Choose the category Select a function Write the arguments Click on OK
Examples of functions
SUM Sums the content of a range of cells
AVERAGE Calculates the arithmetic means of the arguments
MAX Finds the maximum value
MIN Finds the minimum value of a range of cells
NOW () Shows the current date and time
IF Evaluates if a condition is fulfilled and returns a value if it is ture and other if it is false
COUNT Counts the numer of cells that contains numerical data.
COUNTIF Counts the number of cells that contain
numerical data and fullfill with a condition
VLOOKUP Finds a numerical or alphanumerical value in a table and returns the value that is indicated.
Data selection (Range of cells)
The cursor is placed over a cell and the mouse pointer is dragged to the last cell.
Give a click on a cell, SHIFT key and click on the last cell.
It is selected a range of cells and press Ctrl key and select other range of cells
Data selection (Rows / Columns)
Click on the header of the row/column and drag the mouse pointer
A range of rows /columns is selected and press Ctrl key and select other rows or columns.
Undo button
It is used to correct errors.
Redo button
It is used to restore the action performed with the Undo button.
Groups in the home tab
Cells Font Alignment Number Clipboard Editing
Group Cells
Insert (Rows, cells, columns)
Delete
Format (Height o rows and with of the columns)
Group Font
Type of font (Calibri) Font size (11) Font style (Bold, italic, underline) Borders (Borders of the cells) Fill color (Red) Font color (Yellow)
Group Alignment
Vertical (Upper, middle, inferior)
Horizontal (Left, center, right)
Merge and center (Combine)
Group number
Increase decimals (4.12322) Decrease decilmals (4.12) Percentage (%) Accounting number ($)
Group clipboard
Copy
Cut (Move)
Paste
Group editing
Sort (Order numerical and alphanumerical data)
Fill (Capture data that have repetitive sequence or in a determined format)
Clear (Modify or delete the content of the cells)
Page layout tab - Page setup group
Page: Orientation, size and scaling
Margins: Top, bottom, right, left, header and footer, center on page, print
Sheet: Establish the area desired to print
Preview
Permits to confirm that is fulfilled all the characteristics of the design.
Review tab Comments group
A comment is used to place a note in a cell to document its content.
Cell-Review-New comment
Charts
They are used to visualize in a clearest way the data contained in a spreadsheet
Types of charts
Column: Comparatives of the series of data in vertical form.
Line: More than one series of data and shows tendencies.
Pie: Proportions of the graphed parts
Bar: Comparatives of the series of data in horizontal form
Components of chart
Chart area: Area that surrounds all the components of the chart.
Plot area: Area that contains only the used kind of chart.
Chart title
Horizontal Axis Title (Category)
Vertical Axis Title (Value)
Horizontal Axis
Vertical Axis
Series: Columns of data that are being graphed.
Legend: Labels that define the name of the series that are being graphed.
Division lines: Horizontal lines that help to visualize the values in the Y axis
Data labels: They are the data that represent the columns of data to graph.
Freeze / Unfreeze
View tab, window group, Freeze panes, select the top row / first column / freeze panes
Databases concepts
Row header: First row, name of the fields
Record: Each one of the rows that conform the table
Field: Each one of the columns that forms the table.
Filters (Data tab, Sort&Filter group)
Numerical data -Font color -Exact value -From a value -Range of values Alphanumerical data -An exact text -That begins with and finishes with - A range of texts
