Excel Techniques & Tips

(Note: Excel 2003 Was Used To Create the Items Below)

	Function/Tip	Procedure
1	Selecting full rows or columns, and all cells in the file. Selected cells are shaded (ie: this is useful when numerous cells' format is to be changed)	Row – click the line number in left column Column – click on the letter above the column All cells – click on the blank box in the upper left corner (intersection of rows & columns)
2	Formatting numbers and fonts (change the desired appearance, length, etc.)	Select the cells to be formatted. Format, Cell, Number, select the desired option, OK. For fonts, click Font instead of Number
3	Insert whole rows or columns, or single cells columns	Insert, select Row, Column, or Cell. If Cell is selected, select either Shift Cells Right, or Shift Cells Down
4	Auto fill ("automatically" continues a series (ie: sequential numbers) in additional cells). This saves data entry time.	Fill in first 3 cells with the beginning of the series, select these and the blank cells to be filled, Edit, Fill, Series, select the desired fill options, OK
5	Merging cells (ie: create one cell at the top of a multi-column file to place the title)	Select the cells to be merged. Click the Merge & Center button
6	Adding cell borders, colors, or patterns to improve the appearance and "useability" of the spreadsheet	Select the cell(s) to be formatted, Format, Cell, Border (or Pattern for colors or patterns), selected the desired item(s), OK. (Note: border line thickness can also be changed.)
7	Auto sum (∑) (add the sum of several numbers in the cell after the last value)	Select the cells to summed. Add one more cell for the “answer”. Autosum ("∑" button)
8	Change text orientation (ie: rotate the direction of the cell’s content up to 90 degrees)	Select the cell(s) to be formatted. Format. Cells, Alignment, insert the desired number of degrees to be rotated, OK
9	Wrapping text in the cell (ie: when the data's length exceeds the width of the cell)	Select the cells to be formatted. Format, Cells, Alignment, check the Wrap Text box, OK
10	Cell auto height/width (the size of the cell sometimes needs to be increased to fit the data entered)	Manual – Place cursor in left column (row ht.), or top row (col. width), click and hold left mouse button, drag to desired ht./width Automatic – Select row or column, Format, Row (or Column), select Height (or Width) or Autofit, enter desired dimension, OK
11	Changing the position of data content in cells (ie: horizontal and vertical alignment)	Select the cells to be formatted. Format, Cells, Alignment, select desired horizontal & vertical alignment, OK
12	Copying formulas or cell content (this speeds up the creation of a new spreadsheet)	Select the cell to be copied. Place the cursor on the square in the lower left corner, click and hold down the left mouse button. drag over the cells to receive the copy info.
13	Format painter (copy the format to adjacent cells)	Select the cell whose format is to copied, click on Format Painter (paint brush), drag the cursor over the cells to receive the format
14	Freeze pane (allows some rows/columns to stay on the screen and be able to scroll to other areas)	Select cell where you want to add the freeze point, Window, Freeze Pane
15	Repeat rows/columns across pages for use when multi-page documents are printed	File, Page Setup, Sheet, Rows To Repeat At Top (ie enter 1:5) or Columns to Repeat At Left (ie: enter A:E)
16	Cell comments (notes) (provide reference notes about cell contents)	Select the cell where comment is to be added, Insert, Comment, type the desired note. The note will appear when the cursor passes over the cell.
17	Hide rows and/or columns (the data does not appear on the screen but remains in the file)	Select row(s)/column(s) to be hidden, Format, Column (or Row), Hide (Note: to unhide, select the rows/columns around the hidden values. Repeat the above steps but select Unhide)
18	Insert page number at bottom (this is useful when multiple pages are involved)	View, Header & Footer, Custom Footer, select desired placement (L/C/R), select Page Number, OK, OK
19	Print only a portion of a worksheet (this can help save paper, etc.)	Select the area to be printed, File, Print Area, Set Print Area. To remove the selection in the future, File, Print Area, Clear Print Area
20	Using web queries to "automatically" access the web and download data such as stock quotes, etc.	Data, Import External Data, Import Data, select the desired option, Open, enter the cell where download should be placed, enter the parameter value & check the Use This value… box, OK. (Note: to refresh in the future, Data, Refresh Data)
21	Create a chart (graph) to provide a visual view of the data (ie: understand trends, identify highest/lowest values, etc.).	Select the data to be included in the chart, press the Chart Wizard button, select the desired chart type, Next, select how the data is oriented (Rows or Columns), input chart format info. (ie:, title, legends, etc.), Next, select the option on where the chart should be placed (as new sheet, or as object in (which worksheet), Finish. Position and resize the chart as desired.
22	Add a water mark in the background (note: the procedure is different than that used with MS Word)	View, Header & Footer, Custom Header, click the Insert Picture button, find the desired graphic file, OK. To resize, click the Format Picture button, Size tab, change the height & width as required. (note: graphic appearance changes are made by clicking Picture tab, Picture, Color, Washout, change the Brightness/Contrast values as desired.)
23	Adding graphics (ie:, photo, or clip art)	Insert, Picture, select Clip Art or From File, select the desired item. Resize as required and move to desired location)
24	Creating a database for numbers &/or text	Add descriptive text at the cell at the top of each column in the new database. Select the cells. Data, Form, fill in boxes with appropriate info., New, repeat until all info. Is entered. Close (Note: Data validation can also be applied to the cells to make sure that the correct type of information is entered.
25	Conditional formatting (have the format of a cell change when the value entered meets defined criteria (ie: numbers turn red when value is less than zero))	Select the cells to be formatted, Format, Conditional Formatting, set criteria for use of the special formats, Format, select the format to be used when criteria are met, OK, Add, repeat these steps for additional criteria)
26	Filter/sort data (this is a help in finding specific info.	Filter: Select the row at the top of the list (ie: the one that describes the column's content), Data, Filter, AutoFilter. A dropdown arrow appears in the cells. In the desired column, click on the arrow then select the option that you want to find. Sort: Select the column to be sorted, Data, Sort, select the desired sort option(s) (ie: ascending/descending), OK
27	Add an in-cell drop down list to allow selection of specific data entry options without the to type them in (ie: if the entry month of the year, name, etc.)	In a separate area of the worksheet, list the information that should be contained in the drop-down. If desired, hide the values. Select the range of cells where the drop-down is to be applied, Data, Validation, List, click on the red arrow in the Source box, select the info. list referenced above, Enter, OK.
28	Data validation (assuring that correct information is placed in cells). This features allows the use of instructional notes that pop-up when the cell is selected for data entry. It allows allows use of error messages to pop-up when the data entered doesn’t meet criteria)	Select cells to be formatted, Data, Validation, Settings (Allow – select the desired option) Option 1 – continue to Input Message, add the desired message, OK Option 2 – continue to Error Alert, add the desired message, OK
29	Creating links to cells, other worksheet, files, web sites, etc.	Insert, Hyperlink, select the desired option (ie: existing file or web page, place in this document, etc.), select the specific file, etc. to be linked, OK
30	Spell checking	Tools, Spelling, respond to any flagged items
31	Shift to the next row when info. into a cell (ie: similar to pushing the return key on a typewriter)	Begin entering info. Press and hold the ALT then Enter keys, contine typing in the new row. Repeat until all of the rows have been entered in the cell
32	“Go to” feature – this allows you quickly jump to a specific cell. This is very useful in large files.	Edit, Go To, enter the cell info. (ie: Z52), OK. (Note: each cell accessed by the Go To feature is stored and can be used again in the future to access the cell(s).
33	Worksheet tabs (adding words, and changing tab color)	Right click on the tab, Rename, type the desired name, OK. To change color, follow same steps, but select Tab Color
34	Open multiple files at once (this is useful if you're working on a project that involves more than one file)	Open all of the Excel files to be included, Window, Arrange, select the desired arrangement, OK, File, Save Workspace. (Note: this creates a workspace file which has a .XLW extension. Opening this file in the future will trigger automatic opening of the files included in the workspace)
Items Common with MS Word
1	"Auto saving" files to avoid info. loss in case of abnormal shutdowns or system crashes	Tools, Options, Save AutoRecover info every, Minutes box (specify auto-saving frequency)
2	Find/replace text	Edit, Find, Replace, enter Find What and Replace With, Replace All
3	Insert symbols (ie: @ © & G )	Insert, Symbols, select desired symbol, Insert
4	Embedding files (ie: other files can be attached and saved as one "package")	Insert, Object, Create From File, check Display as Icon, Browse, find file, Insert, OK