Excel Module 1 CISC
worksheet title
Worksheet titles and subtitles should be as brief and meaningful as possible. A worksheet title could include the name of the organization, department, or a description of the content of the worksheet. A worksheet subtitle, if included, could include a more detailed description of the content of the worksheet. Examples of worksheet titles are January 2021 Payroll and Year 2021 Projected Budget, and examples of subtitles are Finance Department and Monthly Projections, respectively.
row and columns
data in a worksheet is identified by row and column titles so that the meaning of each entry is clear. Rows typically contain information such as categories of data. Columns typically describe how data is grouped in the worksheet, such as by month or by department. - Data entered in columns should be identified using column titles to identify what the column contains.
screentip
is a label that appears when you point to a button or object, which may include the name, purpose, or keyboard shortcut for the object. It may also include a link to associated Help topics, if any.
folder
is an electronic container that helps you organize your computer files, like a cardboard folder on your desk; it can contain subfolders for organizing files into smaller groups.
tool tabs/contextual tabs
tabs that appear in addition to the main tabs on the ribbon when you perform certain tasks or work with objects, such as pictures or tables.
worksheet titles
worksheet title, Frangold Real Estate Budget, identifies the purpose of the worksheet. The worksheet subtitle, Monthly Estimates, identifies the type of data contained in the worksheet. Why? A title and subtitle help the reader to understand clearly what the worksheet contains.
clearing cell or range
- As you are clearing cell entries, always remember that you should never press the SPACEBAR to clear a cell. Pressing the spacebar enters a blank character. A blank character is interpreted by Excel as text and is different from an empty cell, even though the cell may appear empty.
Command
- Commands are one of the principal ways you communicate with an app so you can tell it what you want it to do.
What is Excel?
- Excel is a powerful spreadsheet app that allows users to organize data, complete calculations, make decisions, graph data, develop professional-looking reports, publish organized data to the web, and access real-time data from websites
Tables
- Tables organize and store data within worksheets. For example, once a user enters data into a worksheet, an Excel table can sort the data, search for specific data, and select data that satisfies defined criteria.
formulas
- The equal sign (=) preceding b6-b17 in the formula alerts Excel that you are entering a formula or function and not text. - ex: Net income (row 19) = Total income (row 6) − Total Expenses (row 17) use formula in b19 =b6-b19
Gridlines
- The evenly spaced horizontal and/or vertical lines used in a worksheet or chart are called gridlines. Gridlines make a worksheet easier to read. If desired, you can turn the gridlines off so that they do not show on the worksheet. While learning Excel, gridlines help you to understand the structure of the worksheet.
Cell
- The intersection of each column and row is a cell. A cell is the box, formed by the intersection of a column and a row, where you enter data. Each worksheet in a workbook has 16,384 columns and 1,048,576 rows for a total of 17,179,869,184 cells. Only a small fraction of the active worksheet appears on the screen at one time. - A cell is referred to by its unique address, or cell reference, which is the column letter and row number location that identifies a cell within a worksheet, such as A1. - To identify a cell, specify the column letter first, followed by the row number. - One cell on the worksheet, designated the active cell, is the worksheet cell into which you are entering data.
Why is it important to plan a worksheet?
- The key to developing a useful worksheet is careful planning - What steps to follow: - Define the problem, including need, source of data, calculations, charting, and web or special requirements. - Design the worksheet. - Enter the data and formulas. - Test the worksheet.
Worksheet
- The worksheet is organized into a rectangular grid containing vertical columns and horizontal rows. A column letter in a box above the grid, also called the column heading, appears above each worksheet column to identify it. A row number in a box on the left side of a worksheet row, also called the row heading, identifies each row.
Web Support
- Web support allows users to save Excel worksheets or parts of a worksheet in a format that a user can view in a browser, so that a user can view and manipulate the worksheet using a browser. Excel web support also provides access to real-time data, such as stock quotes, using web queries.
document properties
- which are the details about a file such as the project author, title, and subject. For example, you could use the class name or topic to describe the workbook's purpose or content in the document properties. - Users can save time locating a particular file because they can view a file's document properties without opening the workbook. - By creating consistent properties for files having similar content, users can better organize their workbooks. - Some organizations require Excel users to add document properties so that other employees can view details about these files.
autocorrect
1 Corrects two initial uppercase letters by changing the second letter to lowercase. 2 Capitalizes the first letter in the names of days. 3 Replaces commonly misspelled words with their correct spelling. For example, it will change the misspelled word recieve to receive when you complete the entry. AutoCorrect will correct the spelling of hundreds of commonly misspelled words automatically.
cell style
A cell style is a predesigned combination of font, font size, and font color that you can apply to a cell. Why? Using the predesigned styles provides a consistent appearance to common portions of your worksheets, such as worksheet titles, worksheet subtitles, column headings, and total rows
chart sheet
A chart sheet is a separate sheet in a workbook that contains only a chart, which is linked to the workbook data.
charts
A column chart is a good way to compare values side by side. A line chart is often used to illustrate changes in data over time. Pie charts show the contribution of each piece of data to the whole, or total, of the data. A pie chart can go even further in comparing values across categories by showing each pie piece in comparison with the others. Area charts, like line charts, illustrate changes over time but are often used to compare more than one set of data, and the area below the lines is filled in with a different color for each set of data. An X Y (scatter) chart is used much like a line chart, but each piece of data is represented by a dot and is not connected with a line. Scatter charts are typically used for viewing scientific, statistical, and engineering data. A map chart depicts data based on geographic location. A stock chart provides a number of methods commonly used in the financial industry to show fluctuations in stock market data. A surface chart compares data from three columns and/or rows in a 3-D manner. A radar chart can compare aggregate values of several sets of data in a manner that resembles a radar screen, with each set of data represented by a different color. A funnel chart illustrates values during various stages. A combo chart allows you to combine multiple types of charts.
calculating each total for each range in a worksheet
A more efficient method, however, would be to copy the SUM function from cell B6 to the range C6:M6. A range of cells you are cutting or copying is called the source area or copy area. The range of cells to which you are pasting is called the destination area or paste area.
pane
A pane, in contrast to a dialog box, is a section of a window, such as the navigation pane in the File Explorer window, that can remain open and visible while you work in the document.
quick access toolbar
A pane, in contrast to a dialog box, is a section of a window, such as the navigation pane in the File Explorer window, that can remain open and visible while you work in the document.
range
A range is a series of two or more adjacent cells in a column, row, or rectangular group of cells, notated using the cell address of its upper left and lower right corners, such as B5:C10. For example, the group of adjacent cells B4 and B5 is a range.
Window
A window is a rectangular-shaped work area that displays an app or a collection of files, folders, and Windows tools. A window has a title bar, an area at the top of a document window or app window that displays the file name and program name.
Workbooks and Worksheets:
A workbook is like a notebook. Inside the workbook are sheets, each of which is called a worksheet. A worksheet is a single sheet in a workbook file that lets you enter and manipulate data, perform calculations with data, and analyze data. Thus, a workbook is a collection of worksheets. Worksheets allow users to enter, calculate, manipulate, and analyze data, such as numbers and text. The terms "worksheet" and "spreadsheet" are interchangeable.
avg, max, min
AutoSum list lets you calculate not only sums but also the average, the number of items, or the maximum or minimum value of a range. You can calculate these using three additional functions: AVERAGE, MAX, and MIN. The AVERAGE function calculates the average value in a range of cells, the MAX function calculates the maximum value in a range of cells, and the MIN function calculates the minimum value in a range of cells.
automatically updated properties
Automatically updated properties are file system or document properties, such as the date you create or change a file, and statistics, such as the file size.
Using its own row
Each income or expense item should be placed in its own row. Why? Entering one item per row allows for maximum flexibility, in case more income or expense items are added in the future.
sheet tab
Each worksheet has a sheet name that appears on a sheet tab, an indicator at the bottom of the window that identifies a worksheet.
how does excel know which cells to sum
Excel automatically selects what it considers to be your choice of the range to sum. When proposing the range, Excel first looks for a range of cells with numbers above the active cell and then to the left. If Excel proposes the wrong range, you can correct it by dragging through the correct range before pressing enter. You also can enter the correct range by typing the beginning cell reference, a colon (:), and the ending cell reference.
Charts
Excel can draw a variety of charts, such as column charts and pie charts.
key tips
If you prefer using the keyboard instead of the mouse, you can display KeyTips for certain commands (Figure 1-14). KeyTips are labels that appear over each tab and command on the ribbon when the ALT key is pressed.
calculating sums and using formulas
In Excel, you can easily perform calculations using a function. A function is a special, predefined formula that provides a shortcut for a commonly used calculation, for example, SUM or COUNT. When you use functions, Excel performs the calculations for you, which helps to prevent errors and allows you to work more efficiently.
pie chart
In this worksheet, the ranges you want to chart are the nonadjacent ranges A9:A16 (expense titles) and N9:N16 (yearly expense totals). The expense titles in the range A9:A16 will identify the slices of the pie chart; these entries are called category names. The range N9:N16 contains the data that determine the size of the slices in the pie; these entries are called the data series. A data series is a column or row in a datasheet and also the set of values represented in a chart. Because eight budget items are being charted, the pie chart contains eight slices.
right arrow key
Pressing an arrow key to complete an entry makes the adjacent cell in the direction of the arrow (up, down, left, or right) the next active cell. However, if your next entry is in a nonadjacent cell, you can complete your current entry by clicking the next cell in which you plan to enter data. You also can press ENTER and then click the appropriate cell for the next entry.
diaglogue box launcher
Some groups on the ribbon have a small arrow in the lower-right corner, called a Dialog Box Launcher, that when clicked displays a dialog box or a pane with more options for the group (Figure 1-12). When presented with a dialog box, you make selections and must close the dialog box before returning to the document
standard properties
Standard properties are document properties associated with all Microsoft Office files and include author, title, and subject
autosum
The AutoSum arrow (shown in Figure 1-29) displays a list of functions that allow you to easily determine the average of a range of numbers, the number of items in a selected range, or the maximum or minimum value of a range.
sum function
The SUM function adds all the numbers in a range of cells. Why? The Excel SUM function is an efficient means to accomplish this task.
fill handle
The easiest way to copy the SUM formula from cell B6 to cells C6:M6 is to use the fill handle. Why? Using the fill handle copies content to adjacent cells using one action, which is more efficient than other methods. The fill handle is a box that appears in the lower-right corner of a selected cell or range. It is used to fill adjacent cells with duplicate or similar data. The following steps use the fill handle to copy cell B6 to the adjacent cells C6:M6.
saving files
The only invalid characters are the backslash (\), slash (/), colon (:), asterisk (*), question mark (?), quotation mark ("), less than symbol (<), greater than symbol (>), and vertical bar (|).
ribbon
The ribbon (shown in Figure 1-8) is a horizontal strip near the top of the window that contains tabs (pages) of grouped command buttons that you click to interact with the app. Each tab in the ribbon contains a group of related commands and settings. Each group is a tab element on the ribbon that contains related commands.
entering numbers
These numbers, which represent yearly income and expense amounts, are entered in rows 4-5 and 9-16. Why? One of the most powerful features of Excel is the ability to perform calculations on numeric data. Before you can perform calculations, you first must enter the data.
right alignment numbers
When you enter numeric data in a cell, Excel recognizes the values as numbers and automatically right-aligns the values in order to vertically align decimal and integer values.
