R. Craig Collins >Common > How To: Excel
How To:Excel ©
R. Craig Collins, 2005, 15
Word Tables to Excel Tables (pdf)
To YouTube Video Companion Part 1 (High Quality best viewed Full screen; PC & Apple compatible; oncampus allow time for buffering)
To YouTube Video Companion Part 2 (High Quality best viewed Full screen; PC & Apple compatible; oncampus allow time for buffering)
Refer to this web site for more info on items mentioned in the video.
Using Word and Excel for basic Database functions (pdf)
The nice thing about Excel is the layout is very similar to Word; same icons,
similar ribbon items, etc. And if you have used tables in Word, you already have
an idea of what spreadsheets do. The biggest difference is that a spreadsheet
table can manipulate numbers much better than just adding a row, as you can
in Word... and Excel does all the math. (Note, a video demonstrating key points is here)
Excel Key Points
Excel is a spreadsheet, a program for manipulating
numbers.
A spreadsheet is composed of tables, which
are made up of rows and columns; the intersection is a cell
Most work in spreadsheets are done using
formulas, such as =3+5, or =A1B2
Compound or complex formulas are called
functions, such as =sum(A1:B2)
Lesson: Entering Data into an Excel Worksheet
Understanding Excel Data Types
Spreadsheets are designed to calculate formulas, analyze numerical data, and
display information in charts. Instead of typing in just words, which are called
labels in a spreadsheet, you may also enter numbers, called values, or manipulate
the numbers with functions and formulas.
Entering Text
Rows are divided into cells, into which you can type labels, values, formulas,
or functions. Select a cell with your mouse, or use the arrow keys, the type
a word or phrase, and then hit the Enter key. Text is left aligned, by default.
A 
B 
C 
D 
E 
F 

1  Test 1  Test 2  Test 3  Test 4  Test 5  
2  Student 1  
3  Student 2 
A 
B 
C 
D 
E 
F 

1  Test 1  Test 2  Test 3  Test 4  Test 5  
2  Student 1  99  96  94  87  26 
3  Student 2  99  100  89  93  99 
Operation  Normal Math  How Entered 
Parens  (2+2) + 8  =(2+2)+8 
Exponents  5^{3}  =5^3 
Multiplication  2 x 2  =2*2 
Division  4÷2  =4/2 
Addition  2+2  =2+2 
Subtraction  22  =21 
Entering Formulas
Aside from entering =31+A, you could also type =31+ then using the mouse, click
on A1. Press Enter when finished.
Using the Status Bar AutoCalculate Feature
If you highlight a series of cells, on the status bar on some versions of Excel appears the sum of the numbers.
This is only a preview feature. You may also right click the sum to get other
calculations.
Note the Sum=7 preview on the status bar
Editing Formulas
If you decide to change a formula that has been entered, select the cell, then
make the changes in the formula bar. Or select the cell, and click [F2]
To reveal all formulas, press Control + [`] (the unshifted ~). To go back to normal view, press Control + [`] again
Lesson: Performing Calculations with Functions
What are functions
Functions are stored formulas, so instead of typing in =A1+A2+A3, you could do
=SUM(A1:A3), which means add values in A1 through A3.
There are many useful functions, such as AVERAGE, COUNT, plus financial, statistical,
database, etc. Using Excel's Help, read Function Reference. You may quickly tell
Excel you want to use a function by clicking the fx next to the formula
bar, or use the Formula tab.
See Functions below for more on the Formula tab.
Using AutoSum (Sum)
If you click under a column of numbers, and press the []
button, Excel will automatically Sum those numbers.
Using the Insert Function Feature
As shown above, you may click the fx button to enter a function, but you
may also choose the Formula tab. Dialog boxes will assist
you with complex functions. Drag across cells to input them into the dialog box,
and use different ranges of numbers (such as A1:A3 andA17). If you have
trouble seeing the cells to click on, click the Collapse button on the Number
text box, choose the number, the click the Expand button to get back to the dialog
box. (Just like minimizing any window...)
Lesson: Resizing Columns
Note, you don't resize cells, you resize columns.
If text is too large to fit in the cell, it may spill into the next cell (as in row 2),
however,
the second something is added to the cell to the right (as in row 3), the words will be truncated.
A 
B 
C 
D 
E 

1  Test 1  Test 2  Test 3  Test 4  
2  Student 1  
3  Stud  99  96  94  87 
The problem is, if numbers were spilling into the next cell, truncation might mislead you as to what the actual number was...
is it 50 or 500000? So numbers don't truncate... instead, if the column is not wide enough to hold a number, it displays #####
A 
B 
C 
D 
E

F 

1  Test 1  Test 2  Test 3  Test 4  
2  Student 1  
3  Stud  99  96  94  ###  
To correct this situation, resize the column.
Three methods
1. Place your cursor between the two columns, the cursor changes to a double sided arrow, click and drag to resize
2. Highlight all the columns that need to be resized, the double click the line between any two of the columns
3. On the Home tab, choose Format, then choose Column Width
Lesson: Formatting
To change text color, alignment, fill color:
Choose the cell, then choose the Home tab, then select Format, then Format Cells...
(See image above)
You may also right click the cell.
Lesson: Creating Charts
Understanding Charting Terminology
Add the following to an Excel Spreadsheet:
Grade  Count 
A  7 
B  6 
C  2 
D  1 