(upbeat music)
- Hello.
I'm professor Rick Jerz,
and I welcome you to this session titled,
A Brief Overview of Microsoft Excel.
I designed this session thinking about students
who haven't used Excel much.
However, students who are familiar with Excel,
also like this session as a refresher about Excel.
Everyone seems to learn something new.
In addition to introducing you
to some Excel fundamentals,
I also provide you some tips
for using Excel more effectively.
I have purposely kept this session short,
so that everybody can quickly come up to speed using Excel.
I think you are going to enjoy this session.
Let's get started.
There are a number of goals
that we have for this session.
I want to make sure that we all
have a basic understanding of Microsoft Excel.
Some of you who have used Microsoft Excel for a while
will find this beginning fairly basic,
but I wanna make sure that everybody
has the same starting point
as we continue to use Microsoft Excel
throughout this course.
I want to also show you some of the basic techniques
to using Microsoft Excel effectively,
including entering data, both numbers and text,
doing calculations and using functions,
formatting your data,
and some keyboard shortcuts.
What is Microsoft Excel?
A simple answer to this question
is that Microsoft Excel is a sophisticated calculator.
For our purpose, we can consider it
to be a business calculator.
In addition to calculations,
Excel can also create graphs,
and it can help us do data management.
It is not a word processor,
nor is it used for presentations directly,
and even though we can use it to manage data,
it is not a database program.
Within the Microsoft Office suite of products,
Microsoft Word is the word processor,
PowerPoint is for presentations,
and Access is the database program.
As we learn to use Microsoft Excel more effectively,
what we learn can be applied
to a number of other Microsoft products.
Microsoft Excel is a spreadsheet program.
Although it is the most popular
spreadsheet program available today,
it was not the first.
Spreadsheets go back to about 1979,
when the product Visicalc came out for the Apple II.
Prior to spreadsheets, if you wanted to perform calculations
with a computer, you had to write a program
in a special language, such as Fortran or BASIC,
in order to do calculations.
Not everyone enjoys learning how to program computers.
Visicalc gave us the ability to do calculations
without having to learn how to write programs.
My first encounter with personal computers,
and spreadsheets, was in around 1980,
when I was working for John Deere,
working on a special project in Horicon, Wisconsin.
A friend of mine invited me
to come over to his house one evening,
to see his new personal computer,
and help him learn how to use this new software product,
called Visicalc.
Horicon was a small town in Wisconsin,
not offering much other excitement,
so I said, "Sure, I'll come over."
After spending an evening with my friend,
I decided that it was much more fun
programming my HP calculator,
than this new Apple computer.
In around 1983, IBM introduced its own personal computer,
and a new spreadsheet product called Lotus 1,2,3
became available for it.
Lotus 1,2,3 became the most popular spreadsheet product
for around eight years,
replacing Visicalc as the market leader.
You might find it interesting
that over 30 years later, I have a real working copy
of Lotus 1,2,3, running on a virtual PC,
on my Macintosh computer.
Throughout the 1980s, a number of other companies
competed with spreadsheet products.
Microsoft Excel was originally developed,
and released for the Macintosh computer around 1985.
Even though there wasn't a version
for IBM compatible computers,
its graphic interface, and ease of use,
helped it to be consistently rated
as one of the best spreadsheet products
in the marketplace.
In around 1987, after Microsoft successfully introduced
its new graphical interface called Windows,
Microsoft took what it learned
from the Macintosh Excel product,
and created Microsoft Excel for Windows.
Again, on my Macintosh computer,
I can show you a real, working copy
of Excel version four, running under Windows 3.1,
on one of my other virtual PCs.
It took Lotus 1,2,3 a number of years
to believe that this new Windows operating system
would become viable.
When it finally did try to move its product
over to Windows, it was too late.
Microsoft Excel became the market leader,
and it has now dominated this marketplace
for over 20 years.
In this course, you could use any version of Excel,
from 2010 to the current version.
Of course, I recommend that you use the latest version
of Excel, and I most frequently run Excel from my Mac.
University computer labs typically have
one of the latest versions of Excel
on its lab computers.
If you have never used Microsoft Excel,
this might be one of your best opportunities
to learn how to use it.
Go ahead and start Excel.
This is how Excel looks when the product opens.
The biggest area in this product
is for us to enter information.
The entire file is sometimes referred to as a workbook.
Within each workbook, you are allowed
to have multiple worksheets.
In most installations of Excel,
the user is given three worksheets.
The most significant change to the user interface
occurred with Excel 2007.
Although to some of you,
this may look like menus and toolbars, it is not.
This area is now referred to as ribbons.
Ribbons really are different than menus.
In most Windows applications,
when you click on a word
in what appears to be a menu position,
you would get many other items to choose from.
But with the ribbon interface,
when you click on these words, you see different toolbars.
Some of you might think of this as live toolbars,
but it's important to realize that we no longer
have menu items.
What will drive some of us crazy,
is that Microsoft has moved
some of your commonly used menu items
to places that you can't find.
The Macintosh version of Excel is different,
in that it has both ribbons and menus.
The most commonly used ribbon is the Home ribbon.
Regardless of which Microsoft Office product you are using,
the Home ribbon is always the leftmost ribbon.
Whenever you need help in Excel,
you can click on this Search tool for help.
You should think of Excel as electronic grid paper.
The grid paper offers rows,
which are numbered from one to,
oh some very large number that you'll never reach.
It has columns, which are labeled as A, B, C, and so forth.
When we get up to Z, it changes to AA, AB, AC, and so forth.
And the intersection of a row and a column
is called a cell.
Every cell has an address,
which is made up of the combination
of its column location, along with its row designation.
So, when I click on this cell,
I can see that in this upper area,
I am clicking on the cell known as C7.
Later on we will do calculations
by referring to these cell addresses.
It is in these cells where everything happens.
Since Excel is a big calculator,
I can start entering numbers into these cells.
In cell C7, I will enter 123,
and then hit the Enter key.
If I want to edit this number,
and make it be something else,
I have two ways of doing this.
I can move up into this area called the edit area,
and make the changes here,
or I can double-click on the cell
that contains the information,
and edit it within the cell.
It used to be that you had to do the editing
in the edit area, but I kind of like this new,
double-click within the cell editing technique.
Doing calculations in Excel is pretty easy.
The key to doing calculations
is that you must start with the equals symbol.
If I wanted to add five and six,
I would enter this as =5+6, Enter.
You should note that this is a little bit different
than doing calculations on your calculator,
where you normally enter 5+6,
and then you end with the equal.
Remember to always start calculations with the equal key.
The most common math operators in Excel
are the plus, the minus,
the asterisk for multiply,
the slash for divide,
and the carat to raise to a power.
So, if I wanted to add five plus six,
and then multiply it by two,
I could enter this as, =5+6*2.
Hm, something seems wrong.
Five plus six is 11, times two, should equal 22.
17 is not the correct answer.
What is happening in Excel,
is that multiplication and division
have a higher priority over addition and subtraction.
So the multiplication of six times two occurs first,
which is equal to 12, and then Excel adds the five to it.
If want to change, or force the precedence of operations,
we can do so by using the parentheses keys.
You might use this technique on more modern calculators.
Now we get the right answer of 22.
In addition to the math operators,
Excel offers a wide variety of internal functions.
In this course, we will learn to use a good share
of these built-in statistical functions.
I will demonstrate functions to you
by using the square root function.
Let me take the square root of 22.
Again, we begin with the equal,
and then you need to type the name of the function.
I know this one to be SQRT, and then a parenthesis,
then 22, end parenthesis.
Let me show you a better way to do this.
If I really want the square root of 22,
I could do the same calcualation
by referencing C7, within the parentheses,
instead of putting the value of 22 here.
We get the same results.
You must master this technique of using cell references,
in order to make your business models very powerful.
I could also enter text into any cell,
and format this text as I would with a word processor.
If I type text into a cell that exceeds its width,
and if there is something in the cell to the right,
the words are cut off.
I can fix this problem in one of two ways.
I can move my mouse up to the line
in between columns B and C, left click,
and drag this line to the right, or to the left,
to adjust the cell width.
Another way that I can do this,
is to move my mouse up to that line,
in between two cells, and simply double click.
Click click.
This adjusts the column width
to the widest text in any cell within that column.
If I don't like how many decimals are shown after a number,
I can adjust the view of this number
by clicking the Decrease Decimal tool on the Home ribbon.
I can also change the view to dollars,
percent, and back to decimal, using these tools.
It is important to realize
that when you do this, you're not changing the value
of that number, you're only changing the way
you're looking at the number.
We can use some of these other tools
to also change the way that this value is displayed.
Another way to format a cell,
is to right click on it, and pick Format Cells,
to bring up a dialog box that provides all
of the formatting options available to us.
The most powerful feature of using spreadsheets
is the ability to setup equations,
and then change input cell values,
and see how the results change.
This is sometimes referred to
as either sensitivity analysis, or what-if analysis.
The spreadsheet made sensitivity analysis easy.
For example, let's assume that this 22 represents
the area of a square room,
and we want to determine the length of each wall.
Yep, the square root function will do this,
and, in this case, the length of a wall
is approximately 4.7 feet.
But how about if the room size is 45 square feet?
What is the length of the wall?
Since we have set this up as an equation,
all that we have to do is change the value of 22,
to 45, and the result is immediately recalculated
to be 6.7 feet.
This is the power of a spreadsheet.
Create a business model.
Setup all the relationships correctly,
and then vary the input to see how the output changes.
This is pretty cool stuff.
I suggest you get in the habit
of frequently saving your work in Excel.
The quickest way to save data,
is to hit the Control and the S key together.
On the Mac, the keystroke is Command-S.
Control-S works in many Windows applications,
and I like this keystroke,
because I could keep my little finger
on the Control key, and then hit the S
with my ring finger.
The very first time that you use this keystroke,
Excel will ask you to name your file.
I will name this one Demo.
From this point on, whenever I hit Control-S,
Excel will just simply save my file.
If you forget to save your file,
and something happens,
for example the power on your computer goes off,
you will lose all your work,
so I highly recommend getting into this habit
of hitting Control-S, oh, about every five minutes or so.
Okay, I think this covers most of the basics.
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions,
or need my help, please let me know.
(upbeat music)