Mercer County Library System
Brian M. Hughes, County Executive
1. Find the Look for the cell with the dark border around it. This is the active cell and it is
active cell ready for you to type in it. The cell address of the active cell appears at the
left-hand side of the formula bar. The cell address is always column-letter
first then row number. For example, cell A1 is the first cell in the
spreadsheet. Cell addresses are used in formulas to identify specific cells.
2. Highlight or To highlight one cell, place the pointer in the middle of the cell and click. To
select a cell highlight a range of cells, click in the middle of the first cell, hold your index
finger down and drag over the remaining cells.
The thick plus sign is the pointer icon for working with cells.
3. Move the Click in the middle of a cell and it will become the active cell.
active cell Use the arrow keys to move the active cell in the arrow‟s direction.
Page Up/Page Down move the active cell one full screen height up or
Home moves the active cell to Column A, keeping it in the same row.
Ctrl+Home moves the active cell to A1.
4. Enter 1. Click once to highlight/select the cell where you want to enter data.
information or 2. Type the number or word(s). Press the Enter key to complete the data
numbers entry and move down one row. Type the next number or word and hit
Enter. This will build a column of information. It is the fastest way to
3. To enter data moving from left to right, press the Tab key to accept the
data into the cell and move to the next cell to the right. Repeat these
steps to enter a row of data.
Note: Text will be left-aligned by default, while values (numbers) will be right-
aligned. You can change the alignment by highlighting cell(s) and clicking on
an alignment tool or command. If you want to enter a number or date and
have Excel “see” it as non-numeric, first type the apostrophe ( „)
6. Print You can print a worksheet or an entire workbook. Click on File then Print.
If you do not change any print settings, Excel will print the worksheet
currently open. To print the entire workbook (more than one sheet), click on
the Entire Workbook setting in the Print Dialog Box. Click OK.
7. Print by To print some, but not all, of a worksheet, highlight the desired data. Click
selection or print on File then Print then the Selection setting. Only the print area selected
area will print. You can also set the print area after you highlight the data-- click
on the File menu then Print Area and Set print area.
8. Page setup The Page setup command on the file menu includes margins, landscape
vs. portrait and header and footer choices. You can center the data
horizontally and/or vertically from the Margins Tab.
The Page Tab allows the data scale to be adjusted to X% so the data may
be enlarged or shrunk. Further, the data may be printed to fit to X pages
wide by X pages tall.
9. Switch Each worksheet has a tab at the bottom with its name on it. Click on a tab to
between make that worksheet active. If you have many worksheets, use the
worksheets sideways triangle symbols to move right and left among the worksheets.
10. Name a To name a sheet, double click on its tab and type a new name. Another way
worksheet is to right-click on the tab and click on Rename.
11. Insert a You may have as many worksheets as you want. Click on Insert then
12. Delete a Open the worksheet to be deleted. Click on the Edit menu then Delete
13. Change To remove the contents of the active cell, press the Delete key.
data already To replace a cell‟s contents with new information, click on the cell and begin
To edit data in a cell without erasing the cell‟s contents, double click in the
cell to enter edit mode. The cell now contains an insertion point. Make the
desired changes then press Enter.
14. Highlight a Hold the pointer over the center of the first cell you want to highlight or
group of cells or select. Press the left mouse button and hold it down. Now drag the mouse
range to highlight all the cells you want then release the button. This is called
“Click „n Drag.”
A group of cells is called a range. The range is described as “the first cell to
the last cell.” The range is represented in Excel language as “B3:F3.” The
colon means “to.”
15. Highlight Click on the grey row number or the grey column letter. The entire row or
entire rows or column will be highlighted. To highlight several rows or columns that are
columns next to one another, click „n drag from the first to last column in the grey
area. To highlight several rows or columns that are not next to one another,
hold down the Ctrl key and click each row or column header.
16. Change the To change column width, place the mouse pointer at the junction between
height of a any two column letters at the top of the columns. When you see the double
column arrow, click „n drag the column‟s vertical border to the desired column width.
To make the column automatically fit its largest entry, double click while you
see the above double arrow. To change a group of columns, highlight them;
click Format then Columns then AutoFit Selection.
17. Change the To change the row height of one row, drag the boundary below the row
row height heading until the row is the height that you want.
You can also select the row or rows that you want to change. Then from the
Format menu, choose Row and then click AutoFit.
18. Insert a row Select the row above which you want to insert the new row. Click on the
or column Insert menu then Rows. The new row will appear above the original row.
To insert a single column, select the column or a cell in the column
immediately to the right of where you want to insert the new column. Click
on the Insert menu then Columns.
19. Delete rows/ Highlight the rows or columns to be deleted. Click on the Edit menu, then
20. Cut & paste Highlight the cells to be moved beginning with the uppermost left cell, which
will become the anchor cell.
Click on the Cut tool on the toolbar.
Click where you would like the upper left cell (anchor cell) to appear.
Click the Paste tool on the toolbar.
21. Copy & Highlight the cells to be copied beginning with the uppermost left cell, which
paste will become the anchor cell
Click on the Copy tool on the toolbar.
Click where you would like the upper left cell (anchor cell) to appear.
4. Click the Paste tool on the toolbar.
22. Insert a cell Click where you need a new cell to appear. Click on the Insert menu then
Cell. You‟ll be asked how to move the data adjacent to the new cell.
22. Delete a cell Click where you need to delete a redundant cell. Click on the Edit menu
then Delete. Choose how to move the data adjacent to the new cell and
23. Copy and Click on the cell containing the formula. Click the Copy icon on the toolbar.
paste a formula Click the destination cell and click the Paste icon on the toolbar.
In the pasted formula, the cell references have automatically changed to
refer to cells in the same relative positions as in the original formula. This is
called "relative cell referencing".
24. Copy a When Excel copies a formula, it automatically changes the column and row
Formula with the references. For example, an autosum formula copied from column A to
Fill Handle column B will changed as follows. The cell range will change from
Sum(a1:a14) to Sum(b1:b14).
The Fill Handle is faster than using the Copy and Paste method. Click on
the cell containing the formula to be copied. Position your pointer on the
little square or handle in the lower right corner of the active cell.
When you see the “thin plus” symbol (shown at left), click and drag
vertically or horizontally to fill additional cells with a formula.
Note: This only works when the cells are contiguous.
25. Cut and Click on the cell containing the formula.
paste a formula Click the Cut icon on the toolbar.
Click the destination cell and then click the Paste icon on the toolbar.
The formula will be removed from its original location and placed in the new
With this command, relative cell referencing does not apply, and the formula
still refers to the original cells. Edit the formula to refer to the new column
26. Paste When you copy and paste a cell‟s contents, you may paste the formula
Special (paste command) or the value that was calculated by the formula (paste
special command). To paste the values, highlight and copy the desired
results and click where you want them to be pasted. Click on Edit then
Paste Special. Click on Values then OK.
27. Move data Copy the data in one or more columns or rows.
between rows Before you paste the copied data, right-click your first destination cell (the
and columns first cell of the row or column into which you want to paste your data), and
then click Paste Special.
In the Paste Special dialog box, select Transpose, and then click OK.
28. Locking a “Locking a cell” is also called an “absolute” reference. Sometimes you
cell reference will need to refer to the same specific cell on the worksheet in every copy of
into a formula a formula. In this case, use an absolute reference. An absolute reference
is fixed and never changes even if you move or copy the formula. Absolute
references are denoted with dollar signs ($) before the column and row
address, such as $A$1.
Excel‟s Order of Calculation
Operation Symbol Example Order
Parentheses () =(4+2)*8 1st
Exponents ^ =3^4 (3 to the 2nd
Division / =8/2 3rd
Multiplication * =4*6 3rd
Subtraction - =3-1 4th
Addition + =5+5 4th
30. Type a 1. Highlight the desired cell and type the equals sign (=). This is the signal to
formula from Excel that what comes after is a formula or function.
2. Type the first cell name
3. Type the arithmetical operator (+, -, *, /, ^)
4. Type the next cell name and the arithmetical operator, etc…
5. When done writing formula, press Enter. The result is displayed in the cell.
Reminder: When writing your formulas, remember the order of operations!
Note: After entering the formula, the formula will appear only in the “formula
bar.” The solution to the formula will show in the cell itself.
31. Type Rather than typing “A4” into a formula, you can click on A4 and it will appear
and “click” a in the formula. This can be a time saver.
1. Highlight the desired cell and type the equals sign (=). This is the signal to
Excel to prepare to write a formula or function
2. Click on the first cell in the formula
3. Type the arithmetical operator (+, -, *, /, ^)
4. Click on the next cell in the formula and type the arithmetical operator…
5. When done, press Enter. The calculated result is displayed in the cell.
32. Why Operations enclosed in parentheses will be performed before other
parentheses operations. For example, (4+3)*8 is not the same as 4+3*8.
A) (4+3)*8 =56: Excel performs operations in parentheses ( ) first, so Excel
first adds 4+3=7 and then performs the multiplication of 7*8=56.
B) 4+3*8=28: Excel looks first for ( )‟s, and because it does not find any, the
program performs the multiplication first, so 3*8=24, then Excel performs the
addition, so 4+24=28.
33. Function Excel can perform many functions that are predetermined mathematical
basics tasks including sums, averages and counting. Like formulas, functions
start with the equals sign and use cell ranges to refer to groups of cells.
For example, A1:A3 stands for "The cells from A1 through A3."
The basic structure of a function is: =Function Name (Cell Range). For
example, =SUM(A1:A3) will produce the same numerical result as
=A1+A2+A3. Insert or type functions in the cells where you want the
answers to appear.
All functions are available through the Insert menu and the function
command. You can also access all the functions by clicking on the function
34. Insert a Click on the Insert menu then the function command. Search for a
function function using the search box or select a function from those showing in
the selection box. If the function you need is not visible, type the name in
the search box or choose a Category of functions to explore.
Name and Description
35. Commonly Syntax
=SUM(A1:A3) Gives the sum of all cells found within the given range.
=AVERAGE(A1: Gives the average of all cells found within the given
=MAX(A1:A3) Gives the maximum value found within the given
=MIN(A1:A3) Gives the minimum value found within the given
=COUNT(A1:A3 Counts the cells within the range which contain
) numerical values
=COUNTA(A1:A Counts the cells within the range which contain any
36. Autosum The addition of a column or a row of numbers is simple enough for Excel
Function to make the calculation automatically. Most often, this is done at the
bottom of a column or at the right of a row of continuous numbers.
Highlight the cell where you want the sum of two or more numbers to
appear. On the toolbar, click on the Autosum icon on the toolbar.
Note: If you highlight a row of numbers without pre-selecting the cell for
your answer, Excel will place it in the first open cell
Cells must be highlighted prior to formatting!
37. Bold and Highlight the cells that label the contents of each column. Click on the
labels Bold and Center tools .
38. Merge and To center your title over all the data, type your title, highlight the cells in
center several one row which cover all the columns of data. Click on the Merge and
cells Center tool on the formatting toolbar.
39. Format First highlight the numbers to be formatted. Click on the Format
numbers menu then the Cells command. Click on the Number tab then the
Number category and you‟ll see that the number of decimals is already
set to two. Set the number of decimals you want then click OK.
On the Number tab, you can also show negative numbers in red or with
40. Change font Highlight the data. Click on the Format menu then Cells. Click on the
Font tab to make changes including font size and style and bold or italic.
41. Hide or Select the rows or columns you want to hide. On the Format menu, point
display rows and to Row or Column, and then click Hide.
To display hidden rows, select the row above and below the rows you
want to display.
To display hidden columns, select the column adjacent to either side of
the columns you want to display.
On the Format menu, point to Row or Column, and then click Unhide.
42. Split merged Make the merged cell the active cell. Click on Format, then Cells, then
cells apart the Alignment tab. Under Text Control, uncheck the box called "Merge
Cells". Click OK. The cells will now be split apart and the cell contents will
appear in the upper left hand cell of the group.
43. Allow text to Highlight the cells, rows, or columns you wish to affect. Click on Format,
wrap to a 2nd then Cells, then the Alignment tab. Under Text Control, click in the
line in a cell check box called Wrap Text. Click OK. When you type in these cells, they
will expand in height to contain whatever text is typed. To remove this
effect, uncheck the check box.
44. Place text This technique is often used to accommodate long labels for columns.
into cells at an Highlight the cells, rows, or columns you wish to affect. Click on Format,
angle then Cells, then the Alignment tab. In the orientation section, click „n
drag the hand of the compass to set the angle. Click OK.
45. Place Highlight the group of cells, then click on Format. Choose Cells and the
borders around Border tab. Click on the desired line style from the Style box to the right.
a group of cells Click on a color, if desired. Click the borders of the sample box on the left,
only in those locations where you would like a line. You can use multiple
line styles, for example have a heavy outline around the whole table and
lighter horizontal lines between the cells. Click OK when finished.
46. Change text Highlight the cells, rows, or columns you wish to affect. Click on the
color Format menu then Cells.
To change the font color, choose the Font color tool and click the drop-
down triangle. Click on the desired color.
47. Change To change the color that fills the background of the cell, click on the
filling color Filling tool icon and choose a color.
48. Quickly Highlight the area to format then click the Format menu and AutoFormat.
apply multiple Choose the desired table format from the list. The chosen style will appear
formats to a data in the Sample box. To apply only certain styles (such as font, borders, or
table colors), choose the Options button and make sure only the desired
formats are checked.
49. Insert Click the Insert menu, then Picture, then WordArt. Click on the basic
WordArt style you prefer from the WordArt gallery. Type your text then click OK.
Drag the double arrow ↔ on the square handles to resize. To change fill
colors and patterns on your WordArt, click the Format WordArt tool on the
WordArt toolbar. Then:
Click the downward pointing triangle beside Color, and then choose
your desired color from the choices available.
If you want a color that is not available, click More colors and pick
your color. Custom colors are available on the Custom Colors tab.
To change the color pattern/arrangement, click Fill Effects and
proceed from there!
50. Insert Clip Click the Insert menu, then Picture, then Clip Art. Click on the desired
Art graphic to insert it. Drag the square handles to change size; drag the
entire graphic to move it to a new location.
51. Make a 1. Highlight the data range you want to graph
graph from a
data table 2. Click the Chart Wizard tool on the Standard toolbar
3. Highlight the desired chart type then click Next
4. Make sure the range (data chosen) is correct then click Next
5. Fill in the title and other information boxes. Click the Legend and
Data Labels tabs for additional options.
6. Choose a location for the chart. You can place the chart in the
same sheet as the data or you place the chart on a new sheet.
52. Turn on the Click on View then Toolbars then Chart.
53. Re-size the Click on the background of the chart. Black handles will appear around the
chart edges. Drag a corner handle with the double-arrow pointer to increase
chart size and keep the chart in proportion.
54. Change font Click on the element whose text size you wish to change. That element
size of labels will now be defined with handles.
Change the font size using the font size box on the formatting toolbar.
You can also right-click and choose from the menu.
55. Change Click on the element you wish to affect. That element will now be defined
color, shading & with handles. Click the Format button on the Chart toolbar. A dialog box
pattern will appear giving formatting options for the highlighted element.
You can also double click on the desired element. The Format dialog box
56. Remove a Click on the data series you wish to remove. Click the Edit menu, then
series from the Clear, then Series. Or you can right click on the data series you wish to
chart remove, then left click the word Clear on the pop-up menu.
57. Print a Click on the graph so you can see the black handles. Click the Print
graph on its own Preview button on the Standard toolbar, and confirm that the graph is
page sized to fit the whole page. Click Print.
58. Print a This only works when you have placed the graph on the same page as the
graph & data on data.
the same page
Click on any cell in the data table, making sure that the graph does not
have the square black handles visible. Click the Print Preview button on
the Standard toolbar, and confirm that the graph and source data both
appear on the page. Click Print.
59. Selecting all Two or more adjacent sheets
worksheets Click the tab for the first sheet, and then hold down SHIFT and click the
tab for the last sheet.
Two or more nonadjacent sheets
Click the tab for the first sheet, and then hold down CTRL and click the
tabs for the other sheets.
All sheets in a workbook
Right-click a sheet tab, and then click Select All Sheets on the shortcut
Tip: To cancel a selection of multiple sheets in a workbook, click any
If no unselected sheet is visible, right-click the tab of a selected sheet.
Then click Ungroup Sheets on the shortcut menu.
60. Add a Comments are notes that you enter for a cell.
comment to a 1. Click the cell you want to comment on.
cell 2. On the Insert menu, click Comment.
3. In the box, type the comment text.
If you don't want your name in the comment, select and delete the name.
4. When you finish typing the text, click outside the comment box.
To delete a comment:
1. Select the cells containing the comments you want to delete.
2. On the Edit menu, point to Clear, and then click Comments.
To display a Comment:
Right-click its cell and then click Show Comment on the shortcut menu.
To display all comments, click Comments on the View menu.
To print a comment:
1. Click the worksheet.
2. If you want to print the comments in place on the worksheet, display
the comments you want to print.
3. On the File menu, click Page Setup, and then click the Sheet tab.
3. Do one of the following:
To print the comments where they appear on the worksheet, click As
displayed on sheet in the Comments box.
To print the comments at the end of the sheet, click At end of sheet in the
61. Show or Gridlines are displayed by default on a worksheet, but you can hide and show
hide Gridlines them as needed.
on a Select one or all the worksheets.
worksheet On the Tools menu, click Options.
On the View tab, under Window options, clear or select the Gridlines check
box to hide or show the gridlines.
62. Sort a Enter your data and include a label for each column. Decide which column you
data table want to rearrange or sort. Highlight the labels and all the information on either
side of the column you want to sort. Click on the Data menu then the Sort
command. Click on under “Sort by” and click on the desired column. Click
on the Ascending button to sort from low to high. The Descending button will
sort from high to low.
63. Freeze or You can view two areas of a worksheet and lock rows or columns in one area by
lock rows and splitting or freezing panes. When you split panes, you'll be able to scroll in both
columns areas of the worksheet, while rows or columns in the non-scrolled area remain
When you freeze panes, you select specific rows or columns that remain visible
when scrolling in the worksheet. For example, you would freeze panes to keep
row and column labels visible as you scroll.
To lock rows, select the row below where you want the split to appear.
To lock columns, select the column to the right of where you want the split to
To lock both rows and columns, click the cell below and to the right of where you
want the split to appear.
1. On the Window menu, click Split.
2. To remove the split, click Remove Split on the Window menu.
To Freeze columns or rows
1. On the Window menu, click Freeze Panes.
2. To unlock rows, click Unfreeze Panes on the Window menu.
64. F2 key Click on a cell containing a formula. Hit the F2 key and notice how colors are
(Edit) used to demonstrate which cells are being used in the formula. The color of the
element in the formula corresponds to the color around the cell.
65. Help Click on the Help menu then Microsoft Excel Help. There are two ways to
search: type a word or phrase in the Search Box. If the first word doesn‟t work,
Another option is to click on the blue Table of Contents link.
Source: Stow-Munroe Falls Public Library, Ohio at www.smfpl.org.
Microsoft Office Online at http://office.microsoft.com