A Guide to Microsoft Excel X
These instructions are pertinent to Microsoft Excel X, however, they may also be useful for earlier and later versions.
- Why Use Excel X
- Starting a New Worksheet
- Opening an Existing Worksheet
- Selecting Cells, Rows, or Columns
- Entering and Changing Data
- Resizing Columns and Rows
- Inserting and Removing Cells
- Creating a Chart
- Using Equations
- Frequently Used Functions
- Inserting Tables in Other Applications
Why Use Excel X
Excel X is a spreadsheet program and is great for organizing data and presenting it in a meaningful way. It is useful for making graphs, charts, tables, budgets, financial planning, address lists, and repetitive calculations or data entry in experiments.
[Back to Top]Starting a New Worksheet
A new workbook appears automatically when you first open Excel X. Each workbook contains a number of worksheets and charts. Tabs at the bottom of the window allow you to move between the worksheets. Each worksheet contains a grid of cells organized into columns (labeled with letters) and rows (labeled with numbers). To create a new workbook manually, click the New button in the toolbar. You may also create a new worksheet by click the File menu, then New Workbook.
[Back to Top]Opening an Existing Worksheet
If you want to open and edit an existing worksheet, expand the File menu and click Open, or press the Open button on the main toolbar. Find the file you want, select it, and click Open. Excel X can open worksheets made in other applications so long as they are saved as text documents.
[Back to Top]Selecting Cells, Rows, or Columns
You can select a cell by clicking on it, or a group of cells by clicking and dragging the cursor. To select a row or column, click the header of the row or column (rows have numbers, columns have letters). Again, to select an interval of rows or columns, drag the cursor. To select several disconnected cells, rows, or columns, hold down the Command key while selecting.
To move between cells, use the Tab, Return, or Arrow keys. The Tab key selects the cell to the right of the currently active cell. The Return key selects the cell below the currently active cell. The Shift key, when used with the Tab or Return key, reverses these processes (i.e. Shift + Tab selects the cell left of the currently active cell, and Shift + Return selects the cell above the currently active cell). The arrow keys select the cell in their indicated directions from the currently active cell.
[Back to Top]Entering and Changing Data
To enter or modify data in a worksheet:
- Select the cell in which you want to enter data.
- Type in the data. It will appear in both the selected cell, and in the editing bar above the worksheet window. If you make a mistake in your entry, correct it in the editing bar. To select the editing bar, move your cursor to it, and click once.
- Press the Return key, an Arrow key, or click on the green check to accept what appears in the editing bar. Press the Escape key or click on the red X to reject what appears in the editing bar.
Resizing Columns and Rows
For display purposes, you may want to adjust the size of the columns and rows. To do so:
- Select the columns or rows that you want to adjust.
- Go to the Format menu. If you would like to change the column width, select the Column submenu and Width. If you would like to change the row height, select the Row submenu and Height.
- Enter the desired size and click OK.
You can also change the size of individual rows or columns by placing your pointer on the column boundary to the right of the column header, or the bottom of the row header. The pointer will change to a double arrow cursor. Drag the edges in order to resize the column or row.
If you double-click on a column boundary, it will adjust the column leftwards to exactly wide enough to display all the text.
If the text still exceeds the width of its column, you might also consider formatting the columns cells to wrap long text onto lower lines. To wrap text:
- Select the cells, rows, or columns that you want to wrap text.
- Under the Format menu, select Cells.
- Click the Alignment tab.
- Under Text control, check Wrap text.
Inserting and Removing Cells
You will occasionally need to insert or remove cells. To insert a single cell:
- Select the cell where you want to insert the new a cell.
- Go to the Insert menu and choose Cells. At the dialog box, select either Shift cells right or Shift cells down to specify the direction you want to displace the current cells. Click OK.
Making a Chart
Charts are useful to present information visually.
- Enter the data you would like to be the horizontal or x-axis coordinates in column B and the vertical or y-axis data in column C. Column A should have labels for each data point. You should not feel bound by this scheme, however, it is a good idea to stick data for each axis in its own column.
- Select your data and press the Chart Wizard button . The following window will appear:
- Select XY (Scatter) as your chart type. Also select whether you would like your graph to have just points, points connected by straight lines, or points connected by smooth lines. Click the Next button.
- Leave the data range field alone. Click on the Series tab.
- Remove one of the series by selecting it and pressing remove. Edit the remaining series by changing the X values to =Sheet1!B$2:$B$6 where 2 and 6 are the first and last row number of your data and B is the column letter for the data points for the horizontal axis of the graph. Change the Y values to =Sheet1!C$2:$C$6 where C is the column letter for the data points for the vertical axis of the graph. If you need a second set of data points (i.e. a second distinct line), add a series and repeat the above steps for two additional columns, D and E. Click Next.
- In the Title pane, you can label a chart and the axes. In the Axes pane, you can toggle axis labeling on or off. In the Gridlines pane, you can determine how many horizontal and vertical lines you would like. In the Legend pane, you may decide whether you want a legend (necessary only if you have more than one line of data) and where you would like it placed. Under Data Labels, you can label each data point with its label or value. Configure your chart and click Next.
- Place the graph As new sheet. This will make the graph easy to access and edit. Click Finish.
- If you need to edit any part of your graph, you can either
double click on the offending error or use the graph toolbar (see
below) to edit the
Chart Wizard options.
Using Equations
In Excel X, you can write an equation in a cell that involves values located in other cells. Moreover, you can use predefined mathematical functions to make complex calculations easier. Excel automatically computes the value of these equations, and will update the value as the cells of which it depends change values.
- Figure out the equation you need using pencil and paper. For example if I was converting kilometers to meters, my equation would be y = 1000 * x where y is what I want to appear in the cell and x is a reference to another cell. Excel can do powers, logarithms, trigonometry, basic statistics, and many other weird functions.
- Select the cell for the equation. Press the = sign next to the input field in the Formula bar. You should get the following:
- Enter your equation. Any other cells you would like to include in your equation can be referenced by their unique column and row number, for example F8. If the value of a cell changes, Excel will automatically update the value of any other cells that refer to it. If you would like to sum a sequence of numbers, you may use the sum function (SUM(A1:A3)).
- Press Return to exit the cell (if you try to click another cell, it will be added to the equation rather than switch focus to that cell). Your equation will automatically update itself anytime a reference cell changes. More functions may be found under the Insert menu in the Function button.
- If you would like to use the calculation for another cell, you can copy and paste the cell (Excel will automatically shift any cell references in the equation the same vector shift as the equation). This may save you time if you are doing a column of identical calculations with an input column next to it. Otherwise, you should check that the cell references in the equation are still correct. Excel can also fill the selected cells up, down, left and right with your equation by selecting Fill and the option you need under the Edit menu.
Cell References in Formulae: If you need to use a similar formula for an entire column or row of data, you can copy and paste the formula for the entire set of data. Some references to cells will need to be changed with every paste, while others need to stay constant. Any cell reference that need to stay constant should use an absolute reference. Cell references that need to change with every paste should be relative references.
There are three ways to reference cells;
- Absolute references bind to a fixed location on the worksheet. It is signified by a $ before the source (i.e., $A$3 always refers to the contents of the cell in the third row in column A).
- Relative references change according to the location of the formula (i.e., a relative reference to cell A3 in the formula in cell B3 will always refer to the cell to the left of the one into which the formula is then pasted; the shift between the formula cell and the reference cell will be the same wherever you move the formula cell).
- Mixed references are references in which one element of the reference is absolute and one is relative (like C$5 where the row is fixed and the column is relative, or $C5 where the column is fixed and the row is relative).
For example, suppose cell
C5 contains the following formula:
=(C$4+$B5)*$B$1
Paste that formula into cell
C6, and it becomes:
=(C$4+$B6)*$B$1
Paste the formula into cell
D6, and it becomes:
=(D$4+$B6)*$B$1
You can also reference a range of cells. Just place a colon between the upper-left and lower-right cells. For example, $A$1:$C$5 references to the cells in rows 1 to 5, columns A to C. Such references are mainly used for specifying function input. For example, SUM($A$1:$C$5) will sum the previous box of cells.
Frequently Used Functions
Function | What it Does |
---|---|
ABS(number) | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
ACOS(number) | Returns the arc cosine of a number. The arc cosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. |
ASIN(number) | Returns the arc sine of a number. The arc sine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
ATAN(number) | Returns the arc tangent of a number. The arc tangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. |
AVERAGE(number1,number2, ...) | Returns the average (arithmetic mean) of the arguments. |
COS(angle in radians) | Returns the cosine of the given angle. |
DEGREES(number) | Converts radians into degrees. |
EXP(number) | Returns e raised to the power of the number. The constant e equals 2.71828182845904, the base of the natural logarithm. |
LN(number) | Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). |
LOG(number, base) | Returns the logarithm of a number to the base you specify. |
MOD(number, divisor) | Returns the remainder after the number is divided by divisor. The result has the same sign as the divisor. |
PI() | Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. |
POWER(number, power) | Returns the result of a number raised to a power. |
PRODUCT(number1, number2...) | Multiplies all the numbers given as arguments and returns the product. |
RADIANS(number) | Converts degrees to radians. |
RANDBETWEEN(number1, number2) | Returns a random number between the numbers you specify. A new random number is returned every time the worksheet is calculated. |
SIN(angle in radians) | Returns the sine of the given angle. |
SQRT(number) | Returns the positive square root for the number given. |
SUM(number1, number2, ...) | Adds all the numbers given as arguments and returns the sum. |
TAN(angle in radians) | Return the tangent of the given angle. |
Inserting a Table Into Other Applications
- Enter the labels and data as you would like them to appear in your other document.
- You may format your table by selecting Cells under the Format menu. You can change the way Excel presents your data by selecting any of the panes and changing the options. The Number pane allows you to format the data as text, currency (and what country's currency), times and dates, percentages, fractions, or your own custom data format. The Alignment pane allows you to specify how the data is positioned in the cells horizontally and vertically (centered, right, justified, or left), Orientation (horizontal, vertical, or your own custom slant), wrapped like a paragraph, or indented. The Font pane allows you to specify the font and style of the text. The Border pane allows you to format the lines between cells, while the Patterns pane allows you to color cells different colors and patterns.
- Select the cells you would like to include in your table. Under the File menu, select Print Area and then Set Print Area.
- Select Print command under the File menu. Instead of Copies & Pages, select Output Options.
- Check the Save as File checkbox and make sure the format is PDF. Click the Save button.
- Select where you want to save the file and click Save.
- Insert the PDF file into whatever program or document you like. In Word X, under the Insert menu, select Picture and From File. Select the PDF you just saved and click Insert. You will need to crop the PDF using Word's crop tool found on the Formatting Palette. You may also need to change the text wrap by double-clicking on the inserted image, selecting the Layout pane, and then choosing the text wrap option appropriate for your document.