MICROSOFT EXCEL
Microsoft Excel is
a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing
tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely
applied spreadsheet for these platforms, especially since version 5 in 1993,
and it has replaced Lotus 1-2-3 as the industry standard
for spreadsheets. Excel forms part of Microsoft Office.
INTRODUCTION TO MS EXCEL 2007
Microsoft
Excel is a very powerful tool for you to use for numeric computations and
analysis. Excel can also function as a simple database but that is another
class. Today we will look at how to get starting with Excel and show you around
the neighborhood sort of speak. I hope to see you in one of the intermediate
classes later on.
What do I see
first?
When you start
up Excel, the first thing that you see is this:
This is a
workbook. A workbook is a collection of worksheets (spreadsheets) and macros.
By default, Excel creates 3 worksheets in a new workbook. The worksheets are
designated at the bottom part of the window where you see the file folder-like
tabs. The tabs are named Sheet1, Sheet2, and Sheet3. If you click on Sheet2,
you will be in Sheet2 and not Sheet1 so you need to be aware of which worksheet
you are in.
Exercise 1:
renaming a worksheet
I suspect that
you will want to rename the worksheet(s) to something more relevant to your
needs. To rename a worksheet:
1.
Double
click on the sheet’s tab (notice the name becomes highlighted)
3.
Hit
enter to complete the rename process
Office Fluent
user interface
In Excel 2007,
the new Office Fluent user interface replaces the traditional menus and
toolbars from previous versions of Excel with a single mechanism designed to
help users find the right features more efficiently. The interface contains
three main components; The Office Button, The Quick Access Toolbar, and The
Ribbon. 
Exercise 2: customize the Quick Access Toolbar
By default,
the toolbar only contains three basic functions; save, undo and redo. You can
customize the toolbar to include a shortcut to any pre-defined function in
Excel.
1.
Right
click within the Quick Access Toolbar, and click on Customize Quick Access
Toolbar
2.
Click
on the name of a function to highlight it, then click Add.
3.
Click
on OK when done.
Exercise 3:
Open a New Workbook
Some times you
have to create a new workbook.
1.
Click
on the Office Button
2.
Click
on New
You are now
able to open recently used or new workbooks from this panel. You can also
open up templates that are available with Excel or ones that you create. If you
click on Blank Workbook, Excel will create a new workbook for you.
If you click
on Installed Templates, you can select already made workbooks that Microsoft
provides. For example, there is a Personal Monthly Budget.
(You could
also open a new workbook by hitting CTRL-N, or by adding New to the Quick
Access Toolbar. Both of these methods open a blank workbook without going
through the New Workbook wizard.)
What are
Columns, Rows, and Cells?
Columns are
the vertical markers in the worksheet and are denoted by the alphabet i.e. A,
B, C. The rows are the horizontal markers in the worksheet and are denoted by numbers
i.e. 1, 2, 3. Cells are the single box that you get where the column and row
intersect i.e. A1, B3, and C2.
You will often
need to know the cell reference. The cell reference is the cell’s name and you
can find that by looking at the toolbar. This means that the cell that is
selected is named I5
Exercise 4:
selecting a cell
1.
We
are going to select C28. Look for the C column.
2.
Look
for the Row number 28.
3.
You
may use your finger to follow the column C down to where row 28 is.
4.
Once
you have located it, click on it.
5.
Look
at the toolbar; the cell reference box should say C28. If it does not, try
again.
6.
After
you have done this, click on a different cell and note the cell reference
box.
Exercise 5:
selecting a group of cells A4 to D10
1.
Click
on the first cell A4
2.
Click
and Hold the mouse button down. Drag the cursor down to D10.
3.
Let
go of the mouse button.
4.
If
you did it correctly, you should see a Highlighted box around those cells. If
not, try again.
Exercise 6:
Basic data entry, fill handle
From the
example above, we have numeric (year, numbers) and text (months) entered as
data in our worksheet. Let us practice by re-creating the example on our own.
Method 1:
1.
Click
on cell A2 to select it.
2.
Type
in 1981 and hit Enter. Notice by hitting Enter, we automatically move down to
the next row. (we can also do the same by hitting the down arrow)
3.
Click
on cell B1 to select it.
4.
Type
in January and hit Tab. By hitting Tab (or right arrow), we move to the next
column.
We can
continue to doing this to enter the data from 1981 to 1992 and so on, but Excel
provides us with a tool to complete sequences.Method 2
1.
Click
on cell A2 to select it.
2.
Type
in 1981 and hit Enter.
3.
Type
in 1982, and then select both cells A2 and A3.
4.
Move
your mouse cursor over the fill handle (small black box on the bottom right of
the active cell) so that the cursor turns into a cross.
5.
Click
and drag the fill handle down to the cell desired.
Copy, Cut,
Paste
You can Copy,
Cut and Paste anything into your worksheet. You can copy from one worksheet to
another worksheet in another book. Let’s concentrate on the basics. We are
going to copy cells D4 to H9.
Exercise 7:
Cut/Copy and Paste to the same worksheet
1.
Using
the same worksheet, select cells A4 to D10.
2.
Use
CTRL-c to copy and CTRL-x to cut the selected cells.
3.
Click
on cell E29. It should be blank
4.
Use
CTRL-v to paste the data. You can also use the toolbar shortcuts for
cut/copy/paste as the functionality is the same.
Exercise 8:
Create example 2
1.
Re-create
the example worksheet shown below
2.
In
cell A1, type Full Name and hit Tab
3.
In
cell B1, type Address and hit Tab
4.
In
cell C1, type City and hit Tab
5.
In
cell D1, type State and hit Tab
6.
In
cell E1, type Zip Code and hit Tab
7.
In
cell F1, type Phone and hit Return.
8.
Continue
and add a few examples (do not worry about the content).
Insert &
delete columns, rows, and cells
Have you ever
entered all of your data and realized that you are missing an entry in the
middle of the worksheet? If yes and you did not know how to add columns or
rows, it would be really difficult to fix. Well, there is an easier
method.
You can insert
columns, rows, or cells in any spot on your worksheet.
Exercise 9:
Insert columns
Before you can
insert a column or row, you need to know how Excel inserts a column or a row.
·
For
columns, Excel inserts new column(s) to the left of the selected
column(s).
·
For
rows, Excel inserts new row(s) above the selected row.
1.
We
want to insert a new column for Last Name between Full Name and Address.
2.
Since
Excel inserts to the left, you need to click on the column letter B. Column B
should be highlighted.
3.
Point
(do not left click) the arrow at the B column and then right click.
4.
A
pop-up menu should appear. In the menu you should see Insert.
5.
Click
on the word Insert. Excel will insert a blank column between Full Name and
Address.
6.
Click
on B1 and type in Last Name.
Exercise 10:
Insert rows
1.
We
want to insert a new row for Anne Frank’s contact information between John Doe
and Fred Johnson.
2.
Since
Excel inserts rows above the selection, you need to click on row number 3. Row
3 should be highlighted.
3.
Right
click while pointing at the number 3.
4.
A
pop-up menu should appear. In the menu you should see Insert.
5.
Click
on the word Insert. Excel will insert a blank row between John Doe and Fred
Johnson.
6.
Click
on A3 and type in Anne Frank’s contact information
Exercise 12:
Delete columns and rows
We all make
mistakes. It is very easy to remove a column or rows.
1.
Select
the column or row that you want to delete.
2.
Point
at the highlighted column name or row name and right click.
3.
A
pop-up menu should appear
4.
Select
Delete
5.
The
column or row should be gone.
Exercise 13:
Insert and delete cells
The insertion
or deletion of columns and rows are easy to keep track of. However, when you
insert or delete a cell or a group of cells, you need to be mindful of where
these cells are. The reason is that the inserted or deleted cells will shift
information in the direction of the insertion or deletion.
1.
Select
cells B3 to C4. (Click on B3 and hold the left mouse button. Drag the cursor to
point at C4.)
2.
Right
click while pointing anywhere in the highlighted area.
3.
A
pop-up menu should appear and select insert or delete. In the example, I
selected the Insert function
4.
The
next window will ask you in which direction would you like to shift the cells.
If you selected delete, you will see the same options.
·
Shift
cells right will shift the cells in columns to the right by the number of
columns in the highlighted selection. For example, if you select B3 to C4, we
have 2 columns so the Shift cells right will move the selection 2 columns to
the right or if it is a deletion, 2 columns to the left.
·
Shift
cells down will shift the cells in rows downward by the number of rows in the
highlighted selection. For example, if you select B3 to C4 we have 2 rows so
the Shift cells down will move the selection 2 rows downward or if it is a
deletion, 2 rows upward.
·
Entire
row and entire column is like using the insert a row or column discussed
earlier.
Inserting
& deleting new worksheets
There will be
many times when you need to add a whole worksheet rather than columns or
rows.
Exercise 14:
Insert a worksheet
1.
Right
Click on the tabs where the name of the worksheets are.
2.
Click
on Insert.
3.
Select
what kind of insert you want from the window.
4.
To
move the worksheet, click and hold the left mouse button on the worksheet tab.
Drag the tab to where you want it to be placed. Note: you can only move the
worksheet tabs to the left or right.
Exercise 15:
Delete a worksheet
1.
Click
on the tab of the worksheet that you want to delete.
2.
Right
Click on the same tab of the worksheet
3.
Click
on Delete
4.
A
warning window will appear to ask you if you are sure about the deletion.
5.
If
you select OK, the worksheet will be removed and if you select Cancel, the
worksheet will not be removed.
Copying
worksheets
You may want
to copy the whole worksheet rather than selecting the cells in the old
worksheet to copy to a new one. Besides being easier to do, there might be some
links in the cells that do not transfer well.
Exercise 16:
Move or copy a worksheet
1.
Click
on the tab of the worksheet that you want to Move or Copy.
2.
Right
Click on the same tab of the worksheet
3.
Click
on Move or Copy
4.
Select
where you want the worksheet to go
5.
If
you want to COPY, you must click in the Create a copy box; otherwise, Excel
will move the worksheet.
6.
Click
on OK
Printing
Printing your
worksheets out to a printer would be a nice thing to know how to do. If the
information that you have on the worksheet fits on an 8.5”x11” paper, you have
no trouble printing your worksheet out. The problem arises when you have a
larger worksheet. You may have noticed that your worksheet has dashed lines
running down and across it; this is your print area.
Exercise 17:
Setting the print area
1.
On
your worksheet, click on the Page Layout tab.
2.
Click
and drag to select the range of cells that you would like to print.
3.
Under
Page Layout, click on Print Area, then Set Print Area.
Exercise 18:
How to print with Print Preview
1.
You
may click on the paper with the magnifying glass icon or click on the Office
Button and click on Print, then Print Preview.
2.
The
menu at the top is important because there are many print functions that you
can use for your worksheet. Here are some examples, turn on the gridlines,
shrink to fit one page, print headers or footers.
3.
Click
on Print. You will be at the Print menu.
4.
Click
Page Setup. You will see a control panel that will allow you to make some
cosmetic changes to the way the worksheet prints.
·
a)
Change the printing orientation - Portrait or Landscape
·
b)
Scale the worksheet - shrink to fit a single sheet or expand to fit a number of
sheets of paper 
·
c)
Change the paper size
·
d)
Change the print quality - 600dpi or 300 dpi
·
e)
Change the first page number - Default is auto
·
f)
Change the margins for the print job
·
a)
Change the position of the header and footer
·
b)
Center the worksheet on the page - horizontally and/or vertically Page Tab
Margins Tab 
·
a)
Add a Header - select a pre-made one or customize it
·
b)
Add a Footer - select a pre-made one or customize it
·
a)
Change the Print area - you can select to print only section
·
b)
Repeat titles at the top of the page or on the left hand side of the
worksheet
·
c)
Turn on the gridlines
·
d)
Print only in Black and White (no grayscale)
·
e)
Print in draft quality (it uses less toner but it is really ugly and graphics
will not be printed out)
·
f)
Turn on the Row and column headings (this is the numbers and alphabets for cell
reference)
·
g)
Change the way Excel prints out the pages - print left to right and then down
or print down and then left to right.
