Microsoft Excel Quick Reference Guide

By: ComputerConscript ©2006

Basic Navigation

Move and Copy Worksheets to another Workbook:

CAUTION: Be careful when you move or copy sheets that contain mathematical formulas.
 Calculations or charts based on worksheet data may become inaccurate if you move it.

1. To move or copy sheets to another workbook, open the workbook that will receive the sheets.

2. Switch to the workbook that contains the sheets you want to move or copy, and then select the sheets.
3. On the Edit menu, click Move or Copy Sheet.
4. In the To book box, click the workbook to receive the sheets. To move or copy the selected sheets to a
   new workbook, click new book.
5. In the Before sheet box, click the sheet before which you want to insert the moved or copied sheets.
6. To copy the sheets instead of moving them, select the Create a copy check box.

Freezing Panes for navigation:

The Freeze Panes command found in the Window menu freezes the top or left panes, or both, on the active sheet.
This is useful for keeping row or column titles on the screen while you scroll to other parts of the worksheet.
The panes that are not frozen do not scroll into the frozen part of the worksheet.

1. To freeze and unfreeze panes, switch to the window in which you want to freeze the panes.

2. If the window is not split, select a place to split the window.

3. From the Window menu, choose Freeze Panes.

4. To unfreeze panes, switch to the window that you want to unfreeze, and in the Window menu, choose Unfreeze Panes.

Add/remove tool bars:

1. Within the view menu at the very top left of the Excel window. Click View | Toolbars.

     (Place a check next to the toolbar(s) you wish to make visible within your Excel window)

Converting file to/from .CSV (comma separated values):

The CSV ("Comma Separated Values") file format is often used to exchange data between different applications. CSV is a simple text file where the comma character ‘,’ is used to separate each field of text.

1. To convert an Excel (.XLS) to a (.CSV) file: open an Excel spreadsheet. Click on file then select ‘Save As’.  The ‘save as’ window appears.  Select the location you would like this file saved in the ‘saved in’ field.
Save as type defaults to XLS. Click the drop down arrow and select: CSV (comma delimited) then click save.

2. To convert a CSV file to Excel (.XLS): You can convert a text file created from another program to a Microsoft Excel file by using the Open command (on the File menu) in Excel. When you open a file, Excel converts the file automatically. You can also import a text file with the Import Data command (Get External Data submenu on the Data menu). When you import a text file, Excel displays the Text Import Wizard, which provides additional flexibility and options for deciding how to import the text and then converts the file.

Select last cell in worksheet:

1. On the Edit menu, click Go To.
2. Click Special.
3. Select Last cell.

Calculations (using formulas)

Sum/Divide/Subtract/Multiple Cells:

Note: The equal sign “=” must precede every formula. Simply type formulas into any cell as appears below:

SUM Addition (type ‘sum’ into cell) adds all numbers in a range of cells.

Example: =SUM(A1:A4) formula adds cells A1 - A4 together, and places the total into the cell.

‘Autosum’ can also be used (Epsilon button “Σ” on standard toolbar).
Select the cell under a list of cells you would like to total. | Click the Autosum button. | Press Enter.

This will enter the correct SUM formula automatically and the total will be displayed within cell.

/ Division (forward slash) divides numbers within cells.

Example: =A1/A4 formula takes the value listed in cell ‘A1’ and divides by the value listed in ‘A4’. 

The result will be displayed in the cell where you entered this formula.

- Subtraction (minus) subtracts numbers within cells.

Example: =A1-A4 formula takes the value listed in cell ‘A1’ and subtracts the value listed in ‘A4’. 

The result will be displayed in the cell where you entered this formula.

*  Multiplication (asterisk) multiplies numbers within cells.

Example: =A1*A4 takes the value listed in cell ‘A1’ and multiplies by the value listed in ‘A4’.

=PRODUCT(A1:A4) multiplies all numbers within range (A1 x A2 x A3 x A4).

=PRODUCT(A1:A4,2) multiplies all numbers within range (A1 x A2 x A3 x A4) (x 2).

Copy dynamic formulas versus static formulas:

When you move a formula, the cell references within the formula do not change.

When you copy a formula, the cell references may change based on the type of reference used.

*see formula reference table – beginning of next page.

1. Select the cell that contains the formula.

2. Verify that the cell references used in the formula will produce the result you want. Switch to the type of reference you need, using the reference table on next page as a guide. To move a formula, use an absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1).

How?

