What is Excel?
Excel is a software application that provides an “electronic spreadsheet,” or worksheet environment. An Excel file, called a workbook, can contain several worksheets. Excel can be used to manage numbers and calculations.
To Start Excel:
Double click on the Excel icon.
The Excel application window:
The components of the application window are:
Title bar: the bar across the top of the window. It is blue whenever that particular window is active. The title bar contains the program name (Excel) and workbook name on the left. On the right are the Minimize, Maximize (or Restore) and Close buttons.
Menu bar: the area directly beneath the title bar. It displays the names of the drop-down menus for the current applications.
Toolbars: the area below the menu bar. Toolbar buttons provide shortcuts for accomplishing many Excel tasks. When you point to a button with the mouse pointer and pause, its name appears. For example, if you point to the capital letter B, you see “Bold” appear. Likewise with the I (Italic) and U (Underline).
To use a button, click on it. The first button on the standard toolbar is the New button.
If you can’t locate an item (button) on the toolbar, click on the More Buttons button (the down arrow at the very end (far right) of any toolbar) and you’ll see even more items from which to choose.
Certain items in the toolbars have many items from which to choose. Click once on the little down arrow to the right of the item. This “drops down” a list of all the possible choices for that item. For example: on the formatting toolbar, you see “Arial” and if you click on the arrow, you’ll see a list of all the fonts that are available. You can scroll through the list by pointing to the up/down arrows and holding the left click down. To select a font simply point to it (with the mouse pointer) and click once.
Formula bar: the area below the Formatting toolbar. It displays the contents of the active cell in a workbook. On the far left side of the formula bar, the reference area identifies the current or active cell.
Status bar: beneath the workbook. It displays information about a selected command and about Excel’s current state. The status bar displays information about activities on the computer. It displays the current operating mode (“Ready” when you start Excel), along with other information, such as the status of your Num Lock and Caps Lock keys.
Task bar: at the very bottom of the screen. From the task bar you can access the Windows Start menu and other open applications.
Workbook: The default Excel workbook contains 3 worksheets named Sheet1 through Sheet3. The sheet names appear on tabs at the bottom of the workbook. A new Excel workbook file can contain up to 255 separate worksheets. The Excel worksheet is a grid of 256 columns by 65,536 rows. Columns are designated by letters running across the top of the worksheet, and rows are designated by numbers running down the left border of the worksheet. Column headings begin with the letter “A” and continue through the letter “Z.” After the 26th column (column Z), headings become double letters, from “AA” to “IV.” Row headings begin with the number 1 and continue through the number 65,536.
Cells: the intersection of a column and a row. Cells are referred to by their column and row locations. For instance, column A, row 1 is cell A1. You enter data (text or numbers) directly into any cell that is active. The active cell is distinguished by a thick, dark border.
Scroll bars: to the right of and below the worksheet grid. Use the scroll bars to display different areas of the active worksheet.
Sheet tabs: at the bottom of the workbook. You can click on the sheet tabs to move from one sheet to another in a workbook.
Tab scrolling buttons: to the left of the sheet tabs. You can click on the tab scrolling buttons to scroll the display of sheet tabs one at a time, or to display the first or last grouping of sheet tabs within a workbook.
The Office Assistant runs by default each time you start the Excel program, and is displayed in the lower-right corner of the screen. To use the Office Assistant, click on it, then type a question in the text box provided, and then click on Search (or press Enter) to find an answer to your question (or display other questions that may lead you to the answer that you seek). You can type key words, instead of an entire question, if you prefer.
To enter data:
* Select, or activate, the cell in which you want to display the data;
use the mouse pointer to point to a cell and then click to select it, or
use the arrow keys on your keyboard to select a cell.
* Type the data.
(Hint: if using the numeric keypad on the keyboard, be sure that Num Lock is on.)
* Enter the data into the cell by using any of these techniques:
press the Enter key,
click on the Enter button in the formula bar (the boxed checkmark), or
press any of the keyboard movement keys, such as Right Arrow.
Worksheet cells can contain constant values (text or numbers) or formulas.
Entering numbers:
By default, numbers are right-aligned in the cell.
By default, text is left-aligned in the cell.
Replacing cell contents:
To correct an item after it is entered into a cell: retype the entry and enter it again. The new entry replaces the old entry.
Editing cell contents:
To edit a cell, double click on it, then use the keyboard or mouse to make changes.
Clearing Cell Contents
To remove the contents of a single cell, select the cell and press the Delete key.
Changing entries as you type
To change an entry before it is entered into a cell:
Press Backspace to delete individual characters.
Press Esc or click on the Cancel button (the X in the formula bar) to clear the entire entry.
Undo
To reverse your last action, click on the Undo button.
Note: not all actions can be undone in Excel. For example, you cannot undo a File, Save.
Moving around the worksheet
Press Home - to move the active cell to column A of the current row.
Press PgDn or PgUp To move the active cell down or up by one screen’s worth of rows.
Press Ctrl+Home to move the active cell to A1 in the active worksheet.
Press Alt+PgUp or Alt+PgDn to move the active cell one screen to the left or right, respectively.
Click in the Name Box, type the cell name (such as K12) and press Enter - to move to the specified cell.
Saving a file:
Until it is saved, a workbook exists only in computer memory, which is a temporary storage place. For permanent storage, a workbook must be saved to a disk. It is important to save your work frequently (every 10 to 15 minutes).
Choose File, Save As to save a file for the first time, or to save the changes in a file with a different name (other than the one specified in the title bar), in a different location, or in a different format.
A “Save As” window pops up. (continue on next page)
Near the top of this window is a field called “Save in:” Pay close attention to this field any time you save a file. This is where your file will be saved. Click the down arrow for this field to see all the possible places where files can be saved.
Near the bottom of this window is a field called “File name:” Pay close attention to this field any time you save a file. Whatever you type in this field will be the name of your file.
Filenames can contain up to 255 characters (letters, numbers, and some symbols), including spaces. You can make the filenames as descriptive as you like.
After you have decided where to will save your file and the file name, click the Save button which is towards the right of the window.
Observe the title bar. The file’s new name is displayed.
To update a previously saved file, click on the Save button. (looks like a floppy disk). This saves the file with the same name and in the same location.
To create a new workbook, click on the New button which is on the far left of the toolbar and looks like a piece of paper with the corner folded.
Click on the Open button (yellow folder with arrow).
In the Open window, find the file you want and click the Open button.
A range is a group of adjacent cells in a worksheet. To select a range of cells:
With the mouse pointer, point to the center of the cell in one corner of the range; press and hold the mouse button while dragging to the opposite corner of the range; then release the mouse button.
To select a group of separate (nonadjacent) ranges that you want to affect at the same time, select the first range, press and hold the Ctrl key, and select the next range(s).
To deselect a range, click away from the selected range.
Place the mouse pointer on the lower right corner of the cell or range, drag to the right or below (one or more cells), release.
Copy data
Select the cell(s), click on the Copy button (2 pieces of paper), move the mouse pointer to the destination, click on the Paste button (clipboard and piece of paper).
Select the cell(s), click on the Cut button (scissors), move the mouse pointer to the destination, click on the Paste button (clipboard and piece of paper).
- or -
Select the data (cell or cells), place the mouse pointer at an edge of the selection (it becomes a 4-way arrow), drag (press and hold the mouse button) to the new destination and drop the selection (release the mouse button).
Fill in a series of text, numbers
This feature is a time-saver when you need to put days of the week, months of the year, or a range of numbers in many cells.
For example: Type January in a cell. Move the mouse pointer to the lower right corner of the cell. Press the left click and hold, then drag down or to the right to fill nearby cells with consecutive months.
The same can be done with days of the week.
Hint: After December or Saturday, for example, the series repeats itself (with January or Sunday). Also, you can begin with any month (not just January) or any day of the week (not just Sunday) or any number (not just 1).
Whatever format you use is repeated.
For example. Jan, Feb, Mar, Apr or Sun, Mon, Tue, Wed.
Fill a range with numbers:
Type a number (such as 1 or 10) in a cell. Type the next increment (such as 2 or 20) in the next cell. Specify those 2 cells as range, then drag the lower right corner of the range to fill a series of adjacent cells. The results: 1,2,3,4,5,6... or 10,20,30,40,50,60,...
Select the range to be sorted.
Click on the Sort Ascending button (A Z) or the Sort Descending button (Z A).
Place the mouse pointer on the boundary to the right of a column heading and double-click. The column width changes (increases/decreases) to accommodate the longest entry in the column.
- and / or -
Place the mouse pointer on the boundary to the right of a column heading and drag to the right (to increase) or to the left (to decrease).
Insert a row or column:
Place the mouse pointer on the Row number or Column letter where you want to insert, left click to select it, right click, select Insert.
Insert multiple rows or columns:
Specify the range of rows or columns where you want to insert, then right click and select Insert.
To delete a row or column
Place the mouse pointer on the Row number or Column letter where you want to delete, left click to select it, right click, select Delete.
To delete multiple rows or columns
Specify the range of rows or columns where you want to delete, then right click and select Delete.
Formulas
Formulas are instructions that you enter to perform calculations.
You can create formulas by using numbers (for example, 10+20); however, it is preferable to construct formulas that refer to worksheet cells. This way, the results of the formulas update automatically when you change numbers in your worksheet. In Excel, you create formulas by preceding the expression with an equal sign (=).
For example, enter the number 10 in cell B1; enter the number 20 in B2; enter the formula =B1+B2 in cell B3. The value 30 is displayed in cell B3. Any time that you change the value in cell B1 or B2, Excel instantly recalculates the value of cell B3.
When entering a formula, you can type in cell references or click on the desired cells. Clicking on the cells may prevent typing errors.
The mathematical operators are:
+ addition
- subtraction
* multiplication
/ division
^ exponents
Order of Operations:
There is a specific sequence that Excel follows when it performs calculations. This sequence is known as the order of operations. An easy way to remember it is PEMDAS (Please Excuse My Dear Aunt Sally).
Parentheses
Exponents
Multiplication
Division
Addition
Subtraction
Identifying cell contents:
To view the true contents of a cell, select the cell and observe its contents in the formula bar.
Choose Tools, Options, View tab; in the Window Options box, check Formulas and click on OK. To put everything back the way it was, repeat these steps.
Functions
A function is made up of 3 things:
1. equal sign (=)
2. function name (or an abbreviation of that name)
3. arguments, which are required data (often a range) enclosed in parentheses.
Examples:
=SUM(A4:A10) Adds all values in cells A4 through A10.
=AVERAGE(A4:A10) Calculate the average value of the numbers in cells A4 through A10.
An easy way to enter a function is:
Click the Insert Function (fx) button. The Insert Function window appears.
You can type in the function you want to perform and click Go.
- or –
click the drop-down arrow for “Or select a category” and scroll through the many options.
Click OK, then you’ll have a window that prompts you for the cells to be used in the formula.
Quickly add up a column (or row) of numbers:
Place the mouse pointer in the cell where you want the result to appear, click on the AutoSum button (looks like a backwards capital E), verify that the range is correct (if not, change it), press Enter.
Formatting a worksheet
The buttons on the formatting toolbar allow you to quickly the appearance of all or part of a worksheet. Select the cell or cells to change, then click on the button of your choice.
Some formatting buttons are toggles. For example: if cell A1 is in Bold and you want to remove the Bold simply select the cell and click on the Bold button.
If you want to apply a formatting feature (or features) to the entire worksheet, this is how to select the whole thing.
Click on the Select All button – the little box in the upper-left corner of the worksheet (above the “1” and left of “A”). To deselect, click anywhere in the worksheet.
Select the cell(s), select Edit, Clear, Formats.
Select the cell(s), click on Format, click on Cells. Select the Alignment Tab. In the Orientation Box (looks like half of a clock), use the mouse pointer to drag the text rotation indicator to the desired orientation, then click OK.
If you need a format that is not available on the formatting toolbar, select the cell(s) you want to affect, click on Format, Cells, and the Number tab. This provides many options from which to choose and the sample box displays exactly how your number would appear. This is handy to use when typing items like social security numbers or phone numbers (these are under the special category). It saves you many keystrokes by placing the dashes and parentheses in the cell(s) for you.
Aligning Cell Contents
To change the alignment of cells
(left, center, right), select the cell(s), then click on the desired
Formatting toolbar button. (Align Left, Center, Align Right). They are
conveniently grouped together.
This is something you are likely to do with a heading.
Type the text in one of the cells, select the range, click on the Merge And Center button (small “a” with arrows in a white box on the Formatting Toolbar). The selected cells are merged into one cell, and the text is centered within it.
To copy formats from 1 cell or range of cells to another
Select the cell(s) to copy from, click on the Format Painter button (looks like a paintbrush), then select the cell(s) to copy to.
To check spelling of the entire worksheet, select cell A1, then click on the Spelling button (ABC /checkmark) on the standard toolbar.
To check spelling of a range of cells, specify the range, then click on the Spelling button.
To preview each page exactly as it will appear when it is printed, click on the Print Preview button (piece of paper with a magnifying glass).
While in print preview, some useful buttons are:
Next – to page forward
Previous – to page backward
Zoom – to get a closer look (a toggle)
Close – to close print preview and return to the worksheet.
While in print preview, select the Setup button, Page tab, select Landscape, click on OK.
While in print preview, select the Setup button, Page tab, select Fit to (leave 1 for # of pages), click on OK.
Specify the area to print, select File, Print Area, Set Print Area.
That area remains the selected print area until it is cleared by doing the following:
Select File, Print Area, Clear Print Area.
Click on the print button. (printer).
To lock certain rows or columns of information on the screen while you scroll through the rest of the worksheet:
To keep certain rows at the top of your screen, select the cell in column A that is directly beneath the last row you would like to lock in place. Choose Window, Freeze Panes. Then you can scroll through the worksheet, keeping the information at the top of the screen in place.
To keep certain columns at the left of your screen, select the cell in row 1 that is to the right of the last column you would like to lock in place. Choose Window, Freeze Panes to freeze columns of information.
To view 2 parts of the worksheet simultaneously
At the top of the vertical scroll bar is the Split Box. Drag it onto the worksheet. To remove the split, double-click on the split.
To check the spelling of an entire worksheet, select cell A1, then click on the Spelling button (ABC √) on the Standard toolbar.
Any word that is not recognized by Excel’s dictionary is listed in the Spelling dialog box. Normally, Excel will suggest one or more words (replacement spellings).
If you agree with Excel’s suggested change, click on Change. To specify another suggestion, select another item from the list. If “No Suggestions” is displayed in the Suggestions list box, enter a correction and click on Change, or click on Ignore Once to bypass this entry.
Change All button – changes all occurrences of a word
Ignore All button – ignores all occurrences of a word
Add to Dictionary button – adds a word to Excel’s dictionary
You can check the spelling of a range of cells, rather than the entire worksheet. Select the range and click on the Spelling button.
This lets you view your document to see exactly how it will look when printed.
Click on the Print Preview button (looks like a sheet of paper with a magnifying glass).
Click on the Next and Previous buttons to page forward and backward.
You may also use the vertical scrollbar on the right.
Zoom button – makes the document bigger for easier reading. It’s a toggle. Click it once to enlarge the document, then click again to put it back to its original size. This is for viewing purposes only; it does not change the font size on the printed page.
When done with Preview, click on the Close button.
Choose Print Preview, Setup.
Click on the Header/Footer tab.
To use a predefined format, click on the drop down arrow across from “(none)” under Header or Footer in the middle of the window. Notice the list of options and the scroll bar to the right. Use the scrollbar to see all of the options. Select the desired format from the list and click OK.
Now see your newly created header and / or footer in place.
To create a custom header/footer, click on Custom Header or Custom Footer. You can then format or edit the selected header or footer in the dialog boxes. Buttons are provided that make creating common headers and footers easier. The 10 buttons are (left to right):
Font, Page Number, Total Pages, Date, Time, Path&Filename, File Name, Sheet Name, Insert Picture, Format Picture
If your printout contains multiple pages, you might want a title heading or column headings on top of each page, or row headings in the first column of each page. To create print titles:
1. Choose File, Page Setup and select the Sheet tab.
2. Under “Print titles”click the box with the red arrow to the right of “Rows to repeat at top.” The mouse pointer is now a solid black right arrow. Position the mouse pointer over the row number and click. To select more than one row, click on the 1st one and drag to include the other row(s). The row(s) selected will be “flashing.” Close the little Page Setup window by clicking on the X or the box with the red arrow right beneath the X.
3. Click on OK.
Use Print Preview and the Next button to verify that it works as expected.
When a worksheet printout is too large to fit on one piece of paper, Excel inserts automatic page breaks based on the paper size, margin settings, and scaling options in the Page Setup dialog box.
You can manually insert page breaks.
To adjust a page break while in Print Preview:
Click on the Page Break Preview button, and drag the blue page break line to the desired row. When done, click on the Print Preview button to see how it looks. From Print Preview, click the Normal View button to return to the worksheet.
To insert a page break directly in the worksheet:
Select the cell in column A, or an entire row, that you want to print on the next page. Choose Insert, Page Break. A dashed line is displayed above the row of the selected cell.
To remove a page break:
Position the cursor directly under the page break line (any column), then choose Insert, Remove Page Break.
A name can be defined to refer to a cell, a group of cells, a value, or a formula. After names are defined in a workbook, you can use them to move quickly to a certain area of the workbook.
Names can be helpful when printing different sections of a workbook, especially if this is something you do repeatedly
To see any/all named ranges, click on the drop-down arrow for the Name box, on the far left end of the formula bar. Usually, this box displays the active cell address such as C41 (where your cursor is).
To go to a named cell or range, select the name from the Name list.
This is helpful if, for example, your worksheet spills over to a second page and you’d like to compress it to fit on 1 page.
Choose File, Page Setup – or - Print Preview, Setup.
Select the Page tab
Under “Scaling” select “Fit to:” and change the Fit to settings. For example: 1 page wide by 1 page tall.
Click on OK.
There are 6 default margins for a worksheet printout. Right, left , top, bottom, header and footer. The header and footer margins determine the amount of space between the header or footer and the body of the worksheet printout. In the Preview window, click on the Margins button to display the margins as lines on the worksheet preview.
You can change margins 2 ways:
1. place the mouse pointer over the line(s) and click and drag the margin line(s). This is a more “visual” approach
- or -
2. click on the Setup button, Margins tab and change the margin values.
This is the way to go if you specifically want a 1” margin on all sides, for example.
Notice that when you click in any of the margin boxes (top, bottom, left, right, header, footer), you see a black line on the page in the middle of the window that indicates which one you’re working with. This is good to know if you have trouble remembering which one is the header margin and which one is the top margin, for example.
You can type in a number or use the up/down arrows to change the number.
To define a name, select the cell or range of cells that you want to name, click in the name box, type in the name, then press Enter. You cannot use spaces in the range name.
To use a named range for printing:
Select the name, (the range will then be highlighted), select File, Print. Under “Print What” select Selection. Click OK. The selected range (only) prints. If you have headers or titles they will print also. Of course, you can use Print Preview, too.
Select an area of the worksheet, then choose File, Print Area, Set Print Area.
This area remains the print area (for previewing/printing) until you choose File, Print Area, Clear Print Area.
A default Excel workbook usually contains 3 worksheets, named “Sheet1” through “Sheet3.”
A worksheet’s name is displayed on the sheet tab.
To move from sheet to sheet:
Click on the corresponding sheet tab.
If you can’t see all of the sheet tabs, adjust the size of the horizontal scroll bar by doing the following:
Place the mouse pointer over the small rectangle which is to the left of the left arrow on the far left end of the horizontal scroll bar. The mouse becomes 2 arrows. Click and drag to the right so that you can see all the sheet tabs.
To rename a worksheet tab:
double-click on the tab, type the new name, press Enter.
Tab names can contain up to 31 characters, including spaces.
To Insert a new worksheet:
Click on Insert, select Worksheet.
To delete a worksheet:
Right-click on the worksheet tab, select Delete.
To move a worksheet:
Click and hold on the worksheet tab, pause for a moment. You’ll see a little black arrow and a sheet of paper. Drag the mouse left and/or right. The black arrow is the insertion point; wherever it is when you release the mouse button is where the sheet will be placed.
To change tab colors:
Right click on the sheet tab, select Tab Color, click on desired color, click OK.
To move sheet to another workbook:
Open the destination workbook if it’s not already open,
Right click on the sheet tab, select Move or Copy, select what workbook to place it in and specify where to place it in relation to other worksheets in that workbook.
You can create formulas that contain references to cells or ranges that are on other sheets of the workbook. A three-dimensional (3-D) reference is a range that spans 2 or more sheets in a workbook.
For example, to add the figures in the same cell across multiple worksheets:
Click in the cell in the worksheet where you want the result to be.
click on fx (on formula bar),
click on Sum,
click on the sheet tab to start with,
click on the cell (you may need to move the Function Arguments window out of your way first),
press and hold Shift,
click on the last sheet to be included in the range,
click OK.
You can create an outline structure for your worksheet that has up to 8 levels of information. Outlining makes it easy to move through many rows or columns, rearrange parts of a worksheet, create summary reports, and create charts from similar levels of data located in different places in a worksheet.
Creating an Outline
For Excel to be able to create an outline for your data, your formulas must consistently “point” in the same direction. If you have a formula below the cells that it operates on, then all of your formulas should be below the cells they work on. Similarly, if you have a formula to the right of the cells that it operates on, then all of your formulas should be to the right of the cells they work on.
To automatically create an outline structure for your worksheet data:
1. Select the range for which you want to create an outline. (To create an outline for the entire worksheet, your cursor can be on any cell.)
2. Choose Data, Group And Outline, Auto Outline.
You’ll now see an area with some lines and dashes to the left of the row numbers.
You can control the display of data in an outline by using the following outline symbols:
Row-level & column-level symbols – These are buttons with numbers to the left of and/or above the Select All button. You can display specific levels of outline information by clicking on these numbers. This is an easy way to display all subtotals in one step, for example.
Row-level & column-level bars – These bars span the ranges referred to by the formulas on the worksheet. The endpoint containing the hide detail symbol is next to the formula. The other endpoint is over the first cell referred to by the formula.
The hide/show detail symbols enable you to expand/collapse the outline, respectively.
Hide detail symbols – Minus signs (-). Hide rows or columns of information.
Show detail symbols – Plus signs (+). Show (display) hidden rows or columns of information.
To Remove an outline:
Choose Clear Outline.
A chart is a graphical representation of worksheet data. Values from worksheet cells are displayed as pie slices, bars, or other shapes in a chart.
Charts are either embedded in a worksheet or are on a separate sheet of the workbook. Either way, the data is linked to the worksheet from which you create it. When data changes on the worksheet, the chart is updated automatically.
To quickly create a chart, select the data that you want to chart and press F11. By default the data is displayed in a column chart with a legend.
Notice that you now have “Chart” on the menu bar. (That’s not on the other worksheets.)
To change the chart type:
Click on Chart on the menu bar, select Chart Type. Read the information that your Office Assistant is providing. Click on other chart types in the list.
Click on the Press & Hold to View Sample button towards the bottom of Chart Type window. Click and Hold the mouse button to view a sample of that chart type with your actual data!
To change the legend titles:
Click on the Chart button, choose Source Data, select the Series tab, click on the Series name to change,
click in the Name box and type in the new name
- or -
click the red arrow at the end of the name box, click sheet tab, click cell, click red arrow or X.
Repeat with the other Series names and click OK when done.
To change the legend position (in relation to the chart):
Click on Chart, choose Chart Options, select the Legend tab, under “Placement” select from Bottom, Corner, Top, Right, Left (watch preview area as you do this). Click on OK.
To add data labels:
Click on Chart, choose Chart Options, select the Data Labels tab, select Value, click on OK.
To add the chart to the worksheet:
Click on Chart, choose Location, select “As object in:,” change Sheet location if necessary (use drop-down arrow), click on OK.
Now the chart is an object in the worksheet where you placed it.
Notice the black resizing squares on all 4 sides and all 4 corners of it.
To change the size of the chart object, place the mouse pointer over the black squares, then click and drag.
To move the whole object, move the mouse to an area inside the object so that “Chart Area” appears. Now click and drag to a new location in the worksheet. The mouse pointer becomes 4 arrows while dragging.
Lists (database)
Using Excel as a database
Planning a list
Lists versus databases
Creating a list
Add records with the data form
Find records with the data form
Sort a list by one field
Sort a list by multiple fields
Print a list
Analyzing list data
Use data validation for list entries
Produce a list from which to choose entries
Set up an input message for data entry
Set up an error message for data entry
Autofilter
Custom filter
Creating subtotals using grouping and outlines
Freezing columns and rows
Lists
A database is an organized collection of related information. Examples of databases include a telephone book, a card catalog, and a class roster of students. Excel refers to a database as a list. Using an Excel list, you can organize and manage worksheet information so that you can quickly find needed data for projects, reports, and charts.
When planning a list, consider what information the list will contain and how you will work with the data now and in the future. Lists are organized in to records. A record contains data about an object or person. Records are divided into fields. Fields are columns in the list. Each field has a field name which is a column label that describes the field. Field names can be up to 255 characters in length (the maximum column width). Field names appear in the first row of a list
Determine any special number formatting required in the list.
Most lists contain both text and numbers. When planning a list, consider whether any fields require specific number formatting or prefixes. For example, you may have some zip codes that begin with zero. Excel automatically drops a leading zero, so you must type an apostrophe when entering a zip code that begins with 0. The apostrophe tells Excel that the cell contains a label (text) rather than a value.
If a column contains both numbers and numbers that contain a text character, such as an apostrophe, you should format all the numbers as text. Otherwise, the numbers are sorted first, and the numbers that contain text characters are sorted after that. For example, 11542, 60614, 87105, ‘01810, ‘02115.
To instruct Excel to treat all the zip codes (for example) as text:
Click on the column letter (to select the whole column), click on Format on the menu bar, Cells, Number tab, Text, OK.
If your list contains more records than can fit on one worksheet (more than 65,536), you should consider using database software (such as Access) rather than spreadsheet software.
Beginning in cell A1 and moving horizontally, type each field name in a separate cell.
Always put field names in the first row of the list.
Enter the information in the rows immediately below the field names.
Do not leave any blank rows.
Select the range of columns, click Format on the menu bar, point to Column, click AutoFit Selection.
If the field name you plan to use is wider than the data in the column, you can turn on Wrap Text to stack the heading in the cell. Doing this allows you to use descriptive field names and still keep the columns from being unnecessarily wide.
There are a few ways to accomplish this:
before entering the field name, select the cell, select Format on the menu bar, Cells, Alignment tab, under “Text control”, click to select Wrap text, OK, then type in the field name.
while entering field names, press Alt-Enter to force a line break
after entering the field name, click on the cell, press F2 (to edit), place the cursor where you want the break to occur, press Alt-Enter. Then press Enter.
You can add records to a list by typing data directly into the cells.
and / or
Once the field names are created, you can also use the data form as a quick, easy method of data entry. A data form is a dialog box that displays one record at a time.
You can use the data form to edit records as well as to add, search for, and delete them. Just find the desired record and edit the data directly in the appropriate box.
Click on any cell in your list, click on Data on the menu bar, Form.
If you receive the message “No list was found…..”, click OK, select any cell within the list, then select Data, Form.
The title bar of the data form window has the sheet name in it.
You see current record # of total # of records (in the upper right corner).
To scroll through the records forward or backward:
ü click on “Find Prev” or “Find Next” buttons
ü use the vertical scroll bar
ü use the up/down arrows on the keyboard.
To add a new record click New. Notice “New Record” in upper right corner.
To move from one field to the next – use the tab key or click with the mouse.
The new records that you add with the data form are placed at the end of the list and are formatted in the same way as the previous records.
To delete a record, click Delete. “Displayed record will be permanently deleted.” Click OK. Notice when a record is deleted, the next one “moves up.”
Excel does not leave a blank row where the deleted record was.
When you delete a record using the data form, you cannot undo your deletion.
When you delete a record by deleting the row in which it resides, you can restore it by using undo.
Restore will “undo” any changes you just made to a record.
It will not bring back a deleted record.
To close the data form, click Close or the X in the upper right corner of the window.
Data, Form, Criteria.
Notice “Criteria” in the upper right corner.
Click in the appropriate field, type in the desired criteria, click Find Next.
Click Find Next until there are no more matching records.
Close when done or click Criteria to return to the Criteria data form.
Notice that Criteria and Form toggle back and forth.
Criteria:
You can use wildcards such as the ? which stands for any single character. For example: J?n will produce Jan, Jen, Jon.
The * wildcard stands for any group of characters. For example, Jan* will produce Jan, Janet, Janice.
You can also use comparison operators when performing a search using the data form. For example >50 <10 <=20 >=30
You can specify more than 1 criterion.
Edit on menu bar, Find, Replace tab or
Edit on menu bar, Replace tab.
Replace – replaces current item that matches Find what box.
Replace All – replaces all occurrences of item in Find what box.
Click anywhere in the column to sort on, click the Sort Ascending (AZ) or Sort Descending (ZA) button.
Click Data on the menu bar, click Sort.
You can specify 1, 2, or 3 sort fields.
Use the drop down arrow to select sort fields.
Notice “Header row.”
If this is checked, the header row will not be included in the sort.
Options – use to sort days of the week or months of the year in their true sort order so that you get:
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
rather than:
Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday.
If a list is small enough to fit on one page, you can print it as you would any other Excel worksheet. If you have more columns than can fit on a portrait-oriented page, try setting the page orientation to landscape.
Click the print preview button on the standard toolbar, click Setup, Page tab, Landscape, OK.
Because lists often have more rows than can fit on a page, you can define the first row of the list (containing the field names) as the print title, which prints at the top of every page.
Select File on the menu bar, Page Setup, Sheet tab, Rows to repeat at top, click in any cell in row 1, OK.
You can use headers and footers to add identifying text, such as the list title or report date.
Select File on the menu bar, Page Setup, Header/Footer tab.
The Excel Data Validation feature allows you to specify what data is valid for a range of cells. This can apply to a single cell, a column, a row, or any range of cells. You can restrict data to whole numbers, decimal numbers, or text, or you can set limits on entries.
You can also specify a list of acceptable entries. Once you’ve specified what data is considered valid, Excel prevents users from entering any other data (considered invalid) except your specified choices. This makes it more likely that data will be entered consistently and correctly.
To produce a list to choose from:
Click the column letter to select the whole column.
Click Data on the menu bar.
Click Validation, click the Settings tab.
Click the “Allow” drop down arrow.
Select List, for example.
Click in the “Source” box, type in the list of possible choices, separated by commas. – or – click the little red arrow to the right, specify the range that contains the valid entries, close the Data Validation window, click Close.
To set an input message when you are about to enter data in a column:
Click the column letter to select the whole column.
Click Data on the menu bar.
Click Validation, click the Input Message tab.
Type in a title.
Type in an input message. (specific instructions for the data entry person ). You may use blank lines.
How it works: when you click on a cell that has this set for it, you see the title and input message appear from the office assistant. The message will still appear even if you have the office assistant hidden.
This option can be used alone – with or without anything specified in the Settings tab.
Remember: this is only a message – it does not prevent invalid data from being entered.
That is dictated by the information set in the settings tab.
To set an error message when undesirable data has been entered:
Click the column letter to select the whole column.
Click Data on the menu bar.
Click Validation, click the Error Alert tab.
Select a style (Stop, Warning, or Information).
Type in a title.
Type in an error message. (specific instructions for the data entry person ). You may use blank lines.
How it works: when you type in invalid data, you see the title and error message appear from the office assistant. The message will still appear even if you have the office assistant hidden.
There are many ways to manipulate or analyze list data with Excel. One way is to filter a list so that only the rows that meet certain criteria are retrieved.
The Excel AutoFilter feature searches for records that meet criteria the user specifies, and then lists those matching records. Once you create a filtered list, you can print it or copy it to another part of the worksheet to manipulate it further.
Click Data on the menu bar, click Filter, click AutoFilter.
Notice the drop down arrows on all columns.
Click the drop down arrow on the field to filter.
(All)
(Top 10…)
(Custom…)
…
{one of each data item in the list}
…
(Blanks)
(NonBlanks)
Click on the item you want for your search criterion.
Notice that 3 things happened:
the field list arrow changed color
only the records meeting that search criteria are displayed
“# of # records found” is displayed in the lower left corner of the screen.
To clear the filter and have all the records reappear:
Click the field list arrow, select (All).
– or –
Select Data, Filter, AutoFilter (to uncheck it) or Show All.
Notice that “undo” is not an option.
How to use the Top 10 feature and why you would want to:
The default is to select the 10 records with the highest value.
Other options: you can select “Bottom” instead of Top to show lowest values, and you can select any number – not just ten.
Keep in mind that you are not limited to just performing one filter on a list and then turning it off. Once you perform a filter to extract a certain group of records, you can perform another filter on that group of records to narrow your search even further.
You can perform more complex filters using options in the Custom AutoFilter dialog box.
With AutoFilter turned on (you see the list arrows on all the field names):
Click the desired field list arrow and select Custom.
You can move the Custom AutoFilter window out of the way if necessary.
Click the drop down arrow for all the different options.
Notice that you can specify an “and” or an “or” condition here.
You can customize your search with the “?” and the “*” wildcards.
The Excel subtotals feature provides a quick, easy way to group and summarize data in a list. Usually, you create subtotals with the SUM function. You also can subtotal groups with functions such as COUNT, AVERAGE, MAX, and MIN. Your list must have field names and be sorted before you can issue the Subtotal command.
Be sure to position the cell pointer within the list range.
Click Data on the menu bar, click Subtotals.
Specify the items you want subtotaled, the function you want to apply to the values, and the fields you want to summarize.
When done, click OK.
Notice the subtotals and grand total.
Notice the Outline to the left of the worksheet showing the structure of the subtotaled lists.
You can click on the plus (+) and minus (-) signs to expand / collapse levels of detail.
You can also click on the 1,2,3 at the top of the outline area to show / hide levels of detail for all groups at once.
To remove the Subtotaling feature:
Click Data on the menu bar, click Subtotals, Remove All.
When you do this, the Outline feature is also automatically turned off.
Use this feature if you’d like to be able to scroll down through rows of data and still be able to see the column headings.
Click in the cell immediately below the column heading in column A.
Click Window, Freeze Panes.
The same can be applied to freeze columns:
Click in the cell immediately to the right of the column to freeze.
Click Window, Freeze Panes.