Introduction to Excel (2007)
Before we start…
Throughout the following pages, we will reference several menu options and how you can get to them. To do this, we will use the following convention: When you see the following, ViewZoom, the first word (“View”) refers to a menu option usually found on the toolbar. The word that follows (“Zoom”) is a menu choice found under the first option you made.
Part 1: Becoming Familiar with Excel
Starting Excel
Note: Different versions of Windows may have slightly different steps/commands. An icon for MS Excel may already be located on the desktop as well.
- Click the Start button on the Windows taskbar.
- Point to Programs (or All Programs)
- Click Microsoft Excel to open a new workbook
The Excel Screen
The screen in Excel looks very different than those used in other types of applications.
The large window, labeled "Microsoft Excel" may take up the entire screen. This is referred to as the Application Window. The top line is called the Title Bar and has three buttons on the right side of the bar (Minimize, Restore, and Close); use these buttons to change the size of the window and/or close it. The Quick Access Toolbar is located on the left side of the Title Bar and allows you to access frequently-used commands. The Quick Access Tool Bar can be left in the default position (as shown in the screenshot) or moved.
The second line is called the Ribbon. The Ribbon helps you quickly find the commands that you need to complete a task. Commands are organized in groups which are collected under menu tabs relating to a type of activity; such as PageLayout. The Ribbon can be shown or hidden using the arrow button on the Title Bar.
The next line has the Name Box and the Formula Bar. The Name Box is used to display the active cell or cells; it can also be used to select a named reference (set of cells, chart, etc.) within your spreadsheet. The Formula Bar displays the contents of the current cell; if the cell contains a formula or function, it displays the formula/function instead of the result. Text, numbers, formulas or functions can also be typed directly into the Formula Bar.
The Zoom feature (located on the Ribbon under the View tab) allows you to change the size of the viewing area. This does not affect the actual printing of the file. Click on the left hand button and make a selection. You can also use the zoom slider in the lower right corner of the application window.
Excel has its own online help menu. You can access the Help options with the question mark icon on the Title Bar or by pressing F1 on the keyboard. A window will appear that prompts you to enter the topic you need help with.
Columns, Rows and Cells
The horizontal bar across the top of the worksheet area is filled with letters, beginning with A. Each letter represents a column while the vertical bar on the left side of the worksheet filled with numbers refers to rows.
The intersection between a column and a row is referred to as a cell. A cell is similar to a box that can be used to store a piece of information. Each piece of information could be a word or group of words, a number or a mathematical formula.
Each cell has its own address. This address is used in formulas for referencing different parts of the worksheet. The address of a cell is labeled with the letter of the column and the number of the row in which it is located. For example, the address of a cell in column B, row 5 would be referred to as B5. The column is always listed first, followed by the row without any spaces between the two.
These cell addresses are useful when entering formulas. Instead of typing actual values in your equations, you simply type the cell address that the value is stored in. Then, if you need to go back and change one of the values the spreadsheet automatically updates the answer based on the new number(s).
The cell with the dark border within the worksheet is referred to as the active cell. If you begin typing, or create a function in the Formula Bar, the active cell will be populated. Each cell may contain text, numbers, or dates. You can enter up to 32,000 characters in each cell (equivalent to a 44 page report!).
Worksheets and Workbooks
A worksheet is made up of up to 17,179,869,180 individual cells. The first 26 columns are lettered A through Z. Excel then begins lettering the 27th column with AA and so on. In a single Excel worksheet there are 16,384 columns (lettered A-XFD) and 1,048,576 rows (numbered 1-1,048,576), however, the more data you have in a worksheet, the less manageable it becomes!
Toward the bottom of the Excel window is a small tab that identifies each sheet in the workbook (file). If there are multiple sheets, you can use the tabs to easily identify what data is stored on each sheet. When you begin a new workbook, the tabs default to being labeled Sheet1, Sheet2, etc. Worksheets can be renamed, moved, copied and deleted by right-clicking on a sheet and selecting the desired command.
Moving Around in Excel
When Excel starts, a new worksheet opens. What is currently visible is only a small portion of what is available for you to use. In order to move to areas that you cannot see, you can:
Use the scroll bars
Use the keys described in the table below
Keystroke
/Result
Tab
/Move one cell to the right
Shift + Tab
/Move one cell to the left
Arrow key
/Move one cell in the direction of the arrow
Ctrl + arrow key
/Move in the direction of the arrow to the last cell before a blank cell, or to the edge of the worksheet if all cells are blank
Page Up
/Moves up one screen
Page Down
/Moves down one screen
Home
/Moves to the beginning of the row
Ctrl + Home
/Moves to cell A1
Ctrl + End
/Moves to the last cell containing data (in the bottom right of the worksheet)
Excel Cursors & Pointers
Excel uses several unique cursors and pointers that are used for different functions. The table below depicts each pointer and its application.
Part 2: Simple Exercises Using Excel
Entering & Editing Data
Entering data is as simple as beginning to type. In the following section, you will enter sample data, edit that sample data, and delete & undelete that data.
Activity 1
Enter the following information into your Excel worksheet:
PM2.5 Concentration (ug/m3)Jan / Feb / Mar / Apr / May / Jun / Total
Year 1 / 32.5 / 28.2 / 9.8 / 15.2 / 12.8 / 12.8
Year 2 / 25.0 / 30.9 / 13.11 / 19.99 / 12.34 / 10.098
Year 3 / 28.45 / 22.14 / 10.856 / 16.24 / 15.334 / 16.84
Year 4 / 26.36 / 14.78 / 16.51 / 18.98 / 12.62 / 12.21
Year 5 / 31.64 / 20.31 / 15.88 / 17.47 / 9.33 / 11.1
Editing data is simple as well. As with any Microsoft application, there are several options for doing this. First, highlight the cell you wish to edit, then:
1.Begin typingto completely overwrite cell contents
2.Double-click the cell and a flashing insertion point appears in the cell
3.Use the formula bar
4.Press F2 on your keyboard
After editing a value, hit Enter or Tab to accept the changes and move to another cell.
Activity 2
Edit three of your values, using any option mentioned above.
Deletion of data can be relatively straightforward. You can:
- Select a cell or range of cells and press delete
- Select a cell or range of cells and HomeEditingClear All.
Activity 3
Delete two values in your table using either choice above.
Undoing an action can save both time and headache. On the Quick Access toolbar, you may see two arrows. Using these arrows, you can either Undo the last action or series of actions you just completed, or Redo an action. Undo/Redo is only available for actions since the last Save command; once saved, changes cannot be undone.
Activity 4
Undo the deletion you completed in Activity 3 until the last two changes made are undone.
Working with columns and rows
Width
Sometimes the data you enter does not fit the default cell width of 8.43 characters. When this happens, you will see either ##### or see a number expressed in scientific notation (2.34E+08). To fix this, you will have to adjust the cell width. There are three options available to do this:
- Make sure the highlighted cell is in the column that you want adjusted. Choose HomeCellsFormat Column width from the menu bar. Then type in a new width and press enter. Note: Default width is 8.43 characters.
- Using the mouse, position the pointer at the right-most end of the column you wish to re-size. The Pointer will become a two-headed arrow. You can then click and drag to the desired column width.
- Using the mouse, position the pointer at the right-most end of the column you wish to resize and double click. It should resize to fit the largest word/character size.
Height
In the same respect, some of the data you enter will not fit the height of the cell and/or row it is in. Here are two ways to change the row height:
- Make sure the highlighted cell is in the row that you want adjusted. Choose HomeCellsFormat Row Height from the menu bar. Then type in a new height and press enter. Note: Default height is 12.75 points (approximately 1/6 inch or 0.4 cm)
- Use the mouse:
- Point to the top edge of the row number boundary to get the two-headed arrow.
- Drag downward to increase the row height.
- Drag upward to decrease the row height.
Inserting & Deleting
If you decide that you need another column in between your existing values, or that you want to insert a row or rows between existing values, you should use the following exercises (Note: these methods can also be completed by using the right-click option on your mouse):
- Inserting a single column: click on the column to the right of where you want the new column, then choose HomeCellsInsert Insert Sheet Columns
- Inserting a single row: click a cell in the row below where you want the new row and choose HomeCellsInsert Insert Sheet Rows
- Deleting a row or column: select a cell in each row or column to be deleted and choose HomeCellsDelete Delete Sheet Rows or Delete Sheet Columns.
Note: Inserting or deleting cells, rows or columns may affect formulas or information in other cells.
Activity 5
Insert a column between Jun and Total. Label this columnSubtotal. Insert a row between the table title (“PM2.5 Concentration”) and the header row (Jan-Jun). You decide that this is not the format you want; remove the newest columns and rows using the options above.
Sorting and Filtering Data
Sorting
One of the more useful features of Excel is the ability for it to sort and filter a data set with ease. A dataset can be sorted by one or more columns, in ascending or descending order. Care must be taken when sorting data that the integrity of the dataset is retained; for instance, if you select one column of data that you want sorted, then data in adjoining cells may not always get sorted with the data selected. (Typically, however, Excel will warn you if there is data adjacent to the selected data and ask if you want to sort only the data selected or all of the data).
To sort data, select the data you wish to sort and under HomeEditingSort & Filter, select the sort order desired. For sorting, you can highlight specific cells or an entire column; selecting the data header will also sort the data in a column without having to worry about adjacent data being affected. To sort data in multiple columns, choose “Custom Sort” from the Sort & Filter menu and set your options (click “Add Level” to add more columns and options).
Filtering
Filtering data allows you to identify specific criteria to apply to the dataset so that only data that meet the criteria are displayed. For instance, you may want to show only the positive values in a column, or see only data for a specific site. If you have large datasets, filtering them is a faster method to finding specific data than using the Sort function.
To apply a filter to your dataset, select a cell in the data headers and click HomeEditingSort & FilterFilter. Small drop-down arrows will appear at the right side of each column header. Click on the drop-down arrow for the desired column. A listing of values contained in the chosen column will appear with checkboxes next to them, as well as an option for “Blanks” and “Select All.” Select by checking or unchecking the desired criteria, and clicking “OK.” You can also create a custom filter by selecting the “[Data Type] Filters” menu option in the Filter drop-down.
Note: For some data types, the checkboxes may combine data into groups (eg. All dates for 2001 will appear under the 2001 category); these groups will be indicated with a small [+] next to the group. You can expand the group by clicking on the [+] or collapse the group by clicking on the [-] that appears when it is expanded.
Once the criteria have been selected and the filter applied, your worksheet will now display only data that meet the criteria. Notice with your filtered dataset that the numbered rows on the left reflect the row that those data appear in within the entire dataset, and the other rows have been automatically hidden by Excel (NOT deleted). Depending on your dataset, these rows may or may not be adjacent to each other.
Note: When working with filtered data, be aware that cutting, deleting, copying or pasting batches of data will usually affect the data in the hidden cells as well as the filtered cells. To select only the data displayed by the filter, go to HomeEditingFind & SelectGo To Special… and then select “Visible cells only.”
Activity 6
Using your existing dataset, sort the data for January from largest to smallest. Next, apply a Filter to your dataset so that it displays only values in June that are greater than 12.
Formatting
Once you have created your worksheet, you may want to format it to make it easy to read and ensure that your data are displayed clearly and appropriately. Formatting is the adjusting the structure and layout of a worksheet and its individual parts, including text alignment, font size, number formats, and even borders and shading.
To format the way your cells display numbers, select the cells you would like to format. Choose HomeCellsFormatFormat Cells tab from the menu bar. The format cells dialog box appears, and allows you to select from different tabs to format Number, Alignment, Font, Borders, Fill (Shading).
There are a variety of number formats available to easily change the way your data are displayed.
Note: changing the format of the numbers only changes the way they are displayed, but does not change the underlying values. This enables you to switch back and forth between different number formats without losing essential information. Changing decimal places displayed automatically rounds the underlying values, however, the original values (not rounded) are still used in any calculations involving those values.
Activity 7
Select the numerical data values in your table and format them so that they display only one digit after the decimal place. Select the Month headers in your table and format the alignment so that the text is centered; format the font so that it is BOLD and underlined.
Saving your work
You will want to frequently save your work. This can prevent a great deal of heartache, headache, and wasted time. Additionally, it is important to save a copy of your original data before editing or manipulating it in any way. To save a copy of your original spreadsheet, use Office ButtonSave As. A dialog box appears prompting you for a location and file name. You can repeat the Save function after additional changes are made by clicking on the floppy disc icon on the toolbar.