Skip to content
Lesson Objectives:○ This lesson introduces you to how to use a spreadsheet application to create simple reports such as budgets, or cash flows.○ Upon completion of this lesson, you should be able to:
■ Info■ New■ Open■ Save■ Save As■ Print■ Share■ Export■ Publish■ Close■ Account■ Options ■ Name Box■ Insert Function■ Formula Bar■ Select All■ Active Cell■ Column Headings■ Row Headings■ Tab Scrolling■ Sheet Tab■ Scroll Bars
○ You can move around the cells of a worksheet using the following methods:■ Scroll Bars - Click the arrow buttons at either end of a scroll bar to move one row or column at a time. Click and drag the scroll box to display another location in the worksheet.■ LEFT, RIGHT, UP, or DOWN - Press a direction key to move one cell at a time.■ HOME - Moves to column A in the current row.■ CTRL+HOME - Moves to cell A1.■ CTRL+END - Moves to the last cell with data in your report.■ CTRL+G or F5 - Displays the Go To dialog box which you can use to move quickly to a cell address.V. Managing Workbooks○ You can also press CTRL+N to create a new blank workbook, or a new blank document type in any Office application.○ You can also press CTRL+O to display the Open dialog box.○ You can also press CTRL+W or CTRL+F4 to close the workbook.VI. Saving Workbooks○ You can also click (Save) on the Quick Access toolbar, or press CTRL+S to quickly save the file. This is common to all Office applications.○ The default file type given to an Excel file is .xlsx, although you can save it in different file formats using the options in the Save as type field (the field that displays below the file name field).VII. Saving Workbooks - Using Excel and Other Spreadsheet File Types○ Excel also supports the more generic file types associated with spreadsheet data. These types include:■ Comma Separated Values (.csv) - these are text files wherein the data is laid out in a columnar format, and the contents of each column are separated by a Comma.■ Tab delimited files (.tsv) - these are text files wherein the data is laid out in a columnar format, and the contents of each column are separated by a tab character.
○ A range can be a single cell, several cells, or the entire worksheet.○ The active cell in the selected range appears in normal color.
You can select individual cells or any range of cells as follows:■ Single cell - Click the cell.■ Extend the selection - Click the first cell and drag to the end of the required range; or click the first cell, hold the SHIFT key, and click the end cell in the range.■ Entire row - Click the row header when you see the number row.■ Entire column - Click the column header when you see the letter column.■ Entire worksheet - Click the Select All button.■ Non-adjacent cells, columns, or rows - Click the cell, column, or row, hold the CTRL key, then click to select the next cell, column, or row.■ Multiple rows - Click the first row number and drag for the number of rows to select.■ Multiple columns - Click the first column letter and drag for the number of columns to select.IX. Copying and Moving Data○ You can press CTRL+C for copy, CTRL+X for cut and CTRL+V for paste in any Office application.○ Paste All - Paste all items in the same order they were collected into the current location on the worksheet.○ Clear All - Clear the Office Clipboard of all entries.
○ You can adjust column widths to display more characters. When a text entry in a cell is longer than the width of the column, Excel displays the text by overflowing the entry into the cells to the right, if they are empty. If the cell to the right contains information, the text is truncated or "cut off" at the column boundary.○ To change the width of a column, on the Home tab, in the Cells group, click Format, and then Column Width.○ You can also point the mouse pointer on the line at the right edge of the column header to be adjusted, and when you see <0xE2><0x86><0x94><0xE2><0x86><0x96>, click and drag to the required width for the column.XI. Changing the Column Widths (Continued)○ Click the row heading or column heading where you want to insert a new row or column. Then press CTRL++ on the Numeric Keypad.○ Click the row heading or column heading where you want to delete. Then press CTRL+- on the Numeric Keypad.XII. Using Common Built-In Functions○ Excel provides over 300 built-in functions for mathematical and data operations.○ Functions accept numbers, values, and cell references as arguments within parentheses, following this format:■ =FUNCTION(numbers or values or cell references)○ Some common functions you will use include:■ =SUM - Calculates the sum of the values in the range of specified cells.■ =AVERAGE - Calculates an average of the values in the specified cells (totals the range and divides the total by the number of entries).■ =MIN - Displays the minimum value in the range of specified cells.■ =MAX - Displays the maximum value in the range of specified cells.■ =COUNT - Counts the number of values within the specified range.○ Cell ranges in a function should be indicated as follows:■ =SUM(A1:A10) - Adds the values in cells A1 through A10.XIII. What Does Formatting Mean?○ Formatting refers to changing the appearance of data to draw attention to parts of the worksheet, or to make the data easier to read.○ You can format a cell or range of cells at any time, either before or after you enter the data.○ A cell remains formatted until you clear the format or reformat the cell.○ When you enter new data in the cell, Excel will display it in the existing format.○ When you copy or fill a cell, you copy its format along with the cell contents.○ You can also press CTRL+1 to display the Format Cells dialog box for further options on formatting various elements of the worksheet.XIV. Formatting Numbers and Decimal Digits○ To format selected cells that contain values, on the Home tab, in the Number group:■ Click the arrow for Number Format, and click the format required, or click one of the commonly-used number format buttons in the Number group of the Home tab.○ Use Merge & Center to center a text label across several cells.○ To split cells that were merged using Merge & Center, click the arrow on the Merge & Center button, then click Unmerge Cells.XV. Changing Fonts and Sizes○ A font is a typeface or text style. Changing fonts alters the way text and numbers appear.○ Keep the number of fonts in a worksheet to one or two, as the appearance of too many font styles can be distracting.XVI. Applying Cell Border○ Borders separate groups of data to improve legibility, especially when the worksheet contains a large volume of numbers. This feature enables you to draw lines around any or all edges of a cell or range of cells.○ To apply borders, on the Home tab, in the Font group, click the Borders button and select a border style.○ You can access more border options in the Format Cells dialog box (CTRL+1).XVII. Working with Charts○ A chart is a pictorial representation of data in a worksheet. A chart can be a more descriptive way of representing your data, as it can clearly illustrate trends or patterns in the data.○ Vertical Axis/Series○ Horizontal Axis/Series○ Chart Title○ LegendXVIII. Working with Charts - Selecting Chart Types○ Excel provides a variety of chart types and several subtypes within each major type:■ Column■ Line■ Pie■ Bar■ Area■ XY (Scatter)■ Stock■ Surface■ RadarXIX. Working with Charts - Chart Type Descriptions○ Column - Compares values over time or categories in a vertical presentation.○ Line - Compares continuous trends.○ Pie - Compares series that make up a whole.○ Bar - Compares values over time or categories in a horizontal presentation.○ Area - Compares a continuous change in volume.○ XY (Scatter) - Determines data patterns.○ Stock - Displays high-low-close data; requires at least three sets of data.○ Surface - Displays trends in values with a three-dimensional presentation and a continuous surface.○ Radar - Determines patterns or trends with points matched up by lines.XX. Working with Charts - Changing the Chart Layout○ Use commands on the Chart Tools ribbon to manipulate items on the chart.■ Axes - Include labels on the horizontal and vertical axes.■ Axis Titles - Add titles to the horizontal and vertical axes; you can also customize items for the chart, such as the units used in the vertical axis.■ Chart Title - Add a title for the chart.■ Data Labels - Include data labels on the chart.■ Data Table - Display the chart data beneath the chart.■ Error Bars - Displays margins of error and standard deviations on the chart.■ Gridlines - Include gridlines on the chart.■ Legend - Include a legend and position it in relation to the chart.XXI. Sorting Data○ One advantage of using Excel to store data is the option to sort and organize the data based on the values in the selected columns or rows.○ On the Home tab, in the Editing group, click Sort & Filter.■ Sort A to Z - Sort items in ascending order (for example, A to Z or 0 to 9).■ Sort Z to A - Sort items in descending order (for example, Z to A or 9 to 0).■ Custom Sort - Set up to 64 levels or priority on what items to sort by order (for example, Last Name, then by First Name, then by Purchases, then by Product, and so on).XXII. Filtering Information○ The quickest and easiest way to filter data in Excel is to use the AutoFilter tool. When you activate this tool, Excel places AutoFilter icons on the right side of each column or field name. Use these icons to select the conditions for the records you want displayed.○ The AutoFilter can find rows where a cell is equal to a specific value or set of values.○ To activate the Filter command, on the Data tab, in the Sort & Filter group, click Filter.XXIII. Understanding Excel Databases○ Databases in Excel are really lists that adhere to a special set of requirements:■ The first row of the database must contain field names (titles) at the top of each column.■ Each column in the database must contain the same category of data in every row in the column.■ Each row (record) in the database contains all of the fields of data.■ The row containing the field names must be formatted differently from the rest of the list (for example, they should appear in bold, or italics).
MID L10
Lesson 10: Using Microsoft Excel - Detailed Notes
I. Looking at the Excel Screen
Name
Column 2
Column 3
Notes
As with any Office application, when you first start the program, you see the Backstage view:
Open
○ The Microsoft Excel is a spreadsheet lets you create, view, edit, and share your files with others quickly and easily. Manage charts, data analyses and more while you view and edit workbooks attached to email messages.
Open
○ Name Box
- Look here for the address of the active cell. For example, if the Name Box displays A21, this indicates the active cell is where column A and row 21 intersect.
Open
Insert Function
Open a dialog box that will help you choose and insert a built-in function.
Open
Formula Bar
View the contents of the active cell. Under certain circumstances, you can use this bar to create or edit entries in the worksheet.
Open
Select All
Click to select all the cells in the worksheet.
Open
Active Cell
Indicates the current cell with the thick border around the cell.
Open
Column Headings
The sequential letters at the top of each column enable you to identify columns.
Open
Row Headings
The sequential numbers on the left side of each row enable you to identify rows.
Open
Tab Scrolling
Use these arrows to navigate between the worksheets in the workbook.
Open
Sheet Tab
The name of the current or active worksheet onscreen.
Open
Scroll Bar
Use these to move horizontally or vertically within the current or active worksheet.
Open
There are no rows in this table
II. Understanding Basic Terminology
Concept
Description
Workbook
An Excel file is called a workbook and a workbook contains one or more individual worksheets.
Worksheet
A worksheet contains rows numbered from 1 to 1,048,576, and columns assigned with letters or letter combinations from A to Z, then AA to ZZ, then AAA to AZZ, and so on up to XFD.
Cell
A cell is the intersection of a row and a column and can contain one single value (text or number), or formula.
Cell Address
Each cell has its own address, or a reference you can use to find it within a worksheet.
Active Cell
The active cell is the cell currently displayed with a thick border, such as cell A1 in the previous picture.
There are no rows in this table
III. Entering Data in the Worksheet
Concept
Description
Text
Types of Data
You can insert three types of data into worksheet cells:
1.Labels
2. Values
3. Formula
Labels
Text entries appear in the cells exactly as you enter them; default to left align.
Values
Numeric values; default to right aligned.
Formulas
Composed of cell references, arithmetic operators, and functions (calculation commands) that perform operations on data.
Numbers
Numbers are constant values such as dollars and percentages; by default, they align to the right side of a cell. If you enter characters other than numbers, Excel treats the entire entry as a label.
There are no rows in this table
IV. Moving Around the Worksheet
VIII. Manipulating the Contents
X. Changing the Column Widths
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.