Introduction To Excel Part 2 Advanced

1
Introduction To ExcelPart 2 Advanced
1
Charles Ramcharan Department of
Biology Laurentian University x4802,
cramcharan_at_Laurentian.ca
- This is Part 2 of a two-part Excel lesson and
this version applies to Excel Vista - This
PowerPoint file and an accompanying Examples file
can be found at the biology website for Charles
Ramcharan - Go to http//laurentian.ca/biology/ram
charan/index.htm - Click Quick Links
2
Note Office Vista
2
- MS Office Vista is the new Office suite for
Windows Vista. - According to reviews and my
personal experience, its - SLOW! - Fragile
(crashes often). - Has a different file format
than previous versions. - Wastes a huge amount
of screen space. - Allows but does not encourage
use of keyboard commands. - Offers not even one
single new function or capability! - The MS help
system has reached its final denouement gone
entirely. - Windows Vista will continue to
support older versions of Office. - Keep your
current software, and update regularly!
3
Outline
3
- This lesson is for those who have already
mastered the basics of Excel. - You should have
your Options set for efficient use, and should
know how to use the menus and move around the
worksheets using only the keypad. - If you dont
have these skills, please review the first file
in this series. - The overall goal of this
tutorial is to introduce you to a few of the data
manipulation abilities of Excel. - Excel has
amazingly powerful routines for managing,
modifying, and analyzing data. - Anytime you find
yourself doing something repetitive with Excel,
youre doing something wrong! - Besides data
manipulation, the data functions are so powerful
that Excel has become a standard modeling tool
for Ecologists, Economists, Engineers, Chemists,
Astrophysicists, Physicists, Mathematicians,
Planners, etc.
4
1. The Copy/Paste Special Menu
4
- Most people use Copy/Paste for making
duplicates of cell values. - However, each cell
has a lot more information than just its
contents. - A cell can have (a) Formatting
(e.g., colour, fonts, borders, shading) (b) Formu
las (c) Comments (d) Values (numbers, text,
results of formulas) - When you use Copy/Paste,
you copy all four cell features at the same
time. - What if you want to copy just the
formulas, but not the format? - Or, the answers
to the formulas, not the formulas themselves? -
Use Copy/Paste Special. - Open the Excel
Examples file and go to the Percent Occurrence
page. - Try copying the Pi data column to another
blank column using Copy/Paste. - It should work
fine. - Now try the same thing but copy the
column to the Blank page. - The formulas no
longer have valid references, so Excel generates
an error. - Now, try copying to the Blank page
with Copy/Paste Values. - It works (but the
formulas are no longer active)!
5
Note Embedding
5
- Using Copy/Paste for putting tables from Excel
into Word is a very bad idea - Windows will
maintain a live link between the Excel and Word
documents. - Windows will get confused and may
crash your document if the Excel and Word files
are not always in exactly the same drive and
directory that they were when the link was
established. - Every time you want to change a
value in the Word table, Excel has to be opened
and the Excel file loaded. - The solution is to
transfer tables from Excel into Word using
Copy/Paste Special, then choose the Formatted
Text option (RTF). - The data will appear as a
table that you can then re-format for Word.
6
2. Conditional Formatting
6
- Finding patterns in data, and identifying
outliers and errors is sometimes difficult. - One
method is to make patterns and outliers easier
for you to see on the worksheet. - Normally,
formatting for a cell or a range of cells is not
dependent on the cell contents. - Conditional
formatting is dependent on cell contents. - You
can use conditional formatting to make certain
values or ranges of values more conspicuous. -
Lets try to highlight values that are 0.1 and
above for Pi - In the Excel Examples file, go to
the Percent Occurrence page and select the range
from J2J149. - Use Format/Conditional Choose
Format only cells that contain - Set Condition
1 to Cell Value is greater than 0.1 - Then
click the Format button and set formatting to
Bold and the text colour to orange. - Click OK
and you will see the values of 0.1 and above
light up.
7
3. Data Filtering
7
- Another method for finding patterns and
outliers is to filter the data. - In the Excel
Examples file, select the range from A1J149. -
Use Data/Filter/AutoFilter and youll see a set
of arrows placed next to your column headings. -
Click the arrow for the column Species and youll
see a list of all the values in that column. -
Choose one of them, and youll now see the data
only for that species! - Notice how Excel warns
you that youre not looking at the entire
dataset The arrow for Species now has an
indicator that the dataset is filtered. - Click
the Disturbance arrow and set that to Undisturbed
and youll only see the data for the undisturbed
sites and whatever species youve chosen. - (If
you see nothing, its because no data fit your
combination of filters.) - Now, lets do
something magic - Copy the range from A1 to the
end of your filtered data. - Go to a blank line
in column A and do Paste Special/Values - You now
have copied only those lines of data that you
filtered! - To cancel all of this, just redo
Data/Filter/AutoFilter
8
4. Creating Frequency Distributions
8
- Excel has a beautiful function that allows you
to quickly generate a frequency distribution from
any set of numerical data. - Unfortunately, its
a bit tricky to use - Go to the Percent
Occurrence page in the Excel Examples file and
find the columns for Categories and Pi Bins. - We
are going to build an array function, which is a
very different type of Excel function to a normal
cell function. - An array function applies to a
range of cells, not just one. - Select the blank
cells from O2O13 in the Bins column. - Type in
frequency(J2J149, N2N12) but dont
press Enter! - Instead of Enter, hold down both
the Shift and Ctrl buttons, while pressing
Enter - Magically, the frequency function will
fill up the range you selected, and counts of the
numbers in the different categories will
appear! - You have an instant frequency
distribution. - The value in cell N13 is the
number of Pi values greater than 0.10, the last
number in the Categories column. - Try changing
a value for one of the bins the frequencies are
adjusted to match.
9
5. Pivot Tables
9
- We dont have time to do anything more than
take at a glimpse at the power of Pivot Tables. -
This is the coolest thing in Excel!! - The Excel
Examples dataset has several replicates of
samples taken from disturbed and undisturbed
sites in Bethel Lake. - The data are occurrences
of different species of benthic invertebrates. -
What if you wanted to know the total numbers of
animals found in each different type of
sampler? - Or in the two types of sites, or the
different replicates? - What if you wanted the
data summed by species, but within the different
replicates? - This could take hours of work! or
no more than 1 minute
10
5. Pivot Tables
10
- Select the range from A1J149. - Use Data/Pivot
Table and then click Next and Next again. -
Youre now looking at the Pivot Table Wizard. -
On the right side are buttons with the names of
all your variables. - There are three areas in
the pivot table where you can drag and drop these
variables to organize your data by rows and
columns, and to specify which variable you want
analyzed. - Lets try it - Drag the Replicate
button and drop it in the Row area. - Drag the
Disturbance button and drop it to the right of
the Replicate button in the Row area. - Drag the
Species button and drop it to the right of the
Disturbance button. - Drag the Abundance button
into the Data area. - Right-click anywhere in the
Total column and choose Summarize Data By, and
then Sum. - Click OK, and then Finish. - Youre
now looking at the abundance of your different
species summed by both Disturbance and Replicate!
11
5. Pivot Tables
11
- You can modify this pivot table in innumerable
ways. - For example, lets move Species to a
column. - Grab the Species button and drop it in
cell D3, right above the cell that says Total. -
Now, lets select the species we want to see. -
Click the little arrow on the Species button and
clear the check-boxes for a few of the species. -
When you click OK, only those species will be
displayed. - The best way to learn how to use
pivot tables is to play with them a lot. - Here
are some things to remember (a) Save your file
often (Alt-F/S) (b) For each pivot table, Excel
creates a duplicate of the included data, so your
file can quickly get huge and slow. (c) If you
want to preserve the results from a pivot table,
but dont want your file to be huge, use
Copy/Paste Special Values to copy the values from
the table into a new space.
12
Things I Didnt Have Time For
12
- Helpful functions If, Choose, Match, Lookup,
Exact - Sorting using indexes - Data set
organization tagged line format - Data set
filtering - Advanced data filtering using
queries - Data exporting using CSV files - Linked
workbooks and their risks - Keyboard macros