Introduction to Excel 2013
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, View®Zoom, 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
The Excel Screen
Depending on individual computer settings, Excel may look somewhat different than the window below.
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); the Quick Access Toolbar is located on the left side of the Title Bar. Use the Minimize, Restore, and Close buttons to size the window and/or close it. The Quick Access Tool Bar 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 Page Layout. 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.
Formula Creation & Math Functions
Excel provides several built-in math functions, or you can create your own custom formulas. To use a built-in function:
1. Click in the cell where you want the results to appear.
2. Click on the Insert Function button next to the Formula Bar to open the function dialog.
3. From within the Insert Function dialog box, you can search for a function by name or select a category in the Function category list. All of the associated functions are listed below the function category, with a description of the function and its arguments below. Note: the Insert Function dialog box automatically displays most recently used functions for the function category.
4. After selecting the function you want, click OK to close the dialog box (Note: some functions have variations available with different arguments and results; a description of each function is listed at the bottom of the dialog box when you select it; always be sure you are using the correct function.)
5. The Function Arguments dialog box appears next
6. Define your arguments: you can do this by entering a value or a cell address (depending on the argument) directly into the space, or you can click the show/hide dialog box button, this will minimize the dialog box so you can more easily select cells in the underlying worksheet (Note: each argument is clarified in the lower part of the Function Arguments dialog box)
7. After defining your arguments, click OK. The results of your formula will appear in the cell.
You can also create your own formula by either typing it directly into the formula bar. There are a few tips you need to keep in mind when creating your own formulas:
· Order of operations (PEMDAS): Parentheses, Exponentials, Multiplication & Division first, Addition and Subtraction second
· All formulas MUST start with an equal sign ( = )
· Use a blank cell as your active cell to avoid errors
· Use appropriate arithmetic and reference operators for correct results
Note: to view the function in a cell, select the cell and notice the formula displayed in the Formula Bar. If you double-click on the cell, the formula is displayed directly in the cell AND the cells or range of cells used in the formula are highlighted in various colors for easy identification.
Arithmetic operators: Perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, using the following arithmetic operators.
Arithmeticoperator /
Meaning /
Example
+ (plus sign) / Addition / = 3+3
– (minus sign) / Subtraction
Negation / = 3–1
= –1
* (asterisk) / Multiplication / = 3*3
/ (forward slash) / Division / = 3/3
% (percent sign) / Percent / = 20%
^ (caret) / Exponent / = 3^2
Reference operators: Combine ranges of cells for calculations with the following operators.
Referenceoperator /
Meaning /
Example
: (colon) / Range operator, which produces one reference to all the cells between two references, including the two references / B5:B15
, (comma) / Union operator, which combines multiple references into one reference / SUM(B5:B15,D5:D15)
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 HomeàEditingàSort & 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 HomeàEditingàSort & FilteràFilter. 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 HomeàEditingàFind & SelectàGo To Special… and then select “Visible cells only.”
Fill & Series Commands
Excel can help you enter a series of numbers, dates, times, or other standardized values. For instance, if you want to fill a column with a list of consecutive dates or numbers, instead of typing dates or numbers in each cell of the column you can use the “Series” command or you can click and drag the “fill handle” on a cell. Both of these methods are described below.
Using the Fill Handle
When you select a cell or cells, a small black square appears in the lower right corner of the selection. When you hover over the fill handle, the pointer changes to a “solid plus” (see above under Excel Cursors and Pointers). You can either:
· Click and hold the fill handle, drag down or across to fill sequential values into those cells
· Right-click the fill handle and hold it down while you drag the selection box over the cells that you want to fill. When you release the mouse button, a menu will appear, choose “fill series” and the boxes will fill automatically. (Note: the right-click option gives you a shortcut menu with additional “fill” options to choose from)