1. Select the cell that contains the formula.

2. In the formula bar, , select the cell reference you want to change.

3. Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type
updates if a formula containing the reference is copied two cells down and two cells to the right.

4. On the Edit menu, click Copy.

5. Select the cell you want to copy it to.

6. To copy the formula and any formatting, on the Edit menu, click Paste

7. To copy the formula only, on the Edit menu, click Paste Special, and then click Formulas.

Formula Reference table and associated functions:

Reference (Description)	Changes to
$A$1 (absolute column and absolute row) (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)	$A$1
A$1 (relative column and absolute row) (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1).	C$1
$A1 (absolute column and relative row)	$A3
A1 (relative column and relative row)	C3

Note:   Placing a dollar sign $ to the left of each cell reference makes the value absolute.   
This means the cell reference will not change, regardless of which cell you copy the formula to.

Tip - You can also copy formulas into adjacent cells by using the fill handle (The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross).
Select the cell that contains the formula, and then drag the fill handle over the range you want to fill. You can
also move formulas and retain the cell reference by dragging the border of the selected cell to the desired cell.

Charting

Create Bar/Column/Pie Chart. Demonstrate understanding of appropriate use of charts:

A bar chart illustrates comparisons among individual items.

A column chart usually shows data changes over a period of time or illustrates comparisons among items.

A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always
shows only one data series and is useful when you want to emphasize a significant element in the data.

1. On the worksheet, arrange the data that you want to plot in a chart.

2. Select the cells that contain the data that you want to use for the chart.
Tip - If the cells that you want to select for the chart are not in a continuous range, select the first group of cells that contain the data that you want to include. Hold down CTRL, and then select any additional cell groups that you want to include. The nonadjacent selections must form a rectangle.

3. Click Chart Wizard  on the Standard toolbar, or click Chart on the Insert menu.

4. Follow the instructions in the Chart Wizard.

Charting continued:

Format data point. Change color, Add labels, format font:

1. Right click any data point within chart (e.g. Pie wedge / Bar within chart).
2. Select “Format Data Point”.
3. Alternatively, select data point | click on “Format” menu | and select “selected data point”.
4. Or simply double click the data point you would like to edit to bring up the same options.

From this point, you can change the look and feel of your charts (color, labels, fonts etc.).

Formatting/Editing Workbook

Change colors of cells, fonts, borders and tabs:

For Cells, Fonts & Borders:

1. Right click any cell or a selected range of cells then select “format cells”.

2. OR select the cell(s) you would like to edit | format menu | select “cells”.

      Within this window, you can change the color of cells, fonts and borders.

For Tabs:

1. Select sheet with tab you would like to change.

2. Format menu | sheet | tab color. OR right click tab and select “tab color”.

Use of Conditional Formatting:

You can monitor formula results or other cell values by applying conditional formats (A format, such as cell shading or font color that Excel automatically applies to cells if a specified condition is true). For example, you can apply green text color to the cell if sales exceed forecast and red shading if sales fall short.

If the value of the cell changes and no longer meets the specified condition, Microsoft Excel clears the formatting from the cell, but leaves the condition applied so that the formatting will be automatically reapplied when the condition is met.

To create a conditional format:

1. Select a cell or range of cells.

2. Select format menu | conditional formatting

Copy/Cut/Paste data with keystrokes (no mouse):

1. Use arrow keys to move cell selection around sheet.

2. Hold shift key while moving arrows to select a range of cells.

3. Holding CTRL and C copies a cell or selection into clipboard (RAM memory).

4. Holding CTRL and X cuts (moves) the contents of a cell or selection into clipboard (RAM memory).

5. Holding CTRL and V pastes the contents of the clipboard to your desired selection area or cell.

Formatting/Editing Workbook continued:

Paste Values (not formulas):

This is done when you want to copy the value and not the formula itself.  If you copy and paste a cell
that contains a formula, you may be pasting the formula, not the value the formula is presenting.

1. Select the cell which contains the formula value you would like to copy.
2. Right Click | Copy.
3. Select the cell where you want this value to appear: Right Click | Paste Special | Select Values.

Use of Format Painter:

You can use Format Painter  on the Standard Toolbar to quickly copy the formatting of cells;
(borders, fills, text formats, or number formats) & apply that formatting to other cells within a sheet.

1. Select a cell that has the formatting you want to copy.
2. Do one of the following:
• To copy the formatting to a single cell or range of cells, click Format Painter ,
  and then drag the mouse pointer across the cell or range of cells that you want to format.
• To copy the formatting to several cells or ranges of cells, double-click Format Painter ,
  and then drag the mouse pointer across each cell or range of cells that you want to format.
  When you're done, either click Format Painter  again or press ESC to turn it off.

Note: If you don’t see your standard toolbar under your word menus (top of your Excel window),
Click View menu | Toolbars | checkmark ‘Standard’ (& other toolbars if preferred).
Remember: Most Excel functions can be accessed in word menus (top left of your Excel window).
Format Painter is an exception.  Toolbars allow you a graphical way to access the same functions.
Tip - Right Clicking on objects (such as cells) within any Windows program (including Excel) brings
 up a menu with commonly used functions.

Format cells for Currency, Number and Percentage:

1. Right click the cell or range of selected cells then select ‘format cells’ option.
2. Under the ‘number tab’, select ‘Currency’ ‘Number’ or ‘Percentage’ as the formatting style.

Data Navigation

Apply AutoFilter:

Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet the criteria you specify for a column. When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered range.

1. Click a cell in the range you want to filter.
2. On the Data menu, point to Filter, and then click AutoFilter.

Data Navigation continued:

Sorting multiple columns (Ascending/Descending):

Note: For best results, the range you sort should have column labels.

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort.
4. Select any other sort options you want (ascending/descending), and then click OK.

Note: Click Ascending to sort the lowest number, the beginning of the alphabet, or the earliest date first in the sorted range. Click Descending to sort the highest number, the end of the alphabet, or the latest date first in the sorted range. Blank cells are always sorted last.

Filtering for Unique Records:

1. Select the column or click a cell in the range or list you want to filter.
2. On the Data menu, point to Filter, and then click Advanced Filter.
3. Do one of the following.

o To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.

o To copy the results of the filter to another location, click Copy to another location.
Then, in the Copy To box, enter a cell reference.

4. Select the Unique records only check box.

Selecting Visible Cells only:

1. Select Edit menu then Go to.

2. Click Special button (bottom left of GoTo window).

3. Select Visible Cells Only and then click OK.

Subtotaling records:

1. Make sure the data you want to subtotal is in the following format: each column has a label in the first
    row and contains similar facts, and there are no blank rows or columns within the range.

2. Click a cell in the column to subtotal.

3. Click Sort Ascending  or Sort Descending .

4. On the Data menu, click Subtotals.

5. In the At each change in box, click the column to subtotal.

6. In the Use function box, click the summary function you want to use to calculate the subtotals.

7. In the Add subtotal to box, select the check box for each column that contains values you want to subtotal.

8. If you want an automatic page break after each subtotal, select the Page break between groups check box.

9. If you want the subtotals to appear above the subtotaled rows instead of below, clear the Summary below data check box.

10. Click OK.

Data Navigation continued:

Using Cell Validation:

You may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that
only positive whole numbers are entered.  Cell Validation verifies the data entered is within your set parameters.

You can even set messages which will appear when the cell is selected to indicate the entry type required.

1. Select one or more cells to validate.

2. On the Data menu, click Validation, and then click the Settings tab.

3. Specify the type of validation that you want under ‘allow’.

4. Optionally, display an input message when the cell is clicked (under input message).

5. Specify how you want Microsoft Office Excel to respond when invalid data is entered (under error alert).

6. Test the data validation to make sure that it's working correctly. Try entering both valid and invalid data
   in the cells to make sure that your settings are working as you intended.

Page Layouts

Setting Print Area:

1. On the View menu, click Page Break Preview.

2. Select the area you want to print.

3. On the File menu, point to Print Area, and then click Set Print Area.
Now when this sheet is printed, only the Print Area selected will print.

Forcing Print onto 1 sheet:

1. Click the worksheet.

2. On the File menu, click Page Setup, and then click the Page tab.

3. Under Scaling, click Fit to.

Adding headers to each page:

1. Select View menu and click Header and Footer.

2. Select the header/footer tab.

3. Select a preset of headers/footers under the “header:” drop down.

4. OR select Custom Header button to create your own.

Repeating Rows on each page:

1. Select the sheet you would like modify.
2. Select File menu then Page Setup.
3. Select Sheet tab.
4. Select the blank field titled: Rows to repeat at top making the cursor appear in this field.
5. Select the row you would like to have reappear on each page (select row number at far left).
6. The value is entered into the blank field upon row selection. Click OK.